No I wont do your assingnment for you... but I do think you have some problems with your order
You are missing at least one RI check
I don't understand the stuff in a the order table such as product type and product name.. isnt that part of the order details?
can a customer only order a single item per order
becasue od th way you have modeled your products you have soem confusion between CD's & DVD's. as far as a shop would be concerend they are just products.. ie I'd have the main common detials int he same product table, and then have child tables hanging off that product table that deal with elements that are specific to a CD or DVD or wahtever. the way you are settign this up you are going to have a problem if say the shop decides to sell downloads (anothe rnew table), or mp3 players or wahtever)
whatr is the differnece between an Employee and and a user.. coudl your coalesce these two entities into one item and the have a child table (if neccdesary to hold the unique data relevant to a customer or employee)
what happens if you have a employee who is also a customer, do you really want the users/employee to enter details twice.. its going to create a problme when the person moves address..... does the person doing the update realise that there are two addresses to maintian.
what happens if the user has more than one address (say work & home)
what happens if you need more than two telephone numbers (eg home, work alt work, alt home, cell, skype....)
what happens if the customer requests a order to be delivered to a different address that on file.
ok so thats a lot of questions, some of the answers to which may be.. I don't need to support that, or its irrelevant in what I'm trying to achieve, which is fair enough
I think you are heading in the right direction, but I do think there is a bit of confusion in your mind in where to store information. and how to handle the requirement to reduce storing the same or similar information in different discrete containers
those questions were posed to attempt to make you think about what you need to complete this project.
as regards separate front end thats down to the application front end.. which isn't the same as the backend / data store. there is no requirement to have one form to one table.
Thats down to how the data is extracted and manipulated. a good application starts with a good physical design which starts with a good grasp of what is required.
In larger server based applications often the data is presented as views, which could be a mix of data from more than one table, or they could be a specific snapshot of what a specific user or usergroup is allowed to view. for example an employee table may have a lot of sensitive data in in.. you may grant a view to say allusers to look at the employees names and work phone number, you may grant access to more senstivie data (eg home address and home phone number) to thier immediate line manager, you may grant full access to everything int he employee table to the personnel / HR function. granting full access to all data to all employees may lead you to at best a Data Protection Act violation and at worst horrible legal implications if an employee got harrassed at work becauase of slipshod design.
Its not really my "homework" either, its just the backbone for my degree project and want to get as solid of a database as possible. I'm just looking for advice and again thanks for what you've said so far.
In relation to the Order issue, Why can the user only get 1 item per order? I thought having the order details would get round this problem?
If I was to make another field Orderdetails and make that the PK would that solve the problem? I thought having the orderid posted from the orders table would make each order unique would address that issue but obviously after looking at it again I was wrong
I#d suggest if you want a review then Id suggest you talk to your peers, or your lecturer or tutors, they after all are closer to waht is actually being requested.
I think you need to go through each table and try to work out if the information is being stored there correctly, if there are no redundancies of data (ie the same element being stored in more than one place).