Relational Model in DBMS – Dr. Balvinder Taneja

1. Introduction to the Relational Model

The Relational Model is the most widely used database model, introduced by Dr. E.F. Codd in 1970. It organizes data into tables (relations) consisting of rows (tuples) and columns (attributes).

Key Features of the Relational Model

✅ Data is stored in tables (relations) → Each table represents an entity.
✅ Rows (Tuples) store individual records → Each row is a unique record.
✅ Columns (Attributes) define properties → Each column holds specific data types.
✅ Relationships between tables are managed using keys (Primary Key, Foreign Key).
✅ Uses SQL (Structured Query Language) for data manipulation.

2. Structure of the Relational Model

The Relational Model represents data in tables (relations), with each table having:

Columns (Attributes) → Define the characteristics of the entity.
Rows (Tuples) → Store actual records of data.
Keys (Primary Key, Foreign Key) → Maintain relationships between tables.

Example: Student Database

Student Table (Relation)

Student_ID (PK)	Name	Age	Department_ID (FK)
101	Alice	20	D1
102	Bob	21	D2
103	Charlie	22	D1

Department Table (Relation)

Department_ID (PK)	Department_Name
D1	Computer Science
D2	Electronics

🔹 Primary Key (PK): A unique identifier (e.g., Student_ID in the Student table).
🔹 Foreign Key (FK): A reference to another table (e.g., Department_ID in the Student table refers to the Department_ID in the Department table).

Graph Representation of the Relationship

(Student)
   |   (Department_ID) ---> (Department)

A student is linked to a department using the foreign key.
A department can have multiple students.

3. Concepts in the Relational Model

1. Relation (Table)

A relation is a table in which data is stored.

2. Attribute (Column)

An attribute is a column in a table that represents a property of an entity.

3. Tuple (Row)

A tuple is a row in a table that represents a single record.

4. Domain

A domain is the set of valid values that an attribute can have.
Example: The Age column may have a domain of [18-60].

5. Primary Key (PK)

A Primary Key is a unique identifier for each record in a table.
Example: Student_ID in the Student table.

6. Foreign Key (FK)

A Foreign Key is an attribute that refers to a Primary Key in another table.
Example: Department_ID in the Student table refers to Department_ID in the Department table.

7. Candidate Key

A Candidate Key is a set of attributes that can uniquely identify a tuple.
Example: Student_ID and Email can both be candidate keys in the Student table.

8. Super Key

A Super Key is a set of one or more attributes that uniquely identify a tuple.
Example: {Student_ID, Name} can be a super key because Student_ID is unique.

4. Operations in the Relational Model (Using SQL)

The Relational Model uses SQL (Structured Query Language) for operations like:

1. SELECT (Retrieve Data)

SELECT * FROM Student;

➡ Retrieves all records from the Student table.

2. INSERT (Add Data)

INSERT INTO Student (Student_ID, Name, Age, Department_ID)
VALUES (104, 'David', 22, 'D2');

➡ Adds a new student record.

3. UPDATE (Modify Data)

UPDATE Student
SET Age = 23
WHERE Student_ID = 103;

➡ Updates the age of Student 103.

4. DELETE (Remove Data)

DELETE FROM Student WHERE Student_ID = 102;

➡ Deletes the record of Student 102.

5. Advantages of the Relational Model

✅ 1. Data Integrity & Accuracy

Enforces primary keys and foreign keys to ensure data consistency.

✅ 2. Easy to Query with SQL

SQL makes it easy to insert, update, delete, and retrieve data.

✅ 3. No Data Duplication

Foreign keys reduce redundancy by referencing other tables.

✅ 4. Supports ACID Properties

Ensures Atomicity, Consistency, Isolation, Durability (ACID) for transactions.

✅ 5. Flexibility & Scalability

Tables can be modified without affecting relationships.

6. Disadvantages of the Relational Model

❌ 1. Performance Issues

Complex joins slow down performance for large databases.

❌ 2. High Storage Requirements

Indexes and relationships require more disk space.

❌ 3. Schema Rigidity

Fixed schema makes it difficult to handle unstructured data (like JSON, multimedia).

❌ 4. Complex Relationships Require More Joins

Retrieving related data from multiple tables requires costly JOIN operations.

7. Applications of the Relational Model

1. Banking Systems

Accounts, Customers, Transactions are stored in relational tables.

2. E-Commerce

Products, Customers, Orders, Payments use relational tables.

3. Social Media Platforms

Users, Posts, Comments, Likes, Friends are managed in relational tables.

4. Healthcare Systems

Patients, Doctors, Appointments, Medical Records are stored in relational databases.

5. Universities and Schools

Student records, Courses, Fees, Exams are managed using relational tables.

8. Comparison: Relational vs. Other Models

Feature	Hierarchical Model	Network Model	Relational Model
Structure	Tree (One-to-Many)	Graph (Many-to-Many)	Tables (Rows & Columns)
Relationships	One-to-Many	Many-to-Many	Many-to-Many
Data Access	Navigational	Navigational	SQL Queries
Flexibility	Low	Medium	High
Redundancy	High	Low	Low
Ease of Use	Hard	Complex	Easy

9. Conclusion

The Relational Model is the most popular database model used today due to its simplicity, flexibility, and powerful querying capabilities. It forms the foundation of modern relational databases (RDBMS) like MySQL, PostgreSQL, Oracle, and Microsoft SQL Server.

Summary of the Relational Model

✅ Easy to manage and query using SQL
✅ Supports complex relationships with Primary and Foreign Keys
✅ Ensures Data Integrity & Reduces Redundancy
❌ Not optimized for unstructured data (JSON, multimedia)
❌ Performance issues for very large databases with complex joins