Course code : CSE2007

Course title : Database Management Systems

Module-2

Relational Data Model

27-01-2026 Dr. V. Srilakshmi 1

 Module-2
• The Relational Data Model and Relational Database
Constraints, key, null, referential integrity constraints,
Relational Database Design using ER-to-Relational
Mapping, Relational Algebra and Relational calculus.

27-01-2026 Dr. V. Srilakshmi 2

 Relational Data Model
• The relational Model was proposed by E.F. Codd to model data in the form of
relations or tables. After designing the conceptual model of the Database
using ER diagram, we need to convert the conceptual model into a relational
model which can be implemented using any RDBMS language like Oracle SQL,
MySQL, etc. So we will see what the Relational Model is.

What is the Relational Model?

• The 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 1.
27-01-2026 Dr. V. Srilakshmi 3
Relational Model Concepts
Relational Model
• The relational model represents the database as a collection of relations.

• Each relation resembles a Table of Values.

• A relation typically contains a set of rows.

• The data elements in each row represent certain facts that correspond to a
real-world entity or relationship.

-In the formal model, rows are called tuples.

• Each column has a column header that specifies how to interpret the Data
Values in each row.

27-01-2026 Dr. V. Srilakshmi 4

Relational Model
Relational Model in DBMS

[Link] Model stores the data into tables (relations).

[Link] makes data sorting and data access easier.

[Link] a standard way to organize data in databases.

27-01-2026 Dr. V. Srilakshmi 5
 Student IMPORTANT TERMINOLOGIES

• Attribute: Attributes are the properties that define a

relation. e.g.; ROLL_NO, NAME
• Relation Schema: A relation schema represents the
name of the relation with its attributes. e.g.; STUDENT
(ROLL_NO, NAME, ADDRESS, PHONE, and AGE) is the
relation schema for STUDENT. If a schema has more
than 1 relation, it is called Relational Schema.
• Tuple: Each row in the relation is known as a tuple.
The above relation contains 4 tuples
• Cardinality: The number of tuples in a relation is • Relation Instance: The set of tuples of a relation at a
known as cardinality. The STUDENT relation defined particular instance of time is called a relation instance.
above has cardinality 4. Table 1 shows the relation instance of STUDENT at a
• Column: The column represents the set of values for a particular time. It can change whenever there is an
particular attribute. The column ROLL_NO is extracted insertion, deletion, or update in the database.
from the relation STUDENT.’ • Degree: The number of attributes in the relation is
• NULL Values: The value which is not known or known as the degree of the relation.
unavailable is called a NULL value. It is represented by The STUDENT relation defined above has degree 5.
blank space. e.g.; PHONE of STUDENT having ROLL_NO
4 is NULL.
27-01-2026 Dr. V. Srilakshmi 6
Relational Model
Relation

A relation consists of
• Relational schema
• Relation instance

27-01-2026 Dr. V. Srilakshmi 7

Relational Model
Formal Definitions -Schema
The Schema(or Description) of a Relation:

• Denoted by R(A1, A2, .....An)

• R is the name of the relation

• The attributes of the relation are A1, A2, ..., An

Relation schema describes the column heads for the table

Example
CUSTOMER (Cust-id, Cust-name, Address, Phoneno)
- CUSTOMER is the relation name
- Defined over the four attributes: Cust-id, Cust-name, Address, Phoneno

Each attribute
27-01-2026
has a domain or a set of valid values. For example, the domain of Cust-id is 6-digit numbers.
Dr. V. Srilakshmi 8
Relational Model
Formal Definitions -Schema
COMPANY Database Schema

27-01-2026 Dr. V. Srilakshmi 9

Relational Model
Formal Definitions - Instance
A relational instance denoted as r is a collection of tuples for a given relational schema at a
specific point of time.

• A relation state r to the relations schema R(A1,A2…,An) also denoted by r®

is a set of n-tuples R{t1,t2,…tm}

• Where each n-tuple is an ordered list of n-values.

T=<v1,v2,….vn>

27-01-2026 Dr. V. Srilakshmi 10

Keys in DBMS
• Keys play an important role in the relational database.

• It is used to uniquely identify any record or row of data from the table. It is also used to
establish and identify relationships between tables.

For example, ID is used as a key in the Student table because it is unique for each student. In the PERSON
table, passport_number, license_number, SSN are keys since they are unique for each person.

27-01-2026 Dr. V. Srilakshmi 11

Keys in DBMS

27-01-2026 Dr. V. Srilakshmi 12

Keys in DBMS
1. Candidate Key

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

• For any given relation,

- It is possible to have multiple candidate keys.

- There exists no general formula to find the total number of candidate keys of a given relation.

Example
Student ( roll , name , sex , age , address , class , section )

Examples of candidate keys-

- ( class , section , roll )
- ( name , address )

27-01-2026 Dr. V. Srilakshmi 13

Keys in DBMS
2. Primary key

• It is the first key used to identify one and only one instance of an entity uniquely. An entity can contain
multiple keys. The key which is most suitable from those lists becomes a primary key.

27-01-2026 Dr. V. Srilakshmi 14

Keys in DBMS
3. Super Key

• Super key is an attribute set that can uniquely identify a tuple. A super key is a superset of a candidate
key.

4. Foreign key
• Foreign keys are the column of the table used to point to the primary key of another table.

27-01-2026 Dr. V. Srilakshmi 15

Keys in DBMS
5. Alternate key

• There may be one or more attributes or a combination of attributes that uniquely identify each tuple in
a relation. These attributes or combinations of the attributes are called the candidate keys. One key is
chosen as the primary key from these candidate keys, and the remaining candidate key, if it exists, is
termed the alternate key.

27-01-2026 Dr. V. Srilakshmi 16

Keys in DBMS
6. Composite key

• Whenever a primary key consists of more than one attribute, it is known as a composite key. This key is
also known as Concatenated Key.

27-01-2026 Dr. V. Srilakshmi 17

 Keys in DBMS
7. Partial Key
• The set of attributes that are used to uniquely identify a weak entity set is
called the Partial key. Only a bunch of the tuples can be identified using the
partial keys.
Example: consider the Apartment as a weak entity and Building as a strong entity. The apartment number
can be used to identify apartments within a particular building but is not globally unique across all
buildings. Hence, it is a partial key.

Entity Attribute Key Type Reason

Unique within a building,

Apartment Apartment Number Partial Key
but not globally unique

Uniquely identifies the

Building Building ID Primary Key
building
27-01-2026 Dr. V. Srilakshmi 18
Keys in DBMS
8. Secondary Key
A secondary key is a candidate key that is not selected as the primary key. It still uniquely identifies a tuple in
the relation, but is not chosen as the main identifier.
Example: In the schema Student(Id, email, enroll_no, name), the candidate keys are Id, email, and enroll_no.
If Id is chosen as the primary key, then email and enroll_no become secondary keys.
In this example, Id is the primary key, and email and enroll_no are secondary keys.

Student Name Id Email Enroll No Key Type

Alice 101 alice@[Link] E1234 Primary Key

Bob 102 bob@[Link] E1235 Secondary Key

Charlie 103 charlie@[Link] E1236 Secondary Key

27-01-2026 Dr. V. Srilakshmi 19

Difference Between Primary Key and Unique
Key

27-01-2026 Dr. V. Srilakshmi 20

 Constraints

27-01-2026 Dr. V. Srilakshmi 21

 Relational Database Constraints
• On modelling the design of the relational database we can put some restrictions
like what values are allowed to be inserted in the relation, what kind of
modifications and deletions are allowed in the relation. These are the restrictions
we impose on the relational database.
• In models like ER models, we did not have such features.
• Constraints in the databases can be categorized into 3 main categories:
[Link] that are applied in the data model is called Implicit constraints.
[Link] 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.
[Link] that cannot be directly applied in the schemas of the data model. We
call these Application based or semantic constraints.
• So here we will deal with Implicit constraints.
27-01-2026 Dr. V. Srilakshmi 22
Integrity Constraint Over Relations in DBMS
• Integrity constraints are rules that help to maintain the accuracy and
consistency of data in a database.

Different types of Integrity Constraints

• Domain Constraint

• Entity Integer Constraint

• Referential Integrity Constraint

• Key Constraints

27-01-2026 Dr. V. Srilakshmi 23

Integrity Constraint Over Relations in DBMS
1. Domain Constraint

• 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.
• Domain Constraints help us to enter the data into the table according to the particular data
type.
• Example: If we assign the datatype of attribute age as int, we cant give it values other then
int datatype.

27-01-2026 Dr. V. Srilakshmi 24

 1. Domain Constraint

• Domain Constraints specify two things - Data Type and constraints such as NOT
NULL, PRIMARY KEY, FOREIGN KEY, CHECK, etc.

a. Not Null Domain Constraint

• The Not Null Constraint is used which restricts a column not to accepting the missing null values
by default. This means that you cannot insert a new column or record without assigning values in
the fields of that column.

Example:
Create table Students (Student_id NUMBER not null, Student_name varchar(30), marks
NUMBER);
27-01-2026 Dr. V. Srilakshmi 25
Integrity Constraint Over Relations in DBMS
[Link] Constraint
b. Check Domain Constraints
• This constraint works as a filter that limits the data in some range. It
ensures that the data is entered into the table only after satisfying some
conditions. Whenever a set of values is inserted into the record, it is verified
under the condition written in the check clause.

Example:
Create table Students (Student_id NUMBER not null, Student_name varchar(30), marks
NUMBER check(marks > 35))

27-01-2026 Dr. V. Srilakshmi 26

Integrity Constraint Over Relations in DBMS
2. Entity Integrity Constraint
PRIMARY KEY Constraint

• Entity Integrity Constraint is used to ensure that the primary key cannot be null. A primary
key is used to identify individual records in a table and if the primary key has a null value,
then we can't identify those records.

Create table Students (Student_ID NUMBER not null, Student_name varchar(30), marks NUMBER,
PRIMARY KEY (Student_ID))

CREATE TABLE Persons (

ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int,

CONSTRAINT PK_Person PRIMARY KEY (ID,LastName)

);
27-01-2026 Dr. V. Srilakshmi 27
Integrity Constraint Over Relations in DBMS
2. Entity Integrity Constraint
• Entity Integrity constraints says that no primary key can take NULL value, since
using primary key we identify each tuple uniquely in a relation.

Example:

Explanation:
In the above relation, EID is made primary key, and the primary key cant take NULL
values but in the third tuple, the primary key is null, so it is a violating Entity
Integrity constraints.

27-01-2026 Dr. V. Srilakshmi 28

 Integrity Constraint Over Relations in DBMS
2. Entity Integrity Constraint
• 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.

Example:

Explanation:
In the above table, EID is the primary key, and first and the last tuple has the same value in
EID ie 01, so it is violating the key constraint.
27-01-2026 Dr. V. Srilakshmi 29
Integrity Constraint Over Relations in DBMS
3. Referential Integrity Constraint:
Foreign key constraint
• The Referential integrity constraints is specified between two relations or tables and used to maintain the consistency
among the tuples in two relations.

• A referential integrity constraint is also known as foreign key constraint. A foreign key is a key whose
values are derived from the Primary key of another table.
CREATE TABLE Student (Roll int PRIMARY KEY, Name varchar(25) , Course varchar(10) );

CREATE TABLE Subject (Roll int references Student, SubCode int, SubName varchar(10) );

There are two referential integrity constraint:

Insert Constraint: Value cannot be inserted in CHILD Table if the value is not lying in MASTER Table.

Delete Constraint: Value cannot be deleted from MASTER Table if the value is lying in CHILD Table.

27-01-2026 Dr. V. Srilakshmi 30

Integrity Constraint Over Relations in DBMS
3. Referential Integrity Constraint

2. Foreign key constraint

27-01-2026 Dr. V. Srilakshmi 31

Integrity Constraint Over Relations in DBMS
4. Key constraint

• In DBMS, a key is used to uniquely identify an entity in an entity set. An entity set can have multiple
keys but out only one can be a primary key. This primary key should not be null and must be unique.
Although it can contain a null and a non-null unique value.

Other constraints
DEFAULT: provides a default value to a column if none is specified.

CREATE TABLE emp (ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName
varchar(255), Age int, City varchar(255) DEFAULT ‘Vijayawada’ );

UNIQUE: provides unique/distinct values to specified columns.

CREATE TABLE Persons ( ID int UNIQUE, LastName varchar(255) NOT NULL, FirstName varchar(255),
Age int );
27-01-2026 Dr. V. Srilakshmi 32
 ER to Relational model mapping
ER Model, when conceptualized into diagrams, gives a good overview of
entity-relationship, which is easier to understand. ER diagrams can be
mapped to relational schema, that is, it is possible to create relational
schema using ER diagram. We cannot import all the ER constraints into
relational model, but an approximate schema can be generated.
There are several processes and algorithms available to convert ER Diagrams
into Relational Schema. Some of them are automated and some of them are
manual. We may focus here on the mapping diagram contents to relational
basics.
ER diagrams mainly comprise of −
• Entity and its attributes
• Relationship, which is association among entities.
27-01-2026 Dr. V. Srilakshmi 33
MAPPING ENTITY
• An entity is a real-world object with some attributes.

Mapping Process (Algorithm):

• Create table for each entity.
• Entity's attributes should become fields of tables with their respective data
types.
• Declare primary key.
27-01-2026 Dr. V. Srilakshmi 34
MAPPING ENTITY
Logical DB Design: ER To Relational

27-01-2026 Dr. V. Srilakshmi 35

GENERAL EXAMPLS
(E-R to DATABASE)
Strong Entity set with Simple attributes

27-01-2026 Dr. V. Srilakshmi 36

GENERAL EXAMPLS
(E-R to DATABASE)
Strong Entity Set With Composite Attributes

Composite attribute

27-01-2026 Dr. V. Srilakshmi 37

GENERAL EXAMPLS
(E-R to DATABASE)
Strong Entity Set With Multi Valued Attributes

27-01-2026 Dr. V. Srilakshmi 38

Mapping Weak Entity Sets
A weak entity set is one which does not have any primary key associated with it.

Mapping Process
i. For each weak entity type, create a corresponding relation that includes all the simple attributes
ii. Add as a foreign key all of the primary key attribute(s) in the entity corresponding to the owner entity
type.
iii. The primary key is the combination of all the primary key attributes from the owner and the partial key of
the weak entity, if any.
27-01-2026 Dr. V. Srilakshmi 39
27-01-2026 Dr. V. Srilakshmi 40
• Derived attributes
• Coded separately in SQL as a view. They are not an attribute in a basic
relation table.

27-01-2026 Dr. V. Srilakshmi 41

Mapping Relationship
A relationship is an association among entities.

Mapping Process
• Create table for a relationship.
• Add the primary keys of all participating Entities as fields of table with their respective data
types.
• If relationship has any attribute, add each attribute as field of table.
• Declare a primary key composing all the primary keys of participating entities.
• Declare all foreign key constraints.
27-01-2026 Dr. V. Srilakshmi 42
Relationship Sets to Tables

27-01-2026 Dr. V. Srilakshmi 43

GENERAL EXAMPLS
(E-R to DATABASE)

Relationship Set to Table conversion

• If we consider the overall ER diagram, three

tables will be required in the relational model.

• One table for the entity set “Employee”.

• One table for the entity set “Department”.

• One table for the relationship set “Works”.

Schema : Works in ( Emp_no , Dept_id , Experience )

27-01-2026 Dr. V. Srilakshmi 44
GENERAL EXAMPLS
(E-R to DATABASE)
Binary Relationships With Cardinality Ratios
For Binary Relationship With Cardinality Ratio m:n

Here, three tables will be required-

1.A ( a1 , a2 )

2.R ( a1 , b1 )

3.B ( b1 , b2 )
27-01-2026 Dr. V. Srilakshmi 45
GENERAL EXAMPLS
(E-R to DATABASE)
Binary Relationships With Cardinality Ratios
Binary Relationship With Cardinality Ratio 1:n

Here, two tables will be required-

The combined table will be drawn for the entity set
1.A ( a1 , a2 ) B and relationship set R.
[Link] ( a1 , b1 , b2 )

27-01-2026 Dr. V. Srilakshmi 46

GENERAL EXAMPLS
(E-R to DATABASE)
Binary Relationships With Cardinality Ratios
Binary Relationship With Cardinality Ratio m:1

Here, two tables will be required-

The combined table will be drawn for the entity set
[Link] ( a1 , a2 , b1 ) A and relationship set R.

2.B ( b1 , b2 )

27-01-2026 Dr. V. Srilakshmi 47

GENERAL EXAMPLS
(E-R to DATABASE)
Binary Relationships With Cardinality Ratios
Binary Relationship With Cardinality Ratio 1:1

Here, two tables will be

required-Way-01:

[Link] ( a1 , a2 , b1 )
2.B ( b1 , b2 ) • The combined table will be drawn for the entity set A and
Way-02: relationship set R.
1.A ( a1 , a2 )
[Link] ( a1 , b1 , b2 )

27-01-2026 Dr. V. Srilakshmi 48

Generalization, Specialization in ER Model
• Generalization
• Process of extracting common properties from a set of entities and
creating a generalized entity from it. It is a bottom-up approach in which
two or more entities can be generalized to a higher-level entity if they have
some attributes in common.

27-01-2026 Dr. V. Srilakshmi 49

• Specialization
• In specialization, an entity is divided into sub-entities based on its
characteristics. It is a top-down approach where the higher-level entity is
specialized into two or more lower-level entities.

27-01-2026 Dr. V. Srilakshmi 50

Mapping Hierarchical Entities
ER specialization or generalization
comes in the form of hierarchical
entity sets.

Mapping Process

• Create tables for all higher-level entities.

• Create tables for lower-level entities.
• Add primary keys of higher-level entities in the
table of lower-level entities.
• In lower-level tables, add all other attributes of
lower-level entities.
• Declare primary key of higher-level table and the
primary key for lower-level table.
• Declare foreign key constraints.

27-01-2026 Dr. V. Srilakshmi 51

• Student (Regno, name)
• PosGrad (Regno, supervisor)
• UnderGrad (Regno, points)

27-01-2026 Dr. V. Srilakshmi 52