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
   297   298   299   300   301   302   303   304   305   306   307