Part-A: Translating an ER Schema to 4NF Relations Please translate the ER diagram into a relational...

Transcribed Image Text:

Part-A: Translating an ER Schema to 4NF Relations Please translate the ER diagram into a relational schema. Clearly name the relations. Translate all attributes including derived attributes. Underline the primary key and indicate foreign keys (format: as we did in the class lectures). Also show unique constraints. If a desired constraint cannot directly be enforced through your relational design, suggest how you could implement it at the relational level. Show a preferred and a valid alternative design for the (immediate) subclasses of EMPLOYEES; also describe the pros and cons in selecting each design option (preferred and alternative) including implications for relationships. -[1:1]- [1:1] date Analyze [O:M] recNo analysis recommendation phone HQlocation DISTRIBUTORS REGIONS [0:5] [0:3] distID name Licensed content ID duration regionID name RECOMMENDATION 1:11 Analysts look at member sessions and browsing history and make a recommendation (whether to offer a particular content item in a region or not). They store key points about their analysis under the "analysis" attribute expertise CONTENT ANALYSTS DEVELOPERS No two employees have the same SSN. The organization also issues separate email addresses to each employee. "m.i." stands for middle initial Iname name EMPLOYEES mame gender A country is located in one region. Each country has a unique country code and name countries cName cCode type language [0:M] rating tite CONTENT [1:1] watch duration -[0:M]- CONTENT INSTANCE start time time -10:M] [O:M] [O:M] genre episode a Can Serve Serves [1:1] MOVIES TV SHOW EPISODES CONTENT 10:M] DELIVERY POINT [1:1] Include sessionID duration [1:1] start MEMBER SESSIONS 0000 date address [O:M] YTD [1:M] end date Contains content copID served location YTD content served refers to the number of content items served Year to Date Watch premiere date season number 0000 SeasonID is unique across all shows, while season number (e.g., "1") may occur for multiple shows manager since seasonID #episodes MANAGERS [0:3] Is Manager years of experience Advise TECHNICAL STAFF YTD [0:20] anguages tickets spoken Is Rep Job title work em all SSN TECH SUPPORT REPRESENTATIVES [0:1] Review SEASONS [0:1] review REVIEW DETAILS The date refers to the date a service ticket was created [1:3]- Work on -[0:M] SERVICE TICKETS status date closed [1:1] 10:11 10:M] MEMBERS member D [O:M] MIS531 ABOR Refer [1:1] member since File Part-A: Translating an ER Schema to 4NF Relations Please translate the ER diagram into a relational schema. Clearly name the relations. Translate all attributes including derived attributes. Underline the primary key and indicate foreign keys (format: as we did in the class lectures). Also show unique constraints. If a desired constraint cannot directly be enforced through your relational design, suggest how you could implement it at the relational level. Show a preferred and a valid alternative design for the (immediate) subclasses of EMPLOYEES; also describe the pros and cons in selecting each design option (preferred and alternative) including implications for relationships. -[1:1]- [1:1] date Analyze [O:M] recNo analysis recommendation phone HQlocation DISTRIBUTORS REGIONS [0:5] [0:3] distID name Licensed content ID duration regionID name RECOMMENDATION 1:11 Analysts look at member sessions and browsing history and make a recommendation (whether to offer a particular content item in a region or not). They store key points about their analysis under the "analysis" attribute expertise CONTENT ANALYSTS DEVELOPERS No two employees have the same SSN. The organization also issues separate email addresses to each employee. "m.i." stands for middle initial Iname name EMPLOYEES mame gender A country is located in one region. Each country has a unique country code and name countries cName cCode type language [0:M] rating tite CONTENT [1:1] watch duration -[0:M]- CONTENT INSTANCE start time time -10:M] [O:M] [O:M] genre episode a Can Serve Serves [1:1] MOVIES TV SHOW EPISODES CONTENT 10:M] DELIVERY POINT [1:1] Include sessionID duration [1:1] start MEMBER SESSIONS 0000 date address [O:M] YTD [1:M] end date Contains content copID served location YTD content served refers to the number of content items served Year to Date Watch premiere date season number 0000 SeasonID is unique across all shows, while season number (e.g., "1") may occur for multiple shows manager since seasonID #episodes MANAGERS [0:3] Is Manager years of experience Advise TECHNICAL STAFF YTD [0:20] anguages tickets spoken Is Rep Job title work em all SSN TECH SUPPORT REPRESENTATIVES [0:1] Review SEASONS [0:1] review REVIEW DETAILS The date refers to the date a service ticket was created [1:3]- Work on -[0:M] SERVICE TICKETS status date closed [1:1] 10:11 10:M] MEMBERS member D [O:M] MIS531 ABOR Refer [1:1] member since File