Figure 4: helpdesk.ddl and helpdesk.dml

All of the data definition language (DDL) to create
the database schema and some data manipulation language
(DML) with some sample data is listed here. Once the
database and schema are created, you will need to insert
some data into your support tables. I have included
some examples in the file helpdesk.dml. You may not
want to run this because the sample data will be meaningless
to you - it is only to show some examples of the syntax
of the insert commands needed for you to insert your
data.

helpdesk.ddl listing:

use Helpdesk
go

CREATE TABLE CTicket (
ticketId       numeric(9,0) NOT NULL,
occurance      numeric(9,0) IDENTITY,
resolution     varchar(255) NULL,
lastModifyDate smalldatetime NULL,
assignedTo     varchar(64) NULL,
keyWords       varchar(80) NULL,
statusChange   varchar(1) NULL,
resolution1    varchar(255) NULL,
timeSpent      smallmoney NULL,
ownerId        smallint NULL,
PRIMARY KEY (ticketId, occurance)
)
go

CREATE TABLE OTicket (
ticketId            numeric(9,0) IDENTITY,
submitDate          smalldatetime NULL,
dateNeededBy        varchar(10) NULL,
status              varchar(1) NULL,
department          varchar(30) NULL,
problemType         varchar(1) NULL,
requestDescription  varchar(255) NULL,
requestDescription2 varchar(255) NULL,
submitFirstName     varchar(64) NULL,
submitLastName      varchar(64) NULL,
priority            varchar(2) NULL,
assigned            varchar(64) NULL,
eMail               varchar(25) NULL,
ownerId             smallint NULL,
problemTypeId       smallint NULL,
submitterId         smallint NULL,
PRIMARY KEY (ticketId)
)
go

CREATE TABLE Owner (
ownerId             smallint NOT NULL,
ownerName           varchar(25) NULL,
ownerEmail          varchar(25) NULL,
PRIMARY KEY (ownerId)
)
go

CREATE TABLE ProblemTypes (
problemTypeId       smallint NOT NULL,
typeDescription     varchar(20) NULL,
PRIMARY KEY (problemTypeId)
)
go

CREATE TABLE Department (
departmentId        int NOT NULL,
deptName            varchar(25) NULL,
PRIMARY KEY (departmentId)
)
go

CREATE TABLE DepartmentPeople (
departmentPeopleId  int NOT NULL,
departmentId        int NULL,
personId            int NULL,
termDate            smalldatetime NULL,
PRIMARY KEY (departmentPeopleId)
)
go

CREATE TABLE MPerson (
personId            int NOT NULL,
lastName            varchar(25) NULL,
altName             varchar(25) NULL,
email               varchar(25) NULL,
termDate            smalldatetime NULL,
PRIMARY KEY (personId)
)
go

helpdesk.dml listing:

use Helpdesk
go
insert Owner values(1, "First Help Desk Support", "first@somewhere.com")
go
insert Owner values(2, " Second Help Desk Support ", "second@somewhere.com")
go
insert Owner values(3, "Third Help Desk Support ", "third@somewhere.com")
go

insert Department values(1, "Department 1")
go
insert Department values(2, "Department 2")
go
insert Department values(3, "Department 3")
go
insert MPerson values(1, "Doe", "John", "doe@somewhere.com", NULL)
go
insert DepartmentPeople values(1,1,1,NULL)
go
insert MPerson values(2, "Doe", "Jane", "jdoe@somewhere.com", NULL)
go
insert DepartmentPeople values(2,1,2,NULL)
go
insert MPerson values(3, "Flintstone", "Fred",
"flintstone@somewhere.com", NULL)
go
insert DepartmentPeople values(3,2,3,NULL)
go
insert MPerson values(4, "Rubble", "Barney",
"rubble@somewhere.com", NULL)
go
insert DepartmentPeople values(4,3,4,NULL)
go

insert ProblemTypes values(1, "Macintosh")
go
insert ProblemTypes values(2, "MS Windows/Win95")
go
insert ProblemTypes values(3, "Unix")
go
insert ProblemTypes values(4, "Telephone")
go
insert ProblemTypes values(5, "Software")
go
insert ProblemTypes values(6, "Sybase")
go
insert ProblemTypes values(9, "Other")
go