DBMS MCQ Questions and Answer Set-10

by
  1. Consider a relation R(A, B, C, D) with the following functional dependencies: A -> (B, C, D), (A, D) -> (B, C) and (C, D) -> (A, B).

Identify the candidate key(s).

a) {A}
b) {A}, {C, D}
c) {A}, {C, D}, {A, D}
d) {C, D}

  1. The database design prevents some data from being stored due to _.

a) Deletion anomalies
b) Insertion anomalies
c) Update anomaly
d) None of these

  1. Consider the relation Sale(Date, Customer, Product, Vendor, VendorCity, SalesRep)
    {Date, Customer, Product} is the composite candidate key and the following functional dependencies are also given:
    Vendor -> VendorCity, Product -> Vendor
    What is the highest normal form of the sale relation?

a) 2NF
b) 3NF
c) 0NF
d) 1NF

  1. Table T exists with single column A having 4 rows with values – 100, 200, NULL, 300. What is the output of the following query?

SELECT MAX(A) FROM t WHERE a>1000;

a) 300
b) NULL
c) No rows
d) Error is thrown

  1. Consider the following statements with respect to a candidate key:
    a. Candidate key identifies rows in a relation uniquely.
    b. There can be only one candidate key in a relation.
    c. A candidate key can be a combination of more than one attribute in a relation.

Identify the statement/(s) which are TRUE.

a) Only a and c
b) Only a and b
c) only a
d) Only b and c

6) Relation R1 has 20 tuples and 6 attributes. Relation R2 has 0 tuples and 8 attributes. When a ‘CROSS JOIN’ is achieved between R1 and R2, how many tuples would the resultant set have?

a) 28
b) 10
c) 0
d) 35

7) Which of the following conditions has to be satisfied for INNER JOIN to work?

a) Columns used for joining must have same name
b) Columns used for joining can have same or different name
c) Columns used for joining must have different names
d) A column is not required for joining

  1. View the Exhibits and examine the structures of the COSTS and PROMOTIONS tables.

Evaluate the following SQL statement:

SQL> SELECT prod_id FROM costs WHERE promo_id IN (SELECT promo_id FROM promotions WHERE promo_cost < ALL (SELECT MAX(promo_cost) FROM promotions GROUP BY (promo_end_datepromo_begin_date)));

What would be the outcome of the above SQL statement?

a. It displays prod IDs in the promo with the lowest cost.
b. It displays prod IDs in the promos with the lowest cost in the same time interval.
c. It displays prod IDs in the promos with the highest cost in the same time interval.
d. It displays prod IDs in the promos with cost less than the highest cost in the same time interval.

  1. Evaluate the following SQL statement:
    SQL> SELECT cust_id, cust_last_name
    FROM customers WHERE cust_credit_limit IN (select cust_credit_limit FROM customers WHERE cust_city =’Singapore’);

Which statement is true regarding the above query if one of the values generated by the subquery is NULL?
a. It produces an error.
b. It executes but returns no rows.
c. It generates output for NULL as well as the other values produced by the subquery.
d. It ignores the NULL value and generates output for the other values produced by the subquery.

  1. In an E-R diagram an entity set is represent by a
    (A) rectangle.
    (B) ellipse.
    (C) diamond box.
    (D) circle.

Leave a Comment