ER and Relational Models

  • Last Updated : 09 Oct, 2019 



 
  
        
    
   
  

  
          12345678        
  

  



   
   
  
      
      
    
  

  

  

          
Question 1
Consider the following relational schema.

    Students(rollno: integer, sname: string)
    Courses(courseno: integer, cname: string)
    Registration(rollno: integer, courseno: integer, percent: real)

Which of the following queries are equivalent to this query in English?

      "Find the distinct names of all students who score 
       more than 90% in the course numbered 107"

gatecs201314
A
 I, II, III and IV
B
 I, II and III only
C
I, II and IV only
D
 II, III and IV only
ER and Relational Models    ER and Relational Models    
Discuss it


Question 1 Explanation: 
 
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.


Question 2
Consider the following relational schema.

    Students(rollno: integer, sname: string)
    Courses(courseno: integer, cname: string)
    Registration(rollno: integer, courseno: integer, percent: real)

Which of the following queries are equivalent to this query in English?

      "Find the distinct names of all students who score 
       more than 90% in the course numbered 107"

gatecs201314
A
 I, II, III and IV
B
 I, II and III only
C
I, II and IV only
D
 II, III and IV only
ER and Relational Models    ER and Relational Models    
Discuss it


Question 2 Explanation: 
 
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.


Question 3
Given the basic ER and relational models, which of the following is INCORRECT?

A
An attribute of an entity can have more than one value
B
 An attribute of an entity can be composite
C
In a row of a relational table, an attribute can have more than one value
D
 In a row of a relational table, an attribute can have exactly one value or a NULL value
GATE CS 2012    ER and Relational Models    
Discuss it


Question 3 Explanation: 
 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.
Question 4
Suppose (A, B) and (C,D) are two relation schemas. Let r1 and r2 be the corresponding relation instances. B is a foreign key that refers to C in r2. If data in r1 and r2 satisfy referential integrity constraints, which of the following is ALWAYS TRUE?

DBMSGATE2012
A
A
B
B
C
C
D
D
GATE CS 2012    ER and Relational Models    
Discuss it


Question 4 Explanation: 
 See Question 3 of http://www.geeksforgeeks.org/database-management-system-set-2/
Question 5
Consider the following relations A, B, C. How many tuples does the result of the following relational algebra expression contain? Assume that the schema of A U B is the same as that of A.




Table A
Id   Name    Age
----------------
12   Arun    60
15   Shreya  24
99   Rohit   11


Table B
Id   Name   Age
----------------
15   Shreya  24
25   Hari    40
98   Rohit   20
99   Rohit   11


Table C
Id   Phone  Area
-----------------
10   2200   02  
99   2100   01

A
7
B
4
C
5
D
9
GATE CS 2012    ER and Relational Models    
Discuss it


Question 6
Consider a relational table r with sufficient number of records, having attributes A1, A2,…, An and let 1 <= p <= n. Two queries Q1 and Q2 are given below.

GATE2011DBMS1

The database can be configured to do ordered indexing on Ap or hashing on Ap. Which of the following statements is TRUE?
A
Ordered indexing will always outperform hashing for both queries
B
Hashing will always outperform ordered indexing for both queries
C
 Hashing will outperform ordered indexing on Q1, but not on Q2
D
Hashing will outperform ordered indexing on Q2, but not on Q1.
GATE CS 2011    ER and Relational Models    
Discuss it


Question 6 Explanation: 
 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. See this for more details.
Question 7
Which of the following tuple relational calculus expression(s) is/are equivalent to 18

cs2008
A
I only 
B
II only 
C
III only 
D
III and IV only 
GATE CS 2008    ER and Relational Models    
Discuss it


Question 7 Explanation: 
 Some transformation rules for tuple relational calculus are :

 

 \cdot (\forall t)(P(t))=(not \hspace{0.2cm}\exists\hspace{0.2cm} t)(not \hspace{0.2cm}(P(t)))   \cdot (\exists t)(P(t))=not(\forall \hspace{0.2cm}t)(not \hspace{0.2cm}(P(t))) 


 

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


 

Please comment below if you find anything wrong in the above post.
Question 8
1) Let R and S be two relations with the following schema
R (P,Q,R1,R2,R3)
S (P,Q,S1,S2)
Where {P, Q} is the key for both schemas. Which of the following queries are equivalent?


A
Only I and II
B
Only I and III
C
Only I, II and III
D
Only I, III and IV
GATE CS 2008    ER and Relational Models    
Discuss it


Question 8 Explanation: 
 See Question 1 of http://www.geeksforgeeks.org/database-management-systems-set-7/
Question 9
Consider the following ER diagram. 


The minimum number of tables needed to represent M, N, P, R1, R2 is
A
2
B
3
C
4
D
5
GATE CS 2008    ER and Relational Models    
Discuss it


Question 9 Explanation: 
 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 )


Question 10
Consider the data given in above question. Which of the following is a correct attribute set for one of the tables for the correct answer to the above question?


A
{M1, M2, M3, P1}
B
{M1, P1, N1, N2}
C
 {M1, P1, N1}
D
 {M1, P1}
GATE CS 2008    ER and Relational Models    
Discuss it


Question 10 Explanation: 
 As given in the explanation http://quiz.geeksforgeeks.org/gate-gate-cs-2008-question-82/

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
                
    
  

    
    
   
  
    

  There are 75 questions to complete.  

    
  

  
          12345678        
  

  
    
 
  
    
  
   
   










    

        My Personal Notes
        arrow_drop_up