CH3 Data Modeling Using the Entity–Relationship (ER) Model

Question 1

Define entity

Answer 1

• An entity, which is a thing or object in the real world with an independent existence.
• Example: Andres Pinzon (Person), Apple (Company)

Question 2

Define relationship instance

Answer 2

• Mathematically, the relationship set R is a set of relationship instances ri, where each ri associates n individual entities (e1, e2, . . . , en), and each entity ej in ri is a member of entity set Ej, 1 ≤ j ≤ n.
• Each relationship instance in the relationship set WORKS_FOR associates one EMPLOYEE entity and one DEPARTMENT entit

Question 3

Define key attribute

Answer 3

• Such an attribute is called a key attribute, and its values can be used to identify each entity uniquely.
• each key attribute has its name underlined inside the oval

Question 4

What is a relationship type? Explain the differences among a relationship
instance, a relationship type, and a relationship set.

Answer 4

A relationship type R among n entity types E1, E2, . . . , En defines a set of associa- tions—or a relationship set—among entities from these entity types. Similar to the case of entity types and entity sets, a relationship type and its corresponding rela- tionship set are customarily referred to by the same name, R. Mathematically, the relationship set R is a set of relationship instances ri, where each ri associates n individual entities (e1, e2, . . . , en), and each entity ej in ri is a member of entity set Ej, 1 ≤ j ≤ n. Hence, a relationship set is a mathematical relation on E1, E2, . . . , En; alternatively, it can be defined as a subset of the Cartesian product of the entity sets E1 × E2 × . . . × En. Each of the entity types E1, E2, . . . , En is said to participate in the relationship type

Question 5

When is the concept of a weak entity used in data modeling? Define the terms owner entity type, weak entity type, identifying relationship type, and partial key.

Answer 5

• Entity types that do not have key attributes of their own are called weak entity types.
• Entities belonging to a weak entity
  type are identified by being related to specific entities from another entity type in com-
  bination with one of their attribute values. We call this other entity type the identifying
  or owner entity type
• Relationship types that related the weak entity type to to its owner
• A weak entity type normally has a partial key,. Examples, the name of dependents of an employe
• both a weak entity type and its identifying relationship are distin- guished by surrounding their boxes and diamonds with double lines

Question 6

Entity type ER convention

Answer 6

Rectangle

Question 7

Composite attributes ER convention

Answer 7

Composite attributes are attached to their component attributes by straight lines.

Question 8

Multivalued attributes ER convention

Answer 8

Multivalued attributes are displayed in double ovals. Figur

Question 9

key attribute ER convention

Answer 9

each key attribute has its name underlined inside the oval, as

Question 10

relationship types ER convention

Answer 10

In ER diagrams, relationship types are displayed as diamond-shaped boxes, which are connected by straight lines to the rectangular boxes representing the participat- ing entity types. Th

Question 11

total participation, partial participation ER convention

Answer 11

, total participation (or existence dependency) is displayed as a double line connecting the participating entity type to the relationship, whereas partial par- ticipation is represented by a single line

Question 12

weak entity type and relationship ER convention

Answer 12

both a weak entity type and its identifying relationship are distin- guished by surrounding their boxes and diamonds with double lines

Question 13

partial key ER convention

Answer 13

partial key attribute is underlined with a dashed or dotted line

Question 14

Derived attribute ER convention

Answer 14

dotted oval

Question 15

3.1 Discuss the role of a high-level data model in the database design process

Answer 15

Main phases of database desing
1. REQUIREMENTS COLLECTION AND ANALYSIS (Output: data requirements, functional Requirements (data flow dia- grams, sequence diagrams, scenario))
2.1.1 CONCEPTUAL DESIGN (Output: Conceptual Schema
(In a high-level data model)/ ER or EER diagram) The conceptual schema is a concise description of the data requirements of the users and includes detailed descriptions of the entity types, relationships, and constraints;
2.1.2 LOGICAL DESIGN (DATA MODEL MAPPING (Output: Logical (Conceptual) Schema
(In the data model of a specific DBMS)) / SQL Data Model
2.1.3 PHYSICAL DESIGN (Internal schema)
2.2.1 (FUNCTIONAL ANALYSIS) (Output: High-Level Transaction Specification)
2.2.2 (Application program design)
2.2.3 TRANSACTION IMPLEMENTATION (Output: Application Programs)

• can be used to communicate with nontechnical users.
• con- centrate on specifying the properties of the data, without being concerned with storage and implementation

Question 16

3.2 List the various cases where use of a NULL value would be appropriate

Answer 16

• not applicable. Ex: Apartment_number of a single-family home would have NULL.
• unknown
  – known that the attribute value exists but is missing. Ex: Height attribute of a person is listed as NULL.
  – not known whether the attribute value exists—for example. Ex: Home_phone attribute of a person is NULL.

Question 17

3.7. What is a participation role? When is it necessary to use role names in the description of relationship types?

Answer 17

• Each entity type that participates in a relationship type plays a particular role in the relationship
• some cases the same entity type participates more than once in a relationship type in different roles. In such cases the role name becomes essential for distinguishing the meaning of the role that each participating entity plays.
• self-referencing relationships. SUPERVISION where both employee and supervisor entities are members of the same EMPLOYEE entity set.

Question 18

3.8. Describe the two alternatives for specifying structural constraints on rela- tionship types. What are the advantages and disadvantages of each?

Answer 18

• The first method distinguished two types of structural constraints:
  – Cardinality ratios (1:1, 1:N, M:N for binary relationships)
  – Participation constraints (total, partial). In total a relation needs to exist for an entity to exist.
• another method of specifying structural constraints is to specify minimum and maximum numbers (min, max) on the participation of each entity type in a relationship type.

Question 19

3.9. Under what conditions can an attribute of a binary relationship type be migrated to become an attribute of one of the participating entity types?

Answer 19

• attributes of 1:1 or 1:N relationship types can be migrated to one of the participating entity types
• 1:N relationship type, a relationship attribute can be migrated only to the entity type on the N-side
• M:N (many-to-many) relationship types, some attributes may be determined by the combination of participating entities in a relationship instance, not by any single entity. Ex: Employee WORKS_ON Project

Question 20

3.10. When we think of relationships as attributes, what are the value sets of these attributes? What class of data models is based on this concept?

Answer 20

• attribute of one entity type refers to another entity type. Therefore, the value sets is the entity set
• functional data models.