6-relationalmodel.htm; updated 2/15/01

LOGICAL DATABASE DESIGN
and the RELATIONAL MODEL

Introduction.


Review of Attributes of the Relational Data Model.

TABLE_NAME( PrimaryKeyField1, Field2, Field3, ... )

example:
EMPLOYEE( EmpID, EmpName, Department, DateHired )

Note that the "..." simply means there could be additional fields.
 


Transforming Extended Entity-Relationship (EER) Diagrams to Relational Tables.

Entity Types.


Transforming Entities.





CUSTOMER(CustomerNumber, CustomerName, AccountBalance,
    CustomerPhone)
 

Transforming Entities with Multivalued Attributes.

CustomerNumber CustomerName AccountBalance CustomerPhone
14112 Mike McClary 114.53 555-1212
555-3434
15267 Anita Ritter 453.78 555-2424
34589 Steve Young 543.30 555-6774
555-3443
CUSTOMER(CustomerNumber, CustomerName, AccountBalance)
 
CustomerNumber CustomerName AccountBalance
14112 Mike McClary 114.53
15267 Anita Ritter 453.78
34589 Steve Young 543.30

CUSTOMER_PHONE(CustomerNumber, CustomerPhone)
 
CustomerNumber CustomerPhone
14112 555-1212
14112 555-3434
15267 555-2424
34589 555-6774
34589 555-3443

 

Transforming Binary 1:N Relationships for Regular Entities.

CUSTOMER(CustomerNumber, CustomerName, AccountBalance)

CUSTOMER_PHONE(CustomerNumber, CustomerPhone)

CUSTOMER_ORDER(OrderNumber, OrderDate, OrderAmount,
    DeliveryDate, CustomerNumber)
 
OrderNumber OrderDate OrderAmount DeliveryDate CustomerNumber
54321 2/8/XX 550.96 2/15/XX 34589
76389 2/9/XX 110.43 2/16/XX 15267
76390 2/9/XX 675.75 2/14/XX 34589

Transforming Binary N:N Relationships. CUSTOMER_ORDER(OrderNumber, OrderDate, OrderAmount,
    DeliveryDate, CustomerNumber)

PRODUCT(ProductNumber, Description, DesiredPrice, QtyStocked)
 
ProductNumber Description DesiredPrice QtyStocked
15567 Table, Dining, Oak 500.00 4
15568 Chair, with Arms, Oak 125.00 25
15569 Chair, without Arms, Oak 110.00 77
62899 Lamp, Brass 175.00 10

ORDERLINE(OrderNumber, ProductNumber, QtyOrdered, NegotiatedPrice)
 
OrderNumber ProductNumber QtyOrdered NegotiatedPrice
76389 15567 1 450.00
76389 15568 2 125.00
76389 15569 4 100.00
76390 15567 1 490.00
76390 62899 2 175.00


Insufficient Primary Keys for Intersection Tables.

PATIENT(PatientID, PatientName, RoomLocation)

TREATMENT(TreatmentID, TreatmentDescription)

PATIENT_TREATMENT(PatientID, TreatmentID, DateTimeTreated, Result)
 

Transforming Binary 1:1 Relationships.

EMPLOYEE(SSN, EmpName, EmpJobTitle, DateHired, ParkingID)

PARKING_PLACE(ParkingID, CoveredOrNot, Category)
 
SSN EmpName EmpJobTitle DateHired ParkingID
444-44-4444 Tracey Conner Lecturer 7/15/XX 45
555-55-5555 Doug Bock Professor 8/15/XX NULL
222-22-2222 Mary Sumner Professor 1/4/XX 23
ParkingID CoveredOrNot Category
45 NO B
62 YES C
23 YES A

OR

EMPLOYEE(SSN, EmpName, EmpJobTitle, DateHired)

PARKING_PLACE(ParkingID, CoveredOrNot, Category, SSN)
 
SSN EmpName EmpJobTitle DateHired
444-44-4444 Tracey Conner Lecturer 7/15/XX
555-55-5555 Doug Bock Professor 8/15/XX
222-22-2222 Mary Sumner Professor 1/4/XX
ParkingID CoveredOrNot Category SSN
45 NO B 444-44-4444
62 YES C NULL
23 YES A 222-22-2222


Transforming 1:1 and 1:N Unary Relationships.

PERSON(PersonID, PersonName, MarriedToPersonID)
 
PersonID PersonName MarriedToPersonID
4 John Brown 6
3 Susan Smith 1
6 Mary Brown 4
1 Barry Smith 3
EMPLOYEE(EmployeeID, EmployeeName, BirthDate,
    ManagerEmployeeID)
 
EmployeeID EmployeeName BirthDate ManagerEmployeeID
42 Tom 01/01/XX NULL
67 Bill 05/05/XX 42
75 Alice 07/07/XX 67
45 Susan 05/07/XX 42

 
 Transforming Unary N:N Relationships.

ITEM(PartNumber, ItemName, ItemCost)
 
PartNumber
ItemName
ItemCost
42
Cylinder Head
$550
55
Engine
$2,500
18
Engine Block
$1,800
44
Exhaust Valve
$45
... ... ...

BILL_OF_MATERIALS(PartNumber, ComponentPartNumber,
    QtyToManufacture)
 
PartNumber
ComponentPartNumber
QtyToManufacture
55
42
1
55
18
1
42
44
4
.... .... ....

 
Transforming Ternary Relationships.

VENDOR(VendorNumber, VendorName, ...)

WAREHOUSE(WarehouseNumber, Location, ...)

ITEM(ItemNumber, Description, Cost, ... )

SHIPMENT(VendorNumber, WarehouseNumber, ItemNumber,
    DateTimeShipped, QtyShipped)
 
 
VendorNumber
VendorName
10
ABC Company
20
XYZ Company
....
....
 
 
WarehouseId
Location
100
Chicago
200
St. Louis
 
 
ItemNumber
Description
Cost
1000
Table
$1500
2000
Chair
$250
3000
Lamp
$175
 
 
ItemNumber
WarehouseNumber
VendorNumber
DateTimeShipped
QtyShipped
1000
100
10
01/01/XX
1
2000
200
20
01/01/XX
3
1000
100
10
01/02/XX
1
....
....
....
 
....

Transforming the Complex Shipment Relationship.
VENDOR(VendorNumber, VendorName, ...)

WAREHOUSE(WarehouseNumber, Location, ...)

ITEM(ItemNumber, Description, Cost, ... )

SHIPMENT(ShipmentNumber, WarehouseNumber, VendorNumber, DateShipped)

ITEMS_ON_SHIPMENT(ShipmentNumber, ItemNumber, QtyShipped)
 
 
VendorNumber
VendorName
10
ABC Company
20
XYZ Company
....
....
 
 
WarehouseId
Location
100
Chicago
200
St. Louis
...
...
 
 
ItemNumber
Description
Cost
1000
Table
$1500
2000
Chair
$250
3000
Lamp
$175
...
...
...
 
 
ShipmentNumber
WarehouseNumber
VendorNumber
DateTimeShipped
0001
100
10
01/01/XX
0002
200
20
01/01/XX
0003
100
10
01/02/XX
....
....
....
 
 
 
ShipmentNumber
ItemNumber
QtyShipped
0001
1000
1
0002
2000
3
0003
1000
1
...
...
...
 

Transforming Associative Relationships.

EMPLOYEE(EmployeeID, EmpName, ...)

COURSE(CourseID, Title, PurchasePrice, ...)

CERTIFICATE(CertNumber, DateCompleted, EmployeeID, CourseID)
 
EmployeeID EmpName
0001 Tom Jones
0002 Barry Manilow
0003 Sandra Bullock
 
 
CourseID Title PurchasePrice
CMIS450 Database Design $1500
CMIS464 Applied Operating Systems $1750
CMIS470 Structured Design $1300
 
 
CertNumber DateCompleted EmployeeID CourseID
14562 1/15/XX 0001 CMIS450
14563 1/15/XX 0002 CMIS470
14563 1/30/XX 0001 CMIS470

 

Transforming Generalization Hierarchy (Supertype-Subtype) to Relationships.

EMPLOYEE(EmployeeID, Name, DateHired, EmployeeSubType)
 
HOURLY(EmployeeID, Wage)
 
SALARIED(EmployeeID, StockOption, Salary) EMPLOYEE(EmployeeID, Name, DateHired, Wage,
   StockOption, Salary)



NORMALIZATION.

Introduction.

Functional Dependency.
TotalPrice = ItemPrice x Quantity
A -> B (read A functionally determines B) Rules of Functional Dependencies: Well-Structured Tables (Relations).
EmpId
Name
Dept
Salary
Course
DateTook
Fee
130
Margaret
Math
45,000
Calculus
01/15
150
130
Margaret
Math
45,000
Biology
02/15
200
200
Susan
Sci
38,000
Biology
01/15
200
250
Chris
Math
52,000
Calculus
03/15
150
250
Chris
Math
52,000
Biology
03/15
200
425
Bill
Math
48,000
Algebra
03/15
200
425
Bill
Math
48,000
Calculus
04/15
150

Problems With This Table:

What is the Primary Key of this table?
To determine an appropriate key, you should first examine the FDs.
EmpId -> Name, Dept, Salary
Course -> Fee
EmpId, Course -> DateTook Data Anomalies. First Normal Form (1NF). Remove Repeating Groups.
EmpId
Name
Dept
Salary
Course
DateTook
Fee
130
Margaret
Math
45,000
Calculus
01/15
150
       
Biology
02/15
200
200
Susan
Sci
38,000
Biology
01/15
200
250
Chris
Math
52,000
Calculus
03/15
150
       
Biology
03/15
200
425
Bill
Math
48,000
Algebra
03/15
200
       
Calculus
04/15
150
EMPLOYEE (EmpId, Name, Dept, Salary, Course1, DateTook1, Fee1, Course2, DateTook2, Fee2, ) EMPLOYEE (EmpId, Name, Dept, Salary, Course, DateTook, Fee)
 
EmpId
Name
Dept
Salary
Course
DateTook
Fee
130
Margaret
Math
45,000
Calculus
01/15
150
130
Margaret
Math
45,000
Biology
02/15
200
200
Susan
Sci
38,000
Biology
01/15
200
250
Chris
Math
52,000
Calculus
03/15
150
250
Chris
Math
52,000
Biology
03/15
200
425
Bill
Math
48,000
Algebra
03/15
200
425
Bill
Math
48,000
Calculus
04/15
150
Second Normal Form (2NF). Remove Partial Dependencies. EmpId -> Name, Dept, Salary
Course -> Fee
EmpId, Course -> DateTook


EMPLOYEE (EmpId, Name, Dept, Salary )
 
EmpId
Name
Dept
Salary
130
Margaret
Math
45,000
200
Susan
Sci
38,000
250
Chris
Math
52,000
425
Bill
Math
48,000

COURSE (Course, Fee )
 
Course
Fee
Calculus
150
Biology
200
Algebra
200

TOOK_COURSE ( EmpId, Course, DateTook )
 
EmpId
Course
DateTook
130
Calculus
01/15
130
Biology
02/15
200
Biology
01/15
250
Calculus
03/15
250
Biology
03/15
425
Algebra
03/15
425
Calculus
04/15

TOOK_COURSE ( EmpId, Course, DateTook )
 

Third Normal Form (3NF).  Remove Transitive Dependencies.

CustNo
Name
Salesperson
Region
8023
Anderson
Smith
South
9167
Bancroft
Hicks
West
7924
Hobbs
Smith
South
6837
Tucker
Hernandez
East
8596
Arnold
Hicks
West
CustNo -> Name, Salesperson, Region
Salesperson -> Region SALES ( CustNo, Name, Salesperson )
 
CustNo
Name
Salesperson
8023
Anderson
Smith
9167
Bancroft
Hicks
7924
Hobbs
Smith
6837
Tucker
Hernandez
8596
Arnold
Hicks

SPERSON (Salesperson, Region )
 
Salesperson
Region
Smith
South
Hicks
West
Hernandez
East

Boyce-Codd Normal Form (BCNF). Every Determinant Is A Candidate Key. Resolve Anomalies For More Than One Candidate Key.

Player
Position
Coach
Earl
Fullback
Joe
John
Guard
Ed
Tony
Fullback
Pete
Earl
Tackle
Jim
Mack
Fullback
Joe
Player, Position -> Coach
Player, Coach -> Position (by the augmentation rule).
Coach -> Position POSITION ( PLAYER, COACH )
 
Player
Coach
Earl
Joe
John
Ed
Tony
Pete
Earl
Jim
Mack
Joe

COACH (COACH, POSITION )
 
Coach
Position
Joe
FB
Ed
G
Pete
FB
Jim
T

Fourth Normal Form (4NF). Remove Multivalued Dependencies.

A ->> B (read A is associated with multiple values of B)
A ->> C (read A is associated with multiple values of C)
B <-/-> C (read B and C are independent of one another) Course ->> Instructor
Course ->> Textbook
Instructor <-/-> Textbook OFFERING (Course, Instructor, Textbook) -- Note the composite key -- this table structure is WRONG!!!!
 
Course
Instructor
Textbook
 
Mngt
White
Drucker
 
Mngt
Green
Drucker
 
Finance
Gray
Weston
 
Finance
Gray
Gilford
 
Mngt
White
Peters
(new row)
Mngt
Green
Peters
(new row)


INSTRUCTOR(Course, Instructor)
 
Course
Instructor
Mngt
White
Mngt
Green
Finance
Gray

TEXT(Course, Textbook)
Course
Textbook
Mngt
Drucker
Finance
Weston
Finance
Gilford
Mngt
Peters

Fifth Normal Form (5NF). Join Dependency.

END OF NOTES