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

