CH3 Data Modeling Using the Entity–Relationship (ER) Model Flashcards
Define entity
- An entity, which is a thing or object in the real world with an independent existence.
- Example: Andres Pinzon (Person), Apple (Company)
Define relationship instance
- 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
Define key attribute
- 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
What is a relationship type? Explain the differences among a relationship
instance, a relationship type, and a relationship set.
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
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.
- 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
Entity type ER convention
Rectangle
Composite attributes ER convention
Composite attributes are attached to their component attributes by straight lines.
Multivalued attributes ER convention
Multivalued attributes are displayed in double ovals. Figur
key attribute ER convention
each key attribute has its name underlined inside the oval, as
relationship types ER convention
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
total participation, partial participation ER convention
, 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
weak entity type and relationship ER convention
both a weak entity type and its identifying relationship are distin- guished by surrounding their boxes and diamonds with double lines
partial key ER convention
partial key attribute is underlined with a dashed or dotted line
Derived attribute ER convention
dotted oval
3.1 Discuss the role of a high-level data model in the database design process
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
3.2 List the various cases where use of a NULL value would be appropriate
- 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.
3.7. What is a participation role? When is it necessary to use role names in the description of relationship types?
- 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.
3.8. Describe the two alternatives for specifying structural constraints on rela- tionship types. What are the advantages and disadvantages of each?
- 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.
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?
- 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
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?
- attribute of one entity type refers to another entity type. Therefore, the value sets is the entity set
- functional data models.
