Project 6.0

Mar 21, 2012

Objective

Project 6: Design and implement a database using a client-server DBMS (MySQL) and implement a simple program that demonstrates the use of an ORM.

Project 6.0: Design and implement the database in MySQL.

Scenario

The doorbell startles you! Entranced at the computer (a bad habit) you get up to see who it could be at 2:30AM. You open the door and see a FedEx man.

"(insert your name)?" he asks, raising an eybrow.

"Yeah, that's me," you say. He scans the package with a handheld device that resembles a sandwich, but is a very dark grey. The laser glows in the dim light and the sandwich-thing emits a loud beep as he shoves the package into your hands.

"Ok. Great. Have a nice day."The FedEx man quickly turns and leaves.

"Strange, no address or anything on this package," you think as you tear it open. "I don't care what it is, I haven't received a package from anyone in so long it could be worms and I'd -- oooh, a phone!" A strange, futuristic looking phone (strangely shaped like a banana, but painted gunmetal grey) lands in your hands. Suddenly, it rings. "Uhhh... heh- Hello?"

"Hello, (you). Do you know who this is?" asks the dark voice on the other end.

"Morpheus?" you say.

"Yes. I've been looking for you. I don't know if you're ready to see what I want to show you, but unfortunately you and I have run out of time. They're coming for you, and I don't know what they're going to do."

"Who's coming for me?"

"Look out the window and see for yourself."

"What, right now?"

"Yes. Now. Do it slowly. The back patio window."

Out across the back yard you see three clowns wearing black suits, black ties and dark sunglasses. "Oh, poop!"

"Yes."

"What the hell do they want from me?"

"I don't know, but if you don't want to find out I suggest you get out of there."

"How?"

"I can guide you but you must do exactly as I say."

"Ok."

"The house across the street from you is empty."

"But what if they -"

"Go, now!" shouts the voice, and you grab your laptop and run out the front door, scared for your life. With manic strength you burst right through the front door of the house across the street, leaving a human-shaped hole in the wall. "Stay here for just a moment. When I tell you, go to the end of the hall, to the room at the end of the hall. Stay as low as you can. Go, now. Good. Now, in the corner there is a desk."

"How do you know all this?" you ask, incredulously. How can this be happening?

"We don't have time. To your left there's a desk. Go to it. Open your laptop. You can use the ethernet jack on the wall at your feet. Now, design and implement a relational database."

"No way. No way. This is crazy."

"There are two ways out of this house. One is that database, the other is in their custody. You take a chance either way. I leave it to you."

"This is insane. Why is this happening to me? What did I do? I'm nobody. I didn't do anything. I'm gonna die. Poop. I can't do this."

Requirements

This project will exercise your ability to work with MySQL, implementing a database schema given an ERD. In addition, you will be programming against this database using an ORM, rather than writing your SQL by hand. The first step of this project is to design and implement your database. The database must:

Implement a properly designed schema given the ERD below.
Use indexes on "appropriate" columns.
Implement the constraints according to the schema description that follows the ERD below. Use at least one each of the following constraints: NOT NULL, DEFAULT, UNIQUE, and CHECK.

Prepare the Project

Create a subdirectory of cs403 called project06. This is your dedicated working directory for all 6.x project assignments.

Create a README file inside your project06 directory.

In your README file, describe what Project 6 is. For example:

Mos Def, CSCI403
Project 6

This is my working directory for creating a database-backed application using an ORM.
This application will ________________ ...

Domain

Our domain for this project will be zoos, and we will be collaboratively narrowing down our ER schema in class together. Our first iteration schema result is below.

This version of our ER schema adheres to the following description and constraints.

A zoo manages many animal habitats.
Zoos in the system have a name. Names are not necessarily unique and can be as long as 128 characters, but probably no longer.
Habitats are simple "living areas" occupied by animals and managed by zoos. They have a name (eg. "Polar Vestibule 3"), a description ("A freezing environment just perfect for polar llamas") and a location ("West wing"). Names and descriptions can be pretty long, but probably not more than 255 characters. We're not certain about location. (What would you recommend?)
All habitats are managed by one specific zoo. They can be occupied by any number of animals up to the maxiumum capacity of the habitat. Each habitat would have a minimum capacity of one animal.
By law, no habitat may have a higher capacity than 50 animals (because at that point all hell breaks loose and liability insurance just doesn't cover wild animal frenzies).
Animals have a name, description and cuteness rating. They occupy habitats, and no animal may occupy more than one particular habitat.
An animal's cuteness rating is a discrete numeric score, standardized by ZACARS (The Zoo Animal Cuteness Association Rating Standard), and is generally a value between 1 and 10, inclusively.
Animals eat feed. An animal might eat a few different kinds of feeds. And a particular kind of feed (eg, chocolate mini-doughnuts) might be eaten by multiple animals.
Feed have a name and a deliciousness quality that is either yes or no. This deliciousness is according to human standards, not animal standards, and is always a yes or no answer (no "Hmmmm... might be delicious with some crème fraiche and fava beans").
This database will be used by an application that allows users to create lists of their favorite animals. More application details will be provided soon.
Every user has credentials, which consist of a unique username and a password. Usernames and passwords must be between 9 and 16 characters in length and can never be blank.

Design & Implement the Database

List the entities and relationships you are modeling in your README file.

Next, use paper to draw your intended database schema. Do this before your SQL DDL statements! It will help you make sure your design is sound before implementing the actual schema.

Once you're satisfied with the design, implement the schema with SQL as a MySQL database called "zoos." Use lower case, plural words for your table names.

Grading Criteria (300 pts)

Using mysqldump, submit a .dump of your database to blackboard. Do not attach a file -- simple paste the output of mysqldump into your bboard submission.