printable version
Test 2 Review A
[1]
[2]
[3]
[4]
[5]
[6]
[7]
[8]
[9]
[10]
[11]
[12]
[13]
[14]
Problem R2a.1.
In studying normal forms, we first defined BCNF: A relation is in BCNF
if every nontrivial functional dependency for that relation has a
superkey on its left-hand side. We later explored a closely related but
more complex definition, which we termed third normal
form (3NF).
Third normal form is more general than BCNF: That is, all BCNF
relations are in 3NF, but some 3NF relations are not in
BCNF.
Explain the deficiency
of BCNF that led to the definition
of 3NF.
Describe how the definition of 3NF differs from the
definition of BCNF.
Some non-BCNF relations cannot be broken into BCNF
relations without breaking apart some FDs applying to the
original non-BCNF relation.
With 3NF, an FD is also acceptable if its right side
consists entirely of attributes appearing in some key for the
relation.
Problem R2a.2.
Suppose we have a relation R(a, b, c, d, e)
whose keys are {a, b} and {c, d}.
Explain why each of the following FDs is consistent with
third normal form.
- a b c → d
- a c e → b c
- a c e → e
- Its left side is a superkey.
- Each right-side attribute either appears on the left side
(c) or appears in a key (b).
- It is trivial
Problem R2a.3.
Suppose we have the relation
books(author, affiliation, title, type, price)
with the functional dependencies
author title → type price
type → price
author → affiliation
Identify which of the functional dependencies violate 3NF,
and synthesize the relation into 3NF relations.
The violating FDs are type → price
and author → affiliation.
The relation synthesizes into three 3NF relations.
writers(author, affiliation)
prices(type, price)
books(author, title, type)
Problem R2a.4.
Draw an entity-relationship diagram for a database of used vehicles,
including each vehicle's
VIN (identification number unique to each vehicle), year of
manufacture, make, model, color,
type (car/truck/minivan/etc.), seats (number), buyer name, and buyer phone number.
Knowing just the make and model is enough to determine the vehicle's type
and the number of seats, and each buyer (who may purchase multiple vehicles)
has just one phone number.
Problem R2a.5.
Draw an entity-relationship diagram for a manufacturer's database to
track client orders. The manufacturer carries several lines of
products. Each order can involve several products, and there is
a quantity and price associated with each product ordered. The
manufacturer tracks client names and telephone numbers.
(You need not bother indicating keys.)
Problem R2a.6.
Draw an entity-relationship diagram for a court database.
A case is initiated by a plaintiff, who names the defendant and
the plaintiff's lawyers. Later, after the defendant is notified
of the case, the defendant names lawyers representing that side.
Then a judge is assigned to the case.
(You need not bother with indicating attributes or keys.)
Problem R2a.7.
Explain what is meant by the term weak entity set, and
give an example.
A weak entity set is an entity set in an
entity-relationship diagram for which the key for the entity set
includes attributes found in another entity set: That is, to
identify a particular entity in the set, we must specify how the entity
relates to another entity.
An example of a weak entity set is the notion of a
course's section. Knowing about a section alone —
without information about the course to which it relates —
isn't very meaningful. Thus, to identify a section, we really
need to know the course information, and so the Section entity
set would be a weak entity set.
Problem R2a.8.
In designing a database for automotive sales, we
create the following entity-relationship diagram.
Convert it into a corresponding set of relational schemas, underlining
the primary-key attributes for each schema.
customers(id, name)
autos(vin, year, make, model, buyer_id)
models(make, model)
Problem R2a.9.
Suppose we were writing a Web site to manage paper submissions
and reviews for a variety of conferences hosted by different
organizations.
We design the following entity-relationship diagram.
Convert it into a corresponding set of relational schemas, underlining
the primary-key attributes for each schema.
reviewers(id, name)
reviews(id, author, title, rating, review)
papers(author, title, sponsor, year)
conferences(sponsor, year, location)
Problem R2a.10.
Below is an entity-relationship diagram for a bug database.
Convert it into a corresponding set of relational schemas, underlining
the primary-key attributes for each schema.
projects(name, url)
bugs(proj_name, id, description, assignee_login)
developers(login, alias)
Problem R2a.11.
Suppose our company runs several projects, each involving several
teams of workers, as indicated in the below entity-relationship
diagram.
Convert it into a corresponding set of relational schemas, underlining
the primary-key attributes for each schema.
projects(name)
teams(project_name, leader_id)
workers(id, office, leader_id)
Problem R2a.12.
Below is an entity-relationship diagram for tracking home sales.
Convert it into a corresponding set of relational schemas,
underlining the primary-key attributes for each schema.
homes(street_address, zip, square_feet)
sales(street_address, zip, date, amount)
buyers(ssn, name)
home_buyers(street_address, zip, date, ssn)
Problem R2a.13.
Below is an entity-relationship diagram for tracking and
discussing bug reports for a collection of software packages.
Convert it into a corresponding set of relational schemas,
underlining the primary-key attributes for each schema.
projects(name, url)
bugs(project_name, id, description)
comments(author, posttime, text)
comment_concerns(author, posttime, project_name, bug_id)
Problem R2a.14.
Below is an entity-relationship diagram for a course schedule where
each section can have any number of instructors. Convert it into a
corresponding set of relational schemas, underlying the primary-key
attributes for each schema.
instructors(first_initial, last_name, phone)
sections(program, number, id, semester, time_slot)
teaches(semester, program, number, section_id, first_initial, last_name)
courses(program, number, title)
requires(requiring_program, requiring_number, required_program, required_number)
