1. Consider the 4 relations, namely, student, takes, section, and course in the relational database schema above. 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 SEEM3550 between year 2019 and year 2023 (inclusive). List the names in alphabetical order (ascending order).

e) Find the students who enrolled in the course SEEM3550 offered by the SEEM department in the year 2023. 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.

2. Consider the following query: Find the IDs of the students who get at least one grade A in the courses that are offered in Spring 2023 or get at least one grade A in the courses that are offered in Fall 2023.

a) What are set operators (give any 2 set operators as your answer)? Answer the above query using set operations.

b) What are the common uses of correlation variables for renaming a relation? Answer the above query using joins and rename operation. You should not use nested query or derived relations.

c) What are the common uses of nested subqueries? Answer the above query using a nested query.

d)Answer the above query using derived relations.

3. Consider a query to find the IDs, names of the courses that are offered by the SEEM department in Spring 2023, and number of students in each course. Give a SQL using aggregate function.

4. Consider a query to find the names of the instructors who have the highest salary. One possible answer is as follows. Is it correct? Give your reason and give the correct SQL query if it isnt.

SELECT name FROM instructor ORDER BY name DESC

5. Answer the following questions using SQL statements.

a) Display the IDs and names of all instructors who have never taught a course.

b) Show the names of the students whose names include substring ch twice or end with en.

c) Find the IDs, names of all the students who take more than 30 credits in a semester.

d) Show IDs and names of the students if she/he does not take any course that is offered by the SEEM department.

e) For each department, show both the department budget and the remaining department budget after paying all its instructors salaries.

f) Show the ID of the students having taken the most courses offered by the SEEM department.