CS 3630 Database Design and Implementation Assignment 5

CS 3630 Database Design and Implementation

Assignment 5 -1 Assumptions • NIN, h. No, and Contract. No are unique for Staff, Hotel, and Contract, respectively. • h. Loc is the city where a hotel is located. • Several hotels may be in the same city. • One hotel could have several contracts. • One contract is for one hotel. • Several staff may work on one contract. • One staff may work on several contracts on the same hotel or different hotels. • hours is the total hours of one staff on one contract. • More than one employee could have the same name. 2

Assumptions 1. Identify all non-trivial FDs NIN e. Name contract. No h. No, h. Loc h. No h. Loc NIN, contract. No All ( NIN, contract. No hours, e. Name, h. No, h. Loc) 2. Choose a PK: NIN, contract. No 3. List all AKs: NONE

Schedule Assignment 6 -1 Due Friday, March 1 Assignment 5 -2 Due Tuesday, March 5 Quiz 2 Wednesday, March 6 Lab 206 4

Second Normal Form (2 NF) A relation R is in 1 NF, and every non-primary-key attribute is fully functionally dependent on the primary key Then R is in 2 NF. No Partial FDs on PK. 5

Lease (RNo, RName, PNo, PAddress, Start, Finish, Rent, ONo, OName) Primary Key: PNo, Start FDs: PNo, Start All PNo PAddress, ONo, Oname (Partial on PK!) Lease 1 (PNo, PAddress, ONo, OName) Primary Key: Pno Alternate Key: PAddress Foreign Key: None FDs: PNo All PAddress All ONo OName Lease (RNo, RName, PNo, Start, Finish, Rent) Primary Key: PNo, Start Alternate Key: None Foreign Key: PNo References Lease 1 FDs: PNo, Start All RNo RName 6

Lease (RNo, RName, PNo, PAddress, Start, Finish, Rent, ONo, OName) Primary Key: PNo, Start Better Name for Lease 1? Lease 1 (PNo, PAddress, ONo, OName) Primary Key: Pno Property Lease (RNo, RName, PNo, Start, Finish, Rent) Primary Key: PNo, Start 7

A 52 Lease (RNo, RName, PNo, PAddress, Start, Finish, Rent, ONo, OName) PK: PNo, Start AK: PNo, Finish PAddress, Start PAddress, Finish FDs: PNo, Start All PNo, Finish All PAddress, Start All PAddress, Finish All RNo RName ONo Oname PAddress PNo, OName PNo PAddress, ONo, OName 8

A 52 RNo R 101 R 102 R 103 R 101 R 105 RName John Mike Kay John Scot PNo P 1001 P 1002 P 1009 PAddress 1001 main 2001 main 1009 first Start 1 -1 -04 1 -1 -05 3 -1 -05 1 -1 -04 Finish 12 -31 -04 2 -28 -05 12 -31 -05 5 -31 -04 Rent 350 380 550 350 Ono O 100 O 110 O 109 OName Tina Tony Table Instance Redundancy 9

A 51 Step 1 Property (PNo, PAddress, ONo, OName) PK: Pno AK: PAddress FK: None FDs: PNo All PAddress All ONo OName Lease (RNo, RName, PNo, Start, Finish, Rent) PK: PNo, Start AK: Pno, Finish FK: PNo References Property FDs: PNo, Start All RNo RName 10

A 51 Step 1: Table Instances! P 1001 P 1002 P 1009 RNo R 101 R 102 R 103 R 101 R 105 RName John Mike Kay John Scot Property 1001 main O 100 2001 main O 110 1009 first O 109 PNo P 1001 P 1002 P 1009 Lease Start 1 -1 -04 1 -1 -05 3 -1 -05 1 -1 -04 Tina Tony Finish 12 -31 -04 2 -28 -05 12 -31 -05 5 -31 -04 Rent 350 380 550 350 Reduced Redundancy! 11

Property (PNo, PAddress, ONo, OName) PK: Pno AK: PAddress FDs: PNo All PAddress All ONo OName PNo P 1001 P 1002 P 1009 P 2009 Table Instance PAddress ONo 1001 main O 100 2001 main O 109 1009 first O 109 2009 first O 109 OName Tina Tony In 2 NF But still some Redundancy Reason? 12

Third Normal Form (3 NF) Relation R in 2 NF, and No non-Primary-Key attribute is transitively functionally dependent on the primary key Then R is in 3 NF. No Transitive FDs on PK. 13

Transitive FDs If A B and B C Then A C is always TRUE C is transitively functionally dependent on A If A is not functionally dependent on either B or C. NO cycle! 14

Always True A B C If A B and B C Then A C 15

Transitive FDs A B C If A B and B C Then A C is a transitive FD If B A is false (A and B are not equivalent) And C A is false (A, B and C are not equivalent) No Cycle! 16

Property (PNo, PAddress, ONo, OName) PK: Pno AK: PAddress FDs: PNo All PAddress All ONo Oname PNo P 1001 P 1002 P 1009 P 2009 Table Instance PAddress ONo 1001 main O 100 2001 main O 109 1009 first O 109 2009 first O 109 OName Tina Tony In 2 NF, but still some redundancy Not in 3 NF! PNo Oname is transitive PNo ONo and ONo Oname Both ONo Pno and Oname Pno are false 17

Decompose Property into 3 NF Property (PNo, PAddress, ONo, OName) PNo All PAddress All ONo Oname New table Based on which FD? Property 1 (ONo, OName) Primary Key: Ono Alternate Keys: None Foreign Keys: ? FDs: ONo Oname Property (PNo, PAddress, ONo) Primary Key: PNo Alternate Keys: PAddress Foreign Keys: ? FDs: PNo All PAddress All 18

Decompose Property into 3 NF Property (PNo, PAddress, ONo, OName) PNo All PAddress All ONo OName Property 1 (ONo, OName) Primary Key: Ono Alternate Keys: None Foreign Keys: None FDs: ONo Oname Property (PNo, PAddress, ONo) Primary Key: PNo Alternate Keys: PAddress Foreign Keys: Ono references Property 1 FDs: PNo All PAddress All Better name for Property 1? Owner 19

Decompose Property into 3 NF A 52 Step 2 Property (PNo, PAddress, ONo, OName) PNo All PAddress All ONo OName Owner (ONo, OName) Primary Key: Ono Alternate Keys: None Foreign Keys: None FDs: ONo Oname Property (PNo, PAddress, ONo) Primary Key: PNo Alternate Keys: PAddress Foreign Keys: Ono references Owner FDs: PNo All PAddress All 20

Table Instances (A 52 Step 2) PNo P 1001 P 1002 P 1009 P 2009 Owner ONo OName O 100 Tina O 109 Tony Property PAddress ONo 1001 main O 100 2001 main O 109 1009 first O 109 2009 first O 109 OName Tina Tony PNo P 1001 P 1002 P 1009 P 2009 Property PAddress 1001 main 2001 main 1009 first 2009 first Ono O 100 O 109 21

Final Table Schemas (A 52 Step 3) Lease (RNo, RName, PNo, Start, Finish, Rent) PK: PNo, Start AK: None FK: Pno references Property (PNo, PAddress, Ono) PK: PNo AK: PAddress FK: Ono references Owner (ONo, Oname) PK: ONo AK: None FK: None 22

Relation R R (A, B, C, D, E, F) Primary Key: A, B Alternate Keys: None Functional Dependencies: A, B All C D E F Is it in 2 NF? Is it in 3 NF? How many tables we will have to decompose it into 3 NF? 23

R (A, B, C, D, E, F) PK: A, B AK: None FK: None Exercise Decompose R into 3 NF Each table: DBDL FDs Table instance FDs: A, B All C D E F A 1 1 2 2 Table Instance B C D x 10 100 y 20 200 x 30 100 y 10 100 E se cis ct F 400 1000 400 24