7. 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. Each dinner serves many members, and each member may attend many dinners. A member receives many invitations, and each invitation is mailed to many members. 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. Because the manager is not a database expert, the first attempt at creating the data- base uses the structure shown in Table P6.7. a. Given the table structure illustrated in Table P6.7, write the relational schema and draw its dependency diagram. Label all transitive and/or partial dependen- cies. (Hint: This structure uses a composite primary key.) 270 Copyright 2019 Cegane Leaming. All Rights Reserved. May not be copied, scanned o duplicated, whole or in part. Due to electronic ciats, some third puy content may be suppressed from the Book onder eChapteri. Editorial revieo has deemed that any suppressed content does not materially attect the overall learnina experience. L'engage Leaming reserves the right to remove additional content any time is subsequent cights Destions require it. Chapter 6 Normalization of Database Tables 239 SAMPLE VALUE 214 Alice B. Vandervoort 325 Meadow Park Murkywater 12345 SAMPLE VALUE 235 Gerald M. Gallega 123 Rose Court Highlight 12349 SAMPLE VALUE 214 Alice B.VanderVoort 325 Meadow Park Murkywater 12345 10 TABLE P6.7 ATTRIBUTE NAME MEMBER_NUM MEMBER_NAME MEMBER_ADDRESS MEMBER CITY MEMBER_ZIPCODE INVITE NUM INVITE DATE ACCEPT_DATE DINNER_DATE DINNER ATTENDED DINNER_CODE DINNER_DESCRIPTION ENTREE_CODE ENTREE_DESCRIPTION DESSERT_CODE DESSERT_DESCRIPTION 23-Feb-2018 12-Mar-2018 23-Feb-2018 27-Feb-2018 15-Mar-2018 27-Feb-2018 15-Mar-2018 17-Mar-2018 15-Mar-2018 Yes Yes No DIS DI5 DI2 Glowing Sea Delight Glowing Sea Delight Ranch Superb EN3 EN3 ENS Stuffed crab Stuffed crab Marinated steak DES DE5 DE2 Chocolate mousse with raspberry sauce Cherries jubilee Apple pie with honey crust b. Break up the dependency diagram you drew in Problem 7a to produce depen- dency diagrams that are in 3NF, and write the relational schema. (Hint: You might have to create a few new attributes. Also, make sure that the new dependency dia grams contain attributes that meet proper design criteria; that is, make sure there are no multivalued attributes, that the naming conventions are met, and so on.) c. Using the results of Problem 7b, draw the Crow's Foot ERD. 7. 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. Each dinner serves many members, and each member may attend many dinners. A member receives many invitations, and each invitation is mailed to many members. 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. Because the manager is not a database expert, the first attempt at creating the data- base uses the structure shown in Table P6.7. a. Given the table structure illustrated in Table P6.7, write the relational schema and draw its dependency diagram. Label all transitive and/or partial dependen- cies. (Hint: This structure uses a composite primary key.) 270 Copyright 2019 Cegane Leaming. All Rights Reserved. May not be copied, scanned o duplicated, whole or in part. Due to electronic ciats, some third puy content may be suppressed from the Book onder eChapteri. Editorial revieo has deemed that any suppressed content does not materially attect the overall learnina experience. L'engage Leaming reserves the right to remove additional content any time is subsequent cights Destions require it. Chapter 6 Normalization of Database Tables 239 SAMPLE VALUE 214 Alice B. Vandervoort 325 Meadow Park Murkywater 12345 SAMPLE VALUE 235 Gerald M. Gallega 123 Rose Court Highlight 12349 SAMPLE VALUE 214 Alice B.VanderVoort 325 Meadow Park Murkywater 12345 10 TABLE P6.7 ATTRIBUTE NAME MEMBER_NUM MEMBER_NAME MEMBER_ADDRESS MEMBER CITY MEMBER_ZIPCODE INVITE NUM INVITE DATE ACCEPT_DATE DINNER_DATE DINNER ATTENDED DINNER_CODE DINNER_DESCRIPTION ENTREE_CODE ENTREE_DESCRIPTION DESSERT_CODE DESSERT_DESCRIPTION 23-Feb-2018 12-Mar-2018 23-Feb-2018 27-Feb-2018 15-Mar-2018 27-Feb-2018 15-Mar-2018 17-Mar-2018 15-Mar-2018 Yes Yes No DIS DI5 DI2 Glowing Sea Delight Glowing Sea Delight Ranch Superb EN3 EN3 ENS Stuffed crab Stuffed crab Marinated steak DES DE5 DE2 Chocolate mousse with raspberry sauce Cherries jubilee Apple pie with honey crust b. Break up the dependency diagram you drew in Problem 7a to produce depen- dency diagrams that are in 3NF, and write the relational schema. (Hint: You might have to create a few new attributes. Also, make sure that the new dependency dia grams contain attributes that meet proper design criteria; that is, make sure there are no multivalued attributes, that the naming conventions are met, and so on.) c. Using the results of Problem 7b, draw the Crow's Foot ERD