Transcript chap04_solution

Chapter 4
Logical Database Design and
the Relational Model
Jason C. H. Chen, Ph.D.
Professor of MIS
School of Business Administration
Gonzaga University
Spokane, WA 99258
[email protected]
1
#1-I(a), p.193
#1-I(b)
2
Fig. 2-11: An associative entity (p. 78)
#1-I (c)
(a) Attribute on a relationship (Link Attribute/Associative)
#1-I (c)
EMPLOYEE
EmployeeiD
EmployeeName
BirthDate
CERTIFICATE
EmployeeID
CourseID
DateCompleted
COURSE
CourseID
CourseTitle
4
Answer to: #1I (c)
#1-III(c)
3NF
6
#2-III-a (p.193) – from Fig3-6b (p. 121)
7
#2-III-b (p.193)
from Fig. 3-7a
(p.122)
8
#2-III-c (p.193) – from Fig.3-9 (p.124)
9
#2-III-d
(p.193) –
from Fig.
3-10
(p.125)
10
3. The normal form for the relations are: (make sure
that you know why and how to transform it to
3NF)
a. 3NF
b. 3NF
c. 2NF
CLASS (CourseNo, SectionNo, Room)
ROOM (Room, Capacity)
d. 1NF
COURSE (CourseNo, CourseName)
CLASS (CourseNo, SectionNo, Room)
ROOM (Room, Capacity)
11
7. Transforming Table 4-3 to relations: (p194)
a) PART SUPPLIER
b)
c)
Part_No
Description
Vendor_Name
Address
Unit_Cost
1234
1234
5678
5678
5678
Logic Chip
Logic Chip
Memory Chip
Memory Chip
Memory Chip
Fast Chips
Smart Chips
Fast Chips
Quality Chips
Smart Chips
Cupertino
Phoenix
Cupertino
Austin
Phoenix
10.00
8.00
3.00
2.00
5.00
Part_No
Vendor_Name
Part_No, Vendor_Name
Description
Address
Unit_Cost
Insert anomaly: we cannot insert a new vendor unless we also
include a part number.
Delete anomaly: if we delete part information, we also lose
information about a vendor who supplies that part.
Modification anomaly: if a vendor address changes, we have to
modify all records (or rows) for that vendor.
12
7. d)
Part_No Description
Vendor_Name Address
Unit_Cost
e) 1NF
f)
PART SUPPLIER
Part_No
Description
Part_No
Vendor_Name
Vendor_Name
Unit_Cost
Address
13
7. g)
14
8. a)
partial dep.
partial dep.
transitive dep.
b) 1NF
15
8. c)
3NF after remove partial & transitive dep.
16
8. d)
3NF after remove partial & transitive dep.
17
8. e) Using MS/Visio
18