Assessment : 3A – Enhanced Entity Relationship Diagram (10%) 3B – Develop a Database Prototype (20%) Submission : Submit Part A via MySCU Submit Part B report and SQL script via MySCU, submit Database on Infotech server. Weight : 30% of overall unit assessment.

A. Overview

Your task is to analyse a scenario, and design, develop and test a database in MySQL using PhpMyAdmin. You are provided with the scenario and supporting documents. The intent of this assignment is to give you experience in design and development. It is based on a fictitious scenario.

It is your job to analyse the data requirements provided in the scenario and design and develop a relational database to meet the client needs. You will also need to add data and create SQL queries to provide results suitable for reporting. Your focus will be to: Produce a fully normalised database design, modelled in an EERD, showing business rules; Develop entities (tables) with correct attributes included; Demonstrate supertypes and their associated subtypes; Demonstrate normalised relations; Make relational joins to ensure this prototype works; Produce evidence of correct working via database queries and screenshots of result sets.
Assignment 3 Part A – Enhanced Entity Relationship Diagram (10%)
Design and produce an Enhanced Entity Relationship Diagram (EERD) using a modelling or drawing tool. Present your assignment in a Word or PDF document with a title page, the EERD and business rule clarification (if necessary).

Assignment 3 Part B – Develop a Database Prototype (20%)
Build and test a database prototype based on your design in Part A.

B. Scenario – Commonwealth Games Swimming Database Project

The 2018 Commonwealth Games is an international multi-sport event for members of the Commonwealth that will be held on the Gold Coast between 4 and 15 April 2018. The event has been called simply ‘Gold Coast 2018’ and has an abbreviation of GC2018. This will be the 21st Commonwealth Games which is held every four years. Over 6,600 athletes and team officials from 70 nations and territories will converge on the Gold Coast for the 11-day sporting and cultural event. Approximately 15,000 volunteers will help to make the event run smoothly and create a wonderful experience for everybody.

Swimming has always been a major event at every Commonwealth Games with Australia often leading the medal-count in the past. There is no doubt that in 2018, with the ‘home turf’ advantage, Australians will expect our swimmers to bring home gold!

The Gold Coast Aquatic Centre has been fully redeveloped in preparation for GC2018. The Centre has six pools, including a new 50 metre 10-lane competition pool, an 8-lane 50 metre pool, a full suite of diving facilities and a dry dive training pit. The final races will always be held in the 10-lane pool.

All events will be held in one of the two 50 metre pools and athletes will compete in events for the four strokes – freestyle, breaststroke, backstroke and butterfly. The lengths for the races vary with 50m, 100m, 200m, 400m, 800m and 1500m races being held for freestyle, and at least 50, 100 and 200 metre races for other strokes. Most races have both Mens and Womens Division and there are also the relay team events and the medley which brings out the most versatile swimmers.

The Commonwealth Games Federation (CGF) provides a report outlining the requirements for swimmers to qualify to complete in the games. This includes the qualifying times which the swimmers must have equalled or bettered in an official competition event within the 12 months leading up to the games.

Qualifying Swim Teams
For a country to qualify and be included in the swimming events, there is no minimum number of team members, however each swimmer must qualify for their selected event(s). Each country’s team must have a team manager, team leader (typically the strongest swimmer) and at least one coach and one medical officer.
To apply for inclusion in the games, prospective countries must provide the Federation with the country’s swimming team details for consideration. All team members, including swimmers, coaches and medical officers, need to provide personal contact information.

Swimmer Details
The swimmers must provide their contact information, gender, date of birth and the name of their coach. For each race event they wish to compete in, they must provide the details of their qualifying time, the date and approved swimming competition where the time was recorded. The qualifying time must have been recorded in 2017.

Other team members:
All other team members must provide their contact information. Each coach also needs to provide details about whether they have qualifications and training as a coach. Coaches are required to have a police check for working with children (“WWC Check”). In addition, coaches are certified by the Federation, and must have the date of certification and level of certification (1 to 5) recorded by the Federation. Medical officers also need to provide details of their qualifications, including the year of their degree or latest qualification and any specialisations.

Preliminary Heats and Final Race
Several preliminary heats/races will be held to determine who will compete in the final race for each event. The result times for each swimmer completing in each of these races must be recorded. For each event, the final winner of the Gold, Silver and Bronze medals will be recorded.

Your task
GC2018 requires a database to store the data associated with all swimming teams, team members as well as the preliminary heats and final races held at the games. The system of spreadsheets and paper forms that GC2018 is currently using has many data inconsistencies that detract from their belief in the accuracy of the data and information provided. While keeping requirements for a new system closely aligned to the current manual system used by GC2018, your task is to develop a prototype database, and test it with queries which are likely to be used by GC2018. You are not required to write an application to use with this database.

The file CSC72001_S1Assignment3 Sample Forms.docx provides you with sample forms and information. The file CSC72001_S1Assignment3 example data provides you with further details and some specific examples of the current manual system data to get you started. Note that this data is provided “as is” and is not transferable directly to tables in the new database.
The entities and attributes mentioned in the scenario and on the sample forms must be included. You will be required to normalise some of the above into smaller relations/tables prior to implementing your design into a database. You may add more attributes to help improve the design and efficiency of the database.

C. Assessment Requirements

Part A – EERD

a. EERD Drawing 7 marks
Using the scenario and the data requirements provided, you must analyse the data storage needs of the client. Identify all entities and attributes and the relationships between them. Using a drawing software tool (Visio, Lucidchart or similar – NOT Word), draw an Enhanced Entity Relationship Diagram (EERD) of your design using crows foot notation. Your EERD must show all entities, attributes, relationships and cardinality as well as any business rules identified. Supertypes and subtypes are also expected in the design.

b. Database Design 3 marks
Your final design choices should allow you to create your relational database in fully normalised form. Following the normalisation process, you will determine the tables, primary keys, foreign keys and columns needed to provide all functionality required by the client as outlined in the scenario above. Identify your database schema using standard formatting for table names, keys, and foreign keys.
Deliverables : Your EERD diagram should be presented in a Word document using a basic report layout with a title page. The database schema should be included on a separate page. See “Submission Format” for document naming requirements.

Part B – Database Prototype

a. Database Development 3 marks
Build your database in MySQL using PhpMyAdmin. Your database should be created on the Infotech server and be named with your username followed by Ass3 (eg: rmason 10Ass3). Referential integrity and any other constraints must be created, as required by your design.

b. Database Design 3 marks
Choice and consistency of table and attribute naming conventions used, selection of data types, primary and foreign keys.

c. Sample Data 3 marks
You must provide enough valid data in your database to run the SQL queries below successfully with at least 5-10 rows in the result set.

d. Use of Views 1 marks
The database should be capable of producing the query results below. To get the required results, you may need to build underlying queries or views. Where appropriate, these views should incorporate the use of standard practical informative column headings that fit the expected use of the view.

e. Export Script 1 marks
Once your database is finalised, you must create an export script (.sql) to create a backup of your database, including all database structures, table definitions and data. Name this SQL script as yourusernameAss3.sql, for example rmason10Ass3.sql.

f. Proof of Testing 1 marks
The results of your queries should be presented in a Word document using a basic report layout with a title page, page numbers etc. The query that you create for each of the parts below should be pasted into your report (not screenshots), followed by a screenshot of your results. Snipping Tool on Windows is useful for taking screenshots of query results.

g. SQL Queries:
Note : your result sets should not display any ‘extra’ columns – such as surrogate keys. Use surrogate keys in your progressive testing by all means, but your finished test results should not include surrogate keys unless specified in the question.

1. Club facilities 0.5 marks
Create a team list for one of the countries. Results should be sorted by last name of the team member, and should include all contact details and team member type (e.g. Swimmer, Coach, Medical Officer or other contact type).
2. Country swimmer count 0.5 marks
Create a query that counts the number of swimmers for each country. Results should have the country name, main contact name, the number of swimmers, and be sorted by the country name.
3. Non-qualifying Swimmers 1 marks
The Federation would like to identify swimmers that have not qualified for the events they have registered in. Create a query that selects these swimmers only, showing the country, swimmer name, email address, contact number along with the Event Name, minimum qualifying time, the swimmer’s qualifying time and the difference between the times.
4. Medical Officer information 0.5 marks
Create a query that displays a list of medical officers, with the name, email and phone number, country and any specialisations, sorted by specialisation then last name.
5. Race information 0.5 marks
Create a query that displays the list of races scheduled for one particular event. Races should be sorted by heat number, and display the event name, race name, starting time and location.
6. Event Winner list 0.5 marks
Create a query to display the list of winners for each event, showing event name, swimmer name and recorded times for the Gold, Silver and Bronze winners, sorted by event name and by winning place.
7. Individual swimmer race results 1 marks
Create a query that displays a list of events for which Rebekah Riley (or use another name from your swimmer data) was registered and the races that they swam in. Results should be sorted by event name, race name, date and time and provide all recorded swim times, places and whether they won any medals.
8. Medal Tally 1 mark
Create a query that displays each country with three calculated columns showing total number of swimmers and how many Gold, Silver and Bronze medals were won. Sort by number of Gold medals won.
9. Qualification list 1.5 mark
Create a query to show a list of all team members who have coach or medical officer qualifications. Your query results should be sorted by the country, then the team member’s last name and first name. You must include the following : Country name; Team member’s last name, first name, phone number; Level of coach qualifications (if applicable) and date of certification; Medical Officer qualifications (if applicable), date of qualification, medical specialisation and insurance details.
10. Qualification check-list 1 mark
The Federation would like to confirm that all coaches were certified no more than 3 years ago. Create a query to show a list of these coaches, with their country, coach name, contact details and date of last training.

Further Information

You can find out more about the 2014 Commonwealth Games held in Glasgow on the Wikipedia page below. This will also give past results, the list of countries and team sizes. Swimming at the 2014 Commonwealth Games

Software
You must use Microsoft Visio, Lucidchart or similar modelling/design tool to create your EERD in Part A. There are a number of open-source/free tools such as Lucidchart and DIA available which are acceptable.
You are required to use phpMyAdmin on the Infotech server to create your MySQL database in Part B.

Marking Criteria
Marking criteria will be made available via a rubric on the MySCU website.

Submission Format
You will be required to submit your assignment document via the MySCU site for this unit. Your assignment submission should be in the form of a Word document and a SQL script text file. Your Word documents should be named as username_Ass3A.doc (or .docx) and username_Ass3B.doc (or .docx). Your SQL script should be named as usernameAss3.sql

Original Work
This assignment must be your own original work. Assignments form a major part of course work. Exchange of ideas with other people can be considered educationally valuable; however, excessive collaboration will be regarded as plagiarism, which is a University offence. For example, the copying of significant parts of a document (or database), even if subsequently modified, is plagiarism. Such academic dishonesty will be penalised in accordance with the University's rules and regulations.
You must not copy material from books, magazines, internet sources or other students’ assignments. Of course, you may include direct quotes from any source, but these must be small (e.g. one sentence or one paragraph) and must be properly referenced, using the Harvard Referencing Style. The value and relevance of including materials from another source must be fully explained. If plagiarism is found in your assignment, you may receive zero marks for this assignment.
The assessment process may require some students to attend an interview in order to explain aspects of their assignment.

Retain duplicate copy
You are strongly advised to retain a copy of your original work and progressive versions of your work during the Session. In the event of any uncertainty regarding assessment items, you may be requested to reproduce a final copy and/or any previous versions of your work.

Penalty for late submission
A penalty of 5% per calendar day will be applied to all late assignments. An extension of time will only be considered (not necessarily approved) when a written request is submitted with appropriate supporting documentation and in accordance with University guidelines.

Marks and Feedback
Marks and feedback comments will be returned through your MySCU site for this unit.

To Download Click Here > CSC72001 S1 Assessment3 Southern Cross Business School.pdf