CS 430 Exam 3

The set of all functional dependencies that can be inferred give the set F, denoted by F+ is called the ___ of set F.

closure

If a birthdate attribute has three components (birthDay, birthMonth, birthYear), it could be treated as a ___ attribute.

composite

If a relation contains as attributes the birthdate of a person as well at the age of the age of that person, the age could be treated as a ___ attribute.

derived

___ specify the set of possible values that may be associated with an attribute.

domain constraints

A database design is in BCNF if one member of the set of relation schemas that constitutes the design is in BCNF.

false

BCNF can always be achieved without sacrificing losslessness or dependency preservation.

false

If some but not all entities in entity set E participates in at least two relationships in relationship set R, the participation is said to be partial.

false

It is required that the primary key of an entity set be used as an attribute of another entity set when that entity set is in total participation of a relationship set.

false

The discriminator of a weak entity set is the combination of the primary key from the identifying relationship set and enough attributes to distinguish the weak entity set.

false

Which integrity constraint is used to establish and enforce referential integrity?

foreign key constraint

A faculty member is a person; a student is a person. These are two examples of ___.

generalization the IS-A relationship

If we wish to constrain ourselves to relations on schema R that satisfy a set F of functional dependencies, we say that F ___ on R.

holds

Match the term used in E-R models with its definition.

Entity - a thing or object in the real world that is distinguishable from all other objects. Entity set - a set of objects of the same type that shared same properties. Attribute - descriptive properties possessed by each member of an entity set. Value - assigned to each attribute with an entity.

Consider the relation R with attributes: A, B, C, D, E, and F. Let S be a set of functional dependencies in R such that S = { A -> B, CD -> E, C -> A }. Which of these attributes are in the closure of { A, B } + ?

- A - B

Consider the relation R with attributes: A, B, C, D, E, and F. Let S be a set of functional dependencies in R such that S = { A -> B, CD -> E, C -> D }. Which of these attributes are in the closure of { A, C, F } + ?

- A - B - C - D - E - F

A relation schema R is in third normal form with respect to set F of functional dependencies, if for all functional dependencies in F+ of the form A -> B where A is a subset of R, B is an attribute of R, and at least one of the following holds:

- A -> B is a trivial functional dependency - A is a superkey for R. - B is part of a key for R.

A relation schema R is in BCNF with respect to a set F of functional dependencies if, for all functional dependencies in F+ of the form A -> B, where A is a subset of R, B is a subset of R - and at least one of the following hold:

- A is a superkey for schema R. - A -> is a trivial functional dependency (that is, B is a subset of A)

Consider the following relation. Check all the answers below that hold for this table. A B C D a1 b1 c1 d1 a1 b3 c1 d2 a3 b1 c1 d3 a2 b7 c1 d5

- AB -> CD - AB -> D - ABD -> C - D -> ABC

Consider the following relation. Check all the answers below that hold for this table. A B C D a1 b1 c1 d1 a2 b3 c1 d2 a3 b1 c1 d3 a2 b7 c1 d5

- AB -> CD - AB -> D - ABD -> C - D -> ABC

Why might we not want to place the restriction on an E-R diagram that only binary relationship sets be used?

- An identifying attribute may have to be created for the entity set created to represent the relationship set, increasing the complexity of the design. - There may not be a way to translate constraints on the ternary relationship in to constraints on the binary relationships. - A n-ary relationship set shows more clearly that several entities participate in a single relationship.

Consider the following relation. Check all the answers below that hold for this table. A B C D a1 b1 c1 d1 a1 b3 c1 d2 a3 b2 c1 d3 a2 b7 c1 d5

- B -> A - B -> ACD - D -> ABC - AB -> D

Consider the relation R with attributes: A, B, C, D, E, and F. Let S be a set of functional dependencies in R such that S = { A -> B, CD -> E, C -> D }. Which of these attributes are in the closure of { C, F } + ?

- C - D - E - F

An entity set that does not have sufficient attributes to form a primary key is called:

Weak entity set.

Match each of the following functional dependency rules of inference with the name of the Armstrong Axiom or Implication to which it belongs.

- Rule of Reflexivity: If Y is a subset of X, then X -> Y. - Rule of Transitivity: If X -> Y and Y -> Z, then X -> Z. - Rule of Augmentation (Unions): If X -> Y and Z is a set of attributes, then XZ -> YZ (or XunionZ -> YunionZ). - Rule of Union (Additive): If X -> Y and X -> Z, then X -> YZ. - Rule of Decomposition (Projection): If X -> YZ, then X -> Y and X -> Z.

Match the database design phase to its description.

-Initial phase: characterize fully the data needs of the prospective database users. -Conceptual-design phase: the designer chooses a data model and by applying the concepts of the chosen data model, translates into a conceptual schema. -Specification of functional requirements: users describe the kinds of. -Logical-design phase: designer maps the high-level conceptual schema onto the implementation data model of the database system that will be used. -Physical-design phase: the physical features of the database, such as internal storage structures are specified.

Match the term used in relationship sets to its definition.

-Relationship: an association among several entities. -Relationship set: a set of association among several entities of the same type. -Participation: when an entity is a part of a relationship set. -Relationship instance: in an E-R schema, this represents an association between the named entities in the real world enterprise being modeled.

Match the attribute type to its description.

-Simple attributes: attributes that have not been subdivided in to sub-parts. -Composite attributes: attributes that are divided into other attributes. -Single-valued attributes: attributes that have a single value for a particular entity. -Multi-valued attributes: attributes that have a set of value for a particular entity. -Derived attributes: an attributes whose value is computed from the value of other attributes.

Date is an example of which type of attribute.

Composite attribute.

Which of the following defines a functional dependency?

If there were a schema (A, B), then A is able to serve as a primary key.

Match the key and participation constraint to its appropriate description.

One-to-one - An entity in A is associated with at most one entity in B, and an entity in B is associated with at most one entity in A. One-to-many - An entity in A is associated with any number of entities in B. An entity in B is associated with at most one entity in A. Many-to-one - An entity in A is associated with at most one entity in B. An entity in B is associated with any number of entities in A. Many-to-many - An entity in A is associated with any number of entities in B. An entity in B is associated with any number of entities in A.

Match the component of an E-R diagram to its description.

Rectangles - Entity sets. Items within the rectangles - Attributes. Diamonds - Relationship sets. Lines - Link attributes to entity sets a. Double line - Shows total participation of/ Line with arrow - Shows a key constraint from t. Double line rectangle w/ associated double line diamond - Weak entity set.

Name the two major pitfalls of a database design process.

Redundancy Incompleteness

___ constraints ensure that a value that appears in one relation for a given set of attributes also appears for a certain set of attributes in another relation.

Referential integrity

We say that a relation schema R is in first normal form if:

The domains of all attributes of R are atomic.

The participation of an entity set E in a relationship set R is said to be total if every entity E participates in at least one relationship in R.

True

The elements within a(n) ___ domain are not divisible.

atomic

If a phoneNumber attribute could include more than one phone number, it could be treated as a ___ attribute.

multi-valued

The cardinality of a database relation is ___.

the number of relationships in which the entity is involved.

A ___ is a statement that the system executes automatically as a side effect of a database modification.

trigger

Some functional dependencies are said to be ___ because they are satisfied by all relations.

trivial

A decomposition is a lossless decomposition if for all legal database instances we project r onto R1 and R2, compute the natural join of the projection results, and we get back exactly r.

true

A functional dependency is a form of an integrity constraint.

true

Aggregation is an abstraction through which relationships are treated as higher level entities.

true

Axioms, or rules of inference, provide a simpler technique for reasoning about functional dependencies.

true

If a relation r is legal under a set of F of functional dependencies, we say that r satisfies F.

true

In general, the goal of a relational database design is to generate a set of relation schemas that allows us to store information without unnecessary redundancy and yet allows us to retrieve information easily.

true

It is always possible to decompose a relation into 3NF form that is both lossless join and dependency preserving.

true