Comp 521 Fall 2015 Midterm 1

Don't Panic!

You may use only your book (print or pdf), your notes, homework, anything else you have downloaded to your computer and the Jupyter notebook.

You may not access the web sites, instant messaging, phones, or spirit guides. You will pledge on your honor that you have neither given nor received unauthorized help.

You must submit your exam before you leave the room.

Enter your onyen below.

Honor Pledge

I certify that no unauthorized assistance has been received or given in the completion of this work.

Enter your full name as the value of the Pledge variable below.

1. Which of the following SQL operations, used in the normal ways we have discussed, will change the schema?

A. delete   
B. insert   
C. select
D. none of these    

2. Which of the following is characteristic of a database schema?

A. It determines a relation’s degree
B. It determines a relation’s cardinality
C. It must specify a primary key
D. None of these.

3. Why is the join operator not one of the five primitive (basic) operators of relational algebra?

A. it has too many variants to be considered a primitive
B. it is equivalent to cross product
C. joins can be accomplished by composing primitive operations
D. it wasn't in the original papers.

4. Which SQL query clause corresponds to the relational algebra $\sigma$ operator.

A. ORDER BY
B. GROUP BY
C. SELECT
D. WHERE

Database info

In the setup code below I will connect to a small database that is included in the zip file for this exam and will create a cursor for your use. There is no need for you to create a new connection object or cursor.

The schema of the data base is a little different from boat club database we used in class. Here is its schema.

create table Sailors 
    (id integer primary key, 
     name text, 
     rating integer, 
     age integer)
create table Boats
    (id integer primary key, 
     name text, 
     color text)
create table Reserves 
    (sid integer, 
     bid integer, 
     day text,  # format yyyy-mm-dd i.e. 2015-09-24 is 24 Sept 2015
     primary key (sid, day) 
     foreign key (sid) references Sailors(id)
     foreign key (bid) references Boats(id))

5. How many boats are in the Boats table?

Remember you must compute your answer for any database with this schema, not for this specific instance. Use the cursor variable provided to execute the appropriate SQL statement and retrieve its result. For each question I tell you the type of result that is expected. You may have to use Python code to transform the result you get back from cursor.execute to get it into the expected form.

6. What is the cardinality of the cross product of the Boats relation with the Reserves relation?

7. How many times have red boats been reserved?

8. What are the names of the sailors who have reserved a boat named Clipper?

Produce a list of their names; you may assume that no two sailors have the same name (this makes it easier).

9. Which boats have been reserved by sailors with ratings higher than 5?

The expected result is a list of boat ids.

10. Which sailors have never reserved a red boat?

11. Which sailors reserved a boat in May or June of 2014?

12. What restriction on the Reserves relation is created by the schema phrase primary key (sid, day)?

A. A sailor may only make one reservation.
B. A sailor may only reserve one boat per day.
C. Each boat may only be reserved once per day.
D. The reserves relation will be ordered by sid and day.

Done!

Now, you should save, restart the kernel (Kernel menu -> Restart), run all the cells (Cell menu -> Run all), check your report, save, and submit your notebook.