Question

You are hired to design a database for a fitness center. As the fitness center is expanding with more than one branch, they want to create a database to keep track of its customers, facilities and employees. Each branch has a unique id and address (building number, street, district, and city). A branch may have more than one facility (e.g. swimming pool, spa, etc.). Each facility must belong to only one branch, and the information for a facility is name and fees. In addition, each fitness branch offers different classes (such as Yoga, Pilates, Zumba, etc.). All classes should be led by at most one trainer, who is an employee.

Employees must work at one and only one branch. Each employee has an id, name, address (building number, street, district, and city) and can have multiple phone numbers. An employee can only be an admin, managing the facility or working as a clerk, or a trainer. An employee cannot be a trainer and an admin because the trainer id compensated an hourly wage while an admin staff paid a monthly salary. Trainers can train individual customers at different time sessions.

A customer must register at one branch. Each customer has a membership number, name (stored as first name and last name), email, and only one phone number. A customer can join more than one class or uses any number of facilities. A customer may also decide to be trained by at most one personal trainer.

Given the above description:

1. Draw a Chen's Notation ER Diagram for the database, identifying the following:

a.All the entities, attributes and relationships

b.Primary key and (discriminator in weak entity, if any). Don’t forget each entity has to have PK.

c.Participation and cardinality constraints. (Explain your choices for two constraints- i.e. identify the words the guided your decision)

d.Specialization and completeness constraints (if there is an ISA relationship).

2. Write a schema for two entities and two relationships of your choice. Remember, sometimes a relationship is better not be represented in a separate schema; if this is the case with your chosen relationship explain what you will do.

Answer 1

Lets’ follow the below steps to create ERD and relational database model for given fitness centre:

Step 1: List entities:

1) Branch

2) Facility

3) Customer

4) Staff

5) Admin

6) Trainer

7) Class

Step 2: List relationship among entities:

1) Branch has many facilities, but a facility is available to one branch only. It is 1: M relationship.

2) Branch offers many classes, but a class belongs to one branch only. It is 1:M relationship.

3) Each class led by a trainer and a trainer can lead many classes. It is M:1 relationship.

4) Employee class has below two sub types:

a) Admin

b) Trainer

5) Customer attends many classes and a class is attended by many customers.

6) A customer used none or many facilities; a facility is used by many customers.

Step 3: List attributes to the entities:

1) Branch(branch_id , address –composite attribute (building_number, street, district, city)

2) Facility(facility_name , fee)

3) Customer(membership_no, name –composite attribute (first name and last name), email, phone_number

4) Employee( emp_id, name, address –composite attribute (building_number, street, district, city) ,phone_numbers(multivalued attribute))

5) Admin(monthly_Salary)

6) Trainer(hourly_wage)

7) Class(class_id, date_time, duration_in_mins)

Step 4: Create ERD in crow’s foot notation:

Step 5: ERD Notations:

Step 6: Schema for 2 entities and 2 relationships

Consider the schema for below entities:

Branch, Customer

1) Branch(branch_id , building_number, street, district, city)

2) Customer(membership_no, first name , last name, email, phone_number)

Relationships in schema:

Let’s solve below relationship:

1) Customer to Branch: Each customer is registered at one branch, thus there is not need to create a new schema to store this relationship. A customer schema will store the link to its branch where he is registered:

Customer(membership_no, first name , last name, email, phone_number, branch_id(fk))

2) Class to Customer: A class is attended by many Customers and a Customer attends many classes. It is M:N relationship which can not be stored in existing tables. Thus a new schema will be created as below:

ClassCustomers(Class_id(fk), customer_membership_no(fk))