Database Chapter 4

relation

A named, two-dimensional table of data. Each relation consists of a set of named columns and an arbitrary number of unnamed rows.

multivalued

_____ attributes result in two relations. the first contains all attributes except the ____ attribute. the second contains the primary key of the first relation, and the ____ attribute

candidate key

a collection of attributes that satisfies the requirements for a primary key is called _____

recursive foreign key

a foreign key in a relation that references the primary key in the same relation

referential integrity

a foreign key value must match a primary key value in the relation on the one side or be null

transitive dependency

a functional dependency between two or more nonkey attributes

composite

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

First Normal Form (1NF)

a relation is in ___ if all multivalued attributes have been removed (each cell only has one value)

second normal form (2NF)

a relation is in ____ if it is in 1NF and no nonkey attribute is dependent on only a portion of the primary key (there are no partial dependencies)

third normal form (3NF)

a relation is in ____ if it is in 2NF AND no transitive dependencies exist

domain constraints

allowable values for an attribute

primary key

an attribute (or combination of attributes) that uniquely identifies each row in a relation

nonkey attribute

an attribute that is not part of the primary key

no

can there be multivalued attributes in a relation?

restrict deletes

do not allow deletion of a record in the parent table if related rows exist in the child table

unique

each relation in a database has a ____ name, each column has a ___ name, each row in a relation is ____

binary (M:N)

for ___ relations, create the relations for the two entities, create a new relation c, primary key for c is the composite of the attributes making up the primary keys of the two original entities. the primary keys become foreign keys into their respective relations

unary (1:M)

for ___ relationships, create a relation R corresponding to the entity, include a foreign key in R that references the primary key value in R

binary (1:1)

for ___ relationships, create the relations for the two entities, include primary key of either relation as a foreign key in the other relation

unary (M:N)

for ___ relationships, create two relations, R representing the entity, S to represent the relationship itself. the primary key for S is a composite key consisting of two copies of the primary key of R (each FK into R)

associative

for ____ entities, create three relations, two for participated entity types and one for the ____ entity. if no identifier assigned, use composite key of the two primary keys from other relations. if partial identifier, combine with this

ternary

for ____ relationships, create four relations, one for each participating entity and one for associative entity. the primary key is a composite of the 3 primary keys. may need surrogate identifier

binary (1:M)

for ____ relationships, create relations for the two entities, include primary key attributes of the one side as a foreign key in the relation on the man side of the relation

restrict, cascade

general practice is to ____ deletes and ____ updates

cascade deletes

if a record in the parent table is deleted, automatically delete related rows in the child table

functionally dependent

in a relation R, an attribute B is ___ ___ on an attribute or collection of attributes, A, if every valid instance of A uniquely determines the value of B (A -> B)

primary key of the owner entity

in a weak entity, create a new relation containing all simple attributes of it and the _____

determinants

in functional dependency, the attributes on the left side of the arrow are called ____

simple

in turning supertype/subtype entities into relations, if disjoint rule is used, the subtype discriminator will be a ____ attribute

composite

in turning supertype/subtype entities into relations, if overlap rule is used, the subtype discriminator will be a ____ attribute

nonloss decomposition

one goal of any decomposition is that it be a _____. this means a join operation should restore the original information

simple, separate the composite keys

only use composite or simple attributes in relations?

surrogate key

primary key of the relation corresponding to the associative entity

relations, primary key

regular entities become ____, identifier becomes the ____

separate

the most commonly employed strategy is to begin by creating a ___ relation for the supertype and each of its subtypes

insignificant

the ordering of rows and columns is: significant or insignificant?

entity integrity

the primary key components cannot be null, this is known as

2NF, 3NF

to convert from ___ to ___ for each transitive dependency, remove the dependent attributes and place them in a separate relation. place the attributes that determined the dependent attributes into the new relation also, but do not remove these from the original relation

foreign key

used to represent relationships between two relations R1 and R2, consists of one or more attributes in R2, enables a dependent relation to refer to its parent

nullify deletes

when a record is deleted in the parent table, find the related records in the child table and set the foreign key in each of the related records to null