Transcript Unnormalized Form (UNF)

Assignment 5–2
For each new relation,
Use DBDL to specify the relation scheme
Give the functional dependencies
Give the corresponding relation instance
-1 on Assignment5-2
-2 on Assignment6-2
-5 on Test1
1
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
2
Assignment 5–2
2NF
Staff (NIN, eName)
PK: NIN
AK: NONE
FK: NONE
FDs:
NIN ===> eName
Table (Relation) Instance
(Remove duplicate records)
NIN
1135
1057
1068
eName
Smith J
Hocine D
White T
3
Assignment 5–2
2NF
Contract (ContractNo, hNo, hLoc)
PK: ContractNo
AK: NONE
FK: NONE
FDs:
ContractNo ===> hNo, hLoc
hNo ===> hLoc
Table (Relation) Instance
(Remove duplicate records)
ContractNo
C1024
C1025
hNo
H25
H24
hLoc
East Kilbride
Glasgow
4
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
1135
C1024
1057
C1024
1068
C1025
1135
C1025
hours
16
24
28
15
5
Assignment 5–2
Not in 3NF
Contract (ContractNo, hNo, hLoc)
PK: ContractNo
AK: NONE
FK: NONE
FDs:
ContractNo ===> hNo, hLoc
hNo ===> hLoc
How many tables?
Hotel (hNo, hLoc)
Contract (ContractNo, hNo)
6
Assignment 5 – Part II
3NF
Hotel (hNo, hLoc)
PK: hNo
AK: NONE
FK: NONE
FDs:
hNo ===> hLoc
Table (Relation) Instance
hNo
H25
H24
hLoc
East Kilbride
Glasgow
7
Assignment 5 – Part II
3NF
Contract (ContractNo, hNo)
PK: ContractNo
AK: NONE
FK: hNo References Hotel
FDs:
ContractNo ===> hNo
Table (Relation) Instance
ContractNo
C1024
C1025
hNo
H25
H24
8
Assignment 5–2
List the names of all relations of your final result.
(not relationships!)
Staff
Hotel
Contract
Hours
9
Style
Staff (NIN, eName)
PK: NIN
AK: None
FK: None
FD:
NIN ===> eName
Hours (NIN, ContractNo, hours)
PK: NIN, ContractNo
AK: None
FK: NIN References Staff
ContractNo References Contract
FD:
NIN, ContractNo ===> hours
Table Instance
Table Instance
NIN
eName
NIN
ContractNo
hours
1135
Smith J
1135
C1024
16
1057
Hocine D
1057
C1024
24
1068
White T
1068
C1025
28
1135
C1025
15
10
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
1135
C1024
1057
C1024
1068
C1025
1135
C1025
hours
16
24
28
15
11