Please visit, subscribe and share 10 Minutes Lectures in Computer Science
Showing posts with label ER Model. Show all posts
Showing posts with label ER Model. Show all posts

# ER model solved quiz, Entity relationship model into conceptual schema solved quiz, ERD solved exercises

## Entity Relationship Model - Quiz Questions

Use this ER diagram to answer the questions below;

1. While reducing the above ER diagram into schema, we will get only two conceptual schemas namely, Pilot and Plane.

(a) TRUE                                 (b) FALSE

Option (b)

We will get three. The relationship will also be converted into separate schema as it is a many-to-many relationship.

2. Which of the following is correct about reduction of the given ER diagram into schema?

(a) Pilot(PID, PName, Hours_Flying, Address, Phone), Plane(Plane_ID, Operator, Source, Destination, Model), flies(PID, Plane_ID)

(b) Pilot(PID, PName, Hours_Flying, Address, Phone), Plane(Plane_ID, Operator, Source, Destination, Model), flies(PID, Plane_ID)

(c) Pilot(PID, PName, Hours_Flying, Address, Phone), Plane(Plane_ID, Operator, Source, Destination, Model, PID)

(d) Pilot(PID, PName, Hours_Flying, Address, Phone, Plane_ID), Plane(Plane_ID, Operator, Source, Destination, Model)

Option (b).

Though option (a) looks very similar to option (b), the keys are missing in (a).

3. Which of the following statement(s) is/are true about the given ER diagram?

(a) A pilot flies zero or more planes

(b) Each pilot flies only one plane

(c) A plane is operated by only one pilot

(d) A plane is operated by zero or more pilots

Options (a) and (d)

‘flies’ is a many-to-many relationship. As per the ERD, a pilot entity is related to zero or more plane entities. And, a plane entity is related to zero or more pilot entities. Hence, both options (a) and (d) are correct.

4. Inclusion of PID in the schema of Plane or Plane_ID in the schema of Pilot results in __________

(a) No redundancy

(b) Minimal redundancy

(c) Data redundancy

(d) None of the above

Option (c)

If we include PID in the schema of plane, then for each plane operated by the particular pilot, the pilot id will be repeated. Also, if the plane is operated by more than one pilot, the Plane_ID, Operator, Source, Destination, and Model attribute values are repeated.

Example:

### Why don’t we include the primary key of participating entity sets into one another in case if the relationship is many-to-many?

Pilot:

 PID PName Hours_Flying Address Phone P101 Kumar 1300 Chennai 0078675645 P102 Martin 200 Vellore 9011110000

Plane:

 Plane_ID Operator Source Destination Model PL_1 Jet Chennai New Delhi X123 PL_3 Air India Bengaluru Chennai AS10

If suppose, pilots P101 and P102 have operated plane PL_1 during some flights, then inclusion of PID into Plane will result in redundancy as follows;

Plane with PID as foreign key:

 Plane_ID Operator Source Destination Model PID PL_1 Jet Chennai New Delhi X123 P101 PL_1 Jet Chennai New Delhi X123 P102 PL_3 Air India Bengaluru Chennai AS10 P102

5. Which of among the following are true if we create a separate table for the relationship ‘flies’?

(a) PID and Plane_ID are foreign keys referencing the relations Pilot and Plane respectively

(b) PID and Plane_ID together forms the key for ‘flies’

(c) PID is the only key of ‘flies’

(d) Plane_ID is the only key of ‘flies’

Options (a) and (b)

While we create separate table for many-to-many relationship, we include the keys of participating entity sets into the new table as foreign keys and also, all the attributes (except descriptive attribute, if any) will together form the key for the new table.

*************************

### Go back to DBMS Quizzes/MCQs page

Go to Normalization - Solved Exercises page

## Reduction of ERD to Relational Schema - Solved Exercise

Question:
Reduce the following ER diagram to relational database schema

Solution:
Notations used in the ERD for relationships
Step 1: First let us reduce the strong entity sets into schema. We have the strong entity sets LOT, RAW_MATERIALS and PRODUCTION_UNITS. They can be converted into schemas as follows;

 Rules to convert: Strong Entity Set Name of the schema = Name of the strong entity set           Attributes of the schema = attributes of the strong entity set           Underline the primary key attribute in the resultant schema

• Lot (LotNumber, CreateDate, Cos-of-Materials)
• Raw_Materials (material-ID, UnitCost, type)
• Production_Units (serial#, exactWeight, productType, productDesc, qualityTest)

Step 2: To decide on whether relationships to be converted into separate tables or not. We have two relationship sets in our problem.
(1) Includes is a one-to-many relationship from entity set Lot to Production_Units.
 Rules to convert: One-to-Many relationship set Insert the primary key attribute of one side entity set as a foreign key in the many side entity set.

LotNumber attribute to be inserted into Prodution_Units schema as a foreign key. So, the schema Production_Units is updated as follows;
• Production_Units (serial#, exactWeight, productType, productDesc, qualityTest, LotNumber)

(2) Created_From is a many-to-many relationship set between Lot and Raw_Materials.
 Rules to convert: Many-to-Many relationship set Create a separate table for many-to-many relationship set with primary keys of participating entity sets as attributes. All the primary key attributes of participating entity sets will form a composite key in the resultant relation.
Hence, create a separate schema for Created_From as follows;
• Created_From(LotNumber, material-ID)

Step 3: To reduce Descriptive attributes into schema.
 Rules to convert: Descriptive attribute Descriptive attribute is an attribute attached to the relationship directly. While reducing the relationship into schema, insert the descriptive attributes with that schema.
Hence, Created_From is updated as follows;
• Created_From(LotNumber, material-ID, Units)

Resultant schema:
After reduction, the schema looks like the following; [primary keys are underlined, foreign keys are connected with the concerned primary keys with headed arrows]

***********

Go to Normalization - Solved Exercises page

# How to convert entity relationship diagram into relational schema?

## Featured Content

### Multiple choice questions in Natural Language Processing Home

MCQ in Natural Language Processing, Quiz questions with answers in NLP, Top interview questions in NLP with answers Multiple Choice Que...

data recovery