Database miscellaneous
Direction: Consider the following relational schemas :
Suppliers (sid : integer, sname : string, city : string, street : string)
Parts (pid : integer, pname : string, color : string)
Catalog (sid : integer, pid : integer, cost : real)
- Consider the following relational query on the above database :
SELECT S.sname
FROM Suppliers S
WHERE S.sid NOT IN (SELECT C. sid
FROM Catalog C
WHERE C.pid NOT IN (SELECT P.pid
FROM Parts P
WHERE P.color < > ‘blue’))
Assume that relations corresponding to the above schema are not empty. Which one of the following is the correct interpretation of the above query?
-
View Hint View Answer Discuss in Forum
S name is selected form Supplier S. Now this S.sid should not be in C.sid of Catalog C.
Therefore, it will find the names of all suppliers who have supplied a non-blue part.Correct Option: A
S name is selected form Supplier S. Now this S.sid should not be in C.sid of Catalog C.
Therefore, it will find the names of all suppliers who have supplied a non-blue part.
- 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 Hint View Answer Discuss in Forum
As given that each supplier and each street within a city has a unique name, it means (same ,city) and (street ,city) combination is unique. So these will be candidate keys. There is no other FD other than CK determining attributes. In all FD, LHS will be SK.
Example:
Hence 2 candidate Keys are possible
(1) (Sname, city) ⇒ (QR)
(2) (Street, city) ⇒ (SR)
⇒ The relation suppcier is in BCNF So the relation should be in BCNF.Correct Option: A
As given that each supplier and each street within a city has a unique name, it means (same ,city) and (street ,city) combination is unique. So these will be candidate keys. There is no other FD other than CK determining attributes. In all FD, LHS will be SK.
Example:
Hence 2 candidate Keys are possible
(1) (Sname, city) ⇒ (QR)
(2) (Street, city) ⇒ (SR)
⇒ The relation suppcier is in BCNF So the relation should be in BCNF.
- Consider the table employee (empId, name, department, salary) and the two queries Q1, Q2 below. Assuming that department 5 has more than one employee, and we want to find the employees who get higher salary than anyone in the department 5, which one of the statements is true for any arbitrary employee table?
Q1 : Select e.empId
From employee e
Where not exists
(Select * From employee s where s.department = “5” and s.salary>=e.salary)
Q2 = Select e.empId
From employee e
Where e.salary > Any
(Select distinct salary from employee s Where s.department = "5")
-
View Hint View Answer Discuss in Forum
As per given,
employee (empId, name, department, salary)
This suggests that an employees is to be denoted using the employee Id, name, department, salary
Query1 It selects an employee but does not compute the result as after the ‘where not exists’, it does not have statement to produce the result.
Query2 It selects an employee who gets higher salary than anyone in the department 5. Therefore, the query2 is correct.Correct Option: B
As per given,
employee (empId, name, department, salary)
This suggests that an employees is to be denoted using the employee Id, name, department, salary
Query1 It selects an employee but does not compute the result as after the ‘where not exists’, it does not have statement to produce the result.
Query2 It selects an employee who gets higher salary than anyone in the department 5. Therefore, the query2 is correct.
- A relational schema for a train reservation database is given below.
Passenger (pid, pname, age)
Reservation (pid, class, tid)
Table: Passenger
What pids are returned by the following SQL query for the above instance of the tables ?
SELECT pid
FROM Reservation
WHERE class = AC AND
EXISTS (SELECT *
FROM Passenger
WHERE age > 65 AND
Passenger.pid = Reservation.pid)
-
View Hint View Answer Discuss in Forum
The solution is best understood in steps
1. select the process Id from reservation whose class is AC the condition is satisfied by 0, 1, 5, 3
2. select a passenger whose age > 65 and the pId of passenger id = pId of reservation id passenger whose ages > 65 are those of pId 1, 2, 3
and the pId common to reservation is 1 and 3.
Correct Option: C
The solution is best understood in steps
1. select the process Id from reservation whose class is AC the condition is satisfied by 0, 1, 5, 3
2. select a passenger whose age > 65 and the pId of passenger id = pId of reservation id passenger whose ages > 65 are those of pId 1, 2, 3
and the pId common to reservation is 1 and 3.
The solution is best understood in steps
1. select the process Id from reservation whose class is AC the condition is satisfied by 0, 1, 5, 3
2. select a passenger whose age > 65 and the pId of passenger id = pId of reservation id passenger whose ages > 65 are those of pId 1, 2, 3
and the pId common to reservation is 1 and 3.
The solution is best understood in steps
1. select the process Id from reservation whose class is AC the condition is satisfied by 0, 1, 5, 3
2. select a passenger whose age > 65 and the pId of passenger id = pId of reservation id passenger whose ages > 65 are those of pId 1, 2, 3
and the pId common to reservation is 1 and 3.
- 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 broken but ranks are skipped: If exactly two customers have the largest balance they each get rank 1 and rank 2 is not assigned.
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 Query2 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 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 Hint View Answer Discuss in Forum
Query1 where A.balance<=B.balance → this line computes the A group of customers and the balance is less than or equal to the group B.
Query2 where A.balance < B.balance → this line computes the A group of customers and the balance is less than the group B.
Therefore, the row produced by query1 and query2 will be same but not in every case as the results of query1 and query2 differ in case where query1 has the situation <= but query2 has only <.
None of the query provides the correct implementation of the specification.Correct Option: C
Query1 where A.balance<=B.balance → this line computes the A group of customers and the balance is less than or equal to the group B.
Query2 where A.balance < B.balance → this line computes the A group of customers and the balance is less than the group B.
Therefore, the row produced by query1 and query2 will be same but not in every case as the results of query1 and query2 differ in case where query1 has the situation <= but query2 has only <.
None of the query provides the correct implementation of the specification.
