|
.
1.
Define each of the following terms:
.
a.
Determinant.
The attribute on the left-handed 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) non-key
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
non-key attributes
(such
as Name) are
functionally dependent on part (but not all) of the
primary key.
.
2.
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 attributes
(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.
Functional
dependency; transitive dependency.
A functional dependency is a constraint
between
any two attributes
(or
two sets of attributes), while
a transitive dependency is a functional dependency
between two
or more non-key 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 on the left-hand side of the
arrow in a functional
dependency, while a candidate key uniquely identifies a
row in a relation.
.
3.
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.
.
4.
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.
.
5.
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 losing important
information not stored elsewhere.
c.
Modification
anomaly: a
simple update must be applied to multiple rows.
.
6.
Fill in the blanks.
.
a.
second
b.
first
c.
third
.
7.
A well-structured relation is a relation
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.
.
8.
Describe how the following components of an E-R diagram
are transformed to relations:
.
a.
Regular entity type:
each entity type is transformed to 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.
.
9.
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.
.
10.
Three conditions that imply a relation is
in second normal form:
.
a.
The primary key consists of a simple attribute.
b.
No non-key attributes exist in the relation.
c.
Every non-key attribute is functionally dependent
on the full set of primary key attributes.
.
11.
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.
.
12.
Relationships between entities are represented by
foreign key values in one relation that match
primary
key values in
another relation.
.
13.
A 1:M unary relationship is represented be a recursive
foreign key whose values reference the primary
key values of
the same relation.
.
14.
A 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.
.
15.
All of the non-key attributes of a
relation are functionally dependent on the primary key
of that relation.
.
^
|
