Page 302 - Information_Practice_Fliipbook_Class11
P. 302
The ItemId column is the primary key of the relation. ItemName is the name of the food item, and the price is its price per
quantity.
To restrict duplicate values, ItemId is made the primary key of the relation.
b. A new relation is required to store the item’s purchase. It would be called Orders, and it should have the following attributes:
i. OrderId: a unique ID for every order. It is the primary key. This constraint satisfies the condition (i).
ii. Quantity: stores the quantity of items purchased by the student.
iii ItemId: foreign key that references the Item relation to make sure that item is available in the canteen. This constraint satisfies
condition (ii).
c. The attribute ‘calories’ should be stored in the Item relation. Since every item has its own calorie value, it would be stored with every
item.
9. An organisation wants to create a database called EMPDEPENDENT to maintain the following details about its employees and their
dependents.
EMPLOYEE(AadharNumber, Name, Address, Department, EmployeeID)
DEPENDENT(EmployeeID, DependentName, Relationship)
a. Name the attributes of EMPLOYEE, which can be used as candidate keys.
b. The company wants to retrieve details about the dependents of a particular employee. Name the tables and the keys that are required
to retrieve this detail.
c. What is the degree of EMPLOYEE and DEPENDENT relation?
Ans. a. AadharNumber, EmployeeID
b. From DEPENDENT table EmployeeID, DependentName, and from the EMPLOYEE table AdhaarNumber, Name would be required
to retrieve details about the dependents of a particular employee.
c. The degree of EMPLOYEE table is 5 and the degree of DEPENDENT table is 3.
10. School uniforms are available at M/s Sheetal Private Limited. They have maintained SCHOOL_UNIFORM Database with two relations viz.
UNIFORM and COST. The following figure shows the database schema and its state.
SCHOOL_UNIFORM Database
Attributes and Constraints
Table: UNIFORM
Attribute UCode UName UColor
Constraints Primary Key Not Null –
Table: COST
Attribute UCode Size Price
Constraints Composite Primary Key >0
Table: COST
UCode Size COST Price
1 M 500
1 L 580
1 XL 620
2 M 810
2 L 890
2 XL 940
3 M 770
3 L 830
3 XL 910
4 S 150
4 L 170
5 S 180
5 L 210
6 M 110
6 L 140
6 XL 160
288 Touchpad Informatics Practices-XI

