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
Answer:
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)
Answer:
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
Answer:
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
Answer:
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’
Answer:
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.
*************************
Related links:
Go back to DBMS Quizzes/MCQs page
Go to Normalization - Solved Exercises page
No comments:
Post a Comment