Entity-Relationship (ER Modeling)

Question 1

Database Design Process

Answer 1

Requirements Collection & Analysis
Functional & Data Requirements
Conceptual Design
Conceptual Schema
Logical Design
Physical Design

Question 2

Database designers interview prospective database users to understand and document the data requirements

Answer 2

Requirements Collection &
Analysis

Question 3

Output of the previous step but we will focus on Data Requirements part of the process

Answer 3

Functional & Data Requirements

Question 4

Enables database designers to concentrate on specifying the properties of the data without being too concerned with storage and implementation details

Answer 4

Conceptual Design

Question 5

Output of Conceptual Design

Concise description of the data requirements and includes detailed descriptions of the entity types, relationships, and constraints

Answer 5

Conceptual Schema

Question 6

Conceptual data model transformed into representational (implementation) data model

Answer 6

Logical Design

Question 7

Internal storage structures, file organizations, indexes, access paths, and other physical design parameters are specified

Answer 7

Physical Design

Question 8

Popular high-level conceptual data model

Describes data as entities, relationships, and attributes

Answer 8

Entity-Relationship (ER) Model

Question 9

Thing or object in the real world with an independent existence

Answer 9

Entity

Question 10

May be physical existence (e.g. car, employee, student)

May be conceptual existence (e.g. job, course)

Has Attributes

Answer 10

Entity

Question 11

A particular property that describes an entity

Answer 11

Attribute

Question 12

Entity vs Entity Type vs Entity Set

Answer 12

Entity - Thing or object in real world

Entity Type - Defines a collection or set of entities that have the same attributes. Describes the schema for a set of entities that share the same structure

Entity Set - Collection of all entities of a particular entity type in the database at any point in time Also called entity collection

Question 13

Has 1 or more key attributes (Represented as a regular entity using a rectangle)

Answer 13

Strong Entity Type

Question 14

Has no key attributes, normally has a partial key and is represented using double rectangles

Answer 14

Weak Entity Type

Question 15

Types of Attributes

Answer 15

Atomic vs Composite
Single-Valued vs Multivalued
Derived vs Stored
Complex
Key
Partial Key
Relationship Attributes

Question 16

Can be divided into smaller subparts which represent more basic attributes with independent meanings

ex: Name broken down to Fname, Lname, MI

Answer 16

Composite Attributes

Question 17

Not divisible

Answer 17

Atomic or Simple Attributes

ex: Age

Question 18

Has single value for a particular
entity

Answer 18

Single-valued

Question 19

Can have a set of values for the same entity

May have lower and upper bounds to constrain the number of values allowed for each individual entity

Represented by double ovals

Answer 19

Multivalued Attribute

Question 20

Value determined using other attributes or properties (e.g. count)

Represented by dashed oval

Answer 20

Derived Attribute

Question 21

Opposite of derived attribute

Answer 21

stored attribute

Question 22

Nesting of composite and/or multivalued attributes

Answer 22

Complex Attributes

Question 23

Attribute whose values are distinct for each individual entity in the entity set

Attribute name underlined with a solid line

Answer 23

Key Attribute (No primary key concept yet in ER modeling)

Question 24

TRUE OR FALSE:

Several attributes can (together) form a key; Use composite attribute and designate it as key

Answer 24

TRUE

Question 25

Attribute that can uniquely identify weak entities that are related to the same owner entity Attribute name underlined with a broken line

Answer 25

Partial Key (discriminator)

Question 26

Defines a set of associations (relationship set) among entities from the involved entity types

Answer 26

Relationship Type

Question 27

In ER diagrams, ___________________ are displayed as diamond-shaped boxes

Answer 27

relationship types

Question 28

Number of participating entity types

Answer 28

Relationship Degree (2 = binary, 3 = ternary)

Question 29

Relationship Constraints

Answer 29

Cardinality Ratio (1:1, 1:N, M:N) Participation (Total and Partial)

Question 30

Specifies the maximum number of relationship instances that an entity can participate in. Can be 1:1 (one-to-one), 1:N (one-to-many), N:1 (many-to-one), and M:N (many-to-many)

Answer 30

Cardinality Ratio

Question 31

Specifies whether the existence of an entity depends on its being related to another entity via the relationship type Specifies the minimum number of relationship instances that each entity can participate in

Answer 31

Participation (minimum cardinality constraint)

Question 32

Attributes recorded when the relationship is initiated between participating entities

Answer 32

Relationship Attributes

Question 33

Migration Rules for Relationship Attributes

Answer 33

M:N - attach to relation 1:N - migrate to N side 1:1 - check for participation if Total: Total - either side if Partial: Partial - attach to relation if Partial:Total - migrate to Total Side

Question 34

TRUE OR FALSE: Relationship types involving a weak entity type will also be weak (represented by single diamond)

Answer 34

FALSE. Represented by Double Diamonds

Question 35

TRUE or FALSE Participation constraint of the weak entity type will always be Total

Answer 35

TRUE

Question 36

Min-max Notation

Answer 36

Attaches the min and max number of participation in a relationship. min valued 1 = total participation min at 0 = partial participation (can be confusing as positioning of **cardinality** is flipped compared to ER Model)

Question 37

TRUE or FALSE An attribute that exists in several entity types may be elevated or promoted to an independent entity type

Answer 37

True

Question 38

TRUE or FALSE An entity type with a single attribute and related to only one other entity may be reduced or demoted to an attribute

Answer 38

True