Data Management - Foundations - D426 (4/6)

Discover entities, relationships, and attributes.(Discovery)

1A. Identify entities, relationships, and attributes in interviews. \n\n1B. Draw ER diagram. \n\n1C. List standard attribute types in glossary. \n\n1D. Document names, synonyms, and descriptions in glossary.

Relationship maximum(Cardinality)

Relationship _______ is the greatest number of instances of one entity that can relate to a single instance of another entity.

unique attribute(Cardinality)

Each value of a ______ attribute describes at most one entity instance.

Determine cardinality(Cardinality)

2A. Determine relationship maxima and minima. \n\n2B. Determine attribute maxima and minima. \n\n2C. Identify unique attributes. \n\n2D. Document cardinality in glossary and, optionally, on ER diagram.

identify(Strong entities)

Identifying attribute values correspond one-to-one to, or ________, entity instances.

strong entity(Strong entities)

A ______ entity has one or more identifying attributes.

Supertype/subtype entities (Supertype & subtype entities)

A ___type entity is a subset of another entity type, called the _____type entity.

partition attribute(Supertype & subtype entities)

Each partitioncorresponds to an optional _________ _________ of the supertype entity.

Create supertype and subtype entities(Supertype & subtype entities)

4A. Identify supertype and subtype entities. \n\n4B. Replace similar entities and optional attributes with supertype and subtype entities. \n\n4C. Identify partitions and partition attributes. \n\n4D. Document supertypes, subtypes, and partitions in glossary and ER diagram.

entity-relationship model (Entities, relationships, and attributes)

An ______-_____________ model is a high-level representation of data requirements, ignoring implementation details.

entity (Entities, relationships, and attributes)

An ______ is a person, place, product, concept, or activity.

relationship (Entities, relationships, and attributes)

A _____________ is a statement about two entities.

attribute (Entities, relationships, and attributes)

An _________ is a descriptive property of an entity.

reflexive relationship(Entities, relationships, and attributes)

A _________ relationship relates an entity to itself.

entity-relationship diagram/ER diagram(Entities, relationships, and attributes)

______-____________ diagram is a schematic picture of entities, relationships, and attributes.

glossary/data dictionary/repository(Entities, relationships, and attributes)

________ , documents additional detail in text format.

entity type(Entities, relationships, and attributes)

An ______ type is a set of things. Ex: All employees in a company.

relationship type(Entities, relationships, and attributes)

A____________ 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 (Entities, relationships, and attributes)

An _________ type is a set of values. Ex: All employee salaries.

entity instance(Entities, relationships, and attributes)

An ______ instance is an individual thing. Ex: The employee Sam Snead.

relationship instance(Entities, relationships, and attributes)

A ____________ instance is a statement about entity instances. Ex: "Maria Rodriguez manages Sales.".

attribute instance(Entities, relationships, and attributes)

An _________ instance is an individual value. Ex: The salary $35,000.

Analysis(Entities, relationships, and attributes)

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

Logical design(Entities, relationships, and attributes)

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

Physical design(Entities, relationships, and attributes)

________ design adds indexes and specifies how tables are organized on storage media.

Analysis steps(Entities, relationships, and attributes)

1. Discover entities, relationships, and attributes \n\n2. Determine cardinality\n3. Distinguish strong and weak entities \n\n4. Create supertype and subtype entities

Logical design steps(Entities, relationships, and attributes)

5. Implement entities \n\n6. Implement relationships \n\n7. Implement attributes \n\n8. Apply normal form

cardinality (Cardinality)

In entity-relationship modeling, ___________ refers to maxima and minima of relationships and attributes.

singular / plural(Cardinality)

A related entity is ________ when the maximum is one and ______ when the maximum is many.

Relationship minimum(Cardinality)

Relationship _______ is the least number of instances of one entity that can relate to a single instance of another entity.

optional / required(Cardinality)

A related entity is ________ when the minimum is zero and ________ when the minimum is one.

Attribute maximum(Cardinality)

Attribute _______ is the greatest number of attribute values that can describe each entity instance. Attribute _______ is usually specified as one (singular) or many (plural).

Attribute minimum(Cardinality)

Attribute _______ is the least number of attribute values that can describe each entity instance. Attribute _______ is usually specified as zero (optional) or one (required).

identifying attribute(Strong entities)

An ___________ attribute is unique, singular, and required.

weak entity / identifying relationship / identifying entity (Weak entities)

A____ entity does not have an identifying attribute. Instead, a ____ entity usually has a relationship, called an identifying relationship, to another entity, called an identifying entity. Cardinality of the identifying entity is 1(1).

Distinguish strong and weak entities. (Strong and weak entities)

3A. Identify strong and weak entities. \n\n3B. Determine the identifying relationship(s) for each weak entity. \n\n3C. Document weak entities and identifying relationships in glossary and ER diagram.

IsA relationship(Supertype & subtype entities)

A supertype entity identifies its subtype entities. The identifying relationship is called an ___ relationship.

Similar entities(Supertype & subtype entities)

_______ entities are entities that have many common attributes and relationships.

partition(Supertype & subtype entities)

A _________ of a supertype entity is a group of mutually exclusive subtype entities.

crow's foot notation(Alternative modeling conventions)

Variations in cardinality conventions are common. One popular convention, called ____'s ____ 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 "____'s ____ notation".

subject area(Alternative modeling conventions)

Decompose a complex model into a group of related entities, called a _______ ____.

independent/dependent(Alternative modeling conventions)

Refer to strong entities as ___________ and weak entities as _________.

Unified Modeling Language/UML(Alternative modeling conventions)

_______ ________ ________ is commonly used for software development. Software data structures are similar to database structures, so UML includes ER conventions.

IDEF1X(Alternative modeling conventions)

______ became popular, in part, due to early adoption by the United States Department of Defense.

Chen notation(Alternative modeling conventions)

____ ________ appeared in an early ER modeling paper by Peter Chen. ____ ________ is not standardized but often appears in literature and tools.

Stable (Selecting primary keys)

Primary key values should not change. When a primary key value changes, statements that specify the old value must also change. Furthermore, the new primary key value must cascade to matching foreign keys.

Simple(Selecting primary keys)

Primary key values should be easy to type and store. Small values are easy to specify in an SQL WHERE clause and speed up query processing. Ex: A 2-byte integer is easier to type and faster to process than a 15-byte character string.

Meaningless(Selecting primary keys)

Primary keys should not contain descriptive information. Descriptive information occasionally changes, so primary keys containing descriptive information are unstable.

strong table (Implementing entities)

A strong entity becomes a _____ 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(Implementing entities)

An__________ key is a single-column primary key created by the database designer when no suitable single-column or composite primary key exists.

subtype table (Implementing entities)

A subtype entity becomes a _______ table .

weak table(Implementing entities)

A weak entity becomes a ____ table.

Implement entities(Implementing entities)

5A. Implement strong entities as tables. \n\n5B. Create an artificial key when no suitable primary key exists. \n\n5C. Implement subtype entities as tables. \n\n5D. Implement weak entities as tables. \n\n5E. Specify cascade and restrict actions for identifying relationships.

Implementing relationships (Implementing relationships)

6A. Implement many-one relationships as foreign key on 'many' side. \n\n6B. Implement one-one relationships as foreign key in table with fewer rows. \n\n6C. Implement many-many relationships as new weak tables. \n\n6D. Specify cascade and restrict rules on foreign keys that implement dependency relationships.

Implement attributes (Implement attributes)

7A. Implement plural attributes as new weak tables. \n\n7B. Specify cascade and restrict rules on new foreign keys in weak tables. \n\n7C. Specify column data types corresponding to attribute types. \n\n7D. Enforce relationship and attribute cardinality with UNIQUE and NOT NULL keywords.

depends on(Functional dependence)

Column A depends on column B means each B value is related to at most one A value.

functional dependence(Functional dependence)

Dependence of one column on another is called __________ dependence.

Multivalued dependence / join dependence (Functional dependence)

__________ dependence and ____ dependence entail dependencies between three or more columns.

Redundancy (Normal forms)

__________ is the repetition of related values in a table.

Normal forms (Normal forms)

______ forms are rules for designing tables with less redundancy.

first normal form (Normal forms)

Every cell of a table contains exactly one value. A table is in _____ normal form when, in addition, the table has a primary key. \n\n\n\nIn a _____ normal form table, every non-key column depends on the primary key \n\nA _____ normal form table has no duplicate rows

second normal form (Normal forms)

A table is in ______ normal form when all non-key columns depend on the whole primary key.

third normal form (Normal forms)

Informally, a table is in _____ 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 (Boyce-Codd normal form)

A_________ key is a simple or composite column that is unique and minimal.

Minimal (Boyce-Codd normal form)

_______ means all columns are necessary for uniqueness.

non-key (Boyce-Codd normal form)

A ___-___ column is a column that is not contained in a candidate key.

third normal form (Boyce-Codd normal form)

A table is in _____ 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 (Boyce-Codd normal form)

A table is in _____-____ normal form if, whenever column A depends on column B, then B is unique. Columns A and B may be simple or composite.

trivial (Boyce-Codd normal form)

When the columns of A are a subset of the columns of B, A always depends on B.These dependencies are called _______.

non-trivial (Boyce-Codd normal form)

A table is in Boyce-Codd normal form if, for all ___-_______ dependencies B → A, B is unique.

Normalization (Applying normal form)

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

depends on(Applying normal form)

Column A _______ 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 (Applying normal form)

In a _____-____ normal form table, if column A depends on column B, then B must be unique.

Denormalization(Applying normal form)

_______________ means intentionally introducing redundancy by merging tables.

Applying normal form(Applying normal form)

8A. Identify dependencies on non-unique columns. \n\n8B. Eliminate redundancy by decomposing tables.\n8C. Consider denormalizing tables in reporting databases.