initial commit

4 files changed, 809 insertions, 0 deletions

diff --git a/comp3311/1/ass1.sql b/comp3311/1/ass1.sql
new file mode 100644
index 0000000..9b223f8
--- /dev/null
+++ b/comp3311/1/ass1.sql
@@ -0,0 +1,311 @@
+-- COMP3311 21T3 Assignment 1
+--
+-- Fill in the gaps ("...") below with your code
+-- You can add any auxiliary views/function that you like
+-- The code in this file MUST load into a database in one pass
+-- It will be tested as follows:
+-- createdb test; psql test -f ass1.dump; psql test -f ass1.sql
+-- Make sure it can load without errorunder these conditions
+
+
+-- Q1: oldest brewery
+
+create or replace view Q1(brewery)
+as
+SELECT Breweries.name
+FROM Breweries
+WHERE Breweries.founded IN (
+    SELECT MIN(Breweries.founded) FROM Breweries)
+;
+
+-- Q2: collaboration beers
+
+create or replace view Q2(beer)
+as
+SELECT B.name
+FROM Beers B
+INNER JOIN (
+    SELECT Brewed_by.beer
+    FROM Brewed_by
+    GROUP BY Brewed_by.beer
+    HAVING COUNT(Brewed_by.beer) > 1) AS INNER_COUNT
+ON INNER_COUNT.beer = B.id
+;
+
+-- Q3: worst beer
+create or replace view Q3(worst)
+as
+SELECT Beers.name
+FROM Beers
+WHERE Beers.rating IN (
+    SELECT MIN(Beers.rating) FROM Beers)
+;
+
+-- Q4: too strong beer
+
+create or replace view Q4(beer,abv,style,max_abv)
+as
+SELECT Beers.name, Beers.abv, Styles.name, Styles.max_abv
+FROM Beers
+INNER JOIN Styles
+ON Styles.id = Beers.style
+WHERE Beers.abv > Styles.max_abv
+;
+
+-- Q5: most common style
+
+create or replace view Q5(style)
+as
+SELECT S.name
+FROM Styles S
+INNER JOIN (
+    SELECT Beers.style
+    FROM Beers
+    GROUP BY Beers.style
+    HAVING COUNT(Beers.style) IN (
+        SELECT MAX(COUNTER.COUNT)
+        FROM (
+            SELECT Beers.style, COUNT(Beers.style)
+            FROM Beers
+            GROUP BY Beers.style
+        ) AS COUNTER
+    )
+) AS INNER_COUNT
+ON INNER_COUNT.style = S.id;
+;
+-- Q6: duplicated style names
+
+create or replace view Q6(style1,style2)
+as
+SELECT s1.name, s2.name
+FROM Styles as s1, Styles as s2
+WHERE UPPER(s1.name) = UPPER(s2.name) AND s1.name < s2.name;
+;
+
+-- Q7: breweries that make no beers
+
+create or replace view Q7(brewery)
+as
+SELECT Breweries.name
+FROM Breweries
+EXCEPT
+SELECT Breweries.name
+FROM Breweries, Brewed_by
+WHERE Breweries.id = Brewed_by.brewery
+;
+
+-- Q8: city with the most breweries
+
+create or replace view Q8(city,country)
+as
+SELECT Locations.metro, Locations.country
+FROM Locations
+INNER JOIN (
+	SELECT COUNTER.located_in
+	FROM (
+		SELECT Breweries.located_in, COUNT(Breweries.located_in)
+		FROM Breweries
+		GROUP BY Breweries.located_in
+	) AS COUNTER
+	GROUP BY (COUNTER.located_in, COUNTER.count)
+	HAVING COUNTER.count IN (
+		SELECT MAX(COUNTER.COUNT)
+	       	FROM (
+		        SELECT Breweries.located_in, COUNT(Breweries.located_in)
+		        FROM Breweries
+		        GROUP BY Breweries.located_in
+		) AS COUNTER)
+) AS MAX_COUNT_LOCATIONS
+ON MAX_COUNT_LOCATIONS.located_in = Locations.id;
+;
+
+-- Q9: breweries that make more than 5 styles
+
+create or replace view Q9(brewery,nstyles)
+as
+SELECT SEARCH.name, COUNT(SEARCH.name)
+FROM (
+        SELECT Breweries.name, C.id
+        FROM Breweries
+        INNER JOIN (
+            SELECT Brewed_by.brewery, Brewed_by.beer
+            FROM Brewed_by
+            GROUP BY(Brewed_by.brewery, Brewed_by.beer)
+        ) AS A ON A.brewery = Breweries.id
+        INNER JOIN (
+            SELECT Beers.id, Beers.style
+            FROM Beers
+            GROUP BY (Beers.id, Beers.style)
+        ) AS B ON B.id = A.beer
+        INNER JOIN (
+            SELECT Styles.id
+            FROM Styles
+        ) AS C ON C.id = B.style
+        GROUP BY (Breweries.name, C.id)
+) AS SEARCH
+GROUP BY (SEARCH.name)
+HAVING COUNT(Search.name) > 5
+;
+
+-- Q10: beers of a certain style
+create type BeerInfo as (beer text, brewery text, style text, year YearValue, abv ABVvalue);
+create or replace function
+    q10(_style text) returns setof BeerInfo
+as $$
+DECLARE
+    ret RECORD;
+    rett RECORD;
+    inf BeerInfo%ROWTYPE;
+BEGIN
+    FOR ret IN
+    SELECT Beers.name AS beername,
+	Beers.id AS beerid,
+        Styles.name AS style,
+        Beers.brewed AS year,
+        Beers.abv AS abv
+    FROM Beers, Styles
+    WHERE Styles.name = _style
+        AND Beers.style = Styles.id
+        LOOP
+            inf.beer = ret.beername;
+            inf.style = ret.style;
+            inf.year = ret.year;
+            inf.abv = ret.abv;
+	    inf.brewery = '';
+
+	    FOR rett IN
+	    SELECT breweries.name as brewname
+	    FROM Brewed_by, Breweries
+	    WHERE Brewed_by.beer = ret.beerid
+		    AND Brewed_by.brewery = Breweries.id
+	    ORDER BY breweries.name
+            LOOP
+            IF (inf.brewery = '') THEN
+                inf.brewery = rett.brewname;
+            ELSE
+                inf.brewery = inf.brewery || ' + ' || rett.brewname;
+            END IF;
+            END LOOP;
+
+            return next inf;
+        END LOOP;
+    RETURN;
+END;
+$$
+language plpgsql;
+
+-- Q11: beers with names matching a pattern
+create or replace function Q11(partial_name text) returns setof text
+AS $$
+DECLARE
+    ret RECORD;
+    rett RECORD;
+    line text;
+BEGIN
+    FOR ret IN
+    SELECT Beers.name AS beername,
+        Beers.id AS beerid,
+        Beers.abv AS beerabv,
+        Styles.name AS stylename
+    FROM Beers, Styles
+    WHERE Styles.id = Beers.style
+	    AND (UPPER(Beers.name) LIKE '%' || UPPER(partial_name) || '%')
+    LOOP
+        line = '';
+
+	FOR rett IN
+        SELECT Breweries.name AS brewname
+        FROM Brewed_by, Breweries
+        WHERE Brewed_by.beer = ret.beerid
+		AND Breweries.id = Brewed_by.brewery
+        ORDER BY breweries.name
+        LOOP
+            IF (line = '') THEN
+                line = rett.brewname;
+            ELSE
+                line = line || ' + ' || rett.brewname;
+            END IF;
+        END LOOP;
+
+        line = '"' || ret.beername || '", ' || line;
+        line = line || ', ' || ret.stylename || ', ' || ret.beerabv || '% ABV';
+        RETURN NEXT line;
+
+    END LOOP;
+    RETURN;
+END;
+$$
+language plpgsql;
+
+-- Q12: breweries and the beers they make
+create or replace function Q12(partial_name text) returns setof text
+as $$
+DECLARE
+ret RECORD;
+rett RECORD;
+line text;
+BEGIN
+    FOR ret IN
+    SELECT Breweries.id AS brewid,
+        Breweries.name AS brewname,
+        Breweries.founded AS brewfounded,
+        Locations.country AS country,
+        Locations.region AS region,
+        Locations.metro AS metro,
+        Locations.town AS town
+
+    FROM Breweries, Locations
+    WHERE (UPPER(Breweries.name) LIKE '%' || UPPER(partial_name) || '%')
+        AND Locations.id = Breweries.located_in
+    ORDER BY Breweries.name
+    LOOP
+        -- First line
+        line = ret.brewname || ', founded ' || ret.brewfounded;
+        RETURN NEXT line;
+
+        -- Second line
+        line = 'located in ';
+        IF (ret.town != '' AND ret.metro != '') THEN
+            line = line || ret.town;
+        ELSEIF (ret.metro != ' ') THEN
+            line = line || ret.metro;
+        ELSEIF (ret.town != ' ') THEN
+            line = line || ret.town;
+        END IF;
+        line = line || ', ';
+
+        IF (ret.region != '') THEN
+            line = line || ret.region || ', ';
+        END IF;
+
+        line = line || ret.country;
+        RETURN NEXT line;
+
+        -- Third line
+        line = '';
+        FOR rett IN
+        SELECT Beers.name AS beername,
+            Beers.brewed AS beerbrewed,
+            Styles.name AS stylename,
+            Beers.abv AS beerabv
+        FROM Beers, Styles, Brewed_by
+        WHERE Brewed_by.brewery = ret.brewid
+            AND Brewed_by.beer = Beers.id
+            AND Styles.id = Beers.style
+        ORDER BY (Beers.brewed, Beers.name)
+        LOOP
+            line = '  "' || rett.beername || '"' || ', '
+                || rett.stylename || ', ' || rett.beerbrewed || ', '
+                || rett.beerabv || '% ABV';
+            RETURN NEXT line;
+        END LOOP;
+        IF (line = '') THEN
+            RETURN NEXT '  No known beers';
+        END IF;
+
+
+    END LOOP;
+    RETURN;
+END;
+$$
+language plpgsql;
diff --git a/comp3311/2/prog b/comp3311/2/prog
new file mode 100755
index 0000000..130b82b
--- /dev/null
+++ b/comp3311/2/prog
@@ -0,0 +1,328 @@
+#!/usr/bin/python3
+# COMP3311 21T3 Ass2 ... progression check for a given student
+
+import sys
+import psycopg2
+import re
+from helpers import *
+
+# define any local helper functions here
+
+### set up some globals
+
+def getMostRecentProgStrm(zid, db):
+  cur = db.cursor();
+  cur.execute("""
+    SELECT program, ST.code
+    FROM Students AS S,
+      Program_enrolments AS PE,
+      Stream_Enrolments AS SE,
+      Programs AS P,
+      Streams AS ST
+    WHERE S.id = %s
+      AND PE.student = S.id
+      AND SE.partof = PE.id
+      AND P.id = PE.program
+      AND ST.id = SE.stream
+    ORDER BY term DESC
+  """, [zid]);
+  info = cur.fetchone();
+  cur.close();
+  if not info:
+    return None
+  else:
+    return info
+
+def getRequirements(prog_code, strm_code, db):
+  cur = db.cursor();
+  cur.execute("""
+  SELECT R.name, R.min_req, R.max_req, R.type, AOG.definition, AOG.defby, 1 AS IS_PROG, R.id
+  FROM Streams AS S, Stream_Rules AS SR, Rules AS R, Academic_Object_groups AS AOG
+  WHERE S.code = %s
+      AND SR.stream = S.id
+      AND SR.rule = R.id
+      AND AOG.id = R.ao_group
+  UNION
+  SELECT R.name, R.min_req, R.max_req, R.type, AOG.definition, AOG.defby, 0 AS IS_PROG, R.id
+  FROM Program_Rules as PR, Rules as R, Academic_Object_Groups AS AOG
+  WHERE PR.program = %s
+      AND R.id = PR.rule
+      AND AOG.id = R.ao_group
+      AND AOG.type != 'stream'
+  ORDER BY IS_PROG DESC, id ASC
+  """, [strm_code, prog_code]);
+  info = cur.fetchall();
+  cur.close();
+  if not info:
+    return None
+  else:
+    return list(info)
+
+
+def maybeRemovePatternRequirement(requirement, course_code, UOC):
+  (name, min_req, max_req, type, definition, defby, stream, aogtype) = requirement
+
+  if type == "FE":
+    if requirement[1] != None:
+      requirement[1] = requirement[1] - UOC
+    if requirement[2] != None:
+      requirement[2] = requirement[2] - UOC
+    return True
+
+  for subject in definition.split(","):
+    match = True
+      
+    if len(subject) < 2:
+        continue
+
+    #print(f"testing {subject} against {course_code}")
+
+    for i in range(0, len(subject)):
+      if subject[i] != '#' and course_code[i] != subject[i]:
+        match = False
+        break
+
+    if match == True:
+      if requirement[1] != None:
+        requirement[1] = requirement[1] - UOC
+      if requirement[2] != None:
+        requirement[2] = requirement[2] - UOC
+
+      if not '#' in subject:
+        requirement[4] = requirement[4].replace(subject, "")
+      return True;
+
+  return False
+
+
+def maybeRemoveEnumRequirement(requirement, course_code):
+  removeStr = None;
+  (name, min_req, max_req, type, definition, defby, stream, aogtype) = requirement
+  for subject in definition.split(","):
+    subject = subject.replace("{", "")
+    subject = subject.replace("}", "")
+    for code in subject.split(";"):
+      if code == course_code:
+        removeStr = subject
+        break
+    if removeStr != None:
+      break
+
+  if removeStr == None:
+    return False 
+
+  requirement[4] = requirement[4].replace(removeStr, "")
+  return True
+
+
+def maybeRemoveInnerRequirement(requirement, course_code, UOC):
+  (name, min_req, max_req, type, definition, defby, stream, aogtype) = requirement
+  if defby == "pattern":
+    return maybeRemovePatternRequirement(requirement, course_code, UOC)
+  elif defby == "enumerated":
+    return maybeRemoveEnumRequirement(requirement, course_code)
+
+
+def shouldPopPattern(requirement):
+  (name, min_req, max_req, type, definition, defby, stream, aogtype) = requirement
+  if min_req != None and min_req <= 0:
+    return True
+  if max_req != None and max_req <= 0:
+    return True
+  return any(l.isalnum() for l in definition) == False
+
+
+def maybeRemoveRequirement(requirements, course_code, UOC):
+  removeIndex = None
+
+  if removeIndex == None:
+    for count, requirement in enumerate(requirements):
+      (name, min_req, max_req, type, definition, defby, stream, _) = requirement
+
+      if stream == 1: 
+        continue
+
+      if type == "FE":
+        continue # handled in next loop
+
+      #print(f"checking against {name} : { maybeRemoveInnerRequirement(requirement, course_code, UOC)}")
+      if maybeRemoveInnerRequirement(requirement, course_code, UOC):
+        removeIndex = count
+        break
+
+  if removeIndex == None:
+    for count, requirement in enumerate(requirements):
+      (name, min_req, max_req, type, definition, defby, stream, _) = requirement
+
+      if stream == 0:
+        continue
+      
+      if type == "FE":
+        continue # handled in next loop
+
+
+      if maybeRemoveInnerRequirement(requirement, course_code, UOC) == True:
+        removeIndex = count
+        break
+
+
+  # Special FE case, they should be prioritised last.
+  isFreeElective = False # For some reason we only return Free Electives here
+  if removeIndex == None:
+    for count, requirement in enumerate(requirements):
+      (name, min_req, max_req, type, definition, defby, stream, _) = requirement
+
+      if type != "FE":
+        continue # handled in next loop
+
+      if maybeRemoveInnerRequirement(requirement, course_code, UOC) == True:
+        removeIndex = count
+        isFreeElective = True
+        break
+
+  if removeIndex == None:
+    return None
+
+  # Remove if our min_req is zero for pattern, or if we have nothing else to 
+  # enumerate in requirements. 
+  (name, min_req, max_req, type, definition, defby, stream, _) = requirements[removeIndex];
+  if defby == "pattern":
+    if shouldPopPattern(requirement):
+      requirements.pop(removeIndex)
+  elif defby == "enumerated":
+    if any(l.isalnum() for l in definition) == False:
+      requirements.pop(removeIndex)
+
+  return name if isFreeElective == False else "Free Electives"
+
+
+def printAcademicRequirementsType(requirements, expected_type, db):
+    for requirement in requirements:
+      (name, min_req, max_req, type, definition, defby, stream, aogtype) = requirement
+      
+      if type != expected_type:
+        continue
+
+      def_count = str.count(definition, ",")
+      if type == "DS":
+        print(f"{min_req} stream(s) from {name}")
+        printStreams(definition, db)
+
+      elif type == "CC" or type == "PE" and min_req:
+        if defby == "pattern":
+          print(getCountString(def_count, min_req, max_req, name))
+          continue
+        printSubjects(definition, defby, db)
+
+      elif type == "GE" and min_req:
+        print(f"{min_req} UOC of {name}")
+
+      elif type == "FE" and min_req:
+        print(f"at least {min_req} UOC of Free Electives")
+
+      #else:
+      #  print(f"UNEXPECTED {name}, {min_req}, {max_req}, {type}, {defby}")
+
+
+def printAcademicRequirements(requirements, db):
+  printAcademicRequirementsType(requirements, "CC", db)
+  printAcademicRequirementsType(requirements, "PE", db)
+  printAcademicRequirementsType(requirements, "GE", db)
+  printAcademicRequirementsType(requirements, "FE", db)
+  
+
+usage = f"Usage: {sys.argv[0]} zID [Program Stream]"
+db = None
+
+### process command-line args
+
+argc = len(sys.argv)
+if argc < 2:
+  print(usage)
+  exit(1)
+zid = sys.argv[1]
+if zid[0] == 'z':
+  zid = zid[1:8]
+digits = re.compile("^\d{7}$")
+if not digits.match(zid):
+  print("Invalid student ID")
+  exit(1)
+
+progCode = None
+strmCode = None
+
+if argc == 4:
+  progCode = sys.argv[2]
+  strmCode = sys.argv[3]
+
+# manipulate database
+
+try:
+  db = psycopg2.connect("dbname=mymyunsw")
+  stuInfo = getStudent(db,zid)
+  if not stuInfo:
+    print(f"Invalid student id {zid}")
+    exit()
+
+  if progCode == None or strmCode == None:
+    (pc, sc) = getMostRecentProgStrm(zid, db)
+    progCode = str(pc)
+    strmCode = str(sc)
+
+  progInfo = getProgram(db,progCode)
+  if progCode:
+    if not progInfo:
+      print(f"Invalid program code {progCode}")
+      exit()
+
+  strmInfo = getStream(db,strmCode)
+  if strmCode:
+    if not strmInfo:
+      print(f"Invalid program code {strmCode}")
+      exit()
+
+  print(f"{stuInfo[0]} {stuInfo[1]}, {stuInfo[2]}")
+  print(f"  {progInfo[0]} {progInfo[1]}")
+  print(f"  {strmCode} {strmInfo[0]}");
+
+  # Our general strategy is threefold:
+  # 1. Generate a data structure containing all the requirements of the degree
+  # 2. Iterate and print all completed subjects, while removing requirements 
+  #    from (1) if fulfilled.
+  # 3. Print the remainder of (1), or ready to graduate if empty.
+  requirements = list(getRequirements(progCode, strmCode, db))
+  requirements = [list(a) for a in requirements]
+
+  print("\nCompleted:")
+
+  total_uoc = 0
+  for (CourseCode, Term, SubjectTitle, Mark, Grade, UOC) in getStudentCourses(db, zid):
+
+    remove_requirement_str = maybeRemoveRequirement(requirements, CourseCode, UOC) if doesGradeAddUOC(Grade) else None
+    mark_str = Mark if Mark != None else "-"
+
+    total_uoc += UOC if doesGradeAddUOC(Grade) and remove_requirement_str != None else 0
+
+    uoc_str = " " + getGradeString(Grade) if getGradeString(Grade) != "Xuoc" else f"{UOC:2d}uoc"
+    if remove_requirement_str != None:
+      uoc_str = uoc_str + f" towards {remove_requirement_str}"
+    elif doesGradeAddUOC(Grade) == False:
+      uoc_str = uoc_str + " does not count"
+    else:
+      uoc_str = " 0uoc does not satisfy any rule"
+
+    print(f"{CourseCode} {Term} {SubjectTitle :<32s}{mark_str :>3} {Grade :>2s}  {uoc_str}")
+
+  print(f"UOC = {total_uoc} so far")
+
+  if len(requirements) > 0:
+    print("\nRemaining to complete degree:")
+    printAcademicRequirements(requirements, db)
+  else:
+    print("Eligible to graduate")
+
+except Exception as err:
+  print("DB error: ", err)
+finally:
+  if db:
+    db.close()
diff --git a/comp3311/2/rules b/comp3311/2/rules
new file mode 100755
index 0000000..7f32a99
--- /dev/null
+++ b/comp3311/2/rules
@@ -0,0 +1,107 @@
+#!/usr/bin/python3
+# COMP3311 21T3 Ass2 ... print list of rules for a program or stream
+
+import sys
+import psycopg2
+import re
+from helpers import *
+
+# define any local helper functions here
+
+# Requires an array with with: name, min_req, max_req, type, definition, defby
+def printAcademicRequirements(rules, db):
+  print("Academic Requirements:")
+  for (name, min_req, max_req, type, definition, defby) in rules:
+    def_count = str.count(definition, ",")
+
+    # There is some weirdly specific behaviour to emulate when printing. While
+    # this might pass the tests, it is unlikely to continue doing so.
+    # According to our lecturer we won't be tested on edge cases, so I can only
+    # hope this is enough.
+    if type == "DS":
+      print(f"{min_req} stream(s) from {name}")
+      printStreams(definition, db)
+
+    elif type == "CC" or type == "PE":
+      print(getCountString(def_count, min_req, max_req, name))
+      printSubjects(definition, defby, db)
+
+    elif type == "GE":
+      print(f"{min_req} UOC of {name}")
+
+    elif type == "FE":
+      print(f"at least {min_req} UOC of Free Electives")
+
+    else:
+      print(f"UNEXPECTED {name}, {min_req}, {max_req}, {type}, {defby}")
+  
+
+### set up some globals
+
+usage = f"Usage: {sys.argv[0]} (ProgramCode|StreamCode)"
+db = None
+
+### process command-line args
+
+argc = len(sys.argv)
+if argc < 2:
+  print(usage)
+  exit(1)
+code = sys.argv[1]
+if len(code) == 4:
+  codeOf = "program"
+elif len(code) == 6:
+  codeOf = "stream"
+
+try:
+  db = psycopg2.connect("dbname=mymyunsw")
+  if codeOf == "program":
+    progInfo = getProgram(db,code)
+    if not progInfo:
+      print(f"Invalid program code {code}")
+      exit()
+
+    print(f"{progInfo[0]} {progInfo[1]}, {progInfo[2]} UOC, {progInfo[3] / 12.0} years")
+    print(f"- offered by {progInfo[4]}");
+
+    cur = db.cursor()
+    query = """
+      SELECT R.name, R.min_req, R.max_req, R.type, AOG.definition, AOG.defby
+      FROM Program_Rules as PR, Rules as R, Academic_Object_Groups AS AOG
+      WHERE PR.program = %s
+        AND R.id = PR.rule
+        AND AOG.id = R.ao_group
+    """
+    cur.execute(query, [code])
+    printAcademicRequirements(cur.fetchall(), db)
+    cur.close()
+
+
+  elif codeOf == "stream":
+    strmInfo = getStream(db,code)
+    if not strmInfo:
+      print(f"Invalid stream code {code}")
+      exit()
+
+    print(f"{code} {strmInfo[0]}");
+    print(f"- offered by {strmInfo[1]}")
+
+    cur = db.cursor()
+    query = """
+      SELECT R.name, R.min_req, R.max_req, R.type, AOG.definition, AOG.defby
+      FROM Streams AS S, Stream_Rules AS SR, Rules AS R, Academic_Object_groups AS AOG
+      WHERE S.code = %s
+        AND SR.stream = S.id
+        AND SR.rule = R.id
+        AND AOG.id = R.ao_group
+    """
+
+    cur.execute(query, [code])
+    printAcademicRequirements(cur.fetchall(), db)
+    cur.close();
+
+except Exception as err:
+  print(err)
+finally:
+  if db:
+    db.close()
diff --git a/comp3311/2/trans b/comp3311/2/trans
new file mode 100755
index 0000000..f530676
--- /dev/null
+++ b/comp3311/2/trans
@@ -0,0 +1,63 @@
+#!/usr/bin/python3
+# COMP3311 21T3 Ass2 ... print a transcript for a given student
+
+import sys
+import psycopg2
+import re
+from helpers import getStudent, getStudentCourses, doesGradeAddRule
+from helpers import doesGradeAddUOC, doesGradeAddWAM, getGradeString
+
+# define any local helper functions here
+
+### set up some globals
+
+usage = f"Usage: {sys.argv[0]} zID"
+db = None
+
+### process command-line args
+
+argc = len(sys.argv)
+if argc < 2:
+  print(usage)
+  exit(1)
+zid = sys.argv[1]
+if zid[0] == 'z':
+  zid = zid[1:8]
+digits = re.compile("^\d{7}$")
+if not digits.match(zid):
+  print(f"Invalid student ID {zid}")
+  exit(1)
+
+# manipulate database
+
+try:
+  db = psycopg2.connect("dbname=mymyunsw")
+  stuInfo = getStudent(db,zid)
+  if not stuInfo:
+    print(f"Invalid student ID {zid}")
+    exit()
+
+  student = getStudent(db, zid)
+  print(f"{zid} {student[1]}, {student[2]}")
+
+  total_uoc = 0;
+  total_wam_num = 0;
+  total_wam_den = 0;
+  for (CourseCode, Term, SubjectTitle, Mark, Grade, UOC) in getStudentCourses(db, zid):
+    total_uoc += UOC if doesGradeAddUOC(Grade) else 0
+    total_wam_num += UOC * (Mark if Mark != None else 0)
+    total_wam_den += UOC if doesGradeAddWAM(Grade) else 0
+
+    mark_str = Mark if Mark != None else "-"
+    uoc_str = " " + getGradeString(Grade) if getGradeString(Grade) != "Xuoc" else f"{UOC:2d}uoc"
+    print(f"{CourseCode} {Term} {SubjectTitle :<32s}{mark_str :>3} {Grade :>2s}  {uoc_str}")
+
+  # Avoid division by zero.
+  print(f"UOC = {total_uoc}, WAM = {total_wam_num / total_wam_den if total_wam_den != 0 else 0 :.1f}");
+
+except Exception as err:
+  print("DB error: ", err)
+finally:
+  if db:
+    db.close()
+