Create an ERD diagram that supports the business operations. Please make sure that the primary keys underlined
Question:
Create an ERD diagram that supports the business operations. Please make sure that the primary keys underlined and please make sure that all the relationships are connected to the right entries. Also please use Crow's foot notation.
Beautiful Intimate Gatherings (BIG) provides a variety of services for couples looking to get married. Couples can discuss the various options with a BIG wedding consultant, who will help them to choose the BIG services that meet their needs and their budget. When a couple of contracts with BIG to provide wedding services that are referred to as a “wedding”. For each wedding, the name of the wedding and the initial date that the couple contracts with BIG is recorded. The name of the wedding is usually the last name of the couple. For example, when Lisa Lopez and Antony Cruz contract with BIG, that would be named the “Lopez-Cruz Wedding”. Every wedding must have at least two people associated with it – the couple being married. Due to death or divorce, it is possible for a person to be a member in more than one wedding couple over time. For each person in the wedding couple, the person’s name, address (street, city, state, zip code), and phone number are recorded.
BIG offers many services such as, the marriage ceremony, reception, rehearsal dinners, dressmaking, limousine, carriage, and others. For each service available, the name of the service, the type of service (ceremony, gathering, transportation, etc.), and a description are kept. Some services are provided by only one contractor, but for most services, BIG has several contractors that can provide that service. Some contractors can provide more than one service. A contractor must provide at least one service to be in the system. BIG will always have at least one contractor for every service. Different contractors can provide a given service in slightly different ways. For example, BIG has two contractors that can provide low-cost, basic catering for a reception and one contractor that can offer high-cost, fine dining for a reception. To help potential clients understand the differences in each contractor providing a service, a description of each contractor’s capabilities in providing each service are kept in the system. Therefore, there is a description for the generic service, regardless of which contractor provides the service, plus a separate description of how the service is provided by each specific contractor. For each contractor, the contractor’s company name, address, and phone number are recorded.
While some weddings involve only a single service, most couples contract with BIG to provide many services. The services that are provided for a particular wedding are organized into “events”. Each event is associated with one and only one wedding. Every wedding must include at least one event. Some events involve several services but others involve only one service. For example, the Lopez-Cruz wedding includes three events: wedding ceremony, reception, and after-reception transportation. The reception involves catering, floral, and valet parking services, while the after-reception transportation involves only limousine service. It is possible for BIG to offer a service that no couple has ever chosen, but most services have been provided for many different weddings. For each event, an event name is assigned, often with the help of the couple. For example, in the Lopez-Cruz wedding, they chose to call the reception the “Post Wedding Gala”. In addition to the event name, the beginning date and time, ending date and time, and a description are entered into the system. Every event must occur at some place, although the event can be entered into the system before the couple decides on a location for the event. For each location, the name of the location, the location type, and address are kept by BIG. Locations that are new to BIG may have never had an event scheduled there, but most have had several events over time. Every event is scheduled for only one location.
A couple may decide which BIG services they want before deciding which contractor they want to provide that service so it is important that the system allows the couple to contract a service without specifying the contractor. For example, the Lopez-Cruz couple may contract for BIG to provide catering to the reception event before they decide if they want a contractor that specializes in Italian cuisine or a contractor that specializes in Latin cuisine.
In addition to knowing which contractor is contracted to provide a service for an event, it is also important to know which contractor actually ends up furnishing each service at an event. For example, LaVerne Florist may be contracted to provide floral services for the Lopez-Cruz reception and Greta’s Gourmet is contracted for the catering. Two days before the event, LaVerne Florist may notify BIG that due to an unforeseen problem, they will not be able to deliver on the contract. As a result, BIG has to get One-Day Flowers to provide the floral service. The system should track that LaVerne Florist was contracted for floral service but One-Day Flowers actually provided the service. It should also show that Greta’s Gourmet was contracted for catering and that Greta’s Gourmet actually provided the catering.
Due to variables in each service, such as the number of planned guests at a reception, an estimated cost and actual cost of furnishing the service for each event must be kept. Also, it is possible in some cases for a contractor to furnish service on a date that is different than the actual event. For example, for an early morning wedding ceremony, a florist may begin delivering and arranging flowers the evening before the ceremony or a caterer may begin setting up tables and decorations for a reception the night before the reception. The actual date that the contractor began furnishing the service must be recorded.
In addition to the wedding couple, there are guests that are invited to some events. Not all events, such as transportation after the reception, have guests invited. A person can be in the BIG system as a member of a couple and have never been a guest at any event. (The couple being married are never considered a “guest” at their own wedding, but can be guests at the weddings of other couples over time.) For events that have guests invited, a guest list is compiled. A person can be invited to many events. BIG places guest books at events for guests to sign. The guest book has space for the guest to write their name, address, and phone number. The address and phone number are usually left blank by guests, but some people do provide that information. After an event is over, BIG will take the information from the guest books and try to match them with the people that were invited to the event. Individuals that were on the guest list but that did not attend are marked as invited and as not attending. Individuals that attended but are not on the guest list are added to the BIG system as guests and are marked as not invited but as attending.
Finally, the total estimated cost of the wedding and the total actual cost of the wedding should be stored as redundant data.
Income Tax Fundamentals 2013
ISBN: 9781285586618
31st Edition
Authors: Gerald E. Whittenburg, Martha Altus Buller, Steven L Gill