CS430/630 – Homework 1
Released Feb 02, Due Feb 15
50 points (5/100 of final grade)
Instructions: The homework is due BEFORE CLASS on Thu Feb 15. Please hand in paper copies (either
typeset or hand-written copies are fine, as long as the hand writing is clear).
Question 1 (25 points)
Consider a database schema with three relations:
Students (sid:integer, sname:string, age:integer)
Enrolled (sid:integer, cid:integer, grade:integer)
Courses(cid:integer, cname:string, credits:integer)
The keys are underlined in each relation. Students are identified uniquely by sid, and courses by cid.
Students enroll to take courses, and for each course they obtain a grade which is an integer. sname is
the student name (string), age represents the student age and is an integer. cname is the course name
(string), and credits is the number of credits for a particular course (integer).
Write relational algebra expressions for the following queries:
(a) Find the names of students who got grade 10 in some course.
(b) Find the ages of students who take some course with 3 credits.
(c) Find the names of students who take a course named ‘Calculus’.
(d) Find the names of students who obtained grade at least 8 in some course that has less than 4
credits.
(e) Find the names of students who obtained only grades of 10 (implies that they took at least one
course).
(f) Find the names of students who took a course with three credits or who obtained grade 10 in
some course.
(g) Find the ages of students who attend ‘Calculus’ but never took any 4-credit course (assume there
is a course ‘Calculus’ with 3 credits).
(h) Find the names of students who have the lowest age.
(i) [630 only] Find the names of students who are enrolled in a single course.
(j) [630 only] Find the grades of students who are enrolled in course(s) with the highest number of
credits.
Question 2 (25 points)
Consider a database schema with three relations:
Movies (movie_id:integer, title:string, year:integer, studio:string)
Actors (actor_id:integer, name:string, nationality:string)
StarsIn(actor_id:integer, movie_id:integer, character:string)
The keys are underlined in each relation. Relation Movies stores information such as unique movie
identifier, title, year and producing studio. Actors contains unique actor identifier, actor name and
nationality. Relation StarsIn tracks which actor starred in which movie, and the name of the character
interpreted in that movie. Assume that one actor plays at most one character in the same movie.
Write relational algebra expressions for the following queries:
(a) Find the titles of movies produced by ‘Universal’ studio
(b) Find the names of actors that played a character named ‘Forrest Gump’ in some movie
(c) Find the names of actors of nationality ‘German’
(d) Find the nationality of actors who played a character named ‘Forrest Gump’ or who starred in a
movie in year 1980
(e) Find the names of actors that star in exactly one movie
(f) [630 students only] Find the names of actors who starred in some movie in or after year 1980, but
who did not star in any role (ever) for a movie produced by “Universal” studio