Q. Given the following relation instance.

x y z
1 4 2
1 5 3
1 6 3
3 2 2

Which of the following functional dependencies are satisfied by the instance? (GATE CS 2000)

View solution

Q. Consider a database table T containing two columns X and Y each of type integer. After the creation of the table, one record (X=1, Y=1) is inserted in the table. Let MX and My denote the respective maximum values of X and Y among all records in the table at any point in time. Using MX and MY, new records are inserted in the table 128 times with X and Y values being MX+1, 2*MY+1 respectively. It may be noted that each time after the insertion, values of MX and MY change. What will be the output of the following SQL query after the steps mentioned above are carried out?
SELECT Y FROM T WHERE X=7;

View solution

Q. Consider the following Employee table

ID salary DeptName
1 10000 EC
2 40000 EC
3 30000 CS
4 40000 ME
5 50000 ME
6 60000 ME
7 70000 CS

How many rows are there in the result of following query?

SELECT E.ID
FROM Employee E
WHERE EXISTS (SELECT E2.salary
FROM Employee E2
WHERE E2.DeptName = 'CS'
AND E.salary > E2.salary)

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. Which of the following is NOT a superkey in a relational schema with attributes V, W, X, Y, Z and primary key V Y ?

View solution

Q. Given the basic ER and relational models, which of the following is INCORRECT?

View solution

Q. A company maintains records of sales made by its salespersons and pays them commission based on each individual's total sales made in a year. This data is maintained in a table with following schema:

salesinfo = (salespersonid, totalsales, commission)
In a certain year, due to better business results, the company decides to further reward its salespersons by enhancing the commission paid to them as per the following formula:
If commission < = 50000, enhance it by 2% If 50000 < commission < = 100000, enhance it by 4% If commission > 100000, enhance it by 6%

The IT staff has written three different SQL scripts to calculate enhancement for each slab, each of these scripts is to run as a separate transaction as follows:

 T1
Update salesinfo
Set commission = commission * 1.02
Where commission < = 50000;
 
 T2
Update salesinfo
Set commission = commission * 1.04
Where commission > 50000 and commission is < = 100000;
 
 T3
Update salesinfo
Set commission = commission * 1.06
Where commission > 100000;

  Which of the following options of running these transactions will update the commission of all salespersons correctly?

View solution

Q. The statement that is executed automatically by the system as a side effect of the modification of the database is

View solution

Q. Consider the following transactions with data items P and Q initialized to zero:

T1: read (P) ;
read (Q) ;
if P = 0 then Q : = Q + 1 ;
write (Q) ;
T2: read (Q) ;
read (P) ;
if Q = 0 then P : = P + 1 ;
write (P) ;

Any non-serial interleaving of T1 and T2 for concurrent execution leads to

View solution

Q. Consider a join (relation algebra) between relations r(R)and s(S) using the nested loop method. There are 3 buffers each of size equal to disk block size, out of which one buffer is reserved for intermediate results. Assuming size(r(R)) < size(s(S)), the join will have fewer number of disk block accesses if

View solution

Q. Consider the relation account (customer, balance) where customer is a primary key and there are no null values. We would like to rank customers according to decreasing balance. The customer with the largest balance gets rank 1. ties are not broke but ranks are skipped: if exactly two customers have the largest balance they each get rank 1 and rank 2 is not assigned
Query1:
select A.customer, count(B.customer)
from account A, account B
where A.balance <=B.balance
group by A.customer

Query2:
select A.customer, 1+count(B.customer)
from account A, account B
where A.balance < B.balance
group by A.customer

Consider these statements about Query1 and Query2.

1. Query1 will produce the same row set as Query2 for
some but not all databases.
2. Both Query1 and Query2 are correct implementation
of the specification
3. Query1 is a correct implementation of the specification
but Query2 is not
4. Neither Query1 nor Query2 is a correct implementation
of the specification
5. Assigning rank with a pure relational query takes
less time than scanning in decreasing balance order
assigning ranks using ODBC.

Which two of the above statements are correct?

View solution

Q. Given the following statements:

S1: A foreign key declaration can always
be replaced by an equivalent check
assertion in SQL.
S2: Given the table R(a,b,c) where a and
b together form the primary key, the
following is a valid table definition.
CREATE TABLE S (
a INTEGER,
d INTEGER,
e INTEGER,
PRIMARY KEY (d),
FOREIGN KEY (a) references R)
Which one of the following statements is CORRECT?

View solution

Q. Consider the following ordering of transactions:

T1 : R(X); T2 : R(X); T1 : w(x); T1 : r(y); T2 : w(x); T2 : Commit; T1: w(y) ; T1: Commit

Which of the following is true?

View solution

Q. Consider a schema R(A,B,C,D) and functional dependencies A->B and C->D. Then the decomposition of R into R1(AB) and R2(CD) is

View solution

Q. Consider the set of relations shown below and the SQL query that follows.

Students: (Roll_number, Name, Date_of_birth)
Courses: (Course number, Course_name, Instructor)
Grades: (Roll_number, Course_number, Grade)

select distinct Name
from Students, Courses, Grades
where Students. Roll_number = Grades.Roll_number
and Courses.Instructor = Korth
and Courses.Course_number = Grades.Course_number
and Grades.grade = A

Which of the following sets is computed by the above query?

View solution

Q. Consider a relation geq which represents “greater than or equal to”, that is, (x,y) ∈ geq only if y >= x.

create table geq
(
ib integer not null
ub integer not null
primary key 1b
foreign key (ub) references geq on delete cascade
)

Which of the following is possible if a tuple (x,y) is deleted?

View solution

Q. Consider the following relational schemes for a library database: Book (Title, Author, Catalog_no, Publisher, Year, Price) Collection (Title, Author, Catalog_no) with in the following functional dependencies:

I. Title Author --> Catalog_no
II. Catalog_no --> Title, Author, Publisher, Year
III. Publisher Title Year --> Price

Assume {Author, Title} is the key for both schemes. Which of the following statements is true?

View solution

Q. Consider the following relational schema:

employee(empId, empName, empDept)
customer(custId, custName, salesRepId, rating)

salesRepId is a foreign key referring to empId of the employee relation. Assume that each employee makes a sale to at least one customer. What does the following query return?

SELECT empName
FROM employee E
WHERE NOT EXISTS (SELECT custId
FROM customer C
WHERE C.salesRepId = E.empId
AND C.rating <> ’GOOD’);

View solution

Q. What is the min and max number of tables required to convert an ER diagram with 2 entities and 1 relationship between them with partial participation constraints of both entities?

View solution