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 grades.
(i) Find the names of students who are enrolled in a single course.
(i) Find the grades of students who are enrolled in course(s) with the highest number of credits.
You must manage a database of recipes for your favorite food show.
Dishes (did:integer, dname:string, origin: string, popularity:integer)
Recipes (did:integer, iid:integer, quantity:integer)
Ingredients (iid:integer, iname:string, unitprice:integer)
There is a table of dishes, with a unique identifier, dish name, origin of the dish (e.g., 'Italy' or 'SouthEast Asia') and popularity (this is a numerical score calculated based on how many "likes" each dish obtains). There is also a table of ingredients: each ingredient has a unique identifier, a name, and price per unit (in dollars). Finally, the recipes table stores how much of each ingredient is needed for each dish (assume that the quantity given in recipes is of the same unit as the unit price is measured in for table ingredients).
Write relational algebra expressions for the following queries:
(a) Find the dish names that do NOT contain any of the following ingredients: sugar, butter, starch.
(b) Find the ingredient names that cost at least $10 per unit and that appear in at least one dish with popularity higher than 10,000.
(c) Find the origin of dishes that use at least one unit of an ingredient called 'saffron'.
(d) List the popularity of "exclusive" dishes, defined as dishes that contain only ingredients costing at least $50 per unit.
(e) Find the name and unit price of rare ingredients, i.e., those that appear in a single dish.