Page 293 - Information_Practice_Fliipbook_Class11
P. 293
5. A company has several employees who work in different departments. However, each employee is assigned to a unique
department. Each employee is assigned a unique employee id. Although department names are unique in the organization,
they have also been allotted unique department ids. Consider the following tables:
Employee Department
+--------+--------------+---------+
+---------+------------+
| Emp_id | Name | Dept_no |
| Dept_no | Dept_name |
+--------+--------------+---------+
+---------+------------+
| E001 | Tarun Sharma | 10 |
| 10 | Accounting |
| E002 | Kajal Chugh | 10 |
| 20 | Sales |
| E003 | Jatin Chawla | 20 |
| E004 | Rahul Khanna | 30 | | 30 | Marketing |
+---------+------------+
+--------+--------------+---------+
(i) Identify a suitable primary key for the Employee and Department tables.
(ii) Give an example of each of the following operations (consider each part independent of others):
a. An insert operation that will be consistent with the current state of the Employee table.
b. An insert operation that will be consistent with the current state of the Department table.
c. An insert operation that will be inconsistent with the current state of the Employee table, but would be fine if the
Employee table were empty.
d. An insert operation that will be inconsistent with the current state of the Department table, but would be fine if the
Department table were empty.
e. An insert operation that will be invalid on the empty table Employee.
f. An insert operation that will be invalid on the empty table Department.
g. Will an operation to delete the tuple having Dept_no 20 be consistent with the current state of the Employee
and Department tables? Justify your answer.
6. A company has several employees and has undertaken several projects. An employee may work on several projects. The
database for the company has the following tables. Attribute names in the table clearly indicate their meaning.
Employee (E_id, Ename, City, Salary, Department,
YearofJoining)
Project (P_no, PName, City, DeptName, StartYear)
WorksOn (P_no, E_id)
Identify a suitable primary key for each table. Also identify suitable foreign keys. If required, you may make suitable
assumptions, state them, and justify your answer in light of the assumptions.
Give an example of each of the following (consider each part independent of others):
(i) A tuple insertion of which will be invalid on the empty table EMPLOYEE.
(ii) A tuple insertion of which will be invalid on the empty table PROJECT.
(iii) A tuple insertion of which will be invalid on the empty table WORKSON.
(iv) A tuple insertion of which will be valid on the empty table EMPLOYEE.
(v) A tuple insertion of which will be valid on the empty table PROJECT.
(vi) A tuple insertion of 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.
Database Concepts and the Structured Query Language 279

