techie avatar image
techie asked

ER Diagram ,Normalization

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?

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

0 Answers


Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.