Module -2

Data Modeling: Enhanced-

Entity-Relationship Model and
Relational Data Model(Part I)

-Jyoti Bansode
SAKEC
 Entity-Relationship Model
� A data model is a collection of conceptual tools for
describing data, data relationships,data semantics and
consistency constraints.
� Entity-Relationship Model: The entity-relationship
(E-R) data model uses a collection of basic objects,
called entities, and relationships among these objects.
� An entity is a “thing” or “object” in the real world that
is distinguishable from other objects.(person, car,
student, employee)or it may be an object with a
conceptual existence ( company, course,project).
 Entities,Attributes and
Relationships
� Entities are described in a database by a set of attributes. e.g.
Roll_No,Name,Branch,Marks are the attributes of Student
entity
� A relationship is an association among several entities.
� E.g. Faculty works_in department, so works_in is the
relationship between Faculty and department
� The set of all entities of the same type and the set of all
relationships of the same type are termed an Entity set and
Relationship set, respectively.
� The overall logical structure (schema) of a database can be
expressed graphically by an Entity-Relationship (E-R)
diagram.
 Key Attributes
� An entity type usually has one or more attributes whose
values are distinct for each individual entity in the entity
set. Such an attribute is called a key attribute, and its
values can be used to identify each entity uniquely.

E.g. In student entity Roll_No is uniquely identifies each

student so it is known as key attribute.

In department entity Department_No will be the key

attribute.
 Key Attributes
� If a set of attributes possesses this property, the proper
way to represent this in the ER model that we describe
here is to define a composite attribute and designate
it as a key attribute of the entity type.
� E.g. In Shipment(Sno,Pno,Qty) The attributes(Sno,Pno)
together identifies any entity so this is composite key
attribute

� In E-R diagram key attribute is represented by

underlined inside the oval.
 Entity-Relationship Diagram
� There are different types of notations are
used to draw E–R diagram
1. Korth, Slberchatz, Sudarshan, Database System
Concepts, 6th Edition, McGraw Hill )
 Entity-Relationship Diagram
� Rectangle divided into two parts represent Entity sets.
First blue part is name of the Entity Set. The second part
contains all attributes.
� Diamond represents Relationship Set
� Lines links Entity set to Relationship set
� Double line indicates total participation of entity in
relationship set
� Double diamonds represents identifying relationship
with weak entity
 Entity-Relationship Diagram

� 2. Another notation is as given in next slides.

(We are going to follow this notation)
Entity-Relationship Diagram
Representation of Entities and Attributes
E-R Diagram with different
Attributes
E-R diagram
 Entity/Entity Set
� An entity is a “thing” or “object” in the real world that
is distinguishable from other objects.
� e.g. Student, Department, Employee these are entities.

� An entity set is a set of entities of the same type that

share the same properties, or attributes.
� E.g The set of all people who are Faculties at a given
College can be defined as the entity set Faculty.
Similarly, the entity set student might represent the set
of all students in College.
 Types Of Entity
Key Attribute: An entity type usually has one or more attributes whose
values are distinct for each individual entity in the entity set. Such an
attribute is called a key attribute, and its values can be used to identify
each entity uniquely.
� E.g. In Student Entity Roll_No or Registration _No can be the
Primary key.
� Strong Entity:An entity set that has a its own key attribute is termed
as strong entity set.
� E.g. In Staff entity set staff_Id can be primary key. So it will be
strong entity. Another example is in Customer cust_Id can be used to
identify the customer so it is primary key and customer will be the
Strong Entity Set.
� It is represented by rectangle
 Types Of Entity
Weak Entity: Entity types that do not have key attributes
of their own are called weak entity types.
� For a weak entity set to be meaningful, it must be associated
with another entity set, called the identifying or owner entity
set. Every weak entity must be associated with an identifying
entity.
� Although a weak entity set does not have a key attribute, but
there should be distinguishing keys among all those entities
in the weak entity set that depend on one particular strong
entity. The discriminator of a weak entity set is a set of
attributes that allows this distinction to be made.
 Types Of Entity
� E.g. Consider the two entities Course and Section
having following attributes.
� Course (course_id, title, credits)

� Section (course_id, sec_id, semester, year)

� In this Course is strong entity having course_id as a

primary key.
� Course has different sections. So Section is known as
weak entity as it depends upon course. If Course does
not exists, the section will also not be there.
 Types Of Entity
� As you can see section entity is having course_id
attribute with its own attributes (sec_id, semester, year)
to have discrimination.
� Weak Entity Set is represented by double rectangle.
 Types Of Entity
� As you can see section entity is having course_id
attribute with its own attributes (sec_id, semester, year)
to have discrimination.
� Weak Entity Set is represented by double rectangle.

Course_Id Title Credit Course_id Sec_id semester year

C1 DBMS 10 C1 S1 III 2nd

C2 JAVA 10 C2 S1 IV 2nd

C2 S2 IV 2nd
E-R Diagram
� Employee is the strong entity
with Employee_id as primary
key (which is represented by
solid underlined) and
Dependent is the weak entity.
� The discriminator(Name) of a
weak entity is underlined with
a dashed, rather than a solid,
line.
� The relationship set
connecting the weak entity set
to the identifying strong
entity set is depicted by a
double diamond.
E-R Diagram
E-R Diagram
� As you can see in the diagram,
Customer is the Strong Entity
with (cust_id as a primary key)
as it does not depend on any other
Entity.
� Loan is the Weak Entity as it
depends on Customer. If
Customer is not there Loan will
not exists.
� In Loan Entity Loan_name is
discriminator which is
represented by dashed(_ _ _ _)
underline.
 Attributes
� Attributes: Attributes are descriptive properties
possessed by each member of an entity set.
� E.g. Entity Student can have attributes such as (Rno,
Name,Phone_No, Address, Percentage,DOB)
� Student((Rno,Name,Phone_No,Address,Percentage,DOB)
� Entity Department can have attributes as(Dno, Dname,
HOD)
� Depaertment(Dno, Dname, HOD)

� In E-R diagram attributes are represented by Oval

Attributes
� In this Instructor has attributes
as (ID,name,salary) where ID
is the key attribute(Primary
key).
� Department has (dept_name,
building, budget) as attributes
and dept_name is the key
attribute(Primary key)

� This is another notation in

which attributes are shown in
ovals. For student Entity
attributes are (Roll_No,
Gender, Age) and Roll_No is
Key Attribute
Types Of Attributes
� Simple Attributes: These
attributes can not be further
divided into subparts.
� The attribute Roll_No can not be
divided further . So it is known as
Simple Attribute.

� Composite Attributes: These

attributes can be divided into
subparts (i.e. Other attributes).
� The attribute Name is further
divided into F_name,M_Name
and L_Name so it is Composite
Attribute. Address also
Composite Attribute.
Types Of Attributes
� Single Valued Attributes:
These attributes can take only one value for
a given entity from an entity set.
� Here all attributes are single valued
because they can take only one value for
specific entity.

�Multivalued Attributes:
These attributes can take more than one
value for a given entity from an entity set.
�They are represented by Double Oval.
�In the given diagram Mob_no and
Email_id are Multivalued Attributes as a
person can have more than one phone
numbers and email_ids
Types Of Attributes
� Derived Attributes:
Derived attributes are those attributes
which can be derived from other
attribute(s).
� They are represented as dashed oval.
�In the diagram Age is the Derived
Attribute as it is derived from
DOB(Date Of Birth) attribute.
�Key Attributes: Key attributes
are those attributes which can identify
an entity uniquely in an entity set.
�It is represented by Underlining the
attribute.
�In the diagram Roll_No is the Key
Attribute as it Uniquely Identifies the
particular entity.
Types Of Attributes
E-R Diagram with different
Attributes
 Relationship Set
� A relationship is an association among several entities.
For example, a member relationship associates an
instructor with her department.
� The set of all relationships of the same type are termed
as relationship set
� Relationship sets are represented by a diamond
connecting a pair of related entity sets. The name of the
relationship is placed inside the diamond.

member
Relationship Set
� In this diagram there are
two Entities Student
(stu_id, Stu_name,
Stu_Addr) and College
(Col_ID,Col_Name)
and Relationship
between these two
entities is named as
StudyIn
Identifying Relationship Set
� The relationship associating
the weak entity set with the
identifying entity set is
called the identifying
relationship.
⮚ It is shown by Double
Diamond
⮚ Double line indicates total
participation of weak entity
⮚ Every section must be
related via sec_course to
some course
Identifying Relationship Set
� This is another notation of E-R
diagram. In this there are two
Entities
Customer(Cust_Id,Cust_name,C
ust_add) and
Loan(loan_name,loan,date)
� As the Loan is the weak entity
,the relationship Borrows is
shown in double diamond as it
is Identifying Relationship
� Double lines indicate total
participation of an entity in a
relationship set.
� Total Participation means every
loan should be related via
Borrows to Customer
Participation Constraint
�
Participation Constraint
is applied on the entity
participating in the
relationship set.
� Total Participation:
� Each entity in the entity
set must participate in
the relationship. If each
student must enroll in a
course, the participation
of student will be total.
Total participation is
shown by double line in
ER diagram.
� Partial Participation:
� The entity in the entity set may
or may NOT participate in the
relationship.
� If some courses are not
enrolled by any of the student,
the participation of course will
be partial.
� The diagram depicts the
‘Enrolled in’ relationship set
with Student Entity set having
total participation and Course
Entity set having partial
participation.
 Relationship Types
� There are different types of Relationships like:
� Binary Relationship

� Ternary Relationship

� N-Ary Relationship

� Recursive Relationship
Relationship Types
� Binary Relationship:
� When there is a relationship
between two different entities,
it is known as a binary
relationship.

� Ternary Relationship:
� When there is a relationship
between three different
entities, it is known as a
ternary relationship.
Relationship Types
� N-Ary Relationship: When any
relationship consists of more than
two Entities then it is called N-ary
relationship.

� Relationships in databases are

often binary. Some relationships
that appear to be
nonbinary could actually be better
represented by several binary
relationships.
⮚ In the diagram Ternary
relationship is converted into
three binary relationships.
Recursive Relationship
� A relationship between two
entities of similar entity type is
called a recursive relationship.
Here the same entity type
participates more than once in
a relationship type with a
different role for each
instance.
� We indicate roles in E-R
diagrams by labeling the lines
that connect diamonds to
rectangles.
Recursive Relationship
� This is the example of
Recursive Relationship.
In this Employee will
work as Worker of a
company and also he is
Manager for some
employees.
� The two Roles he plays
are indicated on the lines
as Worker and Manager.
 Mapping Cardinality/Mapping
Constraints
� Mapping cardinalities, or cardinality ratios, express the
number of entities to which another entity can be
associated via a relationship set.
� For a binary relationship set R between entity sets A
and B, the mapping cardinality must be one of the
following:
� One-To-One

� One-To-Many

� Many-To-One

� Many-To-Many
Mapping Cardinality
� One-To-One:An entity in A is
associated with at most one entity
in B, and an entity in B is
associated with at most one entity
in A.
� E.g. Student can enrol at most one
course and course can have at most
one student.
� E.g. We draw a directed line from
the relationship set advisor to both
entity sets instructor and student.
This indicates that an instructor
may advise at most one student,
and a student may have at most
one advisor.
Mapping Cardinality
� One-To-Many:An entity in
A is associated with any
number (zero or more)of
entities in B. An entity in B,
however, can be associated
with at most one entity in A.
� E.g. An instructor may advise
many students, but a student
may have at most one advisor.
� e.g. A student can enrol for
many courses
Mapping Cardinality
� Many-To-One:An entity in A
is associated with at most one
entity in B. An entity in B,
however, can be associated with
any number (zero or more) of
entities in A.
� This indicates that an Customer
may borrows at most one loan,
but a loan may be borrowed by
many Customers
� This is another notation which
shows student can take at most
one course and one course can be
taken by many students.
Mapping Cardinality
� Many-To-Many: An entity in
A is associated with any number
(zero or more)of entities in B, and
an entity in B is associated with
any number (zero or more) of
entities in A.
� An instructor may advise many
students, and a student may have
many advisors.
� This is another notation to show
mapping cardinality. Which
shows one student can take many
courses and one course can be
taken by many students.
E-R Diagram of Banking System
 Enhanced/Extended Entity-
Relationship(EER)
� Although the basic E-R concepts can model most database
features, some aspects of a database may be more appropriately
expressed by certain extensions to the basic E-R model.
� Features of EER Model:
1. EER creates a design more accurate to database schemas.
2. It reflects the data properties and constraints more precisely.
3. It includes all modeling concepts of the ER model.
4. Diagrammatic technique helps for displaying the EER schema.
5. It includes the concept of specialization and generalization.
6. It is used to represent a collection of objects that is union of
objects of different of different entity types.
Extended Entity-Relationship(EER)
� It is a diagrammatic technique for displaying the
following concepts:
� Sub Class and Super Class

� Specialization and Generalization

� Union or Category

� Aggregation
 Sub Class and Super Class
⮚ Sub Class and Super Class:
� Sub class and Super class relationship leads the concept
of Inheritance.
� The relationship between sub class and super class is
denoted with d symbol.

� 1. Super Class: Super class is an entity type that has a

relationship with one or more subtypes.
For example: Shape super class is having sub groups as
Square, Circle, Triangle.
Sub Class and Super Class
� 2. Sub Class: It is a
group of entities with
unique attributes.
� Sub class inherits
properties and attributes
from its super class.
For example: Square,
Circle, Triangle are the
sub class of Shape super
class.
Example from Fundamentals Of Database systems
by Elmasri and Navathe
 Generalization
� We suppress the differences among several entity types, identify
their common features, and generalize them into a single superclass
of which the original entity types are special subclasses.
� For example, consider the entity types CAR and TRUCK they have
several common attributes, they can be generalized into the entity
type VEHICLE. Both CAR and TRUCK are now subclasses of the
� generalized superclass VEHICLE. We use the term generalization to
refer to the process of defining a generalized entity type from the
given entity types.
Example from Fundamentals Of Database
systems by Elmasri and Navathe
Generalization
� . Generalization: Generalization is
the process of generalizing the
entities which contain the properties
of all the generalized entities.
� It is a bottom-up approach, in which
two lower level entities combine to
form a higher level entity.
� Generalization is the reverse process
of Specialization.
� It defines a general entity type from
a set of specialized entity type.
� It minimizes the difference between
the entities by identifying the
common features.
 Specialization
� The process of designating sub-groupings within an entity set is called
specialization.
� An entity set may include sub-groupings of entities that are distinct in some
way from other entities in the set. For instance, a subset of entities within an
entity set may have attributes that are not shared by all the entities in the entity
set.As an example, the entity set person may be further classified as one of the
following:
� • employee
� • student
� Each of these person types is described by a set of attributes that includes all
� the attributes of entity set person plus possibly additional attributes
� For example: employee entities may be described further by the attribute
salary, whereas student entities may be described further by the attribute
tot_cred.
 Specialization
In summary, the specialization process allows us to do the following:
■ Define a set of subclasses of an entity type
■ Establish additional specific attributes with each subclass
■ Establish additional specific relationship types between each
subclass and other entity types or other subclasses
Specialization
� Specialization is a process that
defines a group entities which is
divided into sub groups based on
their characteristic.
� It is a top down approach, in
which one higher entity can be
broken down into two lower level
entity.
� It maximizes the difference
between the members of an entity
by identifying the unique
characteristic or attributes of each
member.
� It defines one or more sub class
for the super class and also forms
the superclass/subclass
relationship.
 EER Diagram

1.Different types of vehicles such as Truck,car are owned by some

owners. The owners may be Bank,Company and person. Draw EER
diagram to represent Superclass and subclass.

2. There are different types of computers such as laptop and desktop.

Different types of softwares to be installed in computer. Also computer
has different accessories.
EER Diagram
EER Diagram
Disjoint Constraint

It specifies that the subclasses of

the specialization must be
disjoint.
This means that an entity can be a
member of at most one of the
subclasses of the specialization.
It is denoted by ‘d’in circle.

� In the diagram Employee can be

either Secretary or Technician or
Engineer
Overlapping Constraint

If the subclasses are not

constrained to be disjoint, their
sets of entities may be
overlapping; that is, the same
entity may be a member of more
than one subclass of the
specialization.
This case, which is the default, is
displayed by placing an ‘o’ in the
circle.
 Total and Partial Specialization
A total specialization constraint specifies that every entity in the
superclass must be a member of at least one subclass in the
specialization.
For example, if every EMPLOYEE must be either an
HOURLY_EMPLOYEE or a SALARIED_EMPLOYEE, then the
specialization {HOURLY_EMPLOYEE, SALARIED_EMPLOYEE}
in is a total specialization of EMPLOYEE.
This is shown in EER diagrams by using a double line to connect the
superclass to the circle.
A single line is used to display a partial specialization,which allows
an entity not to belong to any of the subclasses.
For example, if some EMPLOYEE entities do not belong to any of the
subclasses {SECRETARY,ENGINEER, TECHNICIAN} then that
specialization is partial.
Total and Partial Specialization

Consider the adjacent figure:

For example, if every EMPLOYEE
must be either an
HOURLY_EMPLOYEE or a
SALARIED_EMPLOYEE, then the
specialization
{HOURLY_EMPLOYEE ,
SALARIED_EMPLOYEE} in is a
total specialization
of EMPLOYEE.
For example, if some EMPLOYEE
entities do not belong to any of the
subclasses
{SECRETARY,ENGINEER,
TECHNICIAN} then that
specialization is partial.
 Total and Partial Specialization
We have the following four possible constraints on
specialization:
■ Disjoint, total
■ Disjoint, partial
■ Overlapping, total
■ Overlapping, partial
Category or Union
� Category represents a single super
class or sub class relationship with
more than one super class.
� It can be a total or partial
participation.

� A Category member must exist in at

least one of its super classes.
� E.g suppose that we have three
entity types: PERSON, BANK, and
COMPANY. In a database for
motor vehicle registration, an owner
of a vehicle can be
a person, a bank or a company.
Category or Union
� The super classes
COMPANY, BANK,
and PERSON are
connected to the circle
with the ∪
symbol,which stands for
the Union
Aggregation
� Aggregation is an abstraction
through which relationships
are treated as higher-level
entities.
� Aggregation is a process that
represent a relationship
between a whole object and its
component parts.
� It abstracts a relationship
between objects and viewing
the relationship as an object.
� It is a process when two entity
is treated as a single entity.
Aggregation
� Create a higher-level
aggregate class
composed of
COMPANY,
JOB_APPLICANT, and
INTERVIEW and to
relate this class to
JOB_OFFER
MB

EER Diagram of Banking System

 Data Models

A data model is a collection of concepts that

can be used to describe the structure of a database.
By structure of a database we mean the data types,
relationships,and constraints that apply to the data.
There are different types of data models:
1. Relational Model
2. Entity-Relationship model
3. Object-Based Data model
4. Semistructured data model
Before Relational Data Model , the network data
model and the hierarchical data model were used.
 Entity Relationship Data Models

A data model is a collection of concepts that

can be used to describe the structure of a database.
By structure of a database we mean the data types,
relationships,and constraints that apply to the data.
There are different types of data models:
1. Relational Model
2. Entity-Relationship model
3. Object-Based Data model
4. Semistructured data model
Before Relational Data Model , the network data
model and the hierarchical data model were used.
 Introduction to Relational Model
The relational model represents the database as a collection of
relations. Informally,each relation resembles a table of values.

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.
 Introduction to Relational Model
Degree: The degree (or arity) of a relation is the number of
attributes n of its relation schema. e.g. In the previous student
relation there are 7 attributes so the degree is 7 for it.

Cardinality: Total number of rows present in the Table.

 Introduction to Relational Model

Domain:For each attribute of a relation, there is a set of

permitted values, called the domain of that attribute.
A domain D is a set of atomic values. By atomic we mean that
each value in the domain is indivisible as far as the formal
relational model is concerned. A common method of specifying a
domain is to specify a data type from which the data values
forming the domain are drawn.
e.g. The marks domain in student relation will have all possible
values of marks.

create table student(roll_no numeric(2),name varchar(10), marks

numeric(3));
 Introduction to Relational Model

Domain:
create table student(roll_no numeric(2),name varchar(10), marks
numeric(3));

1. insert into student values(1,'Ajay',45);

2. insert into student values(121,'Vijay',90);
3. insert into student values(121,'Vijay',9012);
4. insert into student values(3,’34’,90);

Which of the above records will get inserted?

 Introduction to Relational Model

Database Schema: The description or overall structure of

a database is called the database schema, which is
specified during database design and is not expected to
change frequently.
e.g. To see the schema or structure ,the command is:
Desc student;
student(roll_no,name,marks)
 Introduction to Relational Model

Instance:The actual data in a database may change quite

frequently. The data in the database at a particular moment
in time is called instances or a database state or snapshot.
e.g. If you add any record or delete any record the instance
get change for that relation.
 Introduction to Relational Model

Instance: If you add any records the instance will

change.
 Introduction to Relational Model

Values and NULLs in the Tuples: Each value in a tuple

is an atomic value i.e. it is not divisible into
components
An important concept is that of NULL values, which are
used to represent the values of attributes that may be
unknown or may not apply to a tuple. A special value, called
NULL, is used in these cases.
e.g. If ph_no of employee is not known then we can put Null
value in it.
 Department Relation

Dno Dname HOD

CM Computer ABC
Engineering
ET Electronics & XYZ
Telecommunication
IT Information PQR
Technology
 Student Relation

Roll_no Reg_no Name Ph_no Marks Dno

1 1001 Uma 234567890 78 IT
2 3002 Pratik 123456789 89 CM
3 1003 Uma 56789046 77 ET
4 5006 Reena 78439021 89 IT
 Types Of Keys in
RDBMS
In RDBMS no two tuples in a relation are allowed to have exactly the
same value for all attributes.

1. Super Key: A superkey is a set of one or more attributes that,

taken collectively, allow us to identify uniquely a tuple in the relation.
Consider the schema student(roll_no, reg_no,name, ph_no, marks,
dno).
Here roll_no or reg_no attribute of the relation student is sufficient to
distinguish one student tuple from another. Thus, roll_no is a
superkey. Consider the Department and Student relation in previous
slides.
The name attribute of student is not a superkey, because several
student might have the same name.
A superkey may contain extraneous attributes. e.g. the combination
of roll_no and name is a superkey for the relation student.
Some more super keys are: reg_no+ph_no, roll_no+marks,
roll_no+reg_no+name
 Types Of Keys in
RDBMS

2. Candidate key: A candidate key is a minimal superkey, that

is, a set of attributes that forms a superkey, but none of
whose subsets is a superkey. Minimal superkeys are called
candidate keys.
Candidate key should uniquely identify the tuple i.e. it should
not have null or duplicate values.
It is possible that several distinct sets of attributes could serve
as a candidate key.
e.g. In student relation roll_no alone and reg_no alone can
be candidate keys.
But combination of {roll_no,name} or {reg_no,dno} or
(reg_no,name} can not be candidate keys as alone {roll_no}
or {reg_no} can identify the tuple and that’s why it can
become candidate keys.
 Types Of Keys in
RDBMS
3. Primary Key: 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 selected
from the set of candidate keys.
A primary key is a minimal set of attributes in a relation that
uniquely identifies tuples in that table.
Primary key does not accept duplicate value and null value.
It is usually better to choose a primary key with a single attribute
or a small number of attributes.
e.g. In student relation either roll_no or reg_no can become
primary key.
In the E-R model it is denoted by underline.
 Types Of Keys in
RDBMS
4. Secondary key: Only one of the candidate keys is selected
as the primary key. The rest of them are known as secondary
keys. The UNIQUE clause specifies alternate (secondary) keys.
e.g. If reg_no is defined as primary key then roll_no can be
defined as unique.

5. Foreign Key:A foreign key is a set of attributes in a

referencing relation, such that for each tuple in the referencing
relation, the values of the foreign key attributes are guaranteed
to occur as the primary key value of a tuple in the referenced
relation.
Foreign key takes value only from primary key of the relation to
which it is referencing. Foreign key can refer to same or different
relation. It can take null or repeated(duplicate values)
 Types Of Keys in
RDBMS

Foreign Key: Consider the following two relations:

1. Department(Dno,Dname,HOD)
In this relation Dno is a Primary key

2. Student(Roll_no,Reg_no,Name,Ph_no,Marks,Dno)
Dno will be the foreign key which will take values from Dno of
Department relation.

3. Consider the following relation

emp(eno,ename,post,salary,mgr)
In this relation eno is the primary key and mgr is the foreign key
which refers to the same relation.
 Example1

Supplier
Sno Sname Location Ph_no Shipment
S1 ABC Delhi 56677885 Sno Pno Qty
S2 XYZ Mumbai 12324566 S1 P1 10
S3 PQR Delhi 7890067 S1 P2 5

S2 P2 15
Product
Pno Pname Price
P1 Keyboard 3000
P2 Mouse 1000
 Example1

1. Identify the super keys,candidate keys,primary key and

foreign keys from Supplier,Product and Shipment relations
given in previous slide. Assume that product name is not having
duplicate values.(Refer table in previous slide)
 Example2
Book
Bno Bname Author Quantity
101 DBMS Korth 10
Concepts
201 Database Navathe 20
System
301 DBMS Ramkrishnan 15
Student
Transaction
Roll Reg Nam Ph_no Mar Dn
Roll_no Bno Days Fine _no _no e ks o
1 201 45 100 1 1001 John 2345678 78 IT
3 301 20 50 90
2 3002 Prati 1234567 89 C
k 89 M
3 1003 Uma 5678904 77 ET
6
4 5006 Ree 7843902 89 IT
 Example2

2. Identify the super keys,primary key and foreign keys from

student,book and transaction relations given in previous slide.
Consider that student can take more than one book at a time.
Also same book will not be issued to same student if he already
having that book. (Refer table in previous slide)
 Relational Model Constraints

There are generally many restrictions or constraints/condition on the actual

values in a database state. Constraints on databases can generally be divided
into three main categories:

1. Constraints that are inherent in the data model. We call these inherent
model-based constraints or implicit constraints.
e.g. The constraint that a relation cannot have duplicate tuples is an inherent
constraint

2. Constraints that can be directly expressed in schemas of the data model,

typically by specifying them in the DDL (data definition language. We call these
schema-based constraints or explicit constraints.

3. Constraints that cannot be directly expressed in the schemas of the data

model, and hence must be expressed and enforced by the application
programs. We call these application-based or semantic constraints or
business rules.
 Schema-based constraints or Explicit
constraints.

The schema-based constraints include Domain constraints, Key constraints,

constraints on NULLs, Entity Integrity constraints, and Referential Integrity
Constraints.

1. Domain Constraints:These are the most elementary form of integrity

constraint. They test values inserted in the database. It specify that within
each tuple, the value of each attribute A must be an atomic value from the
particular domain. Domain can be specified by using the data types like
numeric,varchar,date etc.

e.g.
Create table student (roll_no int(5) primary key,reg_no int(10), Name
varchar(40), ph_no int(10), marks int(3) ,dno varchar(5));
 Schema-based constraints or Explicit
constraints.

Check Constraint: The CHECK constraint is used to allow

certain values or range of values in particular attribute. If you
define a CHECK constraint on a attribute it allows only certain
values for this column.
e.g.
1.create table department (dno varchar(5) primary key,
dname varchar(30), HOD varchar(35),check (dno in(‘CM’, ‘IT’,
‘ET’)));
2.Create table student (roll_no int(5) primary key,reg_no int(10),
Name varchar(40), ph_no int(10), marks int(3)
check(marks>40),dno varchar(5));
3. Alter table student add constraint chk1 check (marks>40);
 Key Constraints and Constraints on
NULL Values
The entity integrity constraint states that no primary key
value can be NULL. This is because the primary key value is
used to identify individual tuples in a relation.

Primary Key Constraint:The PRIMARY KEY constraint

uniquely identifies each tuple in a relation.

Primary keys must contain UNIQUE values and cannot contain

NULL values.

A relation can have only ONE primary key. This primary key can
consist of single or multiple attributes(fields).
 Primary Key Constraints

Primary key: Using Create command

1. create table department (dno varchar(5) primary key,
dname varchar(30), HOD varchar(35));
2. Create table student (roll_no int(5),reg_no int(10), Name varchar(40),
ph_no int(10), marks int(3) ,dno varchar(5), primary key(roll_no);
3. create table shipment(sno varchar(5),pno varchar(5), Quantity int(5),
constraint pk primary key(sno,pno));

Primary Key: Using Alter command

1. alter table department add primary key(dno);
2. alter table department add constraint pk1 primary key (sno,pno);
 Unique Constraint

Unique Constraint:The UNIQUE constraint ensures that all

values in a attribute should be unique(different).

Both unique and primary key constraints ensure unique values in

attribute.

There can be many UNIQUE constraints per relation, but only

one PRIMARY KEY constraint per relation.
 Unique Constraint

Unique Constraint: Using Create command

1. create table department (dno varchar(5) primary key,
dname varchar(30) unique, HOD varchar(35));
2. Create table student (roll_no int(5) primary key,reg_no int(10),
Name varchar(40), ph_no int(10) unique, marks int(3) ,dno
varchar(5), unique (reg_no);
3. Create table student (roll_no int(5) primary key,reg_no int(10),
Name varchar(40), ph_no int(10), marks int(3) ,dno
varchar(5),constraint uk unique (reg_no);

Unique Key: Using Alter command

1. alter table department add unique(dname);
2. alter table department add constraint uk1 unique(reg_no);
 Not Null Constraint

Not Null Constraint: By default an attribute can have NULL

values.

The NOT NULL constraint enforces an attribute not to accept

NULL values.

This enforces a field to always contain some value so you you

cannot insert a new tuple or update a tuple without adding a
value to this field.
 Not Null Constraint

NOT NULL constraint :Using Create command

1. create table department (dno varchar(5) primary key,dname
varchar(30) unique, HOD varchar(35) not null);
2. Create table student (roll_no int(5) primary key,reg_no int(10), Name
varchar(40) not null , ph_no int(10) unique, marks int(3) ,dno
varchar(5), unique (reg_no);
3. Create table student (roll_no int(5) primary key,reg_no int(10), Name
varchar(40) not null, ph_no int(10) not null, marks int(3) ,dno
varchar(5),constraint uk unique (reg_no);

NOT NULL Constraint : Using Alter command

1. alter table department modify HOD varchar(35) not null;
 Referential Integrity Constraint
(Foreign Key)
Referential Integrity constraint :A FOREIGN KEY is a key
used to link two relations together.

A FOREIGN KEY is an attribute in one relation that refers to the

PRIMARY KEY in another relation.

The relation containing the foreign key is called the child relation
and the relation containing the primary key is called the
referenced or parent relation.
 Referential Integrity Constraint
(Foreign Key)
Referential Integrity Constraint:Using Create command
1. create table department (dno varchar(5) primary key,
dname varchar(30) unique, HOD varchar(35) not null);
2. Create table student (roll_no int(5) primary key,reg_no int(10), Name
varchar(40) not null , ph_no int(10) unique, marks int(3) ,dno varchar(5)
references department(dno), unique (reg_no);
3. Create table student (roll_no int(5) primary key,reg_no int(10), Name
varchar(40) not null, ph_no int(10) not null, marks int(3) ,dno
varchar(5),constraint uk unique (reg_no), foreign key(dno) references
department(dno);

Referential Integrity Constraint: Using Alter command

1. alter table student add constraint fork1 foreign key(dno) references
department (dno);
Example

create table emp(eno numeric(10),dept varchar(5) references department(dno));

insert into emp values(1,10);

insert into emp values(2,10);

insert into emp values(3,20);

insert into emp values(3,null);

select * from emp;

 Default Constraint

Default Constraint:The DEFAULT constraint is used to provide a

default value for an attribute.
The default value will be added to all new records IF no other
value is specified.

Default Constraint: Using Create command

1. create table department (dno varchar(5) primary key,
dname varchar(30), HOD varchar(35)default 'xyz');

Default Constraint : Using Alter command

1. alter table department alter dname set default 'Information';
2.alter table supplier modify location varchar(30) default
'Mumbai';(Oracle)
Example:
(which records will get inserted in relation student)

Create table student (roll_no numeric(5) primary key,reg_no numeric(10), Name

varchar(40) not null, ph_no numeric(10) not null, marks numeric(3) ,dno
varchar(5),constraint uk unique (reg_no));

insert into student values(1,23,'Neha',1245,45,10);

insert into student values(1,123,'Neha',1245,45,10);

insert into student values(2,231,'Neha',1245,45,10);

insert into student values(3,231,'Neha',1245,45,10);

insert into student values(3,123,Null,1245,45,10);

insert into student values(4,1234,'Null',1245,45,10);

select * from student;

 Example
(How to insert records if Default Constraint is used)

create table department (Dno varchar(5) primary key,

Dname varchar(30), HOD varchar(35)default 'XYZ');

insert into department(Dno,Dname) values(10,'IT');

select * from department;

Dno Dname HOD

10 IT XYZ
ER-to-Relational Mapping Algorithm

■ Step 1: Mapping of Regular Entity Types

■ Step 2: Mapping of Weak Entity Types
■ Step 3: Mapping of Binary 1:1 Relation
Types
■ Step 4: Mapping of Binary 1:N Relationship
Types.
■ Step 5: Mapping of Binary M:N Relationship
Types.
■ Step 6: Mapping of Multivalued attributes.
■ Step 7: Mapping of N-ary Relationship
Types.

Slide 7- 107
■ Mapping EER Model Constructs to
Relations
■ Step 8: Options for Mapping Specialization
or Generalization.
■ Step 9: Mapping of Union Types
(Categories).
The ER conceptual schema diagram for the COMPANY
database. Figure1

Slide 7- 109
Result of mapping the COMPANY ER schema into a
relational schema.

Slide 7- 110
ER-to-Relational Mapping Algorithm

■ Step 1: Mapping of Regular Entity Types.

■ For each regular (strong) entity type E in the ER
schema, create a relation R that includes all the simple
attributes of E.
■ Choose one of the key attributes of E as the primary
key for R.
■ If the chosen key of E is composite, the set of simple
attributes that form it will together form the primary key
of R.

■ Example: We create the relations EMPLOYEE,

DEPARTMENT, and PROJECT in the relational schema
corresponding to the regular entities in the ER diagram.

■ SSN, DNUMBER, and PNUMBER are the primary keys

for the relations EMPLOYEE, DEPARTMENT, and
PROJECT as shown.

Slide 7- 111
According to step1 there will be three strong entities
which will be get converted as following relations:

1. Department (Dname, Dnumber) : Where Dno as

Primary key
2. Employee
(Fname,Mint,Lname,Ssn,Bdate,Address,Sex, Salary) :
Where Ssn is Primary key
3.Project (Pname,Pnumber,Location) : Where Pnumber
as Primary key
 ER-to-Relational Mapping
Algorithm (contd.)
■ Step 2: Mapping of Weak Entity Types
■ For each weak entity type W in the ER schema
with owner entity type E, create a relation R &
include all simple attributes (or simple
components of composite attributes) of W as
attributes of R.
■ Also, include as foreign key attributes of R the
primary key attribute(s) of the relation(s) that
correspond to the owner entity type(s).
■ The primary key of R is the combination of the
primary key(s) of the owner(s) and the partial
key of the weak entity type W, if any.

Slide 7- 113
■ Example: Create the relation
DEPENDENT in this step to correspond
to the weak entity type DEPENDENT.
■ Include the primary key SSN of the
EMPLOYEE relation as a foreign key attribute
of DEPENDENT (renamed to ESSN).
■ The primary key of the DEPENDENT relation
is the combination {ESSN,
DEPENDENT_NAME} because
DEPENDENT_NAME is the partial key of
DEPENDENT.
According to step 2 there will be one more relation as
Dependent:

4. Dependent
(Essn,Dependent_name,Sex,Bdate,Relation):
Where (Essn , Dependent_Name) is composite primary
key and Essn is Foreign key which will refer to Ssn of
Emp
ER-to-Relational Mapping
Algorithm (contd.)
■ Step 3: Mapping of Binary 1:1 Relation Types
■ For each binary 1:1 relationship type R in the ER
schema, identify the relations S and T that
correspond to the entity types participating in R.
■ There are three possible approaches:
1. Foreign Key approach: Choose one of the relations-
say S-and include a foreign key in S the primary key of
T. It is better to choose an entity type with total
participation in R in the role of S.
■ Example: 1:1 relation MANAGES is mapped by
choosing the participating entity type DEPARTMENT
to serve in the role of S, because its participation in
the MANAGES relationship type is total.
.

Slide 7- 116
2.Merged relation option: An alternate mapping of a
1:1 relationship type is possible by merging the two
entity types and the relationship into a single relation.
This may be appropriate when both participations are
total.
3.Cross-reference or relationship relation option:
The third alternative is to set up a third relation R for
the purpose of cross-referencing the primary keys of
the two relations S and T representing the entity types
 Manages is the 1:1 relationship. According to step 3
Mgr_Ssn will be added as Foreign key and Mgr_start_date
will be added as an attribute. So Department will have
following attributes:

Department
(Dname,Dnumber,Mgr_Ssn,Mgr_start_date) : Where
Dno as Primary key and Mgr_Ssn will be foreign key
which will refer to Ssn of Employee relation.
ER-to-Relational Mapping
Algorithm (contd.)
■ Step 4: Mapping of Binary 1:N Relationship
Types.
■ For each regular binary 1:N relationship
type R, identify the relation S that represent
the participating entity type at the N-side of
the relationship type.
■ Include as foreign key in S the primary key
of the relation T that represents the other
entity type participating in R.
■ Include any simple attributes of the 1:N
relation type as attributes of S.

Slide 7- 119
■ Example: 1:N relationship types
WORKS_FOR, CONTROLS, and
SUPERVISION in the figure.
■ For WORKS_FOR we include the primary
key DNUMBER of the DEPARTMENT
relation as foreign key in the EMPLOYEE
relation and call it DNO.
There are three 1:N relationship types WORKS_FOR,
CONTROLS, and SUPERVISION in the figure. According to
step 4 the relations will be as follows after adding
foreign keys in Employee and Project relations:
Employee (Fname,Mint,Lname,Ssn,Bdate,Address,Sex,
Salary,Super_Ssn,Dno) : Where Super_Ssn will be foreign
key(for Supervision relation) which will refer to Ssn of
same relation and Dno will be the foreign key(for
Works_For relationship) which will refer to Dnumber of
Department
Project (Pname,Pnumber,Location,Dnum) : Where Dnum
will be foreign key(for Controls relationship) which will refer
to Dnumber of Department.
ER-to-Relational Mapping Algorithm
(contd.)
■ Step 5: Mapping of Binary M:N
Relationship Types.
■ For each regular binary M:N relationship type
R, create a new relation S to represent R.
■ Include as foreign key attributes in S the primary
keys of the relations that represent the
participating entity types; their combination will
form the primary key of S.
■ Also include any simple attributes of the M:N
relationship type (or simple components of
composite attributes) as attributes of S.

Slide 7- 122
■ Example: The M:N relationship type WORKS_ON from
the ER diagram is mapped by creating a relation
WORKS_ON in the relational database schema.
■ The primary keys of the PROJECT and
EMPLOYEE relations are included as foreign keys
in WORKS_ON and renamed PNO and ESSN,
respectively.
■ Attribute HOURS in WORKS_ON represents the
HOURS attribute of the relation type. The primary
key of the WORKS_ON relation is the combination
of the foreign key attributes {ESSN, PNO}.
There is one M:N relationship types WORKS_ON in the
figure. According to step 5 there will be separate
relation of WORKS_ON as follows:

5. Works_On(Essn,Pno,Hours): Where (Essn,Pno)

will be composite primary key. Essn will be foreign
key which will refer to Ssn of Employee and Pno will
be foreign key which will refer to Pnumber of Project
relation.
■ Step 6: Mapping of Multivalued attributes.
■ For each multivalued attribute A, create a new
relation R.
■ This relation R will include an attribute corresponding
to A, plus the primary key attribute K-as a foreign key
in R-of the relation that represents the entity type of
relationship type that has A as an attribute.
■ The primary key of R is the combination of A and K.
If the multivalued attribute is composite, we include
its simple components.
■ .
ER-to-Relational Mapping
Slide 7- 125
Algorithm (contd.)
■ Example: The relation
DEPT_LOCATIONS is created.
■ The attribute DLOCATION represents the
multivalued attribute LOCATIONS of
DEPARTMENT, while DNUMBER-as foreign
key-represents the primary key of the
DEPARTMENT relation.
■ The primary key of R is the combination of
{DNUMBER, DLOCATION}
There is only one multivalued attribute in Department
relation as shown in the figure. According to step 6
there will be separate relation of Dept_Locations as
follows:

6.Dept_Locations(Dnumber,Dlocation):Where
(Dnumber,Dlocation) will be composite primary key.
Dnumber will be foreign key which will refer to
Dnumber of Department relation.
So finally there will be total 6 relations for E-R
diagram of Figure 1.

1.Employee (Fname,Mint,Lname,Ssn,Bdate,Address,Sex,
Salary,Super_Ssn,Dno)

2. Department(Dname,Dnumber,Mgr_Ssn,Mgr_start_date)

3.Project (Pname,Pnumber,Location,Dnum)

4. Dependent
(Essn,Dependent_name,Sex,Bdate,Relation)

5.Works_On(Essn,Pno,Hours)

6.Dept_Locations(Dnumber,Dlocation)
ER-to-Relational Mapping Algorithm (contd.)
■ Step 7: Mapping of N-ary Relationship
Types.
■ For each n-ary relationship type R, where
n>2, create a new relationship S to
represent R.
■ Include as foreign key attributes in S the
primary keys of the relations that represent
the participating entity types.
■ Also include any simple attributes of the n-
ary relationship type (or simple components
of composite attributes) as attributes of S.

Slide 7- 129
■ Example: The relationship type SUPPLY
in the ER on the next slide.
■ This can be mapped to the relation SUPPLY
shown in the relational schema, whose primary
key is the combination of the three foreign keys
{SNAME, PARTNO, PROJNAME}
FIGURE 4.11
Ternary relationship types. (a) The SUPPLY relationship.

Slide 7- 131
FIGURE 7.3
Mapping the n-ary relationship type SUPPLY from
Figure 4.11a.

Slide 7- 132
 Summary of Mapping constructs
and constraints
Table 7.1 Correspondence between ER and Relational Models
ER Model Relational Model
Entity type “Entity” relation
1:1 or 1:N relationship type Foreign key (or “relationship” relation)
M:N relationship type “Relationship” relation and two foreign keys
n-ary relationship type “Relationship” relation and n foreign keys
Simple attribute Attribute
Composite attribute Set of simple component attributes
Multivalued attribute Relation and foreign key
Value set Domain
Key attribute Primary (or secondary) key Slide 7- 133
Mapping EER Model Constructs
to Relations
■ Step8: Options for Mapping Specialization or Generalization.
■ Convert each specialization with m
subclasses {S1, S2,….,Sm} and
generalized superclass C, where the
attributes of C are {k,a1,…an} and k
is the (primary) key, into relational
schemas using one of the four
following options:
■Option 8A: Multiple relations- Superclass and subclasses
■Option 8B: Multiple relations-Subclass relations only

■Option 8C: Single relation with one type attribute

■Option 8D: Single relation with multiple type attributes

Slide 7- 134
Mapping EER Model Constructs
to Relations
■ Option 8A: Multiple relations- Superclass and subclasses
■ Create a relation L for C with attributes Attrs(L) = {k,a1,…an}
and PK(L) = k. Create a relation Li for each subclass Si, 1 < i <
m, with the attributesAttrs(Li) = {k} U {attributes of Si} and
PK(Li)=k. This option works for any specialization (total or
partial, disjoint of over-lapping).
■ Option 8B: Multiple relations-Subclass relations only
■ Create a relation Li for each subclass Si, 1 < i < m, with the
attributes Attr(Li) = {attributes of Si} U {k,a1…,an} and PK(Li) =
k. This option only works for a specialization whose subclasses
are total (every entity in the superclass must belong to (at least)
one of the subclasses).

Slide 7- 135
EER diagram notation for an attribute-defined
specialization on JobType.(Example of option 8A)

Slide 7- 136
Options for mapping specialization or generalization.
(a) Mapping the EER schema using option 8A.

Slide 7- 137
Generalization. (b) Generalizing CAR and TRUCK into the
superclass VEHICLE. (Example Of option 8B)

Slide 7- 138
Options for mapping specialization or generalization.
(b) Mapping the EER schema using option 8B.

Slide 7- 139
Mapping EER Model Constructs to
Relations (contd.)
■ Option 8C: Single relation with one type attribute
■ Create a single relation L with attributes Attrs(L) =
{k,a1,…an} U {attributes of S1} U…U {attributes of Sm} U {t}
and PK(L) = k. The attribute t is called a type (or
discriminating) attribute that indicates the subclass to
which each tuple belongs
■ Option 8D: Single relation with multiple type
attributes
■ Create a single relation schema L with attributes Attrs(L) =
{k,a1,…an} U {attributes of S1} U…U {attributes of Sm} U {t1,
t2,…,tm} and PK(L) = k. Each ti, 1 < I < m, is a Boolean type
attribute indicating whether a tuple belongs to the subclass
Si.

Slide 7- 140
EER diagram notation for an attribute-defined specialization
on JobType. (Example of option 8C)

Slide 7- 141
Options for mapping specialization or generalization.
(c) Mapping the EER schema using option 8C.

Slide 7- 142
EER diagram notation for an overlapping (non-disjoint)
specialization. (Example of option 8D)

Slide 7- 143
Options for mapping specialization or generalization. (d)
Mapping using option 8D with Boolean type fields Mflag
and Pflag.

Slide 7- 144
Summary of options 8A,8B,8C,8D

Slide 7- 145
Mapping EER Model Constructs to Relations
(contd.)

■ Mapping of Shared Subclasses (Multiple

Inheritance)
■ A shared subclass, such as
STUDENT_ASSISTANT, is a subclass of several
classes, indicating multiple inheritance. These
classes must all have the same key attribute;
otherwise, the shared subclass would be modeled
as a category.
■ We can apply any of the options discussed in Step
8 to a shared subclass, subject to the restriction
discussed in Step 8 of the mapping algorithm.
Below both 8C and 8D are used for the shared
class STUDENT_ASSISTANT.

Slide 7- 146
A specialization lattice with multiple inheritance for a
UNIVERSITY database.

Slide 7- 147
Mapping the EER specialization lattice in previous slide
figure using multiple options.

Slide 7- 148
Mapping EER Model Constructs to Relations
(contd.)
■ Step 9: Mapping of Union Types (Categories).
■ For mapping a category whose defining superclass
have different keys, it is customary to specify a new
key attribute, called a surrogate key, when creating a
relation to correspond to the category.
■ In the example below we can create a relation
OWNER to correspond to the OWNER category and
include any attributes of the category in this relation.
The primary key of the OWNER relation is the
surrogate key, which we called OwnerId.

Slide 7- 149
Two categories (union types): OWNER and
REGISTERED_VEHICLE.

Slide 7- 150
Mapping the EER categories (union types) in previous
slide figure to relations.

Slide 7- 151
 Chapter Summary

■ ER-to-Relational Mapping Algorithm

■ Step 1: Mapping of Regular Entity Types
■ Step 2: Mapping of Weak Entity Types
■ Step 3: Mapping of Binary 1:1 Relation
Types
■ Step 4: Mapping of Binary 1:N Relationship
Types.
■ Step 5: Mapping of Binary M:N Relationship
Types.
■ Step 6: Mapping of Multivalued attributes.
■ Step 7: Mapping of N-ary Relationship
Types.

Slide 7- 152
 Chapter Summary

■ Mapping EER Model Constructs to Relations

■ Step 8: Options for Mapping Specialization or
Generalization.
■ Step 9: Mapping of Union Types (Categories).
 Text Books
1. Korth, Silberchatz, Sudarshan, Database System
Concepts, 6th Edition, McGraw Hill
2. Elmasri and Navathe, Fundamentals of Database
Systems, 6th Edition, Pearson education
3. Raghu Ramkrishnan and Johannes Gehrke, Database
Management Systems, TMH