G54DIS / COMP4039 – Databases, Interfaces & Software Design Principles
Coursework 1, Part B: Querying the
database
Due date – 20th November 2020, submission open for 24hrs, submitted via
Moodle
Late Submissions Policy
Late submissions will be accepted, but they will be penalised at the University
standard rate of 5% per working day.
Plagiarism Policy
See DIS-COMP4039 Coursework 1, Part A for the plagiarism policy.
Coursework Scenario
For this coursework we will use same scenario as for DIS-COMP4039 Coursework
1, Part A. However, this time you should use the database supplied (cousework-1b-
database.sql) which contains a simplified database for this scenario. You should
import this database, and work with it for the following exercise.
Coursework 1B questions
Note that Coursework 1 is worth a total of 20% of the marks for this module. It
is divided into two separate parts, 1A and 1B (this part), worth 10% each. This
element (1B) is marked out of a total of 15—these marks will be scaled to provide
the final mark for this coursework.
When a character string is used in an example (e.g. “Rachel Smith” in Question 1),
use the given character string and not any other character string. A query that uses
another character string will be counted as incorrect even if the syntax of the query is
the same.
You will be provided with a template submission file in the form SubmissionCW1B-
FirstNameLastName.sql which you need to fill and submit on Moodle.
• Do not write anything other than SQL queries to specifically solve each
question in that file.
• Only write the SQL queries that are required in the questions.
• If you do not have an answer for a question, do NOT delete its header, and
simply leave it blank.
• Terminate each query with a semi-colon so that they can be run in sequence.
• Some questions will call for more than one query, some questions will need to
be solved with a single query. Use the smallest number of queries possible.


G54DIS / COMP4039 – Databases, Interfaces & Software Design Principles
Question 1
Show the date and amount of every fine charged to the person named: “Rachel
Smith”.
[1 mark]
Question 2
Show the car registration number, the name of the owner, and the total amount of
the fines accumulated, for all cars which have accumulated a total of more than
£1,000 of fines.
[1 mark]
Question 3
Show the average amount of fines for each person present in the database. Order
the results in reverse alphabetical order of the people names.
[1 mark]
Question 4
Show the offence descriptions and the maximum values of those fines given to
people driving a red car.
[2 marks]
Question 5
The aim is to show a list of all of the offences committed by “Lewis Thomson”,
together the dates on which they were committed, the offence, the officer’s
statement, the amount for which he was fined on each occasion and the theoretical
maximum amount which he could have been fined.
The output of this query should be:

Provide TWO different SQL queries that solve the above problem, using different
techniques (name the techniques in comments in your code). The SQL queries must
be searches on the name, not any PK IDs (e.g., a SELECT on “Lewis Thomson”
rather than “4”, assuming this to be the PK ID associated with “Lewis Thomson”).
[2 marks]

G54DIS / COMP4039 – Databases, Interfaces & Software Design Principles
Question 6
Write SQL that will add a new fine to the database, given to a new person driving a
new vehicle – as follows:
• Name: Andy Phillips
• Date of Birth: 3rd November, 1997
• Address: Manchester
• Vehicle: Silver Vauxhall Astra, LZ64XYK
• Offence: Driving without insurance
• Fine: 1000
• Report: Insurance expired two days previously
[3 marks]
Question 7
Write SQL that will update the database so that all records of blue cars will be
removed. Then, show all of the information stored in the Fines table.
[2 marks]
Question 8
Write SQL that will create a new table called MajorOffenders that contains three
columns: PersonID, Name and FineTotal with data for those people with total fines
greater than 1,000. Then show all of the information stored in this new table.
NOTE: Tuples of this new relation should be calculated from the existing tables in
the database using relational queries and aggregate functions.
[3 marks]