Page 201 - Informatics_Practices_Fliipbook_Class12
P. 201

5                                             SQL: WORKING WITH

                                                                                       TWO TABLES











              Chapter Outline


              5.1 Referential Integrity









            Introduction

            In the previous chapter, we discussed creating tables, inserting records, and retrieving information from tables in a database.
            However, one SQL query would retrieve tuples from one table only. This chapter will use Structured Query Language (SQL)
            to retrieve tuples from multiple tables. Such tables have a common attribute that creates a relationship between two or
            more tables. While inserting the data into such tables, we must ensure that the foreign key constraints are not violated.

            5.1 Referential Integrity

            While  working  with  two  or  more  related  tables  in  a  database,  SQL  enforces  the  referential  integrity  constraints.
            Figure  5.1  shows  the  relationship  between  entities  EMPLOYEE,  DEPARTMENT, and  PROJECT.  Since  an
            EMPLOYEE works in  a  DEPARTMENT,  these  two  entities  are  related  via  Dept_No  of  the  table  EMPLOYEE,
            which refers to the primary key Dept_No  of DEPARTMENT. You will recall that the attribute Dept_No of the
            table EMPLOYEE is called a foreign key as it is the primary key of the table DEPARTMENT. Since DEPARTMENT is
            managed by a manager who is also an EMPLOYEE, these two entities are related through foreign key Mgr_Id of
            DEPARTMENT, which refers to the primary key ID  of EMPLOYEE. Further, since each PROJECT is handled by a
            DEPARTMENT, these two entities are related to each other through the foreign key Dept_Num of PROJECT, which

            refers to the primary key Dept_No of DEPARTMENT.
                                                            Works In
                               Employee                                                 Department
                                                          Managed By

                                                                                         Handled By


                                             Works on
                                                                                    Project

                                      Figure 5.1 Relationship between entities of COMPANY Database
                                                                                     SQL: Working with Two Tables  187
   196   197   198   199   200   201   202   203   204   205   206