The Relational Database

Model
 Objectives
• How relational database model takes a logical view of
data
• Understand how the relational model’s basic
components are relations implemented through tables in
a relational DBMS
• How relations are organized in tables composed of rows
(tuples) and columns (attributes)
• Use relational database operators, the data dictionary,
and the system catalog
• How data redundancy is handled in the relational
database model
• Why indexing is important
2
 A Logical View of Data
• Relational Model
• A way to organize data using tables (relations).
• Purpose: Lets programmers focus on what data is needed, not how
it's stored.
Key Features:
• ✅ Logical View of Data
• Programmers interact with tables, not how data is stored physically.
• Example: You query a "Customers" table without needing to know
where it's stored on disk.
• ✅ Tables (Relations)
• Data is stored in rows and columns (like a spreadsheet).
• Each row = a record, each column = a field/attribute.

3
• ✅ Data and Structural Independence
• Changes in storage or structure don't affect how you
access the data.
• Makes the system easier to maintain and update.
• ✅ Conceptually Like a File
• From a user’s perspective, a table feels like a simple file
with rows of data.
• ✅ Easier to Understand
• Compared to older models like hierarchical or network
databases:
– No complex pointers or tree navigation.
– Simpler and more intuitive for developers and users.
4
 Tables and Their
Characteristics
Table
• A two-dimensional structure with rows and
columns.
• Each row = one record (or entity).
• Each column = one attribute (or field).
Entity Set
• A group of related entities (e.g., all students).
• In the relational model, an entity set is
represented as a table.

5
 Tables and Their
Characteristics (continued)
• Table = Relation
• In the relational model, a table is also called a relation.
• This term was introduced by E.F. Codd, who created the relational
model.
Why "Relation"?
• A relation is a mathematical concept similar to a table with rows
and columns.
• Codd used this concept to describe how data should be organized.
Persistent Relation
• A table is a persistent relation – meaning:
– Its data is stored permanently in the database.
– You can reuse and update it later.

6
 Tables and Their
•
Characteristics (continued)
Table = 2D Structure
– Made of rows and columns.
• Each Row = One Entity (Tuple)
– A row shows one record from the entity set.
• Each Column = One Attribute
– Columns have unique names.
• Each Cell = One Value
– Where a row and column meet, there’s one data value.
• Same Data Type in Column
– All values in a column must be of the same type (e.g., all integers).
• Attribute Domain
– Each column has a range of allowed values.
• Row/Column Order Doesn’t Matter
– DBMS doesn’t care about the order of rows or columns.
• Unique Identifier (Primary Key)
– Every table needs one or more attributes to uniquely identify each row.

7
 Keys
1. Determination
• If you know attribute A, and it always gives you
attribute B, then A determines B.
• Example: If you know a student's ID, you can find their
name.
→ StudentID → StudentName
2. Primary Key (PK)
• A primary key is:
– One attribute (or a combination of attributes),
– That uniquely identifies each row in a table.
• No two rows can have the same primary key value.

8
 Keys (continued)
Types of Keys in a Relational Table
1. Composite Key
• A key made up of two or more attributes.
• Used when one attribute alone can’t uniquely identify a
row.
• ✅ Example: {StudentID, CourseID} in a table tracking
course registrations.
2. Key Attribute
• Any attribute that is part of a key (including composite
keys).
– A student can take many courses, and a course can have many
students.
• ✅ In {StudentID, CourseID}, both are key attributes.
9
3. Superkey
• Any combination of attributes that uniquely identifies
a row.
• Can include extra, unnecessary attributes.
• ✅ Example: {StudentID, Email} is a superkey if
StudentID alone is enough.

10
• 🔑 Example:
• Let’s say we have this table:
Student(StudentID, Name, Email)
• If StudentID is already unique for each student, then:
• {StudentID} → is a superkey ✅
• {StudentID, Email} → is also a superkey ✅
(Even though StudentID alone is enough, Email is just
extra)
• So:
• All primary keys are superkeys, but
Not all superkeys are primary keys (because they may
have extra fields).
11
4. Candidate Key
• A superkey with no extra (redundant)
attributes.
• The minimum set of attributes needed to
uniquely identify a row.
• ✅ If StudentID alone is enough, then it’s a
candidate key (but {StudentID, Email} is
not).
12
• 🧠 Example:
• Let’s say we have this table:
Student(StudentID, Name, Email)
• Assume:
• StudentID is unique for every student.
• Email is also unique.
• Then:
• {StudentID} → ✅ Candidate Key
• {Email} → ✅ Candidate Key
• {StudentID, Email} → ❌ Not a candidate key (because it has extra
attributes — both together are more than needed)

13
 Keys (continued)
Nulls (No Data / Empty Field)
• ❌ No value is entered in the field.
• ❌ Not allowed in Primary Key (because PK must be complete
and unique).
• ⚠️Better to avoid nulls in other columns if possible.
What Null Can Mean:
• 🔍 The value is unknown.
• 📦 The value is missing (we know it, but didn’t enter it).
• 🚫 The value is not needed (not applicable).
Problems with Nulls:
• Functions like COUNT, SUM, AVERAGE may give wrong or
incomplete results (they skip nulls).
• Linking tables (JOINs) may not work properly if nulls are
involved.
14
 Keys (continued)
Controlled redundancy:
• Controlled redundancy helps a relational database work
properly.
• Tables in the database share common fields (like IDs) so they
can be linked together.
• If a value appears multiple times in a table, it’s not a problem if
it’s needed to make relationships work.
• Redundancy is only bad when there is unnecessary or repeated
data that is not needed.
• Example
• If StudentID appears 3 times because a student takes 3 courses
→ ✅ That’s controlled redundancy (it’s needed).
• If StudentName is written again and again when StudentID is
enough → ❌ That’s unnecessary redundancy.
15
Keys (continued)

16
Keys (continued)

17
 Keys (continued)
• 🔑 Foreign Key (FK)
• A foreign key is a column (or set of columns) in one table that
matches the primary key in another table.
• It is used to link two tables together.
• Example:
If you have a Students table and a Classes table, the Class_ID in
Students can be a foreign key pointing to the Class_ID in Classes.
• 📏 Referential Integrity
• It means the foreign key must match an existing value in the other
table.
• You cannot enter a value in the foreign key column if it doesn't exist
in the related table.
• Simple Rule:
Every foreign key value must point to a real row in the other table.

18
• 🔍 Secondary Key
• A secondary key is used to search or find
data, but it is not unique.
• It helps in quickly retrieving rows from the
table.
• Example:
If you search students by City or Last
Name, these columns act as secondary
keys.
19
Keys (continued)

20
 Integrity Rules
1. Entity Integrity
• Rule: Primary key cannot be NULL.
• Reason: Every table must have a unique identifier (primary key).
• Example:
In a Students table, each student must have a unique Student_ID.
You can’t leave it empty.
2. Referential Integrity
• Rule: Foreign key must match an existing primary key in the
related table OR be NULL.
• Reason: Ensures relationships between tables stay valid and
consistent.
• Example:
If a student has Class_ID = 5, then Class_ID = 5 must exist in the
Classes table.
21
Integrity Rules (continued)

22
Relational Database Operators
• Relational algebra
– Defines theoretical way of manipulating
table contents using relational operators
– Use of relational algebra operators on
existing tables (relations) produces new
relations

23
 Relational Algebra Operators
(continued)
• UNION
• INTERSECT
• DIFFERENCE
• PRODUCT
• SELECT
• PROJECT
• JOIN
• DIVIDE
24
• 🔗 UNION
Combines all rows from two tables but removes duplicates.
• Requirements:
Both tables must have:
– Same number of columns
– Same data types in each column
• Example:
If Table A has names Ali, Sara
and Table B has Sara, Ahmed,
then:
• A UNION B = Ali, Sara, Ahmed
• 🤝 INTERSECT
Returns only the rows that exist in both tables.
• Example:
If Table A has Ali, Sara
and Table B has Sara, Ahmed,
then:
• A INTERSECT B = Sara
25
Relational Algebra Operators
(continued)

26
Relational Algebra Operators
(continued)

27
Difference
Returns rows that are in one table but not in the other.
• Example:
If Table A has Ali, Sara, Ahmed
and Table B has Sara, Ahmed
then:
• A - B = Ali
• Use: To subtract data from one table using another.
Product (Cartesian Product)
Combines every row from the first table with every row from the second
table.
• Also called: Cartesian Product
• Example:
If Table A has 2 rows and Table B has 3 rows, the result will have 2 × 3 = 6
rows.
• Use: Usually used as a step before filtering with a JOIN.
28
Relational Algebra Operators
(continued)

29
Relational Algebra Operators
(continued)

30
Select
Picks specific rows from a table based on a condition.
• Works on: Rows (horizontal filtering)
• Example:
From a Students table, select only students who live in Lahore.
• SELECT * FROM Students WHERE City = 'Lahore';
• Think of it as: Filtering rows
Project
Picks specific columns (attributes) from a table.
• Works on: Columns (vertical filtering)
• Example:
From a Students table, show only the Name and City.
• SELECT Name, City FROM Students;
• Think of it as: Filtering columns

31
Relational Algebra Operators
(continued)

32
Relational Algebra Operators
(continued)

33
• Join
Combines rows from two or more tables
based on a related column (usually a
common attribute like an ID).
• Why it’s powerful:
It lets you use data from multiple tables
together, even if they are stored
separately.

34
35
36
Relational Algebra Operators
(continued)

37
Natural Join
• A natural join connects two tables automatically using common
column(s) (like ID, Agent_Code, etc.).
• It returns only rows where the values in the common columns match.
• It removes duplicate columns in the final result.
How It Works (3 Steps)
• PRODUCT
– Combines all possible row pairs (Cartesian Product).
• SELECT
– Filters rows where the common column(s) (like Agent_Code) have equal
values.
• PROJECT
– Removes duplicate columns and keeps only one copy of each common
column.

38
39
40
Relational Algebra Operators
(continued)

41
Relational Algebra Operators
(continued)

42
Relational Algebra Operators
(continued)

43
• Natural Join
• A Natural Join only shows rows where
there is a match in both tables based on
a common column.
• It removes unmatched rows.
• It removes duplicate columns from the
result.

44
• Equijoin
Joins two tables using an equality (=) condition
on common columns.
• Result: Keeps duplicate columns from both
tables.
• You must manually write the join condition
(e.g., A.Agent_ID = B.Agent_ID).
• It is the most common type of join.

45
🌐 Outer Join
Returns all matched rows, and also keeps the unmatched rows from one
or both tables.
For unmatched rows, it fills in NULL values where data is missing.
Types of Outer Join
1. Left Outer Join
• Returns:
– All rows from the left table (e.g., CUSTOMER)
– Matching rows from the right table (AGENT)
– If there’s no match, shows NULL for AGENT info
• Example:
• SELECT * FROM CUSTOMER C LEFT JOIN AGENT A ON C.Agent_ID =
A.Agent_ID;
• 🟢 Shows all customers — even if they don’t have an agent.

46
• 2. ✅ Right Outer Join
• Returns:
– All rows from the right table (AGENT)
– Matching rows from the left table (CUSTOMER)
– If there’s no match, shows NULL for CUSTOMER info
• Example:
• SELECT * FROM CUSTOMER C RIGHT JOIN AGENT
A ON C.Agent_ID = A.Agent_ID;
• 🟢 Shows all agents — even if they don’t have any
customers.

47
Relational Algebra Operators
(continued)

48
Relational Algebra Operators
(continued)

49
 Relational Algebra Operators
(continued)
• DIVIDE Operation
The DIVIDE operation gives you the items from one
table that match all the values from another table.
• Tables needed:
– A two-column table – shows a relationship (like which student
takes which subject).
– A one-column table – contains the set of values you
want to check against (like a list of subjects).

50
Relational Algebra Operators
(continued)

51
 Example Scenario
• Tables:
• 1. Enrolled(Student, Course)
This table shows which students are enrolled in which courses.
• StudentCourse Ali Math Ali English Sara Math Sara English Sara Science Usman Math
• 2. RequiredCourses(Course)
This table shows the courses that are required.
• Course Math English
• 🔸 Goal:
• Find the names of students who are enrolled in all required courses (Math and English).
• 🔸 DIVIDE Operation:
• Enrolled ÷ RequiredCourses
• 🔸 Result:
• Student Ali Sara
• ➡ Explanation:
• Ali is enrolled in both Math and English ✅
• Sara is enrolled in Math, English, and more (extra is OK) ✅
• Usman is only in Math ❌ (missing English)

52
 The Data Dictionary and
System Catalog
• Data dictionary
• A data dictionary gives full details about all the
tables in a database.
• It lists each table’s columns (attributes) and their
features (like name, type, size).
• It stores metadata – which means “data about
data.”
• It’s called the designer’s database because it
shows how the database was planned and built .
53
A Sample Data Dictionary

54
 The Data Dictionary and
System Catalog (continued)
• System catalog
• A system catalog stores metadata (data about
data).
• It is a detailed version of the data dictionary that
describes everything in the database (like tables,
views, indexes, users, etc.).
• The terms system catalog and data dictionary
are often used to mean the same thing.
• You can search (query) the system catalog just
like a normal table.
55
 Relationships within the
Relational Database
1:M (One-to-Many) Relationship:
• This is the best and most common type in relational databases.
• Example: One customer can place many orders.
1:1 (One-to-One) Relationship:
• This is rare and should only be used when truly needed.
• Example: One person has one passport.
M:N (Many-to-Many) Relationship:
• This type is not allowed directly in relational databases.
• It must be converted into two 1:M relationships using a bridge
(junction) table.
• Example: Students and courses — use a table like "Enrollment" to
link them.

56
 The 1:M Relationship
• The One-to-Many (1:M) relationship is
the standard and most common type used
in relational databases.
• It is the normal pattern you will find in
almost every database system.
• Example: One teacher can teach many
students.

57
The 1:M Relationship
(continued)

58
The 1:M Relationship
(continued)

59
The 1:M Relationship
(continued)

60
The 1:M Relationship
(continued)

61
 The 1:1 Relationship
• In a 1:1 relationship, one record in an entity
matches with only one record in another, and
vice versa.
• It is uncommon and may mean the data was
not organized well.
• Sometimes, it shows that both entities should
be in the same table.
• But in some special cases, a 1:1 relationship is
necessary — like for security, privacy, or
optional data stored separately.
62
The 1:1 Relationship
(continued)

63
The 1:1 Relationship (continued)

64
 The M:N Relationship
• M:N relationships cannot be used directly in a
relational database.
• They can be split into two One-to-Many (1:M)
relationships.
• This is done by using a composite (or bridge) entity —
a new table that links the two original entities.
• This helps avoid problems and makes the relationship
easier to manage.
• Example: Students and Courses → use an Enrollment
table to connect them.

65
The M:N Relationship
(continued)

66
 The M:N Relationship
(continued)
• A composite entity (also called a linking or bridge table) is used
to convert an M:N relationship into two 1:M relationships.
• This new table must include at least the primary keys from the
two original tables as foreign keys.
• The table will have many rows, each showing a unique link
between the two entities.
• You can also add more attributes to this table if needed (e.g.,
date of enrollment, quantity, grade, etc.).
Example:
• Students ⇄ Enrollment ⇄ Courses
• The Enrollment table will contain: StudentID, CourseID, and
maybe additional data like EnrollmentDate.

67
The M:N Relationship
(continued)

68
The M:N Relationship
(continued)

69
The M:N Relationship
(continued)

70
The M:N Relationship
(continued)

71
 Data Redundancy Revisited
• Data redundancy means the same data is stored in more
than one place.
• This can cause data anomalies (problems like
mismatched or inconsistent data), which can ruin the
database’s accuracy.
• Foreign keys help control redundancy by linking tables
using shared attributes.
• They make sure data stays consistent and accurate
across related tables.
• However, in some cases, a small amount of redundancy
is useful or required for performance or backup reasons.

72
Data Redundancy Revisited
(continued)

73
Data Redundancy Revisited
(continued)

74
 Indexes
• An index is a special structure used to quickly
find rows in a table.
• It works like a roadmap that helps locate data
without scanning the whole table.
• The index key is the value used to look things up
— it points to the exact location of the data.
• A unique index means each key value points to
only one row (no duplicates allowed).
• Each index is made for one specific table only.

75
Indexes (continued)

76
 Codd’s Relational Database
Rules
• In 1985, E.F. Codd (the founder of the relational
model) created a list of 12 rules to define what a
true relational database should follow.
• He made these rules because many companies
were calling their products “relational,” even
when they did not follow basic relational
principles.
• These rules were meant to set a standard and
help users know if a database system was truly
relational.
77
Codd’s Relational Database
Rules (Continued)

78