COMP1750 2021-Assignment 2 DEPARTMENT OF COMPUTING COMP1750 2021 – ASSIGNMENT TWO (out of 50 – worth 15%) Due on Friday by 5 pm in week 11 (21 May 2021) Case Background G’day ticketing company has been selling concert tickets featuring different artists for the past 20 years across Australia. You have been hired to build a database to store and record their ticket sales, as well as preparing queries, forms, reports, and a user-friendly navigational menu. Note: • An artist may have multiple performances in the same venue, but not in the same year. • Assume there is only one venue per city • Artist may consist of either bands (group performers) or an individual artist • Member consists of individuals that formed the bands or further data of individual artists Task Descriptions Your tasks in this assignment are as follows: Task 1: Setting up Database (5 marks) Note that anything with “???” means that you will need to assign an appropriate replacement. 1. Create a blank database called GDAY followed by your student ID number (e.g. GDAY12345678). 2. Artist Table (1 mark) a. Create the table Artist with the following fields and properties: Field Name Data Type Description Properties ArtistID Number Artist/Band ID Size: ??? AName Short Text Artist/Band name Size: 50 Caption: Artist/Band Name StartYear Number Artist/Band starting year Size: ??? Website ??? Artist/Band’s official website URL Format: < TicketPrice ??? Artist/Band's average price per ticket b. ArtistID should be the primary key, if it’s not already the primary key. COMP1750 2021-Assignment 2 c. Populate the table with the following records: ArtistID AName StartYear Website TicketPrice 1 U2 1976 http://www.u2.com/ 350 2 Beyonce 1990 http://www.beyonce.com/ 266 3 Keith Urban 1991 http://www.keithurban.net/ 217 4 Coldplay 1996 http://coldplay.com/ 207 5 Taylor Swift 2004 https://taylorswift.com/ 250 6 Ed Sheeran 2004 http://www.edsheeran.com/ 293 7 Adele 2006 http://adele.com/ 193 8 5 Seconds of Summer 2011 http://www.5sos.com/ 223 3. Import the other three tables (Artist, Sales and Venue) from the Excel data file provided and adjust the table properties as follow (3 marks): a. Member Table Field Name Data Type Description Properties MID Number Artist Member’s ID Size: Integer PName Short Text Artist Member’s professional name Size: 50 Caption: Professional Name BirthName ??? Artist Member’s birth name Size: 50 DOB ??? Artist Member’s date of birth Format: Medium Date Caption: Date of Birth Input mask: ??? Origin Short Text Artist Member’s country of origin Size: 20 Artist Number Related Artist Size: Integer Caption: Artist/Band ID b. Sales Table Field Name Data Type Description Properties ArtistID Number Artist/Band ID Size: Integer Caption: Artist/Band ID Location Short Text Venue location Size: 15 Format: ??? (format as capital letters) Year ??? Year of ticket sales Size: Integer Sales ??? Number of tickets sold Size: Long Integer c. Venue Table Field Name Data Type Description Properties City ??? Unique venue location Size: 15 Format: > VenueName Short Text Venue name Size: 30 Address Short Text ??? Size: 60 Phone Short Text Main contact number Size: 10 Input mask: ??? Capacity Number Maximum seating capacity Size: Long Integer d. Assign a primary key to each table, without adding another field. 4. Create the relationship between the four tables. Enforce referential integrity for each relationship created (1 mark). COMP1750 2021-Assignment 2 Task 2: Queries (30 marks) Notes: • Please keep the order of fields to be displayed in the order stated in the instructions. • Use the minimum number of tables and fields required to complete the task. • Do not hide any field, unless instructed to do so. • Minimise any duplications of results in each query. • Do not assume that your database will only have current records, so your query should be valid for future records. Using “Query Design”, create and save each of the following queries: 1. Basic Queries (2 marks each – 1 mark for correct fields required & 1 mark for everything else) • Q1: Display individual artist professional names and country of origin, starting with the oldest person, without displaying the date of births. Save as Query1. • Q2: Display artist’s professional names, birth names and date of births of those who come from “UK”, without displaying “UK”. Save as Query2. • Q3: Display venue names, and phone numbers that have less than 75,000 capacity (exclusive) and the phone numbers start from “13”. Display venues with the lower capacity first. Save as Query3. 2. Intermediate Queries (3 marks each – 1 mark for correct fields required, 1 mark for aggregate function & 1 mark for everything else) • Q4: Display the locations, and sales for Taylor Swift’s concerts in 2016. Do not show “Taylor Swift” nor “2016”. Save as Query4. • Q5: Display the name of the venue, the year and sales for venues that has the word “Stadium” as part of its name and only the highest (maximum) sales made each year in the year 2010 or prior. Save as Query5. • Q6: Display the total number of ticket sales over time for each venue. Show the lowest total number first. Save as Query6. • Q7: Display artist/band names (sorted alphabetically), the venue names, and the average number of ticket sales. Only show the results with more than 50,000 tickets sold in average. Save as Query7. 3. Advanced Queries (4 marks each – 1 mark for query type Select/Update, 1 mark for correct fields, 2 marks for everything else) • Q8: Display the artist/band name with the total revenue (in dollars) of ticket sales (show the highest earning first) per artist/band names. Save as Query8. • Q9: Display the artist/band name with discounted ticket price (with 20% discount in dollars) without changing the current ticket price. Show the result from the highest discounted price first and only if the discounted price is $200 or above per artist/band. Save as Query9. • Q10: Create a query to permanently update “AU” country of origin to “Australia”. Save as Query10. COMP1750 2021-Assignment 2 Task 3: Form (5 marks) 1. Using “Form Wizard”, create a form for Member table without showing the date of birth nor the country of origin in columnar layout. Save this form as “Member” (2 mark). 2. Add a logo/image and title, then change the themes, color scheme, and/or fonts (1 mark). 3. Add your details in a blank form and assign yourself to one of the bands with MID 18 (1 mark). 4. Lock the MID text box (1 mark). Task 4: Report (5 marks) 1. Using “Report Wizard”, create a report including the artist/band name, website URL, member’s professional name, birth name, and date of birth (2 marks). 2. View data based on Artist without any grouping and sort by the artist’s professional name alphabetically (1 mark). 3. Use block layout in landscape orientation (1 mark). 4. Use similar logo, themes/color scheme/fonts. Save this report as “Artist” (1 mark). Task 5: Navigation Form (5 marks) Create a navigation form with horizontal/vertical tabs that includes the following: 1. Member form in the first tab (1 mark). 2. Artist report in the second tab (1 mark). 3. A quit application button/text in the form header area (1 mark). 4. Use similar logo, themes/color scheme/fonts. Save this navigation form as “Menu” (1 mark). 5. Set it so that this navigation form is automatically open, when opening the database (1 mark). Submission Please submit your Access file on iLearn (there will be a submission link made available in the Assignment section) by the deadline. You need to name the Access file followed by your student ID (e.g. GDAY12345678.accdb).
欢迎咨询51作业君