IT unit ACCESS (software)10%
DEPARTMENT OF COMPUTING
ISYS104 2019 – ASSIGNMENT TWO
(out of 50 – worth 10%)
Due on Friday of week 10
(exact date is available on iLearn submission link)
Case Background
G’day ticketing company has been selling concert tickets featuring different artists for the past 18 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.
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
a. Create the table Artist with the following fields and properties:
Field Name Data Type Description Properties
ArtistID Number Artist/Band ID Size: Integer
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/Bands average price per ticket
b. ArtistID should be the primary key, if it’s not already the primary key.
c. Populate the table with the following records:
ArtistID AName StartYear Website TicketPrice
1 U2 1976 http://www.u2.com/ 300
2 Beyonce 1990 http://www.beyonce.com/ 236
3 Keith Urban 1991 http://www.keithurban.net/ 237
4 Coldplay 1996 http://coldplay.com/ 227
5 Taylor Swift 2004 https://taylorswift.com/ 250
6 Ed Sheeran 2004 http://www.edsheeran.com/ 253
7 Adele 2006 http://adele.com/ 183
8 5 Seconds of Summer 2011 http://www.5sos.com/ 216
3. Import the other three tables (Artist, Sales and Venue) from the Excel data file provided and adjust the table properties as follow:
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:
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.
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)
• Q1: Display individual artist professional names and country of origin, starting with the youngest person, without displaying the date of births.
• Q2: Display artist’s professional names, birth names and date of births of those that have the professional name started with the letter “a”.
• Q3: Display venue names and phone numbers that have less than 45,000 capacity. Display venues with the lower capacity first.
2. Intermediate Queries (3 marks each)
• Q4: Display the locations, years, and sales for Adele’s concerts in 2016. Do not show “Adele” nor “2016”.
• 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 sales made in the year 2010 or prior.
• Q6: Display the average number of ticket sales over time for each venue. Show the lowest average first.
• Q7: Display artist/band names (sorted alphabetically), the venue names, and the total number of ticket sales. Only show the results with more than 50,000 tickets sold overall.
3. Advanced Queries (4 marks each)
• Q8: Display the artist/band name with the total revenue (in dollars) of ticket sales (show the highest earning first) per artist/band names.
• Q9: Display the artist/band name with discounted ticket price (with 15% discount in dollars) per artist/band (sort alphabetically) without changing the current ticket price.
• Q10: Create a query to permanently update “AU” country of origin to “Australia”.
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. Add a logo/image and title, then change the themes, colour scheme, and/or fonts.
3. Add your details in a blank form and assign yourself to one of the band with MID 18.
4. Lock the MID text box.
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. View data based on Artist and sort by the artist’s professional name alphabetically.
3. Use block layout in landscape orientation.
4. Use similar logo, themes/colour scheme/fonts. Save this report as “Artist”.
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.
2. Artist report in the second tab.
3. A quit application button/text in the form header area.
Use similar logo, themes/colour scheme/fonts. Save this navigation form as “Menu”.
Set it so that this navigation form is automatically open, when opening the database.
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 to include your student ID (e.g. GDAY12345678.accdb).
GET ANSWERS / LIVE CHAT