CS145 Assignment #2

Due Wednesday, Oct 13, 1999

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 immediately. 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)

(a)

Please attach a copy of your E/R schema from the PDA part of Assignment #1. 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.

(b)

(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.

(c)

(20 pts.) Are there any flaws in the relational database schema you get from part (b)? Are there opportunities to combine relations without introducing redundancy? 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 it.

(d)

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.

Problem Set

1. (40 pts.) In this problem, we shall design a database using various models. The data involves cities, the countries they are in, and ``consulates.'' 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 population).

   • 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 Location.

   • Countries and consulates are related by a representation Represents. 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 own consultates. 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.

   (a)

   Draw an E/R diagram that represents, as best you can, the information described above. Do not forget to indicate keys, possible weak entity sets, and arrows on relationships where appropriate.

   (b)

   Draw a network diagram for the same information.

   (c)

   Draw a representation for this information in the hierarchical model.

   (d)

   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.

2. (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.

   (a)

   Draw an E/R diagram for the classes of automobiles. Hint: 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.

   (b)

   Convert your E/R diagram to relations, using the ``E/R'' style of translation. 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).

   (c)

   Show the design using the ``object-oriented'' style of translation to relations. Show how the data of part (b) would be stored in the relations.

   (d)

   Show the design using a single relation with nulls. Again, show the data of part (b) in this relation.