JustLee DataBase Referential Integrity

1. JustLee DataBaseReferential Integrity Jason C. H. Chen, Ph.D. Professor of MIS School of Business Administration Gonzaga University Spokane, WA 99258 chen@jepson.gonzaga.edu

2. Q1: Identify pk and fk CUSTOMERS ORDERS Q2: Identify the relationship (“cardinality”) between “CUSTOMERS” and “ORDERS”

3. Customers and Orders Entities with Referential Integrity

4. Q1: Identify pk and fk CUSTOMERS pk fk pk ORDERS Q2: Identify the relationship (“cardinality”) between “CUSTOMERS” and “ORDERS”

5. Q1: Can we “create” ORDERS#1 (100) if CUSTOMERS#5 (1005) is not created? ? Why? pk fk ORDERS pk

6. Q2: Can we “delete” CUSTOMERS#5 (1005) if ORDERS#1 (100) is still in the database? Why? pk fk ORDERS pk

7. Referential Integrity pk fk ORDERS pk Order of Entering Data: CUSTOMERS  ORDERS The table with pk (e.g., customers) should be created first before the table with fk (orders) Order of Deleting Data: ORDERS  CUSTOMERS The table with fk (orders) should be deleted first before the table with pk (customers) Do we have (or even want) to manually enforce the data (referential) integrity? Yes/No. Why? How?

8. pk Customers#5 Can we “delete” customers#5 if orders#1 is still in the database? Why? pk fk orders#1 Can we “create” orders#1 if customers#5 is not created? Why?

9. Assignment on JLDB for Referential Integrity • 1) Identify pk, fk etc. • 2) Identify “Order of Entering Data” • 3) Identify “Order of Deleting Data”

10. See “Partial Solution” on the next slide JustLee DDL (Original one)

11. Partial Solution

12. JustLee DDL (Original one)

13. pk CUSTOMERS CUSTOMERS Customer# (pk) . . ORDERS Order# (pk) . , pk fk ORDERS CUSTOMERS Customer# (pk) . . ORDERS Order# (pk) . .

14. Using the FOREIGN KEY ConstraintReferential Integrity • Requires a value to exist in the referenced column of another table • NULL values are allowed • Enforces referential integrity • Maps to the PRIMARY KEY in parent table customers pk orders fk pk

15. Using the FOREIGN KEY Constraint Referential Integrity (cont.) • You cannot delete a value in a parent table (pk) referenced by a row in a child table (fk) customers pk orders fk pk

16. Referential Integrity customers pk orders fk pk

17. Referential Integrity • The table with pk (e.g., customers) should be created first before the table with fk (orders) • The table with fk (orders) should be deleted first before the table with pk (customers) • How to maintain the “Referential Integrity” in an efficient way? • Answer: include “constraints” in the DDL.

19. pk CUSTOMERS pk fk ORDERS CREATE TABLE Customers (Customer# NUMBER(4), LastName VARCHAR2(10) NOT NULL, FirstName VARCHAR2(10) NOT NULL, Address VARCHAR2(20), City VARCHAR2(12), State VARCHAR2(2), Zip VARCHAR2(5), Referred NUMBER(4), Region CHAR(2), Email VARCHAR2(30), CONSTRAINT customers_customer#_pk PRIMARY KEY(customer#), CONSTRAINT customers_region_ck CHECK (region IN ('N', 'NW', 'NE', 'S', 'SE', 'SW', 'W', 'E')) );

20. pk CUSTOMERS pk ORDERS fk CREATE TABLE Orders (Order# NUMBER(4), Customer# NUMBER(4), OrderDate DATE NOT NULL, ShipDate DATE, ShipStreet VARCHAR2(18), ShipCity VARCHAR2(15), ShipState VARCHAR2(2), ShipZip VARCHAR2(5), ShipCost NUMBER(4,2), CONSTRAINT orders_order#_pkPRIMARY KEY(order#), CONSTRAINT orders_customer#_fkFOREIGN KEY (customer#) REFERENCES customers(customer#));

21. pk ORDERS pk BOOKS Cpk, fk cpk fk ORDERITEMS CREATE TABLE ORDERITEMS ( Order# NUMBER(4), Item# NUMBER(2), ISBN VARCHAR2(10), Quantity NUMBER(3) NOT NULL, PaidEach NUMBER(5,2) NOT NULL, CONSTRAINT orderitems_order#_item#_pkPRIMARY KEY (order#, item#), CONSTRAINT orderitems_order#_fkFOREIGN KEY (order#) REFERENCES orders (order#) , CONSTRAINT orderitems_isbn_fkFOREIGN KEY (isbn) REFERENCES books (isbn) , CONSTRAINT oderitems_quantity_ckCHECK (quantity > 0) );