Unnormalized Form (UNF) - PowerPoint

Document Sample
Unnormalized Form (UNF) - PowerPoint Powered By Docstoc
					Assignment 5–2
             Assignment 6–2

                  Normalization
                     BCNF
                    Note 17

For each new relation,
    Use DBDL to specify the relation scheme
    Give the functional dependencies
    Give the corresponding relation instance
  Relational Integrity

• Entity Integrity
• Referential Integrity
• Enterprise Constraints
              Null Value

The value of an attribute could be NULL
    NOT known at the moment or
    NOT Applicable

Example
    Cell Phone number
    BranchNo in Staff table
             Entity Integrity

In a base relation, no attribute of a primary
  (candidate) key can be NULL.

Candidate Key:
    Minimum set of attributes to uniquely
 identify records.
        Referential Integrity

                    Foreign key
• Must match a primary key in the parent relation, or
• Wholly NULL

For any table instance!
                         Example
           Branch                                       Staff
                                              Sno       Phone     Bno
    Bno    Phone     …
                                            SG100               B101
  B101
                                            SG363               Null
  B205
                                            SA200               B000


                    Which branch is SA200 in?

                             Foreign key
• Must match a primary key in the parent relation, or
• Wholly NULL
                                Example
               Room                                        Booking

    Hotelno   RoomNo       …             HotelNo      RoomNo    BookingID    GuestNo

    H101      R001                    Null          R001             001    G100

    H101      R009                    H101          Null             002    G200

    H109      R001                    H101          R001         003        G200




                                       Which room?

                                        Foreign key
•    Must match a primary key in the parent relation, or
•    Wholly NULL
   Database Schema

Branch (Bno…)
Staff (Sno…Bno)
Owner (Ono…)
PropertyForRent (Pno…Ono)
Renter (Rno…)
Viewing (Rno, Pno, ViewDate…)
 In what order to create the tables?

Branch (Bno…)                   Branch (Bno…)
Staff (Sno…Bno)                 Staff (Sno…Bno)
PropertyForRent (Pno…Ono)       Owner (Ono…)
Owner (Ono…)                    PropertyForRent (Pno…Ono)
Viewing (Rno, Pno, ViewDate…)   Renter (Rno…)
Renter (Rno…)                   Viewing (Rno, Pno, ViewDate…)

Will it work?                   Will it work?
   In what order to drop the tables?

Branch (Bno…)                   Viewing (Rno, Pno, ViewDate…)
Staff (Sno…Bno)                 Staff (Sno…Bno)
Owner (Ono…)                    PropertyForRent (Pno…Ono)
PropertyForRent (Pno…Ono)       Owner (Ono…)
Renter (Rno…)                   Renter (Rno…)
Viewing (Rno, Pno, ViewDate…)   Branch (Bno…)

Will it work?                   Will it work?
                        Insert Rows
            Branch                                         Staff
    Bno      Phone      …                           Sno    Phone     Bno
  B101                                             SG100           B101

  B205                                             SG363           Null
                                                   SA200           B123

Insert into Branch: no problem

Insert into Staff:
     Cannot insert a staff with Bno being ‘B123’
     if Branch table has no ‘B123’ in Bno column

    FK can be null, meaning not known at the time
                          Delete Rows
              Branch                       Staff
     Bno      Phone       …         Sno    Phone     Bno
   B101                            SG100           B101

   B205                            SG363           Null
                                   SA200           B101

Delete from Staff: no problem

Delete from Branch:
     Delete branch 'B101'
     What about staff in 'B101‘?
               ANSI SQL Solutions
In ANSI SQL, there are five choices

• No Action
    Cannot delete
• Set to Null
• Set to Default
• No Check
    No good
• Cascade
    Delete all staff in 'B101' from Staff table when deleting
  branch ‘B101’
                 Oracle Solutions
In Oracle, only two choices are implemented

• No Action
    Cannot delete
• Cascade
    Delete all staff in 'B101' from Staff table when deleting
  branch ‘B101’
• Set to Null: not implemented
• Set to Default : not implemented
• No Check : not implemented
                     Update Record
            Branch                                        Staff
    Bno     Phone      …                         Sno     Phone      Bno
  B101                                         SG100              B101

  B205                                         SG363              Null
                                               SA200              B205

Update table Staff
    ‘B101’ of SG100 to ‘B205’
    New value must exist in Branch

Update table Branch
    ‘B101’ to ‘B303’
    Five choices in ANSI SQL, only No Action is implemented in Oracle
    Enterprise Constraints

• Business rules need to be enforced in
  database
• Functional Dependency
• Domain for each attribute
  HotelRoom (HotelNo, RoomNo…Type…)
    Type is a string, but three possible values:
        Family, Double, Single
• Trigger
Assignment 6–2