Question

1. The manager of a company dinner club would like to have an information system that assists him to plan the meals and to keep track of who attends the dinners, and so on.

Because the manager is not an IS expert, the following table is used to store the information. As a member can attend many dinners and a member will not attend more than 1 dinner on the same date, the primary key of the following table is Member ID and Dinner ID. Dinners can have many courses, from one-course dinner to as many courses as the chef desired.

MemberID	MemberName	MemberAddress	DinnerID	DinnerDate	VenueCode	VenueDescription	FoodCode	FoodDescription
214	Peter Wong	325 Meadow Park	D0001	02/02/2020	L01	Grand_Ball_Room	EN3	Stu ed crab
							DEB
235	Mary Lee		D0002	02/02/2020	L02	Café	EN5
							DEB
250	Peter Wong		D0003	03/03/20	L01	Grand_Ball_Room	SO1	Marinated Steak
							EN5	Chocolate Mousse
							DE2	Key Lime Pie
235	Mary Lee		D0003	03/03/2020	L02	Café	S01	Pumpkin Soup
							SA2	Marinated Steak
							DE2	Apple Pie
300	Paul Lee		D0004	03/03/2020	L03	Petit_Ball_Room	SA2	Apple Pie

1. Transform the table above into first normal form 1NF. (To do this, check if there are multivalued attributes and transform the table to get rid of them)
   1. Identify the dependencies and which type they are (full dependencies, partial dependencies, transitive dependencies
2. Transform the table above into second normal form 2NF. (To do this separate partial dependencies into separate tables).
3. Transform the table above into third normal form 3NF. (To do this, remove the transitive dependencies by creating separate tables and relate them with the common attribute)

Answer 1

a) The multivalued dependency here is "Food Cod"

this is how the table will look in 1NF

b) the dependencies are :

MemberID --> Member name,Member Address,DinnerID,VenueCode,VenueDescription,FoodCod (PARTIAL DEPENDECY)

DinnerID --> DinnerDate(PARTIAL DEPENDENCY)

MemberID--> VenueCode,VenueDescription (TRANSITIVE DEPENDENCY)

MemberID,DinnerID --> Member name,Member Address,DinnerDate,VenueCode,VenueDescription,FoodCod

MemberName --> VenueCode,VenueDescription (FULL DEPENDENCY)

Therefore Primary Key is [MemberID,DinnerID] since from these two we can find all other attributes.

dependecy No.2 is a partial dependency since dinnerID is part of candidate key

c) To convert to 2NF we need to remove the two partial dependecies

d) to convert to 3NF we need to remove the transitive dependency

so the final table are as follows :