The school canteen wants to maintain records of items available in the school canteen and generate bills when students purchase any item from the canteen. The school wants to create a canteen database to keep track of items in the canteen and the items purchased by students. Design a database by answering the following questions:

a) To store each item name along with its price, what relation should be used?
Decide appropriate attribute names along with their data type. Each item and its price should be stored only once. What restriction should be used while defining the relation?
Ans: We will create new relation BILL to keep bill details as per the scheme given below-

We will apply Primary key constraint on ICode attribute to store each item and its price only once.

b) In order to generate bill, we should know the quantity of an item purchased. Should this information be in a new relation or a part of the previous relation? If a new relation is required, decide appropriate name and data type for attributes. Also, identify appropriate primary key and foreign key so that the following two restrictions are satisfied:     
(i) Bill can be generated only for available items in the canteen.
(ii) The same bill cannot be generated for different orders.

Ans: We will create new relation BILL to keep bill details as per the scheme given below-

(i) We will apply Primary key to BillNo attribute to avoid same bill for different order.
(ii) We will make ICode to Foreign key (References ITEM relation) to generate bill for available items.

c) The school wants to find out how many calories students intake when they order an item. In which relation should the attribute ‘calories’ be stored?

Ans: We have use attribute ‘calories’ in both the relations.