ER and Relational Models

Question 11

Consider the relation employee(name, sex, supervisorName) with name as the key. supervisorName gives the name of the supervisor of the employee under consideration. What does the following Tuple Relational Calculus query produce?

• Names of employees with a male supervisor.
• Names of employees with no immediate male subordinates.
• Names of employees with no immediate female subordinates.
• Names of employees with a female supervisor.

Question 12

Consider a join (relation algebra) between relations r(R)and s(S) using the nested loop method. There are 3 buffers each of size equal to disk block size, out of which one buffer is reserved for intermediate results. Assuming size(r(R)) < size(s(S)), the join will have fewer number of disk block accesses if

• relation r(R) is in the outer loop.

• relation s(S) is in the outer loop.

• join selection factor between r(R) and s(S) is more than 0.5.

• join selection factor between r(R) and s(S) is less than 0.5.

Question 13

• A
• B
• C
• D

Question 14

Consider the relational schema given below, where eId of the relation dependent is a foreign key referring to empId of the relation employee. Assume that every employee has at least one associated dependent in the dependent relation.

employee (empId, empName, empAge)
dependent(depId, eId, depName, depAge) 

Consider the following relational algebra query: The above query evaluates to the set of empIds of employees whose age is greater than that of

• some dependent.
• all dependents.
• some of his/her dependents
• all of his/her dependents.

Question 15

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

• 2

• 3

• 4

• 5

Question 16

In a schema with attributes A, B, C, D and E following set of functional dependencies are given

A → B A → C CD → E B → D E → A

Which of the following functional dependencies is NOT implied by the above set?

• CD → AC
• BD → CD
• BC → CD
• AC → BC

Question 17

A database of research articles in a journal uses the following schema.

(VOLUME, NUMBER, STARTPGE, ENDPAGE, TITLE, YEAR, PRICE) 

The primary key is (VOLUME, NUMBER, STARTPAGE, ENDPAGE) and the following functional dependencies exist in the schema.

(VOLUME, NUMBER, STARTPAGE, ENDPAGE) -> TITLE
(VOLUME, NUMBER)                     -> YEAR
(VOLUME, NUMBER, STARTPAGE, ENDPAGE) -> PRICE 

The database is redesigned to use the following schemas.

(VOLUME, NUMBER, STARTPAGE, ENDPAGE, TITLE, PRICE)
(VOLUME, NUMBER, YEAR) 

Which is the weakest normal form that the new database satisfies, but the old one does not?

• 1NF
• 2NF
• 3NF
• BCNF

Question 18

Which of the following relational query languages have the same expressive power?

1. Relational algebra
2. Tuple relational calculus restricted to safe expressions
3. Domain relational calculus restricted to safe expressions

• II and III only

• I and II only

• I and III only

• I, II and III

Question 19

A Relation R with FD set {A->BC, B->A, A->C, A->D, D->A}. How many candidate keys will be there in R?

• 1
• 2
• 3
• 4

Question 20

What is the min and max number of tables required to convert an ER diagram with 2 entities and 1 relationship between them with partial participation constraints of both entities?

• Min 1 and max 2
• Min 1 and max 3
• Min 2 and max 3
• Min 2 and max 2