Page 191 - Informatics_Practices_Fliipbook_Class12
P. 191

(iii)  A tuple insertion which will be invalid on the empty table WorksOn.
                  (iv)  A tuple insertion which will be valid on the empty table Employee.
                  (v)  A tuple insertion which will be valid on the empty table Project.
                  (vi)  A tuple insertion which will be valid on the empty table WorksOn.
               7.  A company deals with several projects. The company has on its panel a number of suppliers which supply different parts.
                  Although a part may be used in several projects, a shipment always relates to a specific part for a specific project being
                  supplied by a supplier. The supplier, part, and project have been abbreviated as S, P, and J respectively in the description
                  of the tables given below:

                  Suppliers (SNo, SName, Status, SCity)
                  Parts (PNo, PName, Colour, Weight, City)
                  Project (JNo, Jname, JCity)
                  Shipment (SNo, PNo, JNo, Quantity)
                  Identify primary key for each table. Also identify the foreign key constraints and foreign keys.
               8.  For the tables mentioned in Q7, and keeping in mind the primary keys identified by you, give 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 following 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>


                                                                                        Database Query using SQL  177
   186   187   188   189   190   191   192   193   194   195   196