[SOLVED] CS ER SQL case study database MONASH

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 keys
5
5

All required attributes shown
Revisit Session 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 session 2
12 12

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

Mapping a 1:M Binary Relationship
16 16

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

Mapping a M:N Binary Relationship
ORDER PRODUCT
Order_ID Order_date
Product_ID Unit_price
20 20

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

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

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

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

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 PKs 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