Relational Model | GATE Computer Science Engineering(CSE) 2026 Mock Test Series - Computer Science Engineering (CSE) PDF Download

Introduction

The relational model was proposed by E.F. Codd to model data in the form of relations or tables. After designing the conceptual model of the database using an ER diagram, we need to convert the conceptual model into the relational model, which can be implemented using any RDBMS language like Oracle SQL, MySQL, etc. So we will see what the relational model is.

What is Relational Model?

The relational model represents how data is stored in relational databases. A relational database stores data in the form of relations (tables). Consider a relation STUDENT with attributes ROLL_NO, NAME, ADDRESS, PHONE, and AGE shown in the table below.

STUDENT

Important Terminologies

1. Attribute: Attributes are the properties that define a relation. e.g.; ROLL_NO, NAME

2. Relation Schema: A relation schema represents the name of the relation with its attributes. e.g., STUDENT (ROLL_NO, NAME, ADDRESS, PHONE and AGE) is the relation schema for STUDENT. If a schema has more than 1 relation, it is called a relational schema.
3. Tuple: Each row in the relation is known as a tuple. The above relation contains 4 tuples, one of which is shown as:
   
4. Relation Instance: The set of tuples of a relation at a particular instance of time is called a relation instance. Table (STUDENT shows the relation instance of STUDENT at a particular time. It can change whenever there is an insertion, deletion or update in the database.
5. Degree: The number of attributes in the relation is known as the degree of the relation. The STUDENT relation defined above has degree 5.
6. Cardinality: The number of tuples in a relation is known as cardinality. The STUDENT relation defined above has cardinality 4.
7. Column: Column represents the set of values for a particular attribute. The column ROLL_NO is extracted from the relation STUDENT.
   
8. NULL Values: The value which is not known or unavailable is called a NULL value. It is represented by blank space. e.g., the PHONE of the STUDENT having ROLL_NO 4 is NULL.
9. Table 1 and Table 2 represent relational model having two relations STUDENT and STUDENT_COURSE

   

Constraints in Relational Model

While designing a relational model, we define some conditions which must hold for data present in the database, called constraints. These constraints are checked before performing any operation (insertion, deletion and update) in the database. If there is a violation in any of the constraints, the operation will fail.

Domain Constraints

These are attribute-level constraints. An attribute can only take values which lie inside the domain range. e.g., if a constraint AGE > 0 is applied to the STUDENT relation, inserting a negative value of AGE will result in failure.

Entity Integrity Constraints

Entity Integrity is a fundamental rule in relational database systems that ensures each table has a unique and identifiable row.

Specifically, entity integrity means that the primary key of a table must never be NULL. Every row in a table must have a unique and non-null value for the primary key so that it can be properly identified and referenced.

Key Constraints

Key constraints are rules in relational databases that ensure the uniqueness and identification of data in a table. These constraints are applied to keys, such as primary keys and candidate keys, to prevent duplicate or null values where they are not allowed. e.g., ROLL_NO in STUDENT is a key. No two students can have the same roll number. So a key has two properties:

• It should be unique for all tuples.
• It can’t have NULL values.

Referential Integrity

When one attribute of a relation can only take values from another attribute of the same relation or any other relation, it is called referential integrity. It is achieved using foreign keys. Let us suppose we have 2 relations

STUDENT

 BRANCH

BRANCH_CODE of STUDENT can only take the values that are present in BRANCH_CODE of BRANCH, which is called a referential integrity constraint. The relation that is referencing another relation is called a REFERENCING RELATION (STUDENT in this case), and the relation to which other relations refer is called a REFERENCED RELATION (BRANCH in this case).

Anomalies

An anomaly is an irregularity or something that deviates from the expected or normal state. When designing databases, we identify three types of anomalies: insert, update, and delete.

Insertion Anomaly in Referencing Relation

We can’t insert a row in REFERENCING RELATION if the referencing attribute’s value is not present in the referenced attribute value. e.g., insertion of a student with BRANCH_CODE ‘ME’ in STUDENT relation will result in error because ‘ME’ is not present in BRANCH_CODE of BRANCH.

Deletion/Updation Anomaly in Referenced Relation

We can’t delete or update a row from REFERENCED RELATION if the value of REFERENCED ATTRIBUTE is used in the value of REFERENCING ATTRIBUTE. e.g., if we try to delete a tuple from BRANCH having BRANCH_CODE ‘CS’, it will result in an error because ‘CS’ is referenced by BRANCH_CODE of STUDENT, but if we try to delete the row from BRANCH with BRANCH_CODE CV, it will be deleted as the value is not being used by a referencing relation. It can be handled by the following method:

On Delete Cascade

It will delete the tuples from REFERENCING RELATION if the value used by REFERENCING ATTRIBUTE is deleted from REFERENCED RELATION. e.g., if we delete a row from BRANCH with BRANCH_CODE ‘CS,’ the rows in the STUDENT relation with BRANCH_CODE CS (ROLL_NO 1 and 2 in this case) will be deleted.

On Update Cascade

It will update the REFERENCING ATTRIBUTE in REFERENCING RELATION if the attribute value used by REFERENCING ATTRIBUTE is updated in REFERENCED RELATION. e.g., if we update a row from BRANCH with BRANCH_CODE ‘CS’ to ‘CSE,’ the rows in the STUDENT relation with BRANCH_CODE CS (ROLL_NO 1 and 2 in this case) will be updated with BRANCH_CODE ‘CSE.’

Codd Rules

Codd rules were proposed by E.F. Codd, which should be satisfied by the relational model.

1. Information Rule: Data stored in a relational model must be a value of some cell of a table.
2. Guaranteed Access Rule: Every data element must be accessible by table name, its primary key and the name of the attribute whose value is to be determined.
3. Systematic Treatment of NULL Values: NULL values in the database must only correspond to missing, unknown, or not applicable values.
4. Active Online Catalog: The structure of the database must be stored in an online catalog which can be queried by authorised users.
5. Comprehensive Data Sub-language Rule: A database should be accessible by a language supported for definition, manipulation and transaction management operation.
6. View Updating Rule: Different views created for various purposes should be automatically updatable by the system.
7. High-level insert, update and delete rules: The relational model should support insert, delete, update, etc. operations at each level of relations. Also, set operations like union, intersection and minus should be supported.
8. Physical data independence: Any modification in the physical location of a table should not enforce modification at the application level.
9. Logical data independence: Any modification in the logical or conceptual schema of a table should not enforce modification at the application level. For example, merging two tables into one should not affect an application accessing it, which is difficult to achieve.
10. Integrity Independence: Integrity constraints modified at the database level should not enforce modification at the application level.
11. Distribution Independence: Distribution of data over various locations should not be visible to end-users.
12. Non-Subversion Rule: Low-level access to data should not be able to bypass the integrity rule to change data.

Keys in Relational Model (Candidate, Super, Primary, Alternate and Foreign)

In the relational model of a database, keys are special attributes or sets of attributes used to uniquely identify tuples (rows) in a relation (table). Keys play a crucial role in maintaining data integrity, ensuring uniqueness, and establishing relationships between tables.

Let us understand keys using the following tables:

Candidate Key 

The minimal set of attributes which can uniquely identify a tuple is known as a candidate key. For example, STUD_NO in the STUDENT relation.

• The value of the candidate key is unique and non-null for every tuple.
• There can be more than one candidate key in a relation. For example, STUD_NO and STUD_PHONE are both candidate keys for the relation STUDENT.
• The candidate key can be simple (having only one attribute) or composite as well. For example, {STUD_NO, COURSE_NO} is a composite candidate key for relation STUDENT_COURSE.

Super Key

The set of attributes which can uniquely identify a tuple is known as Super Key. For Example, STUD_NO, (STUD_NO, STUD_NAME) etc.

• Adding zero or more attributes to a candidate key generates super key.
• A candidate key is a super key, but vice versa is not true.

Primary Key

There can be more than one candidate key in a relation, out of which one can be chosen as the primary key. For example, STUD_NO as well as STUD_PHONE are both candidate keys for the relation STUDENT, but STUD_NO can be chosen as the primary key (only one out of many candidate keys).

Alternate Key

The candidate keys other than the primary key are called alternate keys. For example, STUD_NO as well as STUD_PHONE are both candidate keys for the relation STUDENT, but STUD_PHONE will be the alternate key (only one out of many candidate keys).

Foreign Key

If an attribute can only take the values which are present as values of some other attribute, it will be a foreign key to the attribute to which it refers. The relation which is being referenced is called the referenced relation, and the corresponding attribute is called the referenced attribute, and the relation which refers to the referenced relation is called the referencing relation, and the corresponding attribute is called the referencing attribute. The referenced attribute of the referencing attribute should be the primary key. For example, STUD_NO in STUDENT_COURSE is a foreign key to STUD_NO in the STUDENT relation.