Relational Model in DBMS

The Relational Model represents data and their relationships through a collection of tables. Each table also known as a relation consists of rows and columns. Every column has a unique name and corresponds to a specific attribute, while each row contains a set of related data values representing a real-world entity or relationship. This model is part of the record-based models which structure data in fixed-format records each belonging to a particular type with a defined set of attributes.

E.F. Codd introduced the Relational Model to organize data as relations or tables. After creating the conceptual design of a database using an ER diagram, this design must be transformed into a relational model which can then be implemented using relational database systems like Oracle SQL or MySQL.

What is the Relational Model? 

The relational model represents how data is stored in Relational Databases. A relational database consists of a collection of tables each of which is assigned a unique name. Consider a relation STUDENT with attributes ROLL_NO, NAME, ADDRESS, PHONE, and AGE shown in the table. 

Table STUDENT

Key Terms

• Attribute: Attributes are the properties that define an entity. e.g. ROLL_NO, NAME, ADDRESS.

• Relation Schema: A relation schema defines the structure of the relation and 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 Relational Schema.

• Tuple: Each row in the relation is known as a tuple. The above relation contains 4 tuples one of which is shown as:

• Relation Instance: The set of tuples of a relation at a particular instance of time is called a relation instance. It can change whenever there is an insertion, deletion or update in the database.

• Degree: The number of attributes in the relation is known as the degree of the relation. The STUDENT relation defined above has degree 5.

• Cardinality: The number of tuples in a relation is known as cardinality. The STUDENT relation defined above has cardinality 4.

• Column: The column represents the set of values for a particular attribute. The column ROLL_NO is extracted from the relation STUDENT.

• NULL Values: The value which is not known or unavailable is called a NULL value. It is represented by NULL. e.g. PHONE of STUDENT having ROLL_NO 4 is NULL. 

• Relation Key: These are basically the keys that are used to identify the rows uniquely or also help in identifying tables. These are of the following types:
  • Primary Key
  • Candidate Key
  • Super Key
  • Foreign Key
  • Alternate Key
  • Composite Key

Relational Model Notation

• Relation schema R of degree n is denoted by by R(A1, A2, …,An).

• Uppercase letters Q, R, S denote relation names.

• Lowercase letters q, r, s denote relation states.

• Letters t, u, v denote tuples.

• In general, the name of a relation schema such as STUDENT also indicates the current set of tuples in that relation.

• An attribute A can be qualified with the relation name R to which it belongs by using the dot notation R.A for example, STUDENT.Name or STUDENT.Age.

• An n-tuple t in a relation r(R) is represented as t=<v1,v2,…,vn> where vi is the value corresponding to the attribute Ai​. The value vi for attribute Ai​ in tuple t can be accessed using t[Ai] or t.Ai.

Characteristics of the Relational Model

• Data Representation: Data is organized in tables (relations), with rows (tuples) representing records and columns (attributes) representing data fields.

• Atomic Values: Each attribute in a table contains atomic values, meaning no multi-valued or nested data is allowed in a single cell.

• Unique Keys: Every table has a primary key to uniquely identify each record, ensuring no duplicate rows.

• Attribute Domain: Each attribute has a defined domain, specifying the valid data types and constraints for the values it can hold.

• Tuples as Rows: Rows in a table, called tuples, represent individual records or instances of real-world entities or relationships.

• Relation Schema: A table’s structure is defined by its schema, which specifies the table name, attributes, and their domains.

• Data Independence: The model ensures logical and physical data independence, allowing changes in the database schema without affecting the application layer.

• Integrity Constraints: The model enforces rules like:
  • Domain constraints: Attribute values must match the specified domain.
  • Entity integrity: No primary key can have NULL values.
  • Referential integrity: Foreign keys must match primary keys in the referenced table or be NULL.

• Relational Operations: Supports operations like selection, projection, join, union, and intersection, enabling powerful data retrieval manipulation.

• Data Consistency: Ensures data consistency through constraints, reducing redundancy and anomalies.

• Set-Based Representation: Tables in the relational model are treated as sets, and operations follow mathematical set theory principles.

Constraints in Relational Model

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

Domain Constraints

Domain Constraints ensure that the value of each attribute A in a tuple must be an atomic value derived from its specified domain, dom(A). Domains are defined by the data types associated with the attributes. Common data types include:

• Numeric types: Includes integers (short, regular, and long) for whole numbers and real numbers (float, double-precision) for decimal values, allowing precise calculations.
• Character types: Consists of fixed-length (CHAR) and variable-length (VARCHAR, TEXT) strings for storing text data of various sizes.
• Boolean values: Stores true or false values, often used for flags or conditional checks in databases.
• Specialized types: Includes types for date (DATE), time (TIME), timestamp (TIMESTAMP), and money (MONEY), used for precise handling of time-related and financial data.

Key Integrity

Every relation in the database should have at least one set of attributes that defines a tuple uniquely. Those set of attributes is called keys. e.g.; ROLL_NO in STUDENT is 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 Constraints

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. Let us suppose we have 2 relations 

Table STUDENT

Table BRANCH

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

Anomalies in the Relational Model

An anomaly is an irregularity or something which 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 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 an 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 been used by 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 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 STUDENT relation with BRANCH_CODE CS (ROLL_NO 1 and 2 in this case) will be updated with BRANCH_CODE ‘CSE’.

For more, refer Anomalies in Relational Model.

Super Keys

Any set of attributes that allows us to identify unique rows (tuples) in a given relationship is known as super keys. Out of these super keys, we can always choose a proper subset among these that can be used as a primary key. Such keys are known as Candidate keys. If there is a combination of two or more attributes that are being used as the primary key then we call it a Composite key.

Codd Rules in Relational Model

Edgar F Codd proposed the relational database model where he stated rules. Now these are known as Codd’s Rules. For any database to be the perfect one, it has to follow the rules.

For more, refer to Codd Rules in Relational Model.

Advantages of the Relational Model

• Simple model: Relational Model is simple and easy to use in comparison to other languages.
• Flexible: Relational Model is more flexible than any other relational model present.
• Secure: Relational Model is more secure than any other relational model.
• Data Accuracy: Data is more accurate in the relational data model.
• Data Integrity: The integrity of the data is maintained in the relational model.
• Operations can be Applied Easily: It is better to perform operations in the relational model.

Disadvantages of the Relational Model

• Relational Database Model is not very good for large databases.
• Sometimes, it becomes difficult to find the relation between tables.
• Because of the complex structure, the response time for queries is high.

Conclusion

The Relational Model is the most popular way to organize data in databases because it’s simple and easy to understand. It uses tables to store information, where each row represents a real-world thing and each column describes its properties. This model makes sure the data is accurate, consistent and easy to update. Since it’s built on solid rules, it’s reliable and works well for all kinds of applications. That’s why it’s widely used in businesses and everyday systems to manage data effectively.

Frequently Asked Questions on Relational Model in DBMS

What is a table in relational model?

In the relational model, a table (or relation) is a structured way to store data in rows and columns.
Rows represent individual records (e.g., a student).
Columns represent attributes (e.g., Name, Age).

What is the relational model in DBMS?

The relational model in DBMS is a way of organizing data into tables (called relations). Each table consists of rows (records) and columns (attributes). It uses keys, like primary and foreign keys, to maintain relationships between tables. This model ensures data consistency, follows strict rules, and is easy to understand and use, making it the most widely used approach in modern databases.

What are the characteristics of relations?

The characteristics of relations are:

• Organized as tables with rows (tuples) and columns (attributes).
• Rows are unique, and no duplicates are allowed.
• Columns have unique names and contain atomic (single) values.
• The order of rows and columns doesn’t matter.
• Each attribute has a defined domain of valid values.
• Null values are allowed for missing or unknown data.

kartik

Follow

Improve

Article Tags :

• Databases
• DBMS
• dbms
• DBMS-Relational Model