Introduction to Databases Relational Algebra and Calculus SQL, Queries, Constraints, Triggers
computer science
Description
Introduction to Databases
Relational Algebra and Calculus
SQL, Queries, Constraints, Triggers
Show the results of the following operations:
(a) ΠD(T2)
(b) T1 ✶T1.C=T2.A T2
(c) T1 ✶ T2
(d) T1 − T2 (Assume that T1 and T2 are union-compatible.)
(e) (T1 ✶ T2) ✶ (T2 ✶ T2)
Express each of the following queries in relational algebra (not in SQL):
(a) (1 pt.) Find the title and year of every pair of movies that have at least onestar in common.
(b) (1.25 pts.) Find the name of each star who never acted in more than one moviein any single year.
(c) (1.25 pts.) Find the name of each star who acted in every movie produced bythe “Paramount” studio between 2000 and 2017.
(a) Print the name and department of each professor who has taught a course notfrom his/her department at least once.
(b) Print the sid and name of each student who has earned a 3.5 or higher grade inat least two different courses.
(c) Print the name and age of the student(s) with the highest GPA in their exactage group (i.e., the name and age of the student(s) with the highest GPA amongall 15 year olds, the name and age of the student(s) with the highest GPA amongall 16 year olds, and so on), for ages less than or equal to 18.
(d) For each department that both (a) has the substring “Engineering” in its name(e.g., “Electrical Engineering”) and (b) has at least 2 students majoring in thedepartment, print the name of the department and the average GPA of thestudents who major in the department.
(e) Some courses are popular among students just because students enrolled in those courses usually get good grades. Print the department name, course number,and course enrollment of each course C such that the following two conditionshold: (1) course C’s enrollment is at least 3% larger than the average enrollment of the courses offered by C’s department, and (2) course C’s average grade isat least 3% larger than the average grade obtained by students in the coursesoffered by C’s department. Assume that the enrollment of a course is the sumof the enrollment of all its sections. You can completely ignore any course thathas no students enrolled in it.
