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.

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.

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:

For each order there must be a corresponding customer.
For each order item there must be a corresponding order.
For each order item there must be a corresponding stock item.
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.

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.