DOC PREVIEW
GSU CIS 8040 - 5. Integrity

This preview shows page 1 out of 4 pages.

Save
View full document
View full document
Premium Document
Do you want full access? Go Premium and unlock all 4 pages.
Access to all documents
Download any document
Ad free experience
Premium Document
Do you want full access? Go Premium and unlock all 4 pages.
Access to all documents
Download any document
Ad free experience

Unformatted text preview:

5 - 1 Copyright © 2012 Robinson College of Business, Georgia State University David S. McDonald Director of Emerging Technologies Tel: 404-413-7368; e-mail: [email protected] CIS 8040 – Data Integrity Relational Integrity Outline Why Integrity Rules? The Entity Integrity Rule The Referential Integrity Rule5 - 2 Copyright © 2012 Robinson College of Business, Georgia State University David S. McDonald Director of Emerging Technologies Tel: 404-413-7368; e-mail: [email protected] Why Integrity Rules? A database can be viewed as a data model of an enterprise There are many business rules, regulations, and constraints Data integrity refers to the correctness of the database Data integrity is critical to a DBMS  A DBMS should accept specification of integrity rules  A DBMS should enforce the rules automatically  Different DBMS understand different integrity rules Entity Integrity Rule The primary key of a base relation can not contain any null value.  If the key is composite, none of its component can contain a null value.  This rule is applied to the primary key, not to other alternate keys if any. Enforcement of this rule:  An update which results in null values in the primary key of a base relation must be rejected.  To enforce this rule, the DBMS must understand the primary key concept  Otherwise, this rule can only be enforced by an application program.5 - 3 Copyright © 2012 Robinson College of Business, Georgia State University David S. McDonald Director of Emerging Technologies Tel: 404-413-7368; e-mail: [email protected] Referential Integrity Rule If FK is the foreign key of R2 which matches the primary key PK of R1, then either  Every value of FK must be equal to a value of PK in some tuple of R1, or  It is a null value if FK is not the primary key of its containing relation. Enforcement of this Rule:  An update on either a referenced primary key or on a foreign key must satisfy this rule. Otherwise, it is rejected. Which operation on the primary key may violate this rule? Which operation on the foreign key may violate this rule? Referential Integrity Enforcement Restricted If it is referenced, reject the deletion If its old value is referenced, reject the modification Delete PK Modify PK (includes Insert & Update) Cascades In addition, if it is referenced, delete all tuples that reference to it In addition, If its old value is referenced, change all foreign key references to its new value Nullifies If it is referenced, set all references to null. But, if any FK is a key attribute of containing relation, reject the deletion. If its old value is reference, perform the same action as above. • The first question that needs to be asked is whether or not the foreign key may accept null values? The solution to this question depends if a null value may make sense for a portion of the real world that is being modeled.5 - 4 Copyright © 2012 Robinson College of Business, Georgia State University David S. McDonald Director of Emerging Technologies Tel: 404-413-7368; e-mail: [email protected] A Pseudo-DDL For Integrity Foreign Key (foreign-key IDENTIFIES target NULLS [NOT] ALLOWED DELETE OF target effect UPDATE OF target-primary-key effect) Example: CREATE TABLE SHIPMENT (S# INTEGER, P# INTEGER, QTY INTEGER) PRIMARY KEY (S#,P#), FOREIGN KEY (S# IDENTIFIES SUPPLIER NULLS NOT ALLOWED DELETE OF SUPPLIER RESTRICTED UPDATE OF SUPPLIER.S# CASCADES), FOREIGN KEY (P# IDENTIFIES PART NULLS NOT ALLOWED DELETE OF PART RESTRICTED UPDATE OF PART.P# RESTRICTED)) Self-study Exercise Using the given SUPPLIER and SPJ relations, discuss how to enforce the referential integrity constraint for the following operations according to the three schemes. 1. Delete the 's1' tuple from SUPPLIER. 2. Delete the 's5' tuple from SUPPLIER. 3. Replace 's1' by 's6' in SUPPLIER. 4. Replace 's5' by 's8' in SUPPLIER. 5. Insert a new 's7' tuple into SUPPLIER. 6. Insert a new 's10' tuple into SPJ. 7. Insert a new 's6' tuple into SPJ. 8. Delete the 's2' tuple from SPJ. 9. Replace 's2' by 's5' in SPJ. 10. Replace 's3' by 's11' in


View Full Document

GSU CIS 8040 - 5. Integrity

Download 5. Integrity
Our administrator received your request to download this document. We will send you the file to your email shortly.
Loading Unlocking...
Login

Join to view 5. Integrity and access 3M+ class-specific study document.

or
We will never post anything without your permission.
Don't have an account?
Sign Up

Join to view 5. Integrity 2 2 and access 3M+ class-specific study document.

or

By creating an account you agree to our Privacy Policy and Terms Of Use

Already a member?