Now that you have a database, Music, you can add tables to it. If you recall from the previous session, the Music database contains several tables including t_bands, t_band_members, t_albums, and so on. Figure 4-3 shows the schema for the Music database.
Figure 4-3 Schema for the Music database
You are not going to create every table in the Music database, but hopefully, based on the tables you do create, you will be able to build the remaining tables. So, go create the t_bandstable:
1. In Enterprise Manager, right-click on the Music database node and select New➪ Table.
2. You see the Choose Name dialog box as shown in Figure 4-4. Enter t_bandsin the “Enter a name for the table:” textbox and click OK. The table design grid is now ready for you to enter column information.
3. In the design grid, enter band_idin the Column Name field of the first row as shown in Figure 4-5. In the Datatype column, select int to signify that the band_idfield will contain integer type data. On the same row, deselect the Allow Nulls checkbox and select the Identity checkbox. Click the Set Primary Key button (it looks like a key) on the SQL Server toolbar to make the band_idcolumn the primary key for the t_bandstable.
4. Create the band_title, music_type_id, and record_company_idcolumns, using Figure 4-5 as a guide.
5. Right-click the t_bandstable design grid as shown in Figure 4-5. You see the Properties dialog box.
Figure 4-5 Creating table columns
6. Select the Indexes/Keys tab and click the New button to create a new index on the band_titlecolumn.
7. Select band_title from the Column name drop-down box and enter IX_band_title in the Index name text box.
8. Select the Create UNIQUE checkbox and the Index option button, and click the Close button (as shown in Figure 4-6).
9. Save and close the t_bandsdesign grid.
Figure 4-6 Creating a unique constraint
To create the t_bandstable using T-SQL, execute the following commands in Query Analyzer:
USE Music GO
CREATE TABLE [dbo].[t_bands] (
[band_id] [int] IDENTITY (1, 1) NOT NULL , [band_title] [varchar] (100) NOT NULL , [music_type_id] [int] NOT NULL ,
[record_company_id] [int] NOT NULL ) ON [PRIMARY]
GO
ALTER TABLE [dbo].[t_bands] WITH NOCHECK ADD
CONSTRAINT [PK_t_bands] PRIMARY KEY NONCLUSTERED (
[band_id] ) ON [PRIMARY] ,
CONSTRAINT [IX_bands_title] UNIQUE NONCLUSTERED (
[band_title] ) ON [PRIMARY] GO
The second command, or the first command after USE Music,creates the t_bands table using the CREATE TABLEstatement. The third command, ALTER TABLE, creates two constraints on the t_bandstable. The first constraint, named PK_t_bands, is placed on theband_idfield. The PK_t_bandsconstraint is the primary key for the t_bandstable. The second constraint, named IX_bands_title, is placed on the band_title column and ensures that the band title is unique.
Now create t_albumsas shown in Figure 4-7.
Figure 4-7 t_albums table
Next you need to create a few constraints on the t_albumstable by following these steps:
1. Open the Properties dialog box for the t_albumstable and select the Indexes/ Keys tab.
2. Create a constraint named IX_band_albumsbased on two columns, album_title and band_id. Make this constraint unique by selecting the “Create UNIQUE” check- box. This constraint ensures that a band doesn’t have albums duplicated in the table. In this example, of course, you could assume that a band will never release two albums with the same name. At this point, you should start to realize that constraints are basically used to enforce business rules on our tables.
3. Close the Properties dialog box by selecting the “Close” button.
4. Expand the Music database node so you can see a complete listing of all database objects (that is, Diagrams, Tables, View, Stored Procedures, and so on).
5. Right-click the Diagrams node and select “New Database Diagram.”
6. Work your way through the Create Database Diagram Wizard. Make sure that you add the t_albumsand t_bandstables to the diagram. It is through this database diagram that you are going to create a foreign key. Specifically, you are going to create a foreign key to the t_bandstable to ensure that all entries in the band_id column of the t_albumstable have a corresponding band_idin the t_bandstable. (This is simply a business rule. You can’t have an album pop out of thin air. It has to be recorded by a band.)
7. Once the diagram has been created and the two tables mentioned in step 6 are on the diagram, drag the band_identry in the t_albumstable and drop it on t_bands.
At this point, you see the Create Relationshipdialog box shown in Figure 4-8. On the Create Relationshipdialog box, ensure that the primary key table is t_bandsand the primary key column is band_id. Also ensure that the foreign key table is t_albumsand the foreign key column is band_id. Click OK.
That’s it. You have created the t_albumstable using Enterprise Manager. Listing 4-1 shows the T-SQL script you could execute to create the t_albumstable and its associated constraints.
Figure 4-8 Creating a relation between t_bands and t_albums Listing 4-1 An example of a T-SQL script
CREATE TABLE [dbo].[t_albums] (
[album_id] [int] IDENTITY (1, 1) NOT NULL , [album_title] [varchar] (255) NOT NULL , [album_publish_date] [datetime] NOT NULL , [band_id] [int] NOT NULL ,
[album_price] [smallmoney] NOT NULL ) ON [PRIMARY]
GO
ALTER TABLE [dbo].[t_albums] WITH NOCHECK ADD
CONSTRAINT [DF_t_albums_album_publish_date] DEFAULT (getdate()) FOR [album_publish_date],
CONSTRAINT [DF_t_albums_album_price] DEFAULT (0.00) FOR [album_price], CONSTRAINT [PK_t_albums] PRIMARY KEY NONCLUSTERED
(
[album_id] ) ON [PRIMARY] ,
CONSTRAINT [IX_band_albums] UNIQUE NONCLUSTERED (
[album_title], [band_id] ) ON [PRIMARY] GO
ALTER TABLE [dbo].[t_albums] ADD
CONSTRAINT [FK_t_albums_t_bands] FOREIGN KEY (
[band_id]
[band_id] )
GO
Everything here should look familiar. These commands are very similar to those used to create the t_bandstable. The only difference is the last command that creates the foreign key to the band_idcolumn in the t_bandstable.
That’s it for tables. Now try creating the rest of the database on your own. If you run into problems, feel free to use the T-SQL statements that are included on the CD.
Next, we take a quick look at the views, stored procedures, and triggers for SQL Server database objects.