· 4 years ago · May 11, 2021, 12:22 PM
1SET NOCOUNT ON
2GO
3
4set nocount on
5set dateformat mdy
6
7USE master
8
9declare @dttm varchar(55)
10select @dttm=convert(varchar,getdate(),113)
11raiserror('Beginning InstPubs.SQL at %s ....',0,1,@dttm) with nowait
12
13GO
14
15if exists (select * from sysdatabases where name='pubs')
16begin
17 raiserror('Dropping existing pubs database ....',0,1)
18 DROP database pubs
19end
20GO
21
22CHECKPOINT
23go
24
25raiserror('Creating pubs database....',0,1)
26go
27/*
28 Use default size with autogrow
29*/
30
31CREATE DATABASE pubs
32GO
33
34CHECKPOINT
35
36GO
37
38USE pubs
39
40GO
41
42if db_name() <> 'pubs'
43 raiserror('Error in InstPubs.SQL, ''USE pubs'' failed! Killing the SPID now.'
44 ,22,127) with log
45
46GO
47
48--Removed in 2012
49-- execute sp_dboption 'pubs' ,'trunc. log on chkpt.' ,'true'
50GO
51
52execute sp_addtype id ,'varchar(11)' ,'NOT NULL'
53execute sp_addtype tid ,'varchar(6)' ,'NOT NULL'
54execute sp_addtype empid ,'char(9)' ,'NOT NULL'
55
56raiserror('Now at the create table section ....',0,1)
57
58GO
59
60CREATE TABLE authors
61(
62 au_id id
63
64 CHECK (au_id like '[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]')
65
66 CONSTRAINT UPKCL_auidind PRIMARY KEY CLUSTERED,
67
68 au_lname varchar(40) NOT NULL,
69 au_fname varchar(20) NOT NULL,
70
71 phone char(12) NOT NULL
72
73 DEFAULT ('UNKNOWN'),
74
75 address varchar(40) NULL,
76 city varchar(20) NULL,
77 state char(2) NULL,
78
79 zip char(5) NULL
80
81 CHECK (zip like '[0-9][0-9][0-9][0-9][0-9]'),
82
83 contract bit NOT NULL
84)
85
86GO
87
88CREATE TABLE publishers
89(
90 pub_id char(4) NOT NULL
91
92 CONSTRAINT UPKCL_pubind PRIMARY KEY CLUSTERED
93
94 CHECK (pub_id in ('1389', '0736', '0877', '1622', '1756')
95 OR pub_id like '99[0-9][0-9]'),
96
97 pub_name varchar(40) NULL,
98 city varchar(20) NULL,
99 state char(2) NULL,
100
101 country varchar(30) NULL
102
103 DEFAULT('USA')
104)
105
106GO
107
108CREATE TABLE titles
109(
110 title_id tid
111
112 CONSTRAINT UPKCL_titleidind PRIMARY KEY CLUSTERED,
113
114 title varchar(80) NOT NULL,
115
116 type char(12) NOT NULL
117
118 DEFAULT ('UNDECIDED'),
119
120 pub_id char(4) NULL
121
122 REFERENCES publishers(pub_id),
123
124 price money NULL,
125 advance money NULL,
126 royalty int NULL,
127 ytd_sales int NULL,
128 notes varchar(200) NULL,
129
130 pubdate datetime NULL
131
132 DEFAULT (null)
133)
134
135GO
136
137CREATE TABLE titleauthor
138(
139 au_id id
140
141 REFERENCES authors(au_id),
142
143 title_id tid
144
145 REFERENCES titles(title_id),
146
147 au_ord tinyint NULL,
148 royaltyper int NULL,
149
150
151 CONSTRAINT UPKCL_taind PRIMARY KEY CLUSTERED(au_id, title_id)
152)
153
154GO
155
156CREATE TABLE stores
157(
158 stor_id char(4) NOT NULL
159
160 CONSTRAINT UPK_storeid PRIMARY KEY CLUSTERED,
161
162 stor_name varchar(40) NULL,
163 stor_address varchar(40) NULL,
164 city varchar(20) NULL,
165 state char(2) NULL,
166 zip char(5) NULL
167)
168
169GO
170
171CREATE TABLE sales
172(
173 stor_id char(4) NOT NULL
174
175 REFERENCES stores(stor_id),
176
177 ord_num varchar(20) NOT NULL,
178 ord_date datetime NOT NULL,
179 qty smallint NOT NULL,
180 payterms varchar(12) NOT NULL,
181
182 title_id tid
183
184 REFERENCES titles(title_id),
185
186
187 CONSTRAINT UPKCL_sales PRIMARY KEY CLUSTERED (stor_id, ord_num, title_id)
188)
189
190GO
191
192CREATE TABLE roysched
193(
194 title_id tid
195
196 REFERENCES titles(title_id),
197
198 lorange int NULL,
199 hirange int NULL,
200 royalty int NULL
201)
202
203GO
204
205CREATE TABLE discounts
206(
207 discounttype varchar(40) NOT NULL,
208
209 stor_id char(4) NULL
210
211 REFERENCES stores(stor_id),
212
213 lowqty smallint NULL,
214 highqty smallint NULL,
215 discount dec(4,2) NOT NULL
216)
217
218GO
219
220CREATE TABLE jobs
221(
222 job_id smallint IDENTITY(1,1)
223
224 PRIMARY KEY CLUSTERED,
225
226 job_desc varchar(50) NOT NULL
227
228 DEFAULT 'New Position - title not formalized yet',
229
230 min_lvl tinyint NOT NULL
231
232 CHECK (min_lvl >= 10),
233
234 max_lvl tinyint NOT NULL
235
236 CHECK (max_lvl <= 250)
237)
238
239GO
240
241CREATE TABLE pub_info
242(
243 pub_id char(4) NOT NULL
244
245 REFERENCES publishers(pub_id)
246
247 CONSTRAINT UPKCL_pubinfo PRIMARY KEY CLUSTERED,
248
249 logo image NULL,
250 pr_info text NULL
251)
252
253GO
254
255CREATE TABLE employee
256(
257 emp_id empid
258
259 CONSTRAINT PK_emp_id PRIMARY KEY NONCLUSTERED
260
261 CONSTRAINT CK_emp_id CHECK (emp_id LIKE
262 '[A-Z][A-Z][A-Z][1-9][0-9][0-9][0-9][0-9][FM]' or
263 emp_id LIKE '[A-Z]-[A-Z][1-9][0-9][0-9][0-9][0-9][FM]'),
264
265 fname varchar(20) NOT NULL,
266 minit char(1) NULL,
267 lname varchar(30) NOT NULL,
268
269 job_id smallint NOT NULL
270
271 DEFAULT 1
272
273 REFERENCES jobs(job_id),
274
275 job_lvl tinyint
276
277 DEFAULT 10,
278
279 pub_id char(4) NOT NULL
280
281 DEFAULT ('9952')
282
283 REFERENCES publishers(pub_id),
284
285 hire_date datetime NOT NULL
286
287 DEFAULT (getdate())
288)