5. What are the limitations of file system that can be overcome by a relational DBMS?
Answer: Limitations of file system overcome by DBMS are :-
(a) Difficulty in data access (b) Data Redundancy (c) Data Inconsistency (d) Data Isolation (e) Data dependence.
6. A school has a rule that each student must participate in a sports activity. So each one should give only one preference for sports activity. Suppose there are five students in a class, each having a unique roll number. The class representative has prepared a list of sports preferences as shown below.
Answer the following:
a) Roll no 24 may not be interested in sports. Can a NULL value be assigned to that student’s preference field?
Answer: Yes, More than one student can have same sports preference. It means, preference column is not a primary key so that we can give the NULL value in preference column.
b) Roll no 17 has given two preferences sports. Which property of relational DBMS is violated here? Can we use any constraint or key in the relational DBMS to check against such violation, if any?
Answer: Each student suppose to give only one preference, as given. Primary Key constraint can be use to check such types of violation.
c) Kabaddi was not chosen by any student. Is it possible to have this tuple in the Sports Preferences relation?
Answer: A sport preference can not be entered unless it has a value for the Rollno column as primary key cannot left blank.
7. In another class having 2 sections, the two respective class representatives have prepared 2 separate Sports Preferences tables, as shown in the figure (below):
Sports preference of section 1 (arranged on roll number column)
Sports preference of section 2 (arranged on Sports name column, and column order is also different)
Are the states of both the relations equivalent? Justify.
Answer: Yes, the states of both the relations are equivalent as the order of rows and columns doe not matter and data is just the same in both the relations.
8. 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?
Answer: To store item details, we can create 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 contraints are :
- Item_no – must be unique and non-null for each row.
- Description and Price cannot be blank or null.
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:
i) The same bill cannot be generated for different orders.
ii) Bill can be generated only for available items in the canteen.
Answer: 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) 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: In Items table
Class 11 Informatics Practices – NCERT Book exercise Solution
