ITDA1001 Database Fundamentals

Entities and their attributes

Member: It stores details of the members of Fit-life Fitness Centre.

• memberID: unique identification number for the member
• firstName: first name of the member
• lastName: last name of the member
• membershipType: type of the membership held by the member. It is a foreign key establishing a relationship with a membership level entity.

Class: it represents a class offered by the fitness centre to its members.

• classID: unique identification number for each class
• className: name of the class such as Yoga class
• classType: class type, such as basic class or premium class.
• additionalFee: the charges additional to the membership to have participation in a class

Schedule: it stores the schedule for each class.

• scheduleID: unique identification number for each schedule
• scheduleDays: it lists all the days on which the class is available. For example, Monday-Friday is stored to indicate that the class will be scheduled from Monday to Friday or five days a week.
• startTime: starting time of the class on the scheduled days. For example: 09:00 AM
• endTime: ending time of the class on the scheduled days. For example: 12:00 PM

Payment

• paymentID: unique identification number for each payment made by the member.
• paymentDate: the date on which a particular payment is made.
• amount: it is the amount transacted in a payment.

membership level

• membershipID: unique identification number for each membership plan
• membershipName: name of the membership plan. For example, basic or premium.
• Charges: it stores the current charges for enrolling in basic or premium membership plans.

Relations and cardinality

attends(member-class): there is a many-to-many relationship between member and class entity because a class may have zero or multiple members, and a member can attend one or multiple classes. The relationship has one attribute: presentOnDate. This attribute is used to store on which date the member was present. Therefore, the target relationship as an entity will contain three attributes: memberID, classID and presentOnDate.

has(class-schedule): there is a one-to-one strictrelationship between class and schedule entity. It means a schedule must only be related to a class, and a class must have only one schedule in the schedule entity.

has(membership level - member): there is a one-to-many relationship as one membership may have zero or multiple members, and there may be many members taking a particular membership level.

paid(member-payment): there is a one-to-many relationship as one member may pay multiple payments, but one payment must be associated with one member only.

Assumptions or constraints

• A member is allowed to accomplish the payment for a membership plan in instalments.
• Only the day on which the member was presented is recorded. It is assumed that the absenteeism can be calculated accordingly.
• Under the basic membership plan, a member is assumed to take part in several classes that are not premium.
• The schedule for a class is not divided into two or multiple parts on the same day. For example, a class cannot be for one hour in the morning and two hours in the evening. The schedule must be continuous for the day.
• Total payment by a member can be derived using all the payment details related to the member.

Activity 2: Business specification

Entities

• Department
• Employee
• Manager
• Project
• Dependent

Relationships

• Works_on_hours(Employee- Project): There is a one-to-many relationship with the total and partial participation of employee and project entity, respectively.

• Works_in(Employee - Department): there is a one-to-many relationship with total and partial participation of Employee and Department entity, respectively.
• Manager(Employee - Department): There is a one-to-one relationship with total participation of both entities. This is because each department has only one manager, and one manager can handle only one department.
• Has (Project - Department): there is a one-to-many relationship with total and partial participation of the Project and Department entity.
• Dependent_of (Dependent - Employee): there is a one-to-one relationship with total participation of both entities.

Weak relations

• Project Department

Operational guidelines

• Each employee must have an association with a department.
• A department may have zero or multiple employees.
• One department has one or no manager.
• One department may have multiple projects or no projects at all.
• An employee must have at least one dependent.
• An employee may or may not be assigned to a project.
• A project must be assigned to at least one employee.
• A department may or may not be assigned to a project.

Are you struggling to keep up with the demands of your academic journey? Don't worry, we've got your back! Exam Question Bank is your trusted partner in achieving academic excellence for all kind of technical and non-technical subjects.

Our comprehensive range of academic services is designed to cater to students at every level. Whether you're a high school student, a college undergraduate, or pursuing advanced studies, we have the expertise and resources to support you.

To connect with expert and ask your query click here Exam Question Bank