Skip to main content

Relational Database Architecture: Entity-Relationship Modeling and Schema Design

🎓 SS2 | 📅 Term 1 • Week 5

| ⏱ About 45 minutes

Learning goals: Deconstruct the architecture of Relational Database Management Systems (RDBMS). • Model real-world entities, attributes, and relationships using Entity-Relationship Diagrams (ERD). • Differentiate between Primary Keys, Candidate Keys, Composite Keys, and Foreign Keys. • Establish referential integrity constraints to prevent orphan records and data anomalies.

Lesson menu On this page

In Junior Secondary school, you organized information using basic spreadsheet tables. You learned that tables have rows and columns.

However, in professional enterprise systems—such as the National Identity Management Commission (NIMC), the Joint Admissions and Matriculation Board (JAMB), or commercial banks—data cannot be stored in isolated spreadsheets. Spreadsheets cannot handle simultaneous access by thousands of users, cannot guarantee data consistency, and duplicate information endlessly.

To solve these problems, British computer scientist Edgar F. Codd invented the Relational Database Model in 1970. In this lesson, we study relational database architecture, learn to design Entity-Relationship Diagrams (ERD), and master key constraints.

---

Learning Outcomes

By the end of this lesson, you will be able to:

1. Explain the relational model: Entities, Attributes, Tuples (Rows), and Relations (Tables).
2. Design Entity-Relationship Diagrams (ERD) representing real-world systems.
3. Classify database keys: Primary Keys, Foreign Keys, Candidate Keys, and Composite Keys.
4. Define Referential Integrity and understand how relational constraints protect data validity.

---

Key Vocabulary

Term	Technical Definition
RDBMS	Relational Database Management System; software (e.g., PostgreSQL, MySQL, SQLite) that manages data organized into related tables.
Entity	A distinct real-world object, person, place, or concept about which data is collected (e.g., Student, Course).
Attribute	A specific property or characteristic that describes an entity (e.g., first_name, date_of_birth).
Tuple	A single row or record in a relational database table containing data for one entity instance.
Primary Key (PK)	An attribute (or combination of attributes) that uniquely identifies each tuple in a table without ambiguity.
Foreign Key (FK)	An attribute in one table that links to the Primary Key of another table, defining a relationship.
Referential Integrity	A database constraint requiring every foreign key value to match an existing primary key value in the related table.

---

The Anatomy of a Relational Table

In relational theory, a table is formally called a Relation. - Columns are called Attributes (each has a defined data type: Integer, VARCHAR, Date, Float). - Rows are called Tuples (each represents one unique instance). - The number of columns is the table's Degree. - The number of rows is the table's Cardinality.

Table: Students (Degree = 4, Cardinality = 3)
┌─────────────────┬──────────────┬─────────────┬──────────────┐
│ student_id (PK) │ first_name   │ last_name   │ class_level  │  <-- Attributes (Columns)
├─────────────────┼──────────────┼─────────────┼──────────────┤
│ 101             │ Amina        │ Bello       │ SS 2         │  <-- Tuple 1 (Row)
│ 102             │ Chinedu      │ Okafor      │ SS 2         │  <-- Tuple 2
│ 103             │ Favour       │ Adebayo     │ SS 2         │  <-- Tuple 3
└─────────────────┴──────────────┴─────────────┴──────────────┘

---

Database Keys: The Foundation of Integrity

Without strict identification rules, duplicate records would corrupt databases (e.g., two students named "Ibrahim Musa" in the same class).

1. Primary Key (PK):
   - MUST be unique for every single row.
   - CANNOT contain NULL (empty) values.
   - Example: student_id or National Identification Number (NIN).

2. Foreign Key (FK):
   - Establishes a link to another table.
   - Can contain duplicate values (e.g., many students share the same class_id).
   - Enforces Referential Integrity: You cannot assign a student to class_id 99 if class 99 does not exist!

3. Composite Key:
   - A primary key composed of two or more attributes combined together.
   - Example: In an exam results table, (student_id + subject_code) forms a composite key because a student takes a specific exam only once per term.

---

Entity-Relationship Modeling (ERD)

Before writing any software, database engineers create an Entity-Relationship Diagram (ERD) to model the real world.

Cardinality Types:

1. One-to-One (1:1): Each Nigerian citizen has exactly one National ID card; each card belongs to one citizen.
2. One-to-Many (1:N): One secondary school has many students; each student is enrolled in one school.
3. Many-to-Many (M:N): One student studies many subjects; each subject is taken by many students.

Engineering Note: Relational databases cannot directly link tables in an $M:N$ relationship. They require an Associative Junction Table with two Foreign Keys to split the relationship into two $1:N$ links!

[Students] (1) ───────< (N) [Enrollments] (N) >─────── (1) [Subjects]
(PK: student_id)            (FK: student_id)              (PK: subject_code)
                            (FK: subject_code)
                            (term, exam_score)

---

Practical Activity: Relational Schema Modeling in Python SQLite

Python includes a built-in RDBMS engine: SQLite3. Let us build a relational database schema for a school clinic with two related tables and enforce foreign key constraints.

Step 1: Create school_db_schema.py

import sqlite3

# Connect to an in-memory SQLite database
conn = sqlite3.connect(":memory:")
cursor = conn.cursor()

# Enable Foreign Key enforcement in SQLite
cursor.execute("PRAGMA foreign_keys = ON;")

# Create Parent Table: Departments
cursor.execute("""
CREATE TABLE departments (
    dept_id INTEGER PRIMARY KEY,
    dept_name TEXT NOT NULL UNIQUE,
    location TEXT NOT NULL
);
""")

# Create Child Table: Staff (with Foreign Key pointing to departments)
cursor.execute("""
CREATE TABLE staff (
    staff_id INTEGER PRIMARY KEY,
    full_name TEXT NOT NULL,
    role TEXT NOT NULL,
    dept_id INTEGER NOT NULL,
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
        ON DELETE RESTRICT
);
""")

# Insert valid departments
cursor.execute("INSERT INTO departments VALUES (1, 'Science & Computing', 'Block B');")
cursor.execute("INSERT INTO departments VALUES (2, 'Languages & Arts', 'Block A');")

# Insert valid staff records
cursor.execute("INSERT INTO staff VALUES (101, 'Mr. Emeka Eze', 'Computer Teacher', 1);")
cursor.execute("INSERT INTO staff VALUES (102, 'Mrs. Aisha Yusuf', 'Physics Teacher', 1);")
cursor.execute("INSERT INTO staff VALUES (103, 'Mr. Tunde Lawal', 'English Teacher', 2);")
conn.commit()

print("=== Departments and Staff Inserted Successfully ===")

# Test Referential Integrity: Try to insert staff with NON-EXISTENT dept_id 99!
try:
    print("\nAttempting to insert staff with invalid dept_id 99...")
    cursor.execute("INSERT INTO staff VALUES (104, 'Ghost Employee', 'Intern', 99);")
    conn.commit()
except sqlite3.IntegrityError as err:
    print(f"FOREIGN KEY CONSTRAINT CAUGHT ERROR: {err}")
    print("Database protected itself against corrupted orphan record!")

conn.close()

Step 2: Run the Script

python school_db_schema.py

---

Expected Results and Model Output

When you run school_db_schema.py, you will see:

=== Departments and Staff Inserted Successfully ===

Attempting to insert staff with invalid dept_id 99...
FOREIGN KEY CONSTRAINT CAUGHT ERROR: FOREIGN KEY constraint failed
Database protected itself against corrupted orphan record!

The database engine rejected the invalid insert because department 99 does not exist. This is referential integrity in action!

---

Common Mistakes to Avoid

1. Forgetting that Primary Keys Must Never Be NULL: A primary key column cannot have missing values because the database requires an unambiguous index for every record.
2. Deleting Parent Records with Active Children: If you delete department 1 while teachers are still assigned to it, you create orphan records unless ON DELETE CASCADE or RESTRICT rules are defined.
3. Using Mutable Natural Attributes as Primary Keys: Avoid using email addresses or telephone numbers as Primary Keys because people change them. Use an auto-incrementing numeric surrogate key (e.g., student_id).

---

Notebook Summary

• An RDBMS organizes data into 2D tables (Relations) composed of attributes (columns) and tuples (rows).
• Primary Keys uniquely identify every row. Foreign Keys link child tables to parent tables.
• Referential Integrity guarantees that foreign keys only reference valid, existing primary keys.
• Many-to-Many Relationships ($M:N$) are implemented using an intermediate junction table containing foreign keys to both parent entities.

---

Optional Extension Challenge

Draw an ERD on paper for a Library Lending System with three entities: Books, Members, and Loans. Write down the attributes for each entity and identify the Primary and Foreign Keys in the Loans junction table.

Check your understanding

What is the primary role of a 'Foreign Key' in a relational database table?

• It is a field that references the Primary Key of another table to establish a logical relationship and enforce referential integrity
• It is a field that encrypts passwords for foreign users
• It is a key used exclusively by international database administrators
• It increases the storage capacity of the hard drive

What critical database rule ensures that a foreign key value must always correspond to an existing, valid primary key record in the parent table?

• Referential Integrity
• Entity Redundancy
• Asymmetric Partitioning
• Binary Normalization

Which relationship cardinality best describes the connection between a Nigerian High School and its enrolled Students?

• One-to-Many (1:N) - One school has many students, but each student attends one primary school at a time
• One-to-One (1:1)
• Many-to-Many (M:N) without an associative table
• Zero-to-Zero

Why is storing all school information (students, classes, fee payments, teacher names) in a single massive flat spreadsheet considered poor database engineering?

• It causes massive data redundancy, insertion anomalies, update anomalies, and deletion anomalies
• Spreadsheets cannot store text characters
• Flat files are prohibited by international copyright laws
• Spreadsheets run out of memory after 10 rows

Check answer Try again Feedback appears after you check your answer.