Database Design II: Logical
Modelling
FIT2094
MONASH
INFORMATION
TECHNOLOGY
2
Revisit – Week 3 Conceptual Model
Keys only
3
Revisit – Week 3 Conceptual Model
All attributes
4
Summary of Terminologies at Different Levels
Conceptual Logical Physical
Entity Relation Table
Attribute Attribute Column
Instance Tuple Row
Identifier Primary Key Primary Key
Relationship — —
— Foreign Key Foreign Key
5
Properties of Relations
▪ Some properties to be considered:
– Each relation has a unique name in the database.
– Each row is unique – i.e. duplicate tuples are not allowed.
– Each column has a (meaningful) name.
– The order of attributes is immaterial.
– The order of tuples is immaterial.
– The entries are single-valued (atomic) – each cell contains a
single entry.
• Multi-valued and composite attributes???
6
Transforming ER diagrams into relations
(mapping conceptual level to logical level)
▪ The steps are:
• Map strong (regular) entities
• Map weak entities
• Map binary relationships
• Map associative entities
• Map unary relationships
• Map ternary relationships
• Map supertype/subtype relationships (is not part of this
unit).
7
Q1. The relational model requires that each cell in a
relation is single-valued (atomic). Considering this
requirement, what construct in an ER diagram cannot be
implemented directly (eg. without adding further entities)
in the relational model (logical level)?
a. Composite key.
b. Composite attribute.
c. Multi-valued attribute.
d. Dependent attribute.
e. More than one option is correct.
8
Map Regular Entities
▪ Composite Attributes
– When the regular entity type contains a composite
attribute, only the simple component attributes of the
composite attribute are included in the new relation.
– Compared to composite attributes, simple attributes not only
improve data accessibility but also help in maintaining data
quality
9
Mapping a Composite Attribute
Monash Software Case Study
* = not null (must have value)
10
Map Regular Entities
▪ Multivalued Attribute
– When the regular entity type contains a multivalued
attribute, two new relations are created.
– The first relation contains all the attributes of the entity type
except the multivalued attribute itself.
– The second relation contains two attributes that form the PK.
One of the attributes is the PK from the first relation, which
becomes the FK in the second relation and the other is the
multivalued attribute.
– There can also be non key attributes in the second relation
depending upon the data requirements.
11
Mapping a Multi valued Attribute
Is there a better solution than the one shown above?
12
Revisit – Week 3 Conceptual Model – IMPROVED
All attributes
13
Mapping a Weak Entity
▪ For each weak entity type, create a new relation and include all of the simple
attributes as attributes of this relation. The PK of the identifying relation is
also included as the FK in this new relation.
14
LogicalConceptual
15
Mapping a 1:M Binary Relationship
16
CUSTOMER ORDER
Q2. Where would you place the Foreign Key when you
map this ER diagram into the relational model?
a. CUSTOMER
b. ORDER
c. Both CUSTOMER and ORDER.
d. None, no FK is needed.
17
Map Binary Relationships (1:M)
For each 1:M binary relationship, first create a relation for each of the
two entity types participating in the relationship. Then include the PK
attribute (or attributes) of the entity on the one-side of the relationship
as the FK on the many-side of the relationship.
18
LogicalConceptual
19
Mapping a M:N Binary Relationship
Order_ID
Order_date
ORDER PRODUCT
Product_ID
Unit_price
20
Q3. What will be the Primary Key of the new created
relation resulting from mapping this ER model at the
conceptual level into a relational model?
a. The primary key of the ORDER table.
b. The primary key of the PRODUCT table.
c. The combination of primary keys of
ORDER and PRODUCT.
Order_ID
Order_date
ORDER PRODUCT
Product_ID
Unit_price
21
22
Map Binary Relationship (M:N)
▪ For a M:N binary relationship
– First create a relation for each of the two entity types
participating in the relationship.
– Then create a new relation and include as foreign key
attributes, the PK attribute (or attributes) for each of the two
participating entity types. These attributes become the PK of
the new relation.
– If there are any nonkey attributes associated with the M:N
relationship, they are also included in the new relation.
23
LogicalConceptual
24
Mapping an associative entity with an Identifier
25
Mapping a 1:1 Binary Relationship
Nurse_ID
Date_of_Birth
NURSE CARE CENTRE
Centre_Name
Location
26
Q4. Where would you place the Foreign Key when
mapping this ER diagram into a relational model?
A. NURSE
B. CARE CENTRE
C. Both NURSE and CARE CENTRE
D. No FK is needed.
Nurse_ID
Date_of_Birth
NURSE CARE CENTRE
Centre_Name
Location
27
28
Map Binary Relationship (1:1)
▪ Create two relations, one for each of the participating entity
types.
– The primary key (PK) on the mandatory side of the
relationship becomes the foreign key (FK) on the optional
side of the relationship.
– where both are optional place the FK on the side which
causes the fewest nulls
– Special case: 1:1 total relationship (mandatory participation
on both sides)
• Consider consolidating the two entity types into one
relation
29
LogicalConceptual
30
Map unary relationships
▪ Unary Relationship is a relationship between the instances of a
single entity type.
▪ Unary 1:M Relationship – A relation is created for the entity
type. Add a FK within the same relation that references the PK of
the relation. A recursive foreign key is a FK in a relation that
references the PK values of the same relation.
▪ Unary M:N Relationship – Two relations are created, one for
the entity type in the relationship and the other as the associative
relation to represent the M:N relationship itself. The PK of the
associative relation consists of two attributes (with different
names) taking their values from the PK of the other relation.
31
Mapping a 1:M Unary Relationship
32
LogicalConceptual
33
Q5. How many relations/tables and relationships do we
need to implement the model below into a relational
model?
a. 2 tables, 1 relationship
b. 2 tables, 2 relationships
c. 3 tables, 2 relationships
d. 4 tables, 3 relationships
34
Mapping a M:N Unary Relationship
35
SQL Developer Data Modeler
36
Adding surrogate keys
Potential problem:
Need to ensure that the identified
key from the conceptual model
(emp_no, training_code, et_date_completed)
will still remain unique
– define a unique index on attributes of
key
Surrogate PK’s may be added
ONLY on the logical model provided
they are justified (include in
documentation / assumptions)
37
Ternary Relationships
modelled as binary:
Ternary
38
Ternary Relationships – model as binary
relationships?
▪ Ternary represents more information than three binary
relationships
▪ For example – Supplier 1 supplies Project 2 with Part 3 –
– ternary
• instance (supplier 1, project 2, part 3) exists
– binaries
• instances
– (supplier1, project 2) (project 2, part 3) (supplier 1,
part 3)
• BUT does not imply (supplier 1, project 2, part 3)
▪ How then do we map such relationships?
39
Mapping a Ternary Relationship
40
Map Ternary (and n-ary) Relationships
▪ Ternary relationship should be converted to an associative entity.
– To map an associative entity type that links three regular
entity types, an associative relation is created.
– The default PK of this relation consists of the three PK
attributes for the participating entity types.
– Any attributes of the associative entity type become
attributes of the new relation.
41
Mapping a Ternary Relationship
42
Reference
Hoffer, J. A. , Prescott, M. B. & McFadden, F. R.
“Modern Database Management”