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

