DBMS Interview Questions and Answers Third 31 to 50

QUESTION 31:

What is DML Compiler?

ANSWER:

It translates DML statements in a query language into low-level instruction that the query evaluation engine can understand.

QUESTION 32:

What is Query evaluation engine?

ANSWER:

It executes low-level instruction generated by compiler.

QUESTION 33:

What is DDL Interpreter?

ANSWER:

It interprets DDL statements and record them in tables containing metadata.

QUESTION 34:

What is Record-at-a-time?

ANSWER:

The Low level or Procedural DML can specify and retrieve each record from a set of records. This retrieve of a record is said to be Record-at-a-time.

QUESTION 35:

What is Set-at-a-time or Set-oriented?

ANSWER:

The High level or Non-procedural DML can specify and retrieve many records in a single DML statement. This retrieve of a record is said to be Set-at-a-time or Set-oriented.

QUESTION 36:

What is Relational Algebra?

ANSWER:

It is procedural query language. It consists of a set of operations that take one or two relations as input and produce a new relation.

QUESTION 37:

What is Relational Calculus?

ANSWER:

It is an applied predicate calculus specifically tailored for relational databases proposed by E.F. Codd. E.g. of languages based on it are DSL ALPHA, QUEL.

QUESTION 38:

How does Tuple-oriented relational calculus differ from domain-oriented relational calculus?

ANSWER:

The tuple-oriented calculus uses a tuple variables i.e., variable whose only permitted values are tuples of that relation. E.g. QUEL

The domain-oriented calculus has domain variables i.e., variables that range over the underlying domains instead of over relation. E.g. ILL, DEDUCE.

QUESTION 39:

What is normalization?

ANSWER:

It is a process of analysing the given relation schemas based on their Functional Dependencies (FDs) and primary key to achieve the properties

? Minimizing redundancy

? Minimizing insertion, deletion and update anomalies.

QUESTION 40:

What is Functional Dependency?

ANSWER:

A Functional dependency is denoted by X Y between two sets of attributes X and Y that are subsets of R specifies a constraint on the possible tuple that can form a relation state r of R. The constraint is for any two tuples t1 and t2 in r if t1[X] = t2[X] then they have t1[Y] = t2[Y]. This means the value of X component of a tuple uniquely determines the value of component Y.

QUESTION 41:

When is a functional dependency F said to be minimal?

ANSWER:

? Every dependency in F has a single attribute for its right hand side.

? We cannot replace any dependency X A in F with a dependency Y A where Y is a proper subset of X and still have a set of dependency that is equivalent to F.

? We cannot remove any dependency from F and still have set of dependency that is equivalent to F.

QUESTION 42:

What is Multivalued dependency?

ANSWER:

Multivalued dependency denoted by X Y specified on relation schema R, where X and Y are both subsets of R, specifies the following constraint on any relation r of R: if two tuples t1 and t2 exist in r such that t1[X] = t2[X] then t3 and t4 should also exist in r with the following properties

? t3[x] = t4[X] = t1[X] = t2[X]

? t3[Y] = t1[Y] and t4[Y] = t2[Y]

? t3[Z] = t2[Z] and t4[Z] = t1[Z]

where [Z = (R-(X U Y)) ]

QUESTION 43:

What is Lossless join property?

ANSWER:

It guarantees that the spurious tuple generation does not occur with respect to relation schemas after decomposition.

QUESTION 44:

What is 1 NF (Normal Form)?

ANSWER:

The domain of attribute must include only atomic (simple, indivisible) values.

QUESTION 45:

What is Fully Functional dependency?

ANSWER:

It is based on concept of full functional dependency. A functional dependency X Y is full functional dependency if removal of any attribute A from X means that the dependency does not hold any more.

QUESTION 46:

What is 2NF?

ANSWER:

A relation schema R is in 2NF if it is in 1NF and every non-prime attribute A in R is fully functionally dependent on primary key.

QUESTION 47:

What is 3NF?

ANSWER:

A relation schema R is in 3NF if it is in 2NF and for every FD X A either of the following is true

? X is a Super-key of R.

? A is a prime attribute of R.

In other words, if every non prime attribute is non-transitively dependent on primary key.

QUESTION 48:

What is BCNF (Boyce-Codd Normal Form)?

ANSWER:

A relation schema R is in BCNF if it is in 3NF and satisfies an additional constraint that for every FD X A, X must be a candidate key.

QUESTION 49:

What is 4NF?

ANSWER:

A relation schema R is said to be in 4NF if for every Multivalued dependency X Y that holds over R, one of following is true

? X is subset or equal to (or) XY = R.

? X is a super key.

QUESTION 50:

What is 5NF?

ANSWER:

A Relation schema R is said to be 5NF if for every join dependency {R1, R2, ..., Rn} that holds R, one the following is true

? Ri = R for some i.

? The join dependency is implied by the set of FD, over R in which the left side is key of R