Q.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:

This is a plain theory question — it asks you to design a relational database schema, justify your choices, and explain keys and normalisation.

(a) To store each item name and its price exactly once, use a relation called Item. The attributes are:

• item_id — INTEGER (or VARCHAR if codes are alphanumeric), primary key
• item_name — VARCHAR(50), NOT NULL, UNIQUE (so the same item name is not entered twice)
• price — DECIMAL(6,2) (or FLOAT), NOT NULL

The restriction is that item_name must be UNIQUE — this ensures each item and its price are stored only once, avoiding redundancy. The primary key item_id also guarantees each row is unique.

(b) Quantity purchased should be in a new relation called Bill. The Item relation stores item master data (one row per item); the purchase quantity is transactional data that changes with every order. Mixing them would violate 1NF (repeating groups) and cause massive redundancy.

Attributes for Bill:

• bill_no — INTEGER, primary key (ensures restriction i: each bill is unique)
• item_id — INTEGER, foreign key referencing Item(item_id) (ensures restriction ii: only available items can appear)
• quantity — INTEGER, NOT NULL, CHECK (quantity > 0)
• purchase_date — DATE (optional but practical)

The primary key bill_no guarantees that the same bill number cannot be generated for different orders. The foreign key item_id → Item(item_id) ensures that a bill can only reference an item that actually exists in the canteen.

--- …

This question tests your understanding of database normalization — the idea that you should store each fact in exactly one place to avoid redundancy and update anomalies. The canteen scenario is a classic one-to-many relationship: one item can appear in many bills, but each bill line refers to exactly one item.

Let's break it down part by part.

(a) To store each item name along with its price, you need a relation that captures the item master data. Since each item and its price should be stored only once, this is a single table with a unique identifier for each item.

Relation name: Item

Attributes and data types:

• item_id — INT (or INTEGER), primary key. This uniquely identifies each item.
• item_name — VARCHAR(100) (or TEXT). Stores the name of the item.
• price — DECIMAL(10,2) (or FLOAT/NUMERIC). Stores the price in rupees with two decimal places.

Restriction: The primary key constraint on item_id ensures each item is stored only once. Additionally, you could add a UNIQUE constraint on item_name if item names are also unique, but the question only requires that each item and its price be stored once — the primary key achieves that.

(b) The quantity of an item purchased is transactional data — it changes with every purchase. This should not be in the Item relation because that would force you to update the item's row every time someone buys it, which is messy and loses history. Instead, create a new relation for the bill/purchase details.

Relation name: Bill

Attributes and data types:

• bill_no — INT, primary key. Uniquely identifies each bill.
• item_id — INT, foreign key referencing Item(item_id). Links the purchase to a specific item.
• quantity — INT (or SMALLINT). Stores how many units of that item were purchased.
• purchase_date — DATE (optional but practical). Not required by the question, but good practice.

Primary key: bill_no — this satisfies restriction (i): the same bill number cannot appear twice, so different orders get different bill numbers.

Foreign key: item_id references Item(item_id) — this satisfies restriction (ii): a bill can only be generated for an item that exists in the Item table. The database will reject any attempt to insert a bill with an item_id that is not present in Item. …