4. Database Design Flashcards
entity-relationship model
An entity-relationship model is 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
descriptive property of an entity.
reflexive relationship
A reflexive relationship relates an entity to itself.
entity-relationship diagram / ER diagram
An entity-relationship diagram, commonly called an ER diagram, is a schematic picture of entities, relationships, and attributes.
glossary / data dictionary / repository
A glossary, also known as a data dictionary or repository, documents additional detail in text format.
entity type
An entity type is a set of things. Ex: All employees in a company.
relationship type
A relationship type is a set of related things. Ex: Employee-Manages-Department is a set of (employee, department) pairs, where the employee manages the department.
attribute type
An attribute type is a set of values. Ex: All employee salaries.
entity instance
An entity instance is an individual thing. Ex: The employee Sam Snead.
relationship instance
A relationship instance is a statement about entity instances. Ex: ‘Maria Rodriguez manages Sales.’
attribute instance
An attribute instance is an individual value. Ex: The salary $35,000.
Analysis
Analysis develops an entity-relationship model, capturing data requirements while ignoring implementation details.
Logical design
Logical design converts the entity-relationship model into tables, columns, and keys for a particular database system.
Physical design
Physical design adds indexes and specifies how tables are organized on storage media.
cardinality
In entity-relationship modeling, cardinality refers to maxima and minima of relationships and attributes.
Relationship maximum
Relationship maximum is the greatest number of instances of one entity that can relate to a single instance of another entity.
singular / plural
A related entity is singular when the maximum is one and plural when the maximum is many.
Relationship minimum
Relationship minimum is the least number of instances of one entity that can relate to a single instance of another entity.
optional / required
A related entity is optional when the minimum is zero and required when the minimum is one.
Attribute maximum
Attribute maximum is the greatest number of attribute values that can describe each entity instance. Attribute maximum is usually specified as one (singular) or many (plural).
Attribute minimum
Attribute minimum is the least number of attribute values that can describe each entity instance. Attribute minimum is usually specified as zero (optional) or one (required).
unique attribute
Each value of a unique attribute describes at most one entity instance.
identifying attribute
An identifying attribute is unique, singular, and required.
identify
Identifying attribute values correspond one-to-one to, or identify, entity instances.
strong entity
A strong entity has one or more identifying attributes.
weak entity / identifying relationship / identifying entity
A weak entity does not have an identifying attribute. Instead, a weak entity usually has a relationship, called an identifying relationship, to another entity, called an identifying entity. Cardinality of the identifying entity is 1(1).
subtype entity / supertype entity
A subtype entity is a subset of another entity type, called the supertype entity.
IsA relationship
A supertype entity identifies its subtype entities. The identifying relationship is called an IsA relationship.
Similar entities
Similar entities are entities that have many common attributes and relationships.
partition
A partition of a supertype entity is a group of mutually exclusive subtype entities.
partition attribute
Each partition corresponds to an optional partition attribute of the supertype entity.
crow’s foot notation
Variations in cardinality conventions are common. One popular convention, called crow’s foot notation, depicts cardinality as a circle (zero), a short line (one), or three short lines (many). The three short lines look like a bird’s foot, hence the name ‘crow’s foot notation’.
subject area
Decompose a complex model into a group of related entities, called a subject area.
independent / dependent
Refer to strong entities as independent and weak entities as dependent.
Unified Modeling Language / UML
Unified Modeling Language, or UML, is commonly used for software development. Software data structures are similar to database structures, so UML includes ER conventions.
IDEF1X
IDEF1X stands for Information DEFinition version 1X. IDEF1X became popular, in part, due to early adoption by the United States Department of Defense.
Chen notation
Chen notation appeared in an early ER modeling paper by Peter Chen. Chen notation is not standardized but often appears in literature and tools.
strong table
A strong entity becomes a strong table. The primary key must be unique and non-NULL, and should be stable, simple, and meaningless. Single-column primary keys are best, but if no such column exists, a composite primary key may have the required properties.
artificial key
An artificial key is a single-column primary key created by the database designer when no suitable single-column or composite primary key exists.
subtype table
A subtype entity becomes a subtype table.
weak table
A weak entity becomes a weak table.
depends on
Column A depends on column B means each B value is related to at most one A value.
functional dependence
Dependence of one column on another is called functional dependence.
Multivalued dependence / join dependence
Multivalued dependence and join dependence entail dependencies between three or more columns.
Redundancy
Redundancy is the repetition of related values in a table.
Normal forms
Normal forms are rules for designing tables with less redundancy.
first normal form
Every cell of a table contains exactly one value. A table is in first normal form when, in addition, the table has a primary key.
second normal form
A table is in second normal form when all non-key columns depend on the whole primary key.
third normal form
Informally, a table is in third normal form when all non-key columns depend on the key, the whole key, and nothing but the key. A formal definition appears elsewhere in this material.
candidate key / Minimal
A candidate key is a simple or composite column that is unique and minimal. Minimal means all columns are necessary for uniqueness.
non-key
A non-key column is a column that is not contained in a candidate key.
third normal form
A table is in third normal form if, whenever a non-key column A depends on column B, then B is unique. Columns A and B may be simple or composite.
Boyce-Codd normal form
A table is in Boyce-Codd normal form if, whenever column A depends on column B, then B is unique. Columns A and B may be simple or composite.
Normalization
Normalization eliminates redundancy by decomposing a table into two or more tables in higher normal form.
depends on
Column A depends on column B when each B value is related to at most one A value. A and B may be simple or composite columns.
Boyce-Codd normal form
In a Boyce-Codd normal form table, if column A depends on column B, then B must be unique.
Denormalization
Denormalization means intentionally introducing redundancy by merging tables.
