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

