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.

A member may attend many dinners, and each dinner may be attended by many members.

Because the manager is not a database expert, the first attempt at creating the database uses the structure shown in Table I:

Table I Sample RESERVATION Records

Attribute Name

Sample Value

Sample Value

Sample Value

MEMBER_NUM

214

235

214

MEMBER_NAME

Alice B. VanderVoort

Gerald M. Gallega

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_NUM

8

9

10

INVITE_DATE

23-Feb-2010

12-Mar-2010

23-Feb-2010

ACCEPT_DATE

27-Feb-2010

15-Mar-2010

27-Feb-2010

DINNER_DATE

15-Mar-2010

17-Mar-2010

15-Mar-2010

DINNER_ATTENDED

Yes

Yes

No

DINNER_CODE

DI5

DI5

DI2

DINNER_DESCRIPTION

Glowing sea delight

Glowing sea delight

Ranch Superb

ENTREE_CODE

EN3

EN3

EN5

ENTREE_DESCRIPTION

Stuffed crab

Stuffed crab

Marinated steak

DESERT_CODE

DE8

DE5

DE2

DESERT_DESCRIPTION

Chocolate mousse

with raspberry sauce

Cherries jubilee

Apple pie with honey

crust

Given the table structure illustrated in Table I, draw its dependency diagram. Label all transitive and/or partial dependencies. Justify each partial or transitive dependency (Hint: This structure uses a composite primary key.)

Break up the dependency diagram you drew in Problem 7a to produce dependency diagrams that are in 3NF. (Hint: You might have to create a few new attributes. Also, make sure that the new dependency diagrams contain attributes that meet proper design criteria; that is, make sure that there are no multivalued attributes, that the naming conventions are met, and so on.)

Using the results of Problem b, draw the Crow’s Foot ERD. You can hand draw it if you do not have the required software.

Transcribed Image Text:MEMBER_ZIPCODE 12345 12349 12345 INVITE_NUM 10 INVITE_DATE 23-Feb-2010 12-Mar-2010 23-Feb-2010 ACCEPT DATE 27-Feb-2010 15-Mar-2010 27-Feb-2010 DINNER_DATE 15-Mar-2010 17-Mar-2010 15-Mar-2010 DINNER_ATTENDED Yes Yes No DINNER_CODE DIS DIS DI2 DINNER_DESCRIPTION Glowing sea delight Glowing sea delight Ranch Superb ENTREE_CODE EN3 EN3 ENS ENTREE_DESCRIPTION Stuffed crab Stuffed crab Marinated steak DESERT_CODE DEB DES DE2 DESERT_DESCRIPTION Chocolate mousse Cherries jubilee Apple pie with honey with raspberry sauce crust a. Given the table structure illustrated in Table I, draw its dependency diagram. Label all transitive and/or partial dependencies. Justify each partial or transitive dependency (Hint: This structure uses a composite primary key.) b. Break up the dependency diagram you drew in Problem 7a to produce dependency diagrams that are in 3NF. (Hint: You might have to create a few new attributes. Also, make sure that the new dependency diagrams contain attributes that meet proper design criteria; that is, make sure that there are no multivalued attributes, that the naming conventions are met, and so on.) c. Using the results of Problem b, draw the Crow's Foot ERD. You can hand draw it if you do not have the required software.
Transcribed Image Text: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. • A member may attend many dinners, and each dinner may be attended by many members. Because the manager is not a database expert, the first attempt at creating the database uses the structure shown in Table I: Table I Sample RESERVATION Records Attribute Name Sample Value Sample Value Sample Value MEMBER_NUM 214 235 214 MEMBER_NAME Alice B. VanderVoort Gerald M. Gallega Alice B. VanderVoort ww.aw MEMBER_ADDRESS 325 Meadow Park 123 Rose Court 325 Meadow Park MEMBER_CITY Murkywater Highlight Murkywater Page 1 of 2

Question

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.
A member may attend many dinners, and each dinner may be attended by many members.

Because the manager is not a database expert, the first attempt at creating the database uses the structure shown in Table I:

Table I Sample RESERVATION Records

Attribute Name	Sample Value	Sample Value	Sample Value
MEMBER_NUM	214	235	214
MEMBER_NAME	Alice B. VanderVoort	Gerald M. Gallega	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_NUM	8	9	10
INVITE_DATE	23-Feb-2010	12-Mar-2010	23-Feb-2010
ACCEPT_DATE	27-Feb-2010	15-Mar-2010	27-Feb-2010
DINNER_DATE	15-Mar-2010	17-Mar-2010	15-Mar-2010
DINNER_ATTENDED	Yes	Yes	No
DINNER_CODE	DI5	DI5	DI2
DINNER_DESCRIPTION	Glowing sea delight	Glowing sea delight	Ranch Superb
ENTREE_CODE	EN3	EN3	EN5
ENTREE_DESCRIPTION	Stuffed crab	Stuffed crab	Marinated steak
DESERT_CODE	DE8	DE5	DE2
DESERT_DESCRIPTION	Chocolate mousse with raspberry sauce	Cherries jubilee	Apple pie with honey crust

Given the table structure illustrated in Table I, draw its dependency diagram. Label all transitive and/or partial dependencies. Justify each partial or transitive dependency (Hint: This structure uses a composite primary key.)
Break up the dependency diagram you drew in Problem 7a to produce dependency diagrams that are in 3NF. (Hint: You might have to create a few new attributes. Also, make sure that the new dependency diagrams contain attributes that meet proper design criteria; that is, make sure that there are no multivalued attributes, that the naming conventions are met, and so on.)
Using the results of Problem b, draw the Crow’s Foot ERD. You can hand draw it if you do not have the required software.

MEMBER_ZIPCODE
12345
12349
12345
INVITE_NUM
10
INVITE_DATE
23-Feb-2010
12-Mar-2010
23-Feb-2010
ACCEPT DATE
27-Feb-2010
15-Mar-2010
27-Feb-2010
DINNER_DATE
15-Mar-2010
17-Mar-2010
15-Mar-2010
DINNER_ATTENDED
Yes
Yes
No
DINNER_CODE
DIS
DIS
DI2
DINNER_DESCRIPTION
Glowing sea delight
Glowing sea delight
Ranch Superb
ENTREE_CODE
EN3
EN3
ENS
ENTREE_DESCRIPTION
Stuffed crab
Stuffed crab
Marinated steak
DESERT_CODE
DEB
DES
DE2
DESERT_DESCRIPTION
Chocolate mousse
Cherries jubilee
Apple pie with honey
with raspberry sauce
crust
a. Given the table structure illustrated in Table I, draw its dependency diagram. Label all transitive
and/or partial dependencies. Justify each partial or transitive dependency (Hint: This structure
uses a composite primary key.)
b. Break up the dependency diagram you drew in Problem 7a to produce dependency diagrams
that are in 3NF. (Hint: You might have to create a few new attributes. Also, make sure that the
new dependency diagrams contain attributes that meet proper design criteria; that is, make
sure that there are no multivalued attributes, that the naming conventions are met, and so on.)
c. Using the results of Problem b, draw the Crow's Foot ERD. You can hand draw it if you do not
have the required software.

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.
• A member may attend many dinners, and each dinner may be attended by many members.
Because the manager is not a database expert, the first attempt at creating the database uses the
structure shown in Table I:
Table I Sample RESERVATION Records
Attribute Name
Sample Value
Sample Value
Sample Value
MEMBER_NUM
214
235
214
MEMBER_NAME
Alice B. VanderVoort
Gerald M. Gallega
Alice B. VanderVoort
ww.aw
MEMBER_ADDRESS
325 Meadow Park
123 Rose Court
325 Meadow Park
MEMBER_CITY
Murkywater
Highlight
Murkywater
Page 1 of 2

Accepted Answer

a)Given the table structure illustrated in Table I, draw its dependency diagram. Label all…