Business Scenario:

Suppose you are given the following business rules to form the basis for a database design. The database must enable the manager of a company dinner club to mail invitations to the club$$s members, to plan the meals, to keep track of who attends the dinners, and so on$.$

o Each dinner serves many members, and each member may attend many dinners. $($Hint: This tells that the relationship between dinner and member is man$-$to$-$many.

o A member receives many invitations $($for attending dinner$),$ and each invitation is mailed to many members. $($Hint: This means that invitation is used as entity to implement the many$-$to$-$many relationship mentioned above. As such, it has a composite primary key: MEMBER$\_$NUM and DINNER$\_$CODE$)$

o A dinner is based on a single entree, but an entree may be used as the basis for many dinners. For example, a dinner may be composed of a fish entree, rice, and corn, or the dinner may be composed of a fish entree, a baked potato, and string beans.

o A dinner ends with single dessert, a dissert may be used for many dinners.

Goal:

The manager is not a database expert. As database professionals, we aim to create the database based on the structure shown in the following table. Accordingly, we want to accomplish the normalization process for this database.$\backslash$table$[[$Attribute Name,Sample Value,Sample Value,Sample Value$],[$MEMBER$\_$NUM,$214,235,214],[$MEMBER$\_$NAME,$\backslash$table$[[$Alice$,$B$.],[$VanderVoort$,]],$Gerald M$.$ Gallega,$\backslash$table$[[$Alice$,$B$.],[$VanderVoort$,]]],[$MEMBER$\_$ADDRESS,$325$ Meadow Park,$123$ Rose Court,$325$ Meadow Park$],[$MEMBER$\_$CITY,Murkywater,Highlight,Murkywater$],[$MEMBER$\_$ZIPCODE,$12345,12349,12345],[$INVITE$\_$DATE,$23-$Feb$-2022,12-$Mar$-2022,23-$Feb$-2022],[$ACCEPT$\_$DATE,$27-$Feb$-2022,15-$Mar$-2022,27-$Feb$-2022],[$DINNER$\_$DATE,$15-$Mar$-2022,17-$Mar$-2022,15-$Mar$-2022],[$DINNER$\_$ATTENDED,Yes,Yes,No$],[$DINNER$\_$CODE,DI$5,$DI$5,$DI$2],[$DINNER$\_$DESCRIPTION,Glowing sea delight,$\backslash$table$[[$Glowing sea$],[$delight$]],$Ranch Superb$],[$ENTREE$\_$CODE,EN$3,$EN$3,$EN$5],[$ENTREE$\_$DESCRIPTION,Stuffed crab,Stuffed crab,Marinated steak$],[$DESSERT$\_$CODE,DE$8,$DE$5,$DE$2],[$DESSERT$\_$DESCRIPTION,$\backslash$table$[[$Chocolate mousse$],[$with raspberry$],[$sauce$]],$Cherries jubilee,$\backslash$table$[[$Apple pie with$],[$honey crust$]]]]$

Task:

Based on the above table, write the relational schema $(1$NF$)$ and explain all partial and$/$or transitive dependencies. $($Hint: The table is in $1NF$ and has a composite PK as explained in the business rules. No need to deal with: MEM$\_$ZIP $$ MEM$\_$CITY$)$

Solution: