Database Chapter 4 MIS 380

2nd Normal Form Requirements

- Is in 1st normal form - every non-key attribute is functionally dependent on the entire primary key

3rd Normal Form Requirements

- Is in 2nd normal form - There are no transitive dependents

Boyce-Codd Normal Form Requirements

- every determinant is a candidate key

1st Normal Form Requirements

- no duplicate rows - no multi-valued attributes - order of rows is insignificant

Functional Dependency

A constraint between two attributes in which the value of one attribute is determined by a value of another attribute.

Transitive Dependency

A functional dependency between the primary key and one or more nonkey attributes that are dependent on the primary key via another nonkey attribute.

2nd normal form

A relation in first normal form in which every nonkey attribute is fully functionally dependent on the primary key.

Third normal form

A relation that is in second normal form and has no transitive dependencies.

Candidate Key

An attribute, or combination of attributes, that uniquely identifies a row in a relation.

Anomaly

An error or inconsistency that may result when user attempts to update a table that contains redundant data.

What are foreign keys?

Foreign keys are identifiers that enable a dependent relation (on the many side of a relationship) to refer to its parent relation (on the one side of the relationship).

Requirements for a table to qualify as a relation:

It must have a unique name. Every attribute value must be atomic (not multivalued, not composite). Every row must be unique (can't have two rows with exactly the same values for all their fields). Attributes (columns) in tables must have unique names. The order of the columns must be irrelevant. The order of the rows must be irrelevant.

What are primary keys?

Primary keys are unique identifiers of the relation. Examples include employee numbers, social security numbers, etc. This guarantees that all rows are unique.

Delete rules

Restrict-don't allow delete of "parent" side if related rows exist in "dependent" side Cascade-automatically delete "dependent" side rows that correspond with the "parent" side row to be deleted Set-to-Null-set the foreign key in the dependent side to null if deleting from the parent side not allowed for weak entities

Determinant

The attribute on the left side of the arrow in a functional dependency.

A candidate key is an attribute, or combination of attributes, that uniquely identifies a row in a relation. (True/False)

True

A cascading delete removes all records in other tables associated with the record to be deleted. (True/False)

True

A primary key is an attribute that uniquely identifies each row in a relation. (True/False)

True

A referential integrity constraint is a rule that maintains consistency among the rows of two relations.

True/False

A primary key that consists of more than one attribute is called a:

composite key

A primary key whose value is unique across all relations is called a(n):

enterprise key

A relation is in first normal form if it has no more than one multivalued attribute. (True/False)

false

An attribute in a relation of a database that serves as the primary key of another relation in the same database is called a:

foreign key

The normal form which deals with multivalued dependencies is called:

fourth normal form

A constraint between two attributes is called a(n):

functional dependency

Types of Anomaly

insertion, deletion, and modification anomalies

All of the following are the main goals of normalization EXCEPT:

minimize data redundancy. simplify the enforcement of referential integrity. *maximize storage space.* make it easier to maintain data.

An attribute (or attributes) that uniquely identifies each row in a relation is called a:

primary key

A rule that states that each foreign key value must match a primary key value in the other relation is called the:

referential integrity constraint.

A two-dimensional table of data sometimes is called a:

relation

What is referential integrity?

rule states that any foreign key value (on the relation of the many side) MUST match a primary key value in the relation of the one side. (Or the foreign key can be null)

A relation that contains no multivalued attributes and has nonkey attributes solely dependent on the primary key but contains transitive dependencies is in which normal form?

second

A functional dependency between two or more nonkey attributes is called a:

transitive dependency.

The allowable range of values for a given attribute is part of the domain constraint. (True/False)

true

The columns of a relation can be interchanged without changing the meaning or use of the relation. (True/False)

true