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

1. 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?
2. In order to generate a 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:
   i) The same bill cannot be generated for different orders.
   ii) Bill can be generated only for available items in the canteen.
3. 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?

Answer 1

a) To store item details, we can create an Items table.

Items_table	Code of Item
Item_No Description Price	Unique, Non-null value Description of Item Non-null price of the item

The restrictions and constraints are:

• Item_no – must be unique and non-null for each row.
• Description and Price cannot be blank or null.

b) 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

c) In Items table.