[3] (25%) (2) (25%) Consider the following relation GO: GO(GroupId, GroupName, GroupEMail, ChairId, ChairMajor, MemberId, MemberMajor,...

Transcribed Image Text:

[3] (25%) (2) (25%) Consider the following relation GO: GO(GroupId, GroupName, GroupEMail, ChairId, ChairMajor, MemberId, MemberMajor, AdvisorFacId) The relation stores information about student groups, their chair persons, members and faculty advisors. Chair persons and members are students with unique student ids (stored as values in ChairId and MemberId respectively). GroupId uniquely identifies a group, and a group has a unique name, and an unique assigned email address. A group also has a faculty advisor with a faculty id of AdvsiorFacId. For example, three tuples are shown below. GroupId 1370 1370 1370 GroupName GroupEmail ChairId Data Science dsq@uhcl.edu 8182787 Data Science dsq@uhcl.edu 8182787 Data Science dsq@uhcl.edu 8182787 ChairMajor MemberId MemberMajor AdvisorFacId DASC 1090910 2213567 DASC 1234218 2213567 DASC 2132963 2213567 DASC CSCI CINF (a) List all applicable functional dependencies. (Make reasonable assumptions if necessary.) (b) What are the candidate keys? (c) What is the highest normal form? Why? (d) If the highest normal form is not BCNF, can you decompose the relation TD losslessly into component relations in BCNF while preserving functional dependencies? If yes, how. If no, why? [3] (25%) (2) (25%) Consider the following relation GO: GO(GroupId, GroupName, GroupEMail, ChairId, ChairMajor, MemberId, MemberMajor, AdvisorFacId) The relation stores information about student groups, their chair persons, members and faculty advisors. Chair persons and members are students with unique student ids (stored as values in ChairId and MemberId respectively). GroupId uniquely identifies a group, and a group has a unique name, and an unique assigned email address. A group also has a faculty advisor with a faculty id of AdvsiorFacId. For example, three tuples are shown below. GroupId 1370 1370 1370 GroupName GroupEmail ChairId Data Science dsq@uhcl.edu 8182787 Data Science dsq@uhcl.edu 8182787 Data Science dsq@uhcl.edu 8182787 ChairMajor MemberId MemberMajor AdvisorFacId DASC 1090910 2213567 DASC 1234218 2213567 DASC 2132963 2213567 DASC CSCI CINF (a) List all applicable functional dependencies. (Make reasonable assumptions if necessary.) (b) What are the candidate keys? (c) What is the highest normal form? Why? (d) If the highest normal form is not BCNF, can you decompose the relation TD losslessly into component relations in BCNF while preserving functional dependencies? If yes, how. If no, why?