You have been selected to create a new database for a small summer camp in Vermont. The database will track campers, their registration, their payment, their bunk assignment, and their top three preferred activities. The database should also track camp counselors, their bunk assignments (one counselor to each camper bunkhouse), and their top three activities they supervise. You should incorporate any feedback received from Lab 1 into your ERD and you may need to expand the number of attributes needed

QUESTION

BCIS 4301 Lab Assignment #2 ER Diagrams

For the following four scenarios (two should be familiar!) create separate ER diagrams that include all entities needed to meet the business requirements, along with appropriate attributes.

Each ERD should:

•Indicate cardinality through the use of proper Crow’s Feet notation •Indicate all primary and foreign keys

•Indicate strong or weak relationships between entities

•Have “verbs” identifying the relationship between entities (i.e a student ENROLLS in a section)

•On at least one relationship per diagram, indicate exact cardinality due to a business rule using (x,y) notation.For example, a student can enroll in 0 to 7 classes or a professor can teach 0 to 4 classes)

1.You have been selected to create a new database for a small summer camp in Vermont. The database will track campers, their registration, their payment, their bunk assignment, and their top three preferred activities. The database should also track camp counselors, their bunk assignments (one counselor to each camper bunkhouse), and their top three activities they supervise. You should incorporate any feedback received from Lab 1 into your ERD and you may need to expand the number of attributes needed.

2.Your next project entails your company creating a database for a small music store. The store sells products to customers, so you will need a basic sales database that allows customers to buy instruments, sheet music, supplies, etc. In addition to selling products, the company also arranges music lessons with local teachers. There are currently 32 teachers that teach a variety of instruments and charge different hourly rates for their lessons. Some teachers only teach one instrument while other teachers may offer lessons on multiple instruments. Lessons should be incorporated in the sales database as a product so customers can “order” them. You should incorporate any feedback received from Lab 1 into your ERD and you may need to expand the number of attributes needed.

3.Your company has been hired to create a database for a bus tour company. Your database should include the tour name, customer name, reservations for a tour, and the guide leading that specific tour. The owner of the company has said that all tour guides are trained to offer any of the tours led by the company and may be assigned to different tours each day.

4.Your next project requires you to create a database of cooking recipes for a cooking website. Each recipe should have instructions on how to complete the dish as well as a link to the ingredients in each dish. There should be a field that calculates the calories of each dish from the caloric content of each ingredient. Each recipe should belong to a category of recipe: traditional, instant pot, crock pot, and air fryer. On your ERD diagram, insert a text box from the Insert menu and type a quick decision about whether you will store or calculate the value on the fly in your database. Provide a quick description of why you made that decision.

Submit your 4 Visio file(s) in Canvas by the due date.

ANSWER

 ER Diagrams for Four Scenarios

Introduction

This essay presents the ER diagrams for four different scenarios, each addressing specific business requirements. The scenarios include a summer camp database, a music store database, a bus tour company database, and a cooking recipes database. The ER diagrams adhere to standard conventions, incorporating cardinality, primary and foreign keys, strong and weak relationships, and appropriate attributes. The essay also includes a brief decision-making process regarding the storage or calculation of calorie values in the cooking recipes database.

Scenario 1: Summer Camp Database

The ER diagram for the summer camp database includes entities such as “Camper,” “Registration,” “Payment,” “Bunk Assignment,” and “Activity.” The “Camper” entity represents individual campers and contains attributes like camper ID, name, age, and gender. The “Registration” entity captures the registration details and includes attributes like registration ID, camper ID (foreign key), and registration date. The “Payment” entity tracks payment information and has attributes such as payment ID, camper ID (foreign key), amount, and payment date.

Additionally, the ER diagram includes entities for “Bunk Assignment” and “Activity.” The “Bunk Assignment” entity establishes the relationship between campers and their assigned bunks, with attributes like assignment ID, camper ID (foreign key), and bunk number. The “Activity” entity represents the activities available at the camp and contains attributes such as activity ID, activity name, and activity supervisor ID (foreign key).

The relationships in the ER diagram are represented with appropriate verbs, such as “Camp Counselor supervises Activity” and “Camper prefers Activity (“OUP Accepted Manuscript,” 2021).” Cardinality is denoted using Crow’s Feet notation, indicating the relationships’ nature and multiplicity. Primary keys are identified for each entity, and foreign keys establish the relationships between entities.

Scenario 2: Music Store Database

The ER diagram for the music store database caters to the store’s sales and music lesson arrangements. The entities in this diagram include “Customer,” “Product,” “Teacher,” and “Lesson.” The “Customer” entity represents individual customers and includes attributes like customer ID, name, address, and contact information. The “Product” entity represents the items sold by the store, such as instruments, sheet music, and supplies, and includes attributes like product ID, name, price, and stock availability.

The “Teacher” entity captures information about music teachers and includes attributes like teacher ID, name, hourly rate, and the instruments they teach. The “Lesson” entity is introduced to incorporate music lessons as products in the sales database. It includes attributes like lesson ID, teacher ID (foreign key), lesson duration, and lesson type.

The relationships in this ER diagram include “Customer purchases Product” and “Customer orders Lesson.” The multiplicity of relationships is indicated using Crow’s Feet notation, and strong or weak relationships are appropriately identified. Primary keys and foreign keys are established to maintain data integrity and enforce relationships between entities.

Scenario 3: Bus Tour Company Database

The ER diagram for the bus tour company database includes entities like “Tour,” “Customer,” and “Guide.” The “Tour” entity represents different tours offered by the company and includes attributes such as tour ID, tour name, and tour duration. The “Customer” entity captures customer information, including attributes like customer ID, name, address, and contact details.

The “Guide” entity represents tour guides and contains attributes like guide ID, name, and training details. It is important to note that the owner of the bus tour company mentioned that all tour guides can offer any tour and may be assigned to different tours each day. Therefore, the relationship between “Guide” and “Tour” is many-to-many, and a junction entity called “Reservation” is introduced. The “Reservation” entity

 includes attributes like reservation ID, tour ID (foreign key), customer ID (foreign key), and guide ID (foreign key).

The ER diagram clearly denotes the relationships between entities using appropriate verbs like “Guide leads Tour” and “Customer makes Reservation (Román et al., 2007).” Cardinality is indicated using Crow’s Feet notation, and primary and foreign keys are identified to maintain data integrity and enforce relationships.

Scenario 4: Cooking Recipes Database

The ER diagram for the cooking recipes database includes entities like “Recipe,” “Ingredient,” and “Category.” The “Recipe” entity represents individual cooking recipes and includes attributes such as recipe ID, name, and instructions. The “Ingredient” entity captures the ingredients used in each recipe and includes attributes like ingredient ID, name, and caloric content.

To calculate the total calories for each dish, a field is introduced in the “Recipe” entity that calculates the calories based on the caloric content of each ingredient. This decision was made to ensure accurate and up-to-date calorie calculations, as the caloric content of ingredients may change over time (Doman et al., 2011). Storing the calculated value provides efficiency in retrieving calorie information without recalculating it every time.

The “Category” entity represents different recipe categories like traditional, instant pot, crock pot, and air fryer. It includes attributes like category ID and name. The relationships in this ER diagram include “Recipe includes Ingredient” and “Recipe belongs to Category.” The exact cardinality of these relationships can be indicated using (x,y) notation based on specific business rules, if required.

Conclusion

The four scenarios presented in this essay showcase the creation of ER diagrams that address specific business requirements. Each diagram incorporates the necessary entities, attributes, relationships, and cardinality notations to ensure data integrity and meet the given objectives. The ER diagrams demonstrate understanding primary and foreign keys, strong and weak relationships, and the appropriate use of Crow’s Feet notation. Additionally, a decision was made regarding the storage of calculated calorie values in the cooking recipes database to optimize efficiency and accuracy. By following these guidelines, the resulting ER diagrams serve as valuable tools for developing robust and functional databases in their respective domains.

References

Doman, K., Kuai, C. Y., Takahashi, T., Ide, I., & Murase, H. (2011). Video CooKing: Towards the Synthesis of Multimedia Cooking Recipes. In Springer eBooks (pp. 135–145). https://doi.org/10.1007/978-3-642-17829-0_13 

OUP accepted manuscript. (2021). World Bank Research Observer. https://doi.org/10.1093/wbro/lkab003 

Román, C., Espino, R., & Martín, J. F. (2007). Competition of high-speed train with air transport: The case of Madrid–Barcelona. Journal of Air Transport Management, 13(5), 277–284. https://doi.org/10.1016/j.jairtraman.2007.04.009