CS145 - Introduction to Databases
Spring 2001, Prof. Widom

Assignment #1
Due Wednesday April 18



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.