Page 217 - Informatics_Practices_Fliipbook_Class12
P. 217

Let's Summarise



                  To retrieve information from multiple tables, the tables are joined on one or more common attributes.
              Ø
                  Foreign keys are used to enforce the integrity of the database across the tables.
              Ø
                  In a relation, the value of a foreign key may be NULL, or it may be the same as the value of a UNIQUE
              Ø
                  attribute (generally primary key) value of the referenced table.
                  When a SELECT statement is used to retrieve data from multiple tables, names of the relevant tables are
              Ø
                  specified in the FROM clause.

                  If cardinality of Table1 is m and cardinality of Table2 is n, the cross product of Table1 and Table2
              Ø
                  will have cardinality m*n.
                  Equijoin condition tests for the equality of value of the attribute common to both tables.
              Ø
                  In the case of Natural join, the two tables are joined on two attributes with the same name and same domain.
              Ø





                                                  Solved Exercise


            A.  Multiple Choice Questions
               1.  Which of the following is NOT true with respect to working on multiple tables in a MYSQL database?
                  a.  The related tables must have a common attribute.
                  b.  While entering data into related tables, foreign key constraints must not be violated.
                  c.  All tables should be from the same database.
                  d.  The common field should not be a primary key in any of the tables.

               2.  Tables T1 and  T2 have 6 and 3 tuples, respectively. What will be the degree and cardinality of the cross product of T1 and
                  T2?
                  a.  9 and 18, respectively
                  b.  18 and 9, respectively
                  c.  18 and 18, respectively
                  d.  9 and 9, respectively
               3.  Which of the following is NOT true about Natural Join?
                  a.  The attribute common to both tables has the same name in both tables.
                  b.  The attribute common to both tables has the same domain in both tables.
                  c.  Natural join removes the duplicate column from the cross product of the two tables.
                  d.  Natural join positions the common attribute as the last column of the result.
               Answer questions numbers 4-9 based on the tables WORKER and PAYLEVEL given below:

                                                             Table: WORKER
                        W_code         Name            Desig          DOJ           DOB          P_Level
                          11        Ghanshyam        Supervisor    2006-10-15    1981-12-12       P001
                          12         Gonsalvis       Operator      2010-03-24    1990-07-10       P003
                          13           Azam          Mechanic      2014-06-24    1995-10-09       P001
                          14           Reena           Clerk       2015-08-10    1980-01-15       P002
                          15           Ritesh        Supervisor    2005-11-06    1979-03-31       P001


                                                                                     SQL: Working with Two Tables  203
   212   213   214   215   216   217   218   219   220   221   222