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
   288   289   290   291   292   293   294   295   296   297   298