CS145 Assignment #1

Due Wednesday, October 8, 1997

Reminder for on-campus students: Assignments are due at 5:00 PM on Wednesday. You are allowed one lateness of up to 48 hours; use that privilege carefully. Assignments should be turned in during class or at the course secretary's office: Gates Building room 495.

Reminder for SITN students: Assignments due on Wednesday must be timestamped by the Thursday morning courier to be considered on-time. Like the on-campus students, you have one free 48-hour exception.

Step 1 of Your PDA (Personal Database Application)

Your first step is to identify the domain you would like to manage with your database, and to construct ODL and entity-relationship schema designs 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. Note that this is a ballpark figure only! 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 subclassing in ODL, or weak entity sets or roles in E/R.

(a)

(10 pts.) 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 only on suitability and conciseness.

(b)

(20 pts.) Specify an ODL schema for your proposed database. As always, don't forget to include keys and inverse relationships.

(c)

(20 pts.) Specify an entity-relationship diagram for your proposed database. As always, don't forget to underline key attributes and include arrowheads indicating the arity of relationships.

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.

Problem Set

1. (20 pts.) Consider the following database schema, which we have expressed both in ODL and equivalent E/R notations: interface Student { attribute string name; attribute int studentID; relationship Set<Class> isTaking inverse Class::hasStudents; relationship Class TAfor inverse Class::hasTA; } interface Class { attribute string name; attribute string professor; relationship Set<Student> hasStudents inverse Student::isTaking; relationship Set<Student> hasTA inverse Student::TAfor; }

   To the above database design for students and classes, we want to make the following five changes:

   i.

   A student can TA more than one class.

   ii.

   A class can have only one TA.

   iii.

   The names of people have three components: first, middle and last.

   iv.

   Classes can be taught by any number of professors and can have more than one name.

   v.

   A professor can belong to one or more departments.

   (a)

   Modify the ODL model above for each of the five changes listed.

   (b)

   Modify the E/R diagram above for each of the five changes listed.

2. (15 pts.) Consider the following database schema, which we have expressed both in ODL and equivalent E/R notations: interface Student { attribute string name; attribute int studentID; relationship Set<Student> TAfor inverse Student::hasTA; relationship Set<Student> hasTA inverse Student::TAfor; }

   To the above "student" database design, we want to add the following special types of students:

   • Ph.D. students
   • M.S. students
   • B.S. students
   • co-terms, who are getting their M.S. and B.S. at the same time.
   Only graduate students (student who are getting their Ph.D. or M.S.) can be TA's.

   (a)

   Modify the ODL model above to reflect these additions.

   (b)

   Modify the E/R diagram above to reflect these additions.

3. (15 pts.) We would like to design a database to maintain information about books. The information we need includes:
   • Authors, including their names and addresses.
   • Publishers, including their names and addresses.
   • Books, including their author(s), copyright, and title.
   • Editions, including the book each edition is of, the publisher, the publication date, the list price, and the ISBN number.
   • Bookstores, including their names, addresses, and the books they stock. Also, each book stocked by a bookstore must have a price.

   (a)

   Specify an ODL schema for this database. In addition to class definitions with attributes and relationships, don't forget to include keys and inverse relationships.

   (b)

   Specify an entity-relationship diagram for this database. Don't forget to underline key attributes and include arrowheads indicating the multiplicity of relationships.

   Note that there is no single right answer to this question, although some answers may be better than others.