Instructions for assignment
M7D1 Program Your Classmate’s Database
For the discussion this week, you will again work with your classmates and practice writing functions and stored procedures. Remember that stored procedures can manipulate data and return data sets; functions can only read data and return scalar values.
Instructions to Learners
Step 1
Select an ER diagram that you have worked on during one of the discussions in a prior module. This ER diagram should be in at least third normal form and contain at least three tables and at least two relationships. Post an initial message with the following elements:
- Write a description for a scalar function that would be useful in your database. Some examples:
- Format a 9-character social security number (e.g., “123456789” formats as “123-45-6789”)
- Format a proper name (e.g., “MARY” formats as “Mary” and “sally” formats as “Sally”)
- Determine the last time given user whose primary key is provided to the function changed his/her password
- Determine the last name for a given user’s primary key
- Write a description for a stored procedure that would be useful in your database. Some examples:
- Return a record set of all users born in the provided month
- Delete all users with the provided last name
- Deduct an amount from an account, if possible
- Attach your ER diagram to your initial message or embed it as an inline image.
STUDENTS POST TO WORK OFF OF. INFORM ME IF YOU NEED MORE INFORMATION.
The database that I have created for this week is for a workout tracker database. The purpose of this database is for a user to be able to decide if it is time to train or not, and then from there they will be able to track their exercises and nutrition. The exercises are divided one step further to include different aspects such as cardio, strength, and recovery training. Being able to track this information should be simple enough for a beginner to use, but complex enough for someone who has long-term goals and ambitions to fine tune their athletic capabilities. A list of entities and attributes, a sample ERD, the datatypes for each attribute can be seen below:
—Entities and Attributes—
| Entities w/ Primary Keys | Attributes w/ Foreign Keys |
| User = user_ID* | Time to train = time_to_train?
Exercises (Foreign Key)= exercises_ID Nutrition (Foreign Key) = nutrition_ID |
| Training Plan = plan_ID* | Training Plan Name = plan_name
Training Plan Description = plan_description Cardiovascular (Foreign Key)= cardio_ID Strength (Foreign Key) = strength_ID Recovery (Foreign Key) = recovery_ID |
| Cardio = cardio_ID* | Length of Session = session_length
Type of Session = session_type Personal Records (Foreign Key) = records_ID |
| Strength = strength_ID*
|
Number of Sets = sets_numb
Number of Reps = reps_numb Personal Records (Foreign Key) = records_ID |
| Personal Records = records_ID*
|
Name of Lift = lift_name
Lift Number = lift_numb Cardio Type = cardio_type Cardio Time = cardio_time |
| Recovery = recovery_ID*
|
Time for Stretching = stretch_time
Time for Mobility = mobility_time Number of Rest Days = rest_days_numb |
| Nutrition = nutrition_ID* | Diet Name = diet_name
Description of Diet = diet_description Current Bodyweight = bodyweight_numb Goal Weight = goal_weight_numb |
—ER Diagram—
—Data Types—
| Attributes | Data Type |
| time_to_train?
user_ID plan_ID plan_name plan_decription nutrition_ID cardio_ID strength_ID recovery_ID records_ID session_length session_type cardio_time cardio_type lift_name lift_numb sets_numb reps_numb stretch_time mobility_time rest_days_numb diet_name diet_description bodyweight_numb goal_weight_numb |
Boolean (1=True/0=False)
VARCHAR (50) VARCHAR (50) CHAR (50) VARCHAR(100) VARCHAR (50) VARCHAR (50) VARCHAR (50) VARCHAR (50) VARCHAR (50) NUMERIC (HH:MM:SS) CHAR(50) NUMERIC (HH:MM:SS) CHAR (50) VARCHAR (50) INT INT INT NUMERIC (HH:MM:SS) NUMERIC (HH:MM:SS) INT VARCHAR (100) VARCHAR (250) INT INT |
Use the notation introduced in pages 88 and 104 of the textbook. The database design should represent a design that is lower than third normal form (i.e., it should be in first or second normal form but no higher).
After taking time to design an ER diagram for the new database design for module four, the next logical step was to translate each table into a relation. According to Harrington (2009), “in order to translate entities into a set of relations, a table should be created for each entity, a primary key should be identified, foreign keys need to be distinguished, and anything that needs to be concatenated, such as the primary keys, should be done” (p. 104). Based off of the textbook’s instruction, the ERD for the Training Tracker can be translated into the following list of relations:
User (user_ID, time_to_train?, exercises_ID, nutrition_ID)
Training Plan (plan_ID, plan_name, plan_description, cardio_ID, strength_ID, recovery_ID)
Cardio (cardio_ID, session_length, session_type, records_ID)
Strength (strength_ID, sets_numb, reps_numb, records_ID)
Personal Records (records_ID, lift_name, lift_numb, cardio_type, cardio_time)
Recovery (recovery_ID, stretch_time, mobility_time, rest_days_numb)
Nutrition (nutrition_ID, diet_name, diet_description, bodyweight_numb, goal_weight_numb)
From here, we can determine which normal form the database is in currently. Harrington (2009) continues that “normal forms are theoretical rules that the design of a relation meet. Each normal form represents an increasingly stringent set of rules. Theoretically, the higher the normal form, the better the design of the relation. Overall, there are six total normal forms” (p. 105). The information provided in the previous sections shows that this new database design is currently in First Normal Form. This is due to the fact that there is not any repeating data stored in a two-dimensional table and there are no repeating groups. Harrington (2009) also states that “a repeating group is an attribute that has more than one value in each row of a table” (p. 106). In order to meet the requirement for first form, there cannot be any multivalued attributes. In order to alleviate issues such as these, an additional entity needed to be created. During the initial creation of the diagram, there was an issue of trying to store data objects such as goal times and max lifts for both the Cardio and Strength entities, respectively. In order for the database to reach at least First Normal Form, the table Personal Records had to be created to store the information for these values. This removed the issue of repeating groups from the Cardio and Strength entities, and through the use of Foreign Keys, placed the values for each within the Personal Records table.
Describe the purpose of the database you designed, together with the meaning of each table and the relationships between the tables. Note that, in order for your classmates to conduct normalization, at least one table in your database design MUST have partial and/or transitive dependencies within. As stated in the introduction of this post, the purpose of this assignment is to create a tracker that anyone can use to account for their exercising and diet in a simple to use format. The initial table is labeled as User. When a user accesses the database, they will be prompted with the option to state if it is time to work out or not. From there, they can access their training plan or nutrition information that can coincide with the user’s profile. From there, the Training Plan entity contains foreign keys that divide information up amongst cardio, strength, and recovery. The name and description of the user’s training plan will be listed here as well for identification. The Cardio, Strength, and Recovery Tables are composite entities that are used to identify different information about the user’s training plan. Under the Cardio entity, users can track their intended session length, session type, and have the option to store their personal records, which is represented as a foreign key called records_ID. The Strength entity also utilizes the records_ID foreign key to store personal records from lifting, while the rest of the table stores the recommended number of sets and reps for their training plan. The Recovery entity identifies the part of the training plan that will allocate time for stretching, mobility and massages, and number of days during the week they should adhere to resting. The final table, Personal Records, is a weak entity that is dependent on the Cardio and Strength entity tables. It stores the values from the cardio sessions and weights sessions so the user can see how they performed at their best. In order to move this database to a higher normal form, a partial or transitive dependency needs to be identified. Below is a table with an example of a partial dependency:
| Personal Records | Lift_name | Lift_numb | Cardio_type | Cardio time |
| 1 | Deadlift | 350lbs | Run | 6:00/mile |
| 2 | Bench Press | 325lbs | Run | 5:55/mile |
| 3 | Back Squat | 400lbs | Run | 5:50/mile |
| 4 | Front Squat | 275lbs | Run | 5:45/mile |
In order to move to a higher normal form, the partial dependency would need to be removed. StudyTonight (2020) states that “a partial dependency is where an attribute in a table depends on only a part of the primary key and not on the whole key.” Moving to the Second Normal Form will take some creative efforts, however, it would not be impossible to do with a little work.
Closing Remarks. Normal Forms help with the overall structure of a database, and provide the organizational requirements to fine to and alleviate data retrieval problems. Although it takes time to fine tune, the added help from another set of eyes allows for a more usable design. The example in the previous section provides ample opportunity for anyone to flex their normalization muscles, and taking the design from First Normal Form to Second Normal Form or higher will be a great challenge for anyone willing to try.
