30 DBMS MCQ Questions and Answer-DBMS Tutorial

  1. Functional dependency is a relationship between or among
    A. Entities
    B. Rows
    C. Attributes
    D. Tables
    Ans : C
  2. Which functional dependency types is/are not present in the following dependencies?
    Empno -> EName, Salary, Deptno, DName
    DeptNo -> DName
    EmpNo -> DName
    A. Full functional dependency
    B. Partial functional dependency
    C. Transitive functional dependency
    D. Both B and C
    Ans : B
  3. If one attribute is determinant of second, which in turn is determinant of third, then the relation cannot be:
    A. Well-structured
    B. 1NF
    C. 2NF
    D. 3NF
    Ans : D
  4. Which of the following statements are TRUE about DISTINCT keyword?
    A. DISTINCT removes duplicates based on all the columns in the SELECT clause
    B. Usage of DISTINCT should be avoided as far as possible due to performance issues
    C. Both A and B
    D. None of the above
    Ans : C
  5. Which of the following columns in a table cannot be updated?
    A. DATE type columns in the table
    B. Columns which allows NULL values in the table
    C. A primary key column which also serves as foreign key reference in another table
    D. All of the above
    Ans : C
  6. The language used application programs to request data from the DBMS is referred to as __
    A. DML
    B. DDL
    C. Query language
    D. All of the Mentioned
    View Answer
    Ans : A
  7. Determine data type for the given column? Column Name: Price ; Description: Cost of an item in rupees and paise ; Example: 200.21
    A. VARCHAR2(50)
    B. NUMBER
    C. NUMBER(5,2)
    D. NUMBER(6)
    Ans : C
  8. Determine data type for the given column? Column Name: IFSC_Code ; Description: A 11 Character alphanumeric code that identifies a bank branch ; Example: SBIN0009044
    A. VARCHAR2(50)
    B. NUMBER
    C. Char(11)
    D. NUMBER(11)
    Ans : C
  9. A many to many relationship between two entities usually results in how many tables?
    A. One
    B. Two
    C. Three
    D. Four
    Ans : C
  10. The attribute AGE is calculated from DATE_OF_BIRTH. The attribute AGE is
    A. Single valued
    B. Multi valued
    C. Composite
    D. Derived
    Ans : D
  11. constraints of functional dependencies are based on.
    A. key
    B. revisited key
    C. superset key
    D. none of these
    Answer – A
  12. Which of the following is a legal expression in SQL?
    A. SELECT NULL FROM SALES;
    B. SELECT NAME FROM SALES;
    C. SELECT * FROM SALES WHEN PRICE = NULL;
    D. SELECT # FROM SALES;
    Answer – B
  13. Consider the following schema –

STUDENTS(student_code, first_name, last_name, email,
phone_no, date_of_birth, honours_subject, percentage_of_marks);

Which of the following query would display names of all the students whose honours subject is English, or honours subject is Spanish and percentage of marks more than 80?

A – select first_name, last name from students where (honours_subject = “English” or honours_subject = “Spanish” ) and percentage_of_marks > 80;

B – select first_name, last name from students where honours_subject = “English” or honours_subject = “Spanish” and percentage_of_marks > 80;

C – select first_name, last name from students where honours_subject = “English” and honours_subject = “Spanish” or percentage_of_marks > 80;

D – select first_name, last name from students where (honours_subject = “English”) and honours_subject = “Spanish” and percentage_of_marks > 80;
Answer – B

  1. Which of the following statement is true?
    a) DELETE does not free the space containing the table and TRUNCATE free the space containing the table
    b) Both DELETE and TRUNCATE free the space containing the table
    c) Both DELETE and TRUNCATE does not free the space containing the table
    d) DELETE free the space containing the table and TRUNCATE does not free the space containing the table
    Answer – A
  2. Consider the following schema −
    STUDENTS(student_code, first_name, last_name, email, phone_no, date_of_birth, honours_subject, percentage_of_marks);
    Which of the following query would display all the students where the second letter in the first name is ‘i’?
    A – select first_name from students where first_name like ‘i%’; B – select first_name from students where first_name like ‘%i’;
    C – select first_name from students where first_name like ‘%i%’;
    D – select first_name from students where first_name like ‘i’;
    Answer – A
  3. Consider the following schema −
    STUDENTS (student_code, first_name, last_name, email, phone_no, date_of_birth, honours_subject, percentage_of_marks);
    Which of the following query would display names of all the students whose email ids are not provided?
    A – select first_name, last name from students where email = 0;
    B – select first_name, last name from students where email = ‘ ‘;
    C – select first_name, last name from students where email is null;
    D – select first_name, last name from students where email = ‘null’;
    Answer – c
  4. 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?
    (a) XY -> Z and Z -> Y
    (b) YZ -> X and Y -> Z
    (c) YZ -> X and X -> Z
    (d) XZ -> Y and Y -> X
    Correct: b
  5. Consider the following relational schemes for a library database:
    Book (Title, Author, Catalog_no, Publisher, Year, Price)
    Collection (Title, Author, Catalog_no)
    within 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?
(A) Both Book and Collection are in BCNF
(B) Both Book and Collection are in 3NF only
(C) Book is in 2NF and Collection is in 3NF
(D) Both Book and Collection are in 2NF only

Correct: C

  1. Let E1 and E2 be two entities in an E/R diagram with simple single-valued attributes. R1 and R2 are two relationships between E1 and E2, where R1 is one-to-many and R2 is many-to-many. R1 and R2 do not have any attributes of their own. What is the minimum number of tables required to represent this situation in the relational model?
    (a) 2
    (b) 3
    (c) 4
    (d) 5
    Correct: b
  2. Consider the relation scheme R = {FUEL, PETROL, DIESEL, CAR, JEEP, SCOOTER} and the set of functional dependencies
    {FUEL} -> {PETROL, DIESEL}, {PETROL} -> {CAR, JEEP}, {DIESEL} -> {SCOOTER} on R.
    What is the CANDIDATE key for R?

a) {PETROL , DIESEL}
b) {FUEL}
c) {PETROL, CAR, JEEP}
d) {DIESEL, SCOOTER}

Correct: b

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

a) {E, F}
b) {E, F, H}
c) {E, F, H, I}
d) {E}
Correct: A

  1. Find the SQL statement below that is equal to the following:

SELECT name FROM customer WHERE state = ‘UP’;

a) SELECT name IN customer WHERE state IN (‘UP’);
b) SELECT name FROM customer WHERE state = ‘%UP’;
c) SELECT name FROM customer WHERE state IN (‘UP’);
d) SELECT name FROM customer WHERE state = ‘%UP%’;
Correct: c

  1. Find the SQL statement to meet following requirement:

Select the names of customers from customer table where length of country is 5 and ends with letter ‘I’.

a) SELECT name IN customer WHERE country IN (‘%I’);
b) SELECT name FROM customer WHERE country = ‘%UP’;
c) SELECT name FROM customer WHERE country LIKE ‘%I%’;
d) SELECT name FROM customer WHERE country LIKE ‘_ _ _ _ I’;
Correct: d

  1. The following functional dependencies are given .
    AB→CD,AF→D,DE→F,C→G,F→E,G→A
    Which one of the following options is false?
    (a) {AB}+ ={ACDEFG}
    (b) {BG}+={ABCDG}
    (c) {AF}+ = {ACDEFG}
    (d) {AB}+ = {ABCDG} Ans: c
  2. What is true about a schema?
    A.A schema is owned by a database user and has the same name as that user
    B.Each user owns a single schema
    C.Schema objects include database links
    D.All of the above
    Answer: D

26.Relation R has eight attributes ABCDEFGH. Fields of R contain only atomic values. F = {CH -> G, A -> BC, B -> CFH, E -> A, F -> EG} is a set of functional dependencies (FDs) so that F+ is exactly the set of FDs that hold for R.
How many candidate keys does the relation R have?
(A) 3
(B) 4
(C) 5
(D) 6
Answer: (B)

  1. Find the highest normal form of a relation R(A,B,C,D,E) with FD set {A->D, B->A, BC->D, AC->BE}
    (A) 1NF
    (B) 2NF
    (C) 3NF
    (D) 4NF
    Answer (A)
  2. Consider a relation office with the following schema;
    Office(Cabin_no, Room_no, Phone)
    Room number is unique. Each room consists of approximately 20 cabins and each cabin number is unique with respect to room number. The same cabin number may be used in different rooms. Each room has unique phone number.
    Which of the following is correct?
    a) Room_no is a candidate key
    b) Phone is a candidate key
    c) (Cabin_no, Phone) and (Cabin_no, Room_no) are the candidate keys.
    d) (Room_no, Phone) and (Room_no, Cabin_no) are the candidate keys.
    Answer C
  3. Consider the following schema of relation R;
    R (A, B, C)
    Attributes A, B, and C are all unique valued attributes. Which of the following is TRUE for R?
    a) A is a candidate key for R
    b) B is a candidate key for R
    c) (A, C) is a super key for R
    d) all of the above
    Answer:d
  4. Given the relation R(A,B,C,D,E) and the set of functional dependencies

F={A→B,A→C,CD→E,B→D,E→A}F={A→B,A→C,CD→E,B→D,E→A},
which of the following is not logically implied by FF?
a- CD → AC
b- BD → CD
c- EA → BD
d- DE → BC
Answer-b

Leave a Comment