Our goal for this assignment is to design and build the schema for a database that we will work with throughout the semester. We are not aiming for a perfect design, but rather, will explore the ways that our designs succeed or fail during the semester, and look at ways to improve those designs (in fact, the last assignment is taking what you have discovered through the semester and making a new model and schema to improve on your designs). Your designs will not be set in stone, but any changes you make to your model or schema will need to be documented in future assignments.

Instructions

Read through the customers description of their needs. If you have questions, please email me for clarification (in effect, I am the customer in this situation)

Start identifying key components that will become part of your model, keep a document of what those components are and why you choose them.

Using the customers description, document what features of those components you believe the customer wishes to track, and what kind of datatype those features will appear as. I recommend using a bulleted list for these features

Start to build the visual model using the modeling techniques described in the lecture slides (based on the optional textbook Mastering Data Modeling)

Review the customers description and compare it with your model to ensure you have addressed all of the customers needs. Start to translate each box in your model into SQL (usually a single box is a single relation in a database, using this modeling technique)

Modify the SQL to translate the links between the boxes into SQL (usually this is either adding columns to a relation, but can sometimes mean something more intensive)

Execute the SQL in a new database to ensure that it runs without errors - you can do this in the previous steps too, as long as what you submit is all of the SQL needed to build the entire set of relations

What to Turn In

Your document containing the components and features your derived from the customers description

Your model

A file with the SQL to build the relations in your database

Customers Description

I would like to build a website that allows visitors to explore the world of visual artworks throughout history. Visitors will also have the option to buy prints of these artworks as well. The main focus of the website is the artwork and its history. Each piece of artwork would have its own page on the site that would include a small to medium size image of the artwork, the title, and artist at a minimum. Additional information could also be included, like when the artwork was produced, what medium was used to create it, and what style it is produced in. Some of the artworks will also have more detailed stories with them. The artist listed on the artwork page should link to the artists homepage - listing important information about the artist, such as their full name, country of origin, artistic influences, birth year and death year (if applicable). The artist page should also show all of the artwork produced by that artist, and be searchable using the key features of the artwork itself. Some artists may have detailed historical stories associated with them, while others will only have minimal information. There obviously needs to be some management of the site, which includes the ability to have multiple art history editors, who can update and modify the history and

features of the artwork and artists. These changes should be logged in an easy to review way, in order to have multiple editors validate any changes. There also needs to be ability to monitor the reprint aspect of the website, including managing the quantity of each artworks reprints and their prices. These people would also need to be able to review orders, and mark them as fulfilled (along with the shipment tracking information) Speaking of the reprint orders, lets talk about how a visitor would order reprints. A visitor can log in to their account on the site, there would be basic information about the customer, like name, address and order history. The customer can also select artists or genres that they want to be notified of changes to. Each artwork page would have a place order and add to wishlist type form on it, to allow the customer to add reprints to their wishlist and shopping cart. The customer can have multiple artworks, and multiple reprints of an artwork in their cart, but only multiple artworks in their wishlist. Both the wishlist and cart are retained even if the customer logs out. The customer can checkout their cart where they can group various prints together to be shipped to different addresses (like 3 prints go to address #1, and 2 prints go to address #2, etc). The financial aspect of the order is handled by an outside service, so we will only track on the website whether the third party service successfully handled the order. The order will have tracking of each item, so that customer knows whether the item is in transit, or back ordered, or being gathered, etc.