Test: Normal Forms- 1 - Computer Science Engineering (CSE) MCQ

Answer is (C) since to identify BCNF we need BCNF definition. One relation which satisfies will be in BCNF and other will be in 3NF. 1st is wrong because dependency may be preserved by both 3NF and BCNF. 2nd is wrong Because both 3NF and BCNF decomposition can be lossless. 4th is wrong because 3NF and BCNF both are in 3NF also.

Generally Normalization is done on the schema itself. From the relational instance given,we may strike out FD s that do not hold. e.g.B does not functionally determine C(This is true). But we cannot say that A functionally determines B for the entire relation itself. This is because that, A->B holds for this instance, but in future there might be some tuples added to the instance that may violate A->B. So overall on the relation we cannot conclude that A->B, from the relational instance which is just a subset of an entire relation.

Dependency Preserving Decomposition:
Decomposition of R into R1 and R2 is a dependency preserving decomposition if closure of functional dependencies after decomposition is same as closure of of FDs before decomposition.
A simple way is to just check whether we can derive all the original FDs from the FDs present after decomposition.

In the above question R(A, B, C, D) is decomposed into R1 (A, B) and R2(C, D) and there are only two FDs A -> B and C -> D. So, the decomposition is dependency preserving

Lossless-Join Decomposition:
Decomposition of R into R1 and R2 is a lossless-join decomposition if at least one of the following functional dependencies are in F+ (Closure of functional dependencies)

R1 ∩ R2 → R1
OR
R1 ∩ R2 → R2

In the above question R(A, B, C, D) is decomposed into R1 (A, B) and R2(C, D), and R1 ∩ R2 is empty. So, the decomposition is not lossless.

(A) This is simple query as we need to find (X, Y) for a given X. (B) This is also simple as need to find (X, X) (C) :-> Cycle < 3 . Means cycle of length 1 & 2. Cycle of length 1 is easy., Same as self loop. Cycle of length 2 is is also not too hard to compute. Though it'll be little complex, will need to do like (X,Y) & (Y, X ) both present & X != Y,. We can do this with constant RA query. (D) :-> This is most hard part. Here we need to find closure of vertices. This will need kind of loop. If the graph is like skewed tree, our query must loop for O(N) Times. We can't do with constant length query here. Answer is :-> D

The above expression evaluates A = a for tables r and s Option A : is only displaying attributes of table r on the select condition Option B : is only displaying attributes of table rOption C: evaluates A = a by joining tables r and s  efficiently , thus correct Therefore, Answer C  

Background :

• Lossless-Join Decomposition:
  Decomposition of R into R1 and R2 is a lossless-join decomposition if at least one of the following functional dependencies are in F+ (Closure of functional dependencies)

R1 ∩ R2 → R1
OR
R1 ∩ R2 → R2

• dependency preserving :
  Decomposition of R into R1 and R2 is a dependency preserving decomposition if closure of functional dependencies after decomposition is same as closure of of FDs before decomposition.
  A simple way is to just check whether we can derive all the original FDs from the FDs present after decomposition.

Question : We know that for lossless decomposition common attribute should be candidate key in one of the relation. A) A->B, B->CD R1(AB) and R2(BCD) B is the key of second and hence decomposition is lossless. B) A->B, B->C, C->D R1(AB) , R2(BC), R3(CD) B is the key of second and C is the key of third, hence lossless. C) AB->C, C->AD R1(ABC), R2(CD) C is key of second, but C->A violates BCNF condition in ABC as C is not a key. We cannot decompose ABC further as AB->C dependency would be lost.D) A ->BCD Already in BCNF. Therefore, Option C AB->C, C->AD is the answer.

A tuple relational calculus expression may at times generate an infinite relation. It may also contain values that do not even appear in the database. Such expressions are said to be unsafe. A safe tuple relational calculus expression is the one which surely generates finite results. To pose a restriction over the unsafety of expressions in tuple relational calculus there is a concept of domain of a tuple relational formula denoted by dom (P) is the set of values referenced by P i.e. values there in P or values in tuple of a relation mentioned in P. Eg: The expression {t | ¬ (t € R)} is not safe because there are infinitely many tuples that do not occur in R relation . In the above question Options (A), (B) and option (D) produce finite set of tuples as each gives out tuples restricted from a particular relation and hence are safe. Option (C) produces infinite number of tuples as it generates all the tuples not in R1 i.e. it can have tuples from any other relation other than R1.Hence it is not safe.

In the above question, the relation schema is (lb , ub), where lb is the primary key, and ub is the foreign key which is referencing the primary key of its own relation. Hence the table geq is both the master (which has the referenced key) as well as the child table (which has the referencing key). The table has two constraint, one is that if there is a tuple (x, y), then y is greater than or equal to x, And the other is referential integrity constraint, which is on-cascade-delete on the foreign key. On-cascade-delete says, that "When the referenced row is deleted from the other table (master table), then delete also from the child table".

Suppose the instance in the given relation is the following:

Now if we delete tuple (5,6) then tuple (4,5) should also be deleted (as 5 in the tuple (4, 5) was referencing to 5 in the tuple(5,6) which no longer exist, hence the referencing tuple should also be deleted), and as (4,5) got deleted hence tuple (3,4) should also be deleted for the same reason. Therefore in total 3 rows have to be deleted if tuple (5,6) is deleted. Now from the above instance we can say that if (x,y), i.e. ( 5,6 ) gets deleted then a tuple (z, w) i.e, (3, 4) is also deleted. And we can see here that w < x. Hence option C.

A functional dependency X -> Y is trivial if Y is a subset of X.

Step 1: 1NF T1: A11, A12, A13 T2: A11, A21, A22, A23  //because A23 is multivalued ,it has to be included in Key attribute Step 2: 2NF // A23 is Multivalued attribute and not allowed in 2NF therefore new tables are: T1: A11, A12, A13 T2: A11, A21, A22 T3: A21, A23 Step 3: 3NF // There is no transitive functional dependency in all tables , So in 3NFTherefore answer is B

Simple,Cartesian product of two tables will result Rows = 3*2=6 Columns= 3+2=5 So Answer is D  

Lodging is the only attribute relating person and hotel room.

First Normal Form A relation is in first normal form if every attribute in that relation is singled valued attribute. Second Normal Form A relation is in 2NF iff it has No Partial Dependency, i.e., no non-prime attribute (attributes which are not part of any candidate key) is dependent on any proper subset of any candidate key of the table. This table has Partial Dependency f1->f3, f2-> f4 given (F1,F2) is Key So answer is A

Super key = Candidate Key + other attributes. But option B does not include Y which is a part of PK or candidate key.

Candidate key = AB
B->G is partial dependency
So, not in 2NF

The BETWEEN operator works with the range of character values also.

A database transaction consists of one or more DML statements to constitute one consistent change in data, or a DDL statement or a DCL command (GRANT or REVOKE). It starts with the first DML statement and ends with a DCL or DDL or TCL (COMMIT or ROLLBACK) command. Note that DDL and DCL commands hold auto commit feature.