Page 219 - Informatics_Practices_Fliipbook_Class12
P. 219

C.  Fill in the blanks.
               1.  ____________ keys are used to enforce the integrity of the database across the tables.
               2.  If cardinality of TableA is x and cardinality of Table B is y, the cross product of TableA and TableB will
                  have cardinality ____________.
               3.  While working with two or more related tables in a database, SQL enforces the ____________ constraints.
               4.  When  a  SELECT  statement  is  used  to  retrieve  data  from  multiple  tables,  names  of  all  the  tables  are  specified  in  the
                  ____________ clause.
               5.  A _____________ operator that removes the duplicate column common to both the tables in an SQL query.

            D.  Answer the following questions:
               1.  Describe the notion of referential integrity constraints.
             Ans.   If a table has a foreign key that refers to a UNIQUE attribute (typically primary key) of another table, then the foreign key
                  value should refer to an existing primary key value or should have a NULL value.
               2.  Differentiate between Natural Join and Equi-Join.
             Ans.   Natural Join: It is based on the common attribute in the two tables being joined. In the resultant query, the duplicate
                  column is removed and  the common column is the first column.
                  Equi-Join: It is also based on the common attribute between the two tables. The values of this common attribute are
                  compared using the equality operator (=). No other comparison operator can be used in case of an equi-join.

            E.  Application Based Questions:
               1.  In addition to EMPLOYEE and DEPARTMENT tables, consider the PROJECT and WORKS_ON tables given below to
                  answer the queries that follow:
                  CREATE TABLE statements for the tables PROJECT and WORKS_ON are given below:

             Ans. CREATE TABLE PROJECT
                  (
                       Proj_No SMALLINT PRIMARY KEY,
                       Proj_Name VARCHAR(25) NOT NULL,
                       Dept_Num SMALLINT,
                       FOREIGN KEY(Dept_Num) REFERENCES DEPARTMENT (Dept_No)
                  );

                  CREATE TABLE WORKS_ON
                  (
                    Proj_No SMALLINT NOT NULL,
                    Emp_Id INT NOT NULL,
                    Hours SMALLINT,
                    PRIMARY KEY(Proj_No, Emp_Id),
                    FOREIGN KEY(Proj_No) REFERENCES PROJECT (Proj_No),
                    FOREIGN KEY(Emp_Id) REFERENCES EMPLOYEE (ID)
                  );
                  PROJECT table

                                                            PROJECT
                                           Proj_No       Proj_Name       Dept_Num
                                           1             Victory         2
                                           2             Wisdom          1

                                           3             Sputnik         1
                                           4             Wizards         5
                                           5             Performer       4


                                                                                     SQL: Working with Two Tables  205
   214   215   216   217   218   219   220   221   222   223   224