Relational Model in DBMS

In this course, we will study the relational data model, the concepts related to it-tables, tuple, instances, schema, key, attribute domain and the constraints related to it.

What is the relational model in DBMS

The Relational Model was given by E.F. Codd to model data in the form of tables or relations.

After designing the conceptual model of Database, we convert the conceptual model into the relational model which can be implemented using any RDBMS languages like Oracle SQL, MySQL, etc.

The purpose of the relational model is to provide a method to specify data and queries: users can directly state what information the database contains and what information they want from it.

The database management system software is responsible for describing the data structures for storing the data and retrieval procedures for answering the queries.

A relation can be said to be nothing but a table of values. Every row represents a collection of related data. These rows in the table are used to denote a real-world entity or relationship.

The relational data model is the primary data model.
The relational model is widely used around the world for storing data and processing it. This model is very simple and has all the properties and capabilities required for processing data with storage efficiency.

STUDENT_ID STUDENT_NAME CONTACT ADDRESS
1 Sammer 7263839 Delhi
2 Kartik 7637822 Kolkata
3 Chirag 3749377 Ranchi
4 Pankaj 3748453 Panipat

Consider the relation <STUDENT> having attributes student_id. Student_name, contact, and address.

What are the concepts related to the relational data model

Relational Data Model

Attribute

Each and every column in a Table. Attributes are the properties that are used to define a relation. e.g., Student_name , contact, etc.

Tables

The relations in the relational data model are saved in the table format. The relations are stored along with their entities. A table consists of two properties: rows and columns. Rows are used to represent records and columns represent attributes.

Tuple

It is a single row of a table, which consists of a single record.

Relation Schema

A relation schema is used to represent the name of the relation along with its attributes.

Degree

The total number of attributes present in the relation is called the degree of relation.

Cardinality

The total number of rows that are present in the Table.

Column

The column describes the set of values for a specific attribute.

Relation instance

Relation instance is a finite set of tuples in the system. Relation instances never contain duplicate tuples.

Relation key

Every row consists of one, two or multiple attributes, which is known as a relation key.

Attribute domain

The scope predefined with every attribute is known as the attribute domain.

What are the constraints in the relational data model?

While designing the Relational Model the conditions that are defined and which must hold for data present in the database are known as Constraints. These constraints are validated before performing any operation like insertion, deletion, and updation in the database system. If there is any violation in any of the constraints, the operation is terminated or failure occurs.

Domain Constraints

An attribute is permitted to take only those values that lie inside the domain range. e.g, If a constraint AGE>0 is applied to the relation, on inserting the negative value of AGE the operation will fail.

Doctor_name Age Conatct_no Address
Dr. Ram 32 2537848494 Amritsar
Dr, Neeraj 29 4654738893 Pune
Dr. Siya 27 3764748949 Delhi

Key Integrity

Every relation in the database must contain at least one set of attributes that can define a tuple uniquely. This set of attributes is called a key. e.g.; id in the Employee table is a key. No two employees can have the same id. So there are two properties of key:

  1. A key should be unique for all tuples.
  2. A key can not have a NULL value.
Id NAME ADDRESS MOBILE DOB
1 Ram Amritsar 83238787 23-08-1995
2 Kriti Lucknow 63778929 8-02-1998
3 Parul Faridabad 73637889 19-02-1999

Referential Integrity

When an attribute in a relationship is only capable of taking values from another attribute of the same relation or any other relation, it is called referential integrity.

<STUDENT> table

Id NAME ADDRESS MOBILE BRANCH_CODE
1 Ram Amritsar kso995 kso995
2 Kriti Lucknow 63778929 sjkd998
3 Parul Faridabad 73637889 sjdn999

<BRANCH> table

BRANCH_CODE BRANCH_NAME
kso005 civil
sjkd998 Computer science and eng.
sjdn999 mechanical
djdk990 chemical

In this case, the BRANCH_CODE of <STUDENT> table can only take the values which are present in BRANCH_CODE of <BRANCH> table which is referential integrity constraint. The relation which is referencing to the other relation is known as REFERENCING RELATION (which is STUDENT in this case) and the relation to which other relations referred is known as REFERENCED RELATION (which is BRANCH in this case).

What are the advantages of using the Relational model

Simplicity

A relational data model is simpler and much easier to use than the hierarchical and network model.

Structural Independence

The performance of the model id improved as it does not focus on the structure.

Easy to use

The relational model is easy as it contains tables having rows and columns which is quite natural and simple to understand.

Query capability

We can avoid high complex database navigation.

Data independence

Without changing any application the structure of the database can be changed.

Scalable

A large number of records, or rows, and the number of fields can be there.

What are the disadvantages of using the Relational model

Some of the relational databases have limits on field lengths which can not be exceeded.

Sometimes the databases can become complex as the amount of data grows, and the relations between data become complicated.

In the case of complex relational database systems, isolated databases can be there, in this the information can not be shared between systems.