University : University of Tasmania UniLearnO is not sponsored or endorsed by this college or university.
Subject Code : CSC72001
Country : Australia
Assignment Task -                 
 

A. Overview
In this assignment, you are a database analyst with the task to analyse a scenario, design, develop and test a database in MySQL using phpMyAdmin.
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.

This assignment intends to give experience in database 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 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 using report template 3A. The report should include a title page, the EERD and business rule clarification (if necessary).


Assignment  Part B Develop a Database Prototype (20%)
Build and test a database prototype based on your design in Part A. S


B. Scenario:
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.
CLUB
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 types 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's 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.


B. Assessment Requirements
Part A – EERD 

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, draw.io or similar – NOT Word), draw an Enhanced Entity RelationshipDiagram (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 is a 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. Additionally, your database design needs to show understanding of relationships between entities including super-subtypes relationship. 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 the provided template. 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 (e.g. eyuwonoA3). 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 2 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.

Privileges 1 mark
Provide access to your database for the user eyuwono or another student user account of your choice. Details of how to do this can be seen in the provided screencast.
e. 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. Create at least four views and where appropriate,  these views should incorporate the use of standard practices, informative column headings that fit the expected use of the view.
f. 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 yourusernameA3.sql e.g. eyuwonoA3.sql.
g. Proof of Testing 1 mark
The results of your queries should be presented in a Word document using the provided report template 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
h. 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 
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 clubs members 
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 
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 a number of members trained sorted by the club state, followed by the number of members.
5. Information about club managers 
Create a query that displays a list of managers, with the name, email, phone number, and the club he/she belongs to, sorted by the manager the last name.

6. Trainers specialised in weight loss 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 the last name of trainers.
7. Members statistic
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
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 the time.
 

This CSC72001- IT Assignment has been solved by our IT Experts at UniLearnO. Our Assignment Writing Experts are efficient to provide a fresh solution to this question. We are serving more than 10000+Students in Australia, UK & US by helping them to score HD in their academics. Our Experts are well trained to follow all marking rubrics & referencing style.

Be it a used or new solution, the quality of the work submitted by our assignment Experts remains unhampered. You may continue to expect the same or even better quality with the used and new assignment solution files respectively. There’s one thing to be noticed that you could choose one between the two and acquire an HD either way. You could choose a new assignment solution file to get yourself an exclusive, plagiarism (with free Turnitin file), expert quality assignment or order an old solution file that was considered worthy of the highest distinction.

  • Uploaded By : Roman
  • Posted on : January 27th, 2019
  • Downloads : 211

Whatsapp Tap to ChatGet instant assistance