Relational Model in DBMS
Subject: Computer Science Fundamentals · Language: DBMS · Level: beginner · 10 min read
Understand relational model, tables, rows, columns, relations, tuples, attributes, domains, schema, instance, keys, and relational constraints.
The relational model is a database model where data is stored in tables. It is one of the most important models in DBMS because most SQL-based databases use it. MySQL, PostgreSQL, Oracle, SQL Server, and SQLite follow the relational approach. In the relational model, a table is called a relation. The table has rows and columns. Each row stores one complete record, and each column stores one property of that record.
A beginner can understand the relational model by thinking of a well-structured spreadsheet. A Student table may have columns such as roll_no, name, department, and phone. Each row contains details of one student.
Why Relational Model Is Important
The relational model is important because it stores data in a simple and structured form. Tables are easy to understand, and relationships between tables can be managed using keys. This model also works well with SQL. Users can write queries to search, filter, sort, group, update, and combine data from multiple tables.
Many important DBMS topics depend on the relational model. Keys, normalization, joins, constraints, transactions, and indexing all become easier when the relational model is clear.
Basic Structure
A relational database contains multiple tables. Each table should store one type of information. For example, a college database may contain Students, Courses, Departments, Teachers, and Marks tables. The tables can be connected using keys. For example, the Marks table can store roll_no to connect marks with the correct student. This connection avoids storing complete student details again and again in the Marks table.
This separation keeps data cleaner and reduces duplication.
Relation, Tuple, and Attribute
In DBMS theory, table-related terms have formal names. Students should know both common names and formal names because interviews and exams may use either.
| Common Term | Relational Model Term | Meaning |
| --- | --- | --- |
| Table | Relation | Collection of related records |
| Row | Tuple | One complete record |
| Column | Attribute | One property of the record |
| Cell value | Attribute value | One value in a row and column |
| Table design | Schema | Structure of relation |
For example, in a Student table, the full table is a relation. One student's record is a tuple. roll_no, name, and department are attributes.
Relation
A relation is a table with rows and columns. It represents one entity or one relationship in the database. For example, Student, Course, and Enrollment can each be relations. A relation should have a meaningful name. It should store one main type of data. Mixing unrelated data in one relation creates confusion and makes queries difficult.
Each relation has a schema, which defines its structure, and an instance, which represents the actual data currently stored.
Tuple
A tuple is one row in a relation. It represents one complete record. In a Student relation, one tuple may store the details of one student. For example, `(101, Rahul, BCA, 85)` can be a tuple if the Student table has roll_no, name, department, and marks columns.
Every tuple in a relation should follow the same structure. This means each row should have values according to the attributes defined in the schema.
Attribute
An attribute is a column in a relation. It describes one property of the entity. In a Student relation, roll_no, name, department, phone, and email can be attributes. Attributes should be chosen carefully. If an attribute is not useful, it makes the table unnecessarily large. If an important attribute is missing, the database may not be able to answer required questions.
Each attribute has a name and usually a data type. For example, roll_no may be an integer, name may be text, and date_of_birth may be a date.
Domain
A domain is the set of valid values allowed for an attribute. For example, the domain of marks may be numbers from 0 to 100. The domain of gender or status may be a limited set of allowed values depending on the design. Domains help keep data valid. If marks should be numeric, the DBMS should not accept unrelated text in that column.
Domain rules are important because wrong values can make reports, calculations, and decisions incorrect.
Schema
A schema is the structure or design of a relation or database. It defines table names, column names, data types, keys, constraints, and relationships. For example, `Student(roll_no, name, department, phone)` is a simple relation schema. It tells us what attributes a Student relation contains.
The schema does not change frequently. It is like the design plan of the database.
Instance
An instance is the actual data stored in the database at a particular time. Unlike schema, the instance changes frequently as records are inserted, updated, and deleted. For example, if the Student table currently contains 500 student records, those 500 rows are part of the current instance. A simple way to remember is: schema is design, instance is data at a moment.
Example Relation
The following simple relation shows how student data may look in a relational database.
```text
Student
+---------+--------+------------+-------+
| roll_no | name | department | marks |
+---------+--------+------------+-------+
| 101 | Rahul | BCA | 85 |
| 102 | Priya | B.Tech | 91 |
| 103 | Amit | BCA | 78 |
+---------+--------+------------+-------+
```
Here, Student is the relation name. roll_no, name, department, and marks are attributes. Each row is a tuple.
Keys in Relational Model
Keys are used to identify rows and connect tables. The beginner-required key types are listed below.
1. Primary key: It uniquely identifies each tuple in a relation. For example, roll_no can identify each student.
2. Foreign key: It connects one table to another table. For example, roll_no in the Marks table can refer to roll_no in the Student table.
3. Candidate key: It is an attribute or set of attributes that can uniquely identify a row. One candidate key is selected as the primary key.
4. Composite key: It uses more than one attribute to identify a row. For example, student_id and course_id together can identify one enrollment record.
Keys are important because they prevent confusion. If two students have the same name, roll_no still identifies the exact student.
Relational Constraints
Relational constraints are rules that keep database data correct. The main types are listed below.
1. Domain constraint: Attribute values must belong to the allowed domain. For example, marks should be numeric and usually within a valid range.
2. Key constraint: Key values must be unique where required. For example, two students should not have the same roll number.
3. Entity integrity: Primary key values cannot be null because a row must be identifiable.
4. Referential integrity: A foreign key must refer to an existing valid record or be null if the design allows it.
Entity Integrity
Entity integrity protects the primary key. Since a primary key identifies a row, it should not be null. If a primary key is missing, the DBMS cannot reliably identify that record. For example, a student record without roll_no may create confusion because roll_no is used to identify students. Therefore, primary key values must be present and unique.
This rule is very important in relational database design.
Referential Integrity
Referential integrity protects relationships between tables. It ensures that a foreign key value refers to an existing record in the related table. For example, if the Marks table has roll_no 105, then roll_no 105 should exist in the Student table. Otherwise, the marks record belongs to a student who does not exist in the database.
Referential integrity keeps connected data meaningful and prevents broken relationships.
Relational Model Diagram
This simple diagram shows how two relations can be connected using a key.
```text
Students table
roll_no (PK) ----+
name |
department |
|
v
Marks table
mark_id
roll_no (FK)
subject
marks
```
Here, roll_no is the primary key in Students and a foreign key in Marks. This connection allows the database to link marks with the correct student.
Advantages of Relational Model
The relational model is easy to understand because data is stored in tables. It also reduces unnecessary duplication when tables are designed properly. It supports powerful querying through SQL. Users can filter records, join tables, group data, calculate summaries, and update records using structured commands.
It also supports integrity rules and transactions, which help keep data correct and reliable.
Limitations of Relational Model
The relational model is excellent for structured data, but it may be less flexible when data is highly nested, irregular, or constantly changing in shape. Complex relationships may require many tables and joins. If the database is not designed properly, queries can become complicated or slow. Even with these limitations, the relational model remains one of the most important and widely used database models.
Important Points to Remember
The relational model stores data in relations, which are tables. Rows are tuples, columns are attributes, and domains define valid values for attributes. Schema means structure, while instance means actual data at a particular time. Keys and constraints keep relational data correct and connected.
For beginners, the main idea is simple: relational databases organize data into clean tables and connect those tables using keys.