Reference no: EM132333628

Assignment - Enhanced Entity Relationship Diagram and Develop a Database Prototype

Task Description

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 (below) and supporting documents. 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.

The intent of this assignment is to give you experience in design and development. It is based on a fictitious scenario. 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

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

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.

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_2018_S1_Assignment3_SampleForms.docx, provided with this assignment, provides you with some sample forms and information. The file CSC72001_2018_S1_Assignment3_example_data will provide 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.

a. EERD Drawing

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

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.

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

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

Choice and consistency of table and attribute naming conventions used, selection of data types, primary and foreign keys.

c. Sample Data
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
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
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 eg: rmason10Ass3.sql.

f. Proof of Testing
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.

i. Team Member list
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).

ii. Country swimmer count
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.

iii. Non-qualifying Swimmers
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.

iv. Medical Officer information -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.

v. Race information
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.

vi. Event Winner list
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.

vii. Individual swimmer race results 1 mark 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.

viii. Medal Tally
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.

ix. Qualification list
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.

x. Qualification check-list
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.

Attachment:- Database Systems Assessment.rar