Great Deal! Get Instant $10 FREE in Account on First Order + 10% Cashback on Every Order Order Now

Assignment 3 CSC XXXXXXXXXXDatabase Systems Page 1 CSC72001 Database Systems Assessment 3 Assessment 3A – Enhanced Entity Relationship Diagram (10%) 3B – Develop a Database Prototype (20%) Due Date:...

1 answer below »
Assignment 3 CSC XXXXXXXXXXDatabase 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 co
ect attributes included;
• Demonstrate supertypes and their associated subtypes;
• Demonstrate normalised relations;
• Make relational joins to ensure this prototype works;
• Produce evidence of co
ect 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 CSC XXXXXXXXXXDatabase Systems Page 2
B. Scenario
The FIT Club Health Centre is a fitness centre with various facilities and has many
anches
across Australia. Every
anch provides standard facilities of 24 hours gym and a swimming pool,
and some additional facilities. A
anch is called a club and supervised by a manager. The
manager is responsible for the management of club facilities, members, trainers and classes
a
angement.
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
e 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 co
ective exercise.
Your task
This health club requires a database to store the data associated with all
anches, facilities,
members, trainers, classes, and personal training service. The system of spreadsheets and paper
forms that FIT Club is cu
ently 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 cu
ent 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
e 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 CSC XXXXXXXXXXDatabase 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.
. 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.
. 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
esults, 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
elow should be pasted into your report (not screenshots), followed by a screenshot of your
esults. Snipping Tool on Windows is useful for taking screenshots of query results.
Assignment 3 CSC XXXXXXXXXXDatabase Systems Page 4
g. SQL Queries:
Note: your result sets should not display any ‘extra’ columns – such as su
ogate keys. Use
su
ogate keys in your progressive testing by all means, but your finished test results should
not include su
ogate 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
Answered Same Day May 29, 2021 CSC72001 Southern Cross University

Solution

Neha answered on May 31 2021
142 Votes
59248/csc72001assessment3
eporttemplate4-ofskedjg.docx
        SOUTHERN CROSS UNIVERSITY
ASSIGNMENT COVER SHEET
For use with online submission of assignments
Please complete all of the following details and then make this sheet the first page of each file of your assignment – do not send it as a separate document.
Your assignments must be submitted as either Word documents, text documents with .rtf extension or as .pdf documents. If you wish to submit in any other file format please discuss this with your lecturer well before the assignment submission date.    
        Student Name:
        
        Student ID No.:
        
        Unit Name:
        Database Systems
        Unit Code:
        CSC72001
        Tutor’s name:
        
        Assignment No.:
        Assessment 3
        Assignment Title:
        
        Due date:
        
        Date submitted:
        
Declaration:
I have read and understand the Rules Relating to Awards (Rule 3 Section 18 – Academic Misconduct Including Plagiarism) as contained in the SCU Policy Li
ary. I understand the penalties that apply for plagiarism and agree to be bound by these rules. The work I am submitting electronically is entirely my own work.
        Signed:
(please type your name)
        
        Date:
        
ASSESSMENT 3
REPORT
BY Table of contents:
Contents
Table of contents:    3
Entity Relationship Diagram    4
Part B.b : Database Design    5
1.    Client Business Rules    5
2.    Assumptions Made    5
3.    Naming Conventions    5
4.    Data Types Chosen    5
5. Tables, attributes, and data type choices    5
Part B.g : SQL Queries:    6
Query 1    Club facilities    6
Query 2    Members list for a class    6
Query 3    Counting club members    6
Query 4    Personal trainers    6
Query 5    Information about club managers    6
Query 6    Trainers specialised in weight loss    6
Query 7    Members statistic    6
Query 8 Club timetable    6
Remove this text before submission of your assessment
After you have completed your assessment, right-click on the table of contents above and click “Update Field” then “Update Page numbers only”. This will build the table of contents for you.
Enhanced Entity Relationship Diagram
Insert your EERD here
Part B.b: Database Design
1. Client Business Rules
· Each member can fill a one or more application forms.
· The member needs to enter the location but then he can use any gym.
· Every gym can have one or more facilities.
· The employee can be a trainer or manager.
· The instructor for a class can be personal trainer.
· The member can enrol for a single type of membership.
2. Assumptions Made
· The members in gym provide accurate information and their status is updated.
· The members in the gym provides medical history.
· The trainers may have one or more specialisation.
· One gym has only one manager.
· Two same class type can have same day.
3. Naming Conventions
· The primary key of each table contains Id in its name.
· The foreign key has the same name as of the referenced key.
· The prefix in the column name are similar to the table name.
· All the constraints are given descriptive names.
· Camel case is used for the table and column names.
4. Data Types Chosen
For this database I have used multiple data types.
· Every primary key has int type.
· Int is used for the numbers.
· Varchar is used for the text type input like name, email, address, status and others.
· Date is used for the fields like employment date, date of registration and date of birth.
· Time data type is used for the class time field.
5. Tables, attributes, and data type choices
For each of the tables in your database, you will need to fill out one of the following tables. Copy and paste the following table in for each of your database tables. Then, determine the best data type for each column, whether it can be NULL, default value if necessary, and whether it is a primary or foreign key or indexed.
        Branch
        Attribute
        Data Type, Size
        Primary Key
        Foreign Key
        Index Type?
        Default?
        NULL?
        BranchId
        Int (11)
        Yes
        
        Clustering
        1
        No
        City
        Varchar(40)
        
        
        
        
        No
        State
        Varchar(40)
        
        
        
        
        No
        PostCode
        Varchar(40)
        
        
        
        
        No
        Address
        Varchar(40)
        
        
        
        
        No
        Name
        Varchar(40)
        
        
        
        
        No
        PhoneNo
        Varchar(40)
        
        
        
        
        No
        ClassType
        Attribute
        Data Type, Size
        Primary Key
        Foreign Key
        Index Type?
        Default?
        NULL?
        ClassId
        Int (11)
        Yes
        
        Clustering
        1
        No
        Description
        Varchar(40)
        
        
        
        
        No
        Class_TimeTabe
        Attribute
        Data Type, Size
        Primary Key
        Foreign Key
        Index Type?
        Default?
        NULL?
        EmployeeId
        Int (11)
        
        Yes
        
        1
        No
        ClassTypeId
        Int (11)
        
        Yes
        
        1
        No
        ClassTime
        Time
        
        
        
        
        
        ClassDay
        Varchar(40)
        
        
        
        
        
        Branch
        Attribute
        Data Type, Size
        Primary Key
        Foreign Key
        Index Type?
        Default?
        NULL?
        BranchId
        Int
        Yes
        
        Clustering
        1
        No
        City
        Varchar(40)
        
        
        
        
        No
        State
        Varchar(40)
        
        
        
        
        No
        PostCode
        Varchar(40)
        
        
        
        
        No
        Address
        Varchar(40)
        
        
        
        
        No
        Name
        Varchar(40)
        
        
        
        
        No
        PhoneNo
        Varchar(40)
        
        
        
        
        No
        Employee
        Attribute
        Data Type, Size
        Primary Key
        Foreign Key
        Index Type?
        Default?
        NULL?
        EmployeeId
        Int (11)
        Yes
        
        Clustering
        1
        No
        Name
        Varchar(40)
        
        
        
        
        No
        BranchId
        Int (11)
        
        Yes
        
        
        No
        EmailAddress
        Varchar(40)
        
        
        
        
        No
        Phone
        Varchar(40)
        
        
        
        
        No
        DOB
        Date
        
        
        
        
        No
        Address
        Varchar(40)
        
        
        
        
        No
        City
        Varchar(40)
        
        
        
        
        No
        State
        Varchar(40)
        
        
        
        
        No
        PostCode
        Int (11)
        
        
        
        
        No
        DateOfEmployment
        Date
        
        
        
        
        No
        Status
        Varchar(40)
        
        
        
        
        No
        IsManage
        TinyInt (4)
        
        
        
        
        No
        IsTraine
        TinyInt (4)
        
        
        
        
        No
        Facility
        Attribute
        Data Type, Size
        Primary Key
        Foreign Key
        Index Type?
        Default?
        NULL?
        FacilityId
        Int (11)
        Yes
        
        Clustering
        1
        No
        Description
        Varchar(40)
        
        
        
        
        No
        FacilityList
        Attribute
        Data Type, Size
        Primary Key
        Foreign Key
        Index Type?
        Default?
        NULL?
        Id
        Int (11)
        Yes
        
        Clustering
        1
        No
        BranchId
        Int (11)
        
        Yes
        
        
        No
        Facility Id
        Int (11)
        
        Yes
        
        
        No
        Manage
        Attribute
        Data Type, Size
        Primary Key
        Foreign Key
        Index Type?
        Default?
        NULL?
        EmployeeId
        Int (11)
        
        Yes
        Clustering
        1
        No
        AlternatePhoneNo
        Varchar(40)
        
        
        
        
        No
        Membership
        Attribute
        Data Type, Size
        Primary Key
        Foreign Key
        Index Type?
        Default?
        NULL?
        MembershipId
        Int (11)
        Yes
        
        Clustering
        1
        No
        Cost
        Int (11)
        
        
        
        
        No
        Description
        Varchar(40)
        
        
        
        
        No
        Members
        Attribute
        Data Type, Size
        Primary Key
        Foreign Key
        Index Type?
        Default?
        NULL?
        MemberId
        Int (11)
        Yes
        
        Clustering
        1
        No
        Name
        Varchar(40)
        
        
        
        
        No
        BranchId
        Int...
SOLUTION.PDF

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here