Improved Entity Relationship Diagram (3A) (10%) Create a database prototype in 3B (20%).

Summary of the Work
A. General
You must use PhpMyAdmin to analyze, create, and test a MySQL database. A scenario and associated documentation are given to you. Your task is to evaluate the data demands specified in the scenario and create a relational database to satisfy the client’s expectations. To get results acceptable for reporting, you will also need to upload data and write SQL queries.
This assignment, which is based on a hypothetical scenario, aims to offer you experience in design and development. The following will be your main areas of focus: • A full normalized database design, modeled in an EERD, demonstrating business rules; • Entity (tables) development with correct attributes included; • Demonstrate supertypes and their associated subtypes; • Demonstrate normalized relations; • Make relational joins to ensure this prototype works; • Evidence of correct working via database queries and screenshots of result sets.
Enhanced Entity Relationship Diagram for Assignment 3, Part A (10%)
Using a modeling or drawing tool, create an Enhanced Entity Relationship Diagram (EERD). Create a Word or PDF document for your assignment, adding a title page, the EERD, and a business rule clarification.
Create a database prototype as part of Assignment 3, Part B (20%).
Assignment 3 2016-S 3 CSC00228 Database Systems 1 B. Scenario: North Coast Dragon Boat Association Database Create and test a database prototype based on your design from Part A. Project
Dragon boat racing is a traditional sport that dates back thousands of years to China. Up to 20 paddlers can race in a single boat during a dragon boat race, and the boat is steered by a sweep who stands on the back and a drummer who sits in the front. Clubs for dragon boat racing exist, and their members range in age from 12-year-old paddlers to those in their 70s.

On NSW’s North Coast, the dragon boat clubs are organized by the North Coast Dragon Boat Association (NCDBA). The NCDBA collects membership dues from participants, organizes insurance, coaches’ and sweeps’ training and certification, workshops for drummers, and insurance. Periodically, the NCDBA holds regattas. For each regatta, a certain club is in charge of planning the races, fundraising events, refreshments, parking, and any other needs for the day.
A database is required by the North Coast Dragon Boat Association (NCDBA) to hold information related to dragon boat racing on the North Coast and to enhance the reporting of information required for the organization. Numerous data anomalies in the spreadsheet and paper form systems that NCDBA currently employs cast doubt on the association’s confidence in the accuracy of the data and information given. Your objective is to create a prototype database and test it with queries that are likely to be used by the association while keeping needs for a new system roughly aligned to the manual one used by NCDBA currently. To use this database, you are not required to create an application.
You can find some sample forms and information in the document CSC00228_2016_S3_Assignment3_SampleForms.docx that is included with this assignment.
Members and clubs
A new club must have at least four prospective members and submit the name and address of the club to the association in order to get started. On the day the club submitted its registration application, it was regarded as having officially launched. Each club must have a president, vice president, secretary, and treasurer according to the association’s rules. Every club member, even regular members, must supply their personal contact information as well as information about their education and experience as an instructor, drummer, or sweep. For dealing with minors, coaches must have a police check as well as a workplace health and safety certificate. Additionally, the Association certifies instructors, and the Association must record the date of certification as well as the degree of certification (from 1 to 5). Also recognized at levels 1 through five are sweeps. The assignment’s instructions include an example membership application form.
In order to participate, members must pay dues to their club, which are then sent to the association, which arranges insurance. This database does not require you to model insurance information. Annual membership dues can be paid in installments and are payable in advance. The payment’s date, amount, and membership year are all noted.
Boats and competitions
Clubs use a boat or several boats for practice and competition. A sweep and a drummer can all fit in a boat that can accommodate either 10 or 20 paddlers. All throughout the year, regattas (competitive race days) are often held. Each sizable club takes charge of a specific regatta in their region. A regatta offers a chance for different clubs to participate in races and for paddlers to develop their racing skills. Each heat consists of a number of races, with a maximum of four boats per race (including preliminary races, quarterfinal races, semifinal races, and final races). The race’s start time is announced, and after it has concluded, the winning club and winning time are recorded for the association’s records. When a member signs up for a race, they choose the boat they want to paddle in, pay a nominal entry fee, and complete the race registration form. The materials for this task include a sample race registration form.
It is necessary to include the entities and attributes indicated in the scenario and on the example forms. Prior to putting your design into a database, you will need to normalize some of the aforementioned into smaller relations or tables. To enhance the database’s functionality and design, you can add new properties.
Requirements for Assessment
Due: Part A of the EERD Week 11 a. 5 points for database design
You must evaluate the client’s database requirements using the scenario and the given data requirements. After the normalization process, you will decide which tables and columns are required to give the customer the functionality they need, as seen in the case above. In the design, supertypes and subtypes are anticipated. Your relational database should be able to be created in its completely normalized form as a result of your final design decisions.
b. 5 marks for the EERD drawing
Draw an Enhanced Entity Relationship Diagram (EERD) of your design using crowsfoot notation in a drawing software program (Visio, Lucidchart, or a comparable program; DO NOT USE WORD FOR THIS). All entities, characteristics, relationships, and cardinality must be displayed in your EERD. A separate page can be used to add business regulations.
Your EERD diagram should be displayed in a Word document with a title page and a simple report structure. For information on document naming standards, see “Submission Format”.
A database prototype for Part B is due. Database Development: 3 marks for Week 13
Create and construct your MySQL database with PhpMyAdmin. Your database needs to be created on the Infotech server with the name rmason10Ass3 and should have your username followed by Ass2. Referential integrity and other limitations must be established in accordance with your design specifications.
b. Database Planning: 3 points
Selection of data types, primary and foreign keys, and uniformity of naming conventions for tables and attributes
Sample Data: 3 points
Your database must contain at least 5–10 rows of valid data in order for the SQL queries below to execute successfully.
Utilizing the Views 1 mark
The database should be able to deliver the results of the following queries: You might need to create underlying queries or views in order to obtain the appropriate results. Where appropriate, these views should employ common, useful, and informative column titles that are appropriate for the view’s intended purpose.
Mark the Export Script 1 file.
Produce an export script (.sql) to produce a backup of all database structures, including table definitions and data, after your database is complete. f. Proof of Testing: 1 mark Name this SQL script yourusernameAss3.sql, for example: rmason10Ass3.sql.
Your queries’ results should be presented in a Word document with a title page, page numbers, and other standard report formatting. You must paste your own custom queries into your report for each of the sections below (not screenshots), then take a picture of your findings. Using Windows’ Snipping Tool, you can screenshot search results. The report needed for Assignment 2 is comparable to this one. g. SQL Queries:
Important Information: Surrogate keys and any ‘additional’ columns shouldn’t be displayed in your result sets. Use surrogate keys when conducting progressive testing, but don’t include them in your final test results unless specifically asked for.
List of club members, 0.5 marks
Make a list of the club members for one of your clubs. The member’s family name should be used to sort the results, which should also include all contact information and the member’s club position (e.g., president, vice president, secretary, or treasurer, or null for an ordinary member).
ii. Club members are taken into account for the 0.5 mark.
Make a query that counts the number of association clubs’ members in each club. The club name and the total number of members should be included in the results, which should then be sorted by membership.
Owing memberships (iii) 1 mark
The association wants to identify any members who have not paid the entire amount due for membership. Create a query that lists the club, member name, email address, contact number, and amount paid for the year for all members who have not paid their full dues, assuming association dues are $100 per year.
iv. Regatta details: 0.5 points
Create a query that shows a list of regattas, sorted by date, along with the name, date, and location of each regatta, as well as the coordinating club and each contact member’s name, email, and phone number.
v. Information about race: 0.5 marks
Make a query that lists all of the races scheduled for a specific regatta. The regatta name, heat, race name, and starting time should be displayed, and races should be sorted by heat and then race number.
vi. Winners list, 0.5 points
Make a query to show the winners for each regatta, sorted by regatta name, heat, and race number, along with the winning club name, winning time, and race number.
vii. Specific member race list: 1 point
Make a query that lists all of the 2014 regattas’ races where Lucas Morse (or another name from your member database) was listed as a competitor. Results must include all information listed on the race registration form and be arranged according to the name, date, and time of the regatta.
Boats used in regattas, item viii 1 mark
Make a query that lists all the boats that entered races in 2014 and calculates the number of races that each boat entered. Include the club name and then sort by boat name.
Qualification list: ix. 1.5 points
Make a query to display a list of all association members who meet the requirements to coach or sweep. The club should come first in the results of your search, followed by the member’s last and first names. You need to remember to include:
The club name, the member’s last name, first name, and phone number, as well as the level of coaching qualifications (if applicable) and the date of certification, are all listed.
List of Refresher Training 1 mark
All coaches who received their certification more than three years ago are eligible for refresher training, according to the Association. Create a query to display a list of these coaches along with information about their club, coach, contact information, and most recent training date.
Software
To develop your EERD in Part A, you must use Microsoft Visio, Lucidchart, or another comparable modeling and design program. There are several open-source and free programs out there that are suitable, like Lucidchart and DIA. Your MySQL database in Part B must be created using phpMyAdmin on the Infotech server.
Marking Standards
A rubric with the scoring standards will be posted on the MySCU website.
Relevant Links
The links below can help you come up with ideas for club names, individual data, and boat names:
http://www.boatnames.com.au/boat-name-ideas.htm http://www.dbnsw.org.au/
Best+Team+Names: http://www.network54.com/Forum/135730/thread/1228571283/last-1370899492
Submission Style
For this unit, you must submit your assignment document through the MySCU website. You must turn in your assignment as a Word document plus a text file with a SQL script. Username_Ass3A.doc (or.docx) and Username_Ass3B.doc (or.docx) are the proper filenames for your Word documents. The name of your SQL script should be usernameAss3.sql.
Original Content
Your original work must be used for this assignment. An important component of course work is assignments. The sharing of ideas with others can be viewed as educationally beneficial, but too much collaboration will be seen as plagiarism, which is a crime at the university. For instance, plagiarism occurs when major portions of a document (or database) are copied and then modified. This academic dishonesty will be punished in accordance with the policies and procedures of the university.
You must not use text from books, publications, websites, or the assignments of other students. Direct quotes from any source are welcome, but they must be brief (one sentence or one paragraph) and correctly cited using the Harvard referencing style. Included resources from another source must be fully justified in terms of their value and relevance. You can get zero points for this project if plagiarism is discovered in it.
Some students may be required to participate in an interview as part of the assessment process to discuss the specifics of their project.
Keep a backup copy.
It is highly recommended that you keep a copy of both your initial work and any further revisions while in the session. You could be asked to recreate a final copy and/or any earlier versions of your work if there is any doubt about the evaluation items.
penalty for submitting late
All late assignments will be assessed a 5% fine each calendar day. Only when a written request is presented with the proper supporting evidence and in compliance with university policies will an extension of time be considered (not necessarily approved).
Marks and comments
Through your MySCU account, you will receive feedback and grades for this unit.