Assignment
Overview
You are to create an ER diagram, and a relational schema for a small business. You need to provide adiscussion of normalisation including the normal form that each entity is in, a list of relationships with all table names, primary and foreign keys, and attributes indicating their type and purpose.
Learning Outcomes Assessed
The following course learning outcomes are assessed by completing this assessment:
- K4. design a relational database for a provided scenario utilising tools and techniques including ER diagrams, relation models and normalisation
- K5.describe relational algebra and its relationship to Structured Query Language (SQL);
- A1.design and implement a relational database using a database management system;
Project Specification
You have been commissioned to create a database for TopFit, acompany that runs a gym in Albury. TopFit offers a variety of fitness, strength and conditioning courses for members at various times. The gym provides top quality teachers and excellent equipment for the training of members.
All of TopFit's coursesconsist of several classes, taught by TopFit staff. Each coursehas a number of classes with a maximum number of participants and some courses have prerequisites (for example, to enrol in "Advanced Cardio-fit", members have to have successfully completed "Introductory Cardio-fit"). Anyone can sign up as a member and enrol in one or more courses at TopFit. Members can pay by different methods, either by monthly membership, or per day (they can attend as many classes as they wish on any given day).
The following business rules and information gathered about the current business activities will allow you to derive entities. Your submission is not expected to have many to many relationships left unresolved. You may add entities or attributes as you see fit. Assumptions can be made toinclude further entities and their relationships, but referential integrity and normalisation processes must be adhered to. Reasons should be given for any relation that is not maintained in 3NF.
- TopFit employs many staff members, keeping track of their unique ID, name, phone number, address and email, as well as tax number, job title and weekly rate of pay.
- Course types run by TopFit have a name, number of classes, a course description and a maximum student number. Some course types have another course type as prerequisite.
- Coursesrun by TopFit from time to time belong to a course type, are assigned to a supervising staff member and have a starting and end date. They also allow staff to records notes about the course.
- Members are recorded by name, address, email, phone number, date of birth and notes about special requirements (dietary or health problems). An emergency contact name and number are also recorded. Additionally, the date of joining up is recorded, and if the member has left, the date of leaving as well. Current members have a joining date but no leaving date.
- If the member makes regular monthly payments, the following details need to be stored: bank name, account type (savings, cheque or credit), account number and account name.
- Members can enrol in one or more of TopFit gym courses, and they can attend as many classes as they wish on any day.
- Members have 3 different methods of paying. All payments recorded by staff include a date paid, amount paid and the member's ID.
Additionally:
o If the payment method is "Automatic Monthly", then success or failure of the direct debit is recorded (if the payment failed, staff will contact the member).
o If the payment method is "Single Day", the payment method is recorded.
o If the payment method is "Multiple Pass", then the number of days on the pass is recorded (this can be 10, 20 or 50), as well as the payment method.
- TopFit tracks every class, recording the id of the staff member who taught the class (this can be any staff member, not just the supervisor for the course), the date and time, the id of the course it belongs to, the roomused and any notes the teacher wishes to record.
- The participation of each member in each class is recorded.
- Members can earn awards for successful completion of courses or for other achievements. TopFit has many award types (for example: "Monthly Hero", "Most improved Beginner" and "Successful completion of Level 3 Strength Training"). For each award type, the gym has an id, name and description of competencies.
- TopFit also maintain a register of all awards earned by members, showing the date awarded, member, award type and special notes. If the award is for a completed course, then the course id is recorded as well, so that a certificate can be issued to the member.
Submission
Your submission should include:
- An ER Diagram with all entity names, attribute names, primary and foreign keys, relationships, cardinality and participation indicated. All many to many relationships should be resolved.
- A discussion of normalisation including the normal form that each entity is in and why that is optimal. Also a discussion of how normalisation was achieved for that entity.
- A list of relationships with all table names, attributes, primary and foreign keys indicated as per the conventions given in the lecture slides (ie entity/table names in capitals, attributes as proper nouns, primary key underlined and foreign keys in italics.)
- A database schema indicating the type and purpose of all attributes.