Project Phase 1

The goal of the first phase is to build an extremely simple database application using SQL Server. You will be expected to create a database schema, build a database on the SQL Server, import some provided data, and create a simple web front end.

In each of the domains, you will get a description of what the database should store. The description of the domain will be given informally, and you will need to prepare a database schema that models the domain.

Your schema should have at the least four tables, but can be much larger. Some of the tables will have five columns or more. There can *hint* be foreign keys between columns in different tables. Each column must have a data-type and declared to be either a key or not a key. Choose informative column and table names.

Application Domains

More details for each application domain are below. Refer to the description page if you can't remember which domain you are in.

Data

UPDATE: [Sept 30, 1PM] For now, just populate your database with some example data that you make up. Each table should have at least 10 records, and try to demonstrate as many aspects of your schema as posible. Feel free to be creative with this and have some fun. (In phase 2 we will give you some book and music data that we will expect you to import, but we don't need alot of data right now).

Part A

Due: Friday October 14th 2005 @ 11:59PM

Tasks for Part A are:

You will be expected to turn in:

Part B

Due: Thursday October 20th 2005 @ 11:59PM

Tasks for Part B are:

You will be expected to turn in:

Note: For this turn in, and in the future, I will be checking the last modified time stamp on iisqlsrv to enforce the deadline. (I know, I don't like deadlines either.)

The Inventory Domain

An inventory in general is a catalog of all the products that a company has. In this case, the company sells books and CDs on its web site. You have been in business for quite some time and your retail business is fairly large. In the interest of efficiency, you have a number of warehouses spread out across the country. Each of these warehouses have different stock quantities of each of the products that are on sale.

Your schema must model the details of all products you have and their availability at the different warehouses, and the warehouses themselves. Here are some details about each of the data entities that your schema should model.

Books

Every book has:

Music

Every CD has:

Availability

Each of the books and CDs can be either in-stock or out-of-stock at the different warehouses. You will be definitely interested in the quantity in stock at your warehouses (for each product), so that you have an up-to-date availability information.

Warehouses

Every Warehouse has:

Additional Modeling:

In addition to the above aspects of the domain, you must add 4 or more attributes of your own. You are welcome to visit any of your favorite online stores for ideas on additional attributes to model in your application.

Part B Stuff

Working Example
Note: Yours can't possibly look exactly like this because you have to add four attributes of your own, and you will have different data.
Starter Project

Queries You Should Support

The interface to the system will be an ASP.NET web page (see the Resources page for details on how to do that). For each of the queries below, you should have a form that implements it. Do make sure that you have a default.aspx form and that all the other forms can be reached from it. Feel free to use either C# or VB.NET, though I expect C# will be a lot more pleasant.

The Billing and Invoice Domain

An invoice is a record of a business transaction, and hence the billing and invoice database has a record on all your transactions. It is thus probably the most crucial component of your business. There is typically a invoice for every order that a customer places. This invoice records all the information regarding the items being purchased as well as information about the customer.

Your schema must model the details of all the purchases that are made by customers, and also information about the customers themselves. Here are some of the data entities of interest.

Invoice

Each invoice has:

Items

Each item purchased has to be connected to the corresponding invoice number. The product that is being purchased has to be identified by its unique identifier. The item name is usually enlightening (book or CD title). Since the customer might order more than one copy of the book or CD, the quantity of the order is important, as well as the price and discount per unit at the time of purchase.

Shippers will put items into packages. Each item will be in a package (assume packages have integer ids). Sometimes if more than one of the same item are ordered, they will end up in more than one package.

Customer:

Each customer has: (A customer is either the person who bought an item or to whom an item is being shipped.)

Additional Modeling:

In addition to the above aspects of the domain, you must add 4 or more attributes of your own. You are welcome to visit any of your favorite online stores for ideas on additional attributes to model in your application.

Part B Stuff

Working Example
Note: Yours can't possibly look exactly like this because you have to add four attributes of your own, and you will have different data.
Starter Project

Queries You Should Support

The interface to the system will be an ASP.NET web page (see the Resources page for details on how to do that). For each of the queries below, you should have a form that implements it. Do make sure that you have a default.aspx form and that all the other forms can be reached from it. Feel free to use either C# or VB.NET, though I expect C# will be a lot more pleasant.

The Shipping Domain

This domain pertains to the shipping and delivery of the items that have been purchased by customers. Customers can choose from many shipping methods (e.g., Next Day, Standard Ground). The options might be priced differently and offered by different shipping agents (e.g., UPS, FedEx). The shipping and delivery database should also keep an accurate record of the status of the orders.

Your schema must model the various shipping options that are available, and enable tracking of the progress of any order.

Package

There has to be a record of each package that has been shipped. Each package has:

Shipping Vendors

There are shipping vendors like UPS, FedEX, or DHL. Each agent has name, a url, and a toll free number. Each vendor has local agents in different cities which each have local address and phone number. Each local agent may serve one or more warehouses. There is a scheduled daily pickup time associated with each warehouse that an agent serves. (Warehouses are part of the inventory domain, refer to them using an int id)

Shipping Methods

Different vendors provide different shipping options such as Standard, Next Day, or Overnight. Each of these options are priced differently. The unit of pricing can be by weight, by number of items, [FIXED: Oct 11 | by distance], or just fixed rate (or a combination!). The price per unit might also vary substantially. Each shipping method has a guaranteed time to delivery. In addition, of course, there may be restrictions with each method including maximum weight, distance, maximum items per package, etc. You can assume that the shipping options of a particular vendor are independent of the warehouse of origin. Methods can be considered unique to each vendor. If two vendors offer methods with equivalent properties then can be considered separate methods. (For example, UPS Ground and FedEx Ground may have the same properties but they can be considered separate methods.)

Additional Modeling:

In addition to the above aspects of the domain, you must add 4 or more attributes of your own. You are welcome to visit any of your favorite online stores for ideas on additional attributes to model in your application.

Part B Stuff

Working Example
Note: Yours can't possibly look exactly like this because you have to add four attributes of your own, and you will have different data.
Starter Project

Queries You Should Support

The interface to the system will be an ASP.NET web page (see the Resources page for details on how to do that). For each of the queries below, you should have a form that implements it. Do make sure that you have a default.aspx form and that all the other forms can be reached from it. Feel free to use either C# or VB.NET, though I expect C# will be a lot more pleasant.