CS145 Assignment #1
Due Tuesday, October 9, 2001, 9:30AM
Some Mechanics for Homeworks
For All Students
Assignments are due in class
on Tuesday. You are allowed one lateness of up to 48
hours; use that privilege carefully.
Remember, you must hand in your assignment by the beginning of class;
assignments handed in at the end of class will be considered late.
For On-Campus Students
Assignments should be turned in during class or at
the course secretary's office: Gates Building room 419.
If you want a receipt for your work, Ms. Weden can give you one.
For SITN students
Assignments due on Tuesday
must be timestamped by the Wednesday-morning courier to be considered
Like all students, you have one free 48-hour exception.
You should not have to worry about receipts for work, because SITN logs
everything it receives.
Remember: the due time/day of 9:30AM Tuesday applies to you too, although
we often cannot verify the exact time you delivered your work to your
local pickup point.
However, please do not imagine, say, that it is OK to hand-deliver the
work Wednesday morning.
Special Directions for Assignment 1 Only
In general, we shall expect your work to be handed in as one set of
pages, fastened together, possibly with some electonic submission as
However, because we want to look at your proposed projects quickly, we
ask that, for this assignment only, you hand in three separate sets of
pages, each with your name at the front.
These three sections can be clipped together if you wish.
The signed statement about rules for sharing ideas in CS145.
Step 1 of your Personal Database Application.
The three written problems at the end of this assignment.
Certification Regarding Honor-Code Policy
I feel bad that I have to take this step, but I have noticed an
increasing number of students who, when confronted in an apparent case of
plagiarism, tell me that they had never read the course policy on the
Therefore, I request that everyone download or paste
the statement from
The Course Introduction reproduced
below, and sign it.
The basic presumption is that the work you do is your own.
Occasionally, especially when working problem sets or
writing programs (but never on exams!), it may be
necessary to ask someone for help.
You are permitted to do so, provided you meet the following two
Any other assistance by another person constitutes a violation
of the honor code and will be treated as such.
You acknowledge the help on the work you hand in.
You understand the work that you hand in, so that you could explain
the reasoning behind the parts of the work done for you by another.
If you have any questions about what this policy means, please discuss
the matter with the instructor now.
Step 1 of Your PDA (Personal Database Application)
As the course progresses you will be building a substantial
database application for a real-world scenario of your choosing. You
will design a relational
schema for the database, and you will create an actual
database using a relational database management system. You will
populate the database with sample data, write interactive queries and
modifications on the database, and develop user-friendly tools for
manipulating the database.
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 data. 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, material from
another course, a research project, etc.--got the most out of this
part of CS145.
Try to pick an application that is relatively substantial, but not too
enormous. For example, when expressed in the entity-relationship
model, you might want your design to have in the range of five or so
entity sets, and a similar number of relationships.
In the past, we have accepted as reasonable those designs where the
total number of entity sets plus relationships was in the 8-14 range,
but have questioned others.
Be aware, however, that entity sets or relationships that should be
represented by attributes instead (a matter we'll discuss in class) do
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 is not
required to use advanced features, such as subclassing, multiway
weak entity sets, if they are not appropriate for your application.
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
only on suitability and conciseness.
Specify an entity-relationship diagram for your proposed
database. As always, don't forget to underline key attributes and
include arrowheads and rounded arrows
indicating the multiplicity of relationships.
If there are weak entity sets, indicate them by double lines, as
described in class.
Preliminary Review of Projects
In order to catch problems at an early stage, everyone is
required to have their design reviewed by the instructor or one
of the TA's. We shall be announcing additional office hours during the
few days before the project is due, so that everyone will have a chance
for an individual discussion with a member of the course staff.
We shall try to catch major problems in your proposed designs
within three days of your submitting it, so your Step 2 of the PDA will
not be adversely affected.
To make sure we can tell you of a problem with your design,
please include your email address on this part of the assignment.
Also, don't forget to save a copy of your PDA for reference as you do
Step 2 of the PDA.
If you are having trouble thinking of an application, or if you are
unsure whether your proposed application is appropriate, please feel
free to consult with one of the course staff.
In this exercise, you will design a database for used cars, as might be
used by an on-line trading site.
The database will store automobiles for sale.
Each automobile has a VIN (vehicle identification number), a model
(e.g., Camero), a make (e.g., Chevrolet), a year (e.g., 1996),
a color (e.g., red),
a mileage (e.g., 50,000 miles), and a body
style (e.g., coupe).
Each automobile has a seller, which may be either a dealer or an
For dealers, the database has a name, address, and phone number.
For individuals, only a phone number and email address are recorded.
In addition, articles about cars are maintained.
Each article is about one model, make, and year of automobile, and each article
has an author.
The same author may write several articles about one make and model for
The database should connect cars with the articles about the make,
model, and year of that car, although it need not make a direct
Give an E/R design for this database.
Briefly explain your reasoning, including the intuitive meaning of any
relationships and any entity sets you use.
Do not forget to indicate keys, many-one relationships, and weak entity
sets in the appropriate ways.
Begin with the following two entity sets:
States has key attribute name, and other attributes that
need not concern us.
Cities is a weak entity set related by a many-one relationship
In to States.
A city has a name, which is unique within a state, but not unique among
all states (e.g., there are 24 states with a city named Springfield, but
no state has two ``Springfields'').
Add the following information, and draw the entire E/R diagram:
There are bus companies.
Each company operates within a single state, and no two bus companies in
a single state can have the same name (although two different companies
in different states could have the same name).
Bus companies have addresses and phone numbers, in addition to a name.
There are bus routes.
Each route has a number and operates within a single city.
Route numbers are unique within a bus company, but two companies can
have routes with the same number in the same city.
It is not always possible to replace a three-way relationship by two (or
even three) two-way (binary) relationships.
Suppose there is a three-way relationship among entity sets A, B,
Suppose also that there is some relationship set S representing the
``current value'' of this relationship.
If we replace this relationship by relationships A-B, A-C, and
B-C, we form the relationship sets for these new relationships by
``projecting'' each triple (a,b,c) in S onto its A and B
components for A-B, and similarly for the other two binary relationships.
Thus, for instance, (a,b) beecomes a pair in the relationship set
Give an example of two relationship sets S1 and S2 (for the
three-way relationship) that are
different, and yet their projections onto the three binary relationship
sets are each the same.
Note that this situation demonstrates that the three-way relationship
contains more information than the three two-way relationships.
If you cannot solve this problem, then for partial credit
try showing the same result, but
assume that we use only two of the three binary relationships, e.g.,
S1 and S2 have the same projections for A-B and B-C only.