Question

For this DATABASE homework assignment you will create an Entity Relationship model for a business case and then convert the model to a set of relations.

 Read through the following business case for the “Drum Corps International”

 Create an Entity relationship model with the necessary entities, attributes, identifiers and relationships to capture the data requirements.

 All relationships should be labeled with verb phrases.

 Use UML notation for this work.

 Relationship lines should not cross.

 Write out two “relationship sentences” for each relationship using the format:

One <entity> may be (or must be) <verb phrase>  one (or many) <entity>

 Once your ER model is completed, Convert the ER model to a set of Relations.

 Clearly indicate a key (key) and any foreign keys (fk) for each Relation. Each relation should only be shown one time.

 This is an individual assignment. Collaboration or copying of another student’s work is not permitted.

Business Case: Marching Band Competition

Drum Corps International (DCI) organizes marching band and drum corps competitions all over the world. They require a database that can keep track of registered students that form teams who then participate in the competitions. For each team member we need to keep track of their personal information (name, address, date of birth, etc.) and associate them with their team. The team has a team name, team colors and mascot. Each team also has a coaching staff (for example). For each staff member we need to keep track of their personal contact information as well as their staff role and associate them with the team. Competitions are scheduled throughout the year. We need to keep track of when each competition starts and ends (date and time), the location for the competition, and which teams enter the competition. Prizes are awarded to each team that competes. Teams can enter more than one competition throughout the year.

Assignment Submission Draw it either using pencil and paper

1. An E-R diagram with all of the entities, attributes, identifiers and relationships. Be sure all relationships are labeled appropriately with verb phrases and the diagram includes an appropriate legend (Name of the Model, Created by, Date/Time, Your Name and Course information, etc.)

2. A set of “Relationship Sentences”

3. The initial set of relations resulting from the conversion from E-R to relational. Clearly indicate keys and foreign keys.

Answer 1

Personal_Info(P_id primary key,T_id reference key(Team) )

Team(T_id primary key)

Coaching_staff(staff_id primary key, T_id reference key(Team) )

Competition (C_id primary key,T_id reference key(Team))

Prize(C_id reference key(competition))

you have any query then plz ask me without any hesitation in the comment section below, if you like my answer then please thumbs up for the answer, before giving thumbs down please discuss the question it may possible that we may understand the question different way and we can edit and change the answers if you agree, thanks :)