NPTEL Introduction to Database Assignment Answers

NPTEL Introduction to Database Assignment Answers

by

1. Which of the following SQL clauses can have a subquery?

(i) FROM (ii) GROUP BY (iii) HAVING

  • Only (i)
  • Only (i) and (ii)
  • Only (i) and (iii)
  • All (i), (ii) and (iii)
Answer :- c

2. Views can never be used in an update query

  • True
  • False
Answer :- b

3. Views are materialized into tables if the view definition has only one table

  • True
  • False
Answer :- b

4. Views can be used in the definition of other views

  • True
  • False
Answer :- a

5. Aggregate operators can be used to define views

  • True
  • False

Consider the relation stud (C1, C2, C3, C4, C5, C6). A SQL query Q created using stud has the columns C1, C2, and C3 in the group by clause. Assuming Q is a syntactically correct query,state whether the following statements are true or false.

Answer :- a

6. The column C4 can be used in the HAVING clause without aggregate operator

  • True
  • False
Answer :- b

7. The column C4 can be used in the WHERE clause without aggregate operator

  • True
  • False

Answer :- a

8. The column C3 can be used in the SELECT clause without aggregate operator

  • True
  • False
Answer :- a

9. The column C3 can be used in the SELECT clause with aggregate operator

  • True
  • False
Answer :- b

10. Consider the following schema of the relation stud:

stud(​ K ​ , C1)

Which of the following queries would find the number of NULLs in the column C1 of the stud relation.

  • SELECT COUNT(C1) FROM stud WHERE C1 IS NOT NULL
  • SELECT COUNT(*) – COUNT(C1) FROM stud WHERE C1 IS NOT NULL
  • SELECT COUNT() FROM stud WHERE C1 = NULL
  • SELECT COUNT(*) – COUNT(C1) FROM stud
Answer :- d

11. Consider two instances of the relations R1 and R2 with the number of rows 10 and 8, respectively. The minimum number of tuples in the result of the following query is

SELECT *
FROM R1 FULL OUTER JOIN R2

  • 10
  • 98
  • 80
  • 18
Answer :- a

12. DLETE keyword can be used to do which of the following operations?

(i) delete all rows (ii) delete specific rows (iii) delete table definition

  • Only (ii)
  • Only (iii)
  • Only (i) and (ii)
  • All (i), (ii) and (iii)
Answer :- c

13. Consider the following query

select deptNo
from course
group by deptNo
having sum(credits) >= ANY (select max(x.totalCredits)
            from ( select sum(credits) as totalCredits
                from course
                group by deptNo) as x
                );

Suppose total-credits of a department is the sum of the credits of the courses offered by the department. Which of the following statements is correct about the above query?

  • It finds the department(s) with the highest number of total-credits across all the departments.
  • It finds the department(s) with total-credits greater than or equal to the sum of the total-credits of all the departments.
  • It finds the department(s) with total-credits greater than or equal to the credits of the course with the highest number of credits across the institute.
  • It lists all the departments in the institute.
Answer :- a

14. Consider the following sets about different approaches to programmatic access of databases and the properties that may apply to these approaches.

{1: Embedded SQL approach; 2: API based approach; 3: Database language approach}
{p: Syntax-check during compilation q: driver; r: cursors; s: No impedance mismatch; t: Multiple active connections }

Identify the correct matching between the sets:

  • 1–p; 2–t; 3–q
  • 1–t; 2–q; 3–s
  • 1–s; 2–q; 3–t
  • 1–p; 2–t; 3–s
Answer :- d

15. Which of the following is an example of non-atomic value:

  • A set of names
  • Value of a composite attribute “address”
  • An integer
  • Both A & B
Answer :- d

16. What is TRUE about the concept of “Functional Dependency” in a relation scheme R?

  • It is a constraint between two sets of attributes of R.
  • Denoted as X → Y, it means that the values of the X attributes uniquely determine the values of the Y attributes in r, where r is any instance of a relation R.
  • It is property of the semantics of the attributes of R.
  • All of the above are TRUE.
Answer :- d

17. Consider a relation R and recall the definition of “First Normal Form(1NF)” and state the validity of the following statements:
S1: The domains of all attributes of R should consist of atomic values.
S2: To check if R is in 1NF, additional information such as functional dependencies in R is essential.

  • S1: True; S2: True
  • S1: True; S2: False
  • S1: False; S2: False
  • S1: False; S2: True
Answer :- b

18. Given a relation R(A, B, C, D) and the set of all functional dependencies F on R where
F = { AB → C, C → D, C → B, B → A } find the TRUE statements among S1 and S2 below:
S1: C+ = {C, D, B, A}
S2: B+ = {B, A}

  • Only S1
  • Only S2
  • Both S1 & S2
  • Neither S1, nor S2
Answer :- a

19. Consider a relation R(A,B,C,D,E) and the following set F of all FDs on R where F = { A → B, BC → D, E → C, D → A }. Choose the correct option:

  • The relation R has exactly two candidate keys.
  • The relation R has exactly three candidate keys.
  • Pairwise, the candidate keys of R do not share any attribute.
  • Both A & C are correct.
Answer :- b

20. Given a relation R(A,B,C,D,E) and the following set of all FDs F on R where F = { AD → C, B → A, C → E, E → BD }. What are all the possible candidate keys of R ?

  • {AD}
  • {AD, BD}
  • {AD, BD, E}
  • {AD, BD, E, C}
Answer :- d

21. Choose the incorrect option about concept of the Second Normal Form(2NF):

  • If R is in 2NF it should not contain an attribute that is partially dependent on a key of R.
  • 2NF is based on the concept of full functional dependency.
  • 2NF is based on the concept of transitive dependency.
  • If the relation is in 3NF, it will also be in 2NF.
Answer :- c

22. Consider a relation R(A,B,C,D,E,F) and the following set of all FDs on R: {AB → C, C → DE, E → F, F → B}. Choose the correct option:

  • R is in 1NF, but not in 2NF
  • R is in 2NF, but not in 3NF
  • R is in 3NF, but not in BCNF
  • R is in BCNF
Answer :- a

23. Consider the following statements:
S1: Any schema that satisfies BCNF also satisfies 3NF.
S2: The definition of 3NF does not allow any functional dependencies which are allowed in BCNF

  • S1: True; S2: False
  • S1: True; S2: True
  • S1: False; S2: True
  • S1: False; S2: False
Answer :- a

24. Consider a schema R(A, B, C, D) and functional dependencies { A → B, C → D }, then decomposition of R into R1(A, B) and R2(C, D) is

  • lossless and dependency preserving
  • lossless but not dependency preserving
  • dependency preserving but not lossless
  • not dependency preserving and also not lossless
Answer :- c

25. Consider a relation R(A,B,C). A functional dependency AB → C holds on R. Then it is also true that:

  • A → C holds on R.
  • B → C holds on R.
  • AB is a key.
  • Both A → C and B → C hold on R
Answer :- c

26. Consider a relation R(A,B,C,D,E,F) and set of all FDs on R as given below:

{ AB → C, C → D, D → E, E → F, F → A }.
Choose the FALSE statement:

  • Every attribute is a prime attribute.
  • Five candidate keys are possible in R.
  • Attribute B is part of each candidate key.
  • Attribute C is part of two candidate keys.
Answer :- b

27. Consider a relation R(A,B,C,D,E,F) and set of all FDs on R as given below: {AB → C, C → D, D → E, E → F, F → A}.Choose the correct option:

  • R is in 1NF, not in 2NF
  • R is in 2NF, not in 3NF
  • R is in 3NF, not in BCNF
  • R is in BCNF
Answer :- d

28. Consider a relation R(A,B,C,D) and FD set F = { A → B, B → C } and the following statements:
S1: ABD is not a candidate key.
S2: There exists a proper subset of ABD such that its closure determines all the attributes of R.
Choose the correct option:

  • S1 is TRUE; S2 is FALSE
  • Both S1 and S2 are TRUE, but S2 is not the correct reason for S1 to be TRUE
  • Both S1 and S2 are TRUE and S2 is the correct reason for S1 to be TRUE
  • Both S1 and S2 are FALSE
Answer :- d

29. Consider the following two FD sets on a relation R = (A, B, C).
F1 = { A → B, B → C}
F2 = { AB → C, AC → B, B → C}
and the two statements given below
S1: F1 covers F2
S2: F2 covers F1
Choose the correct option::

  • S1: TRUE; S2: TRUE
  • S1: FALSE; S2: TRUE
  • S1: FALSE; S2: FALSE
  • S1: TRUE; S2: FALSE
Answer :- b

30. Consider a relation R(A,B,C,D,E) and the FD set F = {A → BC, CD → E, B → D, E → A}, and the following statements about closure of the attributes wrt F:
S1: A+ = {ABCDE}
S2: B+ = {BD}
S3: C+ = {CD}
S4: D+ = {D}
S5: E+ = {EA}
Choose the correct option:

  • Only S1 and S2 are TRUE
  • Only S1, S2 and S3 are TRUE
  • Only S1, S3, and S5 are FALSE
  • Only S3 and S5 are FALSE
Answer :- c

31. For the given relation and FD set in Question 6, compute all the candidate keys of R.

  • {E}
  • {E, A}
  • {E, A, BC}
  • {E, A, BC, CD}
Answer :- d

32. Consider a relation R(A,B,C,D,E,F) and the following set of all FDs on R: {AB → C, C → DE, E → F, F → B} Compute the highest normal form in which R is:

  • 1NF
  • 2NF
  • 3NF
  • BCNF
Answer :- d

33. Consider a relation R(A,B,C,D) and the following FD set F = {A → B, B → C, C → D, D → A} Compute the highest normal form in which R is:

  • 1NF
  • 2NF
  • 3NF
  • BCNF
Answer :- d

34. Fourth Normal Form(4NF) and Fifth Normal Form(5NF) deal with …X… and …Y… types of dependencies, respectively. Choose the correct option for X and Y:

  • X: Multi-valued; Y: Join
  • X: Multi-valued; Y: Inclusion
  • X: Join; Y: Multi-Valued
  • X: Inclusion; Y: Join
Answer :- a

Leave a Comment