Writing Datalog (Practice)

Problem 1

Consider a database consisting of the same relations, where the primary key of each relation is bold:

Sailors(sid, sname, rating, age)
Boats(bid, bname, color)
Reserves(sid, bid, day)

(a). Find the Colours of Boats Reserved by Sailors Named Albert or Andrea. Be Sure to Specify Which of the Colours Apply to Albert and Which of the Colours Apply to Andrea

ColorsForName(N, C) :- Boats(B, _, C), Reserves(S, B, _), Sailors(S, N, _, _)

ColorsForAlbert(C) :- ColorsForName("Albert", C)
ColorsForAndrea(C) :- ColorsForName("Andrea", C)

(b). Find the Names and IDs of Sailors Who Have a Rating of 7 or Who Have Reserved Boat ID 103

Ans(N, I) :- Sailors(I, N, 7, _)

Ans(N, I) :- Sailors(I, N, _, _), Reserves(I, 103, _)

(c). Find the IDs of Sailors over the Age of 19 Who Have Reserved a Red Boat

Ans(I) :- Sailors(I, _, _, A), A > 19, Reserves(I, B, _), Boats(B, _, "red")

(d). Find the Names of Sailors Who Have Reserved at Least Two Different Boats with the Same Color

Ans(N) :- Sailors(I, N, _, _), Reserves(I, B1, _), Reserves(I, B2, _), B1 != B2, Boats(B1, _, C), Boats(B2, _, C)

(e). Find the name(s) of Sailors with the Lowest Rating

Bad(N) :- Sailors(_, N, R1, _), Sailors(_, _, R2, _), R1 > R2

Ans(N) :- Sailors(_, N, _, _), not Bad(N)

(f). Find the name and Rating of the Oldest sailor(s)

Bad(N) :- Sailors(_, N, _, A1), Sailors(_, _, _, A2), A1 < A2

Ans(N, R) :- Sailors(_, N, R, _), not Bad(N)

(g).Find The Names and Ages of Sailors Who Have Reserved All Red Boats

Bad(I) :- Sailors(I, _, _, _), Boats(B, _, _, "red"), not Reserves(I, B, _)

Ans(N, A) :- Sailors(_, N, _, A), not Bad(I)

Problem 2

Consider a database consisting a shortened/condensed version of our SuperRent relations, where the primary key of each relation is bolded:

Customers (cid, cname, email, age)
VehicleTypes (typeName, rate, numberOfSeats)
Reserves (cid, typeName, startDate, endDate)

(a). Find the Typename of Vehicle Types that Have Been Reserved Which only Has 5 Seats

Ans(T) :- Vehicletypes(T, _, 5), Reserves(_, T, _, _)

(b). Find the Age of Customers that Have Reserved Vehicle Types with the Rate above 50

Ans(A) :- Customers(C, _, _, A), Reserves(C, T, _, _), VehicleTypes(T, R, _), R > 50

(c). Find the Cname and Email of Customers that Have Never Reserved Any Vehicle Types

Ans(N, E) :- Customers(C, N, E, _), not Reserves(C, _, _, _)

(d). Find the Id of the Youngest Customer that Has Reserved the Vehicle Type "SUV"

NotYoungestSUVReserver(C) :- Customers(C, _, _, A1), Reserves(C, "SUV", _, _), Customers(C2, _, _, A2), Reserves(C2, "SUV", _, _), A1 > A2

YoungestSUVReserver(C) :- Customers(C, _, _, _), Reserves(C, "SUV", _, _), not NotYoungestSUVReserver(C)

(e). Find the Enddate of Reservations Made by Customer with the name "Dwight Schrute"

Ans(D) :- Cusomters(C, "Dwight Schrute", _, _), Reserves(C, _, _, D)

Problem 3

Consider the following relations

Course(cid, deptName, cName, year, majorRequired)
- The primary key of this relation is {cid, deptName} (e.g., 304, CPSC)
- cid is a course id, e.g., 110, 304;
- deptName is the name of the department, e.g., CPSC, MATH;
- cName is the name of the course, e.g., “database management”;
- year is an integer in the set {1, 2, 3, 4};
- majorRequired is a Boolean value which is either true or false.
Prereq(cid, deptName, prereqCid, prereqDeptName)
- The primary key for this relation is {cid, deptName, prereqCid, prereqDeptName}
- This is a relation that stores the prerequisite courses for the course (cid, deptName);
- Each prerequisite course is denoted by (prereqCid, prereqDeptName).
Offering(cid, deptName, calendarYear, term, enrollment, avgGrade)
- The primary key for this relation is {cid, deptName, calendarYear, term}
- This is a relation that stores all the offerings of a course (cid, deptName);
- calendarYear is the year the course was offered, e.g., 2017, 2018;
- term is an integer in the set {1, 2, 3}, i.e., there are only 3 terms per year;
- enrollment is the number of students enrolled in that course during that year and that term;
- avgGrade is the average grade of the class for that offering; it ranges from 0% to 100%.
Took(sid, cid, deptName, calendarYear, term, grade)
- The primary key of this relation is {sid, cid, deptName, calendarYear, term}
- This is a relation that records the grade of a student taking a particular course during a particular year and term;
- sid is the student id of the student;
- (cid, deptName, calendarYear, term) specifies a particular offering of a course;
- grade is the grade of the student who took that offering of the course; it ranges from 0% to 100%, without any null values. The passing grade of any course is 50%.

Write an SQL query to answer each of the following questions.