CS145 Assignment #1

Due Wednesday, October 7, 1998

Some Mechanics for Homeworks

For All Students

Assignments are due at 5:00 PM on Wednesday. You are allowed one lateness of up to 48 hours; use that privilege carefully.

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. Lambeth can give you one, but she often leaves around 4PM.

For SITN students

Assignments due on Wednesday must be timestamped by the Thursday morning courier to be considered on-time. 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 5PM Wednesday 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 Thursday morning.

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 schemas 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 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): (20 pts.) Describe 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.

Please put your PDA description at the front of your assignment. We shall look at these quickly to catch major problems before you have to hand in Assignment 2. Also, to make sure we can tell you of a problem with your design, include your email on this assignment.

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

(25 pts.) We would like to design a database to maintain information about hospital staff, including doctors and nurses, and patients at the hospital. The information we need includes:
- Staff, including their names, addresses and social-security numbers.
- Patients, including their names, addresses, and the name of their insurance company.
- Patients are each assigned to a ward (room).
- Those staff who are nurses are assigned to zero or more wards. Each ward has at least one nurse assigned.
- Those staff who are doctors are assigned to zero or more patients. Patients may or may not have a doctor assigned, and they may have more than one doctor. Patients in the same ward may have different doctors but will always have the same nurse(s).
(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.

(c)
Give a network-model design for this database. Note that ``isa'' is not a part of the network model as we have presented it. Do the best you can.

(d)
Give a hierarchical-model design for this database, again handling ``isa'' as best you can.

Note that there is no single right answer to this question, although some answers may be better than others.
(15 pts.) The following E/R diagram is an attempt to design a database in which a store keeps a permanent record of customers (identified by social-security numbers) and the items they buy (identified by a unique item ID assigned by the store).

However, there is a problem with this design, related to our ability to recover the history of, say, orders by a particular customer for a particular item.

(a)
Explain what the problem is and propose a solution.

(b)
Draw a revised E/R diagram that implements your solution.

(c)
Give an ODL description of your revised design.