Blog

2022/5/3 17:24 COMP3311 Sample Solutions https://www.cse.unsw.edu.au/~cs3311/22T1/exams/sample/soln.html 1/10 COMP3311 22T1 COMP3311 22T1 Exam Sample Sample Solutions Database Systems These solutions are simply suggestions. In most cases many alternatives exist which would be equally correct and also worth full marks. Note that the order of tuples does not matter one bit in the SQL questions. The test scripts set the order themselves. Q1 — COMP3311 22T1 Exam Sample — Q1: view of teams and #matches create or replace view Q1(team, nmatches) as select t.country, count(*) from Teams t join Involves m on (m.team=t.id) group by t.country Q2 — COMP3311 22T1 Exam Sample — Q2: view of players scoring several amazing goals create or replace view Q2(player,ngoals) as select p.name as player, count(g.id) as goals from Players p join Goals g on (g.scoredBy = p.id) where g.rating=’amazing’ group by p.name having count(g.id) > 1 ; Q3 — COMP3311 22T1 Exam Sample — Q3: team(s) with most players who have never scored a goal create or replace view PlayersAndGoals (player,team,ngoals) as select p.name, t.country, count(g.id) from Teams t join Players p on (p.memberof = t.id) left outer join Goals g on (p.id = g.scoredby) group by p.name, t.country ; create or replace view CountryAndGoalless(team,nplayers) as select team, count(*) as players from PlayersAndGoals where ngoals = 0 group by team ; create or replace view Q3(team,nplayers) as select team, players 2022/5/3 17:24 COMP3311 Sample Solutions https://www.cse.unsw.edu.au/~cs3311/22T1/exams/sample/soln.html 2/10 from CountryAndGoalless where players = (select max(players) from CountryAndGoalless) ; Q4 — COMP3311 22T1 Exam Sample — Q4: function that takes two team names and — returns #matches they’ve played against each other create or replace function MatchesFor(text) returns setof integer as $$ select m.id from Matches m join Involves i on (m.id = i.match) join Teams t on (i.team = t.id) where t.country = $1 $$ language sql; create or replace function Q4(_team1 text, _team2 text) returns integer as $$ declare nmatches integer; begin perform * from Teams where country = _team1; if (not found) then return NULL; end if; perform * from Teams where country = _team2; if (not found) then return NULL; end if; select count(*) into nmatches from ((select * from MatchesFor(_team1)) intersect (select * from MatchesFor(_team2)) ) as X; return nmatches; end; $$ language plpgsql; Q5 — COMP3311 22T1 Exam Sample — Q5: show “cards” awarded against a given team — should have parameterised these views via an SQL function create or replace view RedCardsFor(team,ncards) as select t.country, count(c.id) from Players p join Teams t on (p.memberof = t.id) join Cards c on (c.givento = p.id) where c.cardtype=’red’ group by t.country ; create or replace view RedCards(team,ncards) as select t.country, coalesce(c.ncards,0) from Teams t left outer join RedCardsFor c on (t.country=c.team) ; 2022/5/3 17:24 COMP3311 Sample Solutions https://www.cse.unsw.edu.au/~cs3311/22T1/exams/sample/soln.html 3/10 create or replace view YellowCardsFor(team,ncards) as select t.country, count(c.id) from Players p join Teams t on (p.memberof = t.id) join Cards c on (c.givento = p.id) where c.cardtype=’yellow’ group by t.country ; create or replace view YellowCards(team,ncards) as select t.country, coalesce(c.ncards,0) from Teams t left outer join YellowCardsFor c on (t.country=c.team) ; drop function if exists q5(text); drop type if exists RedYellow; create type RedYellow as (nreds integer, nyellows integer); create or replace function Q5(_team text) returns RedYellow as $$ declare reds integer; yellows integer; result RedYellow; begin select r.ncards, y.ncards into reds, yellows from RedCards r join YellowCards y on (r.team = y.team) where r.team = _team; if (not found) then result.nreds := NULL; result.nyellows := NULL; else result.nreds := reds; result.nyellows := yellows; end if; return result; end; $$ language plpgsql ; Q6 — q6.sql drop view if exists Q6; drop view if exists MatchScores; drop view if exists TeamScores; drop view if exists TeamsInMatches; drop view if exists GoalsByTeamInMatch; create view GoalsByTeamInMatch as select g.scoredIn as match, p.memberOf as team, count(*) as goals from Goals g join Players p on (p.id = g.scoredBy) group by g.scoredIn, p.memberOf; ; 2022/5/3 17:24 COMP3311 Sample Solutions https://www.cse.unsw.edu.au/~cs3311/22T1/exams/sample/soln.html 4/10 create view TeamsInMatches as select i.match as match, i.team as team, t.country as country from Involves i join Teams t on (i.team = t.id) ; create view TeamScores as select tim.match, tim.country, coalesce(gtm.goals, 0) as goals from TeamsInMatches tim left join GoalsByTeamInMatch gtm on (tim.team = gtm.team and tim.match = gtm.match) ; create view MatchScores as select t1.match, t1.country as team1, t1.goals as goals1, t2.country as team2, t2.goals as goals2 from TeamScores t1 join TeamScores t2 on (t1.match = t2.match and t1.country < t2.country) ; create view Q6 as select m.city as location, m.playedOn as date, ms.team1, ms.goals1, ms.team2, ms.goals2 from Matches m join MatchScores ms on (m.id = ms.match) ; #!/usr/bin/python3 # COMP3311 22T1 Exam Sample # Q6: print match reports for a specified team in a given year import sys import psycopg2 def getResult(g1,g2): if g1 > g2: result = “won” elif g1 < g2: result = "lost" else: result = "drew" return result db = None cur = None if len(sys.argv) < 3: print(f"Usage: {sys.argv[0]} TeamName Year") exit(1) team = sys.argv[1] year = sys.argv[2] if not year.isnumeric: print(f"Invalid year {year}") start_year = f"{year}-01-01" end_year = f"{year}-12-31" qT = "select count(*) from Teams where country = %s" q6 = """ select * 2022/5/3 17:24 COMP3311 Sample Solutions https://www.cse.unsw.edu.au/~cs3311/22T1/exams/sample/soln.html 5/10 from q6 where (team1 = %s or team2 = %s) and date between %s and %s order by date “”” try: db = psycopg2.connect(“dbname=footy”) cur = db.cursor(); cur.execute(qT, [team]) tup = cur.fetchone() if not tup: print(f”No team ‘{team}'”) exit(1) cur.execute(q6, [team,team,start_year,end_year]) res = cur.fetchall() if len(res) == 0: print(“No matches”) exit(1) for tup in res: where,date,t1,g1,t2,g2 = tup if t1 == team: result = getResult(g1,g2) goals = f”{g1}-{g2}” opponent = t2 else: result = getResult(g2,g1) goals = f”{g2}-{g1}” opponent = t1 print(f”played {opponent} in {where} on {date} and {result} {goals}”) except psycopg2.Error as err: print(“DB error: “, err) finally: if db: db.close() if cur: cur.close() Q7 #!/usr/bin/python3 # COMP3311 22T1 Exam Sample # Q7: print a specified player’s career performance # and, yes, John was naughty using a query inside a for loop … import sys import psycopg2 db = None cur = None if len(sys.argv) < 2: print(f"Usage: {sys.argv[0]} PlayerName") exit(1) player = sys.argv[1] qPlayer = "select id,name from Players where name = %s"; qGames = """ select m.id, m.city, m.playedOn 2022/5/3 17:24 COMP3311 Sample Solutions https://www.cse.unsw.edu.au/~cs3311/22T1/exams/sample/soln.html 6/10 from Teams t join Involves i on (i.team=t.id) join Matches m on (m.id=i.match) join Players p on (t.id=p.memberof) where p.id = %s order by m.playedOn “”” qGoals = “select count(*) from Goals where scoredIn = %s and scoredBy = %s” qTeam = “”” select t.country from Teams t join Players p on (t.id = p.memberof) where p.id = %s “”” totMatches = 0 totGoals = 0 try: db = psycopg2.connect(“dbname=footy”) cur = db.cursor(); cur.execute(qPlayer,[player]) res = cur.fetchone() if not res: print(“No such player”) exit(1) pid,name = res cur.execute(qGames, [pid]) for g in cur.fetchall(): totMatches = totMatches + 1 mid,city,date = g cur.execute(qGoals, [mid,pid]) ngoals = cur.fetchone()[0]; totGoals = totGoals + ngoals if ngoals == 0: continue elif ngoals == 1: goals = ” and scored 1 goal” else: goals = f” and scored {ngoals} goals” print(f”played in {city} on {date}{goals}”) cur.execute(qTeam, [pid]) team = cur.fetchone()[0] print(f”Summary: played for {team}, {totMatches} matches, {totGoals} goals”) except psycopg2.Error as err: print(“DB error: “, err) finally: if cur: cur.close() if db: db.close() Q8 a. ER-style mapping for subclasses: create table Employee ( id integer, name text, position text, primary key (id) 2022/5/3 17:24 COMP3311 Sample Solutions https://www.cse.unsw.edu.au/~cs3311/22T1/exams/sample/soln.html 7/10 ); create table PartTime ( id integer references Employee(id), fraction float check (0.0 < fraction and fraction < 1.0), primary key (id) ); create table Casual ( id integer references Employee(id), primary key (id) ); create table HoursWorked ( id integer references Casual(id), onDate date, starting time, ending time, primary key (id,onDate), constraint timing check (starting < ending) ); We cannot enforce the total participation constraint (an employee may have no associated subclass tuples). We cannot enforce the disjoint subclasses constraint (an employee may have several associated subclass tuples). b. Single-table mapping for subclasses: create table Employee ( id integer, name text, position text, etype text not null check (etype in ('part-time','casual')), fraction float check (0.0 < fraction and fraction < 1.0), primary key (id), constraint CheckValidTypeData check ((etype = 'part-time' and fraction is not null) or (etype = 'casual' and fraction is null)) ); create table HoursWorked ( id integer references Employee(id), onDate date, starting time, ending time, primary key (id,onDate), constraint timing check (starting < ending) ); With an appropriate CheckValidTypeData constraint we can enforce the disjoint subclass constraint. With the not null requirement on etype, we can enforce the total participation constraint. The etype field could be replaced by a boolean which checks isCasual. It is also feasible to omit the etype field and simply assume that fraction being not null means that the employee is part-time. In neither case can we enforce that part-time employees do not have hours-worked associated with them. Q9 a. Trigger to handle adding a new CourseEnrolments tuple: create function fixCoursesOnAddCourseEnrolment() returns trigger as $$ 2022/5/3 17:24 COMP3311 Sample Solutions https://www.cse.unsw.edu.au/~cs3311/22T1/exams/sample/soln.html 8/10 declare _nS integer; _nE integer; _sum integer; _avg float; begin select nS,nE,avgEval into _nS,_nE,_avg from Courses where id=new.course; — add one more student _ns := _nS + 1; if (new.stuEval is not null) then — got another evaluation _nE := _nE + 1; if (_nS ≤ 10 or (3*_nE) ≤ _nS) then — added a new student, but still not enough for valid eval _avg := null; else — compute new evaluation select sum(stuEval) into _sum from CourseEnrolments where course=new.course; _sum := _sum + new.stuEval; _avg := _sum::float / _nE; end if; end if; — update Course record update Courses set ns = _nS, nE = _nE, avgEval = _avg where id=new.course; — since “after” trigger, return value irrelevant return new; end; $$ language plpgsql; b. Trigger to handle dropping a CourseEnrolments tuple: create function fixCoursesOnDropCourseEnrolment() returns trigger as $$ declare _nS integer; _nE integer; _sum integer; _avg float; begin select nS,nE,avgEval into _nS,_nE,_avg from Courses where id=old.course; — we always add one more student _nS := _nS – 1; if (old.stuEval is not null) then — lost an evaluation _nE := _nE – 1; if (_nS ≤ 10 or (3*_nE) ≤ _nS) then — no longer enough for valid eval _avg := null; else — compute new evaluation select sum(stuEval) into _sum from CourseEnrolments where course=old.course and student<>old.student; _avg := _sum::float / _nE; end if; end if; — update Course record update Courses set nS = _nS, nE = _nE, avgEval = _avg where id=old.course; — since “after” trigger, return value irrelevant return old; end; 2022/5/3 17:24 COMP3311 Sample Solutions https://www.cse.unsw.edu.au/~cs3311/22T1/exams/sample/soln.html 9/10 $$ language plpgsql; c. Trigger to handle updating a CourseEnrolments tuple: create function fixCoursesOnModCourseEnrolment() returns trigger as $$ declare _newEval integer; _oldEval integer; _nE integer; _nS integer; _sum integer; _avg float; begin select nS,nE,avgEval into _nS,_nE,_avg from Courses where id=old.course; if (old.stuEval is null and new.stuEval is not null) then — update involves adding evaluation _nE := _nE + 1; end if; — treat NULL as zero for arithmetic _oldEval := coalesce(old.stuEval,0); _newEval := coalesce(new.stuEval,0); if (_oldEval <> _newEval) then — compute new evaluation select sum(stuEval) into _sum from CourseEnrolments where course=old.course; _avg := (_sum – _oldEval + _newEval)::float / _nE; end if; — update Course record update Courses set nS = _nS, nE = _nE, avgEval = _avg where id=old.course; — since “after” trigger, return value irrelevant return new; end; $$ language plpgsql; Q10 a. The code prints a list of teams and the number of matches they have played in each city. b. The outer query (teams) is executed once, and returns 100 tuples (assumption). For each of these, one (inner) query (count) is executed. Total calls to execute() = 101. c. Python code to achieve the same effect with a single query: q = “”” select t.country, m.city, count(*) from Teams t join Involves i on (i.team = t.id) join Matches m on (i.match = m.id) group by t.country, m.city order by t.country, m.city “”” db = psycopg2.connect(“dbname=footy”) cur = db.cursor() cur.execute(q) results = cur.fetchall() for tuple in results: team, city, nmatches = tuple print(f”{t} {c} {n}”) 2022/5/3 17:24 COMP3311 Sample Solutions https://www.cse.unsw.edu.au/~cs3311/22T1/exams/sample/soln.html 10/10 Q11 a. FDs: A→BC, DE→F, ADE→G (also accept A→B, A→C instead of A→BC) b. Step Attrs FDs Key Notes 1 ABCDEFG A→BC, DE→F,ADE→G ADE A→BC violates BCNF, LHS is partial key, so partition 2a ABC A→BC A No FDs violate BCNF, so ABC is part ofsolution 2b ADEFG DE→F, ADE→G ADE DE→F violates BCNF, LHS is partial key,so partition 3a DEF DE→F DE No FDs violate BCNF, so DEF is part ofsolution 3b ADEG ADE→G ADE No FDs violate BCNF, so ADEG is partof solution Solution: three tables: ABC, DEF, ADEG (i.e. Student, Assessment, Mark) Q12 a. Which employees earn more than $20 per hour (give their employee id and name) Tmp1 = Sel[payRate>20]Employees Res = Proj[eno,ename]Tmp1 b. Who are the department managers (give just their name) Tmp1 = Employees Join Departments (on eno) Res = Proj[ename]Tmp1 c. Which employees worked on every day during the last week (give just their name) Tmp1 = Proj[day]Timesheet Tmp2 = Proj[eno,day]Timesheet Tmp3 = Tmp2 / Tmp1 Tmp4 = Employees Join Tmp3 (on eno) Res = Proj[ename]Tmp4 Would expect to see division used … if not, but still correct, ok, e.g. Tmp1 = Proj[eno](Sel[day=’Mon’]Timesheet) Tmp2 = Proj[eno](Sel[day=’Tue’]Timesheet) … Tmp7 = Proj[eno](Sel[day=’Sun’]Timesheet) Tmp8 = Tmp1 Intersect Tmp2 Intersect … Tmp7 Tmp9 = Employees Join Tmp8 Res = Proj[ename]Tmp9