-1- Database Systems
Lesson 8
Objectives
Transformation of (E)ER Model into Relational Model
o Attributes
Simple
Composite
Multivalued
o Entities
Regular
Weak
Associative
o Relationship
Unary
One-to-one
One-to-Many
Many-to-Many
Binary
One-to-one
o Minimum/Maximum same on both
o Optional-Mandatory
One-to-Many
Many-to-Many
Ternary
Quaternary
o Super Type
o Subtype
Regular Entities
Regular entities are mapped to relations.
Example
Student
Above entity “Student” will be transformed into Relation
Student (………..)
Attributes
Simple attributes
E-R attributes map directly onto the columns/fields of relation
Example
-2- Database Systems
CUSTOM
ER entity
type with
simple
attributes
CUSTOMER relation
In the above example regular entity “Customer” mapped into relation and simple
attributes mapped as fields/columns of relation.
Composite attributes
Use only their simple component (attributes). In other words simple components of
composite will be mapped as fields/columns of relation.
Example
CUSTOME
R entity
type with
composite
attribute
CUSTOMER relation with address detail
In the above example simple components (zip, state, city, street) of customer_address
becomes the columns of relation (customer).
-3- Database Systems
Multivalued Attribute
Separate relation will be created for each multivalued attributes with a foreign key
taken from the superior entity.
Example
Multivalued attribute becomes a separate relation
1–to–many relationship between original entity a
In the above example a separate relation “Employee_Skill” is created for multivalued
“Skill” with foreign key “Employee_ID” from Employee.
Weak Entities
o Becomes a separate relation with a foreign key taken from the superior
entity
o Primary key composed of:
Partial identifier of weak entity
Primary key of identifying relation (strong entity)
Example
-4- Database Systems
Above ER model having weak entity “Dependent” will be transformed into relational
as follow.
NOTE: the domain
constraint for the
foreign key should
NOT allow null
value if
Foreign
keyDEPENDENT is a
weak entity
Composite primary key
In the above example “Dependent” is a weak entity. It is transformed into relation with
foreign key from Employee. Dependent also have composite attribute, so simple
components are becomes fields/column.
Associative Entity
Identifier Not Assigned
o Default primary key for the association relation is composed of the
primary keys of the two entities (as in M:N relationship)
-5- Database Systems
Example
In the above example “Order Line” is an associative entity. A separate relation is
created for Order line
o Identifier Assigned
o It is natural and familiar to end-users
o Default identifier may not be unique
-6- Database Systems
Relationship
Unary Relationship
One-to-Many Relationship
Recursive foreign key in the same relation
Example
-7- Database Systems
EMPLOYEE
entity with
Manages
relationship
EMPLOYE
E relation
with
recursive
foreign key
Many-to-Many Relationship
Bill-of-
materials
relationships
(M:N)
ITEM
and
COMPO
NENT
relations
-8- Database Systems
Binary Relationship
One-to-One Relationship
Primary key on the mandatory side becomes a foreign key on the optional side
Example
One-to-Many Relationship
Primary key on the one side becomes a foreign key on the many side
-9- Database Systems
Again, no null
value in the
foreign key…
this is because
of the Foreig
mandatoryn key
minimum
cardinality
Many-to-Many Relationship
Create a new relation with the primary keys of the two entities as its primary key
Example
-10- Database Systems
The Supplies relationship will need to become a separ
Composite
primary key New
Foreign interse
key Foreign ction
key
relatio
n
Ternary/Quaternary Relationship
One relation for each entity and one for the associative entity
Associative entity has foreign keys to each entity in the relationship
Example
-11- Database Systems
Remember that
the primary
key MUST be
unique
-12- Database Systems
Mapping Super type/Subtype Relationships
One relation for super type and for each subtype
Super type attributes (including identifier and subtype discriminator) go into super
type relation
Subtype attributes go into each subtype; primary key of super type relation also
becomes primary key of subtype relation
1:1 relationship established between super type and each subtype, with super type
as primary table
-13- Database Systems
These are
implemented as
one-to-one
relationships