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

---

Introduction

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.

• There are many hotels in the chain. Each hotel has a name, a street address (which is made up of a street number, street name, city, state, and postal code), a home page URL (Web address), and a primary phone number.
• Each hotel consists of a set of rooms arranged on various floors. Each room has an identifier which is unique within that hotel. Most of the time, rooms are numbered (e.g. 690), but they may be given a name (e.g. Presidential Suite) instead, so long as the name or number is unique within the hotel. Floors are numbered, and it's necessary, for each room, to know what floor it's on, since some customers prefer rooms on lower floors or higher floors. For simplicity, assume that each room is on only one floor. (Some real hotels have suites that span multiple floors.)
• For each room, it's also necessary to keep track of how many beds it has, as well as whether smoking is allowed in the room. This information is used to help match guests to rooms with desired characteristics.
• When a guest plans to stay at a hotel in the future, he or she makes a room reservation at the desired hotel. Each reservation indicates information about the guest, the desired arrival and departure dates, as well as preferences that aid in selecting the right kind of room for that guest: whether the room should be smoking or non-smoking, whether the room should have one beds or two, and whether the room should be on a high floor or a low floor. These room preferences are optional, and are not included with every reservation; some guests are willing to take any available room, while some only care about some preferences but not others.
• Also required with each reservation is information about a credit card that's used to secure the reservation; credit cards are indicated by a credit card number (which is a sequence of up to 16 digits) and an expiration date (a month and a year, such as "January 2007").
• At any given time, a guest may have multiple reservations; reservation information is removed from the database after the guest's reservation is used to put them into an actual room, or when the guest cancels the reservation prematurely.
• The database tracks historical information about every guest's stay in any room in any hotel. At minimum, it's necessary to know what day the stay began, what day it ended, what room it was, what hotel it was, and who the guest was.
• Information about each guest of each hotel is tracked historically. For each guest who has ever reserved or stayed in a room, the database must store the guest's first, middle, and last names, street address, email address, and three phone numbers (home, work, cell). Email addresses and the phone numbers are optional, while the other information is required.
• An invoice is generated during a guest's stay at the hotel, detailing the individual charges accrued by the guest. These charges include not only the regular room rate, but also applicable taxes, as well as charges at the hotel's restaurants, bars, spas, shops, and so on. An invoice is displayed — either in printed or Web-based form — as a sequence of line items, with each line item consisting of a description and an amount, such as "Hotel Cafe — $29.75". Note that the database does not keep track of, say, the costs of items on the restaurant's menu or the cost of renting each room at various times throughout the year; it is assumed that another software system provides this information to our database, since our system only handles reservations and billing.
• When a guest pays his or her bill — or a portion of his or her bill — a line item is added to the invoice that indicates how much was paid, and in what form the payment was made (e.g. "Visa — $-500.00", in the case of a $500 payment made using a Visa credit card).
• At the bottom of each invoice is a total balance, which is the sum of the amounts in each of the line items, including both charges and payments. An invoice is considered paid if the amount is $0.00.

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.