Assigned: 3 September 2015
Due: 17 September 2015
Answer each question below by filling in code at the point indicated. Do not modify any of the other cells.
You must run the cells in order. It looks like you can come back later after restarting and pick up where you left off but that is not the case. When restarting you always have to start at the top of the notebook and run each cell in turn.
# fill in the information below
Author = 'jpuccio'
Collaborators = ['Zen Yang']
# checker setup do NOT change this
import comp521
check, report = comp521.start('A1')
# setup, no need to change this
from Dee import *
from DeeDatabase import Database
from CourseEnrollmentDee import DeeDB
import sqlite3
# I'll open a DB in memory
conn = sqlite3.connect(":memory:")
cursor = conn.cursor()
Use SQL to create tables for the following database schema modeling a library.
STUDENT sid(INTEGER) sname(TEXT)
BOOK bid(INTEGER) bname(TEXT) author(TEXT)
BORROWS id(INTEGER) sid(INTEGER) bid(INTEGER) date(TEXT)
All primary keys are bold. In the BORROWS table, bid is a foreign key referencing BOOK and sid is a foreign key referencing STUDENT.
Make sure you use the exact names and data types for the tables and attributes in order to pass the automatic test.
# I'll clean up for you so you don't get tripped up by trying more than once. Leave this alone
cursor.execute('DROP TABLE IF EXISTS "STUDENT"')
cursor.execute('DROP TABLE IF EXISTS "BOOK"')
cursor.execute('DROP TABLE IF EXISTS "BORROWS"')
# insert your code to create the table here using cursor to create the tables.
cursor.execute("CREATE TABLE STUDENT (sid INT, sname TEXT, PRIMARY KEY(sid))")
cursor.execute("CREATE TABLE BOOK (bid INT, bname TEXT, author TEXT, PRIMARY KEY(bid))")
cursor.execute("CREATE TABLE BORROWS (\
id INT, \
sid INT, \
bid INT, \
date TEXT, \
FOREIGN KEY (sid) REFERENCES STUDENT(sid), \
FOREIGN KEY (bid) REFERENCES BOOK(bid) \
)")
# leave this testing code alone
c1 = cursor.execute("SELECT name from sqlite_master WHERE type='table' order by name asc").fetchall()
check('Q1a', c1, points=6)
Q1a correct
Insert the following data into the tables you created above.
| sid | sname |
|---|---|
| 1 | Alice |
| 2 | Bob |
| 3 | Cindy |
| bid | bname | author |
|---|---|---|
| 1 | Database Management Systems | Ramakrishnan |
| 2 | Lord of the Rings | Tolkien |
| id | sid | bid | date |
|---|---|---|---|
| 1 | 2 | 1 | 8/3/2015 |
| 2 | 3 | 2 | 9/1/2015 |
# write your code here
cursor.execute("INSERT INTO STUDENT (sid,sname) VALUES (1,'Alice')")
cursor.execute("INSERT INTO STUDENT (sid,sname) VALUES (2,'Bob')")
cursor.execute("INSERT INTO STUDENT (sid,sname) VALUES (3,'Cindy')")
cursor.execute("INSERT INTO BOOK (bid,bname,author) VALUES (1,'Database Management Systems','Ramakrishnan')")
cursor.execute("INSERT INTO BOOK (bid,bname,author) VALUES (2,'Lord of the Rings','Tolkien')")
cursor.execute("INSERT INTO BORROWS (id,sid,bid,date) VALUES (1,2,1,'8/3/2015')")
cursor.execute("INSERT INTO BORROWS (id,sid,bid,date) VALUES (2,3,2,'9/1/2015')")
# testing code
c2a = cursor.execute('select * from STUDENT').fetchall()
check('Q2a', c2a, points=3)
c2b = cursor.execute('select * from BOOK').fetchall()
check('Q2b', c2b, points=3)
c2c = cursor.execute('select * from BORROWS').fetchall()
check('Q2c', c2c, points=3)
Q2a correct Q2b correct Q2c correct
Delete the record with id equal 1 from the BORROWS table.
# write your code here
cursor.execute("DELETE FROM BORROWS WHERE id=1")
# don't touch this
c3 = cursor.execute('''select * from BORROWS''').fetchall()
check('Q3', c3, points=5)
Q3 correct
Consider the following schema for a database modeling courses at UNC.
DEPARTMENT (did, name)
COURSE (cid, did, name, num, creditHours)
STUDENT (sid, fname, lname, did)
ENROLLED_IN (eid, sid, cid)In the COURSE table, did is a foreign key referencing DEPARTMENT(did). In the ENROLLED_IN table, sid is a foreign key referencing STUDENT(sid), and cid is a foreign key referencing COURSE(cid). In STUDENT, did is a foreign key referencing DEPARTMENT(did).
Use Dee to write relational algebra queries for the following. In particular, you may find the following Dee operators most useful: Projection, Restriction (corresponds to select in relational algebra), Join, Intersection, Difference, Union, and Divide.
Your, already populated, Dee database is named DeeDB.
Find the names of all courses below the 500 level. You can get the Dee relation for COURSE with DeeDB.COURSE.
# write code here used DeeDB and Dee methods.
c4 = DeeDB.COURSE.where(lambda t: t.num<500).project(['name'])
# checking code
check('Q4', c4, points=5)
Q4 correct
Find the first and last names of all students enrolled in "Files and Databases".
# Assign your result to c5
getStudents = DeeDB.COURSE.where(lambda t: t.name=="Files and Databases").project(["cid"]) & DeeDB.ENROLLED_IN & DeeDB.STUDENT
c5 = getStudents.project(["fname","lname"])
# checking
check('Q5', c5, points=5)
Q5 correct
Find the first and last names of all students in the ANTH department.
# assign to c6
getStudents = DeeDB.DEPARTMENT.where(lambda t: t.name=="ANTH").project(["did"]) & DeeDB.STUDENT
c6 = getStudents.project(["fname","lname"])
# checking
check('Q6', c6, points=5)
Q6 correct
List the first and last names of all students enrolled in no courses.
# assign to c7
getStudentsInNoCourses = DeeDB.STUDENT.project(["sid"]) - DeeDB.ENROLLED_IN.project(["sid"])
c7 = (getStudentsInNoCourses & DeeDB.STUDENT).project(["fname","lname"])
# checking
check('Q7', c7, points=5)
Q7 correct
List the first and last names of all students enrolled in every COMP course.
# assign to c8
allCOMPCourseIDs = (DeeDB.DEPARTMENT.where(lambda t: t.name=="COMP").project(["did"]) & DeeDB.COURSE).project(["cid"])
allStudentsInAllCOMPCourses = (DIVIDE_SIMPLE(DeeDB.ENROLLED_IN.project(["sid","cid"]), allCOMPCourseIDs).project(["sid"]) & DeeDB.STUDENT)
c8 = allStudentsInAllCOMPCourses.project(["fname","lname"])
check('Q8', c8, points=15)
Q8 correct
Now use the sqlite3 database courseEnrollment.db, which has the same schema as above to write SQL queries for the following. I will open it for you below. You must execute this next cell before the others!
econn = sqlite3.connect('CourseEnrollment.db')
ecursor = econn.cursor()
# use ecursor to refer to this database below.
Find the names of all 4 credit hour courses.
# your code here
c9 = ecursor.execute("SELECT name FROM COURSE WHERE creditHours=4").fetchall()
check('Q9', c9, points=10)
Q9 correct
Find the first and last names of all students enrolled in COMP courses.
# your code here
#c10 = ecursor.execute("SELECT fname,lname FROM STUDENT S, ENROLLED_IN E WHERE (SELECT did FROM DEPARTMENT WHERE name='COMP')")
c10 = ecursor.execute("SELECT DISTINCT fname, lname FROM STUDENT WHERE sid IN (SELECT sid FROM ENROLLED_IN WHERE cid IN (SELECT cid FROM COURSE WHERE did = (SELECT did FROM DEPARTMENT WHERE name='COMP')))").fetchall()
check('Q10', c10, points=10)
Q10 correct
Find the first and last names of all students enrolled in courses of at least 500 level.
# your code here
c11 = ecursor.execute("SELECT DISTINCT fname,lname FROM STUDENT WHERE sid IN (SELECT sid FROM ENROLLED_IN WHERE cid IN (SELECT cid FROM COURSE WHERE num>=500))").fetchall()
check('Q11', c11, points=10)
Q11 correct
Find the first and last names of all students enrolled in 'Data Structures' and "Anthropology of the Body and the Subject'.
# your code here
c12 = ecursor.execute("SELECT DISTINCT fname,lname FROM STUDENT WHERE sid IN (SELECT sid FROM ENROLLED_IN WHERE cid = (SELECT cid FROM COURSE WHERE name='Data Structures') INTERSECT SELECT sid FROM ENROLLED_IN WHERE cid = (SELECT cid FROM COURSE WHERE name='Anthropology of the Body and the Subject'))").fetchall()
check('Q12', c12, points=15)
Q12 correct
Now get your report and submit your assignment.
report(Author, Collaborators)
Q10 Q11 Q12 Q1a Q2a Q2b Q2c Q3 Q4 Q5 Q6 Q7 Q8 Q9 Report for jpuccio Collaborators: ['Zen Yang'] 14 of 14 correct, 100 of 100 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.