80 likes | 230 Views
Solution to Q1. Note: the mandatory side of the relationships are not identified. Notes to assist Q2.
E N D
Solution to Q1 Note: the mandatory side of the relationships are not identified
Notes to assist Q2 • Basically, three sets of relationships exist: A COURSE may generate one or more CLASSes, an INSTRUCTOR teaches up to two CLASSes, and a TRAINEE may enroll in up to two CLASSes. A trainee can take more than one class, and each class contains many (10 or more) trainees, so there is a M:N relationship between TRAINEE and CLASS. (We must, therefore, create a composite entity to serve as the bridge between TRAINEE and CLASS.) A class is taught by only one instructor, but an instructor can teach up to two classes. Therefore, there is a 1:M relationship between INSTRUCTOR and CLASS. • Finally, a COURSE may generate more than one CLASS, while each CLASS is based on one COURSE, so there is a 1:M relationship between COURSE and CLASS. These relationships are all reflected in the following E-R diagram. Note the optional and mandatory relationships: to exist, a CLASS must have TRAINEEs enrolled in it, but TRAINEEs do not necessarily take CLASSes. (Some may take "on the job training.") An INSTRUCTOR may not be teaching any CLASSes, doing research instead, but each CLASS must have an INSTRUCTOR. If not enough people sign up for a CLASS, a COURSE may not generate any CLASSes, but each CLASS must represent a COURSE.
Solution to Q2 Note: the mandatory side of the relationships are not identified
Solution to Q3 - step 1 Note: the mandatory side of the relationships are not identified
Solution to Q3 - step 2 Note: Kroenke uses the ‘rounded corners’ notation for a weak entity Note: the mandatory side of the relationships are not identified
Solution to Q4 (0,N) (0,N) agent 1 represents 1 manages (1,1) (1,1) M 1 M M 1 M plays client musician instrument (0,N) (0,N) (1,1) (0,N) (1,1) (0,N) 1 1 hosts performs (1,1) M M event (1,1)
Notes to assist Q5 • Note the following relationships: • The main components are TEAM and GAME. • Each team plays each other team at least twice. • To play a game, two teams are necessary: the home team and the visitor team. • Each team plays once as the home team and once as the visitor team. • Given these relationships, it becomes clear that TEAM participates in a recursive M:N relationship with GAME. The relationship between TEAM and GAME becomes clearer if we list some attributes for each of these entities: • GAME entity TEAM entity • GAME_NUM TEAM_NUM • GAME_DATE TEAM_NAME • GAME_HOME_TEAM TEAM_CITY • GAME_VISIT_TEAM • GAME_HOME_POINTS • GAME_VISIT_POINTS • Note: TEAM_NUM appears at least twice in a GAME:once as GAME_HOME_TEAM and once as GAME_VISIT_TEAM.
Solution to Q5 Note: Kroenke uses the ‘rounded corners’ notation for a weak entity Note: the mandatory side of the relationships are not identified Note: the mandatory side of the relationships are not identified