4) Database Design

entity-relationship model

a high-level representation of data requirements, ignoring implementation details

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 (ER) diagram

depict entities, relationships, and attributes

glossary or data dictionary or repository

documents names, synonyms, and descriptions of all entities, relationships, and attributes in a text format

entity type

a set of things

relationship type

a set of related things

attribute type

a set of values

entity instance

an individual thing

relationship instance

a statement about entity instances

attribute instance

an individual value

conceptual design

develops an entity-relationship model, capturing data requirements while ignoring implementation details

logical design

converts the entity-relationship model into tables, columns, and keys for a particular database system

physical design

adds indexes and specifies how tables are organized on storage media

binary relationship

the simplest kind of relationship between two entity types

one-to-many (1-M)

binary relationship that links one instance of an entity to multiple instances of another entity, where each child entity relates back to only one instance (ex. a state has many cities, a city is in only one state)

many-to-many (M-M)

binary relationship that links one record in Table A to many records in Table B, and vice versa (ex. a student takes many classes, a class has many students)

modality

the minimum number of entity occurrences that can be involved in a relationship

intersection data

located in an intersection table, which acts as a bridge, holding foreign keys from both related tables and additional attributes to that specific pairing (ex. a student’s enrollment date in a course)

associated entity

a construct that resolves an M-M relationship between two or more entities; transforming the complex M-M into two simpler 1-M relationships (ex. a student has many enrollments, a course has many enrollments)

unary relationships

associate occurrences of an entity type with other occurrences of the same entity type

one-to-one unary relationship

links instances of the same entity type where each instance relates to exactly one other instance of that same type

ternary relationship

a relationship that involves three different entity types

cardinality

refers to maxima and minima of relationship attributes

relationship maximum

the greatest number of instances of one entity that can relate to a single instance of another entity

singular

maximum is one

plural

maximum is many

crow’s foot notation

notation for the relationship maximum on an ER diagram

relationship minimum

the least number of instances of one entity that can relate to a single instance of another entity

optional

relationship minimum is zero

required

relationship minimum is one

attribute maximum

the greatest number of attribute values that can describe each instance

attribute minimum

the least number of attribute values that can describe each instance

unique attribute

describes at most one entity instance

identifying attribute

unique, singular, and required

identify

corresponds one-to-one to entity instances

strong entity

has one or more identifying attributes

weak entity

does not have an identifying attribute

subtype entity

a subset of another entity type, called the supertype entity

similar entities

entities that have may common attributes and relationships

partition

a group of mutually exclusive subtype entities

IsA relationship

the identifying relationship between a supertype entity and its subtype entities

subject area

a group of related entities from a complex model

independent

refers to strong entities

dependent

refers to weak entities

Unified Modeling Language (UML)

commonly used for software development in which software data structures are similar to database structures (uses ER conventions)

Information DEFinition version 1X (IDEF1X)

used by the US Department of Defense

Chen notation

notation that is not standardized but often appears in literature and tools

artificial key

a simple primary key created by the database designer

depends on

each B value is related to at most one A value

functional dependence

dependence of one column on another

multivalued dependence or join dependence

dependencies between three or more columns

redundancy

the repetition of related values in a table

normal forms

rules for designing tables with less redundancy

first normal form

every cell of a table contains exactly one value and the table has a primary key

second normal form

a table in which all non-key columns depends on the whole primary key

third normal form

a table in which all non-key columns depend on the key, the whole key, and nothing but the key

candidate key

a simple or composite column that is unique and minimal

non-key

a column not contained in a candidate key

Boyce-Codd normal form

a table in which when column A depends on column B, then column B is unique

normalization

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

denormalization

intentionally introducing redundancy by merging tables