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 constraints 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 the 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
9. An organisation wants to create a database EMPDEPENDENT to maintain following details about its employees and their dependent.
EMPLOYEE(AadharNumber, Name, Address, Department,EmployeeID)
DEPENDENT(EmployeeID, DependentName, Relationship)
a) Name the attributes of EMPLOYEE, which can be used as candidate keys.
Answer: AadharNumber, EmployeeId
b) The company wants to retrieve details of dependent of a particular employee. Name the tables and the key which are required to retrieve this detail.
Answer: Employee and Dependent tables linked via key EmployeeId
c) What is the degree of EMPLOYEE and DEPENDENT relation?
Answer:
Degree of Employee : 5,
Degree of Dependent : 3
10. School uniform is available at M/s Sheetal Private Limited. They have maintained SCHOOL_UNIFORM Database with two relations viz. UNIFORM and COST. The following figure shows database schema and its state.
a) Can they insert the following tuples to the UNIFORM Relation? Give reasons in support of your answer.
i) 7, Handkerchief, NULL
ii) 4, Ribbon, Red
iii) 8, NULL, White
Answer: (i) Yes, it can be inserted.
(ii) Yes, It can be inserted,
(iii) No. It cannot be inserted as UName cannot be NULL
b) Can they insert the following tuples to the COST Relation? Give reasons in support of your answer.
i) 7, S, 0
ii) 9, XL, 100
Answer: (i) NO, it cannot be inserted as price must be > 0.
(ii) Yes, it can be inserted.
11. In a multiplex, movies are screened in different auditoriums. One movie can be shown in more than one auditorium. In order to maintain the record of movies, the multiplex maintains a relational database consisting of two relations viz. MOVIE and AUDI respectively as shown below:
Movie(Movie_ID, MovieName, ReleaseDate)
Audi(AudiNo, Movie_ID, Seats, ScreenType, TicketPrice)
a) Is it correct to assign Movie_ID as the primary key in the MOVIE relation? If no, then suggest an appropriate primary key.
Answer: Yes, It is correct to assign Movie_ID as the primary key.
b) Is it correct to assign AudiNo as the primary key in the AUDI relation? If no, then suggest appropriate primary key.
Answer: No, AudiNo along with MovieID should be made the primary key. That is, the relation should have a composite primary key as AudiNo + Movie_ID
c) Is there any foreign key in any of these relations?
Answer: MovieID
12. For the below-given database STUDENT-PROJECT, answer the following:
a) Name the primary key of each table.
Answer: Table STUDENT : RollNo ,
Table PROJECT : ProjectNo,
Table PROJECT_ASSIGNED : Registration_ID
b) Find foreign key(s) in table PROJECT-ASSIGNED.
Answer: ProjectNo
c) Is there any alternate key in table STUDENT? Give justification for your answer.
Answer: Name can be an alternate key as it stores unique values for each row.
d) Can a user assign a duplicate value to the field RollNo of STUDENT table? Justify.
Answer: No, RollNo being the primary key must be unique values for each row.
13. For the above-given database STUDENT-PROJECT, can we perform the following operations?
a) Insert a student record with a missing roll number value.
Answer: No
b) Insert a student record with missing registration number value.
Answer: Yes
c) Insert a project detail without submission-date.
Answer: Yes
d) Insert a record with registration ID IP-101-19 and ProjectNo 206 in table PROJECT-ASSIGNED.
Answer: No, because Registration Id IP-101-19 does not exists in Student Table, and ProjectNo 206 is also does not exists in Project Table.
Class 12 Computer Science – NCERT Exercise Solutions
Class 11 Informatics Practices – NCERT Book exercise Solution
Published on : January 28, 2022 | Last Updated on : June 5, 2022 | By Anjeev Kr Singh
