• Week 07 Tuesday
• Rest of Term
• Assignment 2 Database
• Keep in mind for Assignment 2
• Psycopg2 recap
• Example: University Database
• Exercise: Course and Student Info
• Exercise: Transcripts on uni database
• Relational Design Theory

❖ Week 07 Tuesday

In today's lecture ...

• Assignment 2,   Python/psycopg2,   Relational Design Theory

Things to do ...

• Quiz 4 due by 23:59 Friday 31 March  (Week 7)
• Assignment 2 due by 23:59 Friday 14 April  (Week 9)
• Play with the  ass2  database and Python/psycopg2

❖ Rest of Term

Plan for remainder of term:

❖ Assignment 2 Database

Database about Pokemon ("pocket monsters")

• Pokemon have a type (or two) and many capabilities
• Pokemon can evolve and move, under certain conditions
• Pokemon can fight and breed, under certain conditions

The database holds general Pokemon properties

• does not hold state info for a specific game

Database schema info: assignments/ass2/schema.php

❖ Assignment 2 Database (cont)

How Dylan built the database

• became a Pokemon master ...
• wrote scripts to extract (some) raw data into schema form
• built database, via  createdb,  psql -f,  \copy

Database dump is ~23MB

Database under pgsql/data/base directory is ~180MB

Your quota on /localstorage is not infinite;  manage space carefully

❖ Assignment 2 Database (cont)

What you get (as templates) and what you submit:

• helpers.sql ... holds SQL views and PLpgSQL functions
• helpers.py ... holds Python functions for general use
• script₁ ... Python script for Q1
• script₂ ... Python script for Q2
• etc. etc. etc.

Put these under your VLab directory, not under /localstorage

Submit via give or Webcms3

❖ Keep in mind for Assignment 2

If 100's of people are finishing the assignment last minute ...

• we get bombarded with 100's of emails, forum posts, etc.

  (it takes much longer to get your question answered)

• vxdb2  has 100's of PG servers running simultaneously

  (it runs s-l-o-w; queries take much longer to run)

Beware:  /localstorage  is not backed up ... put only your DB there

❖ Psycopg2 recap

Psycopg2 is a library for Python ↔ PostgreSQL interaction.

Where psycopg2 fits in the PL/DB architecture

❖ Psycopg2 recap (cont)

Common psycopg2 operations:

• conn = psycopg2.connect(DB_connection_string)
• conn.close()
• conn.commit()
• cur = conn.cursor()
• cur.execute(SQL_statement, Values)
• cur.mogrify(SQL_statement, Values)
• list = cur.fetchall()
• tup = cur.fetchone()
• list = cur.fetchmany(nTuples)

❖ Psycopg2 recap (cont)

cur.execute(SQL_statement, Values)

• if supplied, insert values into the SQL statement
• then execute the modified SQL statement

Examples:

# run a fixed query
cur.execute("select * from R where x = 1");

# run a query with values inserted
cur.execute("select * from R where x = %s", (1,))
cur.execute("select * from R where x = %s", [1])

# run a query stored in a variable
query = "select * from Students where name ilike %s"
pattern = "%mith%"
cur.execute(query, [pattern])

❖ Psycopg2 recap (cont)

Typical database access pattern in Psycopg2

qry = 'select a,b,c from R where d = %s'
try:
   db = psycopg2.connect('dbname=mydb')
   cur = db.cursor()
   cur.execute(qry, [6])
   for tup in cur.fetchall():
      x,y,z = tup  # extract a,b,c values into x,y,z
      ... do something with x, y, z ...

except Exception as err:
  print("DB error: ", err)

finally:
  if db:
    db.close()

❖ Example: University Database

Consider the  uni  database ...

People(id, family, given, fullname, birthday, origin)

Students(id)

Subjects(id, code, name, uoc, offeredby, ...)
Courses(id, subject, term, homepage)
Streams(id, code, name, offeredby, stype, ...)
Programs(id, code, name, uoc, offeredby, ...)

Terms(id, year, ttype, code, name, starting, ending)

Course_enrolments(student, course, mark, grade)
Stream_enrolments(part_of_prog_enr, stream)
Program_enrolments(id, student, term, program, ...))

❖ Exercise: Course and Student Info

Write a Python script that, given a partial student name

• shows the students matching the name and all programs they were enrolled in

Usage examples:  ./stu 'smith',     ./stu 'nobody'

Write a Python script that, given a term code

• counts enrolments for every course in that term

Usage examples:  ./enrs 17s2,     ./enrs 19T1

❖ Exercise: Transcripts on uni database

Goal: print a transcript for a given student

• list of courses they took, ordered by term,subj
• UOC_earned, WAM = weighted_sum_marks / UOC_attempted

Method:

collect and display info about student
query to collect (subj,term,title,mark,grade,uoc)
foreach (subj,term,title,mark,grade,uoc) tuple
  display basic enrolment info (subj,term,title)
  depending on mark/grade,
    display mark and grade and UOC
    increment two UOCs, accumulate weighted sum
 print UOC_awarded, WAM

❖ Relational Design Theory

We know how to express ER data models and SQL schemas

But how do we know that our models/schemas are "good"?

Properties of "good" models/schemas

• accurate ... represent scenario faithfully
• complete ... represent all aspects of a scenario
• minimal ... minimse stored data; no repetition

Relational design theory addresses the last issue

Relies on the notion of functional dependency