Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2012
    Location
    Zimbabwe
    Posts
    3

    database design for a pc reservation system

    I'm designing a database for a PC reservation system for a High school...i have come up with the following tables,can anyone help with the design and normalisation .Am very new in database design...

    STUDENT DETAILS
    #StudentNumber
    #Barcode
    Name
    Surname

    (the StudentNumber is unique for every student and so is the barcode , i decided to make the two the composite key for the table)
    but StudentNumber-->name
    and also Barcode-->name

    STUDENT CONTACT TABLE
    #StudentNumber(foreign key)
    PhoneNumber
    Email
    Physical Address



    STUDENT STATUS
    #StudentNumber(foreign key)
    #StatusID
    Status
    (student status is to show if student is blocked or allowed to use PC)
    StudentNumber-->StatusID
    StutusID-->Status

    BOOKINGS PER DAY
    #StudentNumber
    #Date
    NumberOfBookingsPerDay

    (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
    #ComputerNumber(primary key)
    ComputerStatusID(foreign key)
    IP address

    COMPUTER STATUS
    #ComputerStatusID(primarykey)
    ComputerStatus

    (computer status is to show the status of the computer in use or currently not in use)

    RESERVATION SLOT
    #SlotID(primary key)
    Date
    StartTime
    EndTime

    (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)


    RESERVATION
    #ReservationID(primary key)
    ComputerNumber(foreign key)
    StudentID(foreign key)
    SlotID(foreign key)
    Date
    Time
    ( the date and time being the date and time the booking was made)
    Last edited by 16124; 03-28-12 at 06:49.

  2. #2
    Join Date
    Feb 2012
    Posts
    76
    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.

  3. #3
    Join Date
    Mar 2012
    Location
    Zimbabwe
    Posts
    3

    thank you reaanb

    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
    but StudentNumber-->name
    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.
    Last edited by 16124; 03-28-12 at 06:37.

  4. #4
    Join Date
    Feb 2012
    Posts
    76
    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.

  5. #5
    Join Date
    Mar 2012
    Location
    Zimbabwe
    Posts
    3

    thanx...

    thanks a lot its becoming clearer and clearer for me,t was a bit misty when is started...
    i have decided to have phone ,email and physical address as distinct relations
    ...thank you

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •