Database Systems Assignment 1

Introduction to Database Systems 
Overview: This assignment consists of ten questions. In each case, you are given a requirement in English and your task is to write a relational calculus query implementing the requirement. All queries are to be formulated over a relational database recording information about students, courses and enrollments. The conceptual view of the database is illustrated by the following relational database schema. Note that this includes an indication of primary and foreign key constraints in the manner discussed in class.
The schema stores information about both ongoing and past classes for a course. In particular, this means two things: (1) no marks are recorded for any enrollment of an ongoing class, and (2) for a past class, a mark is recorded for each of its enrollments. You may also assume that each class has at least one enrollment and that a department has at least one professor.
Assignment submission: You must submit a PDF file containing your answers to all questions to crowdmark by the assignment due date and time. Each answer must be given on a separate page.
1
In formulating your answers to any of the questions, you are free to use conditions of the form “x op y” or “x op c”, where “op” is a comparison operator (<, ≤, =, ≥, ), and where “c” is a constant. You are also free to write “R(...,−,...)” as shorthand for “∃x.R(...,x,...),” where x is a fresh variable not used anywhere else in the query.
1. The student number and name of students in their third year who have obtained a grade of at least 90 in at least two courses in a department with either the name “pure math” or the name “computer science”.
2. The number, name and year of students in at least their third year who have not taken either CS 241 or CS 246.
3. The names of departments, other than the department with code “CS”, who have a professor who has taught a CS 245 class in some term for which a student obtained a grade among the highest CS 348 grades of students in that term.
4. Rooms that have always been used to teach either CS courses or combinatorics and optimization (CO) courses.
5. The number, name, office and department name of each professor who is not currently teaching on Mondays before noon and is also not currently teaching on Fridays after noon.
6. For each past term and either a CO or CS course in the term, the minimum and maximum final grade obtained. (Be sure to include the term and course identification in each result.)
7. The department code and course number of any course that has always been taught by a CS professor in the past, but is currently being taught by a non-CS professor.
8. The number and name of any professor who has taught or is teaching on Friday, and, whenever this has been the case, has never had to also teach on Monday.
9. The number and name of fourth year students who have taken CS 348 and, whenever they did, obtained a higher grade in at most one other class taken during the same term.
2
10. The number and name of each professor who has taught the same course as some other professor in the same term, and, whenever this was the case, had at least two students who obtained grades in the course that were higher than any grades obtained by students who were taught by the other professor.
3