**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:**

- m+n and 0
- mn and 0
- m+n and m-n
- mn and m+n

**Ans: **b

**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?**

- To avoid confusion with legitimate data values like 0 (zero) for integer columns and ’’ (the empty string) for string columns.
- To leave columns in a tuple marked as ’’unknown’’ when the actual value is unknown.
- To fill a column in a tuple when that column does not really ”exist” for that particular tuple.
- 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.

- I only
- I and II only
- II and III only
- 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

- (I)
- (I), (II), and (III)
- (IV)
- (I) and (IV)

**Ans:** c

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

- If X is deleted, then Y is also deleted
- If Y is deleted, then X is also deleted
- If Y is deleted, then X is not deleted
- 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
- 2
- 3
- 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**

- 2
- 3
- 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?**

- Min 1 and max 2
- Min 1 and max 3
- Min 2 and max 3
- 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?**

- An attribute of an entity can have multiple values
- An attribute of an entity can be composite
- An attribute can have more than one value in a row of a relational table.
- 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?**

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