STUDENT STATUS #StudentNumber(foreign key)
(student status is to show if student is blocked or allowed to use PC)
BOOKINGS PER DAY
(StudentNumber ,Date) composite key
(to have a maximum of three bookings/reservations per day)
(date here is the booking will take place (i.e when the student will use the PC)
(computer status is to show the status of the computer in use or currently not in use)
(the date here is the date the for the booking (i.e date the student will use the PC))
(the slot time to determine when a session start and end ,since this system a session onlystart on top of the hour @ exacty ..O'clock and last an hour)
( the date and time being the date and time the booking was made)
It would help if you showed the functional dependencies rather than assuming people understand what you have in mind from your field and table names. Also, your notation isn't exactly obvious - does # indicate primary key or candidate key? If the former, you have a 2NF violation if StudentNumber --> Name, if the latter some of your other tables like BOOKINGS PER DAY don't make sense.
What do you mean by "complete"? 6NF? If so, no, your tables aren't completely normalized. If you mean 3NF, it possibly still isn't. In STUDENT STATUS, do you have StudentNumber --> StatusID and StatusID --> Status? If so, you have a transitive dependency.
In RESERVATION, you have ComputerID (foreign key), but what does it refer to? COMPUTER's ComputerNumber? If you're going to be neither explicit nor consistent, no-one will know what you're talking about.
thank you for the correction ,its just that am still new in database design ,but i have looked at what you said and came up with the following
For the RESERVATION table , Computer ID was actually supposed to be ComputerNumber...
In the STUDENT STATUS table indeed there is transitive dependency ,please help how can i deal with it.
in the STUDENT table the student number is unique for every student and also the barcode is unique for every student (meaning each is a candidate key),i then thought of making the two the composite key for the relation
and also Barcode-->name
i am not if that works
For the BOOKINGS PER DAY table,it came about as the system has to limit the number of bookings a student can have at most three bookings a day ,thus i decided to keep BookingsPerDay on its own table (the BookingsPerDay to be incremented by one each time a student makes a booking)...with the composite key being (StudentNumber ,date)
am also not sure if this makes sense ,i need help.
The usual way to normalize a transitive dependency is to separate it into two distinct relations, similar to what you did with COMPUTER and COMPUTER STATUS
Having two candidate keys in a relation is not a problem. Just make sure you clearly distinguish the primary key from the candidate keys so that it doesn't look as if your foreign keys are referring to a subset of the primary key.
Is the relationship between STUDENT DETAILS AND STUDENT CONTACT TABLE one-to-one or one-to-many? If one-to-one, you don't necessarily need a separate table in 3NF, though it's not wrong either. If one-to-many, Phone Number, Email and Physical Address are conflated and would be better as distinct relations.
(StudentNumber, Date) is fine as a key for BOOKINGS PER DAY. I was just using as an example to critique the # you used to indicate keys, it was being used inconsistently.