JUNE 2013 - PAPER III

7. The “PROJECT” operator of a relational algebra creates a new table that has always
(A) More columns than columns in original table
(B) More rows than original table
(C) Same number of rows as the original table
(D) Same number of columns as the original table

Ans:-C

Explanation:-
The Projection operator is also a unary operator. Whereas the selection operator chooses a subset of the rows of the relation, the projection operator chooses a subset of the columns. The Projection operation on a table, simply forms another table by copying specified columns, from the original table. Symbol of projection is π. Given a employee table having the columns(Emp-id, name, salary).
To select only the name of employee :- π_name(employee)
To know only the salary :- π_salary(employee)
Selection operation:
It yields a horizontal subset of a given relation that is the subset of row should be selected with in the given relation for which a particular condition is satisfied. Sign of selection is σ. To see the details of those employees whose salary is greater than 10000, the selection operation will be used.
σ_salary>10000(employee).
So, looking at the options given for the answer, the PROJECT operator cannot create a table having more columns than the original table. Same number of columns also ruled out. More rows than the original table is also not correct. So, it will be Same number of rows as the original table, but less number of columns than the original table. SO, the correct answer is C.

---

8. The employee information of an Organization is stored in the relation : Employee (name, sex, salary, deptname) Consider the following SQL query Select deptname from Employee Where sex = ‘M’ group by deptname having avg (salary) > {select avg (salary) from Employee} Output of the given query corresponds to
(A) Average salary of employee more than average salary of the organization.
(B) Average salary less than average salary of the organization.
(C) Average salary of employee equal to average salary of the organization.
(D) Average salary of male employees in a department is more than average salary of the organization

Ans:- D

Explanation:-
The question is very length and intimidating. But the answer is surprisingly simple. Rule out the wrong options first and you will always get the correct answer. There is a greater than symbol used in the query and we all know what it stands for. Options B and C says that Average salary less and equal. So both B and C are ruled out. Option A says Average salary of employee more than average salary of the organization. But in the query there is a where clause which is sex='M' and so the query is average salary of male employees is more than average salary of the organization. So the correct answer is option D.

---

9. For a database relation R(a, b, c, d) where the domains of a, b, c, d include only the atomic values. The functional dependency a → c, b → d holds in the following relation
(A) In 1NF not in 2NF
(B) In 2NF not in 3NF
(C) In 3NF
(D) In 1NF

Ans:-A

Explanation:-
A relation is in the first normal form if the domain of each attribute contains only atomic values. It means atomicity must be present in the relation. So, the question clearly mentions the same. The domains of a,b,c,d include only the atomic values and so 1NF holds but the question is whether 2NF holds or not. A relation will be in second normal form if it is in the first normal form and all the non primary key attribute must be fully functionally dependent on the primary key attribute. Although the functional dependency is given, no information is provided about the primary key here. So the correct answer is A.

---