Problem 1. (4 points) Consider the following relational database:

student ( student_number , name )

course ( course_number , year , semester , classroom )

registration ( ? ? ? )

(a) (1 points) Identify an appropriate primary key for each of the student and course relations. Assume that (i) each student is assigned a unique number, (ii) for each combination of year and semester (e.g., 2019 Spring), each course is assigned a unique course number, and (iii) a course with a certain course number (e.g., CSI 508) may have been offered multiple times (e.g., 2018 Fall and 2019 Spring). Explain why the primary key that you have chosen for the course relation is a candidate key.

(b) (10 points) Given your choice of primary keys for the student and course relations, identify appropriate attributes for the registration relation. This registration relation must store data regarding the courses that each student has taken. Assume that a student may have taken a course (e.g., CSI 410) multiple times (e.g., 2018 Spring and 2019 Fall). Also, identify an appropriate primary key for the registration relation.

(c) (10 points) Given your choice of primary keys, identify all of the foreign keys. For each foreign key, specify the referencing and referenced relations.

(d) (10 points) Describe a situation where an attempt to update the registration relation violates a foreign key constraint. Explain why your answer is correct.