Q. Which of the following relational query languages have the same expressive power?

1.Relational algebra
2.Tuple relational calculus restricted to safe expressions
3.Domain relational calculus restricted to safe expressions

View solution

Q. Consider the following database schedule with two transactions, T1 and T2.

S = r2(X); r1(X); r2(Y); w1(X); r1(Y); w2(X); a1; a2;

where ri(Z) denotes a read operation by transaction Ti on a variable Z, wi(Z) denotes a write operation by Ti on a variable Z and ai denotes an abort by transaction Ti . Which one of the following statements about the above schedule is TRUE?

View solution

Q. Consider the relation scheme R = {E, F, G, H, I, J, K, L, M, M} and the set of functional dependencies {{E, F} -> {G}, {F} -> {I, J}, {E, H} -> {K, L}, K -> {M}, L -> {N} on R. What is the key for R?

View solution

Q. Consider the following relational schema:

Suppliers(sid:integer, sname:string, city:string, street:string)
Parts(pid:integer, pname:string, color:string)
Catalog(sid:integer, pid:integer, cost:real)
(sid,pid are primary keys)
Assume that, in the suppliers relation above, each supplier and each street within a city has a unique name, and (sname, city) forms a candidate key. No other functional dependencies are implied other than those implied by primary and candidate keys. Which one of the following is TRUE about the above schema?

View solution

Q. A database of research articles in a journal uses the following schema.

(VOLUME, NUMBER, STARTPGE, ENDPAGE, TITLE, YEAR, PRICE)

The primary key is (VOLUME, NUMBER, STARTPAGE, ENDPAGE) and the following functional dependencies exist in the schema.

(VOLUME, NUMBER, STARTPAGE, ENDPAGE) -> TITLE
(VOLUME, NUMBER) -> YEAR
(VOLUME, NUMBER, STARTPAGE, ENDPAGE) -> PRICE

The database is redesigned to use the following schemas.

(VOLUME, NUMBER, STARTPAGE, ENDPAGE, TITLE, PRICE)
(VOLUME, NUMBER, YEAR)

Which is the weakest normal form that the new database satisfies, but the old one does not?

View solution

Q. Relation R is decomposed using a set of functional dependencies, F and relation S is decomposed using another set of functional dependencies G. One decomposition is definitely BCNF, the other is definitely 3NF, but it is not known which is which. To make a guaranteed identification, which one of the following tests should be used on the decompositions? (Assume that the closures of F and G are available).

View solution

Q. Given relations r(w, x) and s(y, z), the result of

SELECT DISTINCT w, x
FROM r, s

is guaranteed to be same as r, provided

View solution

Q. Let r be a relation instance with schema R = (A, B, C, D). We define r1 = ΠA, B, C (r) and r2 = ΠA.D (r). Let s = r1 * r2 where * denotes natural join. Given that the decomposition of r into r1 and r2 is lossy, which one of the following is TRUE?

View solution

Q. Which of the given options define a transaction correctly?

View solution

Q. Consider the following two phase locking protocol. Suppose a transaction T accesses (for read or write operations), a certain set of objects {O1,...,Ok}. This is done in the following manner: Step 1. T acquires exclusive locks to O1, . . . , Ok in increasing order of their addresses. Step 2. The required operations are performed. Step 3. All locks are released. This protocol will

View solution

Q. Which of the following is wrong?
The goal of concurrent execution is

View solution

Q. Given the following schema:

employees(emp-id, first-name, last-name, hire-date, dept-id, salary)
departments(dept-id, dept-name, manager-id, location-id)

You want to display the last names and hire dates of all latest hires in their respective departments in the location ID 1700. You issue the following query:

SQL> SELECT last-name, hire-date
FROM employees
WHERE (dept-id, hire-date) IN
(SELECT dept-id, MAX(hire-date)
FROM employees JOIN departments USING(dept-id)
WHERE location-id = 1700
GROUP BY dept-id);

What is the outcome?

View solution

Q. R(A,B,C,D) is a relation. Which of the following does not have a lossless join, dependency preserving BCNF decomposition?

View solution

Q. Relation R with an associated set of functional dependencies, F is decomposed into BCNF. The redundancy (arising out of functional dependencies) in the resulting set relations is.

View solution

Q. In an inventory management system implemented at a trading corporation, there are several tables designed to hold all the information. Amongst these, the following two tables hold information on which items are supplied by which suppliers, and which warehouse keeps which items along with the stock-level of these items. Supply = (supplierid, itemcode) Inventory = (itemcode, warehouse, stocklevel) For a specific information required by the management, following SQL query has been written

Select distinct STMP.supplierid
From Supply as STMP
Where not unique (Select ITMP.supplierid
From Inventory, Supply as ITMP
Where STMP.supplierid = ITMP.supplierid
And ITMP.itemcode = Inventory.itemcode
And Inventory.warehouse = 'Nagpur');

For the warehouse at Nagpur, this query will find all suppliers who

View solution

Q. Consider a relational table with a single record for each registered student with the following attributes.

1. Registration_Num: Unique registration number
of each registered student
2. UID: Unique identity number, unique at the
national level for each citizen
3. BankAccount_Num: Unique account number at
the bank. A student can have multiple accounts
or join accounts. This attribute stores the
primary account number.
4. Name: Name of the student
5. Hostel_Room: Room number of the hostel

Which one of the following option is INCORRECT?

View solution

Q. The following table has two attributes A and C where A is the primary key and C is the foreign key referencing A with on-delete cascade.

A C
-----
2 4
3 4
4 3
5 2
7 2
9 5
6 4

The set of all tuples that must be additionally deleted to preserve referential integrity when the tuple (2,4) is deleted is:

View solution

Q. Which of the following command is used to delete a table in SQL?

View solution

Q. How many serial schedules are possible using n-transactions?

View solution

Q. Which of the following schedule are not conflict serializable?

View solution