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

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/Band’s 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/ 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

 

  1. 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.

  1. Create the relationship between the four tables. Enforce referential integrity for each relationship created.

Task  2:        Queries

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
    • Q1: Display artist names and official websites, who started professionally after 2000. Show them starting with the ones started more recently.
    • 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 more than 55,000 capacity. Display venues with the higher capacity first.
  2. Intermediate Queries
    • Q4: Display the locations and sales for Ed Sheeran’s concerts in 2013. Do not show the year “2013”.
    • 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 artist/band names, location, year, and sales for concerts in Sydney from the years 2011 to 2015.
    • 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 ticket sold.
  3. Advanced Queries
    • Q8: Display the artist/band name with the total revenue (in dollars) of ticket sales in the years between 2010 and 2018 (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 “US” country of origin to “United States”.

Task  3:        Form

  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, color scheme, and/or fonts.
  3. Add your details in a blank form and assign yourself to one of the bands with MID 18.
  4. Lock the MID text box.

 

Task  4:        Report

  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/color scheme/fonts. Save this report as “Artist”.

 

Task  5:        Navigation Form

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/color scheme/fonts. Save this navigation form as “Menu”.

Set it so that this navigation form is automatically open, when opening the database.

 

Replace “John Walker” in below table with <your name>

 

$35.00 – Buy Solution

Click on Buy Solution and make payment. All prices shown above are in USD. Payment supported in all currencies. Price shown above includes the solution of all questions mentioned on this page. Please note that our prices are fixed (do not bargain).

After making payment, solution is available instantly.Solution is available either in Word or Excel format unless otherwise specified.

If your question is slightly different from the above question, please contact us at info@myassignmentguru.com with your version of question.