Page 294 - Information_Practice_Fliipbook_Class11
P. 294

8.  For the tables mentioned in Q7, and keeping in mind the primary keys identified by you, given an example of a tuple such
              that its insertion:
              (i)  on the empty table SUPPLIERS will generate an error.
              (ii)  on the empty table PARTS will generate an error.
              (iii)  on the empty table SUPPLIERS will be successful.
              (iv)  on the empty table PARTS will be successful.
              (v)  on the empty table PROJECT will be successful.
              (vi)  on the empty table SHIPMENT that will execute successfully will be successful.

           9.  Consider  the tables  EMPLOYEES,  DEPARTMENT and  RESOURCES which  store the details  of  the employees,  the
              department they belong to and the resources each department has.
              Employee (employee_id, name, date_of_birth, age, department_id, address)
              Department (department_id, name, number_of_people)
              Resources (resource_id, department_id, resource_name, quantity)
              Identify the primary and foreign keys for all the three tables. Determine which table should be created first so that the
              foreign keys can be referred correctly.
          10.  Differentiate between entity integrity and referential integrity. Support your answer with suitable examples.
          11.  A command is executed to insert each of the following tuples in the table STUDENT.

                                            STUDENT (Primary key: S_ID)
                                       +------+----------------+-------+---------+-------+
                                       | S_ID | SName          | Class | Section | Marks |
                                       +------+----------------+-------+---------+-------+
                                       |   17 | Mukesh Agrawal |    11 | C       |    88 |
                                       |   21 | Sanjoy         |    11 | A       |    76 |
                                       |   34 | Meena          |    12 | B       |    67 |
                                       |   41 | Latha          |    12 | A       |    40 |
                                       |   22 | Niranjan       |    11 | C       |    91 |
                                       +------+----------------+-------+---------+-------+
              For each operation, indicate whether it will be executed successfully. Justify your answer. If not, mention which DBMS
              constraint does the insert operation violate? Consider each operation independent of others.
              (i)  <18, "Mukesh Agrawal", 11, "C", 88>
              (ii)  <21, "Sanjay", 11, "A", 76>
              (iii)  <NULL, "Phule Bai", 11, "A", 88>
              (iv)  <20, NULL, 11, "A", 88>
              (v)  <20, NULL, 11, NULL, NULL>
              (vi)  <20, NULL, NULL, NULL, NULL>
          12.  Consider a table PERSON described as follows:
              +-----------------------+----------------------------+------------+
              | Field      | Type          | Null |
              +-----------------------+----------------------------+------------+
              | AadhaarNo  | decimal(12,0) | NO   |
              | last_name  | varchar(20)   | NO   |
              | first_name | varchar(20)   | YES  |
              | birth_date | date          | YES  |
              | PhoneNo    | decimal(10,0) | YES  |
              +-----------------------+----------------------------+------------+
              PERSON
                                +--------------+-----------+------------+------------+------------+
                                | AadhaarNo    | last_name | first_nmae  | birth_date | PhoneNo    |
                                +--------------+-----------+------------+------------+------------+
                                | 672034567812 | Sharma    | Arjun      | 1990-08-31 | 8345123987 |
                                | 712349049911 | Wadhwa    | Sumit      | 1992-09-12 | 9812476543 |
                                +--------------+-----------+------------+------------+------------+
              AadhaarNo and  last_name obey the  NOT NULL constraint.  AadhaarNo  also  obeys  the  uniqueness  constraint
              UNIQUE.

          280  Touchpad Informatics Practices-XI
   289   290   291   292   293   294   295   296   297   298   299