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

