Chapter5

09Oct10

1.                  Define each of the following terms:

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

b.                  Functional dependency  A constraint between two attributes or two sets of attributes.

c.                   Transitive dependency  A functional dependency between two (or more) nonkey attributes.

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

e.                   Normalization  The process of decomposing relations with anomalies to produce smaller, well-structured relations.

f.                   Composite key  A primary key that consists of more than one attribute.

g.                  Relation  A named, two-dimensional table of data.

h.                  Normal form  A state of a relation that results from applying simple rules regarding functional dependencies (or relationships between attributes) to that relation.

i.                    Partial functional dependency  A functional dependency in which one or more nonkey attributes (such as Name) are functionally dependent on part (but not all) of the primary key.

j.                    Enterprise key  A primary key whose value is unique across all relations.

k.                  Surrogate primary key:  A serial number or other system assigned primary key for a relation.

 

2.                  f  well-structured relation

e  anomaly

a  functional dependency

j  determinant

g  composite key

d  1NF

h  2NF

i  3NF

c  recursive foreign key

k   relation

b  transitive dependency

 

3.                  Contrast the following terms:

a.                   Normal form; normalization  Normal form is a state of a particular relation regarding functional dependencies, while normalization is the process of decomposing relations with anomalies to produce smaller, well-structured relations.

b.                  Candidate key; primary key  A primary key is an attribute (or combination of attributes) that uniquely identifies a row in a relation.  When a relation has more than one such attribute (or combination of attributes), each is called a candidate key.  The primary key is then the one chosen by users to uniquely identify the rows in the relation.

c.                   Partial dependency; transitive dependency  A partial functional dependency exists when a nonkey attribute is functionally dependent on part (but not all) of a composite primary key; a transitive dependency is a functional dependency between two or more nonkey attributes.

d.                  Composite key; recursive foreign key  A composite key is a primary key that consists of more than one attribute, while a recursive foreign key is a foreign key in a relation that references the primary key values of that same relation.

e.                   Determinant; candidate key  A determinant is the attribute on the left-hand side of the arrow in a functional dependency, while a candidate key uniquely identifies a row in a relation.

f.                   Foreign key; primary key  A primary key uniquely identifies each row in a relation, while a foreign key is a primary key in another table.

g.                  Enterprise key; surrogate primary key  An enterprise key is a primary key whose value is unique across all relations in the whole database and is likely to hold no business meaning.  A surrogate primary key is a primary key whose value is a serial number or other system assigned value and is unique to the relation.

 

4.                  Six important properties of relations are:

a.                   Each relation in a database has a unique name.

b.                  An entry at the intersection of each row and column is atomic (or single valued).

c.                   Each row is unique.

d.                  Each attribute within a table has a unique name.

e.                   The sequence of columns is insignificant.

f.                   The sequence of rows is insignificant.

 

5.                  Describe two properties that must be satisfied by candidate keys:

a.                   Unique identification: For every row, the value of the key must uniquely identify that row.

b.                  Nonredundancy: No attribute in the key can be deleted without destroying the property of unique identification.

 

6.                  Three types of anomalies in tables:

a.                   Insertion anomaly: A new row cannot be inserted unless all primary key values are supplied.

b.                  Deletion anomaly: Deleting a row results in the loss of important information not stored elsewhere.

c.                   Modification anomaly: A simple update must be applied to multiple rows.

 

7.                  Fill in the blanks.

a.                   second

b.                  first

c.                   third

 

8.                  A well-structured relation is one that contains a minimum amount of redundancy and allows users to insert, modify, and delete the rows in a table without errors or inconsistency.  Well-structured relations are important because they promote database integrity.

 

9.                  Describe how the following components of an E-R diagram are transformed into relations:

a.                   Regular entity type: Each entity type is transformed into a simple relation.  Each simple attribute of the entity type becomes an attribute of the relation.

b.                  Relationship (1:M): A relation is created for each of the two entity types participating in the relationship.  The primary key attribute of the entity on the one-side of the relationship becomes a foreign key in the relation on the many-side of the relationship.

c.                   Relationship (M:N): A new relation is created to represent this relationship.  The primary key for each of the participating entity types is included in this new relation.

d.                  Relationship (supertype/subtype): A separate relation is created for the supertype and each of its subtypes.  The primary key of the supertype is assigned to each subtype, as well as attributes that are unique to the subtype.

e.                   Multivalued attribute: A new relation is created to replace the multivalued attribute.  The primary key of this new relation consists of two attributes:  the primary key of the original relation, plus the multivalued attribute itself.

f.                   Weak entity: A new relation is created corresponding to the weak entity.  The primary key of this relation consists of the primary key of the owner relation, plus the partial identifier of the weak entity type.

g.                  Composite attribute: The simple component attributes of the composite attribute are included in the new relation.

 

10.              Four typical problems in merging relations:

a.                   Synonyms: Two (or more) attributes have different names but the same meaning.  Solution: Convince users to standardize on a single name.

b.                  Homonyms: A single attribute has more than one meaning.

Solution: Create new attribute names that capture the separate meanings.

c.                   Transitive dependency: Merging relations produces transitive dependencies.

Solution: Create 3 NF relations by removing the transitive dependency.

d.                  Supertype/subtype: May be implied by content of existing relations.

Solution: Create new relations that explicitly recognize this relationship.

11.              Three conditions that imply a relation is in second normal form:

a.                   The primary key consists of a simple attribute

b.                  No nonkey attributes exist in the relation

c.                   Every nonkey attribute is functionally dependent on the full set of primary key attributes

 

12.              Integrity constraints enforced in SQL CREATE TABLE commands:

a.                   Entity integrity: enforced by NOT NULL clause

b.                  Referential integrity: enforced by FOREIGN KEY REFERENCES statement

 

13.              Relationships between entities are represented by foreign key values in one relation that match primary key values in another relation.

 

14.              A 1:M unary relationship is represented by a recursive foreign key whose values reference the primary key values of the same relation.

 

15.              An M:N ternary relationship is represented by a new associative relation whose primary key consists of the primary key attributes of the participating entity types.

 

16.              A new relation (called the associative relation) is created to represent the associative entity. The default primary key consists of the primary key values from the relations that participate in the association.

 

17.              All of the nonkey attributes of a relation are functionally dependent on the primary key of that relation.

 

18.       A foreign key must not be null when the minimum cardinality of the relationship implied by the foreign key is one.

 

19.       The only way to eliminate ripple effects is to create an enterprise key from the very beginning of database development.

 

20.       A unary 1:M relationship always utilizes a recursive foreign key, whereas an M:N recursive relationship dictates that a second table must be created for the relationship.

 

21.       The following three conditions are adequate to suggest that a surrogate key should be developed for a relation:

- the presence of a composite primary key

- the natural primary key (the one initially identified by users during conceptual modeling activities) is too long or made up of more than two attributes which will cause performance issues with database software

- the natural primary key cannot be guaranteed to be unique over time (e.g., due to duplicates or re-use over time)

Advertisement

Filed under: Uncategorized   |  Closed