Balochistan University of Information Technology, Engineering & Management Sciences
Introduction to
Database Systems
Mohammad Imran
Lecturer
Department of Information Technology
Balochistan University of Information Technology, Engineering & Management Sciences
Introduction to Database Systems Spring 2015 Mohammad Imran May 4, 2015 1
Balochistan University of Information Technology, Engineering & Management Sciences
Lecture 6
Mapping ERD to
Relational Model
Introduction to Database Systems Spring 2015 Mohammad Imran May 4, 2015 2
Balochistan University of Information Technology, Engineering & Management Sciences
So far…
• We have seen different modeling techniques to model the
database
• How to implement these models in original database
design?
Introduction to Database Systems Spring 2015 Mohammad Imran May 4, 2015 3
Balochistan University of Information Technology, Engineering & Management Sciences
Convert ER Model to
Relational Model
• The process is called mapping of ER model to relational
model
• Apparently Converting ERD to Relational Database Design
Introduction to Database Systems Spring 2015 Mohammad Imran May 4, 2015 4
Balochistan University of Information Technology, Engineering & Management Sciences
Review - Concepts
Relational Model is made up of tables
• A row of table = a relational instance/tuple
• A column of table = an attribute
• A table = a schema/relation
• Cardinality = number of rows
• Degree = number of columns
Introduction to Database Systems Spring 2015 Mohammad Imran May 4, 2015
Balochistan University of Information Technology, Engineering & Management Sciences
Review - Example
Attribute
Cardinality = 2
tuple/relational
instance SID Name Major GPA
1234 Bilal CS 2.8
5678 Ali EE 3.6
4 Degree
A Schema / Relation
Introduction to Database Systems Spring 2015 Mohammad Imran May 4, 2015 6
Balochistan University of Information Technology, Engineering & Management Sciences
Review Entity Set
• An Entity Set is a
o set of entities of the same type
o e.g.
• All persons having an account at a bank
• All students studying in a university
• All patients being checked up in hospital
Introduction to Database Systems Spring 2015 Mohammad Imran May 4, 2015 7
Balochistan University of Information Technology, Engineering & Management Sciences
Strong Entity Set
• An entity set that has a primary key is termed as strong
entity set
• May I have some examples of Strong Entity Sets from you
people?
o You have already done it
Introduction to Database Systems Spring 2015 Mohammad Imran May 4, 2015 8
Balochistan University of Information Technology, Engineering & Management Sciences
Example – Strong Entity Set
SID Name SSN Name
Student Advisor Professor
Major Dept
GPA
SID Name Major GPA SSN Name Dept
1234 Hamza IT 3.9 9999 Babar EE
5678 Rimsha ENG 3.6 8888 Imran IT
Introduction to Database Systems Spring 2015 Mohammad Imran May 4, 2015 9
Balochistan University of Information Technology, Engineering & Management Sciences
Weak Entity Set
• A weak entity is one that can only exist when owned by
another one
• An entity set that does not have sufficient attributes to form a
primary key
• A weak entity is existence dependent, i.e. existence of a weak
entity depends on the existence of an identifying entity set
• The Discriminator (or partial key) of a weak entity set is the
set of attributes that distinguishes among all the entities of
a weak entity set
Introduction to Database Systems Spring 2015 Mohammad Imran May 4, 2015 10
Balochistan University of Information Technology, Engineering & Management Sciences
Weak Entity Set (Contd.)
• The primary key of a weak entity set is formed by the
primary key of the strong entity set on which the weak
entity set is existence dependent, plus the weak entity
set’s discriminator (keep minimum possible attributes for
discriminator)
Introduction to Database Systems Spring 2015 Mohammad Imran May 4, 2015 11
Balochistan University of Information Technology, Engineering & Management Sciences
Weak Entity Set
ERD Representation
• Weak entity set is represented by double rectangle in ERD
• Relationship between strong and weak entity set is
represented by double diamond in ERD called as Identifying
Relationship
• Line connecting Weak entity set to identifying relationship
is bold or double-lined (Total Participation - One to many)
• Underline the discriminator of a weak entity set with a
dashed line
Introduction to Database Systems Spring 2015 Mohammad Imran May 4, 2015 12
Balochistan University of Information Technology, Engineering & Management Sciences
Example Weak Entity
• A ROOM (Weak) can only exist in a BUILDING (Strong)
• A TIRE might be considered as a strong entity because it also
can exist without being attached to a CAR
• A company insurance policy insures an employee and any
dependents
o DEPENDENT (Weak) cannot exist without the EMPLOYEE
(Strong); that is, a person cannot get insurance coverage as a
dependent unless the person is a dependent of an employee
• DEPENDENT is the weak entity in the relationship
o "EMPLOYEE has DEPENDENT"
Introduction to Database Systems Spring 2015 Mohammad Imran May 4, 2015 13
Balochistan University of Information Technology, Engineering & Management Sciences
DB Modeling & Implementation
Ideas
Database
Relational Physical
Model
Schema Storage
(E/R)
Complex file organization
Diagrams (E/R) Tables:
column names: attributes
rows: tuples
Introduction to Database Systems Spring 2015 Mohammad Imran May 4, 2015 14
Balochistan University of Information Technology, Engineering & Management Sciences
Why do we need both ER and relational models?
• Relational model has just a • ER model is richer: entities,
single concept: Tables relationships, attributes, etc.
o Allow us to express queries at o well-suited for capturing
a very high level application requirements
o well-suited for efficient o not so well-suited for
manipulations on computers computer implementation (no
query language)
Introduction to Database Systems Spring 2015 Mohammad Imran May 4, 2015 15
Balochistan University of Information Technology, Engineering & Management Sciences
Relational Model
Products:
Name Price Category Manufacturer
Gizmo $19.99 Gadgets GizmoWorks
PowerGizmo $29.99 Gadgets GizmoWorks
SingleTouch $149.99 Photography Canon
MultiTouch $203.99 Household Hitachi
Introduction to Database Systems Spring 2015 Mohammad Imran May 4, 2015 16
Balochistan University of Information Technology, Engineering & Management Sciences
Concepts Cleared?
• So we had a review of previous concepts related to ERD
• Lets move on and see how to convert an EDR to
relational Model
Introduction to Database Systems Spring 2015 Mohammad Imran May 4, 2015 17
Balochistan University of Information Technology, Engineering & Management Sciences
Mapping Process
Lets get started
Introduction to Database Systems Spring 2015 Mohammad Imran May 4, 2015 18
Balochistan University of Information Technology, Engineering & Management Sciences
Entities Mapping
• An entity set within ER diagram is turned into a table
• You may preferably keep the same name for the entity or
give it a sensible name
• But avoid DBMS reserved words as well as avoid the use
of special characters
Introduction to Database Systems Spring 2015 Mohammad Imran May 4, 2015 19
Balochistan University of Information Technology, Engineering & Management Sciences
Attributes Mapping
• Each attribute turns into a column (attribute) in the table
• The key attribute of the entity is the primary key of the
table which is usually underlined
• It can be composite if required but can never be null
It is highly recommended that every table should start with its
primary key attribute conventionally named as TablenameID
Introduction to Database Systems Spring 2015 Mohammad Imran May 4, 2015 20
Balochistan University of Information Technology, Engineering & Management Sciences
Initial Mapping Schema
• The initial relational schema is expressed in the following
format writing the table names with the attributes list
inside a parentheses
Persons( personID , firstName, lastName, eMail )
Introduction to Database Systems Spring 2015 Mohammad Imran May 4, 2015 21
Balochistan University of Information Technology, Engineering & Management Sciences
Example – Strong Entity Set
SID Name TID Name
Student Advisor Professor
Major Dept
GPA
SID Name Major GPA TID Name Dept
1234 John CS 2.8 9999 Smith Math
5678 Mary EE 3.6 8888 Lee CS
Introduction to Database Systems Spring 2015 Mohammad Imran May 4, 2015 22
Balochistan University of Information Technology, Engineering & Management Sciences
Example – Weak Entity Set
Age
TID Name Name
Teacher owns Children
Deptt: Address
Age Name Teacher_TID
10 Ali 1234
8 Sidra 5678
* Primary key of Children is Teacher_TID + Name
Introduction to Database Systems Spring 2015 Mohammad Imran May 4, 2015 23
Balochistan University of Information Technology, Engineering & Management Sciences
Mapping Relationships
to ER Model
Introduction to Database Systems Spring 2015 Mohammad Imran May 4, 2015 24
Balochistan University of Information Technology, Engineering & Management Sciences
1:1 Relationships Mapping
• Place the primary key of the relation (table/ entity) A
within the table of the relation B as Foreign key
• Or place the Primary Key of Relation B in table of relation
A as Foreign Key
Introduction to Database Systems Spring 2015 Mohammad Imran May 4, 2015 25
Balochistan University of Information Technology, Engineering & Management Sciences
1:1 Relationships Mapping (Contd.)
• Consider the following one to One relationship
Attribute 1
EntBID
EntAID Attribute 1
1 1
Entity A Relates to Entity B
Attribute 2
Introduction to Database Systems Spring 2015 Mohammad Imran May 4, 2015 26
Balochistan University of Information Technology, Engineering & Management Sciences
1:1 Relationships Mapping (Contd.)
• Following can be mapping of diagram shown in previous
slide:
o EntityA( EntAID , attribute1, attribute2)
o EntityB ( EntBID , EntAID, attribute1)
• OR Vice Versa can be used
o EntityA( EntAID , EntBID, attribute1, attribute2)
o EntityB ( EntBID , attribute1)
*Bold and italics are Foreign Keys, underlined are Primary Keys
Introduction to Database Systems Spring 2015 Mohammad Imran May 4, 2015 27
Balochistan University of Information Technology, Engineering & Management Sciences
1:N Relationships Mapping
• This is the tricky part!
• Use attributes in the same way as 1:1 relationship
• But we have only one choice as opposed to two choices like
1:1 relationships mapping
• Taking example:
o Person can have a House from zero to many , but a House
can have only one Person
o To represent such relationship the personid as the Parent node
must be placed within the Child table as a foreign key but not
the other way
Introduction to Database Systems Spring 2015 Mohammad Imran May 4, 2015 28
Balochistan University of Information Technology, Engineering & Management Sciences
1:N Relationships Mapping (Contd.)
Introduction to Database Systems Spring 2015 Mohammad Imran May 4, 2015 29
Balochistan University of Information Technology, Engineering & Management Sciences
1:N Relationships Mapping (Contd.)
• Following is the mapping of
diagram on the right:
• Persons( personid , name,
lastname, email )
House ( houseid , num ,
address, personid)
• Simply – Put PK of 1
cardinality relation as FK in
M cardinality relation
Introduction to Database Systems Spring 2015 Mohammad Imran May 4, 2015 30
Balochistan University of Information Technology, Engineering & Management Sciences
M:N Relationships Mapping
• Use separate tables to express such type of relationship
• New Table should include, PKs of both relations as FK, attributes of
relationship (if any)
• PK of the new relation is usually composite: simply combine both
FKs. If this is not unique, include additional fields as needed
• Example:
o The Person can live or work in many countries
o A country can have many people
o To express this relationship within a relational schema we use a
separate table
Introduction to Database Systems Spring 2015 Mohammad Imran May 4, 2015 31
Balochistan University of Information Technology, Engineering & Management Sciences
M:N Relationships Mapping (Contd.)
Introduction to Database Systems Spring 2015 Mohammad Imran May 4, 2015 32
Balochistan University of Information Technology, Engineering & Management Sciences
M:N Relationships Mapping (Contd.)
• Mapping of diagram on
the right should be:
• Persons( personid ,
name, lastname, email )
Countries ( countryid ,
name, code)
HasRelat ( hasrelatid ,
personid , countryid)
Introduction to Database Systems Spring 2015 Mohammad Imran May 4, 2015 33
Balochistan University of Information Technology, Engineering & Management Sciences
Convert ERD to Relational Model
Introduction to Database Systems Spring 2015 Mohammad Imran May 4, 2015 34
Balochistan University of Information Technology, Engineering & Management Sciences
Convert ERD to Relational Model - Solution
• Company( CompanyID , name)
• Staff( StaffID, name, dob, address, CompanyID)
• Task ( TaskID , description)
Perform(Perform ID, StaffID , TaskID)
or
• Perform(StaffID , TaskID)
• Child( ChildName , StaffID)
Introduction to Database Systems Spring 2015 Mohammad Imran May 4, 2015 35
Balochistan University of Information Technology, Engineering & Management Sciences
Thank you
37
Introduction to Database Systems Spring 2015 Mohammad Imran May 4, 2015
