Databases Reference
In-Depth Information
13.
Using a document at your own school (for example, a report card), determine the attributes present in the
document. Using your knowledge of the policies at your school, determine the functional dependencies present
in the document. Use these dependencies to create a set of tables and columns that you could use to pro-
duce the document.
14.
Describe the different ways of implementing one-to-one relationships. Assume you are maintaining information
on offices (office numbers, buildings, and phone numbers) and faculty (numbers and names). No office houses
more than one faculty member; no faculty member is assigned more than one office. Illustrate the ways of imple-
menting one-to-one relationships using offices and faculty. Which option would be best in each of the following
situations?
a.
221
A faculty member must have an office, and each office must be occupied by a faculty member.
b.
A faculty member must have an office, but some offices are not currently occupied. You must maintain infor-
mation about the unoccupied offices in an Office relation.
c.
Some faculty members do not have an office, but all offices are occupied.
d.
Some faculty members do not have an office, but some offices are not occupied.
15.
For each of the following collections of relations, give the assumptions concerning the relationship between stu-
dents, courses, and faculty members that are implied by the collection. In each relation, only the primary keys
are shown.
a.
Student (
StudentNum
,
CourseNum
,
FacultyNum
)
b.
Student (
StudentNum
,
CourseNum
)
Faculty (CourseNum,
FacultyNum
)
c.
Student (
StudentNum
,
CourseNum
)
Faculty (
CourseNum
,
FacultyNum
)
StudentFaculty (
StudentNum
,
FacultyNum
)
d.
Student (
StudentNum
,
CourseNum
, FacultyNum)
e.
Student (
StudentNum
,
CourseNum
)
Faculty (
CourseNum
,
FacultyNum
)
StudentFaculty (
StudentNum
, FacultyNum)
16.
Describe the relationship between columns that can be null and entity subtypes. Under what circumstances would
these columns lead to more than one entity subtype?
17.
How is it possible to merge a collection of relations that is in third normal form into a cumulative design that is
in third normal form but not obtain a collection of relations that is in third normal form? Give an example other
than the one in the text.
18.
Describe the entity-relationship model. How are entities, relationships, and attributes represented in this model?
What is a composite entity? Describe the approach to diagrams that uses a crow's foot. Describe how you would
represent cardinality in an E-R diagram.
The following exercises are based on the Premiere Products database as designed in Example 1. In each exer-
cise, represent your answer in DBDL and with a diagram. You may use any of the styles presented in this chapter for
the diagram.
1.
Indicate the changes you need to make to the design of the Premiere Products database to support the follow-
ing situation: A customer is not necessarily represented by a single sales rep but can be represented by sev-
eral sales reps. When a customer places an order, the sales rep who gets the commission on the order must be
one of the collection of sales reps who represents the customer.
2.
Indicate the changes you need to make to the design of the Premiere Products database to support the follow-
ing situation: There is no relationship between customers and sales reps. When a customer places an order, it
may be through any sales rep. On the order, identify both the customer placing the order and the sales rep
responsible for the order.
