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: act actID SERIAL actname VARCHAR(100) genre VARCHAR(20) members INTEGER standardfee INTEGER gig gigID SERIAL venueid INTEGER gigtitle VARCHAR(100) gigdate TIMESTAMP gigstatus VARCHAR(10) act_gig actID INTEGER gigID INTEGER actfee INTEGER ontime TIMESTAMP duration INTEGER venue venueid SERIAL venuename VARCHAR(100) hirecost INTEGER capacity INTEGER gig_ticket gigID INTEGER pricetype VARCHAR(2) cost INTEGER ticket ticketid SERIAL gigID INTEGER pricetype VARCHAR(2) cost INTEGER CustomerName VARCHAR(100) CustomerEmail VARCHAR(100) 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 An FAQ will be maintained at https://warwick.ac.uk/fac/sci/dcs/teaching/material/cs258/courseworkfaq/ 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. 4 https://docs.oracle.com/javase/8/docs/api/java/sql/PreparedStatement.html
欢迎咨询51作业君
