CGS 2545 Chapter 4

Which of the following are properties of relations?

- Each attribute has a unique name. - No two rows in a relation are identical. -There are no multivalued attributes in a relation

Which of the following anomalies results from a transitive dependency?

-Insertion -Modification -Deletion

Which of the following are anomalies that can be caused by redundancies in tables?

-Insertion -Deletion -Modification

Explain what a candidate key is and how it might be used.

A candidate key is an attribute or a combination of attributes that uniquely identifies a row in a relation. A candidate key must be nonredundant and must also uniquely identify each row. When we look at functional dependencies and candidate keys, we can always say that a determinant is always a candidate key

Discuss how to map a unary one-to-many relationship

A foreign key attribute is added with in the same relation that references the primary key values.

An alternative name for an attribute is called a(n)

Alias

__________ do not generally arise out of transitive dependencies.

Anomalies

__________ can model more complex data relationships, such as ternary relationships.

Case tools

________ is a component of the relational data model included to specify business rules to maintain the integrity of data when they are manipulated.

Data integrity

The allowable range of values for a given attribute is part of the ____________.

Delivery Constraint

The attribute on the left-hand side of the arrow in a functional dependency is the:

Determinant

Discuss the two major occasions when you benefit from using normalization.

During logical database design, normalization is used as a check and balance to make sure that your E-R diagram is correct as well as your relational schema before doing physical design. Another occasion when you benefit is reverse-engineering an older system, since many of the tables and user views are redundant

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

Enterprise key

_________ are useful for developing prototype applications and for testing queries.

Sample data

Discuss how to map a unary many-to-many relationship

The primary key of the associative relation consists of two attributes which take their value from the primary key of the other relation

___________ is the process of merging relations together.

View integration

List and explain all of the normal forms.

When a relation is in first normal form, all repeating groups have been removed. Second normal form means that any partial functional dependencies have been removed. A relation is in third normal form when all transitive dependencies have been resolved. A relation is in fourth normal form when any dependencies from multivalued attributes have been removed. Fifth normal form removes any further anomalies

Explain how subtype/supertype relationships are converted to relations.

When we have a supertype/subtype relationship, we need to assign one entity for the supertype and one for each subtype. The supertype has all attributes common to all subtypes as well as a primary key. Each subtype relation has the primary key of the supertype as well as any attributes that are specific to that subtype. Finally, we assign one or more attributes to the supertype to function as subtype discriminators

An ________ is a type of flaw in the database server.

anomaly

A composite key consists of only one

attribute

A _________ is an attribute, or combination of attributes, that uniquely identifies a row in a relation.

candidate key

A _________ removes all records in other tables associated with the record to be deleted.

cascading delete

The __________ of a relation can be interchanged without changing the meaning or use of the relation.

columns

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

composite key

In the SQL language, the ________ statement is used to make table definitions.

create table

When a regular entity type contains a multivalued attribute, one must:

create two new relations, one containing the multivalued attribute

An identifier assigned to an associative entity is also called a ______________.

cross-relation key

The relational data model consists of which components?

data structure, data manipulation, and data integrity.

A nonkey attribute is also called a(n):

descriptor

An ________ is a foreign key whose value is unique across all relations.

enterprise key

The ________ states that no primary key attribute may be null.

entity integrity rule

When all multivalued attributes have been removed from a relation, it is said to be in:

first normal form

A __________ is a primary key of a relation that also is a primary key in another relation

foreign key

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

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

functional dependency

An attribute that may have more than one meaning is called a(n):

homonym

A domain definition consists of the following components EXCEPT:

integrity constraints

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

maximize storage space

A well-structured relation contains _____ redundancy and allows users to manipulate the relation without errors or inconsistencies.

minimal

There can be _______ attributes in a relation.

multivalued

The entity integrity rule states that:

no primary key attribute can be null

When ________, the goal is to decompose relations with anomalies to produce smaller, well-structured relations.

normalizing

The entity integrity rule states that a primary key attribute can be _____.

null

A relation is in first normal form if it has no more than _____ multivalued attribute.

one

When transforming a weak entity, one should create _____ relation with both the attributes of the strong entity and the attributes of the weak entity.

one

The primary key of the many side migrates to the one side when transforming a ______ relationship.

one-to-many

When transforming a ________ relationship, a new relation is always created.

one-to-one

A functional dependency in which one or more nonkey attributes are functionally dependent on part, but not all, of the primary key is called a ________ dependency.

partial functional

A ___________ is a functional dependency in which one or more nonkey attributes are functionally dependent on part (but not all) of the primary key.

partial functional dependency

A ___________ is an attribute that uniquely identifies each row in a relation.

primary key

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

primary key

In the relational data model, associations between tables are defined through the use of ________.

primary keys

When transforming a unary many-to-many relationship to relations, a _________ key is used.

recursive foreign

A ___________ is a rule that maintains consistency among the rows of two relations.

referential integrity constraint

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

All values that appear in a column of a _______ must be taken from the same domain.

relation

Data integrity consists of powerful operations to manipulate data stored in:

relations

Unlike columns, the ______ of a relation may not be interchanged and must be stored in one sequence.

rows

Data structures include data organized in the form of tables with:

rows and columns

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

The relational data model does, at this time, directly support ______ relationships.

subtype/supertype

A __________ is an attribute that may have more than one meaning.

synonym

Two or more attributes having different names but the same meaning are called:

synonyms

When two or more attributes describe the same characteristic of an entity, they are __________.

synonyms

A candidate key must satisfy all of the following conditions EXCEPT:

the key must indicate the row's position in the table

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

transitive dependency

A _________ is a functional dependency between two or more nonkey attributes.

transversal dependency

The ________ in SQL creates a new table.

truncate table statement

When a regular entity type contains a multivalued attribute, ____relations are created.

two

If an identifier is not assigned, the default primary key for an associative relation consists of the ___________ attributes from the other two relations.

two primary key

One property of a relation is that each attribute within a relation has a _____.

unique name

A relation that contains minimal redundancy and allows easy use is considered to be:

well-structured

Understanding the steps involved in transforming EER diagrams into relations is important because:

you must be able to check the output of a CASE tool