Database Management System - B.Tech. 5th Semester Examination, 2022

Four DML commands are:

1. create, update, delete, select
2. insert, update, drop, select
3. create, alter, delete, select
4. insert, modify, delete, select

View is a:

1. temporary table
2. virtual table
3. dynamic table
4. permanent table

The different levels of data abstraction are:

1. Physical level
2. Logical level
3. View level
4. all of the above

Which of the following is true?

1. a super-key is always a candidate key.
2. every 3NF schema is also a BCNF.
3. generalization is bottom-up approach.
4. none of these.

What is the purpose of project operation:

1. It selects certain columns.
2. It selects certain rows.
3. It selects certain strings.
4. It selects certain integers.

The weak entity set does not have sufficient attributes to form.

1. Primary key
2. Candidate key
3. Both (i) and (ii)
4. Super key

Which normal form is considered adequate for normal relational database design?

1. 2 NF
2. 5 NF
3. 4 NF
4. 3 NF

Which of the following is not a super key in R(V, W, X, Y, Z) with primary key VY?

1. VXYZ
2. VWXZ
3. VWXY
4. VWXYZ

Consider R (A, B, C, D, E) with following FDs:- \( A \rightarrow B \), \( A \rightarrow C \), \( CD \rightarrow E \), \( B \rightarrow D \), \( E \rightarrow A \) which of the following FDs is not implied by above set?

1. \( CD \rightarrow AC \)
2. \( BD \rightarrow CD \)
3. \( BC \rightarrow CD \)
4. \( AC \rightarrow BC \)

Which of the following is a concurrency control protocol.

1. Strict 2-phase locking protocol
2. Timestamp based protocol
3. Graph based protocol
4. All of the above

What is attribute closure \( X^+ \) of a set of attributes X with respect to a set of FDs F? Give the algorithm for commuting \( X^+ \) for X.

Relation R (ABCDEFGH) contains only atomic values for all of its attributes. \( F = \{ CH \rightarrow G, A \rightarrow BC, B \rightarrow CFH, E \rightarrow A, F \rightarrow EG \} \) is a set of functional dependencies (FDs) so that F is exactly the set of FDs that hold for R.
(i) Find all the candidate keys the relation R have?
(ii) Find the highest normal form with justification in which R exist?

Briefly explain the ACID properties of the transactions to ensure integrity of the data.

Consider the transactions T1, T2 and T3 and schedules S1 and S2 given below:
T1: r1 (X); r1(Z); w1 (X); w1 (Z)
T2: r2 (Y); r2 (Z); w2 (Z)
T3: r3 (Y); r3 (X); w3 (Y)
S1: r1 (X); r3(Y); r3(X); r2 (Y); r2(Z); w3(Y); w2 (Z); r1 (Z); w1(X); w1 (Z).
S2: r1 (X); r3 (Y); r2(Y); r3(X); r1(Z); r2(Z); w3 (Y); w1 (X); w2(Z); w1 (Z).
Determine which of the above schedules are conflict - serializable.

Explain the reasons for the update, insertion and deletion anomalies.

With example discuss candidate key, super key, primary key and foreign key.

What are the typical phases of query processing? With a sketch, discuss these phases in high level query processing.

When is the decomposition of relation schema R into two relation schemes X and Y, said to be a loss-less-join decomposition? Why is this property so important? Explain with example.

Explain the terms partial functional dependency' and 'transitive dependency'. Define 2NF and 3 NF in relation with these terms.

Discuss the concept of generalization, specialization and aggregation.

Consider the following employee database, primary keys are underlined.
Employee (ename, street, city)
Works (ename, cname, salary)
Company (cname, city)
Manages (cname, manager-name)
Write SQL queries to:
(i) Find the names of all the employees who work for XYZ.
(ii) Find all employees who live in the same city as the company for which they work.
(iii) Find all employees who live in the same cities and on the same streets as do their managers.
(iv) Find all employees who earn more than the average salary of all employees of their company.

Discuss the advantages and disadvantages of using DBMS as compared to a conventional file system.

What is weak entity set? Explain with suitable example. How weak entities are represented as relational schemas.