MGT 4058 Test 2

What is a recursive foreign key?

A foreign key in a relation that references the primary key values of that same relation

What is transitive dependency?

A functional dependency between two (or more) non-key attributes

What is a partial functional dependency?

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

What is functional dependency?

A particular relationship between two attributes or two sets of attributes: the value of one attribute (the determinant) determines the value of other attributes Represented as A-> B Data in a relation do not prove that a functional dependency exists Only knowledge of the problem domain is a reliable method for identifying a functional dependency

What is always a determinant?

A primary key. Although, a determinant may or may not be a primary key

What is the rule for a many-to-many unary relationship for transforming E-R diagrams into relations?

Create TWO tables: one for the entity type and one for an associative relation in which the primary key has two attributes, both taken from the primary key of the entity

What is the rule for a many-to-many binary relationship for transforming E-R diagrams into relations?

Create a NEW RELATION with the primary keys of the two entities as its primary key

What is the rule for supertype/subtype relationships for transforming E-R diagrams into relations?

Create a separate relation for the supertype and each of the subtypes. Assign common attributes to the supertype. Assign to the subtypes the primary key of the supertype and those attributes unique to that subtype. Assign attribute(s) to the supertype to function as subtype discriminator(s)

What is the rule for multi-valued attributes when transforming E-R diagrams into relations?

If an entity has a multivalued attribute, this multivalued attribute in an E-R diagram is mapped to a relation of its own. The relation is named after the plural multivalued attribute and the relation should have one column which is the entity's primary key

How is the primary key of a weak entity set formed?

It is formed by the primary key of the strong entity plus the weak entity's discriminator. In the case of a 1:1 relationship, a weak entity has no partial key; the primary key of the strong entity is mapped to be the primary key of the weak entity

What is a determinant?

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

What is the rule for associative entities with an identifier not assigned for transforming E-R diagrams into relations?

The default primary key for the associative relation (also called the intersection table) is a composite key composed of the primary keys of the two entities

What is normalization?

The process of decomposing relations with anomalies to produce smaller, well-structured relations; the formal process for grouping attributes into relations; a tool to validate and improve logical designs so that they satisfy certain constraints to avoid unnecessary duplication of data

How does the E-R Model and Normalization relate?

Normalization should be part of the database design process The E-R model provides the big picture or macro view of an organization's data requirements and operations. An E-R model is created through an iterative process. We begin by identifying relevant entities, their attributes, and their relationships. Then we use the results to identify additional entities and attributes Normalization procedures focus on the characteristics of specific entities; that is, normalization represents a micro view of the entities within the E-R model. And the normalization process may yield additional entities and attributes to be incorporated into the E-R model. Therefore, it is difficult to separate the normalization process from the E-R modeling process; the two techniques should be used concurrently

What is the rule for a one-to-one binary relationship for transforming E-R diagrams into relations?

Primary key on the MANDATORY side becomes a foreign key on the OPTIONAL side

What is the rule for a one-to-many binary relationship for transforming E-R diagrams into relations?

Primary key on the ONE side becomes a foreign key on the MANY side

Does a transitive dependency still yield data anomalies?

Yes

What makes a table in 2NF?

1. It is in 1NF 2. It includes no partial dependencies; that is, no attribute is dependent on only a portion of the primary key

What makes a table in 3NF?

1. It is in 2NF 2. It contains no transitive dependencies

What are the reasons to have an associative entity with an identifier assigned for transforming E-R diagrams into relations?

1. The associative entity type has a natural identifier that is familiar to end users 2. The default identifier (consisting of the identifiers for each of the participating entity types) may not uniquely identify instances of the associative entity

A relation that is in 1NF will be in 2NF if ANY ONE of what conditions apply?

1. The primary key consists of only one attribute 2. No non-key attributes exist in the relation (thus all of the attributes in the relation are components of the primary key) 3. Every non-key attribute is functionally dependent on the full set of primary key attributes

What makes a relation in 1NF?

1. There are no repeating groups in the relation. In other words, each row/column intersection can contain one and only one value, rather than a set of values (NO MULTIVALUED ATTRIBUTES) 2. A primary key has been defined, which uniquely identifies each row in the relation

How do you convert a 1NF to a 2NF?

1. Write each key component on a separate line, and then write the original key on the last line. Each component will become the key in a new table 2. Write the dependent attributes after each new key

What is normal form?

A state of a relation that can be determined by applying simple rules regarding functional dependencies (or relationships between attributes)

What problem is created by partial functional dependency?

A table that contains such dependencies is still subject to data redundancies and, therefore, to various anomalies

How is a table with repeating groups converted to a relation in 1NF?

By extending the data in each column to fill the cells that are empty because of the repeating groups structures

What is the general rule when transforming E-R diagrams into relations?

Each entity in an E-R diagram is mapped to a single table in a relational database. The table is named after the entity

What is the rule for associative entities for transforming E-R diagrams into relations?

Mapping the associative entity involves essentially the same steps as mapping a M:N relationship

What is the rule for weak entities for transforming E-R diagrams into relations?

One column per attribute PLUS column(s) for primary key(s) of its strong entity

What is the rule for regular (strong) entities when transforming E-R diagrams into relations?

One column per attribute. For composite attributes, use only their simple component attributes

What is the rule for ternary (and n-ary) relationships for transforming E-R diagrams into relations?

One relation for each entity and one for the associative entity. The default primary key of this associative entity consists of the primary keys for the participating entity types. In some cases, additional attributes are required to form a unique primary key

What is the rule for a one-to-many unary relationship for transforming E-R diagrams into relations?

Recursive foreign key in the SAME relation (ONE table)

What is the discriminator of a weak entity?

The partial key/partial identifier. The set of attributes that distinguishes among all the entities of a weak entity set

What are the objectives of the normalization process?

To eliminate certain kinds of data redundancy and to avoid certain anomalies