1 / 16

CS 3630 Database Design and Implementation

CS 3630 Database Design and Implementation. Assignment7. Creating tables and inserting records Due Friday, March 29 Table names and column names must be exactly the same as specified. Script File Style. ------------------------------------------------ -- Name : Qi Yang

jaed
Download Presentation

CS 3630 Database Design and Implementation

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. CS 3630 Database Design and Implementation

  2. Assignment7 Creating tables and inserting records Due Friday, March 29 Table names and column names must be exactly the same as specified.

  3. Script File Style ------------------------------------------------ -- Name : Qi Yang -- UserName : YangQ -- Date : 03-14-2019 -- Course : CS 3630 -- Assignment7: Creating tables -- Inserting records ------------------------------------------------ Drop Table test2; Drop Table test1; Create table test1 . . . Desc Test1 Pause Create Table Test2 . . . Desc test2 Pause Insert into test1 . . . Commit; Select * From Test1; . . .

  4. Column Constraints • Primary key Sno Char(4) Primary Key, • Alternate Key SSN Char(9) Unique, • Foreign Key BNo char(4) References Branch, -- when attribute has the same name BNo char(4) References Branch, -- even when FK and PK have different names BNo char(4) References Branch on Delete Cascade, -- Do NOT use “Foreign Key” in column constaints!

  5. Column Constraints • Domain constraint Salary Number Check (Salary > 10000 and Salary < 200000), PType varchar2(6) Check (PType IN ('House', 'Flat', 'Appt')), -- Strings are in single quotes, NOT double quotes Bno Char(4) Default 'B363' References Branch, Rent Float Check (Rent Between 200 and 400), -- between is Inclusive • Required data LName Varchar2(20) Not Null, -- Can be specified only by column constraint

  6. Column Constraints Create table Staff ( SNo char(4) Primary Key, Bno Char(4) Default 'B363' References Branch on Delete Cascade, FName Varchar2(20) Not Null, LName Varchar2(20) Not Null, -- assuming functions DateDiff and Now DOB Date Not Null Check (DateDiff(Year, Now, DOB) >= 16), Salary Number Check (Salary Between 30000 and 100000), SSN Char(9) Unique, Tel_No Char(12));

  7. Table Constraints • Constraints on one or more columns • Composite PK, AK, FK • Cannot use Not Null in table constraints • Cannot use Default in table constraints(?) • Naming the constraints

  8. Table Constraints Create table Staff ( SNo char(4), FName Varchar2(20) Not Null, LName Varchar2(20) Not Null, DOB Date, Salary Number default, BNo Char(4), Tel_No Char(12), SSN Char(11), Constraint PK_Staff Primary Key (SNo), Constraint Range_of_Salary Check (Salary between 30000 and 200000), Unique (SSN), Foreign Key (BNo) References Branch (BNo));

  9. Attribute Order in Foreign Key -- Primary key (c1, c2) for TableA Foreign Key (c1, c2) References TableA, -- Same order as PK Foreign Key (c2, c1) References TableA(c2, c1), -- Different order from PK Foreign Key (c2, c1) References TableA, -- Incorrect!

  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! Referential Integrity!

  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! Referential Integrity!

  13. Enforcing Referential Integrity The foreign key value must exist in the parent table if FK is provided FK can be null, meaning not known at the time Insert Delete Update

  14. ANSI SQL Solutions Five choices to enforce referential integrity • No Action Cannot insert/delete/update • Set to Null • Set to Default • No Check No good • Cascade for delete Delete all staff in 'B101' from Staff table when deleting branch ‘B101’ Dangerous!

  15. Oracle Solutions In Oracle, only two choices are implemented • No Action Cannot insert/delete/update • Cascade for delete 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

  16. Project Phase I Due Wednesday, March 27, by 11 PM

More Related