Notes

Table Design
The initial table design of our system is:
Table : food_menu{food_id, menu_id, food_price, menu_name, food_name, description, food_description, food_type}


Composite Primary keys with justification
We made composite primary key composed of two key attributes. i.e food_id and menu_id.

Partial Dependency
In our entity set we see that menu_name and description is partially dependent on menu_id.
And food_name , food_price, food_description, food_type is partially dependent on food_id.


Functional Dependency¬¬¬
Table: menu{menu_id, food_id, menu_name, description }
{menu_id}{ menu_name }

Table: food { food_id, food_name, food_type, food_description, food_price}
{food_id}{food_name}
{food_id}{price}

Normalization

1NF test
For 1NF test there should not be any multiple values in the single column.
Table : food_menu{food_id, menu_id, food_price, menu_name, food_name, description, food_description, food_type}
This table initially satisfies 1NF condition. As there are no multiple values in single column.


2NF test
For 2NF test non key attribute should be totally dependent on composite primary key considering there exists composite primary key. In our entity set we see that menu_name and description is partially dependent on menu_id. food_name , food_price, food_description, food_type is partially dependent on food_id.
Now we use 2NF rule to create two table ie food and menu as food_id and menu_id as their primary key respectively.

Table: menu{menu_id, food_id, menu_name, description }
Table: food { food_id, food_name, food_type, food_description, food_price}

3NF test
For 3NF test there should not exist any transitive dependency in the table. The table after 2NF test is given below:
Table: menu{menu_id, menu_name, description }
Table: food { food_id, food_name, food_type, food_description, food_price}
menu_name is dependent on menu_id and no other attribute is dependent on menu_name. So the table for menu remains same.
food_name is dependent on food_id. And food_price is also dependent on food_id. So there is no transitive dependency in our table. Also the table of the food remains same as the 2NF.


Primary Key¬¬
After the normalization process we created two tables food and menu with food_id and menu_id as their primary keys respectively.The underlined attributes in each entity are the primary keys of the corresponding entities because it uniquely identifies each entity.
1. menu (menu_id, food_id, menu_name, description)
menu_id is selected as the primary key because it can have a distinct value unlike other attributes in the table employee. i.e food_id, menu_name, price, description. There are several menus in our system. menu_id can uniquely identify the menus among various menus.
2. food (food_id, food_name, food_type, food_description, food_price)
There are several food among the food table. For a example Mo:Mo cannont uniquely identify the the food item. There are other variety of the similar food. We used food_id as the single attribute key for our primary key.