Select ONE of the following 5 scenarios and design an entity relationship diagram with the following components:
- Identify the entities that need to be modeled
- Identify the attributes that each entity will include
- Identify the relationships as required.
- Identify the primary keys (identifier attributes)
- Identify any business rules or assumptions that are associated with your data model
Scenario 1 – Online Ordering and Delivery Service for Sandwich Shop Joe’s Sub Shop is starting an online ordering system that will provide local delivery within five miles of the sub shop. The customer can order a limited menu of the sandwiches the sub shop creates. The online order system will need to track the following information customer, order and product. The shop will need to know the quantity ordered, toppings and other directions for the order. The delivery drive will need to know the address and phone number of the customer.
Scenario 2 – A Local Football League You have been asked to develop a database to maintain details of the football teams signed up to play in a local league, including the schedule. The database must maintain details of the name and location of each team, and for each team the players (and their positions) who are on the team. So that a handicap system can be developed later the database should maintain the gender, and age of each player. Each player should also have one ‘phone number stored. Schedule and field locations for each of the games the teams will play.
Scenario 3 – Student Internship Database
A local university is starting an internship program for its seniors to gain real world experience in local businesses to gain real-world experiences from the business word. The following information needs to be tracked, student, intern opportunity, company, degree program, application. The details related to the student is name, degree program, contact information. For the company need to track company name, address, contact information, Please identify any additional information that would be necessary to support the database design.
Scenario 4 – Children’s Playgroup
A local community center is establishing a children’s playgroup that will introduce children in the neighborhood to other parents and children. This playgroup will sponsor weekly play sessions with a structured activity and free play time each week. If this program is successful, the community center would like to offer this play session on a daily basis. The following information needs to be considered, a parent can have more than one child, the parent information, including name, phone number, and mailing address need to be tracking. Child’s name, gender and age need to be tracked. The ability to track one or more sessions, including date, time, location and session type. Eventually the community center would like to add additional special “activities” that the parents and children would be interested.
Scenario 5 – Pet Sitting Service Company
A local company is starting that will provide pet sitting services for the local community. This company will provide in-house and daily pet sitting services for dogs, cats, fish, birds and reptiles. Each of their employees is insured and are certified pet care specialists. The company needs to track the following employee information, employee name, and address, pet types certified to support, hourly rate and availability. Information about the pet owner needs to be track, including pet owner name, address, billing information, and phone number. The pet information includes pet type, pet name, pet medications, pet likes and dislikes. The company needs to track the pet sitting requirements for each pet, including in-house or number of visits per day, total number of days for pet sitting order, special requirements for pet.In addition any special pet food, treats or information needs to be track about each pet. Each pet owner can have one or more pets.