Test: Normal Forms- 2


Test Description

20 Questions MCQ Test RRB JE for Computer Science Engineering | Test: Normal Forms- 2

Test: Normal Forms- 2 for Computer Science Engineering (CSE) 2022 is part of RRB JE for Computer Science Engineering preparation. The Test: Normal Forms- 2 questions and answers have been prepared according to the Computer Science Engineering (CSE) exam syllabus.The Test: Normal Forms- 2 MCQs are made for Computer Science Engineering (CSE) 2022 Exam. Find important definitions, questions, notes, meanings, examples, exercises, MCQs and online tests for Test: Normal Forms- 2 below.
Solutions of Test: Normal Forms- 2 questions in English are available as part of our RRB JE for Computer Science Engineering for Computer Science Engineering (CSE) & Test: Normal Forms- 2 solutions in Hindi for RRB JE for Computer Science Engineering course. Download more important topics, notes, lectures and mock test series for Computer Science Engineering (CSE) Exam by signing up for free. Attempt Test: Normal Forms- 2 | 20 questions in 60 minutes | Mock test for Computer Science Engineering (CSE) preparation | Free important questions MCQ to study RRB JE for Computer Science Engineering for Computer Science Engineering (CSE) Exam | Download free PDF with solutions
1 Crore+ students have signed up on EduRev. Have you?
Test: Normal Forms- 2 - Question 1

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?

Detailed Solution for Test: Normal Forms- 2 - Question 1

A+ is ABCEFGH which is all attributes except D. B+ is also ABCEFGH which is all attributes except D. E+ is also ABCEFGH which is all attributes except D. F+ is also ABCEFGH which is all attributes except D. So there are total 4 candidate keys AD, BD, ED and FD

Test: Normal Forms- 2 - Question 2

Consider the FDs given in above question. The relation R is

Detailed Solution for Test: Normal Forms- 2 - Question 2

The table is not in 2nd Normal Form as the non-prime attributes are dependent on subsets of candidate keys. The candidate keys are AD, BD, ED and FD. In all of the following FDs, the non-prime attributes are dependent on a partial candidate key. A -> BC B -> CFH F -> EG

Test: Normal Forms- 2 - Question 3

Which of the following is TRUE?

Detailed Solution for Test: Normal Forms- 2 - Question 3

BCNF is a stronger version 3NF. So every relation in BCNF will also be in 3NF.

Test: Normal Forms- 2 - Question 4

Consider a relational table with a single record for each registered student with the following attributes.

1. Registration_Num: Unique registration number of each registered student
2. UID: Unique identity number, unique at the national level for each citizen
3. BankAccount_Num: Unique account number at the bank. A student can have multiple accounts or join accounts. This attribute stores the primary account number.
4. Name: Name of the student
5. Hostel_Room: Room number of the hostel


Q. Which one of the following option is INCORRECT?

Detailed Solution for Test: Normal Forms- 2 - Question 4

A Candidate Key value must uniquely identify the corresponding row in table. BankAccount_Number is not a candidate key. As per the question “A student can have multiple accounts or joint accounts. This attributes stores the primary account number”. If two students have a joint account and if the joint account is their primary account, then BankAccount_Number value cannot uniquely identify a row.

Test: Normal Forms- 2 - Question 5

Consider the following relational schema:

Suppliers(sid:integer, sname:string, city:string, street:string)
Parts(pid:integer, pname:string, color:string)
Catalog(sid:integer, pid:integer, cost:real)


Q. Assume that, in the suppliers relation above, each supplier and each street within a city has a unique name, and (sname, city) forms a candidate key. No other functional dependencies are implied other than those implied by primary and candidate keys. Which one of the following is TRUE about the above schema?

Detailed Solution for Test: Normal Forms- 2 - Question 5

A relation is in BCNF if for every one of its dependencies X → Y, at least one of the following conditions hold:

X → Y is a trivial functional dependency (Y ⊆ X)
X is a superkey for schema R

Since (sname, city) forms a candidate key, there is no non-tirvial dependency X → Y where X is not a superkey

Test: Normal Forms- 2 - Question 6

Consider the following relational schemes for a library database: Book (Title, Author, Catalog_no, Publisher, Year, Price) Collection (Title, Author, Catalog_no) with in the following functional dependencies:

I. Title Author --> Catalog_no
II. Catalog_no --> Title, Author, Publisher, Year
III. Publisher Title Year --> Price

 

Q. Assume {Author, Title} is the key for both schemes. Which of the following statements is true?

Detailed Solution for Test: Normal Forms- 2 - Question 6

Book (Title, Author, Catalog_no, Publisher, Year, Price)
Collection (Title, Author, Catalog_no)
with in 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

  • The table "Collection" is in BCNF as there is only one functional dependency “Title Author –> Catalog_no” and {Author, Title} is key for collection.
  • Book is not in BCNF because Catalog_no is not a key and there is a functional dependency “Catalog_no –> Title Author Publisher Year”.
  • Book is not in 3NF because non-prime attributes (Publisher Year) are transitively dependent on key [Title, Author].
  • Book is in 2NF because every non-prime attribute of the table is either dependent on the whole of a candidate key [Title, Author], or on another non prime attribute. In table book, candidate keys are {Title, Author} and {Catalog_no}. In table Book, non-prime attributes (attributes that do not occur in any candidate key) are Publisher, Year and Prince

Please refer Database Normalization | Normal Forms for details of normal forms.

Test: Normal Forms- 2 - Question 7

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

Detailed Solution for Test: Normal Forms- 2 - Question 7

All attributes can be derived from {E, F, H} To solve these kind of questions that are frequently asked in GATE paper, try to solve it by using shortcuts so that enough amount of time can be saved.
Fist Method: Using the given options try to obtain closure of each options. The solution is the one that contains R and also minimal Super Key, i.e Candidate Key.

A) {EF}+ = {EFGIJ} ≠ R(The given relation)
B) {EFH}+ = {EFGHIJKLMN} = R (Correct since each member of the given relation is determined)
C) {EFHKL}+ = {EFGHIJKLMN} = R (Not correct although each member of the given relation can be determined but it is not minimal, since by the definition of Candidate key it should be minimal Super Key)
D) {E}+ = {E} ≠ R

Second Method:

Since, {EFGHIJKLMN}+ = {EFGHIJKLMN}
{EFGHIJKLM}+ = {EFGHIJKLMN} (Since L -> {N}, hence can replace N by L)
In a similar way K -> {M} hence replace M by K
{EFGHIJKL}+ = {EFGHIJKLMN}
Again {EFGHIJ}+ = {EFGHIJKLMN} (Since {E, H} -> {K, L}, hence replace KL by EH)
{EFGH}+ = {EFGHIJKLMN} (Since {F} -> {I, J})
{EFH}+ = {EFGHIJKLMN} (Since {E, F} -> {G})

Test: Normal Forms- 2 - Question 8

Given the following two statements:

S1: Every table with two single-valued attributes is in 1NF, 2NF, 3NF and BCNF.
S2: AB->C, D->E, E->C is a minimal cover for the set of functional dependencies AB->C, D->E, AB->E, E->C.


Q. Which one of the following is CORRECT?

Detailed Solution for Test: Normal Forms- 2 - Question 8

S1: Every table with two single-valued
attributes is in 1NF, 2NF, 3NF and BCNF.

A relational schema R is in BCNF iff in Every non-trivial Functional Dependency X->Y, X is Super Key. If we can prove the relation is in BCNF then by default it would be in 1NF, 2NF, 3NF also. Let R(AB) be a two attribute relation, then

  1. If {A->B} exists then BCNF since {A}+ = AB = R
  2. If {B->A} exists then BCNF since {B}+ = AB = R
  3. If {A->B,B->A} exists then BCNF since A and B both are Super Key now.
  4. If {No non trivial Functional Dependency} then default BCNF.

Hence it's proved that a Relation with two single - valued attributes is in BCNF hence its also in 1NF, 2NF, 3NF. Hence S1 is true.

S2: AB->C, D->E, E->C is a minimal cover for the set of functional dependencies
AB->C, D->E, AB->E, E->C.

As we know Minimal Cover is the process of eliminating redundant Functional Dependencies and Extraneous attributes in Functional Dependency Set. So each dependency of F = {AB->C, D->E, AB->E, E->C} should be implied in minimal cover. As we can see AB->E is not covered in minimal cover since {AB}+ = ABC in the given cover {AB->C, D->E, E->C} Hence, S2 is false.

Test: Normal Forms- 2 - Question 9

The maximum number of superkeys for the relation schema R(E,F,G,H) with E as the key is

Detailed Solution for Test: Normal Forms- 2 - Question 9

Maximum no. of possible superkeys for a table with n attributes = 2^(n-1) Here, n = 4. So, the possible superkeys = 24-1 = 8 The possible superkeys are : E, EH, EG, EF, EGH, EFH, EFG, EFGH

Test: Normal Forms- 2 - Question 10

Given the STUDENTS relation as shown below.

For (StudentName, StudentAge) to be the key for this instance, the value X should not be equal to

Detailed Solution for Test: Normal Forms- 2 - Question 10

There is already an entry with same name and age as 19. So the age of this entry must be something other than 19.

Test: Normal Forms- 2 - Question 11

Which one of the following statements about normal forms is FALSE?

Test: Normal Forms- 2 - Question 12

Let r be a relation instance with schema R = (A, B, C, D). We define r1 = ΠA, B, C (r) and r2 = ΠA.D (r). Let s = r1 * r2 where * denotes natural join. Given that the decomposition of r into r1 and r2 is lossy, which one of the following is TRUE?

Test: Normal Forms- 2 - Question 13

Consider a relation scheme R = (A, B, C, D, E, H) on which the following functional dependencies hold: {A–>B, BC–>D, E–>C, D–>A}. What are the candidate keys of R?

Test: Normal Forms- 2 - Question 14

Let R1 (A, B, C) and R2 (D, E) be two relation schema, where the primary keys are shown underlined, and let C be a foreign key in R1 referring to R2. Suppose there is no violation of the above referential integrity constraint in the corresponding relation instances r1 and r2. Which one of the following relational algebra expressions would necessarily produce an empty relation ?

Detailed Solution for Test: Normal Forms- 2 - Question 14

Since C is a foreign key in R1 and there is no violation of the above referential integrity constraint, the set of values in C must be a subset of values in R2.

Test: Normal Forms- 2 - Question 15

The relation scheme Student Performance (name, courseNo, rollNo, grade) has the following functional dependencies:

name, courseNo → grade
rollNo, courseNo → grade
name → rollNo
rollNo → name


Q. The highest normal form of this relation scheme is

Detailed Solution for Test: Normal Forms- 2 - Question 15

For easy understanding let's say attributes (name, courseNo, rollNo, grade) be (A,B,C,D)
Then given FDs are as follows:
AB->D, CB->D, A->C, C->A
Here there are two Candidate keys, AB and CB.
Now AB->D and CB->D satisfy BCNF as LHS is superkey in both.
But, A->C and C->A, doesn't satisfy BCNF. Hence we check for 3NF for these 2 FDs.
As C and A on RHS of both the FDs are prime attributes, they satisfy 3NF.
Hence for the whole relation the highest normal form is 3NF.

Test: Normal Forms- 2 - Question 16

Consider the relation Student (name, sex, marks), where the primary key is shown underlined, pertaining to students in a class that has at least one boy and one girl. What does the following relational algebra expression produce? (Note: r is the rename operator).

 


 Q. The condition in join is "(sex = female ^ x = male ^ marks ≤ m)"

Detailed Solution for Test: Normal Forms- 2 - Question 16

The above relational algebra expression has two sub expressions. The first one takes as input the Student relation (Student) and filters out all the tuples where sex=female(r sex=female (Student)) and then projects their names (P name r sex=female (Student)). So we get a new relation with names of all the female students.

The second one takes as input the Student relation and performs a rename operation on one with attributes name, sex and marks renamed as n, x, m respectively (r n, x, m(Student)) and then followed by a self-Cartesian product on the Student relation. The condition (sex = female ^ m = male ^ marks ≤ m) filters tuples with all female students from the first relation, male students from the second relation and performs a Cartesian product where marks of the female student is either less than or equal to a male student and then projects their names. So we get a new relation with names of all female students whose marks are lesser than at least one of the male student.

The difference operator(-) between the two subexpressions gives the names of all female students whose marks are more than all male students of the class. (From all the female students’ names we remove all those whose marks are at least more the one male student)

Test: Normal Forms- 2 - Question 17

Consider the following functional dependencies in a database:

Data_of_Birth → Age
Age → Eligibility
Name → Roll_number
Roll_number → Name
Course_number → Course_name
Course_number → Instructor
(Roll_number, Course_number) → Grade


Q. The relation (Roll_number, Name, Date_of_birth, Age) is:

Detailed Solution for Test: Normal Forms- 2 - Question 17

The given table is not in 2NF as age is dependent on date of birth.

Test: Normal Forms- 2 - Question 18

Relation R with an associated set of functional dependencies, F is decomposed into BCNF. The redundancy (arising out of functional dependencies) in the resulting set relations is.

Detailed Solution for Test: Normal Forms- 2 - Question 18

If a relational schema is in BCNF then all redundancy based on functional dependency has been removed, although other types of redundancy may still exist.

Test: Normal Forms- 2 - Question 19

With regard to the expressive power of the formal relational query languages, which of the following statements is true?

Detailed Solution for Test: Normal Forms- 2 - Question 19

A query can be formulated in relational calculus if and only if it can be formulated in relational algebra. So, relational algebra has the same power as relational calculus. 
But, it is possible to write syntactically correct relational calculus queries that have infinite number of answers. Such queries are unsafe. Queries that have an finite number of answers are safe relational calculus queries. 
Thus, Relational algebra has the same power as safe relational calculus. 
 
Thus, option (C) is the answer. 
 
Please comment below if you find anything wrong in the above post.

Test: Normal Forms- 2 - Question 20

 Functional Dependencies are the types of constraints that are based on______

Use Code STAYHOME200 and get INR 200 additional OFF
Use Coupon Code
Information about Test: Normal Forms- 2 Page
In this test you can find the Exam questions for Test: Normal Forms- 2 solved & explained in the simplest way possible. Besides giving Questions and answers for Test: Normal Forms- 2, EduRev gives you an ample number of Online tests for practice