DB Normalization Process ER Model Transformation

1
DB Normalization ProcessE-R Model Transformation

• Shaun Simpson
• MIS 372
• Database Management

2
E-R Model TransformationOverview

• E-R Diagram Overview
• Transformation Schemes
• Simple Transformation
• Complex Transformation
• MN Relationship
• SuperSub Type Relationship
• View Integration
• Concluding Remarks

3
E-R Diagram OverviewA Graphical Summary

• In transforming an E-R diagram, we will cover the
  following kinds of components

1. One-to-One 2. One-to-Many 3. Many-to-Many 4.
Super-Sub Type
Q. How do we trans- form them into proper
relations?
4
Transformation SchemesSome Tricks!!

• Step 1 Start from the side with a
  cardinality.
• Step 2 Focus on the entity at the many side (?)
  of a (1M) relationship
• Step 3 Focus on the gerund of a MN
  relationship
• Step 4 Focus on the Sub Entity of a Super-sub
  relationship!

5
Simple TransformationEntities at end of (11)
or (1M) relationship

• Basic Steps
• Start from the side

• Bring in all attached attributes into a relation
• Identify its key (either a simple or a
  composite!)

6
Simple TransformationExample of (11)
Relationship

• Example 1

7
Simple TransformationExample of (11)
Relationship

• Example 1

? Move the key of Strong entity into the
weak entity
8
Simple TransformationExample of (11)
Relationship

• Example 2

Note Since each LOAN will be declared
default only once in its life time, LID will be
sufficient to serve as the key.
9
Simple Transformation Example of (1M)
Relationship

• Basic Steps
• Start from the side and form the relation

10
Simple Transformation Example of (1M)
Relationship
An employee may be in charge of many projects,
but each project can be managed by ONLY one
employee.
EMPLOYEE(
) PROJECT (
)
11
Simple Transformation Example of (1M)
Relationship
An employee may be in charge of many projects,
but each project can be managed by ONLY one
employee.
EMPLOYEE(EID, Name, Phone, , Dept) PROJECT(ProjID
, Title, Amt, , DueDate, EID)
12
Simple Transformation Example of (1M)
Relationship
What if no key can be identified at the Many (?)
side?
Solution We may move the key from the side
to the Many(?) side to form a
composite key!
FACULTY(
) JOBHIST (
)
13
Simple Transformation Example of (1M)
Relationship
What if no key can be identified at the Many (?)
side?
Solution We may move the key from the side
to the Many(?) side to form a
composite key!
FACULTY(EID, Name, Phone, , Dept) JOBHIST (EID,
PromoDate, Title)
14
Complex Transformation Example of (MN)
Relationship

• Basic Steps
• Form the entities at both sides

• Move the key from the two Many (?) sides to form
  
• an Association relation

15
Complex Transformation Example of (MN)
Relationship
Assumptions - An employee may be in charge of
many projects, and each project may be
co-managed by gt 1 employee - there is no other
property associated with this (MN)
relationship!!
PROJ_MGRS(EID, ProjID)
16
Complex Transformation Example of (MN)
Relationship

• Assumptions
• Student may attend gt 1 school
• A School has at least one student

STUDENT (
) SCHOOL (
) STU_SCH (
)
17
Complex Transformation Example of (MN)
Relationship

• Assumptions
• Student may attend gt 1 school
• A School has at least one student

STUDENT ( SSN, Name,Phone, ZIP ) SCHOOL (SchID,
Name, Type, ZIP) STU_SCH (SID, Date, SSN, SchID)
18
Complex Transformation Example of (MN)
Relationship
A customer may order different products many times
ORDER(CID, ProdID, Time, Date, Units)
19
Complex Transformation Example of Super-Sub
Relationship

• Solution the key of the super type entity
  should be transferred down to the sub type!!

20
Complex Transformation Example of Super-Sub
Relationship

• A Property management firm handles different
  types of rental units

21
View Integration ProblemsAn Important Footnote

• After the normalization, we may come up with many
  normalized but fragmented relations - because
  each relation may be designed for different user
  groups.
• Hence, we need to consolidate relations which
  share the same primary key!
• But, there are problems ...

22
View Integration ProblemsSome Common Problems

• Synonym Problems - different terms (attribute
  names) mean the same thing
• Homonym Problems - same term may mean different
  things across different depts
• Not appropriate for Super-Sub Type Entities -
  Never integrate a super type with a sub type
• Might create Transitive FD - careless combination
  may lead to a lousy relation
• STU_MAJ(SID, FID) MAJ_ADV(FID, MAJ)