Reference no: EM132533207

CSC72001 Database Systems Assignment - Southern Cross University, Australia

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.

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 title page, the EERD and business rule clarification (if necessary).

Part B - Develop a Database Prototype

Build and test a database prototype based on your design in Part A.

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 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

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.

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 (eg: rmason 10Ass3).

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 rows in the result set.

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 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

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.

1. Club facilities

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 club 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 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 manager 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 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 time.