Database Design(Normal Forms)

Question 31

Consider the following entity relationship diagram (ERD), where two entities E1 and E2 have a relation R of cardinality 1 : m.

The attributes of E1 are A11, A12 and A13 where A11 is the key attribute. The attributes of E2 are A21, A22 and A23 where A21 is the key attribute and A23 is a multi-valued attribute. Relation R does not have any attribute. A relational database containing minimum number of tables with each table satisfying the requirements of the third normal form (3NF) is designed from the above ERD. The number of tables in the database is

• 2
• 3
• 5
• 4

Question 32

A relational database contains two tables student and department in which student table has columns roll_no, name and dept_id and department table has columns dept_id and dept_name. The following insert statements were executed successfully to populate the empty tables:

Insert into department values (1, \'Mathematics\')
Insert into department values (2, \'Physics\')
Insert into student values (l, \'Navin\', 1)
Insert into student values (2, \'Mukesh\', 2)
Insert into student values (3, \'Gita\', 1) 

How many rows and columns will be retrieved by the following SQL statement?

Select * from student, department

• 0 row and 4 columns
• 3 rows and 4 columns
• 3 rows and 5 columns
• 6 rows and 5 columns

Question 33

Consider the entities \'hotel room\', and \'person\' with a many to many relationship \'lodging\' as shown below: If we wish to store information about the rent payment to be made by person (s) occupying different hotel rooms, then this information should appear as an attribute of  

• Person
• Hotel Room
• Lodging
• None of these

Question 34

A table has fields Fl, F2, F3, F4, F5 with the following functional dependencies F1 → F3   F2→ F4   (F1 . F2) → F5 In terms of Normalization, this table is in  

• 1 NF
• 2 NF
• 3 NF
• none

Question 35

Which of the following is NOT a superkey in a relational schema with attributes V, W, X, Y, Z and primary key V Y ?

• V X Y Z
• V W X Z
• V W X Y
• V W X Y Z

Question 36

Let R (A, B, C, D, E, P, G) be a relational schema in which the following functional depen­dencies are known to hold: AB → CD, DE → P, C → E, P → C and B → G. The relational schema R is

• in BCNF
• in 3NF, but not in BCNF
• in 2NF, but not in 3NF
• not in 2NF

Question 37

Which option is true about the SQL query given below?

SELECT firstName, lastName 
FROM Employee 
WHERE lastName BETWEEN \'A%\' AND \'D%\';

• It will display all the employees having last names starting with the alphabets \'A\' till \'D\' inclusive of A and exclusive of D.
• It will throw an error as BETWEEN can only be used for Numbers and not strings.
• It will display all the employees having last names starting from \'A\' and ending with \'D\'.
• It will display all the employees having last names in the range of starting alphabets as \'A\' and \'D\' excluding the names starting with \'A\' and \'D\'.

Question 38

Which of the given options define a transaction correctly?

• A transaction consists of DDL statements on the database schema.
• A transaction consists of COMMIT or ROLLBACK in a database session.
• A transaction consists of either a collection of DML statements or a DDL or DCL or TCL statement to form a logical unit of work in a database session.
• A transaction consists of collection of DML and DDL statements in different sessions of the database.

Question 39

Let R = ( A, B, C, D, E, F ) be a relation scheme with the following dependencies: C→F, E→A, EC→D, A→B. Which of the following is a key of R?

• CD
• EC
• AE
• AC

Question 40

Consider the schema R= ( S, T, U, V ) and the dependencies S→T, T→U, U→V and V→S. Let R (R1 and R2) be a decomposition such that R1∩R2 ≠ Ø. The decomposition is:

• Not in 2NF
• In 2NF but not in 3NF
• In 3NF but not in 2NF
• In both 2NF and 3NF