User Database Design Everyday Body Fitness Company need to a system to keep track of a large collection of training programs and customers registered with them. Everyday Body Fitness Company is planning to design this database model to hold information relating to their programs, Trainers, exercise logs and their customers. Initial requirements analysis brings out the following User view requirements / business rules about what needs to be recorded. Assume that you are hired to model and develop the database by using these business rules. To facilitate this, you first need to design the database by using an Entity Relationship (ER) Diagram with several entities/ tables and relationships. To fulfil the information-level design of the database that satisfies the following constraints and user view requirements. To fulfil this information-level design you are required to read the requirements described in the scenario given below and answer the questions.
User view 1 requirement / business rule
There are customers, each of which may sign up for one or more training programs For each customer a unique Cust_number and their first name, last name, email address must be recorded.
User view 2 requirement / business rule
Each training program has a program number (PNum) that uniquely identifies the program. The program title, StartDate, Finish Date, and Price must also be recorded. Each training program has an identified Trainer linked with it. A trainer can work on one or more scheduled programs, however there are new trainers they don't have any training programs allocated to them.
User view 3 requirement / business rule
For each trainer, a unique ID number, their first name, last name, date of birth, address, contactNo, user name (unique user name for each trainer) and password must be recorded.
User view 4 requirement / business rule
Exercise log keeps track of what customers doing and customers can maintain one or more exercise logs. As a database designer for Everyday Fitness, you need to ensure that each Exercise Log has a unique ID number, Date, Exercise category, Time spent, calories burnt.
User view 5 requirement / business rule
There are a number of training programs and large number of customers registered, hence Everyday Body Fitness Company will keep track of training program number, registered customer Cust_numbers and customer_payments details
(Note If given business rule is already in normal form, how can i make an assumption and show steps of 1FN 2FN and 3FN to make into normal form)
1. how can i make assumptions about data that are not explicitly given in the problem?
2. ER diagram And show their relationship? 3. determine the functional dependence?
4. normalize to 3NF?