Print bookPrint book

Week 4

Site: Newgate University Minna - Elearning Platform
Course: Data Management
Book: Week 4
Printed by: Guest user
Date: Sunday, 14 June 2026, 3:51 AM

Description

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.

Table of contents

4.1 The Relational Data Model

Proposed by Edgar F. Codd (IBM) in 1970, the relational model represents data as a collection of TABLES (relations). It is the most widely used data model today and forms the basis of SQL databases.

KEY TERMINOLOGY:

Formal Term

Common Term

Description

Relation

Table

A two-dimensional table with rows and columns

Tuple

Row / Record

A single data instance in a relation (one student, one order, etc.)

Attribute

Column / Field

A named property of the relation (StudentName, Age, etc.)

Domain

Data Type

The set of permissible values for an attribute (e.g., integer 0-100 for Score)

Degree

Number of columns

The number of attributes in a relation

Cardinality

Number of rows

The number of tuples currently in a relation

Schema

Table definition

The structure of the relation: name + list of attributes with domains

 

 

 

 

EXAMPLE: STUDENT Relation (Table):

MatricNo (PK)

StudentName | Department

Level | GPA

23B/UE/BCSX/1001

Habibullah Ladan | Cyber Security

400 | 4.15

23B/UE/BSE/10011

Fatima Abdullahi | Software Engineering

200 | 3.87

23B/UE/BICT/10023

Fatima Musa | Information Technology

300 | 3.52

23B/UE/BNS/2001

Amina Bello | Nursing Science

100 | 4.50

 

Properties of a valid relation: (1) Each cell contains exactly one value (atomic). (2) Each column has a unique name. (3) All values in a column are of the same domain. (4) Each row is unique,  no two tuples are identical. (5) The order of rows and columns does not matter.

4.2 Types of Keys

Key Type

Definition and Example

Superkey

Any set of attributes that uniquely identifies a tuple. E.g., {MatricNo}, {MatricNo, StudentName}, or {MatricNo, StudentName, Email} are all superkeys.

Candidate Key

A minimal superkey, no attribute can be removed. E.g., {MatricNo} and {Email} are both candidate keys for STUDENT (assuming email is unique).

Primary Key (PK)

The chosen candidate key used to identify tuples. Underlined in schema notation. E.g., MatricNo is the PK for STUDENT.

Alternate Key

A candidate key that was not chosen as the primary key. E.g., Email is an alternate key for STUDENT.

Composite Key

A primary key consisting of two or more attributes. E.g., (MatricNo, CourseCode) is the composite PK for ENROLMENT.

Foreign Key (FK)

An attribute (or set) in one table that references the primary key of another table. Creates a link between tables. E.g., DeptCode in STUDENT references DeptCode in DEPARTMENT.

Surrogate Key

An artificial key generated by the system (e.g., auto-increment ID). Used when no natural key exists or when natural keys are complex.

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

4.4 ER to Relational Schema Mapping

Converting an ER diagram to relational tables follows these rules:

ER Concept

Mapping Rule

Strong Entity

Create a table with all simple attributes. Composite attributes → individual columns. Multi-valued attribute → separate table. Derived attributes → usually omitted (computed in queries).

Weak Entity

Create a table with its own attributes + the PK of the owner entity (as FK). Primary key = {owner PK + weak entity's partial key}.

1:1 Relationship

Include the PK of one entity as FK in the other (whichever has total participation is preferred).

1:N Relationship

Include the PK of the '1' side as a FK in the 'N' side table.

M:N Relationship

Create a new junction/bridge table with the PKs of BOTH entities as its composite PK (both are also FKs).

Multi-valued Attribute

Create a separate table with the entity PK + the multi-valued attribute. The PK is {entity PK, attribute value}.

 

MAPPING EXAMPLE — University ER to Relational Schema:

STUDENT (MatricNo, FirstName, LastName, Email, Level, DeptCode[FK→DEPARTMENT])

DEPARTMENT (DeptCode, DeptName, Faculty)

COURSE (CourseCode, CourseName, CreditUnits, DeptCode[FK→DEPARTMENT])

ENROLMENT (MatricNo[FK→STUDENT], CourseCode[FK→COURSE], Grade, Semester) — PK: (MatricNo, CourseCode, Semester)

LECTURER (StaffID, FullName, Rank, DeptCode[FK→DEPARTMENT])

Reading List / References

Codd, E. F. (1970). A Relational Model of Data for Large Shared Data Banks. Communications of the ACM, 13(6), 377-387.

Ramakrishnan, R. & Gehrke, J. (2003). Database Management Systems, Chapters 3 & 19. McGraw-Hill.

Silberschatz et al. (2020). Database System Concepts, Chapter 2: Relational Model. McGraw-Hill.

Activities

Self-Assessment Quiz: 1. What is the difference between a superkey and a candidate key? 2. State the Entity Integrity constraint. Why is it necessary? 3. State the Referential Integrity constraint. What happens if you delete a department that students still reference (ON DELETE RESTRICT)? 4. Map the following 1:N relationship to relational tables: One BANK_BRANCH has many ACCOUNTS. Each ACCOUNT belongs to exactly one BRANCH.