COMP3311 25T2 Assignment 1
SQL & PLpgSQL on the Football DB 		Database Systems
Last updated: Sat 22nd Jun 11:00am
Most recent changes are shown in red ... older changes are shown in brown.

[Assignment Spec]   [SQL Schema]   [Examples]   [Testing]

Aims

This assignment gives you practice in

The goal is to build useful data-access operations on the Football DB, which stores information about international matches, teams, players, goals and disciplinary cards.
Your tasks involve writing views that extract meaningful insights and PLpgSQL functions that provide user-friendly search capabilities.

Summary

Submission: Login to Course Web Site -> Assignments -> Assignment 1 -> [Submit] -> upload ass1.sql

or, on a CSE server: give cs3311 ass1 ass1.sql
Required Files: ass1.sql (contains both SQL views and PLpgSQL functions)
Deadline: 20:59:59 Friday 4th July 2025
Marks: 13 marks toward your total mark for this course
Late Penalty: 0.2 percent off the achieved mark for each hour late, up to 5 days
any submission after 5 days scores 0 marks (UNSW late-penalty policy)

How to do this assignment

Background

Football (a.k.a. soccer) is frequently described as "the world game", and huge quantities of data are collected for international fixtures: match venues, participating teams, player rosters, goals, disciplinary actions, and so on.
The Football DB for this assignment is a compact abstraction designed around the entity-relationship diagram shown below.

Football schema

The schema models:

The full SQL definition of the schema is provided on the SQL Schema page.
Study the design and familiarise yourself with the data before attempting the questions.

Setting Up

Create a working directory and obtain the starter files:

cse$ mkdir -p ~/cs3311/ass1
cse$ cd ~/cs3311/ass1
cse$ cp    /home/cs3311/web/25T2/assignments/ass1/files/ass1.sql     ass1.sql
cse$ ln -s /home/cs3311/web/25T2/assignments/ass1/database/ass1.dump ass1.dump

Then on vxdb02:

vxdb02$ dropdb --if-exists ass1
vxdb02$ createdb ass1
vxdb02$ psql ass1 -f ass1.dump   -- loads schema + data
vxdb02$ psql ass1                -- explore the database

if you are working on your own machine, you can download the starter files here:
ass1.sql
ass1.dump

Your Tasks

Answer each of the following questions by adding SQL or PLpgSQL code to ass1.sql.
If a question askes you to create a view, you may also create auxiliary views, but not auxiliary functions.
If a question askes you to create a function, you may also create auxiliary views or auxiliary functions.
But be sure they appear before their first use.
ass1.sql should not create new tables, types, or other SQL objects: just views and functions.
ass1.sql should not insert, update, or delete any data.

Q0 (2 marks)

Style mark

Given that you've already taken multiple programming courses, we should be able to assume that you'll express your code with good style conventions. But, just in case...

You must ensure that your SQL queries follow a consistent style. The one used in the lectures is fine. An alternative, where the word join comes at the start of the line, is also OK. The main thing is to choose one style and use it consistently.

Similarly, PLpgSQL should be laid out like you would lay out any procedural programming language. E.g. bodies of loops should be indented from the for or while statement that introduces them. E.g. the body of a function should be indented from the begin...end.

Ugly, inconsistent layout of SQL queries and PLpgSQL functions will be penalised.

Q1 (0.5 marks)

Create a view Q1(player, born) that returns the name and birth date of the oldest registered player(s). If multiple players share the same earliest birthday, include all of them and order them by player in ascending order.

Q2 (0.5 marks)

Create a view Q2(team, country, total_matches) that returns each team (id and country) that has played in at least 5 distinct matches. Order the result by total_matches in descending order, then by team in ascending order.

Q3 (0.5 marks)

Create a view Q3(player_id, player, goals_scored, first_goal_date) listing every player who has scored at least 6 goals in total, along with their total goal count, and the date of their first-ever goal. Order by goals_scored in descending order first, then by player (ascending), and then finally by player_id in ascending order.

Q4 (1 mark)

Create a view Q4(player_id, player, yellow_cards, red_cards, discipline_score) where discipline_score = red_cards * 5 + yellow_cards * 2. Include only players who have received at least two cards. Order the result by discipline_score in descending order, then by player (ascending), and then by player_id (ascending).

Q5 (1.5 marks)

Create a view Q5(match_id, home_team, away_team, goals_for_each_team) that, for each match with a total goals > 4, returns:

(You can assume Involves always lists exactly two teams.)

Order by ascending match_id.

Q6 (2 marks)

Create a view Q6(match_id, score, yellow, red) that lists each match in which:

Order the result by total number of cards in the match (yellow + red) in descending order, then by ascending match_id.

Q7 (2 marks)

Create a view Q7(match_id, winning_team, halftime_score, fulltime_score) for comeback wins: the eventual winner was behind at half-time (goals counted ≤ 45 min). Compute both scorelines as 'x-y' strings (home-away), identifying home and away teams by Involves.is_home. Order by match_id (ascending).

Q8 (2 marks)

Write a PL/pgSQL function Q8(search_term text) that performs a case-insensitive search on Players(name). The function must return a set of text rows, each formatted as: player_name | country | position | career_goals | career_cards.

The function should:

  1. Select only those players whose name contains the search_term string, ignoring uppercase/lowercase differences.
  2. Compute career_goals = total number of goals scored by that player.
  3. Compute career_cards = total number of cards (yellow + red) received by that player.
  4. Return rows sorted by career_goals in descending order, then by player_name in ascending order, and then by Players.id in ascending order.

Q9 (3 marks)

Write a PL/pgSQL function q9(_match_id integer) that:

Submission and Testing

We will test your submission as follows:

Note that there is a time-limit on the execution of queries. If any query takes longer than 3 seconds to run (you can check this using the \timing flag) your mark for that query will be reduced.

Your submitted code must be complete so that when we do the above, your ass1.sql will load without errors. If your code does not work when installed for testing as described above and the reason for the failure is that your ass1.sql did not contain all of the required definitions, you will be penalised by a 2 mark penalty. If you code does not load because your definitions are in the wrong order, there will be a 1 mark penalty.

A Clear Understanding of Responsibilities

Our Commitment to You

We value transparency and strive to ensure a clear understanding of our responsibilities and expectations. In this regard, we would like to outline some of our responsibilities to you:

Assignment Release and Marking
Addressing Errors and Providing Support

Your Responsibilities

To ensure a fair and productive learning experience, we kindly request that you:

Have fun