Docstoc

Unnormalized Form _UNF_

Document Sample
Unnormalized Form _UNF_ Powered By Docstoc
					   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 key
Then R is in 3NF.


No Transitive FDs on PK.


                                            2
Boyce-Codd Normal Form (BCNF)


                 Definition
 R in 1NF and
 Every determinant (the left side of a FD)
  is a candidate key.



                                             3
       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.




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




                                          5
                          Example
Lease (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?


                                                                     6
  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)                      Renter (RNo, RName)
   ONo ---> Oname                           RNo ---> RName

Property (PNo, PAddress, ONo)
    PNo ---> PAddress, ONo
    PAddress ---> PNo, Ono
                                          Only 4 tables, not 5.
                                          Ono will not be in Lease.
Lease (RNo, PNo, Start, Finish, Rent)
    PNo, Start ---> All
    PNo, Finish ---> All
                                                                      7
             Example
R (A, B, C, D, E)
 PK: A, B, C
 AK: B, C, D
 FK: None
 FDs: A, B, C  All
         B, C, D  All
         B, D  A


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

FDs: A, B, C  All
     B, C, D  All
     B, D  A
                       9
    Decomposing to BCNF
R (A, B, C, D, E)
 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 PK
B and D should remain in the original table as FK
A should not remain in the original table
PK must be changed to B, C, D.


                                                        10
    Decomposing to BCNF
R (A, B, C, D, E)        R1 (A, B, D)
 PK: A, B, C              PK: B, D
 AK: B, C, D              AK: NONE
 FK: None                 FK: None
 FDs: A, B, C  All       FDs: B, D  A
         B, C, D  All
         B, D  A

                          R2 (B, C, D, E)
                           PK: B, C, D
                           AK: NONE
                           FK: B, D References R1
                           FDs: B, C, D  All

                                                    11
            Table Instance
A B    C   D E
                     A B D
2 10   x   u ct
                     2 10 u
1 20   y   v cis
                     1 20 v
2 10   z   u se
1 20   x   v cs
                    B    C D   E
                    10   x u   ct
                    20   y v   cis
                    10   z u   se
                    20   x v   cs
                                     12
 Selecting B, C, D as PK at the Beginning

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

                                              13
    Review: Normalization

•   1NF
    Remove multi-value attributes
    Why: each element is not 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
     Strong requirement
     Any candidate keys

In most cases, 3NF is enough.

                                                         14
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


                                      15
   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

How to get Property data for a lease?
How to get Renter data for a lease?
How to get Owner data for a lease?                                   16
       De-Normalization

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


                                        17
Review: Database Design
A 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

                                                                 18
Assignment 6-1

Due Wednesday

Assignment 5-2

  Due Friday
                 19
               Quiz 2
• Friday, March 4
• 20 points
• Derive table schemas from E-R Model
  (Mapping E-R Model to Database Schema)
• DBDL
• Functional Dependency
• Which Notes?
• Assignment 4, 5-1 and 6-1
                                      20

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:31
posted:1/29/2012
language:English
pages:20