CS 430 - Midterm 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 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

___________________________ 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.

domain

Which integrity constraint is used to establish and enforce referential integrity? (Question by Haines)

foreign key constraint

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

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}+?

F, C, D, E

A faculty member is a person; a student is a person. These are two examples of ________________________. More than one answer may apply.

Is-a

Match the attribute type to its description. Simple attributes Composite attributes Single-valued attribute Multi-valued attribute Derived attribute An attribute whose value is computed from the values of other attributes.

- - Attributes that have a single value for a particular entity. Attributes that have a set of values for a particular entity. An attribute whose value is computed from the values of other attributes.

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 -> B is a trivial functional dependency (that is, B is a subset of A) A is a superkey for schema R.

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

A n-ary relationship set shows more clearly that several entities participate in a single relationship. 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.

Match the term used in E-R models with its definition. Entity Entity set Attribute Value

A thing or object in the real world that is distinguishable from all other objects. A set of objects of the same type that share the same properties. Descriptive properties posessed by each member of an entity set. 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 -> D}.Which of these attributes are in the closure of {A, C, F}+?

A, B, C, D, E, F

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 ABD -> C AB -> D D -> ABC

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

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

Match the term used in relationship sets to its definition. Relationship Relationship set Participation Relationship instance

An association among several entities. A set of associations among several entities of the same type. When an entity set is a part of a relationship set. In an E-R schema, this represents an association between the named entities in the real-world enterprise being modeled.

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 on e of the following holds:

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

Match the database design phase to its description. Initial phase Conceptual-design phase Specification of functional requirements Logical-design phase Physical-design phase

Characterize fully the data needs of the prospective database users. The designer chooses a data model and by applying the concepts of the choosen data model, translates into a conceptual schema. Users describe the kinds of operations or transactions that will be performed on the data. Designer maps the high-level conceptual schema onto the implementation data model of the database system that will be used.

Date is an example of which type of attribute.

Composite

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

Composite

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

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

___________________ 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 of the following defines a functional dependency?

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

Name the two major pitfalls of a database design process.

Incompleteness Redundancy

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 each of the following functional dependency rules of inference with the name of the Armstrong Axiom or Implication to which it belongs. If Y is a subset of X, then X -> Y If X -> Y and Y -> Z, then X -> Z If X -> Y and Z is a set of attributes, then XZ -> YZ (or XunionZ -> YunionZ) If X -> Y and X -> Z, then X-> YZ If X-> YZ, then X -> Y and X -> Z

Rule of Reflexity Rule of Transitivity Rule of Augmentation (Unions) Rule of Union (Additive) Rule of Decomposition (Projection)

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

The domains of all attributes of R are atomic.

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

Trigger

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 F of functional dependencies, we say that r satisfies F.

True

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

True

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

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

Weak entity Set

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