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

