Chapter 3

Data Modeling Using the Entity-Relationship Modeling

Multiple-Choice Questions:
1) _______ is an object modeling methodology.
a) EML b) UML c) OML d) DML

2) _______ diagrams are important part of object modeling methodology.

a) application b) data c) class d) level

3) During the ______ design phase of database design, the properties of data is given importance,
rather than its storage details.
a) conceptual b) logical c) physical d) actual

4) ________ is the basic object of ER model which is a thing in real world.

a) relation b) domain c) attribute d) entity

5) Entities are described by properties called as _______.

a) attributes b) characteristics c) features d) relations

6) Attributes that are not divisible are called _________.

a) composite b) atomic c) complex d) structured

7) _______ attributes can have more than one value.

a) composite b) simple c) multi-valued d) single valued

8) When the value of an attribute A is obtained from the value of an attribute B, then the attribute
A is called _______.
a) composite b) stored c) derived d) retrieved

9) The entity types are represented in ER-diagrams by ______.

a) ovals b) rectangles c) double ovals d) diamonds

10) The multivalued attributes are represented in ER-diagrams by ______.

a) ovals b) rectangles c) double ovals d) diamonds

11) ________ attribute values are used to identify each entity uniquely.
a) complex b) unique c) characters d) key

12) An entity type without a key attribute is called _______ entity type.
a) null b) weak c) strong d) single

13) ________ specifies the set of values that can be assigned to the attribute.
a) block b) relation c) structure d) domain

14) Multivalued attributes are shown by _______.

a) ( ) b) { } c) < > d)

15) The relationships are displayed as _______ in ER-diagrams.

a) rectangles b) ovals c) triangles d) diamonds
16) The relationship in which an entity type participates more than once is a _____ relationship.
a) recursive b) iterative c) enumerated d) implied

17) _______ specifies the maximum number of relationship instances that an entity can participate.
a) range b) domain c) cardinality d) ceiling

18) In ER diagrams, the total participation is displayed as a _______.

a) oval b) single line c) double line d) arrow

19) A weak entity type normally has a ________ key.

a) partial b) total c) super d) strong

20) A weak entity type always has a ______ participation constraint with respect to its identifying
relationships.
a) partial b) total c) overlap d) disjoint

21) The partial key attribute is underlined with a ______ line.

a) single b) shaded c) dotted d) double

TRUE or FALSE:
1. The ER model is high-level conceptual model. (T/F)
2. The class diagrams in UML specify the operations on objects also. (T/F)
3. In database design the conceptual design phase comes after logical design phase. (T/F)
4. Composite attributes cannot be further subdivided. (T/F)
5. A multivalued attribute can have lower and upper bounds. (T/F)
6. An attribute value can be derived from another attribute. (T/F)
7. The names on entity types and entity sets are different. (T/F)
8. An entity cannot have more than one key attribute. (T/F)
9. A relationship type of degree two is called as ternary relationship. (T/F)
10. Relationship types can also have attributes. (T/F)
11. The attribute of a relationship type can be added to participating entity types. (T/F)
12. A weak entity type can have more than one identifying entity type. (T/F)
13. The number of levels of weak entity types cannot be more than one. (T/F)

Short Answer Questions:

1. What are the main phases of database design? Write them in order.
Ans. Requirement collection & analysis, conceptual design, logical design and physical design.

2. How does ER model describes data?

Ans. ER model describes data as entities, relationships, attributes

3. What are the different values a null value can have?

Ans. A Null value can mean an unknown, not applicable, exists but not available.
Match the following notations for ER diagrams:

Symbol Meaning

7
MULTIVALUED ATTRIBUTE
1.
8
DERIVED ATTRIBUTE
2.
4
IDENTIFYING RELATIONSHIP
3.
1
ENTITY
4.
5
ATTRIBUTE
5.
2
WEAK ENTITY
6.
3
RELATIONSHIP
7.
6
KEY ATTRIBUTE
8.
10

PARTIAL KEY

9.
9
COMPOSITE ATTRIBUTE
10.
Identify the cardinality ratios and participation constraints of the following
binary relationships:

Select the cardinality ratio:

r1 1:1
e1
d1 1:N
e2 r2 N:1
d2
e3 r3 M:N
d3
r4
e4 d4 Participation of EMPLOYEE:
r5
e5 d5 Total or Partial
e6 d6
r6 Participation of PROJECT:
Total or Partial
EMPLOYEE WORKS_ON PROJECT

Select the cardinality ratio:

e1 r1 1:1
d1
e2 r2
1:N
d2 N:1
e3 r3
d3 M:N
r4
e4 d4
r5 Participation of EMPLOYEE:
e5 d5 Total or Partial
r6
e6 d6
Participation of DEPARTMENT:
DEPARTMENT Total or Partial
EMPLOYEE MANAGES
Select the cardinality ratio:
e1 r1
1:1
d1 1:N
e2 r2
d2 N:1
e3 r3
d3
M:N
r4
e4 d4
Participation of EMPLOYEE:
r5
e5 d5 Total or Partial
r6
e6 d6
Participation of PROJECT:
Total or Partial
EMPLOYEE WORKS_ON PROJECT

Select the cardinality ratio:

e1 r1 1:1
d1
e2 r2
1:N
d2 N:1
e3 r3
d3 M:N
r4
e4 d4
r5 Participation of EMPLOYEE:
e5 d5 Total or Partial
r6
e6 d6
Participation of DEPARTMENT:
EMPLOYEE MANAGES DEPARTMENT Total or Partial
In the ER diagram for COMPANY database identify the following:

1. Names of (nonweak) entity types ________________________________________________

2. Names of weak entities _________________________________________________________
3. Key of DEPARTMENT _______________
4. Primary key of EMPLOYEE ____________________
5. Names of partial keys (if any) ____________________________________________________
6. Names of composite attributes ____________________________________________________
7. Names of multivalued attributes ___________________________________________________
8. Names of relationships ___________________________________________________________
9. Names of identifying relationships _________________________________________________
10. Names of derived attributes _______________________________________________________

Answer the following questions based on the ER diagram for COMPANY database:

1. All the EMPLOYEEs must work on some PROJECT. (T / F)

2. All the EMPLOYEEs should have a DEPENDENT. (T / F)
3. All the DEPARTMENTs should have a PROJECT. (T / F)
4. An EMPLOYEE need not be a manager of a DEPARTMENT. (T / F)
5. A DEPARTMENT need not be managed by an EMPLOYEE. (T / F)

Answer the following questions based on the ER diagram for COMPANY database:

1. One EMPLOYEE can have many DEPENDENTs. (T / F)

2. One EMPLOYEE can work on a single PROJECT. (T / F)
3. One DEPARTMENT can control many PROJECTs. (T / F)
4. One PROJECT can belong to only one DEPARTMENT. (T / F)
5. One EMPLOYEE can work for many DEPARTMENTs. (T / F)
6. One PROJECT can be done by only one EMPLOYEE. (T / F)
7. One EMPLOYEE can supervise only one EMPLOYEE. (T / F)
8. One DEPARTMENT is managed by only one EMPLOYEE. (T / F)
9. One DEPARTMENT can have many EMPLOYEEs. (T / F)
10. One EMPLOYEE can manage many DEPARTMENTs. (T / F)
Answer the following questions based on the ER diagram for COMPANY database:

1. The attribute NumberOFEmployees of DEPARTMENT entity is a _____________ attribute.

a) key b) derived c) composite d) multivalued
2. The attribute Name of EMPLOYEE entity is a ____________ attribute.
a) key b) derived c) composite d) multivalued
3. The attribute Locations of DEPARTMENT entity is a ____________ attribute.
a) key b) derived c) composite d) multivalued
4. The attribute Name of DEPENDENT entity is a __________ key.
a) primary b) candidate c) partial d) super
5. The attribute Name of PROJECT entity is a _________ key.
a) primary b) candidate c) partial d) super
6. The attribute Ssn of EMPLOYEE entity is a _________ key.
a) primary b) candidate c) partial d) super
7. The relationship DEPENDENTS_OF is a ___________ relationship.
a) identifying b) recursive c) strong d) weak
8. The relationship SUPERVISION is a ___________ relationship.
a) identifying b) recursive c) strong d) weak
9. The attribute Name of PROJECT entity is a ____________ attribute.
a) simple b) derived c) composite d) multivalued
10. The entity DEPENDENT is a _________ entity.
a) weak b) good c) strong d) super
In the ER diagram for AIRLINE database identify the following:

1. Names of (nonweak) entity types ________________________________________________

2. Names of weak entities _________________________________________________________
3. Key of AIRPORT _______________
4. Primary key of AIRPLANE ____________________
5. Names of partial keys (if any) ____________________________________________________
6. Names of composite attributes ____________________________________________________
7. Names of multivalued attributes ___________________________________________________
8. Names of relationships ___________________________________________________________
9. Names of identifying relationships _________________________________________________
10. Names of derived attributes _______________________________________________________

Answer the following questions based on the ER diagram for AIRPLANE database shown below:

1. The attribute NumberOFAirplanes of AIRPLANE entity is a _____________ attribute.

a) key b) derived c) composite d) multivalued
2. The attribute CustomerName of RESERVATION entity is a ____________ attribute.
a) key b) derived c) composite d) multivalued
3. The attribute Weekdays of FLIGHT entity is a ____________ attribute.
a) key b) derived c) composite d) multivalued
4. The attribute SeatNo of SEAT entity is a __________ key.
a) primary b) candidate c) partial d) super
5. The attributes Number and Airline of FLIGHT entity is a _________ key.
a) primary b) candidate c) partial d) super
6. The attribute AirplaneId of AIRPLANE entity is a _________ key.
a) primary b) candidate c) partial d) super
7. The relationship INSTANCE OF is a ___________ relationship.
a) identifying b) recursive c) strong d) weak
8. The entity FLIGHT LEG is a ___________ entity.
a) weak b) good c) strong d) super
9. The attribute State of AIRPORT entity is a ____________ attribute.
a) simple b) derived c) composite d) multivalued
10. The entity FLIGHT is a _________ entity.
a) weak b) good c) strong d) super
Answer the following questions based on the ER diagram for AIRLINE database:

1. One AIRPLANE TYPE can have only one AIRPLANE. (T / F)

2. One AIRPLANE TYPE can land in only one AIRPORT. (T / F)
3. One AIRPORT can allow landing of many AIRPLANE TYPEs. (T / F)
4. One FLIGHT can have many FAREs. (T / F)
5. One AIRPLANE can be assigned many LEG INSTANCEs. (T / F)
In the following ER diagram for BANK database identify the following:

1. Names of (nonweak) entity types ________________________________________________

2. Names of weak entities _________________________________________________________
3. Key of BANK _______________
4. Primary key of CUSTOMER ____________________
5. Names of partial keys (if any) ____________________________________________________
6. Names of composite attributes ____________________________________________________
7. Names of multivalued attributes ___________________________________________________
8. Names of relationships ___________________________________________________________
9. Names of identifying relationships _________________________________________________
10. Names of derived attributes _______________________________________________________

Answer the following questions based on the ER diagram for BANK database shown below:

1. The attribute NumberOFBranches of BANK-BRANCH entity is a _____________ attribute.

a) key b) derived c) composite d) multivalued
2. The attribute Name of CUSTOMER entity is a ____________ attribute.
a) key b) derived c) composite d) multivalued
3. The attribute Type of ACCOUNT entity is a ____________ attribute.
a) key b) derived c) composite d) multivalued
4. The attribute BranchNo of BANK-BRANCH entity is a __________ key.
a) primary b) candidate c) partial d) super
5. The attributes LoanNo and Type of LOAN entity is a _________ key.
a) primary b) candidate c) partial d) super
6. The attribute SSN of CUSTOMER entity is a _________ key.
a) primary b) candidate c) partial d) super
7. The relationship BRANCHES is a ___________ relationship.
a) identifying b) recursive c) strong d) weak
8. The entity BANK-BRANCH is a ___________ relationship.
a) weak b) good c) strong d) super
9. The attribute Code of BANK entity is a ____________ attribute.
a) simple b) derived c) composite d) multivalued
10. The entity CUSTOMER is a _________ entity.
a) weak b) good c) strong d) super
Answer the following questions based on the ER diagram for BANK database shown below:

1. One BANK can have many BRANCHES. (T / F)

2. One BANK-BRANCH can have only one ACCOUNT. (T / F)
3. One CUSTOMER can have many ACCOUNTs. (T / F)
4. One CUSTOMER can have only one LOAN. (T / F)
5. One ACCOUNT can belong to many CUSTOMERS. (T / F)
Composite and multivalued attributes can be nested to any number of values.
Suppose we want to design an attribute for a STUDENT entity type to keep track of previous
college education. The student might have attended more than one college previously.
Such an attribute will have:

One entry for each college previously attended having college name, start date and end
date, degree entries (can be more than one), and courses entries.

Each degree entry contains a degree name, month and year the degree was awarded.

Each course entry contains a course name, semester, year and grade.

Design an attribute to hold this information using the notations ( ) for composite attributes and
{ } for multivalued attributes as well as show how it can be represented in ER diagram.
Consider the following set of requirements for a university database that is used to keep track of
students transcripts.

The university keeps track of each students name, student number, National ID number,
current address and phone, permanent address and phone, birthdate, gender, class (I year, II
year), major department, minor department, and degree program (B.Sc., B.A.,,Ph.D.).
The name of the student should have first, middle, and last names. The permanent address
should have apartment number, street number, city, state, postal code. Both National ID
number and student number must be unique for each student.

Each department must be described by a name, department code, office number, office
phone, and college. Both name and code must be unique for each department.

Each course has a course name, description, course number, number of credit hours, level
number, and offering department. The course number should be unique for each
department.

Each section has an instructor name, semester, year, course number and section number.
The section number must be unique and each course can have multiple sections taught
during same semester/year.

A grade report has a student number, student name, section number, letter grade, and
numeric grade (percentage marks).

Design an ER schema for this application and draw an ER diagram for that schema. Specify key
attributes for each entity type, and structural constraints on each relationship type.