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:
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?
Relation Name: ITEMS Attributes and Data Types:
- ItemName - VARCHAR (String)
- Price - DECIMAL or INTEGER
To ensure each item and its price is stored only once, the restriction Primary Key should be used on the attribute ItemName. This ensures that no duplicate item names can be entered in the relation.
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) The same bill cannot be generated for different orders. ii) Bill can be generated only for available items in the canteen.
Yes, a new relation should be created because an item can be purchased multiple times by different students, and storing quantity in the ITEMS relation would cause data redundancy.
Relation Name: PURCHASES (or BILLS) Attributes and Data Types:
- BillNo - INTEGER
- ItemName - VARCHAR (String)
- Quantity - INTEGER
Primary Key: BillNo (satisfies restriction i - ensures same bill cannot be generated for different orders)
Foreign Key: ItemName referencing ITEMS(ItemName) (satisfies restriction ii - ensures bill can be generated only for available items in the canteen)
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?
The attribute 'calories' should be stored in the ITEMS relation. This is because calories is a property of the item itself, not of the purchase. Each item has a fixed calorie value regardless of how many times it is purchased. Adding 'calories' to the ITEMS relation allows the school to calculate calorie intake by multiplying calories per item with quantity purchased from the PURCHASES relation.
Explanation
This question tests understanding of relational database design principles including normalization, primary keys, and foreign keys. Part (a) requires identifying that ITEMS needs a Primary Key to prevent duplicate entries. Part (b) tests understanding of why separate relations are needed (normalization) and how Primary Key ensures unique bills while Foreign Key enforces referential integrity. Part (c) tests understanding of where attributes belong based on their relationship to entities - calories is an inherent property of food items, not purchases.