Create a logical ERD for each of the problems on the following pages using the crowsfoot notation discussed in class. Be sure that each entity has the entity name at the top of the box, the primary key attribute, or attributes in the middle of the box, and the non-key attributes in the bottom of the box. Lines should separate each part of the entity box. Follow these instructions about each ERD:

• The ERD should not have any many-to-many (M:N) relationships.
• All attributes should be placed within an entity.
• Each entity must have a primary key defined. A primary key may consist of one or more attributes.
• Each relationship must have a foreign key. Denote the foreign key(s) with the notation (FK) on the ERD. It is not necessary to number the foreign keys, but if you do that would be nice.
• Each relationship must include both a maximum and minimum cardinality for both sides of the relationship.
• Each relationship must have a relationship verb.
• Remember that 1:1 relationships are rare. Make sure that you carefully scrutinize any 1:1 relationships to ensure that they are actually 1:1.

Question #2. Design a database for ShapeShifters Fitness Center, a company that provides personal training. ShapeShifters plans to use this database to keep track of: customers and potential customers, membership type, trainers, personal training events (both those that are scheduled and those that are completed), and notes and price related to a specific training event. Sample data for ShapeShifters is provided in the Excel workbook called ShapeShiftersData available as a link in the same cell where you found this assignment. Each row in the worksheet represents one person signed up for one personal training event conducted, or scheduled to be conducted, by a trainer at ShapeShifters. Here is some additional information about the sample data:

• A customer has only one name and phone number.
• A customer who is a member has a membership typeID. It is possible for a customer to get training from ShapeShifters, but not be a member of the fitness center. If a customer is not a member (like Jim Beaker in the sample data) then the membership typeID is null for that customer.
• A membership type and membership monthly fee is related to a membership type. For example, a "standard" membership type is always $72/month, while a "corporate" membership fee is always $65/month.
• A training event consists of only one trainer, but there could be many customers at a single training event. In the sample data, Melanie Flobert, Maria Hernandez, Jill Johnson, and Loquesha Bartlett are signed up for Marianne Yourdon's Cross-Fit Specialization training event (Training EventID 13 in the data).
• A trainer may schedule many training events, and a customer may schedule many training events.
• A training event does not have a specific name. Most of the time, the training notes are filled in after a training event has occurred to document what happened during the training event. Sometimes, the training notes are completed prior to training, such as training event ID 13, the Cross-fit specialization class. The date when the training notes are completed should not affect the design of the database.
• The hourly rate paid to a trainer is related to a trainer. A trainer only has one hourly rate.
• The training hourly price is related to a specific training event. The price may change for each event.

Only for this question of the assignment (question #2) create sample data tables for your final data model showing how 3 rows of the ShapeShiftersData worksheet will be stored among your newly designed tables. Make sure that there are about 3 rows of data in each of the tables you designed on the ERD. Remember to include the foreign keys, and the foreign key data values in those tables!

Shape Shifters Data.xlsx Download Shape Shifters Data.xlsx (12.2 KB) Sample List of Scheduled and Completed Training Classes Shapeshifters Fitness Center Customer Membership Type Membership Start Membership ID Customer Name Customer Phone ID Date Membership Type Monthly Fee 10058 Melanie Flobert (775) 266-8551 ( 100 2/17/2018 Standard $72.00 10058 Melanie Flobert (775) 266-8551 100 2/17/2018 Standard $72.00 Trainer ID Trainer Hourly Training Training Rate Event ID Training Date Completed Training Hourly Price Trainer Name 312 Jane Martin 312 Jane Martin $25.00 $25.00 Y Y $85.00 $65.00 1 10/20/2020 2 11/22/2020 3 10/26/2020 Training Hours Training Notes Performed initial evaluation and 1 created routine 2 Zoom Supervised routine Modified routine to include 0.5 pilates Created specialized 1 cardio/weight routine 1.5 Zoom Supervised routine 10058 Melanie Flobert (775) 266-8551 100 2/17/2018 Standard $72.00 $35.00 Y $95.00 12577 Justin Crandall 12577 Justin Crandall (530) 772-6003 (530) 772-6003 227 Anna Martinez Jacoby 208 Sorrenson 312 Jane Martin 100 100 1/5/2019 1/5/2019 Standard Standard $72.00 $72.00 $75.00 $25.00 4 12/15/2020 5 12/10/2020 Y Y $125.00 $50.00 12577 Justin Crandall (530) 772-6003 100 1/5/2019 Standard $72.00 $25.00 6 2/18/2021 n $50.00 1 Zoom Modified routine to 1 1 include pilates 12577 Justin Crandall (530) 772-6003 ( 100 1/5/2019 Standard $72.00 $40.00 7 1/4/2021 Y Y $70.00 18923 Loquesha Bartlett (805) 788-4122 200 5/14/2020 Corporate $65.00 $40.00 8 2/26/2021 0.5 $50.00 200 100 5/14/2020 8/12/2020 Corporate Standard $65.00 $72.00 18923 Loquesha Bartlett 19800 Maria Hernandez 21500 Jim Beaker 21500 Jim Beaker (805) 788-4122 (775) 544-5685 (702) 348-9751 (702) 348-9751 263 Kendall Summers Marianne 218 Yourdon Marianne 218 Yourdon Marianne 218 Yourdon 312 Jane Martin 312 Jane Martin 312 Jane Martin Marianne 218 Yourdon Marianne 218 Yourdon Marianne 218 Yourdon Marianne 218 Yourdon $40.00 $25.00 $25.00 $25.00 9 10 11 12 2/19/2021 1/27/2021 2/17/2021 2/14/2021 n Y n 2 3 Created exercise structure 2.5 1 $100.00 $ $75.00 $155.00 $155.00 n n 10058 Melanie Flobert (775) 266-8551 100 2/17/2018 Standard $72.00 $40.00 $ 13 2/5/2021 $25.00 1 Cross-fit specialization class 1 Cross-fit specialization class n 19800 Maria Hernandez (775) 544-5685 100 8/12/2020 Standard $72.00 $40.00 13 2/5/2021 $25.00 n n 13859 Jill Johnson (775) 344-2021 200 1/1/2021 Corporate $65.00 $40.00 13 2/5/2021 1 Cross-fit specialization class $25.00 n 18923 Loquesha Bartlett (805) 788-4122 200 5/14/2020 Corporate $65.00 $40.00 13 2/5/2021 1 Cross-fit specialization class $25.00 Shape Shifters Data.xlsx Download Shape Shifters Data.xlsx (12.2 KB) Sample List of Scheduled and Completed Training Classes Shapeshifters Fitness Center Customer Membership Type Membership Start Membership ID Customer Name Customer Phone ID Date Membership Type Monthly Fee 10058 Melanie Flobert (775) 266-8551 ( 100 2/17/2018 Standard $72.00 10058 Melanie Flobert (775) 266-8551 100 2/17/2018 Standard $72.00 Trainer ID Trainer Hourly Training Training Rate Event ID Training Date Completed Training Hourly Price Trainer Name 312 Jane Martin 312 Jane Martin $25.00 $25.00 Y Y $85.00 $65.00 1 10/20/2020 2 11/22/2020 3 10/26/2020 Training Hours Training Notes Performed initial evaluation and 1 created routine 2 Zoom Supervised routine Modified routine to include 0.5 pilates Created specialized 1 cardio/weight routine 1.5 Zoom Supervised routine 10058 Melanie Flobert (775) 266-8551 100 2/17/2018 Standard $72.00 $35.00 Y $95.00 12577 Justin Crandall 12577 Justin Crandall (530) 772-6003 (530) 772-6003 227 Anna Martinez Jacoby 208 Sorrenson 312 Jane Martin 100 100 1/5/2019 1/5/2019 Standard Standard $72.00 $72.00 $75.00 $25.00 4 12/15/2020 5 12/10/2020 Y Y $125.00 $50.00 12577 Justin Crandall (530) 772-6003 100 1/5/2019 Standard $72.00 $25.00 6 2/18/2021 n $50.00 1 Zoom Modified routine to 1 1 include pilates 12577 Justin Crandall (530) 772-6003 ( 100 1/5/2019 Standard $72.00 $40.00 7 1/4/2021 Y Y $70.00 18923 Loquesha Bartlett (805) 788-4122 200 5/14/2020 Corporate $65.00 $40.00 8 2/26/2021 0.5 $50.00 200 100 5/14/2020 8/12/2020 Corporate Standard $65.00 $72.00 18923 Loquesha Bartlett 19800 Maria Hernandez 21500 Jim Beaker 21500 Jim Beaker (805) 788-4122 (775) 544-5685 (702) 348-9751 (702) 348-9751 263 Kendall Summers Marianne 218 Yourdon Marianne 218 Yourdon Marianne 218 Yourdon 312 Jane Martin 312 Jane Martin 312 Jane Martin Marianne 218 Yourdon Marianne 218 Yourdon Marianne 218 Yourdon Marianne 218 Yourdon $40.00 $25.00 $25.00 $25.00 9 10 11 12 2/19/2021 1/27/2021 2/17/2021 2/14/2021 n Y n 2 3 Created exercise structure 2.5 1 $100.00 $ $75.00 $155.00 $155.00 n n 10058 Melanie Flobert (775) 266-8551 100 2/17/2018 Standard $72.00 $40.00 $ 13 2/5/2021 $25.00 1 Cross-fit specialization class 1 Cross-fit specialization class n 19800 Maria Hernandez (775) 544-5685 100 8/12/2020 Standard $72.00 $40.00 13 2/5/2021 $25.00 n n 13859 Jill Johnson (775) 344-2021 200 1/1/2021 Corporate $65.00 $40.00 13 2/5/2021 1 Cross-fit specialization class $25.00 n 18923 Loquesha Bartlett (805) 788-4122 200 5/14/2020 Corporate $65.00 $40.00 13 2/5/2021 1 Cross-fit specialization class $25.00