CS145 Assignment #2
Due Wednesday, Oct 13, 1999
Remember: the rules for deadlines and late homeworks are described in
The First Homework.
For this assignment, everyone needs an Oracle account.
If you filled out a Class Signup Sheet, we
shall send your leland account to Stuart Miyasato, who is the
administrator for the oracle installation at Stanford.
If you have not given us a signup sheet, please get it to Ms. Siroker
You may also email her your leland account, but please do not use
cs145-help for this purpose.
Step 2 of Your PDA (Personal Database Application)
- Please attach a copy of your E/R schema from the PDA part of
If you have modified your design because of TA feedback (or any other
reason), please hand in the modified design instead; the new design will
not be graded but will be compared with your relational design.
- (20 pts.)
Use the method for translating an E/R diagram to relations described in
class and the text to produce a set of relations from your E/R design.
Specify your relational schema using the notation of Section 3.1.2,
and please be sure to underline key attributes.
- (20 pts.) Are there any flaws in the relational database
schema you get from part (b)?
Are there opportunities to combine relations without introducing
If so, indicate which, and if not, tell us there are none.
Are there examples of non-BCNF relation schemas?
If so, do you want to decompose them?
For each opportunity to combine or decompose
relations, decide whether or not to do
so, and explain your reasoning briefly (e.g., tell us what queries you
expect will be typical for your database, and tell how the design you
pick facilitates them).
Is there anything
you still don't like about the schema (e.g., attribute names, relation
structure, duplicated information, etc.)? If so, modify the relational
schema to something you prefer. You will be working with this schema quite
a bit, so it's worth spending some time to make sure you're happy with
- Login to Oracle, using your Oracle account, which should be set up
early in October.
Use the Oracle Introductory Guide to see
how to login and change your password.
Try some simple commands, such as help or table (relation) creation.
No credit will be given,
but it is important that people try logging in as soon as there is a good
chance your login will be recognized. We'll need time to handle login problems
such as someone who thinks they are registered for the class and isn't.
Don't forget to save a copy of your PDA for reference as you
do Step 3 of the PDA.
- (40 pts.)
In this problem, we shall design a database using various models.
The data involves cities, the countries they are in, and
The relevant information:
- Each city is in a unique country. Data about cities include the
name of the city and its population.
Names of cities are unique within countries, but two cities in different
countries may have the same name (and possibly even the same
- Information about a country includes its name and
its head of state. You may assume no
two countries have the same name.
- Information about a consulate includes its name, which is unique
(e.g., ``consulate of the United States in Toronto'') and its street address
(which is unique within a city, but might not be unique among all
consulates in different cities).
- Cities and the country they are in
are related by a relationship In.
- Consulates and the city in which each is located
are related by a relationship
Countries and consulates are related by a representation
Each country represented by a consulate has a consul, who is represented
only by a name.
A subtle point is that occasionally, a consulate will represent more
that one country.
For example, country A may not have diplomatic relations with country B,
so A will ask country C to represent A's interests in country B at their
In that case, the consul for country A is actually a citizen of country
C, and that person will likely not be the same as the consul for country
C at the same consulate.
While you might assume that a country would have at most one consulate
in a given city, that is not always the case.
For example, the US retains two consulates in Jerusalem.
Draw an E/R diagram that represents, as best you can, the information
Do not forget to indicate keys, possible weak entity sets, and arrows on
relationships where appropriate.
Draw a network diagram for the same information.
Draw a representation for this information in the hierarchical model.
Design a relational database schema for this information.
Please indicate keys in your relation schemas.
If your schema has any redundancy, you should tell us about it, but you
need not remove redundancy.
- (20 pts.)
There are a number of different kinds of automobiles.
All automobiles have a model name and a manufacturer
(e.g., Honda =
manufacturer; Accord = model).
Most automobiles have gas engines; these have a number of cylinders
(noCyl, as an attribute).
A few automobiles have electric engines; these have a voltage.
Some automobiles are SUV's; these may be either gas or electric, and
they have an attribute capacity.
Draw an E/R diagram for the classes of automobiles.
You do not need to invent entity sets such as ``electric SUV''; the
entities that are electric SUV's will have a representative in both the
``electric'' entity set and the SUV entity set.
Convert your E/R diagram to relations, using the ``E/R'' style of
Show the schema of each relation, including keys, and also show how the
data would be placed in this database schema, if the cars in question
were a Honda Accord, 4 cyl., a Nissan Pathfinder SUV with a 6-cylinder
engine and a 100 cu. ft. capacity, a General Motors EV1 electric with a
12-volt engine, and a Range-Rover eRover model electric SUV with a
110-volt motor, a 90 cu. ft. capacity,
and a 200-mile extension cord (OK; the extension-cord bit
is a joke, but use the rest of the data).
Show the design using the ``object-oriented'' style of translation to
relations. Show how the data of part (b) would be stored in the
Show the design using a single relation with nulls.
Again, show the data of part (b) in this relation.