High Distinction Task

Overview

You’ve now been given exposure to quite a few tools in the database analysis and design toolkit. In this task, you’re going to combine these tools – and your creativity and expertize – to deliver a complete database analysis, design, and piloting activity (i.e. narrative, ERDS, DB solution, queries, reports and visualizations).
We give you the task description early in semester to enable you to get thinking about the task, however, you’ll probably find it easier to work on the task when we’ve covered a few more lectures on advanced SQL, ERDs, and business narratives.
It’s important to note that this task is setup for those who feel they’ve really mastered the content in the unit. It demands you go beyond simple application of concepts and tools. It is not compulsory.
Only attempt this task if you really feel you can produce a high quality solution. A low quality submission that does not adequately meet requirements or has many shortcomings will not will not be eligible for a HD grade.
Remember, for D and HD tasks there are no resubmissions! One submission, one grade.

Getting Started

As per our other tasks, you’ll be mainly submitting screenshots via Doubtfire
Create a file named HDTASK.PDF.This is a PDF of the file named HDTASK.DOCX described below.
This file will contain screen shots described in this document.
When you’re ready to submit, log into Doubtfire and submit this file into the appropriate HDTASK task in Doubtfire.

Grading

Each part of this document has a number of marks associated with it.
You will be awarded marks for how successfully you complete each part.
You must attempt all of the sections described below.
A score of 13-16 will score a satisfactory
A score of 17+ will score an excellent
Any attempt that does not attempt all sections or does not score at least 13/20 will not be eligible for a HD grade.

Task Descriptions

These tasks are centred around a problem that you identify, describe and implement as a database solution.

Task 1 – Business Narrative (worth up to 4 of 20 marks)

Describe the problem that you want to solve and describe how you believe that a relational database is an appropriate solution. This must be in the form of a business narrative.
- Describe how the problem is being dealt with currently.
- Describe the data that is involved.
- Describe why it is useful for the organisation to use a database rather than some other application
- Describe the business rules that apply to the data you want to store.
- Describe the types of queries, reports and/or visualisations that you believe will provide useful outcomes.
See Appendix 1 for further discussion about the type of problem you should tackle.
Note: It is recommended that you discuss the problem with your tutor to ensure that it is an appropriate problem. Many times students select a problem that is too simple, far too complicated, or is simply not suited to a relational database solution.

Task 2 – Entity Relationship Diagram(worth up to 4 of 20 marks)

Draw an ERD that match the requirements of the business narrative that you described.
Generate a relational schema based on the ERD.
Do not include entities or attributes that have not been described in the narrative.
Note:Again, it is recommended that you discuss the ERD you create with yourtutor. Your tutor can discuss with you whether you have a viable solution or not. You tutor may see some shortcomings within your solution that you may have not noticed.

Task 3 – Database Solution(worth up to 4 marks out of 20)

Build a database solution in MS Access or an SQL script that can be run on the feenixOracle server (or other server with agreement from your tutor).
All tables must have a 9999 suffix (last 4 digits of your student id)

Task 4 – Test Data(worth up to 5 marks out of 20)

Populate the tables with sample data.
One of the test entries for at least one of the entities (e.g. person related, product related, …or whatever your case is based upon) must include a reference to your name and/or studentID
There must be sufficient data for queries, reports and/or visualizations to have meaning for your tutor.
Create simple queries that list all of the data in each of the tables.

Task 5 – Queries, Reports, Visualisations(worth up to 5 marks out of 20)

Create a series of complex queries and visualizations that meet some requirements as listed in section 1 above. You must have at least 8 reports and visualizations. There must be a mixture of each. A complex query typically involves using totals and/or multiple tables and/or subqueries and/or outer joins etc.
Note:You may use Power BI to generate your visualizations, although, this is not essential, especially if you’re a Mac OS user (Easy to do from Access. It will require a bit more work if you are using SQL statements. Note: There is no simple method of transferring data from the iSQL tables that you create on the Swinburne Server to Power BI. See Appendix 2)

Preparing for submission…

Copy and Paste the business narrative and business rules you have deduced to the file named HDTASK.DOCX
Copy and Paste a screenshot your ERD into the file named HDTASK.DOCX
Copy and Paste your Relational Schema into the file named HDTASK.DOCX
Copy and Paste your Relational Diagram (MS Access) or Create Table statements (Oracle) into the file named HDTASK.DOCX.
Copy and Paste the result set of the simple Queries which list the data in all tables. Make sure each list is clearly labelled. Place this into the file named HDTASK.DOCX.
Copy and Paste the Query Design Grid (MS Access) or SQL statements (Oracle) into the file named HDTASK.DOCX
- Make sure that all formulae used to generate results are clearly shown.
- Copy and Paste a screenshot of the result set of each of the queries into the file named HDTASK.DOCX
When completed, export the file to a .pdf document named HDTASK.PDF and upload to Doubtfire.

Appendix 1 – Selecting the case

A reasonable problem is one that is going to contain a minimum of 7 entities.
- An example might be your local sporting club.
- The club wants to keep various current and historical data. E.g. a list of current players, list of past players, list of officials, list of players in this week’s team, a list of results against other teams.
- You might want to record that Emma Brown was a female player and played every year from 2009 to 2015.
- In 2014 and 2015 Emma Brown was club president. She is now non-playing member of the club.
- You might want to record that the senior team finished in 2^nd place in 2016 with 11 wins and 5 losses. The under 16 team finished in 8^th place in both 2015 (4 wins/ 12 losses) and 2016 (6 wins / 10 losses).
- The club may have various membership fees for players. You may want to record all payments made by players and a report of all players who currently owe money to the club.
- You may want to create a chart that displays the final position of each team over the past 25 years.
The ERD solution must have at least two intersection-type entities. (E.g. Casting in Movie-Casting-Actor, Loan in Patron-Loan-LibraryBook, Purchase in Customer-Purchase-Product). Try not to be over ambitious.
We know that some students will want to create a database that meets every requirement for a small business or group that they are involved in. This may involve 20 or 30 entities. This would be a mistake. We would suggest that you simply concentrate on a small segment of the organisation and create a solution for that. (Similar to the distinction task that has 2 distinct segments: The tour bookings segment and the product purchases segment.)
Remember:It is recommended that you discuss the problem with yourtutor to ensure that it is an appropriate problem.

Appendix 2 –Preparing data from iSQLJr for PowerBI

If your database solution will be built using SQL statements and you want to use visualizations for a table(s) then you could use one of these options:

This option is simple but a bit fiddly.

Once your iSQLJr database has been created and populated with data, simply execute queries that list all rows in each table.
In iSQLJr, copy each result set including the column headings.
Then Paste this data into a notepad file. Save the notepad file e.g. movie.txt
In Power BI, use the Get Data / Text file option. The data will be imported as the movie table.
Repeat this for every SQL iSQLJr table that you have.
Finally use Power BI’s relationship manger to create relationships between the tables.

It took your convenor under 10 minutes to import all 5 movie database tables into Power BI using this method.

This option is not for the faint-hearted. It suggests that you do not create an ISQL database at all.

Instead you Create a Microsoft Azure account via the DreamSpark programhttps://catalog.imagine.microsoft.com/en-us/Catalog/Product/99
Once done, you can create a MySQL database or SQLServer database. Choose options that do not involve you having to pay a fee!
Now create and populate the database tables.
Once completed, you could connect to it via Power BI.
Warning: Tutors & Convenors are not in a position to give much assistance using MS Azure or MySQL or SQLServer. This option is for those students who are familiar with working database servers other than Oracle via iSQL.