Part 1

Part 1: You are asked to write SQL queries given a schema. We will not be providing you with a database for this question. It is advised that you think of some way to be able to check if your queries really work the way they should. Please submit this part in separate files specified in each of the problem.

Part 2: This part requires you to look at the database associated with a sql file called booktown.sql . This file should be attached along with this document on canvas. You will need your Oracle account to load and see the database. Please answer question 5 in the file named booktownqueries_problems.sql . Question 6 is to be answered in a pdf file called hw2q6.pdf. Please neatly write the relational algebra and relational calculus questions and scan them with a high quality scanner (either a good phone or a scanner found in the library). Alternatively, you can write them using an equation writing software (e.g. LaTeX).

Write some queries for the given relational schema. You are given the following relations (primary keys are underlined):

Student (sid, name, major) : student ID, name, and majors of students

Project (pid, ptitle) : project ID and title of projects

Course (cid, title) : course ID and title of courses

Member (pid, sid ) : Relationship. Student sid is a member of project pid Enrolled (sid, cid) : Relationship. Student sid is enrolled in course cid.

Please submit the answers to these questions in separate files as shown. The files should execute within sqlplus and should only answer the query (no need to create tables).

Question 1

Find the student IDs of all students who are enrolled in (EECS484 and EECS485) or enrolled in (EECS482 and EECS486) or enrolled in EECS281. For example, student A will be the output if student A is enrolled in (EECS484 and EECS485) or (EECS482 and EECS486) or (EECS281). Note that EECS482 is a course title.

Correct solutions that use views, nested queries will only receive 50% of the grade. (This query can be done without views, nested queries)

FIle: q1.sql

Question 2

Find the student IDs of all students who have a project partner who is enrolled in either EECS482 or EECS483 and either EECS484 or EECS485 and EECS280. For example, student A will be the output if students A and B are project partners and B is enrolled in (EECS482 or EECS483) and (EECS484 or EECS485) and (EECS280).

Correct solutions that use views, nested queries, or set operation like intersections, minus, or unions will receive only 50% credit. (This query can be done without views, nested queries, intersections, minus, or unions)

FIle: q2.sql

Question 3 :

We would like to know the CS majors who are taking non-heavy CS-based courses. We define non-heavy CS-based courses as courses where more than 100 non-CS majors are enrolled. Return all the student IDs (sid) and names of these CS students. The result of the query should be output in decreasing order by name.

You may use views or nested queries for this part.

File: q3.sql

Question 4 :

The school is interested in seeing the possible combinations of students who take a course together but are not project partners. Define a VIEW called StudentPairs that contains all student ID pairs (sid1, sid2) with the following property: The students who are enrolled in a common course but are not members of the same project. List each pair of students only once. For example, if you list (1, 2), do not list (2, 1). Student IDs can be assumed to be integers. You may use views or nested queries for this part. Correct solutions that use extra tables than needed will receive only 50% credit.

Hint: This one is actually quite subtle and tricky! Try not to lose potential student pairs when joining your tables!

FIle: q4.sql

Part 2

We have provided you a sample database in booktown.sql. Please look through the beginning of this file to understand the schema. Feel free to add more sample data to test your answers. However, dont change the schema! We will be testing your answers using the schema that we have given you.

To build the database in SQLPlus, log into CAEN and navigate to the directory where booktown.sql is located at. Run sqlplus as in Project 1 to connect to Oracle and load booktown.sql:

SQL> START booktown.sql

Question 5

• Please provide queries to the questions listed in sql . There should be 8 questions in total in this file.
• Your queries should work for any database with the schema provided in sql, not just with the same data we have given you.
• If you ever make intermediate view, make sure you drop them at the end of that question.

Clarifications:

For problem 3 in question 5, List titles, publication, authors id, authors last name, and authors first name of all books by authors who have published a book after 1999-10-01 but before 2001-10-01. Here who have published a book we mean at least one book rather than exactly one book.

IMPORTANT: Ensure that once you are done, the entire booktownqueries_problems.sql can run completely without errors by:

SQL> START booktownqueries_problems.sql

Question 6 :

Write Relational algebra expressions for your solutions to Q5.1, Q5.2, Q5.3 and Q5.4 (in booktownqueries_problems.sql). Write Relational calculus expressions for Q5.1, Q5.2 and Q5.3 (in booktownqueries_problems.sql).

These do not involve aggregation and should be doable with relational algebra.