Page 192 - Informatics_Practices_Fliipbook_Class12
P. 192

12.  Consider a table PERSON described as follows:
               +------------+---------------+------+
               | Field      | Type          | Null  |
               +------------+---------------+------+
               | AadharNo   | decimal(12,0) | NO   |
               | last_name  | varchar(20)   | NO   |
               | first_name  | varchar(20)   | YES  |
               | birth_date      | date             | YES   |
               | PhoneNo              | decimal(10,0)      | YES   |
               +------------+---------------+------+

                                                         PERSON
                AadharNo            last_name       first_nmae   birth_date       PhoneNo
                672034567812        Sharma          Arjun        1990-08-31       8345123987
                712349049911        Wadhwa          Sumit        1992-09-12       9812476543


              AadharNo  and last_name  obey the NOT NULL  constraint. AadharNo  also obeys the uniqueness constraint.
              UNIQUE.
              Check whether the following tuples can be added to the PERSON table? Justify your answer.
              (i)   <799575933699, NULL, "Pooja", "1990-10-01", 9776626565>
              (ii)  <712349049911,"Singh","Gagan","1990-11-11",9812476543 >
          13.  Consider a Food and Beverage department having Dept_No 6 located in Pune. Also, consider the following tuple for an
              employee with id E0011 who is the manager of this department.

                                                   7, First Floor, MSFC
                E0010   Rashmi   Singhania   F                        Pune    411016   08-Oct-1990  90000   4
                                                  Building, Shivajinagar
              You need to insert a tuple both for a new employee and a new manager. Now if you attempt to insert a tuple for a new
              department first, it will be rejected by DBMS since it violates referential integrity constraint as there is no employee with id
              E0011. Similarly, adding employee entry working in department 6 will also be rejected since there is no department with
              this number. Propose a solution for this problem that does not violate referential integrity constraints.


                Assertion Reasoning Based Questions


               The following questions are Assertion(A) and Reasoning(R) based questions. Mark the correct choice as
              a.  Both A and R are true and R is the correct explanation of A
              b.  Both A and R are true and R is not the correct explanation of A
              c.  A is true but R is false
              d.  A is false but R is true
           1.  Assertion(A):  UPDATE-SET is a DDL command.
              Reasoning(R):  A DDL command modifies the structure of a table in a database.
           2.  Assertion(A):  COUNT() is an example of an aggregate function.
              Reasoning(R):  Aggregate functions apply to a single tuple in a relation.
           3.  Assertion(A):   GROUP  BY  clause  along  with  aggregate  functions  can  be  used  to  generate  summary  reports  in  a
                          database.
              Reasoning(R): WHERE clause is used to specify conditions on groups that will be displayed as output.








          178  Touchpad Informatics Practices-XII
   187   188   189   190   191   192   193   194   195   196   197