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:

b. ArtistID should be the primary key, if it’s not already the primary key.

c. Populate the table with the following records:

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

b. Sales Table

    

c. Venue Table

d. Assign a primary key to each table, without adding anotherfield.

4. Create the relationship between the four tables. Enforce referential integrity for each relationship created (1 mark).

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 ticketsales 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 “Australia” country of origin to “AU”. Save as Query10.

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/orfonts, and add your details in a blank form and assign yourself to one of the bands with MID18. (2 marks).

3. 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).