Week 4
Teaching and Learning Methods to be Employed
i. Lecture with relational model concepts and worked examples
ii. ER-to-relational mapping exercise
iii. Lab: Creating tables with constraints in MySQL
iv. Quiz on entity and referential integrity
Learning Outcomes / Objectives
By the end of this week, students should be able to:
1. Describe the relational data model using formal terminology.
2. Convert an ER diagram into a relational schema.
3. Define and distinguish between different types of keys.
4. Explain and implement entity integrity and referential integrity constraints.
5. Write SQL DDL statements to create tables with constraints.
4.3 Integrity Constraints
Integrity constraints are rules that ensure the accuracy, validity, and consistency of data in the database. The two most fundamental integrity constraints in the relational model are:
4.3.1 Entity Integrity Constraint
RULE: No attribute that forms part of the PRIMARY KEY of a base relation may accept a NULL value.
RATIONALE: The primary key is used to uniquely identify each tuple. If it were NULL, we could not identify the tuple, defeating the entire purpose of the primary key.
|
Entity Integrity Example: In the STUDENT table, MatricNo is the primary key. The entity integrity constraint ensures that: - You CANNOT insert a student record without a MatricNo - You CANNOT insert TWO students with the same MatricNo - MatricNo can NEVER be NULL SQL Implementation: MatricNo VARCHAR(20) NOT NULL PRIMARY KEY |
4.3.2 Referential Integrity Constraint
RULE: If a foreign key exists in a relation, either the foreign key value must match a primary key value in the referenced relation, or the foreign key value must be NULL.
RATIONALE: Foreign keys create links between tables. Referential integrity ensures these links are always valid, you cannot reference a record that doesn't exist.
|
Referential Integrity Example: STUDENT table has DeptCode as a foreign key referencing DEPARTMENT(DeptCode). The referential integrity constraint ensures: - You CANNOT insert a student with DeptCode='CHEM' if 'CHEM' doesn't exist in DEPARTMENT - You CANNOT delete a department if students still reference it (without cascading rules) SQL: DeptCode VARCHAR(10) REFERENCES DEPARTMENT(DeptCode) ON DELETE RESTRICT |
CASCADE ACTIONS on referential integrity violation:
1. ON DELETE CASCADE — When a referenced row is deleted, delete all referencing rows
2. ON DELETE SET NULL — When a referenced row is deleted, set FK to NULL
3. ON DELETE RESTRICT — Prevent deletion if referenced rows exist (most conservative)
4. ON DELETE SET DEFAULT — Set FK to a default value when referenced row is deleted