Chapter : 3
Relational Model & Relational Algebra
Relational Model :
Relational Model was proposed by E.F. Codd to model data in the form of relations or tables.
After designing the conceptual model of Database using ER diagram, we need to convert the
conceptual model in the relational model which can be implemented using any RDBMS
languages like Oracle SQL, MySQL etc. So we will see what Relational Model is.
What is Relational Model?
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 Table
RDBMS stands for relational database management system. A relational model can be
represented as a table of rows and columns. A relational database has following major
components:
1. Table
2. Record or Tuple
3. Field or Column name or Attribute
4. Domain
5. Schema
6. Instance
7. Keys
Subject : DBMS Dr.Anirudh Mangore
Chapter : 3
Relational Model & Relational Algebra
1. Table
A table is a collection of data represented in rows and columns. Each table has a name in
database. For example, the following table “STUDENT” stores the information of students in
database.
Table: STUDENT
Student_Id Student_Name Student_Addr Student_Age
101 Chaitanya Dayal Bagh, Agra 27
102 Ajeet Delhi 26
103 Rahul Gurgaon 24
104 Shubham Chennai 25
2. Record or Tuple
Each row of a table is known as record. It is also known as tuple. For example, the following
row is a record that we have taken from the above table.
102 Ajeet Delhi 26
3. Field or Column name or Attribute
The above table “STUDENT” has four fields (or attributes): Student_Id, Student_Name,
Student_Addr & Student_Age.
4. Domain
A domain is a set of permitted values for an attribute in table. For example, a domain of
month-of-year can accept January, February,…December as values, a domain of dates can
accept all possible valid dates etc. We specify domain of attribute while creating a table.
An attribute cannot accept values that are outside of their domains. For example, In the above
table “STUDENT”, the Student_Id field has integer domain so that field cannot accept values
that are not integers for example, Student_Id cannot has values like, “First”, 10.11 etc.
Subject : DBMS Dr.Anirudh Mangore
Chapter : 3
Relational Model & Relational Algebra
5. Relation Schema
A relation schema describes the structure of the relation, with the name of the relation(name of table),
its attributes and their names and type.
6. Relational instance:
In the relational database system, the relational instance is represented by a finite set of tuples.
Relation instances do not have duplicate tuples.
7.Relational key:
In the relational key, each row has one or more attributes. It can identify the row in the relation uniquely.
Properties of Relations :
● Name of the relation is distinct from all other relations.
● Each relation cell contains exactly one atomic (single) value
● Each attribute contains a distinct name
● Attribute domain has no significance
● tuple has no duplicate value
● Order of tuple can have a different sequence
Constraints
Every relation has some conditions that must hold for it to be a valid relation. These conditions are
called Relational Integrity Constraints. There are three main integrity constraints −
● Key constraints
● Domain constraints
● Referential integrity constraints
Key Constraints
There must be at least one minimal subset of attributes in the relation, which can identify a tuple
uniquely. This minimal subset of attributes is called key for that relation. If there are more than one
such minimal subsets, these are called candidate keys.
Key constraints force that −
● in a relation with a key attribute, no two tuples can have identical values for key attributes.
● a key attribute can not have NULL values.
Key constraints are also referred to as Entity Constraints.
Subject : DBMS Dr.Anirudh Mangore
Chapter : 3
Relational Model & Relational Algebra
Domain Constraints
Attributes have specific values in real-world scenario. For example, age can only be a positive
integer. The same constraints have been tried to employ on the attributes of a relation. Every attribute
is bound to have a specific range of values. For example, age cannot be less than zero and
telephone numbers cannot contain a digit outside 0-9.
Referential integrity Constraints
Referential integrity constraints work on the concept of Foreign Keys. A foreign key is a key attribute
of a relation that can be referred in other relation.
Referential integrity constraint states that if a relation refers to a key attribute of a different or same
relation, then that key element must exist.
Converting E-R Models to Relational Models
Entity becomes Table
Entity in ER Model is changed into tables, or we can say for every Entity in ER model, a table is
created in Relational Model.
Attributes become Columns
And the attributes of the Entity gets converted to columns of the table.
And the primary key specified for the entity in the ER model, will become the primary key for the table
in relational model.
Relationship becomes a Relationship Table
In ER diagram, we use diamond/rhombus to reprsent a relationship between two entities. In Relational
model we create a relationship table for ER Model relationships too.
Subject : DBMS Dr.Anirudh Mangore
Chapter : 3
Relational Model & Relational Algebra
Converting ER Diagrams to Tables-
Following rules are used for converting an ER diagram into the tables-
For Strong Entity Set With Only Simple Attributes-
A strong entity set with only simple attributes will require only one table in relational model.
● Attributes of the table will be the attributes of the entity set.
● The primary key of the table will be the key attribute of the entity set.
Example-
Student
id name age address
Subject : DBMS Dr.Anirudh Mangore
Chapter : 3
Relational Model & Relational Algebra
For Strong Entity Set With Composite Attributes-
A strong entity set with any number of composite attributes will require only one table in relational model.
While conversion, simple attributes of the composite attributes are taken into account and not the
composite attribute itself.
Example-
Roll_no First_name Last_name House_no Street City
Schema : Student ( Roll_no , First_name , Last_name , House_no , Street , City )
Subject : DBMS Dr.Anirudh Mangore
Chapter : 3
Relational Model & Relational Algebra
For Strong Entity Set With Multi Valued Attributes-
A strong entity set with any number of multi valued attributes will require two tables in relational model.
● One table will contain all the simple attributes with the primary key.
● Other table will contain the primary key and all the multi valued attributes.
Example-
Roll_no City
Roll_no Mobile_no
Subject : DBMS Dr.Anirudh Mangore
Chapter : 3
Relational Model & Relational Algebra
Translating Relationship Set into a Table-
A relationship set will require one table in the relational model.
Attributes of the table are-
● Primary key attributes of the participating entity sets
● Its own descriptive attributes if any.
Set of non-descriptive attributes will be the primary key.
Example-
Emp_no Dept_id since
Schema : Works in ( Emp_no , Dept_id , since )
If we consider the overall ER diagram, three tables will be required in relational model-
● One table for the entity set “Employee”
● One table for the entity set “Department”
● One table for the relationship set “Works in”
Subject : DBMS Dr.Anirudh Mangore
Chapter : 3
Relational Model & Relational Algebra
Keys :
Keys are a very important part of the Relational database model. They are used to establish and
identify relationships between tables and also to uniquely identify any record or row of data inside a
table.
A Key can be a single attribute or a group of attributes, where the combination may act as a key.
Why we need a Key?
In real world applications, number of tables required for storing the data is huge, and the different
tables are related to each other as well.
Also, tables store a lot of data in them. Tables generally extends to thousands of records stored in
them, unsorted and unorganised.
Now to fetch any particular record from such dataset, you will have to apply some conditions, but what
if there is duplicate data present and every time you try to fetch some data by applying certain
condition, you get the wrong data. How many trials before you get the right data?
To avoid all this, Keys are defined to easily identify any row of data in a table.
Let's try to understand about all the keys using a simple example.
Let's take a simple Student table, with fields student_id, name, phone and age.
student_id name phone age
1 Akon 9876723452 17
2 Akon 9991165674 19
3 Bkon 7898756543 18
4 Ckon 8987867898 19
5 Dkon 9990080080 17
Subject : DBMS Dr.Anirudh Mangore
Chapter : 3
Relational Model & Relational Algebra
1. Super Key-
● A super key is a set of attributes that can identify each tuple uniquely in the given relation.
● A super key is not restricted to have any specific number of attributes.
● Thus, a super key may consist of any number of attributes.
Example-
Consider the following Student schema-
Student ( roll , name , gender, age , address , class , section )
Given below are the examples of super keys since each set can uniquely identify each student in the
Student table-
● ( roll , name , gender, age , address , class , section )
● ( class , section , roll )
● (class , section , roll , gender)
● ( name , address )
NOTE-
All the attributes in a super key are definitely sufficient to identify each tuple uniquely in the given relation
but all of them may not be necessary.
Subject : DBMS Dr.Anirudh Mangore
Chapter : 3
Relational Model & Relational Algebra
2. Candidate Key-
A minimal super key is called as a candidate key.
OR
A set of minimal attribute(s) that can identify each tuple uniquely in the given relation is called as a
candidate key.
Example-
Consider the following Student schema-
Student ( roll , name , Gender , age , address , class , section )
Given below are the examples of candidate keys since each set consists of minimal attributes required to
identify each student uniquely in the Student table-
● ( class , section , roll )
● ( name , address )
NOTE-
● All the attributes in a candidate key are sufficient as well as necessary to identify each tuple
uniquely.
● Removing any attribute from the candidate key fails in identifying each tuple uniquely.
● The value of candidate key must always be unique.
● The value of candidate key can never be NULL.
● It is possible to have multiple candidate keys in a relation.
● Those attributes which appears in some candidate key are called as prime attributes.
3. Primary Key-
A primary key is a candidate key that the database designer selects while designing the database.
OR
Candidate key that the database designer implements is called as a primary key.
NOTE-
● The value of primary key can never be NULL.
● The value of primary key must always be unique.
● The values of primary key can never be changed i.e. no updation is possible.
● The value of primary key must be assigned when inserting a record.
● A relation is allowed to have only one primary key.
Subject : DBMS Dr.Anirudh Mangore
Chapter : 3
Relational Model & Relational Algebra
Remember-
Subject : DBMS Dr.Anirudh Mangore
Chapter : 3
Relational Model & Relational Algebra
4. Foreign Key-
● Foreign keys are the column of the table which is used to point to the primary key of another
table.
● In a company, every employee works in a specific department, and employee and department
are two different entities. So we can't store the information of the department in the employee
table. That's why we link these two tables through the primary key of one table.
● We add the primary key of the DEPARTMENT table, Department_Id as a new attribute in the
EMPLOYEE table.
● Now in the EMPLOYEE table, Department_Id is the foreign key, and both the tables are related.
NOTE-
● Foreign key references the primary key of the table.
● Foreign key can take only those values which are present in the primary key of the referenced
relation.
● Foreign key may have a name other than that of a primary key.
● Foreign key can take the NULL value.
● There is no restriction on a foreign key to be unique.
● In fact, foreign key is not unique most of the time.
● Referenced relation may also be called as the master table or primary table.
● Referencing relation may also be called as the foreign table.
Subject : DBMS Dr.Anirudh Mangore
