MONASH
INFORMATION TECHNOLOGY
Database Design II: Logical Modelling
Reference
Several of the examples and diagrams used this session 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 (session 2) – Database Model independent
▪ Step 2 Logical Model (this session)
– 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 Session 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 key pairing
5
5
HiFlying Drone Conceptual Model
6
6
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).
7
7
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. Mapping composite to simple component attributes is a general requirement, may be overridden only if the client requirements are such that it should not occur.
8
8
Mapping a Composite Attribute
9
9
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.
10 10
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 session 2
11 11
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.
12 12
Conceptual Logical
13 13
Mapping a 1:M Binary Relationship
14 14
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.
15 15
Conceptual Logical
16 16
Mapping a M:N Binary Relationship
?
17 17
18 18
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.
19 19
Mapping an associative entity with an Identifier
20 20
Mapping a 1:1 Binary Relationship
?
21 21
Relationship Participation Mandatory vs Optional
NURSE participation in this relationship? CARECENTRE participation in this relationship?
22 22
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
23 23
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.
24 24
Mapping a 1:M Unary Relationship
25 25
Mapping a M:N Unary Relationship
26 26
SQL Developer Data Modeler
27 27
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
28 28
Logical Logical – with Surrogate key
29 29
Ternary Relationships
Ternary modelled as binary:
30 30
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?
31 31
Mapping a Ternary Relationship
32 32
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.
33 33
Mapping a Ternary Relationship
34 34
Consolidation of Normalisation and Logical Model
3NF:
TRAINING (train_code, train_desc, train_active_mnths) DRONE_TYPE (dt_code, dt_model, dt_manuf, train_code) TRAINER (trainer_id, trainer_rego, trainer_fname, trainer_lname, trainer_category)
TRAINING_COURSE (train_code, traincourse_date, trainer_id) CUSTOMER (cust_id, cust_fname, cust_lname) CUST_TRAINING (train_code, traincourse_date, cust_id, ct_exam_date, ct_date_expire)
What effect will this have on the logical model?
35 35
36 36
lookup table
check constraint
37 37
38 38