Entity Relationship (E-R) Modeling

What does the entity relationship model represent?

Represents conceptual view.

What are the main components of the entity relationship model?

• Entities

• Attributes

• Relationships

What are entities?

• Corresponds to entire table, not row.

• Represented by rectangle.

What is a relational database?

Designer focuses on logical representation rather than physical. Allows for more effective design strategies.

What are the advantages of using tables?

• Structural and data independence.

• Related records stored in independent tables.

• Logical simplicity.

What are the elements of a relational database?

• Entity

• Attribute

• Relationship

What are tables?

• Holds related entities or entity set.

• Also called relations.

• Comprised of rows and columns.

What are common notations?

• Chen’s

• Crow’s Feet (used in class)

• Modeling Language (UML)

What are attributes?

• Characteristics of entities.

• Domain is set of possible values.

• Primary keys underlined.

What are the different types of attributes?

• Simple

• Composite

• Single-valued

• Multi-valued

• Derived

What are the different types of keys?

• Candidate key

• Primary key

• Foreign key

What are keys?

One or more attributes that determine other attributes such as: key attribute and composite key. It has full functional dependence.

What is entity integrity?

• Uniqueness

• No ‘null’ value in key.

What are the entity integrity rules?

• Ensures all entities are unique.

• Each entity has a unique key.

What rules apply to referential integrity?

• Foreign key must have null value or match primary key values.

• Makes it impossible to delete row whose primary key has mandatory matching foreign key values in another table.

What are ERD symbols?

A rectangle represents the entity type. Entities are named with a noun.

What does a verb represent?

The relationship between entities. Can be written above the line or if including relationship triangles, can be written in the triangle.

What is the “1” side of relationship?

• A |, 1 (UML), or no change in diagram (assumed to be 1).

• An O or 0 (UML) in diagram (optional).

What is the “Many” relationships?

Three prongs “Crow’s foot” or an * (UML).

What do entitiy relationships indicate?

Number of associated entities participating in the relationship.

What is unary?

• Single entity

• Recursive

• Exists between occurrences of same entity set.

What is binary?

Two entities associated.

What is ternary?

Three or more entities associated.

What is participation?

Determines whether an entity occurrence is mandatory or optional. Can be a zero (0) or one (1).

What is optional participation?

Entity occurrence does not require a corresponding occurrence in related entity.

What is mandatory participation?

• Entity occurrence requires corresponding occurrence in related entity.

• If no optionality symbol is shown on ERD, it is assumed mandatory.

What is cardinality?

The degree or maximum if possible entity occurrences that may relate to a single occurrence of an associated entity type. Can be one (1) or many (crow’s foot).

Relationships are often described by these maximum values:

• 1:1 (one to one relationship)

• 1:M (one to many relationship)

• M:N (many to many relationship)

What are associative entities?

• Used to ‘bridge’ between M:N relationships.

• Composite entities composed of primary keys of each entity needing connection.

What does a weak entity represent?

Represents a strong ownership between the “whole” and the “part.” A “part” belongs to only one “whole.”

What is a weak entity?

• Existence-dependent on another entity.

• Has primary key that is partially or totally derived from parent entity.

What are some of the challenges and conflicting goals of database design?

• Database must be designed to conform to design standards.

• High-speed processing may require design compromises.

• Quest for timely information may be the focus of database design.

• Other concerns include: Security, performance, shared access, and integrity.