1
BCSE302L – DBMS
Module 2 Data Modeling
Dr. K.P. Vijayakumar,
VIT Chennai
Dr. K.P. Vijayakumar, VIT Chennai,
India.
Overview 2
Entity Relationship Model
Types of Attributes
Relationship
Structural Constraints
Relational Model
Relational Model Constraints
Mapping ER Model to a relational schema
Integrity Constraint
Dr. K.P. Vijayakumar, VIT Chennai, India.
Data Model 3
Collection of Tools for describing
Data
Data relationships
Data semantics
Data constraints
Types of Data Model
Entity Relationship Model
Object Oriented Model Object based
Relational Data Model
Network Model Record based
Hierarchical Data Model
Dr. K.P. Vijayakumar, VIT Chennai, India.
ER Model 4
ER model is the logical representation of
data as objects and relationships among
them.
Designed by Peter Chen and published in
1976
ER Model is a high-level conceptual data
model.
ER model is a design or blueprint of a
database.
ER diagram shows the complete logical
structure of a database.
Dr. K.P. Vijayakumar, VIT Chennai, India.
STUDENT
Name
Jack
Stu_ID
CD0748
Major
CSE
ER Schema 5
John CD0468 MECH Description of a database is called the
COURSE database schema.
Co_Name Co_Id Co_Dept
Pictorial representation of schema is called
Database CS1078 CSE
a schema diagram.
Robotics MH073 MECH
This model is frequently used for the
STUDENT conceptual design of database applications.
Name Stu_ID Major
COURSE
Co_Name Co_Id Co_Dept
Dr. K.P. Vijayakumar, VIT Chennai, India.
ER Diagram 6
ER diagram has three main components
1. Entity
2. Attribute
3. Relationship
Dr. K.P. Vijayakumar, VIT Chennai, India.
Image source : Tutorialspoint
Entities and Attributes 7
Entity
STUDENT
An object or a thing in the real word represents an
Name RegNo Major entity
Ram 22BCE1001 CSE An entity may be an object with a physical existence
John 22BCE1002 MECH Example : person, car, house, employee, student
Entities- Students, courses
COURSE
Co_Name Co_Code Co_Dept
Entity Type
A collection entities that have same attributes, but
Database CS1078 CSE
each entity has its own value(s) for each attribute
Robotics MH073 MECH Entity Set
The collection of all entities of a particular entity
type in the database at any point in time
Dr. K.P. Vijayakumar, VIT Chennai, India.
Source: Ramez Elmasri and Shamkant B. Navathe
Entity Types 8
Types of Entities in ER Model
Strong entity or regular entity
Entity type that do have key attribute
Weak entity or identifying entity
Entity type that do not have key attributes of their
own
Dr. K.P. Vijayakumar, VIT Chennai, India.
Source: Ramez Elmasri and Shamkant B. Navathe
Attributes 9
Attributes
Each entity has attributes—the particular
properties that describe it
Employee Entity
Name, Id, Address, age, designation , salary
Types of Attributes
Simple and Composite
Single valued and multivalued
Stored and Derived
Dr. K.P. Vijayakumar, VIT Chennai, India.
Source: Ramez Elmasri and Shamkant B. Navathe
Attributes 10
Simple and Composite Attribute
Simple or atomic
Attributes that are not divisible into smaller
subparts
Example : age, rollno
Composite
can be divided into smaller subparts
Dr. K.P. Vijayakumar, VIT Chennai, India.
Attributes 11
Single valued and Multi valued Attribute
Single valued
Attribute that consists of single value
Example : age, regno
Multivalued
Attribute that has multiple values
Example
Mobile No.
Degree
Email
Dr. K.P. Vijayakumar, VIT Chennai, India.
Attributes 12
Stored and Derived Attribute
Stored
The value derivable from an attribute which is
referred as stored attribute
Example : DoB
Derived
Value of one attribute can be derived from
another attribute
Example
Age DoB
Dr. K.P. Vijayakumar, VIT Chennai, India.
Attributes 13
NULL values
Several meaning for Null values
Not applicable value
Example : Apartment No, Degree
Value Unknown
Value exists but is not available
Dr. K.P. Vijayakumar, VIT Chennai, India.
Attributes 14
Complex Attributes
composite and multivalued attributes can be nested arbitrarily
Composite - ()
Phone(Area_code, Phone_number)
Address(Street_address (Number, Street, Apartment_no), City, State, Zip)
Multivalued - {}
{ Phone(Area_code,Phone_number) }
Example
Address_phone
Address_phone( Address(Street_address (Number, Street, Apartment_no), City, State,
Zip), {Phone(Area_code,Phone_number)})
Dr. K.P. Vijayakumar, VIT Chennai, India.
Relationship 15
A relationship is an association among
two or more entities.
Within entity-relationship diagrams,
relationships are used to document the
interaction between two entities.
Types of Relationships are usually verbs
such as assign, associate, or track.
Dr. K.P. Vijayakumar, VIT Chennai, India.
Higher Relationship Degree 16
Degree of a relationship is the number of
participating entity types
Binary
Ternary or Higher
Binary relationship
Ternary
Dr. K.P. Vijayakumar, VIT Chennai, India.
Source: Ramez Elmasri and Shamkant B. Navathe
Mapping Cardinalities 17
A B
Mapping cardinalities or cardinality ratios
express the number of entities to which
another entity can be associated via a
relationship set
useful in describing binary relationship sets
One to one
One to many
Many to one
Many to Many
Dr. K.P. Vijayakumar, VIT Chennai, India.
Source: Ramez Elmasri and Shamkant B. Navathe
Mapping Cardinalities 18
a. 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.
b. 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-to-one One-to-many
one entity in A.
Dr. K.P. Vijayakumar, VIT Chennai, India.
Savings Account Mapping Cardinalities 19
One to one One to many
Customer Account Account Fixed Deposit No
Vijay 12345 12345 FD101
Ram 12346 12346 FD102
Sam 12347 12347 FD103
Manas 12348 12348 FD104
FD105
FD106
FD107
Dr. K.P. Vijayakumar, VIT Chennai, India.
Source: Ramez Elmasri and Shamkant B. Navathe
Mapping Cardinalities 20
c. 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.
d. Many-to-many. An entity in A is
associated with any number (zero or
Many-to-one Many-to-many more) of entities in B, and an entity in B
is associated with any number (zero or
more) of entities in A.
Dr. K.P. Vijayakumar, VIT Chennai, India.
Joint Account Mapping Cardinalities 21
Many to One Many to Many
Customer Account Customer Account
Vijay 12345 Vijay 12345
Ram Ram 12346
Sam 12346 Sam 12346
Manas 12347 Manas 12347
Sisir Sisir 12348
12349
Dr. K.P. Vijayakumar, VIT Chennai, India.
Source: Ramez Elmasri and Shamkant B. Navathe
Structural Constraints 23
Partial Participation Participation Constraints
specifies whether the existence of an entity depends
Total Participation on its being related to another entity via the
relationship type.
Total Participation / existence dependency
Partial Participation if every entity in E must participate in at least one
relationship in R
Ex: every entity in the total set of Instructor entities
must be related to a Student entity via Adivsor
relationship
Partial Participation
Total Participation Some entities in E do not participate in relationships
in R
Ex: some part ofDr.set of Instructor entities are related
K.P. Vijayakumar, VIT Chennai, India.
Source: Ramez Elmasri and Shamkant B. Navathe
to a Student entity via Adivsor relationship
Structural Constraints 24
Structural constraints specified using (min, max)
notation
Dr. K.P. Vijayakumar, VIT Chennai, India.
Source: Ramez Elmasri and Shamkant B. Navathe
Stud_Course Keys 25
Name Stu-ID Major Cname Ccode Key is an attribute or collection of attributes
Ram 22bcse1001 CSE DBMS BCSE302L that uniquely identifies an entity among
John 22bcse1002 CSE IWP CSE3002 entity set.
Ram 22bcse1001 CSE OS CSE2005 For example, the roll_number of a student
makes him/her identifiable among students.
Super Key – {Stu-ID,CCode} Super Key − A set of attributes (one or more) that
Candidate Key – {Stu-ID}, {CCode} collectively identifies an entity in an entity set.
Primary Key – {Stu-ID}, {Ccode} Candidate Key − A minimal super key is called a
candidate key. i., each of the keys is called candidate
key.
Stud Course
Primary Key − A primary key is one of the candidate
Name Stu-ID Major Cname Ccode keys chosen by the database designer to uniquely
Ram 22bcse1001 CSE DBMS BCSE302L identify the entity set.
John 22bcse1002 CSE IWP CSE3002
OS CSE2005
Dr. K.P. Vijayakumar, VIT Chennai, India.
Source: Ramez Elmasri and Shamkant B. Navathe
Stud Course Keys 26
Name Stu-ID Major Cname Ccode
DBMS BCSE302L
Foreign Key − An attribute to link two entity
Ram 22bcse1001 CSE
John 22bcse1002 CSE IWP CSE3002
sets through the primary key.
OS CSE2005
Primary Key – {Stu-ID}, {Ccode}
Stud-Course
Stud
Name Stu-ID Major Stu-ID Cname Ccode
Primary Key – {Stu-ID}, {Ccode} Ram 22bcse1001 CSE 22bcse1001 DBMS BCSE302L
Foreign Key – {Stu-ID} John 22bcse1002 CSE 22bcse1001 IWP CSE3002
22bcse1002 OS CSE2005
Dr. K.P. Vijayakumar, VIT Chennai, India.
Source: Ramez Elmasri and Shamkant B. Navathe
Notation of ER(Entity Relationship) 27
Diagram
Dr. K.P. Vijayakumar, VIT Chennai, India.
Entities, Attributes, Relation & Keys 28
EID Name Designation
Employee
Employee ={Eid, Name,Designation}
Eid - Key attribute Employee Entity Set
Dr. K.P. Vijayakumar, VIT Chennai, India.
Entities, Attributes, Relation & Keys 29
DID Name EID
Department
Department={DID,Name, EID}
DID - Key attribute Department Entity Set
Dr. K.P. Vijayakumar, VIT Chennai, India.
ER Diagram 30
EID Name Posting DID Name EID
Employee Works-In Department
Works-ln Relationship Set
Dr. K.P. Vijayakumar, VIT Chennai, India.
Descriptive Attribute 31
Attribute that helps to describe the
relationship
Relationship attribute
Stores the information about the relationship
Students Access VToP Login
Last AccessDate
Descriptive Attribute Dr. K.P. Vijayakumar, VIT Chennai, India.
Descriptive Attribute 32
Dr. K.P. Vijayakumar, VIT Chennai, India.
ER Diagram 33
Entity types for the COMPANY database
• Entity Types : DEPARTMENT, PROJECT, EMPLOYEE, DEPENDENT
1. DEPARTMENT = {Name, Number, Locations, Manager, Manager_start_date}
Locations is multivalued attribute.
Name, Number are key attributes
2. PROJECT = {Name, Number, Location, and Controlling_department}
Name, Number are key attributes
3. EMPLOYEE= {Name, Ssn, Sex, Address, Salary, Birth_date, Department, Supervisor}
Name and Address may be composite attributes;
Name—First_name, Middle_initial, Last_name
4. DEPENDENT = {Ssn, Dependent_name, Sex, Birth_date, Relationship }
(Relationship – Relationship to the employee)
Dr. K.P. Vijayakumar, VIT Chennai, India.
Source: Ramez Elmasri and Shamkant B. Navathe
ER Diagram of Company DB 34
Entity Set and Attributes
Dr. K.P. Vijayakumar, VIT Chennai, India.
ER Diagram of Company DB 35
Dr. K.P. Vijayakumar, VIT Chennai, India.
Extended ER - Specialization and Generalization 36
Generalization is the process of
extracting common properties from a set
entities
Bottom up approach
Ex : Savings_account, Checking_account
can be generalized to Account entity
Specialization is the process of defining a
set of sub entities from an entity type.
Top down approach
For example, the set of subclasses
{Savings_account, Checking_account} is a
specialization of the superclass ACCOUNT.
Top down approach Bottom up approch Dr. K.P. Vijayakumar, VIT Chennai, India.
ER Diagram 37
BANK database
Entity Types : CUSTOMER, ACCOUNT, LOAN,BRANCH,PAYMENT, EMPLOYEE
CUSTOMER = {Customer_Name, Customer_id, Customer_Street, Customer_City}
Customer-id is key attribute
ACCOUNT = {Account_id, Balance}
Account_id is key attributes
LOAN = {Loan_number, Amount}
Loan_number is key attribute
PAYMENT = {Payment_number, Payment_date,Payment_amount)
Payment_number is key attribute
BRANCH = {Branch_name, Branch_city, Assets}
EMPLOYEE= {Employee_Name, Employee_id, Telephone_number, Dependent_name, Start_date}
Employee_id is key attribute
Dr. K.P. Vijayakumar, VIT Chennai, India.
Source: Ramez Elmasri and Shamkant B. Navathe
ER schema Diagram of Bank DB 38
Specialization and
Generalization
Base Entity Set
Sub Entity Sets
Dr. K.P. Vijayakumar, VIT Chennai, India.
Extended ER – Aggregation 39
Aggregation is the process by which
entities are combined to form a single
meaningful entity
The process flow for aggregation in DBMS.
Dr. K.P. Vijayakumar, VIT Chennai, India.
Image Source: Scaler.com, section.io
40
Relation/
Relational Data Model
columns
Table Database is a collection of relations
A1 A2 A3
A Relational model groups data into one or more tables.
t1
rows The data is represented in the form of rows and columns i.e.
t2
tables.
Domain
Set of atomic values
Relation Schema
STUDENT R, denoted by R(A1, A2, ...,An)
Name Stu_ID Major
Student(Name,Stud_ID,Major)
Jack CD0748 CSE
Relation/ Relation State
John CD0468 MECH
r of the relation schema R(A1, A2, ..., An), also denoted
by r(R), is a set of n-tuples r = {t1, t2, ..., tm}.
Dr. K.P. Vijayakumar, VIT Chennai,
India.
41
Relational Data Model
Dr. K.P. Vijayakumar, VIT Chennai,
India.
Stud_Course Keys 42
Name Stu-ID Major Cname Ccode Key is an attribute or collection of attributes that
Ram 22bcse1001 CSE DBMS BCSE302L uniquely identifies an entity among entity set.
John 22bcse1002 CSE IWP CSE3002 For example, the roll_number of a student makes
him/her identifiable among students.
Ram 22bcse1001 CSE OS CSE2005
Super Key − A set of attributes (one or more)
Super Key – {Stu-ID,CCode} that collectively identifies a tuple or row in the
Candidate Key – {Stu-ID}, {CCode} relation schema.
Primary Key – {Stu-ID}, {Ccode} Candidate Key − A minimal super key is called a
candidate key. i.e, a relation schema may have
more than one key. In this case, each of the keys
Stud Course is called a candidate key.
Name Stu-ID Major Cname Ccode
Primary Key − A primary key is one of the
Ram 22bcse1001 CSE DBMS BCSE302L candidate keys chosen to uniquely identify a
John 22bcse1002 CSE IWP CSE3002 tuple or row in the relation schema.
OS CSE2005
Dr. K.P. Vijayakumar, VIT Chennai, India.
Source: Ramez Elmasri and Shamkant B. Navathe
Stud Course Keys 43
Name Stu-ID Major Cname Ccode
DBMS BCSE302L
Foreign Key − An attribute to link two
Ram 22bcse1001 CSE
John 22bcse1002 CSE IWP CSE3002
relation schemas through the primary key.
OS CSE2005
Primary Key – {Stu-ID}, {Ccode}
Stud-Course
Stud
Name Stu-ID Major Stu-ID Cname Ccode
Primary Key – {Stu-ID} Ram 22bcse1001 CSE 22bcse1001 DBMS BCSE302L
Foreign Key – {Stu-ID} John 22bcse1002 CSE 22bcse1001 IWP CSE3002
22bcse1002 OS CSE2005
Dr. K.P. Vijayakumar, VIT Chennai, India.
Source: Ramez Elmasri and Shamkant B. Navathe
44
Stud Referential Integrity
Name Stu-ID Major
• It ensure that a value that appears in one relation
Ram 22bcse1001 CSE
for a given set of attributes also appears for a certain
John 22bcse1002 CSE
set of attributes in another relation.
Ishan 22bce1003 CSE
• Observe the following rules:
Stud-Course 1. Can’t enter a value in the foreign key column
Stu-ID Cname Ccode
of the related table that does not exist in the
22bcse1001 DBMS BCSE302L
primary key of the primary key table.
22bcse1001 IWP CSE3002
2. Can’t delete a row from a primary key table if
22bcse1002 OS CSE2005
matching rows exist in a related table
3. Can’t change a primary key value in the
1. Try to insert 22bce1004 into Stud- primary key table if matching values exist in a
Course
related table.
2. Try to delete 22bce1002 from Stud
3. Try to modify 22bce1001 in Stud
Dr. K.P. Vijayakumar, VIT Chennai,
Source: Ramez Elmasri and Shamkant B. Navathe
India.
45
Integrity Constraints
• It ensure that changes made to the database by
authorized users do not result in a loss of data
consistency
1. NOT NULL - prohibits the insertion of a null
value for the attribute
2. UNIQUE - unique values are permitted, null is
also allowed
3. CHECK - attribute values satisfy specified
conditions
Dr. K.P. Vijayakumar, VIT Chennai,
Source: Ramez Elmasri and Shamkant B. Navathe
India.
46
Relational Model Constraints
1. Domain constraint
• Set of possible values that may be
associated with an attribute
• Data types - Integers, Real numbers,
characters, boolean, string, date,time etc..
• range
2. Key Constraints and constraints on Null Values
• Super Key, Candidate key, Primary Key and
Unique key
3. Entity Integrity Constraints
• Primary Key
4. Referential Integrity Constraints
• Foreign Key
5. Other types of Constraints
Source: Ramez Elmasri and Shamkant B. Navathe • Triggers and Assertions
Dr. K.P. Vijayakumar, VIT Chennai,
India.
47
Mapping ER to a Relational Schema
• ER-to-Relational Mapping Algorithm (or)
Reducing E-R Diagrams to Relational Schemas
ER Diagram 1. Mapping of Regular Entity Types
2. Mapping of Weak Entity Types
3. Mapping of Binary 1:1 Relationship Types
• Foreign key approach,
• Merged relation approach
• Cross-reference or relationship relation
Relational Schema approach
4. Mapping of Binary 1:M Relationship Types
• Foreign key approach,
• Cross-reference or relationship relation
approach
Dr. K.P. Vijayakumar, VIT Chennai,
Source: Ramez Elmasri and Shamkant B. Navathe
India.
48
Mapping ER Model to a Relational Schema
• ER-to-Relational Mapping Algorithm
ER Model 5. Mapping of Binary M:N Relationship Types
• Create a new relation S to represent R
• Cross-reference or relationship relation
approach
• No multivalued attributes
6. Mapping of Multivalued Attributes
Relational Schema • Create a new relation R
• Cross-reference or relationship relation
approach
7. Mapping of N-ary Relationship Types
Dr. K.P. Vijayakumar, VIT Chennai,
Source: Ramez Elmasri and Shamkant B. Navathe
India.
ER schema Diagram of Company DB 49
Dr. K.P. Vijayakumar, VIT Chennai, India.
Source: Ramez Elmasri and Shamkant B. Navathe
50
Mapping ER Model to a Relational Schema
• ER-to-Relational Mapping Algorithm
1. Mapping of Regular Entity Types
Relational Schema
1. Entity Types – Regular/Strong
Primary Key
Dr. K.P. Vijayakumar, VIT Chennai,
India.
51
Mapping ER Model to a Relational Schema
• ER-to-Relational Mapping Algorithm
2. Mapping of Weak Entity Types
Relational Schema
2. Mapping of Weak Entity Types
Foreign Key
Dr. K.P. Vijayakumar, VIT Chennai,
India.
52
Mapping ER Model to a Relational Schema
• ER-to-Relational Mapping Algorithm
2. Mapping of Weak Entity Types
Foreign Key
Refers
Primary Key Dr. K.P. Vijayakumar, VIT Chennai,
India.
53
Mapping ER Model to a Relational Schema
• ER-to-Relational Mapping Algorithm
3. Mapping of Binary 1:1 Relationship Types
• Foreign key approach
• Merged relation approach
• Cross-reference or relationship
relation approach
Dr. K.P. Vijayakumar, VIT Chennai,
India.
54
Mapping ER Model to a Relational Schema
• ER-to-Relational Mapping Algorithm
3. Mapping of Binary 1:1 Relationship Types
Primary Key
Relational Schema • Foreign key approach
Foreign Key
Dr. K.P. Vijayakumar, VIT Chennai,
India.
55
Mapping ER Model to a Relational Schema
• ER-to-Relational Mapping Algorithm
3. Mapping of Binary 1:1 Relationship Types
Primary Key
Relational Schema • Merged relation approach
• merge the two entity types and
the relationship into a single
relation
• possible when both
participations are total
Foreign Key
Dr. K.P. Vijayakumar, VIT Chennai,
India.
56
Mapping ER Model to a Relational Schema
• ER-to-Relational Mapping Algorithm
3. Mapping of Binary 1:1 Relationship Types
Primary Key
Relational Schema • Cross-reference or relationship
relation approach
Foreign Key
Dr. K.P. Vijayakumar, VIT Chennai,
India.
57
Mapping ER Model to a Relational Schema
• ER-to-Relational Mapping Algorithm
4. Mapping of Binary 1:N Relationship Types
• Foreign key approach
Dr. K.P. Vijayakumar, VIT Chennai,
India.
58
Mapping ER Model to a Relational Schema
• ER-to-Relational Mapping Algorithm
4. Mapping of Binary 1:N Relationship Types
Relational Schema • Foreign key approach
Primary Key Foreign Key
Dr. K.P. Vijayakumar, VIT Chennai,
India.
59
Mapping ER Model to a Relational Schema
• ER-to-Relational Mapping Algorithm
4. Mapping of Binary 1:N Relationship Types
Relational Schema • Cross-reference or relationship
relation approach
Primary Key Foreign Key
Dr. K.P. Vijayakumar, VIT Chennai,
India.
60
Mapping ER Model to a Relational Schema
Primary Key • ER-to-Relational Mapping Algorithm
4. Mapping of Binary 1:N Relationship Types
Relational Schema • Foreign key approach
Foreign Key
Dr. K.P. Vijayakumar, VIT Chennai,
India.
61
Mapping ER Model to a Relational Schema
• ER-to-Relational Mapping Algorithm
4. Mapping of Binary 1:N Relationship Types
Relational Schema • Cross-reference or relationship
relation approach
Dr. K.P. Vijayakumar, VIT Chennai,
India.
62
Mapping ER Model to a Relational Schema
• ER-to-Relational Mapping Algorithm
5. Mapping of Binary M:N Relationship Types
• Create a new relation S to represent R
foreign key attributes {Essn, Pno}.
Dr. K.P. Vijayakumar, VIT Chennai,
India.
63
Mapping ER Model to a Relational Schema
• ER-to-Relational Mapping Algorithm
5. Mapping of Binary M:N Relationship Types
• Cross reference approach
Dr. K.P. Vijayakumar, VIT Chennai,
India.
64
Mapping ER Model to a Relational Schema
• ER-to-Relational Mapping Algorithm
6. Mapping of Multivalued Attributes
• Create a new relation R
Multivalued Attributes
Dr. K.P. Vijayakumar, VIT Chennai,
India.
65
Result of Mapping ER Model to a Relational Schema
Dr. K.P. Vijayakumar, VIT Chennai,
Source: Ramez Elmasri and Shamkant B. Navathe
India.
66
Result of Mapping ER Model to a Relational Schema
Dr. K.P. Vijayakumar, VIT Chennai,
Source: Ramez Elmasri and Shamkant B. Navathe
India.
67
Mapping ER Model to a Relational Schema
• ER-to-Relational Mapping Algorithm
7. Mapping of N –ary Relationship types
Dr. K.P. Vijayakumar, VIT Chennai,
India.
68
Mapping ER and EER to a Relational Schema
• ER and Extended ER to a Relation Schema
Employee
ISA
Secretary Technician Engineer
Dr. K.P. Vijayakumar, VIT Chennai,
India.
69
END OF MODULE - 2
Dr. K.P. Vijayakumar, VIT Chennai, India.