ITM500 W08 01 AdvancedModeling

Advanced Modeling

1

Objectives





Gain an understanding of more advanced modeling techniques
Understand primary key selection
Learn how to build a physical database from a logical model

2

Agenda





Unary or recursive relationships
Entity integrity: Keys
Interesting design cases
Converting logical model to a physical model 3

Last Class Review


JOINS




ANSI and traditional join syntax

DML
 insert
 update
 delete

4

Unary, Recursive,
Relations




A relation where source and target are a same entity
Can be 1:N or N:M




Manager - Employee
Social network relationships
Part-subpart

5

Primary Keys




The function of a primary key is to guarantee entity integrity.
Primary keys are used as foreign keys in other entities that need to refer to this entity.

6

Primary Key Properties







Unique values
Non-intelligent
No change over time
Minimum number of attributes
Security/Privacy compliant
Preferably numeric

7

Single Attribute vs.
Composite PKs






Strong entities should preferably have single attribute primary keys
Weak entities most often have composite primary keys: parent’s PK plus child’s identifying attribute
Associative entities usually have composite primary keys made of PKs of the two associated entities
8

Surrogate Keys



Best practice or workaround solution?
Automatically satisfy all the PK properties:







Unique values
Non-intelligent
No change over time
Minimum number of attributes
Security/Privacy compliant
Preferably numeric
9

Design Case 1:
Implementing 1:1
Relationships
Where to put the FK?


ER Relationship
Constraint

Resolution

One side is mandatory and the other optional

PK of the mandatory entity goes in the optional entity as a FK. The FK is mandatory. Both sides are optional PK from the entity that appears in more relationships goes into the “more optional” entity.
Both sides are mandatory Are the two entities not the same one?
Can you use the same attribute as a PK