5. Consider the relational database schemas given below and the associated sets

Question

5. Consider the relational database schemas given below and the associated sets of functional dependencies and answer the questions for each. a. SailingClub(memID, memName, memAge, memGender, boatName, boatID, boatLength) memID-->memName, memAge, memGender boD>boatName, boatLength memID, boatID->memName,memAge,memGender,boatName,boatLength Is this table in 2d Normal Form? Explain why or why not. If it is not in 2NF, decompose SailingClub into 2NF tables. b. CompanyRoster(comEmail, comName, numEmplyees, totalRevenue, presidentName, presidentEmail, presidentPhone) comEa-comName, numEmployees, totalRevenue, presidentName,presidentEmail,presidentPhone presidentEmail-->presidentName, presidentPh presidentPhone->presidentName, presidentEmail Is this table in 2NF? Why or why not? Is this table in 3NF? why or why not. Do the decompositions to produce a set of 3NF tables. Are the tables also in Boyce-Codd Normal Form? Why or why not? one c. SurgicalTeam(sugeonID, nurselD, nurseStation, surgeryDate, patientName) surgeonID, nurselD, surgeryDate->nurseStation, patientName nurseStation-->nurseID Is this table 2NF? Why or why not? Is this table 3NF? Why or why not? Is this table BCNF? Why or why not Do the decompositions to produce a set of BCNF tables.

Explanation / Answer

a)

Here closure of memID,boatID is

{memID,boatID}+ = {memID,memName,memAge,memGender,boatName,boatID,boatLength}.

Here we got all the attributes of table for the closure of {memID,boatID}.

Hence the key for this table is {memID,boatID}.

Now if the table is in 2nd Normal Form then there will be no partial dependencies in it.

If determinent of a dependency is part of the key then that is partial dependency.

Here memID --> memName,memAge,memGender and

        boatID --> boatName,boatLength

are partial dependencies.

Hence this is not in 2nd Normal Form.

Decomposing SailingClub into 2NF tables :-
------------------------------------------

Here {memID}+ = {memID,memName,memAge,memGender}

     {boatID}+ = {boatID,boatName,boatLength}

Here 1st 2NF table is {memID,memName,memAge,memGender} with memID as key

Here 2nd 2NF table is {boatID,boatName,boatLength} with boatID as key

and 3rd NF table is {memID,boatID} with {memID,boatID} as key

b)

Here closure of comEmail is

{comEmail}+ = {comEmail,comName,numEmployees,totalRevenue,presidentName,presidentEmail,presidentPhone}

Here we got all attributes Hence comEmail is the key of the table.

Here there are no partial dependencies in the given dependencies.

Hence this is in 2nd Normal Form.

If a table is in 3rd Normal Form then there should be no transitive dependencies.

Here presidentEmail --> presidentName,presidentPhone

     presidentPhone --> presidentName,presidentEmail

are transitive dependencies.

Hence the table is not in 3rd Normal Form.

Decomposing CompanyRoster into 3NF tables :-
-----------------------------------------

Here {presidentEmail}+ = {presidentEmail,presidentName,presidentPhone}

     {presidentPhone}+ = {presidentPhone,presidentName,presidentEmail}

Hence 3NF tables are {presidentEmail,presidentName,presidentPhone} with presidentEmail as key

                     {presidentPhone,presidentName,presidentEmail} with presidentPhone as key
                   
                     {comEmail,comName,numEmployees,totalRevenue} with comEmail as key

Now if we observe here all the determinants comEmail,presidentEmail, presidentPhone

all are keys hence the relation is in BCNF.
                    

c)

  

Here {surgeonID,nurseID,surgeryDate}+ = {surgeonID,nurseID,surgeryDate,nurseStation,patientName}

Here we got all the attributes and hence the key is {surgeonID,nurseID,surgeryDate}

Here the table is in 2nd Normal Form as there are no partial functional dependencies.

Here nurseStation --> nurseID is transitive dependency

Decomposing SurgicalTeam into 3NF tables :-
--------------------------------------------

    {nurseStation}+ = {nurseStation,nurseID}

    Hence 3NF tables are {nurseStation,nurseID} with nurseStation as key

    {surgeonID,surgeryDate,nurseStation,patientName} with {surgeonID,nurseID,surgeryDate} as key

    Now if we observe here all the determinants {surgeonID,nurseID,surgeryDate} and nurseStation

     all are keys hence the relation is in BCNF.

   

Related Questions

Navigate

• Browse (All)
• Browse 5
• Subjects

---

---

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