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:

a) Relation ITEM(ItemName VARCHAR(30), Price DECIMAL(6,2)) with ItemName as PRIMARY KEY — the primary-key (unique, not-NULL) restriction guarantees each item and its price is stored only once.

b) A new relation is needed (quantity is a property of a purchase, not of an item): BILL(BillNo INT PRIMARY KEY, ItemName VARCHAR(30), Quantity INT) — BillNo as primary key satisfies (i) "same bill cannot be generated for different orders", and ItemName as foreign key referencing ITEM(ItemName) satisfies (ii) "bill only for available items". …

a) Storing items and prices — relation, attributes, restriction

Item name and price describe the item entity, so use one relation:

CREATE TABLE ITEM (
  ItemName VARCHAR(30) PRIMARY KEY,   -- unique + NOT NULL: each item stored exactly once
  Price    DECIMAL(6,2) NOT NULL
);

• Attributes and types: ItemName VARCHAR(30) (names are text), Price DECIMAL(6,2) (money should be exact decimal, not FLOAT).
• Restriction: declare ItemName as the PRIMARY KEY. The primary key's uniqueness + not-NULL rules are exactly the requirement "each item and its price should be stored only once" — a second INSERT of ('Samosa', …) is rejected as a duplicate key. (An alternative design adds a numeric ItemCode as primary key with ItemName UNIQUE; either way, the restriction doing the work is a unique key on the item.)

b) Recording quantity for billing — a new relation

Quantity purchased is not a property of an item — it is a property of a purchase (bill). Putting Quantity inside ITEM would overwrite it at every sale and could not keep two students' purchases apart. So a new relation is required:

CREATE TABLE BILL (
  BillNo   INT PRIMARY KEY,           -- restriction (i): a bill number can never repeat
  ItemName VARCHAR(30) NOT NULL,
  Quantity INT NOT NULL CHECK (Quantity > 0),
  FOREIGN KEY (ItemName) REFERENCES ITEM(ItemName)   -- restriction (ii)
);

• Attributes and types: BillNo INT, ItemName VARCHAR(30), Quantity INT.
• Primary key = BillNo. Uniqueness of the primary key means the same bill number cannot be generated for two different orders — restriction (i) enforced by the DBMS itself.
• Foreign key = ItemName → ITEM(ItemName). Referential integrity means a bill can name only an item that already exists in ITEM — i.e. bills can be generated only for items available in the canteen — restriction (ii). Trying INSERT INTO BILL VALUES (101, 'Pizza', 2); when Pizza is not in ITEM fails with a foreign-key violation.
• The bill amount needs no stored column — it is derived on demand:

SELECT B.BillNo, B.ItemName, B.Quantity, B.Quantity * I.Price AS Amount
FROM   BILL B JOIN ITEM I ON B.ItemName = I.ItemName;