Assignment 3 CSC72001 - Database Systems Page 1 CSC72001 Database Systems Assessment 3 Assessment 3A – Enhanced Entity Relationship Diagram (10%) 3B – Develop a Database Prototype (20%) Due Date: 3A – 11.30 pm Friday Week 11 3B – 11.30 pm Wednesday Week 13 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. Assignment 3 CSC72001 - Database Systems Page 2 B. Scenario The FIT Club Health Centre is a fitness centre with various facilities and has many branches across Australia. Every branch provides standard facilities of 24 hours gym and a swimming pool, and some additional facilities. A branch is called a club and supervised by a manager. The manager is responsible for the management of club facilities, members, trainers and classes arrangement. CLUBS As of now, there are eight clubs across Australia: two in New South Wales, one in Western Australia, one in Victoria, two in Queensland, one in Tasmania, and a new club in Southern Australia. Every club owns many trainers as class instructors and personal trainers. The facilities for each club may differ depending on the manager preferences. However, the standard facilities for each club should are a 24 hours gym and a swimming pool. Other facilities that are available are cycle studio, kids’ playroom, sauna room, and outdoor training park. MEMBERS FIT Club uses two type of membership system: the first category is all access members who can access all facilities in clubs, the second category is class-only members who are only participating in the club classes. The fee for the all-access member is $15 per week and $5 per week for the class-only member. Members have to choose a home club when registering, but the membership allows them to visit any clubs across Australia. If a member wishes to leave the club, the member data will still be stored although the membership status will be set to inactive. In another case, if a member will be away from Australia for more than a month (e.g. vacation, overseas work), the membership status can be put on hold, and it can be activated again. CLASSES FIT Club has set a standard list of classes provided in all clubs. However, every club may have a different class timetable depending on instructor availability. Class instructors are assigned from club owned trainers. TRAINERS A trainer is employed by the centre and may be assigned to multiple clubs at a time. Trainers will be responsible for being class instructors and offering personal training service. The personal training service is available for any members, although there is an additional charge for the service. The fee varies among trainers, but it is set on a weekly basis. Each trainer has his/her specialisation, ranging from nutrition, weight loss, bodybuilding, to corrective exercise. Your task This health club requires a database to store the data associated with all branches, facilities, members, trainers, classes, and personal training service. The system of spreadsheets and paper forms that FIT Club is currently using has many data inconsistencies that detract from their belief in the accuracy of the data and information provided. Your task is to analyse, design and develop a prototype database, also test it with queries which are likely to be used by the health centre management. You are not required to write an application to use with this database. The file CSC72001_2018_S1_Assignment3_SampleForms.docx provides you with sample forms and information. The file CSC72001_2018_S1_Assignment3_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. Assignment 3 CSC72001 - Database Systems Page 3 C. Assessment Requirements Part A – EERD – due Week 11 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 – due Week 13 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 mark 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 mark 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 mark 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. Assignment 3 CSC72001 - Database Systems Page 4 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 mark Create a query that displays the list of clubs that provide kids playroom as one of their facilities, showing club name, state, club phone number sorted by club state. 2. Members list for a class 0.5 mark Create a list of class-only members for one of the class. Results should be sorted by the last name of the member and should include all contact details. 3. Counting club members 1.5mark Create a query that displays all clubs and the number of members for each of those clubs. Your query should select the club name, state, manager name, number of total members, number of all-access members, number of class-only members sorted by club state. 4. Personal trainers 1 mark FIT Club management would like to identify trainers that have experiences as personal trainers. Create a query that selects these trainers only, showing the club name, trainer name, the number of members trained sorted by the club state, followed by the number of members. 5. Information about club managers 0.5 marks Create a query that displays a list of managers, with the name, email, phone number, and the club he/she belongs to, sorted by manager last name. 6. Trainers specialised in weight loss 1 marks Create a query to display the list of active trainers who are currently active and specialised in weight loss. The query results need to show trainers name, clubs name, and trainers contact details, sorted by last name of trainers. 7. Members statistic 1.5 mark Create a query that displays members statistics for each club showing club name, state, the total number of members, number of active members, number of on hold members, number of inactive members. 8. Club timetable 1.5 mark Create a query that displays the classes timetable for a club. The query result has to display day, time, class name, instructor name sorted by day, followed by time. 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. Assignment 3 CSC72001 - Database Systems Page 5 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.