Page 190 - Informatics_Practices_Fliipbook_Class12
P. 190

2.  Consider a table that stores the data of an item using the following attributes- ItemCode,  ItemName,  Price.
              Suggest an appropriate domain for each attribute.
           3.  Can a relation have two identical tuples? Why / Why not?
           4.  Define the following with respect to databases:'
              (i)  Domain
              (ii)  Constraint
              (iii)  Candidate Key
              (iv)  Alternate Key
           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
                                       Emp_id       Name               Dept_no
                                       E001         Tarun Sharma       10
                                       E002         Kajal Chugh        10
                                       E003         Jatin Chawla       20
                                       E004         Rahul Khanna       30
                                                          Department
                                       Dept_no           Dept_name
                                       10                Accounting
                                       20                Sales
                                       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 which  will be invalid on the empty table Employee.
              (ii)  A tuple insertion which will be invalid on the empty table Project.




          176  Touchpad Informatics Practices-XII
   185   186   187   188   189   190   191   192   193   194   195