Question 2 (20 points) The following (very simplistic) ER diagram models football teams and players. The Substitutes relation models a player being substituted by another player on the team. A. (12 points) Translate the ER diagram above to the relational model by specifying the resulting relations, their attributes, keys (indicated by underlining), and foreign keys. You may modify the attribute names in foreign keys as appropriate, and should specify when the foreign key must exist using NOT NULL. Use the abbreviated syntax from lectures rather than SQL DDL. Once you have identified 2 CIS 550 Fall 2021 the primary key(s), you do not need to specify a NOT NULL constraint for these keys, as we assume all keys must be non-null. However, you will need to specify this constraint for any other attributes you believe cannot be null. For example, for the ER diagram below, you would write: Employee(ssn, name, title, home-dept) home-dept FOREIGN KEY REFERENCES Department(id) Department(id, name, manager) manager FOREIGN KEY REFERENCES Employee(ssn) manager NOT NULL Note: Assume that the captain of the team must play for the team, and that a player can only substitute for a player on the same team. Simplify your relations based on these stated constraints, that are not captured in the ER diagram. B. (8 points) State whether or not (Yes or No) each of the following scenarios is possible under the given ER diagram (not your relational translation, which may capture more constraints). Briefly justify your answer in each case. As an example, for the sample ER diagram of employees and departments shown above, if you were given the scenario "The department 'Accounting' has no manager." your answer would be "No, the "1..1" label on the Dept side of the Mgr relationship requires each department to have a manger." 1. The player named "James" (number 47 on the Chicago Bears) is a substitute (replacement) for players named "Nick" and "Ben" (numbers 25 and 66, resp., on the Chicago Bears). 2. The player named "Nick" with number 009 plays on team named "Philadelphia Eagles". The player named "Nick" with number 010 plays on team named "Philadelphia Eagles". The player named "Nick" with number 010 plays on team named "Pittsburgh Steelers". 3. The player named "Nick" with number 009 plays on team named "Philadelphia Eagles". The player named "Jack" with number 009 plays on team named "Philadelphia Eagles". 4. The player named "James" (number 47 on the Chicago Bears) is the captain of the team "Philadelphia Eagles