1 / 20

Assignment 6-1

Assignment 6-1. Friday. Assignment 5–2 Note 15 and 16 Quiz 2. 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

lore
Download Presentation

Assignment 6-1

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Assignment 6-1

  2. Friday Assignment 5–2 Note 15 and 16 Quiz 2

  3. Relational Integrity • Entity Integrity • Referential Integrity • Enterprise Constraints

  4. 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 if BranchNo is not required for all staff

  5. RDBMS Relational DBMS should be able to handle NULL One issue Null = Null? List all staff who have not been assigned to a branch yet BranchNo = Null -- Will not work BranchNo is null - This will work

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

  7. Referential Integrity Foreign key • Must match a primary key in the parent relation, or • Wholly NULL For any table instance!

  8. Example Which branch is SA200 in? Foreign key • Must match a primary key in the parent relation, or • Wholly NULL • SG363 is OK: has not been signed to a branch yet

  9. Example Which room is the first booking for? Foreign key • Must match a primary key in the parent relation, or • Wholly NULL

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

  11. In what order to create the tables? Branch (Bno…) Staff (Sno…Bno) Owner (Ono…) PropertyForRent (Pno…Ono) Renter (Rno…) Viewing (Rno, Pno, ViewDate…) Will it work? YES! Branch (Bno…) Staff (Sno…Bno) PropertyForRent (Pno…Ono) Owner (Ono…) Viewing (Rno, Pno, ViewDate…) Renter (Rno…) Will it work? NO!

  12. In what order to drop the tables? Viewing (Rno, Pno, ViewDate…) Staff (Sno…Bno) PropertyForRent (Pno…Ono) Owner (Ono…) Renter (Rno…) Branch (Bno…) Will it work? YES! Branch (Bno…) Staff (Sno…Bno) Owner (Ono…) PropertyForRent (Pno…Ono) Renter (Rno…) Viewing (Rno, Pno, ViewDate…) Will it work? NO!

  13. Insert Rows The PK must be unique The foreign key value must exist in the parent table if FK is provided FK can be null, meaning not known at the time

  14. Insert Rows 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

  15. Delete Rows No records in other tables reference the record to be deleted.

  16. Delete Rows Delete from Staff: no problem Delete from Branch: Delete branch 'B101' What about staff in 'B101‘?

  17. 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’ Dangerous!

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

  19. Update Record 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

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

More Related