Task Description
A. Overview
Your task is to analyse, develop and test a database in MySQL using PhpMyAdmin. You are provided with a scenario (below) and supporting do uments. It is your job to analyse the data requirements provided in the scenario and develop a relational databa e 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 design and development, and it is based on a fictitious scenario.
Your focus will be:
- A full normalised database design, modelled in an EERD, showing business rules;
- Entity (tables) development with correct attributes included;
- Demonstrate supertypes and their associated su
- Demonstrate normalised relations;types;
- Make relational joins to ensure this prototype works;
- 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 tit e page, the EERD and business rule clarification.
Assignment 3 Part B - Develop a Database Prototype
Build and test a database prototype based on your design in Part A.
B. Scenario - North Coast Dragon Boat Association Database Project
Dragon boating is an ancient sport which originated in China thousands of years ago. Dragon boating involves racing with up to 20 paddlers in one boat, a sweep that stands on the back of the boat to steer, and a drummer that sits in the front of the boat. Dragon Boating clubs exist to promote, practice and participate in dragon boat races, and members range from age 12 to paddlers in their 70s.
The North Coast Dragon Boat Association (NCDBA) provides an association for organising the dragon boat clubs on the North Coast of NSW. NCDBA collects membership fees from members and arranges insurance, as well as training and certification for sweeps and coaches, and workshops for drummers. NCDBA regattas are held periodically, and each regatta is the responsibility of a particular club, who organises fund-raising activities, races, refreshments, parking, and anything else required on the day.
The North Coast Dragon Boat Association (NCDBA) requires a database to store the data associated with dragon boat racing on the North Coast, and to improve the reporting of information needed for the organisation. The system of spreadsheets and paper forms that NCDBA is currently using has many data inconsistencies that detract from the Association's belief in the accuracy of the data and information provided. While keeping requirements for a new system closely aligned to the current manual one used by NCDBA, your task is to develop a prototype database, and test it with queries which are likely to be used by the Association. You are not required to write an application to use with this database.
The file CSC00228_2016_S3_Assignment3_SampleForms.docx, provided with this assignment, provides you with some sample forms and information.
Clubs and Members
To start a new club, prospective members must provide the Association with a Club name and address, and have at least four prospective members. The club is assumed to have started on the date that the club applied for registration. The Association requires that each club has (at least) a President, Vice-president, Secretary and Treasurer. All club members, including ordinary members, need to provide personal contact information and details about whether they have qualifications and training as a coach, drummer or sweep. Coaches are required to have a police check for working with children, and must have a Workplace Health and Safety certificate. In addition, coaches are certified by the Association, and must have the date of certification and level of certification (1 to 5) recorded by the Association. Sweeps are also certified at level 1 to 5. An example membership application form is included in the documentation for this assignment.
Members are required to pay dues to their club which are forwarded to the Association, who organises insurance. You do not need to model insurance details in this database. Membership dues are paid yearly, but this may be paid in instalments. Date and amount of payment, as well as membership year is recorded.
Boats and Regattas
Clubs own one or more boats for training and racing. Boats have a capacity or size that will fit either 10 or 20 paddlers, as well as a sweep and a drummer. Regattas (competition race days) are held regularly throughout the year. Each large club assumes responsibility for a particular regatta in their area. A regatta is an opportunity for multiple clubs to compete in races, and for individual paddlers to gain experience in racing. A number of races are held in each heat (including ordinary heats, quarter-finals, semi-finals and finals), with a maximum of four boats in each race. The starting time for the race is advertised, and when the race has been run, the winning club and winning time is recorded for the Association's information. If a member wishes to participate in a race, they fill out the race registration form, pay a small fee, and indicate in which boat they wish to paddle. An example of the race registration form is provided in the documentation for this assignment.
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.
Assessment Requirements
Part A - EERD
a. Database Design
Using the scenario and the data requirements provided, you must analyse the database needs of the client. Following the normalisation process, you will determine the tables and columns needed to provide all functionality required by the client as outlined in the scenario above. Supertypes and subtypes are expected in the design. Your final design choices should allow you to create your relational database in fully normalised form.
b. EERD Drawing
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. Business rules can be included on a separate page.
Your EERD diagram should be presented in a Word document using a basic report layout with a title page. See "Submission Format" for document naming requirements.
Part B - Database Prototype
a. Database Development
Design and build your database in MySQL using PhpMyAdmin. Your database should be created on the Infotech server and be named with your username followed by Ass2 (eg: rmason10Ass3). 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 resulting rows.
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 all database structures, including 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. This report is similar to that required for Assignment 2.
g. SQL Queries:
Important 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. Club membership list
Create a club membership list for one of your clubs. Results should be sorted by family name of the member, and should include all contact details and club position status (eg: President, Vice President, Secretary or Treasurer, or null for ordinary member).
ii. Club members count
Create a query that counts the number of members in each club in the Association. Results should have the club name and the number of members, and be sorted by the number of members.
iii. Owing memberships
The Association would like to identify members that have not paid their full membership fees. Assuming association fees are $100.00 per annum, create a query that shows the club, member name, email address, contact number and amount paid for the year for all members that have not paid their full fees.
iv. Regatta information
Create a query that displays a list of Regattas, with the regatta name, date, place and organising club and contact member details including name and email and phone number, sorted by date.
v. Race information
Create a query that displays the list of races scheduled for one particular Regatta. Races should be sorted by heat and then race number, and display the regatta name, heat, race name and starting time.
vi. Winner list
Create a query to display the list of winners for each Regatta, showing Regatta name, Heat, Race number, Race name, winning club name and winning time, sorted by Regatta name, Heat and Race number.
vii. Particular member race list
Create a query that displays a list of races in 2014 regattas for which Lucas Morse (or use another name from your member data) was registered. Results should be sorted by Regatta name, date and time and provide all details displayed on the race registration form.
viii. Boats used in regattas
Create a query that displays a list of boats which were registered in races in 2014, and calculate how many races each were registered in. Include and sort by club name and then boat name.
ix. Qualification list
Create a query to show a list of all association members who have coach or sweep qualifications. Your query results should be sorted by the club, then the member's last name and first name. You must include the following:
- Club name;
- Member's last name, first name, phone number;
- Level of coach qualifications (if applicable) and date of certification;
- Level of sweep qualifications (if applicable) and date of certification.
x. Refresher Training list
The Association would like to offer refresher training to all coaches who were certified more than 3 years ago. Create a query to show a list of these coaches, with their club name, coach name, contact details and date of last training.
Attachment:- Assignment.pdf