CS145 - Introduction to Databases
Spring 2000, Prof. Widom
Written Assignment #1
Due Wednesday April 12
Turn-in procedure and late policy reminder
- Assignments must be turned in on paper. On your assignment
please include your name and your preferred email address.
- On-campus students: Your assignment must be turned in
by 5:00 PM on Wednesday April 12. You may turn the assignment in during
class on Wednesday, or you may turn it in at the course administrator's
office: Marianne Siroker, Gates 436. Assignments less than 24 hours
late will be penalized 10%. Assignments more than 24 but less than 48
hours late will be penalized 30%. No assignments will be accepted
after 5:00 PM on Friday April 14.
- SITN students: Your assignment must be timestamped by
the courier on Thursday April 13. Late assignments timestamped by the
courier on Friday April 14 will be penalized 20%. Assignments
timestamped later than April 14 will not be accepted. Assignments not
sent by SITN courier are subject to the late policy for on-campus
students specified above. No late assignments are permitted for
remote SITN students.
- For emergencies: Since emergencies do arise, each
student is allocated four "chits". Each chit may be used to turn in a
single assignment up to 24 hours late with no penalty, or two chits
for up to 48 hours late. For SITN students, two chits are required to
turn in an assignment one courier-day late. As with penalized late
work, no assignments will be accepted more than 48 hours (or one
courier-day) late. We'll keep track of your chits automatically.
The assignment
Problem 1
In the E/R model, consider a relationship set R between two
entity sets E1 and E2. Does it ever make sense for the
same pair of entities e1 (from E1) and e2 (from
E2) to participate in the relationship set more than once?
That is, might we ever want to have two (or more) instances of
(e1, e2) in R? If not, explain why not. If so,
give a real-world example: give details of two entity sets and a
relationship set that model a real-world domain where we might have a
pair of entities related more than once.
Problem 2
Hints: There is some interplay between Problem 1 and at least
one part of this problem, and not every part of this problem
necessarily has a single correct answer.
- Part (a) Consider a relationship set R between two
entity sets E1 and E2. Let K1 and K2 be
the key attributes for E1 and E2, respectively. Assume
for now that R has no attributes. Thus, if we translate
R to the relational model, the schema for R's relation
will be (K1,K2). If R is a one-one relationship
set, what is a key for the corresponding relation
(K1,K2)?
- Part (b) Continuing, if R is a one-many
relationship set from E1 to E2, what is a key for the
corresponding relation (K1,K2)?
- Part (c) Continuing, if R is a many-many
relationship set, what is a key for the corresponding relation
(K1,K2)?
- Part (d) Now suppose R is a many-many relationship
set and has an attribute A, so the schema for the corresponding
relation is (K1,K2,A). What is a key for
(K1,K2,A)?
- Part (e) Now suppose R is a multiway relationship
set between three entity sets E1, E2, and E3.
Let K1, K2, and K3 be the key attributes for
E1, E2, and E3, respectively. Assume R
has no attributes. Thus, if we translate R to the relational
model, the schema for R's relation will be
(K1,K2,K3). If in the E/R diagram there is one
arrowhead pointing to entity set E1 and no other arrowheads,
what is the key for (K1,K2,K3)?
- Part (f) Continuing, if in the E/R diagram there is one
arrowhead pointing to entity set E1, one arrowhead pointing to
E2, and no other arrowheads, what is the key for
(K1,K2,K3)?
- Part (g) Continuing, if in the E/R diagram there is one
arrowhead pointing to each entity set, what is the key for
(K1,K2,K3)?
- Part (h) Continuing, if in the E/R diagram there are no
arrowheads at all, what is the key for
(K1,K2,K3)?
Problem 3
Find a site on the Web for which you're quite sure there is a database
management system operating behind the scenes. If the site you select
has a wide variety of data and a very powerful interface, try to
isolate a smaller portion of the site (or choose a simpler site).
- Part (a) What is the URL for the site you selected?
- Part (b) Briefly describe, in English, the data managed
by the site (or portion of the site) and the queries available to the
user.
- Part (c) Specify a possible E/R design for the data
managed by the site. Don't forget to underline key attributes for
entity sets and include arrowheads indicating the multiplicity of
relationship sets. If there are weak entity sets or "is-a"
relationships, make sure to notate them appropriately.
- Part (d) Using the method for translating an E/R diagram
to relations, produce a set of relations from your E/R design.
Specify (underline) keys for all relations.
- Part (e) Give a different relational schema from the one
you got in part (d) that still captures the same information. Specify
(underline) keys for all relations.
Problem 4
You are to design a database for an insurance company. The data will
include information about customers (name, address, and phone number),
information about insured cars (make, model, year, vehicle-ID#,
license#, and insurance rate), information about insured homes
(address, insured value, and insurance rate), and information about
claims made on insured cars and homes (date of claim, date of
settlement, and amount of settlement). You may assume that all
insured cars and homes are owned by a single customer, but you should
allow a customer to own several cars and homes. Please state any
additional assumptions you make about the real world in your design.
- Part (a) Specify an E/R design for your database.
Don't forget to underline key attributes for entity sets and include
arrowheads indicating the multiplicity of relationship sets. If there
are weak entity sets or "is-a" relationships, make sure to notate them
appropriately.
- Part (b) Using the method for translating an E/R diagram
to relations, produce a set of relations from your E/R design.
Specify (underline) keys for all relations.
- Part (c) Give a different relational schema from the one
you got in part (b) that still captures the same information. Specify
(underline) keys for all relations.
Note: There is no single right answer to this question by any
means, although some answers may be better than others.
Problem 5
Consider the four relations on pages 188-189 of the Ullman/Widom
textbook. These relations contain a database for describing PC's,
laptops, and printers.
- Part (a) Reverse-engineer a corresponding E/R diagram
for this relational schema. That is, give an E/R diagram that would
be mapped to this set of relations.
- Part (b) Give an alternate relational design for this
database that uses only one relation.
- Part (c) Briefly discuss the advantages and
disadvantages of the original four-relation design versus the
one-relation design in part (b).