Assignment 4A – Enhanced Entity Relationship Diagram (20%)
4B – Develop a Database Prototype, Test and Evaluate (25%)
Both 4A and 4B should be submitted in one report.
Due Date: 4A – 14 Dec 2020 11:00 PM
4B – 14 Dec 2020 11:00 PM
Submission: Submit Part A via MySCU
Submit Part B report and SQL script via MySCU. Submit Database on Infotech server.
Weight: 45% of overall unit assessment.
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 (on MySCU). 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 correctly 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 4 Part A – Enhanced Entity Relationship Diagram (20%)
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 4 Part B – Develop a Database Prototype, Test and Evaluate (25%) Build and test a database prototype based on your design in Part A.
B. Scenario – North Coast Dragon Boat Association
This scenario extends the case study completed in ISY91015 Information Systems Analysis and Design (available on MySCU). You may wish to read this to help your understanding.
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 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 ISY92115_2020_SP6_Assignment4_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. EERD Drawing 14 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 6 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.
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 6 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 _DMA4 (eg:
sthudumu10_DMA4). Referential integrity and any other constraints must be created, as required by your design.
b. Database Design 6 marks
Choice and consistency of table and attribute naming conventions used, selection of data types, primary and foreign keys.
c. Sample Data 6 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 2 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 2 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 eg: sthudumu10Ass3.sql.
f. Proof of Testing 2 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.
i. Club membership list 1 mark
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 1 mark
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 2 marks
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 1 mark
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 1 mark
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 1 mark
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 2 marks
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 2 marks
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 3 marks
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 check-list 2 mark
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.
Useful Links
The following links may be useful to generate ideas for boat names, people data, and club names:
http://www.boatnames.com.au/boat-name-ideas.htm http://www.dbnsw.org.au/
http://www.network54.com/Forum/135730/thread/1228571283/last-
1370899492/Best+Team+Names—–
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_A4.doc (or .docx) and username_A4B.doc (or .docx). Your SQL script should be named as username_DMA4.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.