MONASH
INFORMATION TECHNOLOGY
Database Design II: Logical Modelling
Reference
Several of the examples and diagrams used this week have been taken from:
Hoffer, J. A. , Prescott, M. B. & McFadden, F. R. “Modern Database Management”
2
2
Step 2 (and 3) of the Design Process
▪ Step 1 Conceptual Model (week 2) – Database Model independent
▪ Step 2 Logical Model (this week)
– Select which type (model) of database you wish to implement your
conceptual model in
• Network, Relational, OO, XML, NoSQL, …
– Database model dependent ▪ Step 3 Physical Model
– Select which specific vendor for your chosen model you will implement in • Oracle, MySQL, IBM DB2, SQL Server, …
– Database vendor dependent
– Final output schema file to implement model (for relational model a set of
tables)
3
3
Summary of Terminologies at Different Levels
Conceptual Logical (Relational) Physical (Relational)
Entity Relation Table
Attribute Attribute Column
Instance Tuple Row
Identifier Primary Key Primary Key
Relationship — —
— Foreign Key Foreign Key
4
4
Recap Week 3 Relational Model Characteristics
▪ Each relation must have a unique name
▪ Each attribute of a relation must have a distinct name within the relation
▪ An attribute cannot be multivalued (consist of repeating values)
▪ All values of an attribute need to be from the same domain
▪ The order of attributes and tuples in a relation is immaterial
▪ Each relation must have a primary key
▪ Logical (not physical) connections are made between relations by virtue of primary/foreign keys
5
5
6
6
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 (for example, 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.
All required attributes shown
Revisit – Week 2 Conceptual Model
7
7
Transforming ER diagrams into relations (mapping
▪ Essentially
– KEY to PK
conceptual level to logical level)
– Represent relationships with PK/FK pairs ▪ 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).
8
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
– Client input needed in some cases to determine if to be left as simple or broken into components
9
9
Mapping a Composite Attribute
Monash Software Case Study
* = not null (must have value)
10 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 11
Mapping a Multi valued Attribute
Is there a better solution than the one shown above?
What are the issues here – this was partially discussed in week 2
12 12
Revisit – Week 2 Conceptual Model – IMPROVED
Note: Surrogate key of skill_code has been added
13 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 14
Conceptual
** Note: logical model must have verbs on relationships, excluded on slides for clarity
Logical
15 15
Mapping a 1:M Binary Relationship
16 16
17 17
Q2. Where would you place the Foreign Key when you map this ER diagram into the relational model?
ORDER
CUSTOMER
A. CUSTOMER
B. ORDER
C. Both CUSTOMER and ORDER. D. None, no FK is needed.
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 18
Conceptual
Logical
19 19
Mapping a M:N Binary Relationship
ORDER PRODUCT
Order_ID Order_date
Product_ID Unit_price
20 20
21 21
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. B. C.
ORDER PRODUCT
The primary key of the ORDER table.
The primary key of the PRODUCT table.
The combination of primary keys of ORDER and PRODUCT.
Order_ID Order_date
Product_ID Unit_price
22 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 23
Conceptual Logical
24 24
Mapping an associative entity with an Identifier
25 25
Mapping a 1:1 Binary Relationship
NURSE
CARE CENTRE
Nurse_ID Date_of_Birth
Centre_Name Location
26 26
27 27
Q4. Where would you place the Foreign Key when mapping this ER diagram into a relational model?
NURSE CARE CENTRE
A. NURSE
B. CARE CENTRE
C. Both NURSE and CARE CENTRE
D. No FK is needed.
Nurse_ID Date_of_Birth
Centre_Name Location
Relationship Participation Mandatory vs Optional
NURSE participation in this relationship? CARECENTRE participation in this relationship?
28 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 from both sides)
• Consider consolidating the two entity types into one relation
29 29
Conceptual Logical
30 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 31
Mapping a 1:M Unary Relationship
32 32
Conceptual Logical
33 33
34 34
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
Mapping a M:N Unary Relationship
35 35
SQL Developer Data Modeler
36 36
Adding surrogate keys
Potential problem:
Need to ensure that the identified key from the conceptual model – the natural key:
(emp_no, training_code, et_date_completed) will still remain unique
Solution, where needed:
Define a unique index on the attributes of natural key
Surrogate PK’s may be added ONLY on the logical model provided they are justified (include in documentation / assumptions).
MANUALLY add new PK attribute (here et_no), DO NOT USE SQL Developers “Create Surrogate Key” option
37 37
Ternary Relationships
Ternary modelled as binary:
38 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 39
Mapping a Ternary Relationship
40 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 41
Mapping a Ternary Relationship
42 42
Week 5 Final Logical Model – conversion from Week 2 Conceptual Model
Monash Software Amalgamation
Week 4 Final Normalisation Result – EMPLOYEE Form 3NF
EMPLOYEE (emp_no, emp_fname, emp_lname, emp_dob, emp_street_no, emp_street, emp_town, emp_pcode)
EMP_PHONE (emp_no, phone_no, phone_type)
EMP_QUALIFICATION (emp_no, degree_name, degree_institution, degree_year)
FAMILY_MEMBER (emp_no, fmemb_no, fmemb_name, fmemb_dob)
EMPLOYEE_SKILL (emp_no, skill_name)
Full dependencies:
emp_no -> emp_fname, emp_lname, emp_dob, emp_street_no, emp_street, emp_town, emp_pcode
emp_no, phone_no -> phone_type
emp_no, degree_name, degree_institution -> degree_year emp_no, fmemb_no -> fmemb_name, fmemb_dob
43 43
44 44