-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathCreate Database Tables.sql
209 lines (179 loc) · 6.06 KB
/
Create Database Tables.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
-- =============================================
-- Author: Vicente Figueroa
-- Create date: 5/14/2019
-- Description: Create Database Tables from Scratch
-- =============================================
CREATE PROCEDURE [Create Database Tables]
AS
BEGIN
/****** Query for creating Maintenance Table ******/
DROP TABLE IF EXISTS [dbo].[Maintenance]
CREATE TABLE [dbo].[Maintenance] (
[Work Item Type ID] int,
[Maintenance ID] int,
[Title] nvarchar(255),
[Assigned To] int,
[State ID] int,
[Version ID] int,
[Comment Count] int,
[Created Date] datetime,
[Accepted By] int,
[Accepted Date] datetime,
[Activity ID] int,
[Priority] int,
[Description] nvarchar(max),
[System ID] int,
[ChildOf] int,
PRIMARY KEY ([Maintenance ID]),
)
/****** Query for creating Release Table ******/
DROP TABLE IF EXISTS [dbo].[Release]
CREATE TABLE [dbo].[Release] (
[Release ID] int,
[Release Num] int,
[Release Name] nvarchar(255),
[Release Date] datetime,
[Associated Features] int,
PRIMARY KEY ([Release ID]),
)
/****** Query for creating System Table ******/
DROP TABLE IF EXISTS [dbo].[System]
CREATE TABLE [dbo].[System] (
[System ID] int,
[Abbreviation] nvarchar(255),
[System Name] nvarchar(255)
PRIMARY KEY ([System ID])
)
/****** Query for creating Product Table ******/
DROP TABLE IF EXISTS [dbo].[Product]
CREATE TABLE [dbo].[Product] (
[Product ID] int,
[Product Name] nvarchar(255),
[Product Description] nvarchar(max),
[Product Type ID] int,
[Product Schema ID] int,
[Product Log] nvarchar(255),
[Approved] nvarchar(10),
PRIMARY KEY ([Product ID]),
)
/****** Query for creating Version Table ******/
DROP TABLE IF EXISTS [dbo].[Version]
CREATE TABLE [dbo].[Version] (
[Version ID] int,
[Area Path] nvarchar(255),
[Product ID] int,
[Release ID] int,
PRIMARY KEY ([Version ID]),
)
/****** Query for creating Feature Table ******/
DROP TABLE IF EXISTS [dbo].[Feature]
CREATE TABLE [dbo].[Feature] (
[Feature ID] int,
[Feature Name] nvarchar(255),
[Feature Description] nvarchar(max),
[Feature Type ID] int,
[Feature Release Date] datetime,
PRIMARY KEY ([Feature ID])
)
/****** Query for creating Work Item Type Table ******/
DROP TABLE IF EXISTS [dbo].[Work Item Type]
CREATE TABLE [dbo].[Work Item Type] (
[Work Item Type ID] int,
[Work Item Type Name] nvarchar(255),
[Work Item Type Description] nvarchar(max),
PRIMARY KEY ([Work Item Type ID])
)
/****** Query for creating Stakeholder Table ******/
DROP TABLE IF EXISTS [dbo].[Stakeholder]
CREATE TABLE [dbo].[Stakeholder] (
[Stakeholder ID] int,
[Stakeholder Name] nvarchar(255),
[Stakeholder Email] nvarchar(255),
PRIMARY KEY ([Stakeholder ID])
)
/****** Query for creating State Table ******/
DROP TABLE IF EXISTS [dbo].[State]
CREATE TABLE [dbo].[State] (
[State ID] int,
[State Name] nvarchar(255),
[State Description] nvarchar(max),
PRIMARY KEY ([State ID])
)
/****** Query for creating Activity Table ******/
DROP TABLE IF EXISTS [dbo].[Activity]
CREATE TABLE [dbo].[Activity] (
[Activity ID] int,
[Activity Name] nvarchar(255),
[Activity Description] nvarchar(max),
PRIMARY KEY ([Activity ID])
)
/****** Query for creating Product Type Table ******/
DROP TABLE IF EXISTS [dbo].[Product Type]
CREATE TABLE [dbo].[Product Type] (
[Product Type ID] int,
[Product Type Name] nvarchar(255),
[Product Type Description] nvarchar(max),
PRIMARY KEY ([Product Type ID])
)
/****** Query for creating Product Schema Table ******/
DROP TABLE IF EXISTS [dbo].[Product Schema]
CREATE TABLE [dbo].[Product Schema] (
[Product Schema ID] int,
[Product Schema Name] nvarchar(255),
[Product Schema Description] nvarchar(max),
PRIMARY KEY ([Product Schema ID])
)
/****** Query for creating Feature Type Table ******/
DROP TABLE IF EXISTS [dbo].[Feature Type]
CREATE TABLE [dbo].[Feature Type] (
[Feature Type ID] int,
[Feature Type Name] nvarchar(255),
[Feature Type Description] nvarchar(max),
PRIMARY KEY ([Feature Type ID])
)
/****** Query for setting FK on Maintenance Table ******/
ALTER TABLE [dbo].[Maintenance]
ADD CONSTRAINT FK__WorkItemTypeID
FOREIGN KEY ([Work Item Type ID]) REFERENCES [Work Item Type]([Work Item Type ID])
ALTER TABLE [dbo].[Maintenance]
ADD CONSTRAINT FK__AssignedTo
FOREIGN KEY ([Assigned To]) REFERENCES [Stakeholder]([Stakeholder ID])
ALTER TABLE [dbo].[Maintenance]
ADD CONSTRAINT FK__State
FOREIGN KEY ([State ID]) REFERENCES [State]([State ID])
ALTER TABLE [dbo].[Maintenance]
ADD CONSTRAINT FK__AcceptedBy
FOREIGN KEY ([Accepted By]) REFERENCES [Stakeholder]([Stakeholder ID])
ALTER TABLE [dbo].[Maintenance]
ADD CONSTRAINT FK__Activity
FOREIGN KEY ([Activity ID]) REFERENCES [Activity]([Activity ID])
ALTER TABLE [dbo].[Maintenance]
ADD CONSTRAINT FK__VersionID
FOREIGN KEY ([Version ID]) REFERENCES [Version]([Version ID])
ALTER TABLE [dbo].[Maintenance]
ADD CONSTRAINT FK__SystemID
FOREIGN KEY ([System ID]) REFERENCES [System]([System ID])
/****** Query for setting FK on Release Table ******/
ALTER TABLE [dbo].[Release]
ADD CONSTRAINT FK__AssociatedFeatures
FOREIGN KEY ([Associated Features]) REFERENCES [dbo].[Feature]([Feature ID])
/****** Query for setting FK on Product Table ******/
ALTER TABLE [dbo].[Product]
ADD CONSTRAINT FK__ProductTypeID
FOREIGN KEY ([Product Type ID]) REFERENCES [dbo].[Product Type]([Product Type ID])
ALTER TABLE [dbo].[Product]
ADD CONSTRAINT FK__ProductSchemaID
FOREIGN KEY ([Product Schema ID]) REFERENCES [dbo].[Product Schema]([Product Schema ID])
/****** Query for setting FK on Version Table ******/
ALTER TABLE [dbo].[Version]
ADD CONSTRAINT FK__ProductID
FOREIGN KEY ([Product ID]) REFERENCES [dbo].[Product]([Product ID])
ALTER TABLE [dbo].[Version]
ADD CONSTRAINT FK__ReleaseID
FOREIGN KEY ([Release ID]) REFERENCES [dbo].[Release]([Release ID])
/****** Query for setting FK on Feature Table ******/
ALTER TABLE [dbo].[Feature]
ADD CONSTRAINT FK__FeatureType
FOREIGN KEY ([Feature Type ID]) REFERENCES [dbo].[Feature Type]([Feature Type ID])
END
GO