COMP3311 Final Exam 22T3

The University of New South Wales COMP3311 Database Systems Final Exam 22T3

Database Systems

Question 8 (6 marks)

Consider the following University database schema:

Students(id, name, dob, address)
Courses(id, code, term, title, uoc)
Enrolments(student, course, mark)

And the following Python code fragment to manipulate it:

zID = sys.argv[1]
query = """
select distinct term
from   Enrolments e join Courses c on c.id = e.course
where  student = %s order by term
"""
query2 = """
select c.code, c.title, c.uoc, e.mark
from   Enrolments e join Courses c on c.id = e.course
where  term = %s and student = %s
"""
try:
    db = psycopg2.connect("dbname=unsw")
    cur = db.cursor()
    cur2 = db.cursor()
    tot1,tot2 = (0,0)
    cur.execute(query, [zID]);
    for term in cur.fetchall():
        print(f"Courses for term {term[0]}")
        cur2.execute(query2, [term, zID])
        for res in cur2.fetchall():
            code,title,uoc,mark = res
            if mark is not None:
                tot1 = tot1 + uoc
                tot2 = tot2 + mark*uoc
            print(f"{res[0]} {res[1]} {res[3]}")
    print(f"{tot2 / tot1}")

1. what is this code computing?

2. what error condition has not been considered?

3. if a student studies 3 courses per term for 10 terms, how many execute() operations occur

4. suggest a single query that could be used with a single for loop to produce the same result more efficiently

Instructions:

• Type your answers to this question into a file called q8.txt
• Submit via:   give cs3311 exam_q8 q8.txt