do it in microsoft access Project Questions 3.58 Consider the table: STAFF_MEETI

Question

do it in microsoft access Project Questions 3.58

Consider the table: STAFF_MEETING (EmployeeName, ProjectName, Date)

A--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.

B--State the functional dependencies in STAFF_MEETING. B Transform this table into one or more tables in BCNF. State the primary keys, candidate keys, foreign keys, and referential integrity constraints.

C-- Is your design in part B an improvement over the original table? What advantages and disadvantages does it have?

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. State the functional dependencies in STUDENT.

B--Transform this table into two or more tables such that each table is in BCNF and in 4NF.

C--State the primary keys, candidate keys, foreign keys, and referential integrity constraints.

Explanation / Answer

Q 3.58 STAFF_MEETING (EmployeeName, ProjectName, Date)

a) State the functional dependencies in STAFF_MEETING.

functional dependencies:

(ProjectName, Date) --> EmployeeName as there will be only one project meeting for that project

ProjectName --> EmployeeName as there will be only one employee who will be assign to that meeting for each project

b)

functional dependencies:

STAFF_MEETING:

(ProjectName, Date) --> EmployeeName and ProjectName --> EmployeeName

candidate key: (ProjectName, Date)

The relation is not in BCNF because every determinant is not the candidate key. so we have make this in BCNF for this we can move ProjectName-->Employee into another table.

STAFF_MEETING2 (ProjectName, Date) and STAFF_MEETING_EMP ( ProjectName,EmployeeName)

STAFF_MEETING2 :

Then STAFF_MEETING2 functional dependencies will be:

(ProjectName, Date) --> EmployeeName and (ProjectName, Date) --> Date

candidate key for STAFF_MEETING2: (ProjectName, Date)

now here every determinant is a candidate key therefore this relation is in BCNF

STAFF_MEETING_EMP:

function dependencies for STAFF_MEETING_EMP: ProjectName-->EmployeeName

candidate key for STAFF_MEETING_EMP: ProjectName

the relation is in now BCNF as every determinant is a candidate key and now all table are in BCNF

Referential Integrity Constraints are ProjectName in STAFF_MEETING_EMPLOYEE must exist in STAFF_MEETING_2.

Q3.59: STUDENT (StudentNumber, StudentName, Dorm, RoomType, DormCost, Club, ClubCost, Sibling, Nickname)

a) Multi-valued dependencies in STUDENT

StudentNumber -->Club

StudentNumber -->Sibling

StudentNumber -->Nickname

STUDENT Functional dependencies:

StudentNumber -->StudentName

StudentNumber --> Dorm

StudentNumber --> RoomType

RoomType --> DormCost

Club --> ClubCost

b) Transform this table into two or more tables such that each table is in BCNF and in 4NF.

To transform the steps are:

1. Remove multi value dependencies first

Move the multi value in different table

STUDENT2 (StudentNumber, StudentName, Dorm, RoomType, DormCost)

STUDENT_CLUB (StudentNumber,Club, ClubCost)

STUDENT_SIB (StudentNumber, Sibling)

STUDENT_NICK (StudentNumber, Nickname)

2. Check each of resulting table for BCNF

STUDENT2 (StudentNumber, StudentName, Dorm, RoomType, DormCost)

functional dependencies:

StudentNumber -->StudentName

StudentNumber -->Dorm

StudentNumber -->RoomType

RoomType-->DormCost

candidate key: StudentNumber

STUDENT2 is not in BCNF as RoomType is determinant to get the STUDENT2 in BCNF we will rearrange the STUDENT2 table as:

STUDENT3 (StudentNumber, StudentName, Dorm, RoomType)

DORM_RATE (RoomType, DormCost)

STUDENT3 (StudentNumber, StudentName, Dorm, RoomType)

functional dependencies : for STUDENT3

StudentNumber-->StudentName

StudentNumber-->Dorm

StudentNumber-->RoomType

Candidate key :StudentNumber

STUDENT3 is in BCNF as every determinant has a candidate key

DORM_RATE (RoomType, DormCost):

functional dependencies for DORM_RATE table:

RoomType-->DormCost

candidate ke: RoomType. Now DORM_RATE is also in BCNF

STUDENT _CLUB (StudentNumber,Club, ClubCost):

functional dependencies for STUDENT_CLUB (StudentNumber,Club, ClubCost):

(StudentNumber,Club) --> ClubCost

Club --> ClubCost

candidate key : StudentNumber,Club).

Now STUDENT_CLUB is not in BCNF as every determinant don’t have a candidate key.

Create another table for Club --> ClubCost

STUDENT_CLUB (StudentNumber,Club)

STUDENT_CLUBCOST (Club,ClubCost)

STUDENT_CLUB2 (StudentNumber,Club):

Functional dependencies for STUDENT_CLUB2 (StudentNumber,Club): none

Candidate key: (StudentNumber,Club)

Now STUDENT_CLUB2 (StudentNumber,Club)

STUDENT_CLUB2 (Club,ClubCost) is in BCNF and in 4NF as the field of multi-valued dependencies is the only field in the table.

STUDENT_CLUBCOST (Club,ClubCost):

functional dependencies for STUDENT_CLUBCOST (Club,ClubCost): Club --> ClubCost)

candidate key: Club and now STUDENT_CLUBCOST (Club,ClubCost) is in BCNF.

STUDENT_SIB (StudentNumber, Sibling):

functional dependencies for STUDENT_SIB (StudentNumber, Sibling): none

candidate key: (StudentNumber, Sibling) STUDENT_SIB (StudentNumber, Sibling) is in BCNF and in $NF as the field of multivalued dependencies is the only field in the table.

STUDENT_NICK (StudentNumber, Nickname):

functional dependencies for STUDENT_NICK (StudentNumber, Nickname): none

candidate key: (StudentNumber, Nickname) STUDENT_NICK (StudentNumber, Nickname)  is in BCNF and in 4NF as the field of multivalued dependencies is the only field in the table.

3. Identify all primary key and foreign key

Identify the primary key for each table:

STUDENT3 (StudentNumber, StudentName, Dorm, RoomType)

Primary key: StudentNumber

Foreign key: RoomType

DORM_RATE (RoomType, DormCost)

Primary key: RoomType

Foreign key: none

STUDENT_CLUB2 (StudentNumber,Club)

Primary key: StudentNumber and CLub

Foreign key: StudentNumber and Club

STUDENT_CLUBCOST (Club,ClubCost)

Primary key: Club

Foreign key: none

STUDENT_SIB (StudentNumber, Sibling)

Primary key: StudentNumber and Sibling

Foreign key: StudentNumber

STUDENT_NICK (StudentNumber, Nickname)

Primary key: StudentNumber and NickName

Foreign key: StudentNumber

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.