Building an E-Commerce Shop Front

Our first task is to create a new database. Use SQL Server's Enterprise Manager to create a new database and name it MusicMad. To do this open up the console root until the name of your server is displayed. Then open it up to display the current databases.

Right click on Databases in the left panel and select New Database. Enter the name MusicMad into the name box and click OK. The default settings for the database are fine for our purposes.

Next we need to create a new user called MMCustomer with the password Madforit and give them access to the MusicMad database. In the Enterprise Manager main console area open up the Security root so you can see Logins and right click it. Select New Login. Enter MMCustomer for the name, check the SQL Server authentication radio button and enter Madforit in the password box. Use the drop down combo box to change the default database to MusicMad.

Then click the Database Access tab at the top of the dialog and check the MusicMad box to give the user access to our database. Click OK and you'll be asked to confirm the password. Re-enter it and click OK to close the dialog box.

Creating the Tables

The database consists of six tables as shown in the diagram below:

When a customer places a new order its details go into the Orders table and the customer details such as name and address in the Customer table. The Customer table's primary key field CustId is also in Orders as a foreign key and links the two together.Meanwhile OrderItems stores the items making up an order and is linked to the Orders table using the OrderId.

Normalizing the data by splitting into separate tables helps keep the database size to a minimum by avoiding data duplication. For a real world system you might want to take it further and have customers create a login before they can order goods and store the customer details just once in the Customer table. If they shop again they can log in and won't have to re-enter their details.

Information about individual goods is stored in the Stock table, which also has links to Artist and Category. Again this helps reduce data duplication, especially as we start inputting many albums in stock by the same artist. With this arrangement only the integer ArtistId is duplicated in the Stock table. It also allows extra details about an artist to be included easily and efficiently. The Category table stores all the different music genres of modern music, for example rock, pop, dance, acid house, etc. Again we could add extra information about the genre if we want.

The steps to follow to create each of the six tables are identical:

• Open up the Console Root tree in Enterprise Manager so that you can see the MusicMad database.
• Open up MusicMad so that Tables is visible.
• Right click on Tables, select New Table, and a dialog box will appear for you to enter the name you want to give the new table.
• Having done that click OK then create the fields for each table as shown below.
• A key icon next to a field name indicates a primary key field. To set a field as a primary key, right click anywhere on it and select Set Primary Key. In the case of setting more than one field as primary key, select both first and then right click.
• After each table has been created close the design screen, making sure you have saved first.

Note: CustId in the Customer table, OrderId in the Orders table and ItemId in the Stock table are Identity fields. This means that they are automatically allocated a new value when a new row is inserted. Check the Identity check box to make them identity fields.

Customer Table

Orders Table

OrderItem Table

Stock Table

Category Table

Artist table

Referential Integrity

To ensure data integrity we need to put constraints on what can be added to and deleted from the table. For example it would not make sense to have an ArtistId in the Stock table that does not exist in the Artist table.

The easiest and quickest way to add these constraints is from the database diagram view. The first step is to create a new database diagram:

• From Enterprise Manager open up the MusicMad database so that you can see the Diagrams branch.
• Right click Diagrams and select New Database Diagram.
• If you're using the full version of SQL Server then the database diagram wizard dialog will pop-up. Use it to add all the tables to the diagram. (Editor's Note: If the wizard doesn't work when you use it, close and restart Enterprise Manager and then repeat the steps above. That seems to fix it).
• If you're using a developer version of SQL Server then you need to drag the tables from the Add Table dialog.

With the tables on the diagram, you can arrange them neatly by drag-and-dropping them to the required position. For example:

Now we can add the relationships between tables. The rules to enforce are:

1. For each order there must be a corresponding customer.
2. For each order item there must be a corresponding order.
3. For each order item there must be a corresponding stock item.
4. For each stock item there must be an artist and a category.

For the first constraint you need to left click and hold down the mouse on the gray box next to CustId in the Orders table then, keeping the mouse button held down, drag over to CustId in the Customer table then let go. You should see a Create Relationship dialog box (like the one below) pop-up with details of the fields and tables involved in the relationship. Just click OK and the relationship will be created. Note that the 'bars' between tables will not necessarily point to the two fields but you can move them so they do.

Follow the steps above and do the same for the following tables:

• Click and drag from OrderItem, OrderId field to Orders, OrderId field.
• Click and drag from OrderItem, ItemId field to Stock, ItemId field.
• Click and drag from Stock, ArtistId field to Artist, ArtistId field.
• Click and drag from Stock, CatId field to Category, CatId field.

Make sure you save the diagram then close the diagram view.

Sample Data

Now would be a good time to add sample data to the database. You'll need to add data to the Category, Artist and Stock tables. The constraints we added above should prevent you adding an item to the Stock table and giving it an ArtistId which does not exist in the Artist table and similarly for the CatId and the Category table.

Shown below is some sample data to get you started. With SQL Server 7.0 comes the ability to type your data directly into the table. For example, to type data into the Category table:

• From Enterprise Manager open up the MusicMad database so that you can see the Tables branch.
• Right click on the Category table and select Open Table and then Return all Rows.
• Click into a box and start typing.
• To save your entries, simply close the table view.

We've included some example data here to get you started, but please do add some more of your own favorite records. A music shop with only five records for sale is a poor one indeed.

Sample Data for the Category Table

Sample Data for the Artist Table

Sample Data for the Stock Table

There's no need to add sample data to the Customer, Orders or OrderItems tables as we'll do that using the web site.