Just Lee Data Base Referential Integrity Jason C

Just. Lee Data. Base Referential Integrity Jason C. H. Chen, Ph. D. Professor of MIS School of Business Administration Gonzaga University Spokane, WA 99258 chen@jepson. gonzaga. edu Dr. Chen, Business Database Systems

Q 1: Identify pk and fk CUSTOMERS Customer# Last. Name First. Name Address City State Zip Referred Region Email NUMBER(4) VARCHAR 2(10) VARCHAR 2(20) VARCHAR 2(12) VARCHAR 2(5) NUMBER(4) CHAR(2) VARCHAR 2(30) ORDERS Order# Customer# Order. Date Ship. Street Ship. City Ship. State Ship. Zip Ship. Cost NUMBER(4) DATE VARCHAR 2(18) VARCHAR 2(15) VARCHAR 2(2) VARCHAR 2(5) NUMBER(4, 2) Q 2: Identify the relationship (“cardinality”) between “CUSTOMERS” and “ORDERS” Dr. Chen, Business Database Systems

Customers and Orders Entities with Referential Integrity Dr. Chen, Business Database Systems

Q 1: Identify pk and fk CUSTOMERS pk Customer# Last. Name First. Name Address City State Zip Referred Region Email NUMBER(4) VARCHAR 2(10) VARCHAR 2(20) VARCHAR 2(12) VARCHAR 2(5) NUMBER(4) CHAR(2) VARCHAR 2(30) pk ORDERS fk Order# Customer# Order. Date Ship. Street Ship. City Ship. State Ship. Zip Ship. Cost NUMBER(4) DATE VARCHAR 2(18) VARCHAR 2(15) VARCHAR 2(2) VARCHAR 2(5) NUMBER(4, 2) Q 2: Identify the relationship (“cardinality”) between “CUSTOMERS” and “ORDERS” Dr. Chen, Business Database Systems

Q 1: Can we “create” ORDERS#1 (100) if CUSTOMERS#5 (1005) is not created? ? Why? pk fk pk Dr. Chen, Business Database Systems ORDERS

Q 2: Can we “delete” CUSTOMERS#5 (1005) if ORDERS#1 (100) is still in the database? Why? pk fk pk Dr. Chen, Business Database Systems ORDERS

Referential Integrity pk pk fk ORDERS 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? Dr. Chen, Business Database Systems Yes/No. Why? How?

pk Customers#5 Can we “delete” customers#5 if orders#1 is still in the database? Why? pk orders#1 Can we “create” orders#1 if customers#5 is not created? Why? Dr. Chen, Business Database Systems fk

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

Just. Lee DDL (Original one) Dr. Chen, Business Database Systems See “Partial Solution” on the next slide

Partial Solution Dr. Chen, Business Database Systems

Just. Lee DDL (Original one) Dr. Chen, Business Database Systems

pk CUSTOMERS Customer# Last. Name First. Name Address City State Zip Referred Region Email NUMBER(4) VARCHAR 2(10) VARCHAR 2 (20) VARCHAR 2(12 ) VARCHAR 2(2) VARCHAR 2 (5) NUMBER( 4) CHAR(2) VARCHAR 2(3 0) pk fk ORDERS Order# Customer# Order. Date Ship. Street Ship. City Ship. State Ship. Zip Ship. Cost NUMBER(4) DATE VARCHAR 2(18) VARCHAR 2(15) VARCHAR 2(2) NUMBER(4, 2) CUSTOMERS Customer# (pk). . Dr. Chen, Business Database Systems ORDERS Order# (pk). , ORDERS Order# (pk). .

Using the FOREIGN KEY Constraint Referential 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 pk Customer# orders Order# Dr. Chen, Business Database Systems Last. Name First. Name Address … Region fk Customer# Order. Date … Ship. Zip Ship. Cost

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 pk Customer# orders Order# Dr. Chen, Business Database Systems Last. Name First. Name Address … Region fk Customer# Order. Date … Ship. Zip Ship. Cost

Referential Integrity customers pk pk customer# 1001 … 1005 1020 orders Order# 1000 … 1003 1012 Dr. Chen, Business Database Systems Last. Name MORALES … GIRARD FALAH fk customer# 1005 1001 1007 Referred NULL … Region SE … NW NE Ship. Zip Ship. Cost 98114 … 32328 49002 2. 00 … 4. 00 6. 00

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. Dr. Chen, Business Database Systems

Dr. Chen, Business Database Systems

pk CUSTOMERS Customer# Last. Name First. Name Address City State Zip Referred Region Email NUMBER(4) VARCHAR 2(10) VARCHAR 2 (20) VARCHAR 2(12 ) VARCHAR 2(2) VARCHAR 2 (5) NUMBER( 4) CHAR(2) VARCHAR 2(3 0) pk fk ORDERS Order# Customer# Order. Date Ship. Street Ship. City Ship. State Ship. Zip Ship. Cost NUMBER(4) DATE VARCHAR 2(18) VARCHAR 2(15) VARCHAR 2(2) NUMBER(4, 2) CREATE TABLE Customers (Customer# NUMBER(4), Last. Name VARCHAR 2(10) NOT NULL, First. Name VARCHAR 2(10) NOT NULL, Address VARCHAR 2(20), City VARCHAR 2(12), State VARCHAR 2(2), Zip VARCHAR 2(5), Referred NUMBER(4), Region CHAR(2), Email VARCHAR 2(30), CONSTRAINT customers_customer#_pk PRIMARY KEY(customer#), CONSTRAINT customers_region_ck CHECK (region IN ('N', 'NW', 'NE', 'SE', 'SW', 'E')) ); Dr. Chen, Business Database Systems

pk CUSTOMERS Customer# Last. Name First. Name Address City State Zip Referred Region Email NUMBER(4) VARCHAR 2(10) VARCHAR 2 (20) VARCHAR 2(12 ) VARCHAR 2(2) VARCHAR 2 (5) NUMBER( 4) CHAR(2) VARCHAR 2(3 0) pk fk ORDERS Order# Customer# Order. Date Ship. Street Ship. City Ship. State Ship. Zip Ship. Cost NUMBER(4) DATE VARCHAR 2(18) VARCHAR 2(15) VARCHAR 2(2) NUMBER(4, 2) CREATE TABLE Orders (Order# NUMBER(4), Customer# NUMBER(4), Order. Date DATE NOT NULL, Ship. Date DATE, Ship. Street VARCHAR 2(18), Ship. City VARCHAR 2(15), Ship. State VARCHAR 2(2), Ship. Zip VARCHAR 2(5), Ship. Cost NUMBER(4, 2), CONSTRAINT orders_order#_pk PRIMARY KEY(order#), CONSTRAINT orders_customer#_fk FOREIGN KEY (customer#) REFERENCES customers(customer#)); Dr. Chen, Business Database Systems

pk ORDERS Order# Customer# Order. Date Ship. Street Ship. City Ship. State Ship. Zip Ship. Cost NUMBER(4) DATE VARCHAR 2(18) VARCHAR 2(15) VARCHAR 2(2) NUMBER(4, 2) pk BOOKS ISBN Title Pub. Date Pub. ID Cost Retail Discount Category VARCHAR 2(10) VARCHAR 2(30) DATE NUMBER(2) NUMBER(5, 2) NUMBER(4, 2) VARCHAR 2(12) Cpk, fk Order# NUMBER(4) cpk Item# NUMBER(2) fk ORDERITEMS ISBN VARCHAR 2(10) Quantity NUMBER(3) Paid. Each NUMBER(5, 2) CREATE TABLE ORDERITEMS ( Order# NUMBER(4), Item# NUMBER(2), ISBN VARCHAR 2(10), Quantity NUMBER(3) NOT NULL, Paid. Each NUMBER(5, 2) NOT NULL, CONSTRAINT orderitems_order#_item#_pk PRIMARY KEY (order#, item#), CONSTRAINT orderitems_order#_fk FOREIGN KEY (order#) REFERENCES orders (order#) , CONSTRAINT orderitems_isbn_fk FOREIGN KEY (isbn) REFERENCES books (isbn) , CONSTRAINT oderitems_quantity_ck CHECK (quantity > 0) ); Dr. Chen, Business Database Systems