The relational model is a type of model, which represents how the data are stored in a relational database.
In a relational database, data is stored in the form of tables (relations). Tables are a combination of rows and columns. In a table, columns represent an attribute of an entity, and rows represent records.
The relational model is proposed by E.F. Codd in 1969.
In the whole world, the relational model demand of the relational model is very high. Because this is not a type of complex model as before and programmers can easily work on it. It also fulfills all the needs of data processing and storage.
What is Relation Model?
The Relational Model is a database model that organizes data into tables (relations) consisting of rows and columns. Each table represents an entity, rows represent records (tuples), and columns represent attributes.
It is based on the concept of storing data in structured form and establishing relationships between tables using keys.
For example, a Student table may contain columns like Student_ID, Name, and Course_ID, where each row represents a different student.
For example, a table named STUDENT is given:
| ID | NAME | COURSE | ADDRESS |
| 1. | ABHISHEK | B.TECH | BIHAR |
| 2. | NISHANT | B.TECH | BIHAR |
| 3. | PRAKASH | B.PHARMA | UP |
| 4. | ALOK | MBBS |
Diagram of Relational Model
Figure: Relational Model
Explanation of the Relational Model Diagram
- The diagram represents the Relational Model, where data is stored in the form of tables (relations).
- There are three main tables: Student, Enrollment, and Course.
- The Student table contains details of students such as Student_ID, Name, and Age.
- Student_ID acts as the Primary Key, which uniquely identifies each student.
- The Course table contains course information such as Course_ID, Course_Name, and Credits.
- Course_ID is the Primary Key of the Course table.
- The Enrollment table connects the Student and Course tables.
- It contains Enroll_ID, Student_ID (FK), and Course_ID (FK).
- Student_ID and Course_ID act as Foreign Keys, linking to their respective tables.
- The Primary Key (PK) ensures that each record in a table is unique.
- The Foreign Key (FK) creates relationships between tables by referencing primary keys of other tables.
- The diagram shows a many-to-many relationship:
- One student can enroll in multiple courses.
- One course can have multiple students.
- Arrows in the diagram indicate how the Enrollment table is connected to both Student and Course tables.
- Overall, the diagram shows how data is organized, related, and managed efficiently using tables, keys, and relationships in the relational model.
Important terms of relational model
1. Attribute
An attribute is the property of a table which defines the relation or table.
For example: in the above table STUDENT, ID, NAME, COURSE, AND ADDRESS are the attributes of the table.
2. Tuple
Each row of a table is known as a tuple. In the above table STUDENT, there are four tuples. One tuple is shown below.
| 2. | NISHANT | B.TECH | BIHAR |
3. Degree
Total no of attributes in a table is known as the degree of that table. The degree of above STUDENT is 4. Because there are four attributes present in the table.
4. Relational schema
It represents attributes with the name of their table.
For example, STUDENT (ID, NAME, COURSE, ADDRESS) is a relation schema for the table STUDENT.
5. Cardinality
Total no of tuples presents in the table is the cardinality of that table. For example, the cardinality of the above table STUDENT is 4. Because total tuple or row in this table is 4.
6. Null values
The value which is not available in the table is known as a NULL value. For example: for id 4 in the above STUDENT table address value is not available. So here address value is NULL.
Relational model Constraints
There are some conditions are always present for each relation. These conditions are called relational integrity constraints.
Basically, there are three types of constraints of the relational model:
- Key constraints
- Domain constraints
- Referral integrity constraints
1. Key Constraints
Key constraints are used to identify each record in a table uniquely. The most common types are:
- Primary Key Constraint: Ensures that a column or a group of columns have unique values across the table. No part of a primary key can be null. This uniqueness ensures that every record can be uniquely identified.
- Foreign Key Constraint: Used to establish a link between two tables. It relates the foreign key in one table to the primary key in another, ensuring the consistency of data across tables.
Key constraints maintain the uniqueness and integrity of data within and across tables, helping enforce relationships between tables.
2. Domain Constraints
Domain constraints specify the permissible values for a given attribute. They define the data type (such as integer, decimal, character, date), range of values, default value, and whether the value can be null. For instance, a domain constraint can limit an “age” field to only integer values between 1 and 100, ensuring that only valid ages are entered into the database.
These constraints are crucial for data accuracy and consistency, ensuring that attributes in a database table adhere to specified rules, such as allowable values and data types.
3. Referential Integrity Constraints
Referential integrity constraints ensure that relationships between tables remain consistent. Specifically, they make sure that a foreign key field in one table correctly refers to a primary key field in another table. This constraint is key to maintaining the accuracy and consistency of data across related tables.
For example, in a database with “students” and “enrollments” tables, a referential integrity constraint would ensure that every record in the “enrollments” table points to an existing record in the “students” table. This prevents situations where an enrollment could exist without a corresponding student, thereby maintaining the logical integrity of the database.
These three types of constraints are fundamental to relational database design, each serving to ensure the quality, integrity, and reliability of the data within the database system.
Objectives of Relational Model
1. Simple Data Representation
The relational model aims to represent data in a simple and structured format using tables (relations), making it easy to understand and use.
2. Data Independence
It ensures that changes in physical storage do not affect the logical structure of the database, providing flexibility.
3. Data Integrity
The model enforces rules such as primary key and foreign key constraints to maintain accuracy and consistency of data.
4. Reduction of Data Redundancy
It minimizes duplication of data through proper design techniques like normalization.
5. Easy Data Retrieval
It supports powerful query languages like SQL, making it easy to retrieve and manipulate data efficiently.
6. Flexibility in Data Handling
The relational model allows easy insertion, deletion, and updating of data without affecting the entire database structure.
7. Security and Control
It provides mechanisms to control access to data, ensuring data security and authorized usage.
Characteristics of Relational Model
1. Data Organized in Tables (Relations)
Data is stored in the form of tables, where each table represents a relation consisting of rows and columns.
2. Rows as Tuples
Each row in a table represents a record, also called a tuple, which contains complete information about a single entity.
3. Columns as Attributes
Each column represents an attribute that defines a specific property of the data, such as Name, Age, or ID.
4. Unique Identification (Primary Key)
Each table has a primary key that uniquely identifies each record, ensuring no duplicate rows.
5. Use of Foreign Keys
Foreign keys are used to establish relationships between different tables, maintaining data consistency.
6. Data Integrity Constraints
The model enforces rules like entity integrity and referential integrity to ensure accurate and valid data.
7. No Duplicate Tuples
Duplicate records are not allowed in a relation, ensuring uniqueness of data.
8. Order Independence
The order of rows and columns does not affect the data or its meaning in a table.
9. Structured Query Language (SQL) Support
It supports SQL for easy data manipulation and retrieval.
Functions of Relational Model
1. Data Storage
The relational model stores data in the form of tables (relations), where each table represents a specific entity. Data is organized into rows (tuples) and columns (attributes), making it easy to store large amounts of structured information in a clear and systematic way.
2. Data Retrieval
It allows users to retrieve required data using query languages like SQL. Users can filter, sort, and combine data from multiple tables using operations such as SELECT, JOIN, and WHERE, making data access flexible and efficient.
3. Data Manipulation
The model supports operations like INSERT, UPDATE, and DELETE to manage data. Users can add new records, modify existing ones, or remove unwanted data without affecting the entire database.
4. Establishing Relationships
It establishes connections between different tables using primary keys and foreign keys. This helps in linking related data, such as connecting a Student table with a Course table, ensuring meaningful data relationships.
5. Ensuring Data Integrity
The relational model enforces rules to maintain data accuracy and consistency. Constraints like primary key (no duplicate values) and foreign key (valid references) ensure that the data remains reliable.
6. Data Security
It provides mechanisms to control access to data. Users can be given different levels of permissions (read, write, update), ensuring that only authorized users can access or modify the data.
7. Reducing Data Redundancy
Through normalization, the relational model reduces duplication of data. This saves storage space and avoids inconsistencies that may arise from repeated data.
8. Supporting Data Independence
The relational model separates logical structure from physical storage. This means changes in how data is stored do not affect how users interact with the database, making the system flexible and easy to maintain.
Advantages of Relational Model
1. Simple and Easy to Understand
The relational model uses tables (rows and columns), which makes data easy to understand, design, and use even for non-technical users.
2. Data Independence
It provides logical and physical data independence, meaning changes in storage or structure do not significantly affect the overall system.
3. Reduced Data Redundancy
Through normalization, it minimizes duplication of data, saving storage space and improving consistency.
4. Data Integrity and Accuracy
Constraints like primary key, foreign key, and unique ensure that data remains accurate and consistent.
5. Flexible Data Retrieval
Powerful query languages like SQL allow users to easily retrieve, filter, and manipulate data.
6. Easy Maintenance
Updating, modifying, and managing the database is simpler due to its structured format.
7. Security
It provides access control mechanisms to protect data from unauthorized access.
8. Scalability
The relational model can handle large amounts of data and can be expanded as needed without major redesign.
Disadvantages of Relational Model
1. Complexity in Large Databases
For very large and complex databases, managing multiple tables and relationships can become difficult and complicated.
2. Performance Issues
As the number of tables increases, operations like joins can slow down query performance, especially with large datasets.
3. High Memory Usage
Relational databases may require more memory and storage due to the use of indexes, constraints, and multiple tables.
4. Rigid Structure
The structure of tables is fixed, so making frequent changes (like adding new attributes) can be difficult and time-consuming.
5. Not Suitable for Unstructured Data
The relational model is designed for structured data and is not efficient for handling unstructured or semi-structured data like images, videos, or JSON.
6. Costly Implementation
Setting up and maintaining relational database systems can be expensive due to software, hardware, and skilled professionals.
7. Normalization Overhead
While normalization reduces redundancy, it can lead to more tables and complex queries, increasing processing time.
Applications of Relational Model
1. Banking Systems
Used to manage customer accounts, transactions, loans, and payments in a structured and secure way.
2. Educational Institutions
Helps in managing data of students, teachers, courses, exams, and results efficiently.
3. E-Commerce Websites
Used to handle products, customers, orders, payments, and inventory in online shopping platforms.
4. Hospital Management Systems
Stores and manages patient records, doctor details, appointments, and billing information.
5. Airline Reservation Systems
Used to manage flight schedules, ticket bookings, passenger details, and seat availability.
6. Library Management Systems
Maintains records of books, members, issue/return details, and fines.
7. Business and Enterprise Applications
Used in companies to manage employee data, payroll, sales, and inventory systems efficiently.
Conclusion
The Relational Model is a powerful and widely used database model that organizes data into structured tables and establishes relationships using keys. It ensures data consistency, integrity, and easy management through concepts like primary keys, foreign keys, and normalization. By providing a simple tabular format and supporting flexible data operations, the relational model makes it easier to store, retrieve, and manipulate data efficiently. Overall, it forms the foundation of modern database systems and is essential for handling structured data in real-world applications.