Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Last Updated: Mar 27, 2024
Difficulty: Easy

ER and Relational models

Leveraging ChatGPT - GenAI as a Microsoft Data Expert
Speaker
Prerita Agarwal
Data Specialist @
23 Jul, 2024 @ 01:30 PM

Introduction

Designing a Database involves multiple phases. Entity-Relationship Model and Relational Model are two crucial phases of designing a database. Entity-Relationship Model provides a visual starting point for database design. And later, we convert those ER diagrams into Relational Models to create our tables.

Refer to this link to learn more about ER and Relational Models.

Now, we will see gate questions on ER and Relational Models in DBMS with their solutions and explanations.

Problems 

1. Consider the join of a relation R with a relation S. If K has m tuples and S has n tuples, then the maximum and minimum sizes of the join, respectively, are:

  1. m+n and 0
  2. mn and 0
  3. m+n and m-n
  4. mn and m+n

Ans: 

Explanation: If is no foreign key constraint between two tables, then the max and min number of tuples in their join is mn and 0, respectively.

2.  In a relational database model, NULL values can be used for all, but which one of the following?

  1. To avoid confusion with legitimate data values like 0 (zero) for integer columns and ’’ (the empty string) for string columns.
  2. To leave columns in a tuple marked as ’’unknown’’ when the actual value is unknown.
  3. To fill a column in a tuple when that column does not really ”exist” for that particular tuple.
  4. To allow duplicate tuples in the table by filling the primary key column(s) with NULL.

Ans: d

3. Consider the following two commands, C1 and C2, on the relation R from an SQL database:

C1: drop table R;

C2: delete from R;

Which of the following statements is TRUE?

I. Both C1 and C2 delete the schema for R.

II. C2 retains relation R but deletes all tuples in R.

III. C1 deletes all tuples of R and the schema for R.

  1. I only
  2. I and II only
  3. II and III only
  4. I, II, and III

Ans: c

4. Drop Table cannot be used to drop a Table referenced by __________ constraint.

(I)Primary key

(II)Subkey

(III)Super key

(IV)Foreign key

  1. (I)
  2.  (I), (II), and (III)
  3.  (IV)
  4.  (I) and (IV)

Ans: c

5. In the E-R model, Y is the dominant entity, and X is the subordinate entity

  1. If X is deleted, then Y is also deleted
  2. If Y is deleted, then X is also deleted
  3. If Y is deleted, then X is not deleted
  4. None of the above

Ans: b

Explanation: If any changes occur in the dominant entity, they are also reflected in the subordinate entity but not vice versa.

So, option (b) is correct.

6.  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. 1
  2. 2
  3. 3
  4. 4

Ans: c

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

7. Consider the following ER diagram.

The minimum number of tables required for representing M, N, P, R1, R2 is

  1. 2
  2. 3
  3. 4
  4. 5

Ans: b

Explanation: Answer is b. three minimum tables are required.

M and P are strong entities, so separate tables must represent them.

The many-to-one and one-to-many relationship that is totally on the many-side can be shown by adding an extra attribute to the “many” side, having the primary key of the “one” side. ( This way, no different table will be needed for Relationship sets )

M table is modified to include the primary key of the P side(i.e., P1). N is a weak entity and is modified to include the primary key of P (i.e., P1).

Therefore there would be a minimum of 3 tables with the schema given below :

M ( M1, M2, M3, P1)

P ( P1, P2 )

N ( P1, N1, N2 )

8. 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?

  1. Min 1 and max 2
  2. Min 1 and max 3
  3. Min 2 and max 3
  4. Min 2 and max 2

Ans: c

Explanation: Maximum number of tables required is 3 in case of many to many relationships between entities. The minimum number of tables is 1 in case of unary relationship and total participation of at least one entity. But in the case of partial participation of both entities, the minimum number of tables required is 2.

9. Given the basic ER and relational models, which of the following is INCORRECT?

  1. An attribute of an entity can have multiple values
  2. An attribute of an entity can be composite
  3. An attribute can have more than one value in a row of a relational table.
  4. An attribute can have exactly one value or a NULL value in a row of a relational table.

Ans: c

Explanation: The term ‘entity’ is related to ER model, and the term ‘relational table’ belongs to the relational model.

Both a and b are true. ER model supports multivalued and composite attributes. See this for more details.

(c) is false and (d) is true. In the Relational model, an entry in a relational table can have exactly one value or a NULL.

10.  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 one is the weakest normal form that the new database satisfies, but the old one does not?

  1. 1NF
  2. 2NF
  3. 3NF
  4. BCNF

Ans: b

Explanation: Old relation has functional dependency: Volume, Number -> Year as a partial dependency. So it does not follow 2NF.

But, there is no partial dependency in the New relation, so it satisfies 2NF and 3NF.

Therefore, 2NF is the weakest normal form that the new database satisfies, but the old one does not.

Option (b) is true.

Get the tech career you deserve, faster!
Connect with our expert counsellors to understand how to hack your way to success
User rating 4.7/5
1:1 doubt support
95% placement record
Akash Pal
Senior Software Engineer
326% Hike After Job Bootcamp
Himanshu Gusain
Programmer Analyst
32 LPA After Job Bootcamp
After Job
Bootcamp

Frequently Asked Questions

  1. What is the relation between ER model and the relational model?
    The main relationship between the ER model and the Relational Model is that the ER model explains the relationship between entities and their attributes. On the other hand, the Relational Model represents the implementation of our ER model.
  2. How do we identify the primary key in an ER model?
    The primary key in the ER Model is indicated by underlining the attribute. The designer selects a candidate key to identify tuples in a table uniquely. It can not be null. It is a key chosen by the database designer used to identify the whole entity set.
  3. What are the advantages of ER model?
    The main advantages of ER Model are that the data requirements are easily understandable using an E - R model as it utilizes clear diagrams. The E-R model can be converted into a relational database easily. The E-R diagram is straightforward to understand as it has clearly defined entities and their relations.
  4. What is the relational model in DBMS?
    The relational model in DBMS is the abstract model used to organize and manage the data stored in a database. It holds data in 2-D inter-related tables, also known as relations, in which each row refers to an entity, and each column represents the properties of the entity.
  5. What are the benefits and drawbacks of the relational database model?
    The main benefits of using relational models are that they can be easily queried, allow for the use of stored procedures to manipulate data, and provide a consistent database design. They also have limitations when it comes to high volume transactions or large amounts of data storage; the issue of speed can arise.

Conclusion

This article extensively discussed various GATE questions on ER and Relational Models in DBMS. This article discusses the solution and explanation of these GATE questions.

Refer to this link to learn more about ER and Relational Models.

Also check out - Strong Number

We hope that this blog has helped you enhance your knowledge regarding memory management in operating systems, and if you would like to learn more, check out our articles in the code studio library. Do upvote our blog to help other ninjas grow. Happy Coding!

Topics covered
1.
Introduction
2.
Problems 
3.
Frequently Asked Questions
4.
Conclusion