· 5 years ago · Mar 02, 2020, 08:28 PM
1-- Move to default database
2USE master;
3GO
4-- Drop the database if it exists and Create the database
5DROP DATABASE IF EXISTS SpeedingTicket;
6CREATE DATABASE SpeedingTicket;
7GO
8-- switch to new database
9USE SpeedingTicket;
10GO
11-- Drop the tables if they exist
12DROP TABLE IF EXISTS Category;
13DROP TABLE IF EXISTS Comment;
14DROP TABLE IF EXISTS Hardware;
15DROP TABLE IF EXISTS SupportStaff;
16DROP TABLE IF EXISTS Task;
17DROP TABLE IF EXISTS Ticket;
18DROP TABLE IF EXISTS TicketCategory;
19DROP TABLE IF EXISTS [User];
20GO
21-- Create Table Comment
22CREATE TABLE Comment (
23 CommentID INT IDENTITY NOT NULL,
24 CommentInfo NVARCHAR(255) NOT NULL,
25 TicketID INT NOT NULL,
26 UserID INT NULL,
27 SupportStaffID INT NULL,
28 PRIMARY KEY (commentID)
29);
30GO
31-- Create Table Hardware
32CREATE TABLE Hardware (
33 HardwareID INT IDENTITY NOT NULL,
34 Warranty NVARCHAR(32) NULL,
35 ModelNumber INT NOT NULL,
36 PRIMARY KEY (HardwareID)
37);
38GO
39--Create Table SupportStaff
40CREATE TABLE SupportStaff (
41 SupportStaffID INT IDENTITY NOT NULL,
42 StaffName NVARCHAR(32) NOT NULL,
43 StaffPhoto VARBINARY(MAX) NOT NULL,
44 StaffNumber INT NOT NULL,
45 PRIMARY KEY (SupportStaffID)
46);
47GO
48-- Create Table Task
49CREATE TABLE Task (
50 TaskID INT IDENTITY NOT NULL,
51 Description NVARCHAR(255) NOT NULL,
52 Status NVARCHAR(32) NOT NULL,
53 TicketID INT NULL,
54 SupportStaffID INT NULL,
55 PRIMARY KEY (TaskID)
56);
57GO
58-- Create Table Ticket
59CREATE TABLE Ticket (
60 TicketID INT IDENTITY NOT NULL,
61 TicketDetail NVARCHAR(255) NOT NULL,
62 TicketStatus NVARCHAR(32) NOT NULL,
63 OpenDate DATETIME2(7) NOT NULL,
64 ClosedDate DATETIME2(7) NULL,
65 UserID INT NULL,
66 TechnicianID INT NULL,
67 StaffID INT NULL,
68 HardwareID INT NULL,
69 PRIMARY KEY (TicketID)
70);
71GO
72-- Create TableCategory
73CREATE TABLE TicketCategory (
74 TicketID INT NOT NULL,
75 CategoryID INT NOT NULL,
76 PRIMARY KEY (TicketID, CategoryID)
77);
78GO
79-- Create Table User
80CREATE TABLE [User] (
81 UserID INT IDENTITY NOT NULL,
82 FirstName NVARCHAR(32) NOT NULL,
83 LastName NVARCHAR(32) NOT NULL,
84 PhoneNumber NCHAR(11) NOT NULL,
85 PRIMARY KEY (UserID)
86);
87GO
88-- Create Table Category
89CREATE TABLE Category (
90 CategoryID INT IDENTITY NOT NULL,
91 CategoryInfo nvarchar(255) NOT NULL,
92 PRIMARY KEY (CategoryID)
93);
94GO
95--Add all the Foreign Keys(FK)
96ALTER TABLE Comment
97ADD CONSTRAINT FK__Comment__TicketID
98FOREIGN KEY (TicketID)
99REFERENCES Ticket (TicketID);
100GO
101ALTER TABLE TicketCategory
102ADD CONSTRAINT FK__TicketCategory__CategoryID
103FOREIGN KEY (CategoryID)
104REFERENCES Category (CategoryID);
105GO
106ALTER TABLE Task
107ADD CONSTRAINT FK__Task__SupportStaffID
108FOREIGN KEY (SupportStaffID)
109REFERENCES SupportStaff (SupportStaffID);
110GO
111ALTER TABLE Comment
112ADD CONSTRAINT FK__Comment__SupportStaffID
113FOREIGN KEY (SupportStaffID)
114REFERENCES SupportStaff (SupportStaffID);
115GO
116ALTER TABLE Ticket
117ADD CONSTRAINT Ticket__TechnicianID
118FOREIGN KEY (TechnicianID)
119REFERENCES SupportStaff (SupportStaffID);
120GO
121ALTER TABLE Ticket
122ADD CONSTRAINT FK__Ticket__HardwareID
123FOREIGN KEY (HardwareID)
124REFERENCES Hardware (HardwareID);
125GO
126ALTER TABLE TicketCategory
127ADD CONSTRAINT FK__TicketCategory__TicketID
128FOREIGN KEY (TicketID) REFERENCES Ticket (TicketID);
129GO
130ALTER TABLE Ticket
131ADD CONSTRAINT FK__Ticket__StaffID
132FOREIGN KEY (StaffID)
133REFERENCES SupportStaff (SupportStaffID);
134GO
135ALTER TABLE Task
136ADD CONSTRAINT FK__Task__TicketID
137FOREIGN KEY (TicketID)
138REFERENCES Ticket (TicketID);
139GO
140ALTER TABLE Comment
141ADD CONSTRAINT FK__Comment__UserID
142FOREIGN KEY (UserID)
143REFERENCES [User] (UserID);
144GO
145ALTER TABLE Ticket
146ADD CONSTRAINT FK__Ticket__UserID
147FOREIGN KEY (UserID)
148REFERENCES [User] (UserID);
149GO
150-- Drop Constraints if they exists
151ALTER TABLE Comment
152DROP CONSTRAINT IF EXISTS FK__Comment__TicketID
153GO
154ALTER TABLE TicketCategory
155DROP CONSTRAINT IF EXISTS FK__TicketCategory__CategoryID
156GO
157ALTER TABLE Task
158DROP CONSTRAINT IF EXISTS FK__Task__SupportStaffID;
159GO
160ALTER TABLE Comment
161DROP CONSTRAINT IF EXISTS FK__Comment__SupportStaffID;
162GO
163ALTER TABLE Ticket
164DROP CONSTRAINT IF EXISTS Ticket__TechnicianID;
165GO
166ALTER TABLE Ticket
167DROP CONSTRAINT IF EXISTS FK__Ticket__HardwareID;
168GO
169ALTER TABLE TicketCategory
170DROP CONSTRAINT IF EXISTS FK__TicketCategory__TicketID
171GO
172ALTER TABLE Ticket
173DROP CONSTRAINT IF EXISTS FK__Ticket__StaffID;
174GO
175ALTER TABLE Task
176DROP CONSTRAINT IF EXISTS FK__Task__TicketID
177GO
178ALTER TABLE Comment
179DROP CONSTRAINT IF EXISTS FK__Comment__UserID
180GO
181ALTER TABLE Ticket
182DROP CONSTRAINT IF EXISTS FK__Ticket__UserID
183GO
184--Check Constraint to check that there is no number on the person's FirstName in the user table
185ALTER TABLE [User]
186ADD CONSTRAINT CK__User__FirstName
187CHECK (FirstName NOT LIKE '%[^0-9]%');
188GO
189--Checking the Check Constraints
190-- INSERT INTO [User] (FirstName, LastName, PhoneNumber)
191-- VALUES ('Bob123', 'Jones', 9028091234);
192-- GO
193--Unique constraint to check for duplicate StaffNumber in the SupportStaff Table
194ALTER TABLE SupportStaff
195ADD CONSTRAINT AK__SupportStaff__StaffNumber
196UNIQUE (StaffNumber);
197GO
198-- To default the closedDate of the Ticket to a blank; thus allowing empty value
199ALTER TABLE Ticket
200ADD CONSTRAINT DF__Ticket__ClosedDate
201DEFAULT ('') FOR ClosedDate;
202GO
203-- -- --Checking the Default Constraints
204-- INSERT INTO Ticket (TicketDetail, TicketStatus, OpenDate, UserID, TechnicianID, StaffID, HardwareID)
205-- VALUES ('Software Issue', 'Escalated',' 2020-02-25', NULL, NULL, NULL,NULL);
206-- GO
207--To drop the index if it exists
208-- DROP INDEX IF EXISTS IX__User__FirstName__LastName
209-- ON [User];
210-- Adding indexes to the FirstName and LastName in order to simplify the search
211CREATE INDEX IX__User__FirstName__LastName
212ON [User] (LastName DESC, FirstName ASC);
213GO