Case Study 4 - Pesendorfer
docx
keyboard_arrow_up
School
University of Tulsa *
*We aren’t endorsed by this school
Course
7093
Subject
Information Systems
Date
Dec 6, 2023
Type
docx
Pages
2
Uploaded by PresidentExplorationOtter33
Case Study 4 – Pesendorfer
3.58 Consider the table:
STAFF_MEETING (EmployeeName, ProjectName, Date)
The rows of this table record the fact that an employee from a particular project attended a meeting on
a given date. Assume that a project meets at most once per day. Also, assume that only one employee
represents a given project but that employees can be assigned to multiple projects.
A.
State the functional dependencies in STAFF_MEETING.
ProjectName
EmployeeName
(ProjectName, Date)
EmployeeName
B.
Transform this table into one or more tables in BCNF. State the primary keys, candidate keys,
foreign keys, and referential integrity constraints.
PROJECT_DATE (ProjectName
, Date
)
STAFF_MEETING (
ProjectName
,
EmployeeName)
Constraint: ProjectName in STAFF_MEETING must exist in PROJECT_DATE
C.
Is your design in part B an improvement over the original table? What advantages and
disadvantages does it have?
Yes, it’s a better design.
Advantage: it avoids modification anomalies as all tables are in BCNF.
Disadvantage: the system requires staff meeting data to be entered in PROJECT_DATE before an
EmployeeName can be added to STAFF_MEETING, which might weird to some system users
3.59 Consider the table:
STUDENT (StudentNumber, StudentName, Dorm, RoomType, DormCost, Club, ClubCost, Sibling,
Nickname)
Assume that students pay different dorm costs depending on the type of room they have but that all
members of a club pay the same cost. Assume that students can have multiple nicknames.
A.
State any multivalued dependencies in STUDENT.
StudentNumber
Club
StudentNumber
Sibling
StudentNumber
Nickname
B.
State the functional dependencies in STUDENT.
StudentNumber
StudentName
StudentNumber
Dorm
StudentNumber
RoomType
Dorm, RoomType
DormCost
Club
ClubCost
C.
Transform this table into two or more tables such that each table is in BCNF and in 4NF. State the
primary keys, candidate keys, foreign keys, and referential integrity constraints.
STUDENT (StudentNumber
, StudentName, Dorm,
RoomType
)
Constraint: STUDENT.RoomType must exist in DORM_COST.RoomType
DORM_COST (RoomType
, DormCost)
STUDENT_CLUB (
StudentNumber
,
Club
)
Constraints: STUDENT_CLUB.StudentNumber must exist in STUDENT.StudentNumber
STUDENT_CLUB.Club must exist in CLUB_COST
CLUB_COST (Club
, ClubCost)
STUDENT_SIBLING (
StudentNumber
, Sibling
)
Constraint: STUDENT_SIBLING.StudentNumber must exist in STUDENT.StudentNumber
STUDENT_NICKNAME (
StudentNumber
, Nickname
)
Constraint: STUDENT_NICKNAME. StudentNumber must exist in
STUDENT.StudentNumber
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help