Paper 3

Write five major benefits of SQL? 5

Write the three main characteristics of a storage media. 3

How can we differentiate an index created on more than one columns of a table? 2

What is the range of Tinyint in SQL server. 1

Give 4 similarities between Materialized views and indexes. 5

Shortly explain the use of semi-colon in SQL statements. 3

Name the three problems which may come up if the concurrency is not controlled properly. 3

How can we differentiate an index created on more than one columns of a table? 2

Compare the following techniques (in terms of their advantages and disadvantages) 10

· Chaining

· Re-hashing

· Over-flow area

Employee table has Emp_ID as the primary key and Dept_Code as the foreign key from the Department table. A department can be deleted only if there are no employees working in it. Analyze and Identify the valid and Invalid statements as per the above scenario. 10

(i) CONSTRAINT Emp_ID_PK PRIMARY KEY(Emp_ID)

(ii) CONSTRAINT Emp_ID_PK PRIMARY KEY(Emp_ID), CONSTRAINT Dept_Code_FK

FOREIGN KEY(Dept_Code) REFERENCES Department(Dept_Code) ON DELETE

(iii) CONSTRAINT Emp_ID_PK PRIMARY KEY(Emp_ID), CONSTRAINT Dept_Code_FK

FOREIGN KEY(Dept_Code) REFERENCES Department(Dept_Code) ON DELETE

CASCADE

(iv) CONSTRAINT Emp_ID_PK PRIMARY KEY(Emp_ID), CONSTRAINT Dept_Code_FK

FOREIGN KEY(Dept_Code) REFERENCES Department(Dept_Code) ON DELETE

RESTRICT

(v) CONSTRAINT Emp_ID_PK PRIMARY KEY(Emp_ID), CONSTRAINT Dept_Code_FK

FOREIGN KEY(Dept_Code) REFERENCES Department(Dept_Code)