Transcript Unnormalized Form (UNF)

CS 3630
Database Design and Implementation
Second Normal Form (2NF)
A relation R is in 1NF, and
every non-primary-key attribute is fully
functionally dependent on the primary key
Then R is in 2NF.
No Partial FDs on PK.
2
Lease (RNo, RName, PNo, PAddress, Start, Finish, Rent, ONo, OName)
Primary Key: PNo, Start
FDs:
PNo, Start ---> RNo, RName, PAddress, Finish, Rent, ONo, OName
PNo ---> PAddress, ONo, Oname (Partial on PK!)
Lease1 (PNo, PAddress, ONo, OName)
Primary Key: Pno
Alternate Key: None
Foreign Key: None
FDs:
PNo ---> PAddress, ONo, OName
PAddress  Pno
ONo ---> OName
Lease2 (RNo, RName, PNo, Start, Finish, Rent)
Primary Key: PNo, Start
Alternate Key: None
Foreign Key: PNo References Lease1
FDs:
PNo, Start ---> RNo, RName, Finish, Rent
RNo ---> RName
3
Lease1 (PNo, PAddress, ONo, OName)
Primary Key: PNo
FDs:
PNo ---> PAddress, ONo, OName
PAddress  PNo
ONo ---> OName
PNo
P1001
P1002
P1009
P2009
Table Instance
PAddress
ONo
1001 main
O100
2001 main
O109
1009 first
O109
2009 first
O109
OName
Tina
Tony
Tony
Tony
In 2NF
But still some Redundancy
Reason?
4
Third Normal Form (3NF)
Relation R in 2NF, and
No non-Primary-Key attribute is transitively
functionally dependent on the primary key
Then R is in 3NF.
No Transitive FDs on PK.
5
Transitive FDs
If A  B and B  C
Then A  C is always TRUE
(provided A is not functionally dependent on
either B or C)
(NO cycle!)
6
Transitive FDs
A
B
C
If
A  B and B  C
Then A  C
7
Transitive FDs
A
B
C
If
A  B and B  C
Then A C
Provided not B  A (A and B are equivalent)
nor C  A (A, B and C are equivalent)
No Cycle!
8
Lease1 (PNo, PAddress, ONo, OName)
Primary Key: PNo
FDs:
PNo ---> PAddress, ONo, OName
PAddress  PNo
ONo ---> OName
PNo
P1001
P1002
P1009
P2009
Table Instance
PAddress
ONo
1001 main
O100
2001 main
O109
1009 first
O109
2009 first
O109
OName
Tina
Tony
Tony
Tony
In 2NF, still some redundancy
Since not in 3NF!
Transitive Functional Dependency on PK
PNo ---> Ono, ONo ---> OName
PNo ---> Oname
No cycle:
Not Ono ---> Pno and
Not Oname ---> PNo
9
Lease1 (PNo, PAddress, ONo, OName)
Primary Key: PNo
FDs:
PNo ---> PAddress, ONo, OName
PAddress ---> PNo
ONo ---> OName
PNo
P1001
P1002
P1009
P2009
Table Instance
PAddress
ONo
OName
1001 main
O100 Tina
2001 main
O109 Tony
1009 first
O109 Tony
2009 first
O109 Tony
In 2NF, but still some redundancy
Not in 3NF!
PNo ---> ONo, Oname
Ono --> Oname
(Oname is transitively functionally dependent on Pno)
10
Decompose Lease1 into 3NF
Lease1 (PNo, PAddress, ONo, OName)
PNo ---> PAddress, ONo, OName
PAddress ---> PNo
ONo ---> OName
Lease11 (ONo, OName)
Primary Key: Ono
Alternate Keys: None
Foreign Keys: None
FDs:
ONo ---> Oname
Better table name?
Owner
Lease12 (PNo, PAddress, ONo)
Primary Key: PNo
Alternate Keys: PAddress
Foreign Keys: Ono references Lease11
FDs:
PNo ---> PAddress, ONo
PAddress  Pno
Better table name?
Property
11
Table Instances
PNo
P1001
P1002
P1009
P2009
Lease11
ONo
OName
O100 Tina
O109 Tony
Lease1
PAddress
ONo
1001 main
O100
2001 main
O109
1009 first
O109
2009 first
O109
PNo
P1001
P1002
P1009
P2009
OName
Tina
Tony
Tony
Tony
Lease12
PAddress
1001 main
2001 main
1009 first
2009 first
Ono
O100
O109
O109
O109
12
Relation R
R (A, B, C, D, E, F)
Primary Key: A, B
Alternate Keys: None
Functional Dependencies:
A, B ---> C, D, E, F
C ---> D
E ---> F
Is it in 2NF?
Is it in 3NF?
13
Table Instance
A
1
1
2
2
B
x
y
x
y
C
10
20
30
10
D
100
200
100
100
E
se
cis
cis
ct
F
400
1000
1000
400
A, B ---> C, D, E, F
C ---> D
E ---> F
14
Decompose R into 3NF
R (A, B, C, D, E, F)
PK: A, B
AK: None
FK: None
FDs:
A, B ---> C, D, E, F
C ---> D
E ---> F
R1 (C, D)
Primary Key: C
Alternate Keys: None
Foreign Key: None
Functional Dependencies:
C ---> D
R3 (A, B, C, E)
Primary Key: A, B
Alternate Keys: None
Foreign Key: C References R1
E References R2
Functional Dependencies:
A, B ---> C, E
R2 (E, F)
Primary Key: E
Alternate Keys: None
Foreign Key: None
Functional Dependencies:
E ---> F
15
Table Instances
R (A, B, C, D, E, F)
A
B
C
D
1
x
10
100
1
y
20
200
2
x
30
100
2
y
10
100
R1 (C, D)
C
D
10
100
20
200
30
100
E
se
cis
cis
ct
F
400
1000
1000
400
R (A, B, C, E)
A
B
C
1
x
10
1
y
20
2
x
30
2
y
10
E
se
cis
cis
ct
R2 (E, F)
E
F
se
400
cis 1000
ct
400
16
Schedule
Assignment 6-1
Due Monday, March 2
Assignment 5-2
Due Friday, Feb 27
Quiz 2
Friday, March 6
17
Assignment 4
18