second normal form (2nf) a relation r is in 1nf, and every non-primary-key attribute is fully...

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 the PK.

1

Third Normal Form (3NF)

Relation R in 2NF, and No non-Primary-Key attribute is transitively

functionally dependent on the primary keyThen R is in 3NF.

No Transitive FDs on PK.

2

Assignment 5-2

Due Wednesday2NF, then 3NF

(No BCNF)

3

Boyce-Codd Normal Form (BCNF)

Definition R in 1NF andThe determinant of each FD is a candidate key.

Review: 1NF determinant candidate key

4

BCNF and 3NF

BCNF is stronger than 3NF

If R in BCNF, then R in 3NF.

If R not in 3NF, then R not in BCNF.

5

ProofIf R not in 3NF, then PK ---> B, and B ---> C, (PK ---> C) NO cycle for transitive FD B ---> PK : False B is not candidate key but a determinant (B ---> C )So, R is not in BCNF.

6

ExampleLease (RNo, RName, PNo, PAddress, Start, Finish, Rent, ONo, OName) Primary Key: PNo, Start Alternate Key: PNo, Finish PAddress, Start PAddress, Finish FDs: PNo, Start ---> All other attributes PNo, Finish ---> All other attributes PAddress, Start ---> All other attributes PAddress, Finish ---> All other attributes PNo ---> PAddress, ONo, OName (Pno not a candidate key) PAddress ---> PNo, ONo, Oname (Paddress not a candidate key) RNo ---> Rname (Rno not a candidate key) ONo ---> OName (Ono not a candidate key)

Not in BCNF.How many tables in order to make it BCNF?

7

Decompose Lease into BCNF Lease (RNo, RName, PNo, PAddress, Start, Finish, Rent, ONo, OName) PNo ---> PAddress, ONo, OName (Pno not a candidate key) PAddress ---> PNo, ONo, Oname (Paddress not a candidate key) RNo ---> Rname (Rno not a candidate key) ONo ---> OName (Ono not a candidate key)

Owner (ONo, OName) ONo ---> Oname

Renter (RNo, RName) RNo ---> RName

Lease (RNo, PNo, Start, Finish, Rent) PNo, Start ---> All PNo, Finish ---> All

Property (PNo, PAddress, ONo) PNo ---> PAddress, ONo PAddress ---> PNo, Ono

8

Only 4 tables, not 5.Pno PaddressPaddress Pno

Ono will not be in Lease.

Example

R (A, B, C, D, E, F) PK: A, B, C AK: B, C, D FK: None FDs: A, B, C All B, C, D All B, D A

9

Table Instance

A B C D E F 2 10 x u ct 1 1 20 y v cis 2 2 10 z u se 3 1 20 x v cs 4

FDs: A, B, C All B, C, D All B, D A

10

Decomposing to BCNFR (A, B, C, D, E, F) PK: A, B, C AK: B, C, D FK: None FDs: A, B, C All B, C, D All B, D A

B, D and A should be in a new table with (B, D) as PKB and D should remain in the original table as FKA should not remain in the original tablePK of the original table must be changed to B, C, D.

11

Decomposing to BCNFR (A, B, C, D, E, F) PK: A, B, C AK: B, C, D FK: None FDs: A, B, C All B, C, D All B, D A

12

R2 (B, C, D, E, F) PK: B, C, D AK: NONE FK: B, D References R1 FDs: B, C, D AllDoes R2 have a FK?

R1 (A, B, D) PK: B, D AK: NONE FK: None FDs: B, D ADoes R1 have a FK?

Table Instance A B C D E F 2 10 x u ct 1 1 20 y v cis 2 2 10 z u se 3 1 20 x v cs 4

13

A B D 2 10 u 1 20 v

B C D E F

10 x u ct 1

20 y v cis 2

10 z u se 3

20 x v cs 4

Selecting B, C, D as PK at the Beginning

R (A, B, C, D, E, F) PK: A, B, C AK: B, C, D FK: None FDs: A, B, C All B, C, D All B, D A

14

R (A, B, C, D, E, F) PK: B, C, D AK: A, B, C FK: None FDs: A, B, C All B, C, D All B, D AA is Partial on PK!

Review: Normalization• 1NF Remove multi-value attributes Why: each element can not be a set (first order logic) • 2NF Remove partial FDs on PK Why: remove redundant data • 3NF Remove transitive FDs on PK Why: remove redundant data • BCNF Stronger than 3NF Any candidate keys Why: PK is a good choice

In most cases, BCNF is enough.

15

Lossless Decomposition

After a relation is normalized into two or more relations, the original relations could be obtained by joining new relations

Primary Key and Foreign Key

16

Decompose Lease into BCNF Lease (RNo, RName, PNo, PAddress, Start, Finish, Rent, ONo, OName)

Owner (ONo, OName) ONo ---> OName

Renter (RNo, RName) RNo ---> RName

Property (PNo, PAddress, ONo) PNo ---> PAddress, ONo PAddress ---> PNo, Ono

Lease (RNo, PNo, Start, Finish, Rent) PNo, Start ---> All other attributes PNo, Finish ---> All other attributes

17

How to get Property data for a lease?

Lease Property

How to get Renter data for a lease?

Lease Renter

How to get Owner data for a lease?

Lease Property Owner

De-Normalization

• Normalized relations Minimal redundancy Need join operation to get results • How far should we go? • Where to stop?

18

19

Review: Database DesignA structured approach that uses procedures, techniques, tools, and

documentation aids to support and facilitate the process of design.

Three main phases 1. Conceptual database design Understanding client data E-R (EER) Model Contract between clients and designers

2. Logical database design Mapping E-R Model to (relational) database schema (Derive relational schema from E-R Model) DBDL Normalization

3. Physical database design

Schedule

• WednesdayAssignment5-2

• FridayNo Class

• Monday, March 11Quiz2

• Wednesday, March 20Test1

20

Quiz 2 (25 points)

• Monday, March 11• Lab 206• Using Computers and Drop Files• Derive table schemas from E-R Model (Mapping E-R Model to Database Schema) • DBDL• Functional Dependency• Note11 – Note15• Assignment 4, 5-1 and 6-1

21