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.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.