Database Management System (DBMS)

UNIT-2

Relational Data Model

Relational Data Models: Entities & Attributes, Entity types, Key Attributes, Relationships, Domains, Tuples,
types of Attributes, Relations, Characteristics of Relations, Keys, Attributes of Relation, Relational Database,
Integrity Constraints.

Relational Algebra: Concept and Relational Algebra operations like Select, Project, Join, Division, Union etc.

Relational Database model:

A relational database consists of a collection of tables, each of which is assigned a unique name. When a
relation is thought of as a table of values, each row in the table represents a collection of related data
values. A row represents a fact that typically corresponds to a real-world entity or relationship. The table
name and column names are used to help to interpret the meaning of the values in each row.

For example, consider the instructor table of Figure 1, which stores information about instructors. The
table has four column headers: ID, name, dept name, and salary. Each row of this table records information
about an instructor, consisting of the instructor’s ID, name, dept name, and salary.

Figure 1 The instructor relation.

In the formal relational model terminology, a row is called a tuple, a column header is called an attribute,
and the table is called a relation.

1 DBMS Unit 2: Relational Database Model {Dr. Kuldeep N. Tripathi}

Terminology of Relational Model:

1. Relation: In the relational model the term relation is used to refer to a table.
2. Tuple: The term tuple is used to refer to a row of a table.
3. Attribute: the term attribute refers to a column of a table.
• The set of allowed values for each attribute is called the domain of the attribute
• Attribute values are (normally) required to be atomic; that is, indivisible
• The special value null is a member of every domain. Indicated that the value is “unknown”
• The null value causes complications in the definition of many operations
4. Domain: For each attribute of a relation, there is a set of permitted values, called the domain of
that attribute. Thus, the domain of the salary attribute of the instructor relation is the set of all
possible salary values, while the domain of the name attribute is the set of all possible instructor
names.
5. Relation Schema: Database schema is the logical structure of the database.
6. Database instance is a snapshot of the data in the database at a given instant in time. We use
the term relation instance to refer to a specific instance of a relation, that is, containing a specific
set of rows. The instance of instructor shown in Figure 1 has 12 tuples, corresponding to 12
instructors.

Relation:

Degree of a Relation: Number of attributes in any relation is called as the degree of a relation.

Relation State: Number of records in table selected from the cartesian product.

Intension and Extension: Relational schema is also called as relations intension. The relation itself is
known as extension.

Relation ‘R’
A1 A2 A3 A4 A5

R (A1, A2, A3, A4, A5) – Relation R with attribute A1, A2, A3, A4, and A5.

D1 D2 D3 D4 D5 Domains

r(R) ⊆ D1 × D2 × D3 × D4 × D5

2 DBMS Unit 2: Relational Database Model {Dr. Kuldeep N. Tripathi}

 • A relation is a subset of cartesian product of all the domains.
• A relation is a similar to set and rows are the same as the elements of the set. So no two rows
or tuples can have the same value of all attributes.
• If in any tuple for any attribute value is not applicable or not known, then we use the NULL
value.

Note
• The terms ‘relation’ and ‘table’ are used interchangeably.

• The terms ‘tuple’ and ‘record’ are used interchangeably.

Relational Constraints:

• Relational constraints are the restrictions imposed on the database contents and operations.

• They ensure the correctness of data in the database.

• Integrity constraints are a set of rules. It is used to maintain the quality of information.

• Integrity constraints ensure that the data insertion, updating, and other processes have to be
performed in such a way that data integrity is not affected.

• Thus, integrity constraint is used to guard against accidental damage to the database.

• Constraints in the databases can be categorized into 3 main categories:

(1) Constraints that are applied in the data model is called Implicit constraints.

(2) Constraints that are directly applied in the schemas of the data model, by specifying
them in the DDL (Data Definition Language). These are called as schema-based
constraints or Explicit constraints.

(3) Constraints that cannot be directly applied in the schemas of the data model. We call
these Application based or semantic constraints.

3 DBMS Unit 2: Relational Database Model {Dr. Kuldeep N. Tripathi}

Types of Constraints:

In DBMS, there are following 5 different types of relational constraints-

1. Domain Constraints
2. Key Constraints
3. Entity Integrity Constraints
4. Referential Integrity Constraints
5. Tuple Uniqueness constraint

Domain Constraints:

• Domain constraint defines the domain or set of values for an attribute.

• It specifies that the value taken by the attribute must be the atomic value from its domain.

• Every domain must contain atomic values (smallest indivisible units) it means composite and
multi-valued attributes are not allowed.

• We perform datatype check here, which means when we assign a data type to a column, we
limit the values that it can contain. Eg. If we assign the datatype of attribute age as int, we
can’t give it values other than int datatype.

4 DBMS Unit 2: Relational Database Model {Dr. Kuldeep N. Tripathi}

 Example:

In the above relation, Name is a composite attribute and Phone is a multi-values attribute, so
it is violating domain constraint.

Example: Consider the following Student table

STU_ID Name Age

S001 Akshay 20

S002 Abhishek 21

S003 Shashank 20

S004 Rahul A
Here, value ‘A’ is not allowed since only integer values can be taken by the age attribute.

Key Constraints:

Key constraint specifies that in any relation-

• All the values of primary key must be unique.

• The value of primary key must not be null.

• These are called uniqueness constraints since it ensures that every tuple in the relation should
be unique.

• A relation can have multiple keys or candidate keys (minimal superkey), out of which we
choose one of the keys as primary key, we don’t have any restriction on choosing the primary
key out of candidate keys, but it is suggested to go with the candidate key with less number
of attributes.

• Null values are not allowed in the primary key, hence Not Null constraint is also a part of
key constraint.

5 DBMS Unit 2: Relational Database Model {Dr. Kuldeep N. Tripathi}

Example: Consider the following Student table

STU_ID Name Age

S001 Akshay 20

S001 Abhishek 21

S003 Shashank 20

S004 Rahul 20

This relation does not satisfy the key constraint as here all the values of primary key are not unique.

Entity Integrity Constraints

• Entity integrity constraint specifies that no attribute of primary key must contain a null
value in any relation.

• This is because the presence of null value in the primary key violates the uniqueness
property.

Example: Consider the following Student table

STU_ID Name Age

S001 Akshay 20

S002 Abhishek 21

S003 Shashank 20

NULL Rahul 20

This relation does not satisfy the entity integrity constraint as here the primary key contains a NULL
value.

6 DBMS Unit 2: Relational Database Model {Dr. Kuldeep N. Tripathi}

Referential Integrity Constraints:

• This constraint is enforced when a foreign key references the primary key of a relation.

• It specifies that all the values taken by the foreign key must either be available in the
relation of the primary key or be null.

Tuple Uniqueness Constraint:

Tuple Uniqueness constraint specifies that all the tuples must be necessarily unique in any relation.

Example-01: Consider the following Student table

STU_ID Name Age

S001 Akshay 20

S002 Abhishek 21

S003 Shashank 20

S004 Rahul 20
This relation satisfies the tuple uniqueness constraint since here all the tuples are unique.

Example-02: Consider the following Student table

STU_ID Name Age

S001 Akshay 20

S001 Akshay 20

S003 Shashank 20

S004 Rahul 20

This relation does not satisfy the tuple uniqueness constraint since here all the tuples are not unique.

7 DBMS Unit 2: Relational Database Model {Dr. Kuldeep N. Tripathi}

Keys:

We must have a way to specify how tuples within a given relation are distinguished. This is expressed in
terms of their attributes. That is, the values of the attribute values of a tuple must be such that they can
uniquely identify the tuple. In other words, no two tuples in a relation are allowed to have exactly the same
value for all attributes.

Types of Keys:

a) Super Key
b) Candidate key (Minimal Super Key)
c) Primary Key
d) Alternate Key
e) Foreign key

Super Key: A Super key is a set of one or more attributes that, taken collectively, allow us to identify
uniquely a tuple in the relation.

• 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, sex, 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, sex, age, address, class, section)

• (class, section, roll)
• (class, section, roll, sex)
• (name, address)

Candidate key:

• A minimal super key is called as a candidate key.

• A set of minimal attributes(s) that can identify each tuple uniquely in the given relation is called
as a candidate key.

8 DBMS Unit 2: Relational Database Model {Dr. Kuldeep N. Tripathi}

Example: Consider the following Student schema

Student (roll, name, sex, 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)

Properties of Candidate Keys:

• 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 keys are called as prime attributes.

Primary Key: We shall use the term primary key to denote a candidate key that is chosen by the database
designer as the principal means of identifying tuples within a relation.

• A primary key is a candidate key that the database designer selects while designing the database.
• Candidate key that the database designer implements is called as a primary key.

Properties of Primary Keys;

• 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.

9 DBMS Unit 2: Relational Database Model {Dr. Kuldeep N. Tripathi}

Alternate Key:

• Candidate keys that are left unimplemented or unused after implementing the primary key are
called as alternate keys.
• Unimplemented candidate keys are called as alternate keys.

Foreign key:

• An attribute ‘X’ is called as a foreign key to some other attribute ‘Y’ when its values are dependent
on the values of attribute ‘Y’.
• The attribute ‘X’ can assume only those values which are assumed by the attribute ‘Y’.
• Here, the relation in which attribute ‘Y’ is present is called as the referenced relation.
• The relation in which attribute ‘X’ is present is called as the referencing relation.
• The attribute ‘Y’ might be present in the same table or in some other table.

Example: Consider the following two schemas

10 DBMS Unit 2: Relational Database Model {Dr. Kuldeep N. Tripathi}

Here, t_dept can take only those values which are present in dept_no in Department table since only those
departments actually exist.

Properties of Foreign Keys:

• 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.

11 DBMS Unit 2: Relational Database Model {Dr. Kuldeep N. Tripathi}

Counting the number of Super Keys (SKs) Possible:

Example 1: R (A1, A2, A3), CK = {A1}

SK = {A1, A1A2, A1A3, A1A2A3}

R (A1, A2, A3)

X 2 * 2 = 4 Super Keys (SKs)

(No. of Supersets)

Example 2: R (A1, A2, A3, ………., An), CK = {A1}

R (A1, A2, A3, ……….. , An)

X 2 2 2

No. of Super Keys = 2 × 2 × 2 ×……. (n-1) times = 2n-1

Example 3: R (A1, A2, A3, ………., An), CK = {A1A2}

R (A1, A2, A3, ………., An)

Leave 2n-2

Super Keys = 2n-2

If CK = {A1A2A3} then Super Keys = 2n-3

Example: R (A1, A2, A3, ………., An), CK = {A1, A2}

12 DBMS Unit 2: Relational Database Model {Dr. Kuldeep N. Tripathi}

Conversion of E-R model to Relational Model: We convert the E-R model into relational model before
implementation. There are following 7 steps that are used to convert E-R model into corresponding
relational model.

1. Conversion of Strong Entity into relation: For every strong entity in a E-R model we form one relation
(Table) into relational model.

• For every entity in ER model, we construct a relation in relational model.

• For every simple attribute we have one column (attribute) in a relational model (Roll-No,
Marks).
• Do not represent the composite attributes as it is. Take the simplest part of the composite
attribute and represent in the relation. For e.g. instead of SName take FName & LName in
the relation.
• No multivalued attributes will be included in the table.
• If there is any prime attribute (Primary key) in the ER model then represent the prime
attribute in the table also.

Student
Roll-No Marks FName LName

Primary Key of Relation Student: Roll-No

2. Conversion of weak entity into relation: Any weak entity can be converted into relation as per below
rules.

13 DBMS Unit 2: Relational Database Model {Dr. Kuldeep N. Tripathi}

 • Weak Entity do not have a primary key. We have a partial key with weak entity.
• Suppose here DName is a partial key in dependent entity. That means for a particular
employee if name is given then we can identify uniquely.
• Create a table for weak entity with all the attributes and add all the primary key attributes of
the owner entity into the weak entity table.
• The added primary key of owner entity becomes a foreign key for the weak entity. And in
weak entity relation foreign key with partial key becomes the primary key.
• Identifying relationship can be taken care with the help of the foreign key.

Employee
E-No EName

Primary Key of Employee Relation: E-No

Dependent PK
E-No DName Age Relation

Primary Key of Dependent Relation: E-No + Name

Foreign Key of Dependent Relation: E-No

3. Convert the relationship (1-to-1) into a relation (Table): Any relationship (1-to-1) can be converted
as given below:

14 DBMS Unit 2: Relational Database Model {Dr. Kuldeep N. Tripathi}

 • Create table for employee and Department entity
• Here no weak entity

Employee
E-No EName

Department
D-No DName

• In order to represent a relationship between two entities take the primary key of one relation
and include it with other relation as a foreign key.
• In case of 1-to-1 relationship, we can add foreign key at any side, but preferably include on
the total participation side.
Final Relational Model:
Employee
E-No EName

Primary Key of Employee Relation: E-No

Department Rename it
D-No DName E-No (Manger-No)

Primary Key of Department Relation: D-No

Foreign Key of Department Relation: E-No

If relationship is also having some attributes:

15 DBMS Unit 2: Relational Database Model {Dr. Kuldeep N. Tripathi}

 • If relationship also have some attributes, then add those attributes towards the total
participation side in 1-to-1 relationship.

Department
D-No DName Manger-No Start-Date

If relationship (1-to-1) is having total participation from both the entities:

• We can use the same method as the above. Or one better method is to join the both the
tables.
• Means in 1-to-1 relationship, if both side (entities) has the total participation then we can
join both the tables and form one table.

4. Convert the 1-to-M relationship into a relation (Table): 1-to-M relationship can be converted as
follows:

16 DBMS Unit 2: Relational Database Model {Dr. Kuldeep N. Tripathi}

 Employee
E-No EName

Department
D-No DName

• In this case make a foreign key towards M (Many) side.

• And if nay attribute with relationship, add it to M (Many) side.
Final Relational Model:
Employee
E-No EName D-No Start-Date

Primary Key of Employee Relation: E-No

Foreign Key of Employee Relation: D-No
Department
D-No DName

Primary Key of Department Relation: D-No

5. Convert the M-to-N relationship into a relation (Table):

17 DBMS Unit 2: Relational Database Model {Dr. Kuldeep N. Tripathi}

 • In M-to-N relationship we cannot use the foreign key to represent the relationship into the
either side.
• In this case we have to create a new table, which contains the primary keys of both the
participating entities as the foreign key.
• For the new relation (table) the primary key will be the combination of all the foreign keys.
• And the attribute with the relationship will be added to new table.

Employee
E-No EName

Project
P-No PName

Works-for
E-No P-No Hours

Primary Key of Employee Relation: E-No

Primary Key of Project Relation: P-No
Primary Key of Works-for Relation: E-No + P-No
Foreign Key for Works-for Relation: E-No & P-No

6. How to Represent Multivalued attributes in a relation:

18 DBMS Unit 2: Relational Database Model {Dr. Kuldeep N. Tripathi}

 • For the multivalued attribute we have to create one newer table. We can not represent
multivalued attribute in the same table. And add a Primary Key of original entity as a foreign
key into the new table.
• Primary key of new table is the combination of both the attributes.

Student
S-No FName LName

Relation for Mobile_Number

S-No Mobile_Number

Primary key for Studetn relation: S-No

Primary key for Mobile_Number relation: S-No + Mobile_Number

Foreign key for Mobile_Number relation: S-No

7. How to convert M-ary relationship into table: (More than 2 entities participating in a relationship)
M-Ary means M entities participating in a relationship.

19 DBMS Unit 2: Relational Database Model {Dr. Kuldeep N. Tripathi}

 • Create a separate table to represent the relationship and add the primary key of all the tables as
a foreign key.
• And Primary key of new table is the combination of all the attributes presents (Foreign Keys).
• If relation also have some attributes, then simply add that attribute in to the new table.

Table for Relationship R

A1 A2 A3 A4

Primary key: A1+A2+A3+A4

Foreign key: A1, A2, A3, A4

PRACTICE PROBLEMS BASED ON CONVERTING ER DIAGRAM TO TABLES-

Problem-01:

Find the minimum number of tables required for the following ER diagram in relational model-

Solution-

20 DBMS Unit 2: Relational Database Model {Dr. Kuldeep N. Tripathi}

Applying the rules, minimum 3 tables will be required-
• MR1 (M1 , M2 , M3 , P1)
• P (P1 , P2)
• NR2 (P1 , N1 , N2)

Problem-02:

Find the minimum number of tables required to represent the given ER diagram in relational model-

Solution-

Applying the rules, minimum 4 tables will be required-

• AR1R2 (a1 , a2 , b1 , c1)
• B (b1 , b2)
• C (c1 , c2)
• R3 (b1 , c1)

21 DBMS Unit 2: Relational Database Model {Dr. Kuldeep N. Tripathi}

Problem-03:

Find the minimum number of tables required to represent the given ER diagram in relational model-

Solution-

Applying the rules, minimum 5 tables will be required-

• BR1R4R5 (b1 , b2 , a1 , c1 , d1)

• A (a1 , a2)
• R2 (a1 , c1)
• CR3 (c1 , c2 , d1)
• D (d1 , d2)

Problem-04:

Find the minimum number of tables required to represent the given ER diagram in relational model-

22 DBMS Unit 2: Relational Database Model {Dr. Kuldeep N. Tripathi}

Solution-

Applying the rules, minimum 3 tables will be required-

• E1 (a1 , a2)
• E2R1R2 (b1 , b2 , a1 , c1 , b3)
• E3 (c1 , c2)
Problem-05:

Find the minimum number of tables required to represent the given ER diagram in relational model-

Solution-

23 DBMS Unit 2: Relational Database Model {Dr. Kuldeep N. Tripathi}

Applying the rules that we have learnt, minimum 6 tables will be required-
• Account (Ac_no , Balance , b_name)
• Branch (b_name , b_city , Assets)
• Loan (L_no , Amt , b_name)
• Borrower (C_name , L_no)
• Customer (C_name , C_street , C_city)
• Depositor (C_name , Ac_no)

24 DBMS Unit 2: Relational Database Model {Dr. Kuldeep N. Tripathi}