1. Choose the correct answer from the given four alternatives:

Primary key cannot be null, relates to

Entity Integrity Constraint

Referential Integrity Constraint

Domain Integrity Constraint

None of the above

SQL commands CREATE, ALTER and DROP come under the purview of

DDL

DML

DCL

All of the above

constitutes Data Control Language (DCL).

Transaction Control Language

Access Control Language

Both and

None of the above

Atomicity of attributes is preserved in normal form.

First

Second

Third

None of the above

protocol relates to backup and recovery.

2 Phase Locking

2 Phase Commit

Both and

None of the above

Verbs identifies of an ER diagram.

Entities

Relation

Attributes

None of the above

In an ER diagram, the "kind-of" relationship relates to

Generalization

Aggregation

Relation

None of the above

2. Explain the following with the help of suitable examples:

Loss-less Decomposition

Dependency Preserving Decomposition

Draw an ER diagram for the statement given below:

"Trainers trains trainees in an institution affiliated to a university, the institution has various departments to offer training."

Choose proper set of attributes for each component in the diagram.

Prepare database for each component, as per the identified schema.

Differentiate the following:

Serial Schedule and Serializable Schedule

3NF and BCNF

Consider the following database schema:

EMPLOYEE ESSN. ENAME, DOB, DEPT NO, SALARY)
DEPENDENT ESSN, DEPEND_NAME, RELATION, DOB)
DEPARTMENT (DEPT NO, DEPT_NAME, MANAGER)

Perform the following queries using both Relational algebra and SQL

Determine the details of dependents of the employee "MANOJ".

Who is the manager of the department, where the employee with ESSN code 2082 works

Find the DOB of the son of the employee having ESSN code 2082.

Will the SQL statement SELECT FROM STUDENT;
serve the purpose of Selection or Projection operation? Justify your answer with suitable arguments.

Briefly discuss the following:

Primary Indexes

Secondary Indexes

Clustering Indexes

Discuss the approaches of concurrency management, which are applied to deal with the problem of deadlock.

6. Explain the concept of transaction management in concurrent environment. Discuss the role of ACID properties for managing the transactions in concurrent environment.

Discuss the Insert, Delete and Update anomalies that occur, if the relation is not in 2NF. Use suitable examples for your discussion.

Explain the concept of multivalued dependencies with suitable examples.

8. Write short notes on the following:

Performance Tuning

Serializability

Fourth Normal Form

Index Data Structures