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

