The Relational Database Model (E. F.
Codd, 1970)
The relational model revolutionized data management by replacing complex file
and network structures with a logical, table-based approach.
It is founded on set theory and predicate logic, enabling data to be represented,
queried, and manipulated mathematically and consistently.
1. Logical View of Data
• The relational model focuses on how data is logically related, not how it
is physically stored on disk.
• The basic building block is a relation, which is implemented as a table in
a Relational Database Management System (RDBMS).
Structure of a Table (Relation)
• Rows (Tuples): Represent individual records or instances of an entity
(e.g., one student).
• Columns (Attributes): Represent properties or characteristics of that
entity (e.g., Name, GPA, Date of Birth).
• Domain: Each column has an allowed set of values (e.g., GPA must be
between 0.0 and 4.0).
• Order Irrelevance: The sequence of rows and columns does not affect
the data meaning.
Example – STUDENT Table
STU_NUM STU_LNAME STU_FNAME STU_GPA
321452 Kimani Florence 3.85
324291 Sirma Julius 3.70
• Each row represents a student (entity occurrence).
• Each column represents an attribute.
• The primary key (STU_NUM) uniquely identifies each row.
This logical structure makes relational databases simple, flexible, and
independent of the underlying storage structure.
2. Keys
Keys are crucial for maintaining data integrity and defining relationships
between tables.
Types of Keys
• Primary Key (PK):
o Uniquely identifies each record in a table.
o Cannot be NULL.
o Example: STU_NUM in STUDENT table.
• Superkey:
o Any combination of attributes that uniquely identifies rows.
o Example: {STU_NUM}, {STU_NUM, STU_LNAME}.
• Candidate Key:
o A minimal superkey — contains no unnecessary attributes.
o Example: {STU_NUM} alone, if it uniquely identifies students.
• Foreign Key (FK):
o An attribute in one table that refers to the primary key in another
table.
o Establishes relationships between tables.
o Example: CRS_CODE in CLASS table refers to CRS_CODE in
COURSE table.
• Secondary Key:
o Used for data retrieval purposes (not necessarily unique).
o Example: Searching customers by CUS_LNAME or CITY.
Integrity Rules
• Entity Integrity Rule:
o The primary key must have unique and non-null values.
o Ensures every record is distinct.
• Referential Integrity Rule:
o Foreign key values must either:
▪ Match an existing primary key value in the referenced table,
or
▪ Be NULL (if no relationship exists yet).
o Prevents “orphan records.”
3. Relational Set Operators
Relational algebra provides a formal mathematical framework for
manipulating tables.
Each operation takes one or more tables as input and produces a new table as
output.
Operator Description Example
Retrieves rows matching SELECT * FROM
SELECT STUDENT WHERE
specific criteria
(RESTRICT) STU_GPA > 3.5;
(horizontal subset).
Operator Description Example
PROJECT STU_LNAME,
Retrieves specific STU_GPA FROM
PROJECT
columns (vertical subset). STUDENT;
Combines rows from two UNION of CLASS_A
UNION compatible tables, and CLASS_B
removing duplicates.
INTERSECT of
Returns rows common to STUDENT_A and
INTERSECT
both tables. STUDENT_B
Returns rows in one table STUDENT – GRADUATE
DIFFERENCE
but not in another.
PRODUCT Combines every row of
(CARTESIAN one table with every row Useful for join preparation.
PRODUCT) of another.
Combines related tables CUSTOMER JOIN
JOIN based on common AGENT ON
attributes. AGENT_CODE
Finds rows in one table
Useful for “all conditions
DIVIDE related to all rows in
met” queries.
another.
Types of JOINs
• Natural Join: Automatically matches common columns and removes
duplicates.
• Equijoin: Matches columns using equality (=).
• Outer Join (Left/Right): Keeps unmatched rows from one or both
tables, filling with NULLs.
• Theta Join: Uses other comparison operators (<, >, <=, etc.).
4. Data Dictionary and System Catalog
These store metadata — data about the data.
• Data Dictionary:
o Contains names, data types, lengths, default values, and constraints
for every attribute in the database.
o Acts as the database designer’s reference.
• System Catalog:
o A system-maintained database containing information about:
▪ Tables, columns, indexes, views, users, access privileges.
o Enables query optimization and schema validation.
Purpose
• Maintains design consistency.
• Prevents naming conflicts:
o Homonyms: Same name, different meaning (e.g., “Code” used for
both employee and product).
o Synonyms: Different names, same meaning (e.g., “STU_NUM”
and “STUDENT_ID”).
5. Relationships in the Relational Database
Relationships define how tables interact and exchange data.
a. One-to-Many (1:M)
• Most common and ideal relationship.
• Implemented by placing the primary key of the “one” table as a foreign
key in the “many” table.
• Example:
o COURSE (CRS_CODE) → CLASS (CRS_CODE)
o One course can have many classes.
b. One-to-One (1:1)
• Each entity in Table A corresponds to exactly one in Table B.
• Used rarely, often when entities could logically belong to one table.
• Example:
o PROFESSOR ↔ DEPARTMENT (each department has one
chair).
c. Many-to-Many (M:N)
• Not directly supported in relational databases.
• Implemented via a composite or associative table (bridge table).
• Example:
o STUDENT ↔ CLASS becomes:
STUDENT → ENROLL ← CLASS
o ENROLL contains both foreign keys (STU_NUM, CLASS_CODE)
and possibly additional attributes like GRADE.
6. Data Redundancy
Problem:
Uncontrolled redundancy leads to anomalies:
• Update anomaly: Changes in one place not reflected elsewhere.
• Insert anomaly: Inability to add data due to missing related data.
• Delete anomaly: Unintended loss of information when deleting data.
Solution:
• Use foreign keys and proper normalization to reduce redundancy.
• Keep controlled redundancy only where necessary (for performance or
history).
Example:
• In an invoice system, copying product price into LINE table preserves
historical accuracy even if the PRODUCT price changes later.
Key Takeaways
• The relational model focuses on data logic and relationships rather than
storage mechanics.
• Tables (relations) are the foundation — with keys enforcing uniqueness
and integrity rules maintaining consistency.
• Relational algebra provides theoretical and practical tools for querying
and combining data.
• Relationships (1:M, 1:1, M:N) define how entities interact.
• Controlled redundancy ensures both efficiency and accuracy in data
management.