KnowledgeBoat Logo
|

Computer Science

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

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

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

(c) 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?

Relational Database

3 Likes

Answer

(a) To store each item name along with its price in the canteen database, we can create a relation (table) called "Items" with the following attributes:

Items table

AttributesDatatypeConstraints
ItemNoIntegerPrimary Key, Unique, non-null value
ItemNameVARCHARNon-null value
PriceFloatNon-null value

The restriction that should be used while defining the "Items" relation is to set the "ItemNo" attribute as the primary key. This ensures that each item number is unique and that each item and its price are stored only once in the database.

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

SaleOrders table

AttributesDatatypeConstraints
ordernointegerPrimary Key Unique, Non-null value
ItemNointegerForeign key,Unique, Non-null value
QuantityintegerNon-null value
PriceFloatNon-null value

With this design, we satisfy both restrictions:

Each order has a unique OrderNo, ensuring that the same bill cannot be generated for different orders. The foreign key constraint on ItemNo ensures that bills can only be generated for available items in the canteen.

(c) 'Calories' should be stored in the "Items" table because they are directly associated with specific items.

Answered By

3 Likes

Related Questions