D426 Chapter 4

entity-relationship model

a high-level representation of data requirements, ignoring implementation details, which guides said implementation in a particular database system, such as MySQL

entity

a person, place, product, concept, or activity

relationship

a statement about two entities

attribute

a descriptive property of an entity

reflexive relationship

relates an entity to itself

entity-relationship diagram

a schematic picture of entities, relationships, and attributes

glossary/data dictionary/repository

documents additional detail for an ER diagram in text format, including names, synonyms, and descriptions of entities, relationships, and attributes

entity type

a set of things, ex: all employees in a company

relationship type

a set of related things. ex: employee-manages-department is a set of pairs

attribute type

a set of values. ex: all employee salaries

instance

an element of a set/type

Discovery

The first step of the analysis phase of database design, wherein documents are reviewed and interviews are conducted with database users and managers in order to determine entities, relationships, and attributes

cardinality

the maxima and minima of relationships and attributes in entity-relationship modeling

relationship maximum

the greatest number of instances of one entity that can relate to a single instance of another entity. Usually specified as one or many. Ex: a specific flight can depart from AT MOST one airport, but an airport may have hundreds of flights that can depart from it on any given day

singular

when only one instance of an entity can be related to another particular entity. ex: a flight only arrives at or departs from one airport

plural

when multiple instances of an entity can be related to another particular entity. ex: multiple flights can arrive at or depart from the same airport

relationship minimum

the least number of instances of one entity that can relate to a single instance of another entity, which is usually 0 or 1.

Ex: a flight may have 0 bookings on it, but a booking must relate to at least 1 flight.

attribute maximum

the greatest number of attribute values that can describe each entity instance

attribute minimum

the least number of attribute values that can describe each entity instance

unique attribute

an attribute that describes no more than one entity instance. Indicated with a 1 placed before the attribute maximum and minimum.

Ex: an employee number, airline codes, VIN, etc.

1. Determine relationship maxima and minima.

2. Determine attribute maxima and minima.

3. Identify unique attributes.

4. Document cardinality in glossary and, optionally, on ER diagram.

Steps to determine cardinality

identifying attribute

a unique, singular, and required (1-1(1)) attribute that corresponds one-to-one to entity instances

strong entity

has one of more identifying attributes, one of which may become the primary key if this is implemented as a table

weak entities

an entity without an identifying attribute, which is instead identified by its relationship to one or more other entities

identifying relationship

a relationship used in place of an identifying attribute for a weak entity

identifying entity

an entity with a 1(1) cardinality to a weak entity, which is represented with a diamond on this entity’s side of the relationship in an ER diagram

1. Identify strong and weak entities.

2. Determine the identifying relationship(s) for each weak entity.

3. Document weak entities and identifying relationships in glossary and ER diagram.

Steps to distinguish strong and weak entities

supertype entity

an overarching entity type whose attributes apply to all entity types contained within.

Ex: Manager has all of the attributes of Employee

subtype entity

a type of entity that is contained as a subset of another entity type and has attributes that other types within the overarching entity type do not.

Ex: In a library database, CD and Book each has attributes that the other does not, but both are part of the “media” type.

IsA Relationship

the identifying relationship between a supertype entity and its subtypes

partition

a group of mutually exclusive subtype entities within a supertype entity. Subtype entities of different partitions overlap and do share instances.

partition attribute

an optional attribute of a supertype that indicates which subtype entity in a partition is associated with each supertype instance.

1. Identify supertype and subtype entities

2. Replace similar entities and optional attributes with supertype and subtype entities.

3. Identify partitions and partition attributes.

4. Document supertypes, subtypes, and partitions in glossary and ER diagram.

Steps to create supertype and subtype entities.

subject area

a group of related entities that may be notated in some ER models

independent

alternative term for strong entities

dependent

alternative term for weak entities

IDEF1X - Information Definition Version 1X

a standardized ER modeling convention that became popular in part due to its early adoption by the US DOD

Chen notation

a non-standardized form of ER modeling notation that often appears in literature and tools

intangible entity

an entity documented in the data model but not tracked with data in the database

artificial key

a single-column primary key that a database designer creates when no suitable single-column or composite primary key exists within the tables natural attributes

functional dependence

when one column depends on another, meaning that the value in the non-dependent column relates to at most one value in the dependent column

redundancy

the repetition of related values in a table, which can cause database management problems as all copies must be changed when the related values are updated.

normal forms

rules for designing tables with less redundancy, which are numbered first through fifth

First normal form

• The table has a primary key

• Every non-key column depends on the primary key.

• The table has no duplicate rows.

• Every cell contains exactly one value.

Second Normal Form

all non-key columns depend on the whole primary key (i.e. if the key is composite, all non-key columns have a singular value for each combination of values from the composite key)

third normal form

when all non-key columns depend on the key, the whole key, and nothing but the key

Boyce-Codd normal form

when all dependencies within a table are on unique columns, which never creates a redundancy

candidate key

a simple or composite column that is unique and minimal

normalization

eliminates redundancy by decomposing a table into two or more tables in higher normal form.

1. List all candidate keys.

2. Identify dependencies on non-key columns.

3. Eliminate dependencies on non-key columns.

steps to normalize a table into Boyce-Codd normal form