1 / 11

Assignment 5–2

Assignment 5–2. Normalization 2NF and 3NF Note 15, 16 For each new relation, Use DBDL to specify the relation scheme Give the functional dependencies Give the corresponding relation instance. Assignment 5–2. hours (NIN, ContractNo, eName, hours, hNo, hLoc)

avent
Download Presentation

Assignment 5–2

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Assignment 5–2 Normalization 2NF and 3NF Note 15, 16 For each new relation, Use DBDL to specify the relation scheme Give the functional dependencies Give the corresponding relation instance

  2. Assignment 5–2 hours (NIN, ContractNo, eName, hours, hNo, hLoc) NIN ===> eName hNo ===> hLoc ContractNo ===> hNo, hLoc NIN, ContractNo ===> eName, hNo, hLoc, hours PK: NIN, ContractNo Not in 2NF NIN ===> eName ContractNo ===> hNo, hLoc How many tables (relations)? Staff: NIN ===> eName Contract: ContractNo ===> hNo, hLoc hours: NIN, ContractNo ===> hours

  3. Assignment 5–2 2NF Staff (NIN, eName) PK: NIN AK: NONE FK: NONE FDs: NIN ===> eName Table (Relation) Instance (Remove duplicate records) NIN eName 1135 Smith J 1057 Hocine D 1068 White T

  4. Assignment 5–2 2NF Contract (ContractNo, hNo, hLoc) PK: ContractNo AK: NONE FK: NONE FDs: hNo ===> hLoc ContractNo ===> hNo, hLoc Table (Relation) Instance (Remove duplicate records) ContractNo hNo hLoc C1024 H25 East Kilbride C1025 H24 Glasgow

  5. Assignment 5–2 2NF hours (NIN, ContractNo, hours) PK: ContractNo, NIN AK: NONE FK: NIN References Staff ContractNo References Contract FDs: NIN, ContractNo ===> hours Table (Relation) Instance NIN ContractNo hours 1135 C1024 16 1057 C1024 24 1068 C1025 28 1135 C1025 15

  6. Assignment 5–2 Not in 3NF Contract (ContractNo, hNo, hLoc) PK: ContractNo AK: NONE FK: NONE FDs: hNo ===> hLoc ContractNo ===> hNo, hLoc How many tables? Hotel (hNo, hLoc) Contract (ContractNo, hNo)

  7. Assignment 5 – Part II 3NF Hotel (hNo, hLoc) PK: hNo AK: NONE FK: NONE FDs: hNo ===> hLoc Table (Relation) Instance hNo hLoc H25 East Kilbride H24 Glasgow

  8. Assignment 5 – Part II 3NF Contract (ContractNo, hNo) PK: ContractNo AK: NONE FK: hNo References Hotel FDs: ContractNo ===> hNo Table (Relation) Instance ContractNo hNo C1024 H25 C1025 H24

  9. Assignment 5–2 List the names of all relations of your final result. (not relationships!) Staff Hotel Contract hours

  10. Style Staff (NIN, eName) PK: NIN AK: None FK: None FD: NIN ===> eName Table Instance hours (NIN, ContractNo, hours) PK: NIN, ContractNo AK: None FK: NIN References Staff ContractNo References Contract FD: NIN, ContractNo ===> hours Table Instance

  11. Style Staff (NIN, eName) PK: NIN AK: None FK: None FD: NIN ===> eName Table Instance NIN eName 1135 Smith J 1057 Hocine D 1068 White T hours (NIN, ContractNo, hours) PK: NIN, ContractNo AK: None FK: NIN References Staff ContractNo References Contract FD: NIN, ContractNo ===> hours Table Instance NIN ContractNo hours 1135 C1024 16 1057 C1024 24 1068 C1025 28 1135 C1025 15

More Related