DAD DIST TASK S2 2021-1fffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffF112412412414124124124124124 PDF

Preview

Summary

abcd abcdabcd abcd abcd abcd abcd abcd abcd abcdabcd abcd abcd abcd abcd abcd abcd abcdabcd abcd abcd abcd abcd abcd abcd abcdabcd abcd abcd abcd abcd abcd abcd abcdabcd abcd abcd abcd abcd abcd...

Description

Distinction Task S2 2021 This is a Distinction Task. Tutors expect that people attempting this task will complete most of this task correctly. If you plan to do just some of the work or aim for a 50% effort, then you may want to reconsider attempting this task. See Grading section at the end of this document. This Distinction Task has 5 parts: Part 1 – Test 3 in week 10, Part 2 – ERDs, Part 3 – Build DB, Part 4 – Queries, Part 5 – Visualizations

Part 1. Test 3 in week 10 You will be asked to create some ERDs based on similar narratives to Part 2 in this document. This will ensure that students who are eligible to obtain a Distinction grade for this unit can successfully create / draw an ERD based on a simple narrative.

Part 2. ERDs & Schemas For each of the narratives below, create an ERD. You may either neatly hand-draw the ERD or use a tool such as MS Visio. (Messy or difficult to read hand drawn ERDs will be rejected). Make sure that every entity has appropriate attributes, identifiers are underlined and that relationship names have been created and underlined where necessary. Create a Relational Schema based on your ERD. Indicate all PKs and FKs. Note: No Surrogate keys may be used in your ERD or Relational Schemas.

Narrative One. Splendid Craft Beer Daytrips (SCBD) runs tours around Victoria, Australia. They take groups of beer drinking fanatics to craft breweries, usually for a day at a time, in order for them to sample some of the state’s finest ales, lagers, and other related brews. Each Daytrip has an ID, and a description, and may run many times in a year (such as the West Daytrip). Frequency depends on the season and the specific daytrip. Each time it is run it is called an ‘Event’ and the Date is recorded. Each event has a fee. Fees have usually increased by around 10% each year. SCBD have a number of customers. Each customer has a name, ID and gender. SCBD customers make bookings for daytrip events. When Payment is made the amount and Type of Payment (C- Credit Card, CC - Cash, etc) is recorded. The date that a booking is made is also recorded. If a database was to be built sometime in the future based on your ERD, queries that would be able to run include: • How much money has been spent by every SCDB customer • The total number of the types of payments made • The total sales (quantity) of each SCBD tour event. (You DO NOT have to create these queries) 1

Narrative Two. Foxy Festival Hire (FFH) is a small company that has a large amount of land in the east of Melbourne that is used to run music, wine and food festivals. Each festival has an id and a name, contact person and contact phone number. Each festival may hire a number of Tents. Each tent has a unique barcode and a size (sq. meters) and hire price. Each tent may be filled with various pieces of equipment-type such as tables, chairs, audio-visual equipment and so on. Each equipment-type has an id, a description and a hire price. (e.g. 1, Plastic Chair, $1.00). Note: Each physical piece of equipment does NOT have any id. FFH will record how many items of each equipment-type will be required. E.g. During the Word Music festival, Tent 1 will require 15 tables, 150 chairs, 4 TV screens and 6 surround sound speakers. If a database was to be built sometime in the future based on your ERD, queries that would be able to run include: • List every Festival and which tents are required. • List all the equipment-type quantities required for each tent within a festival • Calculate the total hire cost of all equipment required for a festival (You DO NOT have to create these queries)

Part 3. Build a Database Building a database and Analysing Data.

You are to create a database solution for Outdoor Events Inc (OEI), an organisation that arranges community sporting events. Money raised via these events is donated to the Swinburne Lost Dogs Home charity. The ERD above shows the major entities and relationships. Every Event has an id and a description. It also has an Event code and a distance that the event covers (such as a 10km race). A list of existing events can be obtained via the Data Generator spreadsheet. The Event Type Codes and descriptions are: RB – Road Bike Race MTB – Mountain Bike Race RN – Running Race CC – Cross Country race PO – Parkland Orienteering BO – Bush Orienteering SP – Special Race

People may participate in many events. Each person has a unique id. They also have a name and a gender. Gender is one of three values M, F, or I. The state that a person lives in is also recorded (for the simplicity of this assignment, the full address or phone number are not recorded). 2

Each person belongs to an Age Category. A list of existing people can be obtained via the Data Generator spreadsheet. Age categories are: 1 - Under 20 2 - 20 to 29 3 - 30 to 39 4 - 40 to 49 5 - Over 50 Each person has been asked to raise money for the worthwhile charity. That amount is recorded. People participating in events are eligible for a T-Shirt. The T-Shirt size required by each participant is recorded. People participating in events may be members. If people are members then they receive advertising about future events and special offers. The membership status is simply Y or N. Each person belongs to an X Category. Valid X Category values are A, B, C and D. You will be required to store a result for each person attempting an event. You will need to record the minutes taken by the person to complete the event. Each event will be attempted by many people. Each person can attempt one or more events. A person may only participate in any single event once. A list of existing results can be obtained via the Data Generator spreadsheet.

Instructions to create data for 3 main tables Data for the 3 main tables is available via CSV files that will be created by the Data Generator spreadsheet in Canvas.

•

Open a blank file worksheet in Excel. You will need to enable Macros to run in your version of Excel. Do this by selecting File / Options / Trust Centre / Setting / Macro Setting / Enable All Macros You may now close the blank worksheet.

•

Download the Data Generator Spreadsheet from Canvas and open it.

•

Click the Generate Data button in that spreadsheet. You will be asked for the final 3 digits of your student id. 3 CSV files will be created for you. Note: Each student in this unit will get a complete set of different data.

• •

Set the Macro security level back to standard File / Options / Trust Centre / Setting / Macro Setting / Disable All Macros with Notification

• • •

This data can be imported into Access. The CSV files can also be imported into Power BI.

3

Once your data is in your database, create the following tables: AgeCategory EventTypes XCategory Data for AgeCategory and EventType are available earlier in this assignment. It is also your job to create a description for each XCategory value. (Let your imagination run wild). Note: tutors do not expect to see your descriptions being the same or similar to any other student. There should be no possible way that 2 students can have the same/similar descriptions. Meaningless descriptions such as “Category 1 Description” will be considered inadequate.

Part 4. Queries and Insert Data Q1. People Query List the id, name, donation, gender, state of all people in your database in descending donation amount sequence. Screen capture the first 10 people. Add the screen capture to the Distinction template document.

Q2. Donation Query Create a query to Sum the total amount of donations. Create a query to Average the total amount of donations. Screen capture the Query Design Grid (Access). Screen capture the Result Sets. Add the screen captures to the Distinction template document.

Q3. Gender Query Create a query to Count how many non-members of each gender there are in your database. A member is any person who has the member value Y. Screen capture the Query Design Grid (Access). Screen capture the Result Set.

Q4. Fastest Query Determine the fastest result for every event. List the event no., fastest time in ascending Event No sequence. Fastest means smallest number of minutes and seconds. Screen capture the Query Design Grid (Access). Screen capture the Result Set.

Q5. Better than average Query List the id, name, gender and result time of all persons who had a slower time than the Average time taken by all people who had a result in Event # 2. Screen capture the Query Design Grid (Access). Screen capture the first 10 people.

Q6. No Results Query List the id, name and gender of all persons who had have no records in the result table. Screen capture the Query Design Grid (Access). Screen capture the first 10 people. 4

Q7. XCategory Query Create a query to Count how many people of each XCategory are in your database. The result should show the XCat id, XCatDescription, Count. Screen capture the Query Design Grid (Access). Screen capture the Result Set

Q8. Insert Data Add yourself to the list of people. Use your Student ID as the id and supply your name, gender, etc. Then add your results for event # 25 (The Student Special Race). Give yourself a time for that race. Then add any six other people (from the existing person table) to also participate in this race. List the id, name, gender, state, donation & result time of all people who participated in event # 25. Screen capture the Results.

Part 5. Visualizations You are to create Power BI visualisations for Outdoor Events Inc (OEI). All visualizations that you create must have appropriate headings, legends, annotations, etc . EVERY VISUALIZATION MUST HAVE YOUR STUDENT ID IN THE HEADING.

Q1. Member Category Viz Create any visualization that shows the total number of people by member value by gender value. E.g. Members: Male 230, Female 261, Intersex 5. Non-Members: Male 191, Female 307, Intersex 8. Screen capture the visualisation settings. Ensure that every total is visible in your screen capture. Screen capture the visualisation.

Q2. Age Category Viz Create any visualization the analyses the results by Age Category Screen capture the visualisation settings. Screen capture the visualisation.

Q3. X Category Viz Create any visualization the analyses the results by X Category Screen capture the visualisation settings. Screen capture the visualisation.

Q4. Event Viz Create any visualization the analyses the results by Event Screen capture the visualisation settings. Screen capture the visualisation.

Q5. Event 25 Viz Create any visualization the analyses the results of Event 25 Screen capture the visualisation settings. Screen capture the visualisation.

Q6. Extra Viz 1 Create any new visualization that analyses some other data in the database Screen capture the visualisation settings. Screen capture the visualisation.

Q7. Extra Viz 2 Create any new visualization that analyses some other data in the database Screen capture the visualisation settings. Screen capture the visualisation. 5

SUBMISSION OF DISTINCTION TASK Both the Task PDF and the Access File have to be submitted onto Canvas, The Powerbi file is not submitted but should be saved in case Tutors wish to see it

GRADING This Distinction Task has 5 parts: Part 1 – ERDs, Part 2 – Build DB, Part 3 – Queries, Part 4 – Visualizations, Part 5 – Test3 in week 10 Each part will be graded based on the following: A – Excellent. The work in this part is almost faultless B – Satisfactory. The work has some minor problems. C – Almost Satisfactory. The work has one major fault, but otherwise the work is OK D – Very Poor. The work has multiple major problems. It shows that you have not understood the basic material from the Pass and Credit tasks well enough. The final grades for the Distinction Task will be allocated based on these rules: 1. 2. 3. 4.

If you have any D scores, then you will fail the Distinction Task If you have more than two C scores, then you will fail the Distinction Task If you have 4 or more A scores, then you will get an EXCELLENT for the Distinction Task If you have any other combination, then you will get a SATISFACTORY for the Distinction Task

6...