CS258编程代写、代写SQL程序语言

日期：2021-11-22 10:20

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/cheat-sheet

2 https://www.oracle.com/technical-resources/articles/java/javadoc-tool.html

3 https://docs.oracle.com/javase/8/docs/api/java/time/LocalDateTime.html

(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/docs/14/functions-datetime.html

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.