BMGT402 - Entity Relationship

What does ER stand for?

Entity Relationship.

What does ERD stand for?

Entity Relationship Diagram.

What forms the basis of an ERD?

The Entity Relationship (ER) model.

What is an ERD?

A data modeling technique that graphically represents a database's main components.

What three main components does an ERD represent?

Entities, attributes, and relationships.

From whose viewpoint does an ERD conceptually represent a database?

The end user's viewpoint.

What is an entity?

An object of interest to the end user about which information should be collected.

What do entities become in relational databases?

Tables.

What is an entity occurrence or entity instance?

An individual entity instance corresponding to a row in a relational table.

How are entities represented in Chen, Crow's Foot, and UML notation?

A rectangle containing the entity's name.

How should entity names be written?

As singular nouns in all uppercase letters.

What is an attribute?

A property or characteristic of an entity.

Where are attributes written in an ERD?

Inside the entity box.

How is a primary key marked in the slide 8 entity box?

With a PK designator; it is required and shown in bold.

Scenario (Slide 8): The STUDENT entity lists STUDENT_ID, STUDENT_FNAME, STUDENT_LNAME, STUDENT_EMAIL, and STUDENT_PHONE. Which attribute is the primary key?

STUDENT_ID.

What are the two classifications of attributes based on whether values must be provided?

Required and optional.

What is a required attribute?

An attribute that must have a value.

What is an optional attribute?

An attribute that may be left empty.

What is an attribute domain?

The set of possible values for an attribute.

What is an identifier?

One or more attributes that uniquely identify each entity instance.

What is a composite identifier?

A primary key composed of more than one attribute.

What is a simple attribute?

An attribute that cannot be subdivided.

What examples of simple attributes are given on slide 10?

Gender, last name, and first name.

What is a composite attribute?

An attribute that can be subdivided into smaller components.

What examples of composite attributes are given on slide 10?

A person's name and address.

Scenario (Slide 10): An attribute stores a person's full name, which can be divided into first and last names. What type of attribute is it?

Composite attribute.

Scenario (Slide 10): An attribute stores only a person's last name. What type is it?

Simple attribute.

What is a single-value attribute?

An attribute that can have only one value.

What examples of single-value attributes appear on slide 11?

UID, SSN, and date of birth.

What is a multi-valued attribute?

An attribute that can have many values.

Are multi-valued attributes allowed in relational models?

No.

What examples of multi-valued attributes appear on slide 11?

Phone numbers and degrees earned.

How can a multi-valued attribute be resolved?

Create a new entity containing the multi-valued attribute's components and connect it with a foreign key.

Scenario (Slide 11): A car may come in many colors. How should the multi-valued color attribute be resolved?

Create a CAR_COLOR entity using a foreign key to associate colors with a car.

What is a derived attribute?

An attribute whose value can be calculated from other attributes.

Must a derived attribute be physically stored in the database?

No.

Scenario (Slide 12): A person's age is calculated from date of birth. What type of attribute is age?

Derived attribute.

What are advantages of storing derived attributes?

Saves CPU processing cycles and data access time, makes values readily available, and can preserve historical values.

What is a disadvantage of storing derived attributes?

They require maintenance to keep calculated values current.

What are advantages of not storing derived attributes?

Saves storage space and calculates a current value when needed.

What are disadvantages of not storing derived attributes?

Uses CPU cycles, increases data access time, and adds query complexity.

Scenario (Slide 12): An age value is stored in a table and becomes outdated after a birthday. What disadvantage does this demonstrate?

Stored derived attributes require maintenance to stay current.

Scenario (Slide 14): Each employee may support many dependents, and each dependent is supported by only one employee. What is the relationship between EMPLOYEE and DEPENDENT?

One-to-many (1:M), from EMPLOYEE to DEPENDENT.

Scenario (Slide 14): Employees must provide home and cell phone numbers. What phone attributes could the EMPLOYEE entity contain?

EMPLOYEE_HOME_PHONE and EMPLOYEE_CELL_PHONE; the slide does not prescribe exact names.

Scenario (Slide 14): An employee can have multiple dependents. Should all dependents be stored in one multi-valued EMPLOYEE field?

No. Create a separate DEPENDENT entity related to EMPLOYEE.

Scenario (Slide 14): Each dependent is supported by only one employee. Which entity should contain the employee foreign key?

DEPENDENT should reference EMPLOYEE's primary key.

Scenario (Slide 14): Each employee may support many dependents, but each dependent has one supporting employee. What minimum participation can be inferred for EMPLOYEE having dependents?

Optional (zero dependents are possible); the dependent must reference one employee.

What is a relationship?

An association between entities.

What are entities in a relationship called?

Participants.

In how many directions do relationships operate?

Both directions.

Why must both directions of a relationship be considered?

To classify its connectivity and cardinality accurately.

How are relationships represented and named on an ERD?

By labeled lines; relationship names are lowercase verbs.

What relationship verb does the professor recommend avoiding?

Has, and similarly meaningless verbs.

Scenario (Slide 16): A professor teaches many classes; each class is taught by a professor. What is the connectivity?

One-to-many (1:M), from PROFESSOR to CLASS.

Scenario (Slide 16): A professor teaches a class. What is a useful relationship label?

teaches.

What is connectivity?

The relationship classification: 1:1, 1:M, or M:N.

What is cardinality?

The minimum and maximum number of entity occurrences associated with one occurrence of a related entity.

What establishes connectivity and cardinality?

Business rules.

How is explicit cardinality written when Crow's Foot symbols are not enough?

As (minimum, maximum), or (x, y), beside the entities.

Which cardinality is represented by the inside Crow's Foot symbol?

Minimum cardinality.

Which cardinality is represented by the outside Crow's Foot symbol?

Maximum cardinality.

What is optional participation?

Participation with minimum cardinality 0; it may or may not occur.

What is mandatory participation?

Participation with minimum cardinality 1; it must occur.

What three quantities matter in Crow's Foot notation?

Zero, one, and many.

What does (0,N) mean?

Zero or many related occurrences; optional many.

What does (1,N) mean?

One or many related occurrences; mandatory many.

What does (1,1) mean?

Exactly one related occurrence; mandatory one.

What does (0,1) mean?

Zero or one related occurrence; optional one.

Scenario (Slide 19): A COURSE may generate zero or many CLASS occurrences. Is CLASS participation optional or mandatory relative to COURSE?

Optional, because its minimum cardinality is 0.

Scenario (Slide 19): A COURSE must generate at least one CLASS. What minimum cardinality applies to CLASS?

1, indicating mandatory participation.

Scenario (Slide 19): Each CLASS is generated by exactly one COURSE. What cardinality is on the COURSE side for one CLASS?

(1,1).

What is existence dependence?

An entity can exist in the database only when associated with another related entity occurrence.

What example of existence dependence is given?

DEPENDENT is existence-dependent on EMPLOYEE.

What is existence independence?

An entity can exist apart from one or more related entities.

What is another name for an existence-independent entity?

Strong or regular entity.

What is a weak or non-identifying relationship?

A relationship in which the child entity's primary key does not contain a component of the parent's primary key.

How is a weak (non-identifying) relationship drawn?

With a dashed line.

What is a strong or identifying relationship?

A relationship in which the child entity's primary key contains a component of the parent's primary key.

How is a strong (identifying) relationship drawn?

With a solid line.

Scenario (Slide 22): COURSE has primary key CRS_CODE; CLASS has its own primary key CLASS_CODE and stores CRS_CODE only as a foreign key. What relationship strength is shown?

Weak (non-identifying), represented by a dashed line.

Scenario (Slide 23): COURSE has primary key CRS_CODE; CLASS uses CRS_CODE together with CLASS_SECTION as its primary key. What relationship strength is shown?

Strong (identifying), represented by a solid line.

What two conditions define a weak entity?

It is existence-dependent, and its primary key is partially or totally derived from the parent entity.

Who determines whether an entity is weak?

The database designer, based on business rules.

Scenario (Slide 24): DEPENDENT cannot exist without EMPLOYEE, and DEPENDENT's primary key includes EMPLOYEE_NUM. Is DEPENDENT a weak entity?

Yes; it is existence-dependent and its key is derived partly from EMPLOYEE.

Scenario (Slide 24): EMPLOYEE supports DEPENDENT, whose primary key contains EMPLOYEE_NUM. Is the relationship identifying or non-identifying?

Identifying (strong).

Are a weak entity and a weak relationship the same thing?

No. A weak entity is existence-dependent with a parent-derived key; a weak relationship is non-identifying and uses a dashed line.

What is relationship degree?

The number of entities or participants associated with a relationship.

What is a unary relationship?

An association maintained within one entity.

What is another name for a unary relationship?

Recursive relationship.

What is a binary relationship?

An association between two entities.

What is a ternary relationship?

An association among three entities.

What are examples of higher-order relationship degrees?

Quaternary (four entities) and quinary (five entities).

Scenario (Slide 27): EMPLOYEE manages EMPLOYEE. What degree of relationship is this?

Unary (recursive).

Scenario (Slide 27): PROFESSOR teaches CLASS. What degree of relationship is this?

Binary.

Scenario (Slide 27): DOCTOR prescribes DRUG to PATIENT. What degree of relationship is this?

Ternary.

Scenario (Slide 28): An EMPLOYEE table has EMP_NUM and EMP_SPOUSE, where EMP_SPOUSE refers to another EMPLOYEE record. What relationship is implemented?

Unary or recursive relationship within EMPLOYEE.