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

Entity Relationship Model - Quiz Questions

Figure 1: for questions 1 to 3

1. What are the relation schemas that we get when we reduce the ERD in Figure 1?

(a) Teacher(ERP, Name, Qualification, Address, Phone); Subject(Sub_ID, Sub_Name, Syllabus, Medium); Teaches(ERP, Sub_ID)

(b) Teacher(ERP, Name, Qualification, Address, Phone, Sub_ID); Subject(Sub_ID, Sub_Name, Syllabus, Medium)

(d) Teacher(ERP, Name, Qualification, Address, Phone, Sub_ID); Subject(Sub_ID, Sub_Name, Syllabus, Medium, ERP)

Answer:

Option (c)

Relationship ‘Teaches’ is a one-to-many relationship from Teacher to Subject. As per the ERD reduction rule, the primary key of one side relation (Teacher) will be included as the foreign key in the many side relation (Subject).

2. Let us suppose that we have 1000 subject entities and 400 teacher entities. What would be the number of records in Subject relation after the reduction of ERD in figure 1 to relation schemas?

(a) 1000

(b) 400

(d) 600

Answer:

Option (a)

The number of records in either subject or teacher won’t change in one-to-many relationship due to reduction of ERD.

3. Let us suppose that we have 1000 subject entities and 400 teacher entities. What would be the number of records in Subject relation after the reduction of ERD in figure 1 to relation schemas and two more new teachers added to the teacher entity?

(a) 1000

(b) 1002

(d) 402

Answer:

Option (a)

Addition of records will change the count only in the added entity set for one-to-many relationship.

Figure 2: to answer questions 4 to 6

4. In ER diagram of figure 2, what are ‘Date’ and ‘Hours’ attributes called?

(a) Relational attributes

(b) Descriptive attributes

(d) Additional attributes

Answer:

Option (b)

Attributes attached to the relationship set is called descriptive attributes. They are usually added in many-to-many relationship if we need to define the relationship with more clarity.

5. Reduction of ERD in figure 2 will lead to following relation schemas.

(a) EMP(Emp_ID, EName, Department, Address, Street, City, Gender); PRJ(PName, Country, Client, Cost); Handles(Emp_ID, PName, Date, Hours)

(b) EMP(Emp_ID, EName, Department, Address, Gender); PRJ(PName, Country, Client, Cost); Handles(Emp_ID, PName)

(c) EMP(Emp_ID, EName, Department, Address, Gender, PName); PRJ(PName, Country, Client, Cost, Emp_ID); Handles(Date, Hours)

(d) EMP(Emp_ID, EName, Department, Street, City, Gender); PRJ(PName, Country, Client, Cost); Handles(Emp_ID, PName, Date, Hours)

Answer:

Option (d)

In ERD of figure 2, ‘address’ (of entity set EMP) is a composite attribute which will be including component attributes (street and city) only after reduction.

Also, ‘Handles’ is a many-to-many relationship with descriptive attributes. The conversion of this relationship will include the primary keys of participating entity sets as foreign keys along with descriptive attributes if any.

6. If we have 300 and 200 entities in EMP and PRJ entity sets respectively, what would be the maximum number of entities the Handle relationship set would have?

(a) 300

(b) 200

(d) 60000

Answer:

Option (d)

Many-to-many relationship will have all possible combinations of entities from participating entity sets. If m and n are the number of entities in participating entity sets, then the maximum number in relationship set will be m*n.

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

Related links: