Write an Interview Experience

Introduction of Relational Model and Codd Rules in DBMS

Difficulty Level : Easy
Last Updated : 17 Mar, 2023

Read

Discuss

It was proposed by Dr. E.F. Codd. It uses the concept of relations to represent each and every file. Relations are Two-Dimensional Tables. It is easy to implement and easy to simplification in the operations to manipulate the data. This is the most popular data model. It is simple to implement. It uses the primary key and secondary key to connect any two files. Normalization Theory is used to design the object-based data model. Relational Algebra and Relational Calculus are used to process the relations manually. Many database languages are oriented toward handling relational data models. A Relational Database Model consists of relations to connect them by key fields. A relation has some attributes. The relation is represented in rows and columns. Each column of the relation is called an attribute. Each row in the relation is called a tuple. Each relation can have one unique column i.e. primary key. Each relation can have n-columns and n-tuple. Each relation is preceded by the name of that relation. The fields of the relations are separated by commas and placed within the parentheses of the relation. The relational model represents data in the form of relations or tables.

Example: Relational Model can be represented as shown below

STUDENT (StudNo, Sname, Special)
ENROLLMENT (StudNo, Subcode, marks)
SUBJECT (Subcode, Subname, Maxmarks, Faccode)
FACULTY (Faccode, Fname, Dept)

Terminologies

Relational Schema: Schema represents the structure of a relation.

Example: Relational Schema of STUDENT relation can be represented as STUDENT(STUD_NO, STUD_NAME, STUD_PHONE, STUD_STATE, STUD_COUNTRY, STUD_AGE).

Relational Instance: The set of values present in a relationship at a particular instance of time is known as a relational instance as shown in Table 1 and Table 2.

Attribute: Each relation is defined in terms of some properties, each of which is known as an attribute.

Example: STUD_NO, STUD_NAME, etc. are attributes of relation STUDENT.

The domain of an attribute: The possible values an attribute can take in a relation is called its domain.

Example: domain of STUD_AGE can be from 18 to 40.

Tuple: Each row of a relation is known as a tuple.

Example: STUDENT relation given below has 4 tuples.

NULL values: Values of some attribute for some tuples may be unknown, missing, or undefined which are represented by NULL. Two NULL values in a relationship are considered different from each other. Table 1 and Table 2 represent the relational model having two relations STUDENT and STUDENT_COURSE.

STUDENT TABLE

STUD_NO	STUD_NAME	STUD_PHONE	STUD_STATE	STUD_COUNTRY	STUD_AGE
1	RAM	9716271721	Haryana	India	20
2	RAM	9898291281	Punjab	India	19
3	SUJIT	7898291981	Rajasthan	India	18
4	SURESH	9985286317	Punjab	India	21

STUDENT COURSE TABLE

STUD_NO	COURSE_NO	COURSE_NAME
1	C1	DBMS
2	C2	Computer Networks
1	C2	Computer Networks

RDBMS Vendors

There are many different vendors that produce relational database management systems (RDBMS). Examples: Oracle Corporation, IBM, Microsoft Corporation, and PostgreSQL Developer Group.

Relational Algebra

It is a procedural Language. It consists of a set of operators that can be performed on relations. Relational Algebra forms the basis for many other high-level data sub-languages like SQL and QBE.
Relational algebra has mainly 9 types of operators.

1. UNION (U): A and B are two relations. It displays total values (Attributes) in both relations. It avoids duplicate values in both relations. U symbol can be used.

Syntax:

A UNION B (or) A U B

Example:

 A = { clerk, manager, salesman}
 B = { president, clerk, manager}
 A UNION B = {clerk, manager, salesman, president}

2. INTERSECTION (∩): A and B are two relations. It displays common elements in both relations. “∩” symbol can be used.

Syntax:

A INTERSECT B (or) A ∩ B

Example:

A = { clerk, manager, salesman}
B = { president, clerk, manager}
A INTERSECT B = { clerk, manager}

3. DIFFERENCE (─): A and B are two relations. It displays elements in relation A not in relation B.

Syntax:

A MINUS B (OR) A ─ B

Example:

A = { clerk, manager, salesman}
B = { president, clerk, manager}
A MINUS B = {salesman}

4. CARTESIAN PRODUCT(X): A and B are two relations. It has a new relation consisting of all pair wises combinations of all elements in A and B. The relation A has “m” elements and relation B has “n” elements, then the resultant relation will be “ m * n “.

Syntax:

A TIMES B (OR) A X B

Example:

A = { clerk, manager, salesman}
B = { president, clerk, manager}
A TIMES B = { (clerk, president), 
(clerk, clerk),(clerk, manager), 
(manager, president), (manager, clerk),
(manager, manager),(salesman, president), 
(salesman, clerk), (salesman, manager) }

5. SELECTION (σ): It displays all the attributes or columns of all the rows in a relation. “” operator can be used to select the attributes in a relation.

Syntax:

σ condition (relation name)

6. PROJECTION (π): It displays some specified columns in a relation. “π” operator can be used to select some specified columns in a relation. It selects tuples that satisfy the given predicate from a relation. It displays some specified columns by using some conditions.

Syntax:

Syntax: π(col1,col2…) Relation Name

Example:

π(sno, sname, total) MARKS

7. JOIN( ): It combines two or more relations. Symbols can be used. It can be mainly divided into mainly 4 types. These are mainly

8. DIVIDE (÷): It divides the tuple from one relation to another relation

Syntax:

A DIVIDE B (OR) A ÷ B

Example:

A = { clerk, manager, salesman} 
B = { clerk, manager}
A DIVIDE B = {salesman}

9. RENAME(ρ): It gives another name to the relation.

Syntax:

ρ(OLD RELATION, NEW RELATION)

Example:

ρ(STUDENT, MARKS)
It changes the “student” relation to “Marks” relation.
It also renames the specified column.
It changes the old-column name to new-column name.

Advantages of Relational Algebra

Relational algebra has a solid mathematical background.
The mathematical background of relational algebra is the basis of many interesting developments and theorems.
If we have two expressions for the same operation and if the expressions are proved to be equivalent, then a query optimizer can automatically substitute the more efficient form.

Disadvantages of Relational Algebra

Relational algebra cannot run on the database server.
Relational algebra cannot do arithmetic.
Example: The price of petrol is 50/-, by increasing 10% price, which cannot be done using relational algebra.
It cannot sort or print results in various formats.
It cannot perform aggregate operations.

Codd’s Twelve Rules of Relational Database

Codd rules were proposed by E.F. Codd which should be satisfied by the relational model. Codd’s Rules are basically used to check whether DBMS has the quality to become Relational Database Management System (RDBMS). But, it is rare to find that any product has fulfilled all the rules of Codd. They generally follow the 8-9 rules of Codd. E.F. Codd has proposed 13 rules which are popularly known as Codd’s 12 rules. These rules are stated as follows:

Rule 0: Foundation Rule– For any system that is advertised as, or claimed to be, a relational database management system, that system must be able to manage databases entirely through its relational capabilities.
Rule 1: Information Rule– Data stored in the Relational model must be a value of some cell of a table.
Rule 2: Guaranteed Access Rule– Every data element must be accessible by the table name, its primary key, and the name of the attribute whose value is to be determined.
Rule 3: Systematic Treatment of NULL values– NULL value in the database must only correspond to missing, unknown, or not applicable values.
Rule 4: Active Online Catalog– The structure of the database must be stored in an online catalog that can be queried by authorized users.
Rule 5: Comprehensive Data Sub-language Rule- A database should be accessible by a language supported for definition, manipulation, and transaction management operation.
Rule 6: View Updating Rule- Different views created for various purposes should be automatically updatable by the system.
Rule 7: High-level insert, update and delete rule- 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.
Rule 8: Physical data independence- Any modification in the physical location of a table should not enforce modification at the application level.
Rule 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 the application accessing it which is difficult to achieve.
Rule 10: Integrity Independence- Integrity constraints modified at the database level should not enforce modification at the application level.
Rule 11: Distribution Independence- Distribution of data over various locations should not be visible to end-users.
Rule 12: Non-Subversion Rule- Low-level access to data should not be able to bypass the integrity rule to change data.

GATE Question

Given the basic ER and relational models, which of the following is INCORRECT? [GATE CS 2012]

An attribute of an entity can have more than one value.
An attribute of an entity can be a composite.
In a row of a relational table, an attribute can have more than one value.
In a row of a relational table, an attribute can have exactly one value or a NULL value.

Answer: In the relation model, an attribute can’t have more than one value. So, option 3 is the answer.

My Personal Notes