Page 186 - Informatics_Practices_Fliipbook_Class12
P. 186

6.  Consider the following database tables:

                                          CLASS (Primary key: ClassId, Section)

                                ClassId         Section       Stream        ClassTeacher
                                    11             A           Science        Sona Sahu

                                    12             B         Commerce        Anirban Bose
                                    11             C            Arts          Vijaya Ahuja
                                    12             B         Commerce        Sushmita Sen
                                    12             C            Arts         Amisha Patel
                                    11             B         Commerce         S. Jayanthi


                                             STUDENT (Primary key: S_ID)
                        S_ID           SName         Class           Section              Marks
                          17           Mukesh          11               C                  88
                                       Agrawal
                          21           Sanjoy          11               A                  76
                          34           Meena           12               B                  67
                          41            Latha          12               A                  40
                          22          Niranjan         11               C                  91
                          11             B         Commerce         S. Jayanthi         S. Jayanthi
              A command is executed to insert each of the following tuples in the table CLASS. 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)   <11, "A", "Science", "Pooja Sahu">
              (ii)  <11, "D", "Science", "Sara Nayak">
              (iii)  <11, NULL, "Science", "Ananya Kasaravall">
              (iv)  <NULL, "D", "Science", "Vinayak Damodar">
              (v)  <11, "D", "Science", "Sona Sahu">
              (vi)  <11, "D", NULL, NULL>
              (vii)  <11, "d", "Commerce","Ananya Kasaravall">
         Ans.  (i)   <11, "A", "Science", "Pooja Sahu">
                  Error: there is already a tuple in the CLASS table with the primary key <11, "A">.
              (ii)  <11, "D", "Science", "Sara Nayak">
                  Insertion will be successful as there is no tuple in the CLASS table with the primary key <11, "D">.
              (iii)  <11, NULL, "Science", "Ananya Kasaravall">
                  Error: Violation of entity integrity constraint. Section is part of the primary key, so cannot be NULL.

              (iv)  <NULL, "D", "Science", "Vinayak Damodar">
                  Error: Violation of entity integrity constraint. ClassId  is part of the primary key, so cannot be NULL.

              (v)  <11, "D", "Science", "Sona Sahu">
                  Insertion will be successful as there is no problem in repeating Stream and ClassTeacher. The same teacher can be a
                  class teacher for more than one class. But if this is not allowed, we will have to declare that ClassTeacher is UNIQUE.
              (vi)  <11, "D", NULL, NULL>
                  Insertion will be successful as non-key attributes can be NULL unless otherwise mentioned.

              (vii)  <11, "d", "Commerce","Ananya Kasaravall">
                  Insertion will be successful as Section "c" is different from Section "C".



          172  Touchpad Informatics Practices-XII
   181   182   183   184   185   186   187   188   189   190   191