Consider the 4 relations, namely, student, takes, section, and course in the relational database schema. Answer the following questions while keeping the efficiency and answer correctness in mind.

a) Consider the foreign key constraint between relations student and takes, what is

the foreign key, referencing relation, referenced relation, respectively? What is the foreign key constraint? Give an example of insertion and deletion operation to relation student or takes which can cause a violation of the foreign key constraint.

b) For the students whose total credit is greater than 50, give a SQL using NATURAL JOIN to find their student IDs, the IDs of the courses they took as well as the grades they got in each taken course.

c) Give a SQL query for the problem in b) using Cartesian product of relations.

d) Give a SQL to find the names of the students who took the course with course_id CCSI1120 between year 2010 and year 2014 (inclusive). List the names in alphabetical

order (ascending order).

e) Find the students who enrolled in the course CCSI1120 offered by the CCSI

department in the year 2014. Return the student information (i.e., ID and name) and the course information (i.e., course id and course title).

e.1 Give a SQL to find the above information using 4 relations: student, takes, section, and course.

e.2 Give a SQL to find the above information using 3 relations: student, takes and course. Explain if SQLs in e.1) and e.2) give the same answer. Justify your answer.