程序代写案例-COMP1750 2021-Assignment 2

欢迎使用51辅导,51作业君孵化低价透明的学长辅导平台,服务保持优质,平均费用压低50%以上! 51fudao.top
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作业君
51作业君

Email:51zuoyejun

@gmail.com

添加客服微信: abby12468