DBMS

DBMS [CBSE NET JUNE 2015 Paper III]

Question 7 DBMS

7. Let E1 and E2 be two entities in E-R diagram with simple single valued attributes. R1 and R2 are two relationships between E1 and E2 where R1 is one-many and R2 is many - many. R1 and R2 donot have any attributes of their own. How many minimum number of tables are required to represent this situation in the relational model?

A. 4

B. 3

C. 2

D. 1
Answer: B)   Strong entities E1 and E2 should be converted into tables. For R1, which is one to many relations, there is no need of a separate table. the "many" side of relation will include the primary key of "one" side as foreign key. For R2, which is many to many relation, a separate table is required by including the primary key of E1 and E2 as foreign keys. Hence we require a minimum of 3 tables.

Question 8 DBMS

8. The student information in a university is stored in the relation STUDENT (name, sex, marks, dept_Name) Consider the following SQL query select deptName from STUDENT where sex = 'M' group by dept_Name having avg (marks) > (select avg (marks) from STUDENT) It returns the names of the department in which

A. the average marks of male students is more than the average marks of the students in the same department

B. The average marks of male students is more than the average marks of the students in the university

C. The average marks of male students is more than the average marks of male students in the university

D. The average marks of students is more than the average marks of male students in the university

Answer 8: B)

Question 9 DBMS

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

A. Lossless, preserving decomposition into 3NF is always possible

B. Lossless, preserving decomposition into BCNF is always possible

C. Any relation with two attributes is in BCNF

D. BCNF is stronger than 3NF

Answer : B)

    

Difference between 3NF and BCNF

3NF vs BCNF 

Normalization is a process that is carried out to minimize the redundancies that are present in data in relational databases. This process will mainly divide large tables in to smaller tables with fewer redundancies. These smaller tables will be related to each other through well defined relationships. In a well normalized database, any alteration or modification in data will require modifying only a single table. Third Normal Form (3NF) was introduced in 1971 by Edgar F. Codd, who is also the inventor of the relational model and the concept of normalization. Boyce-Codd Normal Form (BCNF) was introduced in 1974 by Codd and Raymond F. Boyce.

What is 3NF?

3NF is the Third normal form used in relational database normalization. According to the Codd’s definition, a table is said to be in 3NF, if and only if, that table is in the second normal form (2NF), and every attribute in the table that do not belong to a candidate key should directly depend on every candidate key of that table. In 1982 Carlo Zaniolo produced a differently expressed definition for 3NF. Tables that comply with the 3NF generally do not contain anomalies that occur when inserting, deleting or updating records in the table.

What is BCNF?

BCNF (also known as 3.5NF) is another normal form used in relational database normalization. It was introduced to capture some the anomalies that are not addressed by the 3NF. A table is said to be in BCNF, if and only if, for each of the dependencies of the form A → B that are non-trivial, A is a super-key. Decomposing a table that is not in the BCNF normal form does not guarantee the production of tables in the BCNF form (while preserving the dependencies which were present in the original table).

What is the difference between 3NF and BCNF?

Both 3NF and BCNF are normal forms that are used in relational databases to minimize redundancies in tables. In a table that is in the BCNF normal form, for every non-trivial functional dependency of the form A → B, A is a super-key whereas, a table that complies with 3NF should be in the 2NF, and every non-prime attribute should directly depend on every candidate key of that table. BCNF is considered as a stronger normal form than the 3NF and it was developed to capture some of the anomalies that could not be captured by 3NF. Obtaining a table that complies with the BCNF form will require decomposing a table that is in the 3NF. This decomposition will result in additional join operations (or Cartesian products) when executing queries. This will increase the computational time. On the other hand, the tables that comply with BCNF would have fewer redundancies than tables that only comply with 3NF. Furthermore, most of the time, it is possible to obtain a table that comply with 3NF without hindering dependency preservation and lossless joining. But this is not always possible with BCNF.

Question 10 DBMS

The relation vendor order (v_no, v_ord_no, v_name, qty_sup, unit_price) is in 2NF because :

A. Non key attribute V_name is dependent on V_no which is part of composite key

B. Non key attribute V_name is dependent on qty_sup

C. key attribute qty_sup is dependent on primary key unit price

D. key attribute v_ord_no is dependent on primary key unit price

Answer : A)

In the above question option C and Option d rejected as unit price cannot serve as primary key. option B is also irrelevant as qty_sup has no concern with v_name. so option A seems to be the best answer

Question 11 DBMS

the relational schemas R1 and R2 form a loseless join decomposition of R if and only if

A. R1 ∩ R2–>(R1-R2)
B. R1–>R2
C. R1 ∩ R2–>(R2-R1)
D. R2–>R1 ∩ R2

Answer : C)

A decomposition of R into R1 and R2 is lossless join if and only if at least one of the following dependencies is in F+: 
R1 ∩ R2àR1 
R1 ∩ R2àR2

Question 12 DBMS

In the index allocation scheme of blocks to a file, the maximum possible size of the file depends on
A. The number of blocks used for the index, and the size of the index
B. The size of the blocks, and the size of the address
C. size of the index
D. size of the blocks

Answer : A) When indexes are created, the maximum no. of blocks given to a file are totally dependent upon size of the index which tells how many blocks can be there, & size of each block.