Module 4: Advanced Modeling - Overview

1
Module 4 Advanced Modeling - Overview

• Attribute Placement
• Normalization Forms
• Resolving Many to Many Relationships
• Decomposing MM into Two 1M
• Recursive Relationships
• Modeling Roles
• Nested Subtypes
• Exclusive Relationships
• Complex Relations
• Time Relationships

2
Attribute Placement

• Normalization
• Dependency

3
Normalization Forms

• 0NF Zero Normal Form
• 1NF First Normal Form
• 2NF Second Normal Form
• 3NF Third Normal Form

4
0NF Zero Normal Form

• Definition A relation R is in zero normal
  form if and only if each tuple ( row) can be
  uniquely identified by the values of a set of
  attributes which constitute the primary key. C
  .J. Date
• Translation Find a unique identifier.
• Guidelines The primary key must
• Be unique.
• Not be repeated within a row.
• Not be null.

5
Example of 0NF Info. Sys. Development Project
Database (I.S.D.P. DB)
EMP Table in 0NF
6
1NF First Normal Form

• Definition
• A relation R is in the first normal form if
  and only if all underlying domains contain atomic
  values only. (C.J. Date)
• Translation
• Every attribute must have exactly one value for
  an occurrence of an entity (removing repeating
  groups).

7
From 0NF to 1NF I.S.D.P. DB (continued)
EMP in 0NF
EMP in 1NF
ASSIGNMENT in 1NF
8
Example of 1NF I.S.D.P. DB (continued)
EMP in 1NF
ASSIGNMENT in 1NF
9
ERD in 1NF for I.S.D.P. DB (continued)
EMP empnoempno ename dno dname
mgr pno start_date
PROJECT pno pname
10
2NF Second Normal Form

• Definition
• A relation R is in second normal form if and
  only if it is in first normal form and every
  non-key attribute is fully dependent on the
  primary key. C.J. Date
• Translation
• Every attribute must depend on the entire unique
  identifier of its entity for its value. (Removed
  partial dependencies.)

11
FROM 1NF TO 2NF I.S.D.P. DB (continued)
ASSIGNMENT in 1NF
ASSIGNMENT in 2NF
PROJECT in 2NF
12
Example of 2NF I.S.D.P. DB (continued)
EMP in 2NF
13
ERD in 2NF for I.S.D.P. DB (continued)
ASSIGNMENT a_id empno pno start_date
PROJECT pno pname
EMP empno ename dno dname mgr
14
3NF Third Normalization Form

• Definition
• A relation R is in third normal form if and
  only if it is in second normal form and every
  non-key attribute is non-transitively dependent
  on the primary key. C.J. Date
• Translation
• Every attribute may not be dependent on any other
  attribute for its value except the primary key of
  the entity. (Transitive dependencies must be
  removed.)

15
From 2NF to 3NF I.S.D.P. DB (continued)
EMP in 2NF
EMP in 3NF
DEPT in 3NF
16
ERD in 3NF for I.S.D.P. DB
PROJECT pno pname
ASSIGNMENT a_id empno pno startdate
EMPempno ename dno mgr
DEPT dno dname
Manager of
Managed by
17
More Examples on Normalization

• Wine Store Database
• Registras Database
• Bobs Lawn Equipment
• More examples
• To access the above examples, visit the following
  web site

www.lee918.com/class/mis_420/ms_access/
18
Resolving Many to Many (MM) Relationships
Example Where should the ordered quantity be
placed for a PART in a particular ORDER?
PART part_id part_description
ORDER order_id cust id
Enrolled in
contain
19
Decomposing MM into Two 1M
PART part_id part_description
ORDER order_id cust id
contains
used
uses
includes
LINE_ITEM (TRANSACTION) part_id order_id
quantity_ordered
20
Recursive Relationships, 1M
Example An employee may manage other employees
and be managed by other employees.
EMP empno ename mgr
Manager of
Managed by
21
Recursive Relationships, MM
PART part_ id part_name
Example Resolve the MM relationship with an
intersection entity.
A component of
Made up of
PART_LIST part_id quantity_ordered
for
for
Made up of
A component of
PART part_id
part_name
22
Modeling Roles

• Identifying Roles
• Modeling Roles as Entities
• Modeling Roles as Subtypes
• Communicating tool
• Creating value BPR
• Avoiding mistake Fulfilling the clients
  requirements

23
Identifying Roles
Course Assignment
for
for
Assigned to
Taken by
Student
Course
Instructor
Taught by
Assigned by
24
Modeling Roles as Entities
Model as a Relationship
Course Assignment
for
for
Assigned to
Taken by
Person
Course
The instructor of
Taught by
25
Modeling Roles as Subtypes
Employee id name
Hourly Workers hourly rate
Salaried Workers salary
Be eligible for
A member of
Assigned to
Consist of
Department
Benefits
26
Nested Subtypes
Musical Piece
Orchestral
Classical
Modern
Rock
Folk
New Age
R B
Country
Jazz
27
Exclusive Relationships
Order Line
for
Service
on
Product
for
on
on
Made up of
Sales Order
28
Complex Relations
Order Item quantity
Part number name
for
Found on
for
name
Consists of
Named on
Order id
Vendor id name
29
Example of Complex Relationships
A company wishes to place orders for parts from
various vendors. Several vendors provide the same
parts. Not all vendors, however, supply all
parts, and on a particular order, it may be
desirable to order a certain quantity of a part
from two vendors on one order. The part numbers
are consistent across the vendors.
Order id
Part number name
require
Included on
Supplied by
supply
Vendor id name
The problem with this model is that it assumes
that all parts are supplied by all vendors. This
may or may not be true. Even if it is true, the
model fails to capture the relationship between
an order and a particular part/vendor
relationship. What is really required is a
relationship between an order and the
relationship between part/vendor. The new model
is shown on the slide. A complex intersection
entity always has mandatory relationships back to
the entities that it relates.
30
Time Relationships
1
2
Weight weight date
weight
Member id name
height o weight
for
Consists of
Member id name
height
31
Time Relationships (continued)
Rental Contract start date
Renter number name
for
The renter of
for
on
Rented by
Associated with
Apartment id
Date every date
The Date entity is assumed and is, therefore,
unnecessary.
32
Review

• Attribute Placement
• Normalization Forms
• Resolving Many to Many Relationships
• Decomposing MM in to Two 1M
• Recursive Relationships
• Modeling Roles
• Nested Subtypes
• Exclusive Relationships
• Complex Relations
• Time Relationships