Test: ER-Model- 2 - Computer Science Engineering (CSE) MCQ

Option A:
This is a SQL query expression. It first perform a cross product of Students and Registration, then WHERE clause only keeps those rows in the cross product set where the student is registered for course no 107, and percentage is > 90. Then select distinct statement gives the distinct names of those students as the result set.

Option B:
This is a relational algebra expression. It first perform a NATURAL JOIN of Students and Registration (NATURAL JOIN implicitly joins on the basis of common attribute, which here is rollno ), then the select operation( sigma) keeps only those rows where the student is registered for courseno 107, and percentage is > 90. And then the projection operation (pi) projects only distinct student names from the set.
Note: Projection operation (pi) always gives the distinct result.

Option C:
This is a Tuple Relational Calculus (TRC) language expression, It is not a procedural language (i.e. it only tells “what to do”, not “how to do”). It just represents a declarative mathematical expression.
Here T is a Tuple variable.
From left to right, it can be read like this, “It is a set of tuples T, where, there exists a tuple S in Relation Students, and there exist a tuple R in relation Registration, such that S.rollno = R.rollno AND R.couseno = 107 AND R.percent > 90 AND T.sname = S.sname”. And the schema of this result is (sname), i.e. each tuple T will contain only student name, because only T.sname has been defined in the expression.
As TRC is a mathematical expression, hence it is expected to give only distinct result set.

Option D:
This is a Domain Relational Calculus (DRC) language expression. This is also not procedural. Here SN is a Domain Variable. It can be read from left to right like this “The set of domain variable SN, where, there exist a domain variable SR , and a domain variable Rp, such that, SN and SR domain variables is in relation Students and SR,107,RP is a domain variables set in relation Registration, AND RP > 90 “ Above, SN represents sname domain attribute in Students relation, SR represents rollno domain attribute in Students relation, and RP represents percentage domain attribute in Registration relation. The schema for the result set is (SN), i.e. only student name.

As DRC is a mathematical expression, hence it is expected to give only distinct result set.

The term ‘entity’ belongs to ER model and the term ‘relational table’ belongs to relational model. A and B both are true. ER model supports both multivalued and composite attributes See this for more details. (C) is false and (D) is true. In Relation model, an entry in relational table can can have exactly one value or a NULL.

B is a foreign key in r1 that refers to C in r2. r1 and r2 satisfy referential integrity constraints. So every value that exists in column B of r1 must also exist in column C of r2.

Result of AUB will be following table

Id   Name    Age
----------------
12   Arun    60
15   Shreya  24
99   Rohit   11
25   Hari    40
98   Rohit   20

The result of given relational algebra expression will be

Id   Name    Age  Id   Phone Area
---------------------------------
12   Arun    60   10   2200   02
15   Shreya  24   10   2200   02   
99   Rohit   11   10   2200   02
25   Hari    40   10   2200   02
98   Rohit   20   10   2200   02
99   Rohit   11   99   2100   01
98   Rohit   20   99   2100   01

If record are accessed for a particular value from table, hashing will do better. If records are accessed in a range of values, ordered indexing will perform better.

Some transformation rules for tuple relational calculus are : 

Thus, using the above rules option (C) is correct. 
 
 

Statement I : ΠP(R⋈S)
In I, Ps from natural join of R and S are selected.
Statement II: ΠP(R)⋈ΠP(S)
II is not equivalent as it may also include Ps where Qs are not same in R and S.

Statement III: ΠPΠP,Q(R)∩ΠP,Q(S)
All Ps from the intersection of ( P,Q ) pairs present in R and S.
Statement IV ΠPΠP,Q(R)-ΠP,Q(R)-ΠP,Q(S)
It is also equivalent to III because of (R-(R-S))=R∩S.

Answer is B, i.e, 3 minimum tables. M, P are strong entities hence they must be represented by separate tables. Many-to-one and one-to-many relationship sets that are total on the many-side can be represented by adding an extra attribute to the “many” side, containing the primary key of the “one” side. ( This way no extra table will be needed for Relationship sets ) M table is modified to include primary key of P side(i.e. P1). N is weak entity, and is modified to include primary key of P (i.e, P1). Therefore there would be minimum of 3 tables with schema given below :
M (M1, M2, M3, P1)
P (P1, P2)
N (P1, N1, N2)

Note: This modification of a table in the case of one-many or many-one to include relationship set at the many side works well, but only in the case when the relationship set doesn't have its own attributes. If the relationship set has its own attribute then we need to make a separate table for the relationship set also.

We get 3 tables. M: {M1, M2, M3, P1} P: {P1, P2} N: {P1, N1, N2} The only attribute set that matches the given table sets is A. Therefore, option A

The expression given in question does following steps in sequence.
a) Select studids of all female students and selects all courseids of all courses.
b) Then the query does a Cartesian Product of the above select two columns from different tables.
c) Finally it subtracts enroll table from the result of above step (b). This will remove all the (studid, courseid) pairs which are present in enroll table. If all female students have registered in a courses, then this course will not be there in the subtracted result.
So the complete expression returns courses in which a proper subset of female students are enrolled.

OR (∨) is commutative and associative, therefore i can rewrite given query as:

It is clear now they are saying something about female employees, This query does not say anything about male employees. Therefore Option A and B are out of consideration.

This query retrieves those e.name who satisfies this condition:

Means retrieves those e.name, who is not a supervisor of any female employees.
i.e it retrieves name of employees with no female subordinate.
(here "immediate" is obvious, as we are checking first level supervisor.)
Hence, option C.

Nested loop join is one of the methods to implement database in memory. A nested loop join is an  algorithm that joins two sets by using two nested loops.

According to nested join,given relation R and S   For each tuple r in R do For each tuple s in S do If r and s satisfy the join condition Then output the tuple <r,s> Cost estimations for the above loop: – b(R) and  b(S) number of blocks in R and in S – Each block of outer relation is read once – Inner relation is read once for each block of outer relation Summing up : IO= b(R)+b(R)*b(S) total  IO operations Lets assume |R|>|S|  i.e b(R) =10  and b(s) =3 Now,   if R is outer relation then, IO= 10+10*3=40 if S is outer relation then IO=3+10*3=33 As it can be observed , that total IO is lesser if the value of outer variable is less and as it is already given that |R|<|S|.Therefore,  Relation r(R) should be in the outer loop to have fewer number of disk block accesses.

The Relational Algebra expression in the question above, does 4 operations, step by step ( innermost braces first ) .

1. Select those tuples from relation r which satisfies expression/condition F1, say the result of this operation is set A.
2. Select those tuples from set A which satisfies expression/condition F2, say the result of this operation is set B.
3. Select attrributes set A2 from set B, say the result of this operation is set C.
4. Select attrributes set A1 from set C, say the result is set D which is the final result.

Now to optimize this expression, we can combine operations/steps 1 and 2 by AND operator between F1 and F2 condition, like F1 ^ F2, and instead of selecting first attribute set A2, we can directly select attribute set A1 from the result of the combined operation, which is represented by expression in Option A .

The below subquery after the subtraction sign produces id's of those employees who have at least one dependent with age greater than or equal the employee's age.

When the result of above subquery is subtracted from all employees, we get the employees whose age is greater than all dependents.

The answer is B, i.e minimum 3 tables. Strong entities E1 and E2 are represented as separate tables. In addition to that many-to-many relationships(R2) must be converted as seperate table by having primary keys of E1 and E2 as foreign keys. One-to-many relaionship (R1) must be transferred to 'many' side table(i.e. E2) by having primary key of one side(E1) as foreign key( this way we need not to make a seperate table for R1). Let relation schema be E1(a1,a2) and E2( b1,b2). Relation E1( a1 is the key)

Relation E2( b1 is the key, a1 is the foreign key, hence R1(one-many) relationship set satisfy here)

Relation R2 ( {a1, b1} combined is the key here , representing many-many relationship R2)

Hence we will have minimum of 3 tables.

Apply membership test for all the given Functional Dependencies.

i.e. BD cannot derive CD

and hence is not implied.

Similarly do for rest two.

Volume, Number -> Year is partial dependency. So it does not follow 2NF. But decomposed relation follows.

Relational algebra is a procedural query language where we input - relations and it yields relations as output. It provides method to get the result. It is performed recursively on a relation and the in between results are relations(output). Basic set of operations for the relational model. Relational calculus is a non - procedural query language. It provides the query to get result. Higher level declarative language for specifying relational queries. Tupple Relational Calculus operates on each tupple. Domain Relational Calculus operates on each column or attribute. Safe expression means fixed no. of tupple or column or attribute as a result But all of them has same expressive power. Just different ways to do so.

Simple candidate key means single attributed key. As (A)⁺ = {A, B, C, D}, (B)⁺ = {B,A, C, D}, (C)⁺ = {C} and (D)⁺ = {D, A, B, C}. So, A, B and D are candidate keys which are simple as well. So, correct option is 3.

Maximum number of tables required is 3 in case of many to many relationships between entities. Minimum number of tables is 1 in case of unary relationship and total participation of atleast one entity. But in case of partial participation of both entities, minimum number of tables required is 2.