Creating the Remaining Tables

Creating the Remaining Tables

Let’s create the rest of the database tables. Apply the process you used to build the Employee table to create the new data tables, using the data presented in Table 7.2 to Table 7.6. Later in this chapter, we’ll discuss how these tables work. For starters, though, you need to insert them into your database.

Table 7.2. The Departments table

Column	SQL Data	Identity	Allow Null	Primary Key
Name	Type
DepartmentID	int	Yes	No	Yes
Department	nvarchar(50)	No	No	No

Table 7.3. The HelpDesk table

Column	SQL Data	Identity	Allow Null	Primary Key
Name	Type
RequestID	int	Yes	No	Yes
EmployeeID	int	No	No	No
StationNumber int		No	Yes	No
CategoryID	int	No	No	No
SubjectID	int	No	No	No
Description	nvarchar(50)	No	Yes	No
StatusID	int	No	No	No

Table 7.4. The HelpDeskCategories table

Column	SQL Data	Identity	Allow Null	Primary Key
Name	Type
CategoryID	int	Yes	No	Yes
Category	nvarchar(50)	No	No	No

271

Chapter 7: Database Design and Development

Table 7.5. The HelpDeskSubjects table

Column	SQL Data	Identity	Allow Null	Primary Key
Name	Type
SubjectID	int	Yes	No	Yes
Subject	nvarchar(50)	No	No	No

Table 7.6. The HelpDeskStatus table

Column	SQL Data	Identity	Allow Null	Primary Key
Name	Type
StatusID	int	Yes	No	Yes
Status	nvarchar(50)	No	No	No

Using SQL Scripts

Yep, there’s a lot of data to type in! While we recommend that you create the tables yourself by defining the fields outlined here, you can achieve the same goal using an SQL script that’s included in this book’s code archive. This script contains SQL code that SQL Server understands, and contains instructions that create data structures (you’ll learn about SQL in Chapter 8). If you want to use the downloadable script, we recommend you have a look over the following tables to get an idea of the structures we’ll be creating, then read the section called “Executing SQL Scripts” that follows.

We already have a clear idea of the data we’ll store in the Employees and Departments tables. The other tables will be used to store help desk requests; we’ll discuss these in more detail in the following pages.

Executing SQL Scripts

If you prefer not to create the data tables manually, you can use the CreateTables.sql script included in the book’s code archive to create the tables for you. This script is most easily used with SQL Server Management Studio. After you log in, click the New Query button on the toolbar (or select File > New >

Query with Current Connection). Paste the contents of the CreateTables.sql script into the window that opens, and press F5 to execute the commands. Note that if you have already created the Employees table, you should remove the CREATE TABLE command that creates this table before you hit F5.

272