Due date and time: Monday, August 28, 2:00pm (part of it may need to be turned in, in person, at the beginning of lecture)

---

Introduction

In the last assignment, we continued our exploration of database design by taking a set of requirements for a billing and reservation database for a chain of hotels and turning it into a conceptual design, expressed as an E-R diagram. When using the E-R model to design a database, the emphasis is on specifying what information will be stored in the database and how that information is interrelated. Implementation issues, such as which information is stored in which tables or what SQL-style constraints are necessary, are deferred until it is time to implement the design. It's more important in a conceptual design to decide what information is needed and what the "shape" of the information is. For that reason, the last assignment ignored implementation altogether, to encourage you to concentrate purely on the design issues.

One of the strengths of the E-R model, when it's used to design a database that will be implemented as a relational database, is that there is a relatively straightforward conversion process between the conceptual design (the E-R diagram) and the implementation of that model as relational database tables. In lecture, we discussed an algorithm for performing this conversion. It's useful to know this algorithm, since it reveals a number of techniques that aid in implementing relational databases. However, since the conversion can be done automatically, you could imagine, in a "real world" context, having a tool that would perform this conversion instead.

This assignment asks you to use the conversion algorithm presented in lecture and the textbook to convert your E-R design from Assignment #4 to an SQL script that creates all of the necessary objects (e.g., tables, constraints, and so on) within a relational database.

---

What to build

In Assignment #4, you constructed an E-R diagram that expressed a conceptual design for a database that handles billing and reservation information for a hotel. In this assignment, I'd like to you to write an SQL script that creates an implementation of your design. Your script should include all of the necessary CREATE TABLE statements, with each table including all relevant columns and both primary key and foreign key constraints. Since you did not consider the effect of cascading deletions or updates in your design, and since these requirements were not specified, you are not required to include ON DELETE or ON UPDATE clauses in your declarations of foreign key constraints, nor are you required to include other kinds of constraints, such as CHECK constraints. Note, also, that some aspects of an E-R design, such as total participation of two entities in a relationship, are not easily verifiable with SQL constraints; for example, some require using assertions, which are not supported in any SQL-based database system. You are not required to handle aspects of the design that cannot be expressed in SQL.

As with previous SQL scripts that you've written in this course, it is required that the script can be executed using MySQL 5.0.

---

How we'll gauge the "correctness" of your implementation

Whenever an assignment builds on a previous one, there is always the danger of doubly penalizing someone who didn't complete the first assignment successfully, by penalizing them again on the second one. This outcome is never entirely avoidable — if you didn't do Assignment #4, you're in a certain amount of trouble here — but in order to minimize the effect of building this assignment on top of the previous one, we'll grade this assignment in terms of your solution to Assignment #4. So long as your solution to the previous assignment constituted a complete attempt to solve the problem, where "complete" is defined as all of the information being present and separated somehow into multiple entity and relationship sets, we'll focus on whether your solution to this assignment is a valid translation of your design to SQL Data Definition Language (DDL) statements.

In short, we're interested in how well you understand how to convert an E-R diagram to a set of relational database tables; your understanding of E-R design was measured in the previous assignment.

---

What if I discover a design flaw along the way?

Some of you may discover, as you build your implementation, that there is a flaw in your design that prevents you from being able to implement it. In these cases, you're permitted to make changes to your original design and resubmit it to us. Additionally, I'd like you to write a short document explaining briefly what changes you made and why you found them to be necessary.

You have the option of resubmitting your design (and accompanying explanation) on paper and turning it in on paper on Monday, August 28 at the beginning of lecture. You may also submit it electronically, though you are required to submit it in a graphics-only format such as JPG or PNG, or as a PDF document, regardless of what tool you used to build it, so that we'll be able to open it and read it even if we don't have your tool.

If you didn't find it necessary to make changes to your design, all you need to submit is an SQL script. In that case, please mention in a comment that you made no changes to your original design.

---

Deliverables

Submit a single SQL script that (re-)creates your database implementation to Checkmate. Remember to start your script by dropping the database if it exists, as we've done in previous assignments, so that your script could be executed successfully even if the database already exists.

If you made changes to your design along the way, you may either submit your new design electronically to Checkmate, or you can bring it with you to lecture on Monday, August 28 and submit it at the beginning of class.

Follow this link for a discussion of how to submit your assignment. Be aware that I'll be holding you to all of the rules specified in that document, including the one that says that you're responsible for submitting the version of the assignment that you want graded. We won't regrade your assignment simply because you submitted the wrong version by accident.