ER Models

Database schema before class: database_day9_end_of_class.sql
Slides from class

Database notes

Entity

A person, place, thing, concept, or event for which data can be stored.

Attribute

A characteristics of an entity. An attribute has a name and a data type.

Composite attribute

An attribute that can be further divided to yield additional attributes. For example, Name can be divided into First Name and Last Name.

Derived attribute

An attribute that does not physically exist within the entity and is derived via an algorithm. For example, the Age attribute of a Person might be derived by subtracting the Person's birth date from the current date.

Required attribute

An attribute that must have a value and cannot be left empty (set to NULL).

Optional attribute

In ER modeling, an attribute that does not require a value; therefore it can be left empty (set to NULL).

Single-valued attribute

An attribute that has only a single value (e.g., OfficeNumber — each person is assigned one office).

Multivalued attribute

Attributes that may have many values (e.g., PhoneNumber — one person may have many phone numbers, cell, office, home, etc).

Relationship

An association between entities.

One-to-Many relationship (1:M)

Associations among two or more entities that are used by data models. In a 1:M relationship, one entity instance is associated with many instances of the related entity. Example: one restaurant can be inspected many times, but one inspection is only for one restaurant.

One-to-One relationship (1:1)

Associations among two or more entities that are used by data models. In a 1:1 relationship, one entity instance is associated with only one instance of the related entity. Example: one Department is chaired by one Professor, and one Professor chairs only one Department.

Many-to-Many relationship (M:N)

Associations among two or more entities that are used by data models. In a M:N relationship, one entity instance is associated with many instances of a related entity and one instance of a related entity is associated with many instances of the first entity. Example: one inspection can result in many violation codes, and one violation code can be issued in many inspections. Use a joining table to express this type of relationship in SQL.

Joining (or bridging or linking) table

A table that links two other tables in a many-to-many relationship.

Business rules

A description of a policy, procedure, or principle within an organization. For example, a pilot cannot be on duty for more than 10 hours during a 24-hour period.

Entity Relationship Model (ERM)

A data model that describes relationships (1:1, 1:M, M:N) among entities at the conceptual level with the help of Entity Relationships Diagrams (ERDs).

Extended Entity Relationship Model (EERM)

Sometimes referred to as an enhanced entity relationship model. The result of adding more semantic constructs such as entity supertypes, entity subtypes, and entity clustering to the original entity relationship model (ERM).

Entity Relationship Diagram (ERD)

A diagram that depicts an entity relationship model's (ERM) entities, attributes, and relationships. There are several popular methods for depicting the relationships including: Crow's foot, Chen, or UML.

Crow's Foot Diagram

A representation of the entity relationship diagram that users a three-pronged symbol to represent the "many" side of a relationship.

External model

The application programmer's view of the data environment. Given its business focus, an external model works with a data subset of the global database schema.

Conceptual model

The output of the conceptual design process. The conceptual model provides a global view of an entire database and describes the main data objects, avoiding details.

Internal model

A level of data abstraction that adapts the conceptual model to a specific DBMS model for implementation. The internal model is the representation of a database as "seen" by the DBMS. In other words, the internal model requires a designer to match the conceptual model's characteristics and constraints to those of the selected implementation.

Reverse engineer

MySQL Workbench examines a database schema and develops an ERD for that schema.

Forward engineer

MySQL Workbench creates a database schema from an ERD.