Databases The owner of a catering company wants a database to keep track of vari

Question

Databases

The owner of a catering company wants a database to keep track of various aspects of the business. Clients may be individuals or businesses who make arrangements for events such as weddings corporate dinners, fundraisers, etc. The company owns a catering hall in which only one event can take place at a time In addition, clients can use the services of the company to cater events at their homes, places of business, or places that the client rents, such as historic mansions. Several of these off-site events can take place at the same time. Clients who rent the catering hall guarantee a minimum of 150 guests, but the hall can accommodate 200. Reservations for the hall are accepted up to two years in advance. Clients who provide their own space can have any number of guests. Reservations for off-site events are usually made seven months in advance. The firm provides the food, table settings (dishes, silverware, and glassware), linens, waiters, and bartenders for the event, regardless of whether it is in the catering hall or elsewhere. Customers can choose the color of the linen. The firm has set menus that are identified by number. For a given menu number, there is one appetizer, one salad, one main course, and one dessert. (For example, menu number 10 may include shrimp cocktail, Caesar salad, prime rib, and chocolate mousse.) The firm quotes a total price for its services, based on the number of guests, the menu, the location, and intangible factors such as how busy they are on that date. The firm can also contract for floral arrangements, musicians, entertainers, and photographers, if the client requests. The client pays the catering company, which then pays the contractor. The price charged the customer for each of these is always 10% above the cost to the catering company. Assume names are unique. Also assume musicians and entertainers provide only one type of music or entertainment.

a. Using these assumptions and stating any others you need to make, list all the non-trivial functional dependencies for this relation

b. What are the candidate keys for this relation? Identify the primary keys

c. Is this relation in third normal form? if not find a 3NF lossless join decomposition of College that preserves dependencies.

Assume the following attributes have been identified: client Name cFName c Phone cStreet. ccity. cstate. czip, event Dat event StartTime event Duration event Type number Guest ocation Name, location Street location City. location State. locationzip, linencolorRequested. numberWaiters number Bartenders total Price, floristName florist Phone, floristCost. florist Price music Contact. musicContact Phone. musicType, musicCost, music Price, entertainer Name entertainer Type entertainer Cost entertainer Phone entertainer Price, photographerName, photographer Phone, photographer Cost, photographer Price. menu NunberChosen menu Appetizer menu Salad, menuMa in menu Dessert

Explanation / Answer

a. Using these assumptions and stating any others you need to make, list all the non-trivial functional dependencies for this relation

gafferContact, gafferContactPhone, gafferPrice

I am going to add the fields: clientID, eventID, locationID, floristID, musicID, entertainerID, photographerID, menuID, totalPriceID, baseEstimate, intangiblesCost, quoteRange, totalPrice. …In part (c) and beyond I add table concept structures which allow for pseudo-recursion / partitioning / drill-downs. The layout is akin to adding entertainmentScheduledID, entertainmentScheduledDate, entertainmentScheduledStartTime, entertainmentScheduledDuration, entertainmentScheduledSetupTime, entertainmentScheduledSetDeconstructionTime
So conceptually this equates to allowing each event to have sub-events, each location to have sub-locations, each ‘set list’ to have multiple musicians play over the course of the night on rotating stages. This structure allows for scaling to managing both bigger or smaller events.
{clientID} -> {clientID, clientLName, cFName, cPhone, cStreet, cCity, cState, cZip}
{eventID} -> {eventID, eventDate, eventStartTime, eventDuration, eventType, numberGuests, locationID, linenColorRequested, floristID, musicID, entertainerID, photographerID, menuID, totalPriceID}
{locationID} -> {locationID, locationName, locationStreet, locationCity, locationState, locationZip}
{numberGuests} -> {numberGuests, numberWaiters, numberBartenders}
{floristID} -> {floristID, floristName, floristPhone, floristCost, floristPrice}
{musicID} -> {musicID, musicContact, musicContactPhone, musicType, musicCost, musicPrice}
{entertainerID} -> {entertainerID, entertainerName, entertainerPhone, entertainerType, entertainerCost, entertainerPrice}
{photographerID} -> {photographerID, photographerName, photographerPhone, photographerCost, photographerPrice}
{menuID} -> {menuID, menuNumberChosen, menuAppetizer, menuSalad, menuMain, menuDessert}
{totalPriceID} -> {totalPriceID, baseEstimate, intangibles, quoteRange, totalPrice}

b. What are the candidate keys for this relation? Identify the primary keys

Short: my primary keys are {clientID}, {eventID}, {locationID}, {floristID}, {musicID}, {entertainerID}, {photographerID}, {menuID}
Long: To avoid cumbersome concatenation to form uniqueness necessary for keys, I constructed an identityID for each abstracted unique concept. Thus a band call “Red Hot Chili Peppers” would be assigned a musicID and their agent’s contacts would be included (From this example we see that we can also add more columns to the music table, such as musicDescription or musicName to differentiate between the DJ/Bands name and the agent who you will speak with on the phone. Thus, my primary keys are {clientID}, {eventID}, {locationID}, {floristID}, {musicID}, {entertainerID}, {photographerID}, {menuID}
Note: I could also implement {numberGuests} in a guest-number-to-needed-servers mapping, or it could be formulae driven. I’m not in the event planning business so maybe other factors need to be considered, like the venue, in deriving the needed amount of support staff. I would like this number to be auto-estimated, then a final number decided upon, so perhaps that actual implementation would be slightly modified.

c. Is this relation in third normal form? if not find a 3NF lossless join decomposition of College that preserves dependencies.

Yes and no. Depending on how strict you are with allowing multiple entertainers or musicians. This doesn’t happen often, however it could happen that an event has multiple entertainers for example, thus it would make sense to add an additional layer of tabling that is called say:
{entertainmentScheduledID} -> {entertainmentScheduledID, entertainmentScheduledDate, entertainmentScheduledStartTime, entertainmentScheduledDuration, entertainmentScheduledSetupTime, entertainmentScheduledSetDeconstructionTime, entertainerID }
The same can be done with musicians, or location, photographers, and even the event itself – this adds an element of pseudo-recursion that can help organize an event on any dimension of the time-space continuum. This adds a level of flexibility and a level of complexity in coordinating across groups in space-time, which will increase head count on a coordinator, and likely additional intangible costs.

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.