ICS 184 / EECS 116 Summer 2006
Assignment #4

Due date and time: Monday, August 21, 2:00pm (in person, at the beginning of lecture)


In the last assignment, we began our exploration of designing databases, by considering how real-world data requirements are expressed as constraints on the tables and columns we'd worked with previously, as views that provide abstractions for complex queries and aid in access control, and as stored procedures and functions that allow a database to perform tasks beyond the scope of those permitted by single SQL statements. In lecture, we also discussed triggers, as well, and how they fit into this framework of features in SQL that provide the ability to more thoroughly implement data requirements — and even some business logic — within a database.

However, database design is a much broader topic than this. How do you discover the data requirements of the underlying enterprise in the first place? Based on these requirements, how do you decide what tables should exist and what columns they should have? How can you understand the relationships between data stored in different tables? What other software systems need to be built around the database? These issues are central to the critical first phases of a database design, where the goal, in general, is to understand the requirements of the entire system and design a database that, when implemented, will adequately support that system.

This assignment explores the conceptual design phase, which you might refer to as data modeling. In this phase, the written requirements already exist, and a conceptual design of a database is built to meet those requirements. (Other courses, such as software engineering courses, or even courses explicitly about requirements engineering, emphasize the phase of the design during which the requirements are gathered and articulated in writing. To focus on the database-related aspects of such a project in this course, we'll skip the requirements gathering.) In our case, we'll use the Entity-Relationship model, as I'll ask you to draw an E-R diagram to represent your design.

The next assignment will focus on taking your conceptual design from this assignment and implementing it as Data Definition Language (DDL) statements in SQL. Therefore, it's important that you make a complete attempt at this assignment. (I should point out, though, that the next assignment will not be graded based on the correctness of your solution to this assignment; the next assignment will offer you the opportunity to fine-tune your design, if necessary.)

Design is, by definition, a somewhat open-ended task; lots of designs could potentially be considered "correct." You'll be asked to write a justification of the decisions you make, to aid us in understanding why you made the choices that you did.

The requirements

In this assignment, as well as the next assignment, we'll design the database that underlies an application that handles reservations and billing for a hypothetical chain of hotels. We'll aim to meet the following set of requirements. Note that each of these requirements is not written with the intention of necessarily implying a particular design decision. Don't assume, for example, that each bullet point indicates, say, an entity set or a relationship set; each bullet point instead indicates a set of related requirements, though each will likely be handled differently in your design.

As with any set of software requirements, there may be additional information that will help you make design decisions as you work through your design. Please ask these kinds of questions on the course newsgroup, so that, when I answer them, the answers will be available to everyone. (In the event that I receive email with a question whose answer will benefit everyone, I'll also post the question and my answer to it on the newsgroup, with personally identifying information removed.) I'll be glad to take other questions via email, as always.

The assignment

I'm asking you to do two things in this assignment. First, I'd like you to build a conceptual design of a database that meets the stated requirements, expressed as an E-R diagram in the format shown in lecture and in your textbook. Second, I'm requiring you to write a short document that briefly explains the decisions you made along the way. In that document, you should give a brief explanation about why you chose each entity set, each relationship set, and each multivalued, composite, or derived attribute. Note that I'm not asking you to write much — it shouldn't require much more than a page — but it should be clear what decisions you made and why you made them. We won't be grading you on the quality of your writing, per se, but it should be cogent enough for us to understand.

A few words about design

The distinction between conceptual design and implementation

It's important to realize that there is a distinction between a conceptual design and the implementation of that design as relational database tables. In particular, bear in mind that not all of the information present in the requirements above is relevant at this stage; the emphasis here is on defining what information needs to be stored, and how that information is interrelated. For example, it is stated above that credit card numbers are up to 16 digits long. This information does not appear anywhere in an E-R diagram, so it would not be relevant in this assignment; however, when you create a column in a table that stores a credit card number in the next assignment, you would then want to be sure you selected a data type that allowed a 16-digit number to be stored (and would have to decide between, say, a numeric type and a string type, considering the tradeoffs of making each choice).

There is no "right" answer here: the need for justification

As you work through your conceptual design, you will need to make a number of decisions. Some of them will be clear-cut; for example, there will certainly need to be an entity set called hotel. However, other decisions will not be so clear-cut. Are credit cards entities in their own right, or are they attributes in some other entity set? Either decision is justifiable, but in either case, you're trading off one advantage for another. I'm as interested in your exploration of the "why" here as anything; many decisions are "correct" here, but only if you understand the tradeoffs that you're making along the way. This is why I'm asking you to justify the decisions you've made in your design; I want to be sure that we're clear about your thinking.

Suggestions about drawing an E-R diagram

Because I'm considering it important that you use the same E-R notation that we discussed in lecture (and is also shown in the textbook), but because E-R notation is not standardized, it's difficult for us to settle on one tool that will suit everyone's needs. For this reason, I'm giving you the option of drawing your diagram on paper, with the strong caveat that we have to be able to read and understand it, so you'll need to draw it carefully. Alternatively, if you'd like to use some kind of tool, you're welcome to do it, though it will be your responsibility to learn how to use one that's appropriate to the task. (One possibility is Microsoft Visio, which you can obtain free of charge through the Microsoft Developer Network Academic Alliance program to which the Bren School of ICS subscribes. See a Lab Attendant in CS 364 for more details. Visio, by default, does not support the E-R notation we've learned in lecture, but you may well be able to figure out other ways to draw the necessary diagrams in Visio.)

Even if you use a tool to draw your diagram, you will be required to print out a copy of it and turn it in at the beginning of lecture on Tuesday, May 30. We will not be accepting electronic submissions of this assignment.


Because of the flexible arrangement that allows you to draw your diagrams by hand, or to use any tool you wish, we will only be accepting hand-submitted copies of this assignment, including both your diagram and your document explaning the design decisions you made. Please type your document for the sake of legibility (and, if you're like me, to save time). You are required to submit the assignment at the beginning of lecture on Wednesday, August 16. Do not submit your assignment at the ICS Distribution Center or via Checkmate.

Please be sure to include a cover page that prominently lists the following information: your name, your UCI student ID# (or UCI Access ID#), "ICS 184 / EECS 116 Summer 2006 Assignment #4", and your UCI email address (i.e., your @uci.edu address).

Be aware, also, that the next assignment will rely on your solution to this one, so it will be important for you to keep a copy of your own work, so that you can use it as you work on the next assignment. We will not grade the next assignment in terms of the correctness of this one, so long as your solution to this assignment represents a complete attempt to solve this problem (i.e., all of the information is included somewhere in your design).