Question

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 question:

In order to generate the 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 relationship is required, decide the appropriate name and data type for attributes. Also, identify the appropriate primary key and foreign key so that the following two restrictions are satisfied:

1. The same bill cannot be generated for different orders.
2. Bill can be generated only for available items in the canteen.

Answer 1

Yes, the item sale information should be stored in a separate relation, say SaleOrders.

Orderno Itemno	Unique non-null order number Itemno from Items table	Primary Key
Qty_purchased Order_Date	Non-null value of qty sold of an item Date of the order