CS258 Unsupervised Practical
Assignment: (2021/2022)
GigSystem Specification
A music promotion company is planning to book acts for an upcoming festival. You will help them
design a system to store information about which acts have played in a particular venue.
The system must consist of the following tables with the following attributes:
Acts (such as a band, solo musician, comedian, etc.) have a name, a number of members in the band
and a standard fee that they would usually charge for being part of a gig.
Gigs take place in a venue, have a gigtitle, take place on a particular gigdate (including start time),
and have a gigstatus of either‘Cancelled’or‘GoingAhead’.
Acts are associated with a particular gig via the act_gig table. Each act charges an actfee (not
necessarily their standardfee) for a gig. They have a particular time when they start (which must be
after the start of the gig), and their performance lasts for a particular duration (a number of
minutes). No gigs should go beyond 11:59pm.
Venues have a name, a hirecost (amount in ￡ that it costs to hire the venue for a day) and a capacity
(maximum number of customers allowed at once, therefore maximum number of tickets to be sold).
Customers buy tickets. The standard cost of a ticket is stored in gig_ticket. There may be different
types of ticket, each with a different cost. The system will mostly deal with ticket type‘A’(for
‘Adult’), but there may be concessionary tickets added to (e.g.‘C’for Children,‘O’for‘OAP’or‘F’for
complementary tickets) – these are just examples, and you should not add any extra constraints. A
customer’s purchased ticket is stored in the ticket table with their CustomerName and
CustomerEmail address. You can assume that a customer is identifiable by their email address (no
two customers share an email address), and that the customer always uses the same name and
email address when booking tickets.
Part 1: Schema [20%]
Based on the above specification (with the same table names and column names), write a
schema.sql file to create the necessary tables.
Use SQL to specify any key and referential integrity constraints you might need to capture the
description given in the introduction as accurately as possible. Also specify any additional constraints
that you believe should apply to the system.
Any other schema related objects (e.g., Views/Procedures/Functions/Triggers/Sequences) that you
use as part of the system should also be defined in schema.sql.
Part 2: Design Choices [5%]
The tables as described in the introduction should not be changed for the purposes of this
coursework. If you were to make any modifications, what changes might you make to improve the
table structure? Write your answer in a section headed Design Choices in README.md
Part 3: Application [75%]
Write (and test) a Java program that implements the options specified below. In the source code we
have provided, you will find GigSystem.java and GigTester.java.
GigSystem.java contains a template of your main Java program. You must implement each of the
options to carry out the described behaviour. You may also add a menu in the main method (similar
to that used in the labs), but the marking will only assess the content of the options’methods. If you
implement a menu, you will be able to test your system interactively (allowing the user to type
input, and sending it to your JDBC queries). If you do make an interactive menu you can use the
readEntry method to gather user input, however, you must not read user input within the option
methods (otherwise automated tests will hang when calling these methods).
GigTester.java will give you an idea of how your GigSystem.java methods will be called. There is a
testOption method for each of the options, but not all are fully implemented. The sample data
provided is not an exhaustive set of tests. It is there to help you understand the basic requirements
of the query. In each case, there may be other situations that can occur that are not demonstrated
by the sample data. You are expected to experiment with other data to ensure that your query can
cope with all eventualities. More information about testing is given below.
You will be expected to submit three files:
? schema.sql – containing all the SQL definitions needed to recreate your database
? README.md – containing information about your solution and answers to the‘Design
Choices’part. This can contain plain text, or you can use markdown1 formatting.
? GigSystem.java – your completed java file
Document your solutions
Any decisions that you make should be justified in your README.md file. It is expected that your
program should be able to appropriately handle any error cases that might occur (such as invalid
inputs or failures that arise from violating database constraints). Any java methods you make should
be commented as clearly as possible (preferably JavaDoc2 style). For each option, you should write
around 100-200 words explaining what your solution does (describing the behaviour of your SQL
statements/queries).
Option 1: Gig Line-Up
The Java program needs to be able to find the line-up for any given gigID. There should be the
actname, the time they will start and the time they will finish. The option1 method should return
this information in the two-dimensional array of strings. If you’d like to see the output, you can use
the printTable method.
The output should look like this (headings must not be in your array of strings, these are given to
illustrate the expected order of the columns):
Act Name On Time Off Time
ViewBee 40
The Where
The Selecter
18:00:00
19:00:00
20:25:00
18:50:00
20:10:00
21:25:00
Option 2: Organising a Gig
Set up a new gig at a given venue (referred to as a string containing the venue name). The venue
supplied will always already exist in the database.
There will be an array of acts (given as actIDs), an array of fees, an array of LocalDateTime3
objects representing the time the act will start, and an array of durations provided. There will be a
standard adult ticket price provided (adultTicket).
If the array of acts & times does not meet the timeslot criteria, or any other constraint fails, cancel
the insertion of the gig and ensure the database state is as it was before the method was called.
The length of the actIDs array will always be the same length as the length of the fees, onTimes
and durations arrays.
The date of the gig can be taken from the date of the first act onTime (this will always be
onTimes[0], although subsequent elements in onTimes will not necessarily be in date order).
Option 3: Booking a Ticket
A customer wants to buy a ticket. You will be provided with customer details , a gigID and a
ticketType (the pricetype of the ticket that is stored in gig_tickets). If any details are inconsistent
1 https://www.markdownguide.org…
2 https://www.oracle.com/techni…
3 https://docs.oracle.com/javas…
(e.g. if the gig does not exist, or there is some other error), make sure the database state is as it was
before the method was called.
Option 4: Cancelling an Act
An act needs to cancel a gig (gigID and actName supplied).
Remove the act from the gig. If this would lead to a gap of more than 20 minutes in the schedule or
if the act is the headline act for this gig (the final or only act), cancel the entire gig (change the status
to‘Cancelled’(case sensitive), and change the cost of all tickets sold for that gig to be 0 (but do not
change the original price of the ticket in gig_ticket). If the gig does need to be cancelled, return an
array of strings containing email addresses of customers who have affected tickets, ordered by
customer email, containing no duplicates. If the gig is not cancelled, return null.
Option 5: Tickets Needed to Sell
For each gig, find how many how many standard tickets (ticket type‘A’) still need to be sold for the
promoters to, at least, be able to pay all the agreed fees for the acts and to pay the venue fee. The
output should only include the gigID and the tickets required to sell, ordered by gigID (smallest first).
Include gigs that haven’t sold any tickets yet.
Sample output (headings must not be in your output, these illustrate expected column order):
gigID Tickets To Sell
1 1100
2 1500
3 1100
4 750
5 1450
6 1100
7 1100
8 1775
9 1573
10 323
Option 6: How Many Tickets Sold
Create a 2-dimensional array of strings to show the total number of tickets (of any pricetype) that
each act has sold. Only consider gigs where the act is listed as a headline act, and only include gigs
that are not listed as cancelled.
For each act, find the number of tickets sold per year and show the total number of tickets ever sold
by each act as a headliner. Note that the year column will need to be considered as text so that the
word‘Total’can be used.
Order your result with the acts who have sold the least total number of tickets first, then ordered by
Year with total at the end of the list of years.
Sample output (headings must not be in your output, these illustrate expected column order):
Act name Year Total Tickets Sold
QLS 2018 2
QLS 2019 1
QLS Total 3
ViewBee 40 2017 3
ViewBee 40 2018 1
ViewBee 40 Total 4
Scalar Swift 2017 3
Scalar Swift 2018 1
Scalar Swift 2019 1
Scalar Swift Total 5
Join Division 2016 2
Join Division 2018 2
Join Division 2020 3
Join Division Total 7
The Selecter 2017 4
The Selecter 2018 4
The Selecter Total 8
The Where 2016 1
The Where 2017 3
The Where 2018 5
The Where 2020 4
The Where Total 13
Option 7: Regular Customers
The festival organisers want to know who regularly attends gigs that feature particular acts. Create a
two-dimensional array of strings that shows each act who has ever performed a gig as a headline act
along with the names of customers who have attended at least one of these gigs per calendar year
(if the act performed such a gig as a headline act in that year). The output should list the acts in
alphabetical order and the customers in order of the number of tickets that the customer has ever
bought for a gig where that act was the headline act (with customers who have bought the most
tickets listed first).
If the act has no such customers, and the act has played gigs as a headline act, make sure the act is
still listed, but with‘[None]’in the customers column.
Sample output (headings must not be in your output, these illustrate expected column order):
Act Name Customer Name
Join Division
QLS
Scalar Swift
Scalar Swift
G Jones
[None]
G Jones
J Smith
Option 8: Economically Feasible Gigs
The festival organisers want to organise a gig with a single act. They’re trying to choose an act for a
specific venue, but don’t want to charge more than the average ticket price. At worst, they want to
make sure they break even, so they need to sell enough tickets to be able to pay for the act’s
standard fee and the cost of hiring the venue.
Based on the standard fee for each act, and the average price amongst all tickets that have been
sold (excluding tickets to gigs that have been cancelled), create a two-dimensional array of strings
that includes each venue and the alongside all acts that it would be economically feasible to book in
that venue (assuming that act is the only act at the gig). If there are no such acts, do not include an
entry for the venue.
Economically feasible means that the ticket sales must be equal to or exceed the cost of paying the
act their standard fee and to pay the venue the hire fee. Also include the minimum number of
tickets that would need to be sold (assuming each ticket is sold at the average price). Order your
output in alphabetical order of venue name, then proportion of tickets that need to be sold (highest
first), and alphabetical order of act name.
=
tickets required
venue capacity
Sample output (headings must not be in your output, these illustrate expected column order):
Venue Name Act Name Tickets Required
Arts Centre Theatre Join Division 150
Big Hall The Where 675
Big Hall Join Division 375
Cinema Join Division 175
Symphony Hall ViewBee 40 1275
Symphony Hall Scalar Swift 1250
Symphony Hall QLS 1225
Symphony Hall The Selecter 1200
Symphony Hall The Where 825
Symphony Hall Join Division 525
Town Hall The Where 575
Town Hall Join Division 225
Town Hall Join Division 275
Village Green Join Division 100
Village Hall Join Division 75
Maintaining the database state
At all times (before an option method starts and after it has completed, even if the option
encounters an error), the following conditions must be observed:
There should be no overlap between acts on the same night (although one act can start as
soon as the previous act has finished, e.g. it’s fine if Act 1 finishes at 20:30 and act 2 starts at
20:30).
There should be no more than 20 minutes interval (gap) in a gig line-up.
Acts cannot start before the date/time of a gig.
The first act at the gig must start within 20 minutes of the date/time of the gig.
No act should be on-stage for more than 2 hours.
No gig should finish after 11:59pm, all gigs start and finish on the same calendar day).
There should not be more tickets sold for a gig than the capacity of the venue.
TimeStamps are used in the database – this is very similar to how we used Date in the labs, but
TimeStamps include a time component. You should use setTimestamp4 in a similar way to how you
used setDate in the labs.
For more information about manipulating dates in SQL, you could look at
https://www.postgresql.org/do…
What constitutes a good answer?
Use prepared statements where you can – this is good for both security and efficiency.
Handle errors appropriately – think about what errors could potentially occur, and how you might
deal with them.
Good answers are thoroughly tested answers.
Use SQL instead of Java where possible. For each query that returns a result (those methods that
have return type String[][]), it is possible to write an answer that gets all data from the
database without processing the query result (other than converting your ResultSet to a two-
dimensional string using the convertResultToStrings method). There should be no need to
do any filtering/ordering in Java. Any solutions that use Java to help with filtering, ordering or
changing the structure of the returned data will receive a lower mark than completely SQL solutions.
For all your answers, supply appropriate comments (and descriptions in README.md) to describe
how your code and SQL queries work. In addition, as with all programming, you should adhere to
programming best practices with respect to formatting.
FAQ
Final Remarks
Please make sure your code works on the DCS servers (you must use PostgreSQL 14, which is the
version installed on the DCS servers). Keep a copy of everything you submit! You may discuss with
fellow students the material covered in lectures and seminars, but you are not allowed to
collaborate on the assignment.
The University of Warwick takes plagiarism seriously, and penalties will be incurred if any form of
plagiarism is detected. Copying, or basing your work on, solutions written by people who have not
taken this module is also counted as plagiarism. This includes material that has been downloaded
from the internet.