CS145 - Introduction to Databases
Spring 2001, Prof. Widom
Assignment #1
Due Wednesday April 18
- Turn-in: Please turn in your written work during class, or
to the course administrator (Sarah Weden in Gates 419) between 9:00 AM
and 4:00 PM. Please put your name and preferred email address on
every page that you turn in - you'd be surprised how many times
assignments mysteriously come apart. Please remember that written
work will not be accepted electronically.
- Deadline: The on-time deadline for on-campus students
is 4:00 PM on the due date. If you don't turn in your assignment
during class then Sarah will timestamp it for you when you give it to
her. The on-time deadline for SITN students is a courier timestamp no
later than the day after the due date.
- Personal Database Application: There are three
important logistics regarding the Personal Database Application (PDA)
part of this assignment (Problem 6):
- Please turn in the PDA problem on a separate sheet of paper - it
will be graded separately. (However the same deadline applies for all
written work, and your entire assignment is late if any part is late.)
- Please make a copy of your PDA design for yourself as
you will need it for subsequent work. Every year the TAs end up
sifting through turned-in work for those students who forget to make a
copy. I recommend avoiding being one of those students.
- We're asking all students, including SITN students, to visit
the instructor or a TA during office hours the week of April 16-20 to
discuss their PDA. The purpose of this requirement is to uncover any
inherent problems with a PDA idea or design that could end up haunting
a student throughout the course. You will not receive credit for your
PDA unless you come to office hours at least once during the week to
have your PDA checked. The course staff are holding expanded
office hours the week of April 16-20 for this purpose. It doesn't
matter whether you visit office hours before or after you turn in your
PDA.
- Late policy for on-campus students: All written work
must be turned in by 4:00 PM on the Wednesday that it is due. Written
work turned in after the deadline but less than 24 hours late (i.e.,
by 4:00 PM on Thursday) will be accepted but penalized 20%. No
written work will be accepted more than 24 hours late.
- Late policy for SITN students: Written work due on
Wednesday must be timestamped by the Thursday courier at the latest.
No late written work is permitted for SITN students sending their work
by courier, and consequently no late penalties apply. Written work
not sent or timestamped by an SITN courier is subject to the late
policy for on-campus students specified above.
- For emergencies: Since emergencies do arise, each
student is allocated four "chits" for turning in work late with no
penalty. Please see the course
information page for a detailed description of chits.
THE ASSIGNMENT
Problem 1
(Warm-up exercise, answer was given in class)
Consider an Entity-Relationship diagram consisting of two entity
sets E1 and E2 connected by a binary relationship set R. Suppose that
R has a single attribute A. Under what conditions can attribute A be
moved to become an attribute of entity set E1 instead of an attribute
of relationship set R, without fundamentally altering the design?
Problem 2
Consider a ternary relationship set R among three entity sets E1, E2,
and E3. Let entity set E1 have one attribute A1 and let A1 be a key,
let E2 have one attribute A2 and let A2 be a key, and let E3 have one
attribute A3 and let A3 be a key. Relationship set R has no
attributes.
Draw the eight possible Entity-Relationship diagrams for
relationship set R and entity sets E1, E2, and E3 when multiplicities
are specified. (There are eight possibilities because each entity set
can either have or not have an arrow pointing to it.) For each E/R
diagram, specify a relation for R and underline in the relation a
minimal key - a key is minimal if attributes that are not needed in
the key are not included. The relations for the entity sets are
trivial and need not be specified.
Problem 3
One of your eight E/R diagrams from Problem 2 has arrowheads to
entity sets E1 and E2 and no arrowhead to E3. Specify a real-world
domain where this configuration accurately captures the situation.
That is, specify a real-world scenario that is best modeled by three
entity sets and a ternary one-one-many relationship set. Your entity
and relationship sets may have any number of attributes.
Problem 4
Construct an Entity-Relationship diagram for a fictional database
maintained by the Stanford athletics department to monitor the
academic progress of student-athletes. The data should include
information about students (e.g., ID#, name, major), athletic teams
(e.g., sport, season), university courses (e.g., department, course#),
and instructors (e.g., name, department). The data should include
which students play which sports, which students take which courses
and the grades they get, and which instructors teach which courses.
The database should contain both current and historical information in
terms of sports played and courses taken and taught. Please state any
assumptions you make about the real world in your design. For
example, you may or may not choose to assume that students play at
most one sport, or at most one sport at a time, or that instructors
teach at most one course at a time. In your E/R diagram 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.
There are many choices to make in designing an E/R diagram for this
database. There is no single right answer to this question by any
means, although some answers may be better than others.
Problem 5
Consider the E/R diagram you produced for Problem 4. 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. Note that since our translation method does not always
handle keys completely, in some cases the key you denote for a
relation may need to be based on the semantics of the expected data.
Problem 6 (Personal Database Application, Part 1)
Your CS145 programming project will be to build a substantial database
application for a real-world domain of your choosing. You will design
a schema on paper, then you will create an actual database using
Oracle. You will populate the database, write interactive queries and
modifications on the database, explore other aspects such as
constraints and triggers, create programs that manipulate the
database, and finally develop a simple Web front-end to your database
application.
Your first step is to identify the domain you would like to manage
with your database, and to construct an Entity-Relationship diagram
for the database. We suggest that you pick an application that you
will enjoy working with, since you'll be stuck with it for the whole
quarter! In previous years, students who built a database about
something they were interested in - a hobby, a favorite Web site,
material from another course, a research project, etc. - got the most
out of this part of CS145. It's especially nice if you pick an
application where you can populate your database using real, as
opposed to fabricated, data. As the project progresses, you'll end up
creating two actual databases, a small one (10's of
entities/relationships) and a large one (1000's or 10,000's of
entities/relationships). Many students find that it only makes sense
for their small database to contain realistic data, while the large
one is made up of synthetic (computer-generated fake) data. If you
have an application where you can get a large amount of real data, all
the better, but it's not necessary.
Try to pick an application with a schema that is relatively
substantial, but not too enormous. For example, your E/R design
should have in the range of five or so entity sets, and a similar
number of relationship sets. This is a ballpark figure only -
shooting for somewhere between 3 and 10 is fine - you'll sense if your
design is too simple or too complex. You should certainly include
different kinds of relationships (e.g., many-one, many-many) and
different kinds of data (strings, integers, etc.), but your
application need not necessarily require advanced features such as
weak entity sets, ""is-a"" relationships, or roles.
To get started on your Personal Database Application
(PDA for short), your assignment this week consists of two
parts:
(a) Write a short (approximately one paragraph) description
of the database application you propose to work with throughout the
course. Your description should be brief and relatively informal. If
there are any unique or particularly difficult aspects of your
proposed application, please point them out. Your description will be
graded on suitability and conciseness.
(b) Specify an E/R diagram for your proposed 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.
If you're having trouble thinking of an application, take a look
at any Web shopping site. They all have a similar theme: products,
customers, orders, shopping baskets, etc., and typically make for an
interesting and appropriately sized application. If you're still
having trouble, or if you're unsure whether your proposed application
is appropriate, please feel free to consult with one of the course
staff. In fact, to alleviate problems, this year we are requiring
all students to visit office hours to consult with a staff member on
your chosen application and E/R design. Please see the logistics section near the top of this page for
details. Coming up with a good design now will pay off greatly as the
project progresses.
- PLEASE TURN IN YOUR PDA PROBLEM ON A SEPARATE SHEET.
- MAKE SURE TO SAVE A COPY OF YOUR E/R DIAGRAM - YOU WILL NEED IT
FOR SUBSEQUENT PROJECT PARTS.