Chapter 5, Chapter 6/ Concepts of DataBase Management/ 8th Edition/ Phil Pratt & Mary Last

Step 1 of information-level design

.1. Represent the user view as a collection of tables *STEP 1: Determine the entities involved and create a separate table for each type of entry *STEP 2: Determine the primary key for each table *STEP 3: Determine the attributes/properties for each entity *STEP 4: Determine relationships between the entities ONE-TO-MANY/ MANY-TO-MANY/ ONE-TO-ONE

____ contains a repeating group.

1NF

Step 2 of information-level design

2.) Normalize these tables (3rd number form (3NF) the Primary Key)

____ normal form has an additional condition that the only determinants the table contains are candidate keys.

3NF

If B (an attribute) is functionally dependent on A, we can also say that ____.

A functionally determines B

Candidate key:

A minimal collection of columns (attributes) in a table on which all columns are functionally dependent but that has not necessarily been chosen as the primary key

The fact that column B is functionally dependent on column A can be written as ____.

A--->B

Alternate keys, secondary keys, and foreign keys are identified with the letters

AK, SK, and FK appear in the parentheses following the alternate key, secondary key, and foreign key, (appear below the line)

A candidate key that was not chosen to be the primary key.

Alternate Key

OPTIONAL role in the relationship.

An entity in a relationship with a *MINIMUM cardinality of ZERO* plays an

MANDATORY role in the relationship.

An entity with a *MINIMUM cardinality of ONE* plays a

determinant

Any column (or collection of columns) that determines another column

A column created for an entity to serve solely as the primary key and that is visible to users.

Artificial key

A design method in which specific user requirements are synthesized into a design. Design starts at a low level. Specific user requirements drive design process.

Bottom-up Design Method

The number of items that must be included in a relationship.

Cardinality

An IDEF1X name for an entity sub-type.

Category

Functional Dependence

Column B is functionally dependent on column A Each value for A is associated with exactly one value of B A → B A functionally determines B

An IDEF1X. A collection of subtypes with the property that every element of the supertype is an element of at least one subtype.

Complete Category

An entity in the entity-relationship model used to *implement a many-to-many-relationship*

Composite Entity

independence (when there is independence among the individual relationships, separate many to many relationships are appropriate)

Crucial Issue

Crucial Issue

Crucial issue in making the determination between a single many-to-many relationship and two (or three) many-to-many relationships is the independence. When all three entities are critical in the relationship, the three-way relationship is appropriate. When there is independence among the to-many-to-many relationship is created when it is not appropriate to do so, the conversion to fourth normal form will correct the problem.

Supports all the user views encountered during a design process.

Cumulative Design

A relational-like language that is used to represent the result of the database design process.

DBDL (Database Design Language)

An entity that *requires a relationship* to another entity for identification .

Dependent Entity

Entity "A" is a subtype of entity "B" *IF EVERY* occurrence of entity "A" is *ALSO* an occurrence of entity "B." Table that is a subtype of another table.

Entity Subtype

An approach to representing data in a database that uses ER diagrams exclusively as the tool for representing entities, attributes and relationships. Visually represents database structure.

Entity-Relationship Model (ER Model)

A relationship in which the *existence of one entity depends on the existence of another related entity*.

Existence Dependency

A table that is in first normal form is better than one that is in second normal form.

False

Functional dependencies can be determined by looking at sample data.

False

Tables that are in second normal form do not update anomalies.

False

The possibility of data dependence is one of the four categories of update anomalies.

False

Table does not contain multiple entries in a field for a single record.

First Normal Form (1NF)

A type of ER diagram; or technically a language of the IDEF (Integrated Definition) family of languages that is used for data modeling.

IDEF1X

Step 3 of information-level design

Identify all keys in these tables (primary, alternate, secondary, and foreign)

Normalization process

Identifying potential problems, called update anomalies, in the design of a relational database Methods for correcting these problems

A relationship that is necessary for identification of an entity.

Identity Relationship

multivalued dependence

In a table with columns A, B, and C, there is a ____ of column B on column A when each value for A is associated with a specific collection of values for B and, further, this collection is independent of any values for C.

An IDEF1X; a collection of subtypes with the property that there are elements of the super type that are NOT elements of any subtype.

Incomplete Category

An entity that *does not require* a relationship to another entity for identification

Independent Entity

The step during DB design in which the goal is to create a clean, DBMS independent design, that will support all user requirements.

Information-Level design

A primary key that consists of a column or collection of columns that is an inherent characteristic of the entity. ALSO CALLED A LOGICAL KEY.

Intelligent Key

A primary key that consists of a column or collection of columns that is an inherent characteristic of the entity. ALSO CALLED AN INTELLIGENT KEY.

Logical Key

The role in a relationship played by an entity with a minimum cardinality of 1 (THAT IS, THERE MUST BE AT LEAST ONE OCCURRENCE OF THE ENTITY).

Mandatory Role

A relationship between *2 entities* in which each occurrence of each entity can be related to many occurrences of the other entity. *CREATE A NEW TABLE WHOSE PRIMARY KEY IS THE COMBINATION OF THE PRIMARY KEYS OF THE ORIGINAL TABLES *YOU CANNOT IMPLEMENT A M2M

Many-to-Many Relationship

A relationship between *3 entities* in which each occurrence of each entity can be related to many occurrences of each of the other entities.

Many-to-Many-to-Many Relationship

Step 4 of information-level design

Merge the results of Steps 1-3 into the cumulative design.

To convert a table to fourth normal form, split the third normal form table into separate tables, each containing the column that ____ the others.

Multidetermines

Consists of a column that uniquely identifies an entity. Also called a logical key or an intelligent key.

Natural Key

A relationship that is not necessary for identification.

Non-Identifying Relationship

Special value. Represents absence of a value in a field. Used when a vale is unknown or inapplicable.

Null

A relationship between *two entities* in which *EACH* occurrence of the 1st entity is related to *MANY* occurrences of the *2nd entity*, and *EACH* occurrence of the *2nd entity* is related to at most, one occurrence of the 1st entity. *INCLUDES PRIMARY KEY OF THE "ONE" TABLE AS A FOREIGN KEY IN THE "MANY" TABLE

One-To-Many Relationship

A relationship between *2 entities* in which each occurrence of the first entity is related to *ONE* occurrence of the 2nd entity AND each occurrence of the 2nd entity is related to, at most, one occurrence of the 1st entity. *SIMPLEST IMPLEMENTATION IS TO TREAT IT AS A ONE-TO-MANY RELATIONSHIP

One-to-One Relationship

The role in a relationship played by an entity with a minimum cardinality of *ZERO* (that is, there need not be any occurrences of the entity).

Optional Rule

The step during DB design in which a design for a given DBMS is produced from the final information-level design. *Process consists of creating a table for each entity in the DBDL design.

Physical-Level Design

The ____ is a column (or collection of columns) A such that all other columns are functionally dependent on A and no subcollection of the columns in A has this property.

Primary key

- Tables (relations), columns (attributes), and primary keys are written by first listing the table name and then, in parentheses, listing the columns that make up the table. The columns that make up the primary key are underlined. - alternate keys are identified by AK and the column that make up the alternate keys - Secondary keys are identified by SK and the columns that make up secondary keys - Foreign keys are identified as FK, and they are followed by an arrow pointing to the table identified by the foreign key.

Rules for defining tables and their keys using DBDL

when it is in first normal form and no nonkey column is dependent on only a portion of the primary key.

Second Normal Form

Normalization Goals

Table in first normal form better than table not in first normal form Table in second normal form better than table in first normal form, and so on Goal: new collection of tables that is free of update anomalies

A column is a nonkey attribute when it is not part of the primary key.

True

Removal of repeating groups is the starting point in the quest to create tables that are as free of problems as possible.

True

The normalization process used to convert a relation or collection of relations to an equivalent collection of third normal form tables is a crucial part of the database design process.

True

The primary key in a table is a determinant but candidate keys are not determinants.

True

A structure that satisfies the properties required to be a relation (table) with the exception of allowing repeating groups (the entries in the table do not have to be single-valued)

Unnormalized Relation

A relation (table) that contains a repeating group

Unnormalized relation

When is a Table in first normal form (1NF)?

When it does not contain repeating groups.

interrelation constraint

a condition that involves two or more relations

*Diamonds* represents in the Entity-Relationship Model (ER Model) as:

a relationship

If there is more than one possible choice for the primary key, and one of the possibilities is chosen to be the primary key, the other choices are referred to as ____.

alternate keys

*Oval* represents in the Entity-Relationship Model (ER Model) as:

an attribute

*Rectangles* represents in the Entity-Relationship Model (ER Model) as:

an entity.

The primary key for each entity appears ____ in the E-R diagram

appears above the line in the rectangle for each entity

Partial dependencies

are dependencies only on a portion of the primary key

Dependency diagram

arrows indicate all functional dependencies Arrows above boxes: normal dependencies Arrows below boxes: partial dependencies

The other columns in each entity appear __ in the E-R diagram

below the line within each rectangle

A(n) ____ is a column or collection of columns on which all columns in the table are functionally dependent but not has not necessarily been chosen as the primary key.

candidate key

Alternate keys

candidate key not chosen as primary key

An alternate key is a ____.

column that could be a primary key but was not chosen

If the primary key of a table contains only a single column, the table is automatically in third normal form.

false

Dot indicates the ___ in the E-R diagram

indicates the "many" part of the one-to-many relationship

Repeating group

multiple entries for a single record

A table is in fourth normal form when it is in third normal form and there are no ____.

multivalued dependence

Second normal form can be defined as a table that is in first normal form but that contains no ____.

no nonkey column is dependent on only a portion of the primary key

Another name for a nonkey column is a ____.

nonkey attribute

A column is a nonkey column if it is ____.

not part of primary key

Nonkey column (nonkey attribute)

not part of primary key

update anomalies

potential problems (Update, Inconsistent data, Additions, and Deletions)

Partial dependencies are dependencies on only a portion of the ____.

primary key

The definition for ____ also defines a candidate key.

primary key

A table is in first normal form if it does not contain ____.

repeating groups

The conversion of an unnormalized table to first normal form requires the removal of ____.

repeating groups

Normal form

table has desirable properties First normal form (1NF) Second normal form (2NF) Third normal form (3NF) Fourth normal form (4NF)

Dashed lines from the rectangle indicates ____ in the E-R diagram

the relationship between the foreign key and the table being identified.

By splitting relations to achieve third normal form tables, you create the need to express interrelation constraints.

true

Converting to third normal form always avoids the problems related to dependencies.

true

If B is functionally dependent on A, you can also say that B functionally determines A.

true

In a dependency diagram, the arrows below the boxes indicate the partial dependencies.

true

To correct update anomalies in a database, tables must be converted into various types of normal forms.

true

Potential problems in the design of a relational database are known as ____.

update anomalies

third normal form (3NF)

when it is in second normal form and the only determinants it contains are candidate keys

fourth normal form (4NF)

when it is in third normal form and there are no multivalued dependencies.