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.
Onyen = 'jpuccio' # not your PID, your onyen
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.
Pledge = 'Joe Puccio' # type your full name between the quotes
# setup
import sqlite3
import comp521
conn = sqlite3.connect('mt1.db')
cursor = conn.cursor()
check, report = comp521.start('MT1')
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 # Assign a letter to the answer variable below
Q1 = 'D'
# Checking code, it will only tell you if the domain of the answer is incorrect
check('Q1', Q1, points=5, choice='ABCD')
Q1 answered
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.# Assign a letter to the answer variable below
Q2 = 'A'
# Checking code, it will only tell you if the domain of the answer is incorrect
check('Q2', Q2, points=5, choice='ABCD')
Q2 answered
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.# Assign a letter to the answer variable below
Q3 = 'C'
# Checking code, it will only tell you if the domain of the answer is incorrect
check('Q3', Q3, points=5, choice='ABCD')
Q3 answered
4. Which SQL query clause corresponds to the relational algebra $\sigma$ operator.
A. ORDER BY
B. GROUP BY
C. SELECT
D. WHERE# Assign a letter to the answer variable below
Q4 = 'C'
# Checking code, it will only tell you if the domain of the answer is incorrect
check('Q4', Q4, points=5, choice='ABCD')
Q4 answered
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.
# expecting an integer
numberOfBoats = cursor.execute("SELECT COUNT(*) FROM Boats").fetchone()
Q5 = numberOfBoats[0]
print 'Q5 =', Q5
# Checking code
check('Q5', Q5, points=5)
Q5 = 11 Q5 incorrect
6. What is the cardinality of the cross product of the Boats relation with the Reserves relation?
# expecting an integer
numberOfRows = cursor.execute("SELECT COUNT(*) FROM Reserves INNER JOIN Boats").fetchone()
Q6 = numberOfRows[0]
print 'Q6 =', Q6
# Checking code
check('Q6', Q6, points=10)
Q6 = 1026 Q6 correct
7. How many times have red boats been reserved?
# expecting an integer
numberOfRedBoatReservations = cursor.execute("SELECT COUNT(*) FROM Reserves INNER JOIN Boats ON Boats.id = Reserves.bid WHERE Boats.color='red'").fetchone()
Q7 = numberOfRedBoatReservations[0]
print 'Q7 =', Q7
# check code
check('Q7', Q7, points=10)
Q7 = 19 Q7 correct
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).
# expecting a list of strings
namesOfSailors = cursor.execute("SELECT DISTINCT Sailors.name FROM Reserves INNER JOIN Boats ON Boats.id = Reserves.bid INNER JOIN Sailors ON Sailors.id = Reserves.sid WHERE Boats.name='Clipper'").fetchall()
namesOfSailorsList = [element[0] for element in namesOfSailors]
Q8 = namesOfSailorsList
print 'Q8 =', Q8
# test code
check('Q8', Q8, points=10, sort=True)
Q8 = [u'Andy', u'Dustin', u'Zorba', u'Brutus', u'Homer', u'Frodo', u'Rusty', u'Bart', u'Marge', u'Lisa'] Q8 correct
9. Which boats have been reserved by sailors with ratings higher than 5?
The expected result is a list of boat ids.
# expecting a list of integers
boatIDs = cursor.execute("SELECT DISTINCT bid FROM Reserves INNER JOIN Sailors ON Sailors.id = Reserves.sid WHERE rating > 5").fetchall()
boatIDsList = [row[0] for row in boatIDs]
Q9 = boatIDsList
print 'Q9 =', Q9
#check code
check('Q9', Q9, points=10, sort=True)
Q9 = [71, 91, 66, 52, 55, 36, 46, 87, 28, 11, 88, 22, 41] Q9 correct
10. Which sailors have never reserved a red boat?
# expecting a list of strings
sailorsWhoHateRedBoats = cursor.execute("SELECT DISTINCT Sailors.name FROM Sailors WHERE Sailors.id NOT IN (SELECT sid FROM Reserves INNER JOIN Boats ON Boats.id = Reserves.bid WHERE Boats.color='red')").fetchall()
sailorsWhoHateRedBoatsList = [row[0] for row in sailorsWhoHateRedBoats]
Q10 = sailorsWhoHateRedBoatsList
print 'Q10 =', Q10
#check code
check('Q10', Q10, points=15, sort=True)
Q10 = [u'Andy', u'Frodo', u'Marge', u'Bob', u'Art'] Q10 correct
11. Which sailors reserved a boat in May or June of 2014?
# expecting a list of strings
sailorsWhoLikeSummer = cursor.execute("SELECT DISTINCT Sailors.name FROM Reserves INNER JOIN Sailors ON Sailors.id = Reserves.sid WHERE day LIKE '2014-05-%' OR day LIKE '2014-06-%'").fetchall()
sailorsWhoLikeSummerList = [row[0] for row in sailorsWhoLikeSummer]
Q11 = sailorsWhoLikeSummerList
print 'Q11 =', Q11
#check
check('Q11', Q11, points=15, sort=True)
Q11 = [u'Homer', u'Bart', u'Brutus'] Q11 correct
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.# Assign a letter to the answer variable below
Q12 = 'B'
#Technically it means they can only make one reservation per day,
#because the boat id could probably be null :)
# Checking code, it will only tell you if the domain of the answer is incorrect
check('Q12', Q12, points=5, choice='ABCD')
Q12 answered
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.
report(Onyen, Pledge)
Q1 answered Q2 answered Q3 answered Q4 answered Q12 answered Report for jpuccio Pledged on my honor: Joe Puccio 5 of 5 answered for up to 25 points 7 of 7 correct, 75 of 75 points
Click the button below to submit your assignment. Watch for a confirmation message that your notebook was successfully uploaded. You may submit as often as you wish, only the last submission will count.