Page 3 of 22 - Chapter 14

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:

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

Order Item Table

Stock Table

Artist table

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:

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:

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:

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