DBMS Module-2

DATABASE MANAGEMENT SYSTEMS
COURSE CODE: CSE-2007

MODULE – 2
(Relational Data Model)
By:
Dr. Nagendra Panini Challa
Assistant Professor, Senior Grade 2
SCOPE, VIT-AP University, India
AGENDA
 The Relational Data Model
 Relational Database Constraints
 key, null, referential integrity
constraints
 Relational Database Design using ER-
to-Relational Mapping
 Relational Algebra and
 Relational calculus
Database Management Systems (DBMS), SCOPE, VIT-AP University, India 14/11/2024 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.

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
• Relation Instance: The set of tuples of a relation
• Cardinality: The number of tuples in a relation is at a particular instance of time is called a
known as cardinality. The STUDENTrelation relation instance. Table 1 shows the relation
defined above has cardinality 4. instance of STUDENT at a particular time. It can
• Column: The column represents the set of values change whenever there is an insertion, deletion,
for a particular attribute. The column ROLL_NO is or update in the database.
extracted from the relation STUDENT.’ • Degree: The number of attributes in the relation
• NULL Values: The value which is not known or is known as the degree of the relation.
unavailable is called a NULL value. It is The STUDENT relation defined above has degree
represented by blank space. e.g.; PHONE of 5.
STUDENT having ROLL_NO 4 is NULL.
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:
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.
 So here we will deal with Implicit constraints.
Mainly Constraints on the relational database are of 4 types:
 Domain constraints
 Key constraints
 Entity Integrity constraints
 Referential integrity constraints

1. Domain constraints:
2. Every domain must contain atomic values(smallest indivisible units) it means

composite and multi-valued attributes are not allowed.
2. 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 cant give it values other then int
datatype.
Example:

2. Key Constraints or Uniqueness Constraints :
 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.
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.
3. Entity Integrity Constraints :
 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.

4. Referential Integrity Constraints :
 The Referential integrity constraints is specified between two relations or tables and used to
maintain the consistency among the tuples in two relations.
 This constraint is enforced through foreign key, when an attribute in the foreign key of
relation R1 have the same domain(s) as the primary key of relation R2, then the foreign key
of R1 is said to reference or refer to the primary key of relation R2.
 The values of the foreign key in a tuple of relation R1 can either take the values of the
primary key for some tuple in relation R2, or can take NULL values, but can’t be empty.
 Example
Explanation:
In the above, DNO of the first relation is the foreign key, and DNO in the second relation is the
primary key. DNO = 22 in the foreign key of the first table is not allowed since DNO = 22
is not defined in the primary key of the second relation. Therefore, Referential integrity
constraints is violated here
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.

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

MAPPING WEAK ENTITY
SETS
A weak entity set is one which does not have any primary key associated
with it.
Mapping Process
• Create table for weak entity set.

• Add all its attributes to table as field.
• Add the primary key of identifying entity
set.
• Declare all foreign key constraints.
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.
Case 1: Binary Relationship with 1:1 cardinality with total participation of an entity
A person has 0 or 1 passport number and Passport

is always owned by 1 person. So it is 1:1
cardinality with full participation constraint from
Passport.
First Convert each entity and relationship to

tables:
Person table corresponds to Person Entity with key
as Per-Id. Similarly Passport table corresponds to
Passport Entity with key as Pass-No. Has Table
represents relationship between Person and
Passport (Which person has which passport). So it
will take attribute Per-Id from Person and Pass-No
from Passport.
As we can see from Table 1, each Per-Id and Pass-No has only one entry in Has Table. So we can merge
all three tables into 1 with attributes shown in Table 2. Each Per-Id will be unique and not null. So it will
be the key. Pass-No can’t be key because for some person, it can be NULL.

Case 2: Binary Relationship with 1:1 cardinality and partial participation of both entities
A male marries 0 or 1 female and vice versa as

well. So it is 1:1 cardinality with partial
participation constraint from both. First Convert
each entity and relationship to tables. Male table
corresponds to Male Entity with key as M-Id.
Similarly Female table corresponds to Female
Entity with key as F-Id. Marry Table represents
relationship between Male and Female (Which
Male marries which female). So it will take
attribute M-Id from Male and F-Id from Female.
As we can see from Table 3, some males and some females do not marry. If we merge 3 tables into 1,
for some M-Id, F-Id will be NULL. So there is no attribute which is always not NULL. So we can’t
merge all three tables into 1. We can convert into 2 tables. In table 4, M-Id who are married will have
F-Id associated. For others, it will be NULL. Table 5 will have information of all females. Primary Keys
have been underlined.
Note:
Binary relationship
with 1:1 cardinality
will have 2 table if
partial participation
of both entities in
the relationship. If
atleast 1 entity has
total participation,
number of tables
required will be 1.

Case 3: Binary Relationship with n: 1 cardinality
In this scenario, every student can enroll only in

one elective course but for an elective course
there can be more than one student. First
Convert each entity and relationship to tables.
Student table corresponds to Student Entity with
key as S-Id.
Similarly Elective_Course table corresponds to
Elective_Course Entity with key as E-Id. Enrolls
Table represents relationship between Student
and Elective_Course (Which student enrolls in
which course). So it will take attribute S-Id from
Student and E-Id from Elective_Course.
As we can see from Table 6, S-Id is not repeating in Enrolls Table. So it can be considered as a key of
Enrolls table. Both Student and Enrolls Table’s key is same; we can merge it as a single table. The
resultant tables are shown in Table 7 and Table 8. Primary Keys have been underlined.

Case 4: Binary Relationship with m: n cardinality
In this scenario, every student can enroll in more

than 1 compulsory course and for a compulsory
course there can be more than 1 student. First
Convert each entity and relationship to tables.
Student table corresponds to Student Entity with
key as S-Id.
Similarly Compulsory_Courses table corresponds to

Compulsory Courses Entity with key as C-Id. Enrolls
Table represents relationship between Student and
Compulsory_Courses (Which student enrolls in
which course). So it will take attribute S-Id from
Person and C-Id from Compulsory_Courses.
As we can see from Table 9, S-Id and C-Id both are repeating in Enrolls Table. But its combination is
unique; so it can be considered as a key of Enrolls table. All tables’ keys are different, these can’t be
merged. Primary Keys of all tables have been underlined.

Case 5: Binary Relationship with weak entity
In this scenario, an employee can have

many dependents and one dependent can
depend on one employee. A dependent
does not have any existence without an
employee (e.g; you as a child can be
dependent of your father in his company).
So it will be a weak entity and its
participation will always be total. Weak
Entity does not have key of its own. So its
key will be combination of key of its
identifying entity (E-Id of Employee in this
case) and its partial key (D-Name).
First Convert each entity and relationship to tables. Employee table corresponds to Employee
Entity with key as E-Id. Similarly Dependents table corresponds to Dependent Entity with key as
D-Name and E-Id. Has Table represents relationship between Employee and Dependents (Which
employee has which dependents). So it will take attribute E-Id from Employee and D-Name from
Dependents.
RELATIONAL ALGEBRA
 Relational algebra is a procedural query language. It gives a step by step
process to obtain the result of the query. It uses operators to perform
queries.
Types:

1. Select Operation:
 The select operation selects tuples that satisfy a given predicate.
 It is denoted by sigma (σ).
Notation: σ p(r)
Where:
 σ is used for selection prediction
r is used for relation
p is used as a propositional logic formula which may use connectors like: AND OR and
NOT. These relational can use as relational operators like =, ≠, ≥, <, >, ≤.

For example: LOAN
Relation
σ BRANCH_NAME="perryride"(LOAN)

Examples:
Output – Selects tuples from Tutorials where topic =

‘Database’.
Output – Selects tuples from Tutorials where the topic is ‘Database’ and ‘author’ is
guru99.
Output – Selects tuples from Customers where sales is greater than

50000

2. Project Operation:
 This operation shows the list of those attributes that we wish to appear in
the result. Rest of the attributes are eliminated from the table.
 It is denoted by ∏.
Notation: ∏ A1, A2, An (r)

Where
 A1, A2, A3 is used as an attribute name of relation r.

Example: CUSTOMER RELATION
∏ NAME, CITY (CUSTOMER)

3. Union Operation:
 Suppose there are two tuples R and S. The union operation contains all the
tuples that are either in R or S or both in R & S.
 It eliminates the duplicate tuples. It is denoted by ∪.
Notation: R ∪ S
A union operation must hold the following condition:
 R and S must have the attribute of the same number.
 Duplicate tuples are eliminated automatically.

∏ CUSTOMER_NAME (BORROW) ∪ ∏ CUSTOMER_NAME (DEPOSITOR
)

4. Set Intersection:
 Suppose there are two tuples R and S. The set intersection operation
contains all tuples that are in both R & S.
 It is denoted by intersection ∩.
Notation: R ∩ S
Example: Using the above DEPOSITOR table and BORROW table
Input:
∏ CUSTOMER_NAME (BORROW) ∩ ∏ CUSTOMER_NAME (DEPOSITOR)

5. Set Difference:
 Suppose there are two tuples R and S. The set intersection operation
contains all tuples that are in R but not in S.
 It is denoted by intersection minus (-).
Notation: R - S
Example: Using the above DEPOSITOR table and BORROW table
Input:
∏ CUSTOMER_NAME (BORROW) - ∏ CUSTOMER_NAME (DEPOSITOR)

6. Cartesian product
 The Cartesian product is used to combine each row in one table with each
row in the other table. It is also known as a cross product.
 It is denoted by X.
Notation: E X D

7. Rename Operation:
The rename operation is used to rename the output relation. It is denoted
by rho (ρ).
Example: We can use the rename operator to rename STUDENT relation to

STUDENT1.
ρ(STUDENT1, STUDENT)

JOINS
 Join operation is essentially a cartesian product followed by a selection criterion.
 Join operation denoted by ⋈.
 JOIN operation also allows joining variously related tuples from different relations.
Types of JOIN:
Various forms of join operation are:
 Inner Joins
 Outer join
 Left Outer Join
 Right Outer Join
 Full Outer Join

SQL | Join (Inner, Left, Right and Full Joins)
SQL Join statement is used to combine data or rows from two or more tables based on a
common field between them. Different types of Joins are as follows:
INNER JOIN,LEFT JOIN,RIGHT JOIN,FULL JOIN
UNION, INTERSECT, AND EXCEPT
What is Relational Calculus?
Before understanding Relational calculus in DBMS, we need to understand Procedural

Language and Declarative Langauge.
• Procedural Language - Those Languages which clearly define how to get the required
results from the Database are called Procedural Language. Relational algebra is a
Procedural Language.
• Declarative Language - Those Language that only cares about What to get from the
database without getting into how to get the results are called Declarative
Language. Relational Calculus is a Declarative Language.
So Relational Calculus is a Declarative Language that uses Predicate Logic or First-Order Logic
to determine the results from Database.

Types of Relational Calculus in DBMS
Relational Calculus is of Two Types:
1. Tuple Relational Calculus (TRC)

2. Domain Relational Calculus (DRC)
Tuple Relational Calculus

A tuple variable is a variable that takes on tuples of a particular relation schema as values.
That is, every value assigned to a given tuple variable has the same number and type of fields.
A tuple relational calculus query has the

form { T I p(T) },
where T is a tuple variable and p(T) denotes a formula that
describes .
The result of this query is the set of all tuples t for which the formula p(T) evaluates to true with T
= t.
Example 1: Write a TRC query to get all the data of customers whose zip code is 12345.
TRC Query:
{t \| t ∈ Customer ∧ t.Zipcode = 12345} or TRC Query: {t \| Customer(t) ∧ t[Zipcode] = 12345 }
Workflow of query - The tuple variable "t" will go through every tuple of the Customer table. Each row
will check whether the Cust_Zipcode is 12345 or not and only return those rows that satisfies the
Predicate expression condition. The TRC expression above can be read as "Return all the tuple which
belongs to the Customer Table and whose Zipcode is equal to 12345."

Domain Relational Calculus

Example 1: Write a DRC query to get the data of all customers with Zip code 12345.
DRC query:
{<x1,x2,x3> \| <x1,x2> ∈ Customer ∧ x3 = 12345 }
Workflow of Query: In the above query x1,x2,x3 (ordered) refers to the attribute or column which we
need in the result, and the predicate condition is that the first two domain variables x1 and x2
should be present while matching the condition for each row and the third domain variable x3
should be equal to 12345.


DBMS Module-2

Uploaded by

Copyright:

Available Formats

DBMS Module-2

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

DBMS Module-2

Uploaded by

Copyright:

Available Formats

DATABASE MANAGEMENT SYSTEMS

COURSE CODE: CSE-2007

Database Management Systems (DBMS), SCOPE, VIT-AP University, India 14/11/2024 2

Database Management Systems (DBMS), SCOPE, VIT-AP University, India 14/11/2024 3

• Attribute: Attributes are the properties that

Database Management Systems (DBMS), SCOPE, VIT-AP University, India 14/11/2024 6

2. Every domain must contain atomic values(smallest indivisible units) it means

Database Management Systems (DBMS), SCOPE, VIT-AP University, India 14/11/2024 7

Database Management Systems (DBMS), SCOPE, VIT-AP University, India 14/11/2024 9

Database Management Systems (DBMS), SCOPE, VIT-AP University, India 14/11/2024 11

Mapping Process (Algorithm):

• Create table for each entity.

• Create table for a relationship.

Database Management Systems (DBMS), SCOPE, VIT-AP University, India 14/11/2024 13

• Create table for weak entity set.

• Create tables for all higher-level

A person has 0 or 1 passport number and Passport

First Convert each entity and relationship to

Database Management Systems (DBMS), SCOPE, VIT-AP University, India 14/11/2024 16

A male marries 0 or 1 female and vice versa as

Database Management Systems (DBMS), SCOPE, VIT-AP University, India 14/11/2024 19

In this scenario, every student can enroll only in

Database Management Systems (DBMS), SCOPE, VIT-AP University, India 14/11/2024 20

In this scenario, every student can enroll in more

Similarly Compulsory_Courses table corresponds to

Database Management Systems (DBMS), SCOPE, VIT-AP University, India 14/11/2024 22

In this scenario, an employee can have

Database Management Systems (DBMS), SCOPE, VIT-AP University, India 14/11/2024 26

Database Management Systems (DBMS), SCOPE, VIT-AP University, India 14/11/2024 27

Database Management Systems (DBMS), SCOPE, VIT-AP University, India 14/11/2024 28

Output – Selects tuples from Tutorials where topic =

Output – Selects tuples from Customers where sales is greater than

Database Management Systems (DBMS), SCOPE, VIT-AP University, India 14/11/2024 29

Notation: ∏ A1, A2, An (r)

Database Management Systems (DBMS), SCOPE, VIT-AP University, India 14/11/2024 30

∏ NAME, CITY (CUSTOMER)

Database Management Systems (DBMS), SCOPE, VIT-AP University, India 14/11/2024 31

Database Management Systems (DBMS), SCOPE, VIT-AP University, India 14/11/2024 32

Database Management Systems (DBMS), SCOPE, VIT-AP University, India 14/11/2024 33

Database Management Systems (DBMS), SCOPE, VIT-AP University, India 14/11/2024 35

Database Management Systems (DBMS), SCOPE, VIT-AP University, India 14/11/2024 36

Database Management Systems (DBMS), SCOPE, VIT-AP University, India 14/11/2024 37

Example: We can use the rename operator to rename STUDENT relation to

Database Management Systems (DBMS), SCOPE, VIT-AP University, India 14/11/2024 39

Database Management Systems (DBMS), SCOPE, VIT-AP University, India 14/11/2024 41

Before understanding Relational calculus in DBMS, we need to understand Procedural

Database Management Systems (DBMS), SCOPE, VIT-AP University, India 14/11/2024 70

Relational Calculus is of Two Types:

1. Tuple Relational Calculus (TRC)

Tuple Relational Calculus

A tuple relational calculus query has the

Database Management Systems (DBMS), SCOPE, VIT-AP University, India 14/11/2024 74

Database Management Systems (DBMS), SCOPE, VIT-AP University, India 14/11/2024 79

Database Management Systems (DBMS), SCOPE, VIT-AP University, India 14/11/2024 80

You might also like