· 10 years ago · Apr 19, 2016, 03:09 AM
1--A5 MS SQL Server
2--similar to SHOW WARNINGS;
3SET ANSI_WARNINGS ON;
4GO
5use master;
6GO
7--drop existing database if it exists
8IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'msw15b')
9DROP DATABASE msw15b;
10GO
11--create database if not exists
12IF NOT EXISTS (SELECT name FROM maSter.dbo.sysdatabases WHERE name = N'msw15b')
13CREATE DATABASE msw15b;
14GO
15use msw15b;
16GO
17-- Table dbo.applicant
18--drop table if exists
19--N=subsequent string may be in Unicode (makes it portable to use with Unicode characters)
20--U=only look for objects with this name that are tables
21
22IF OBJECT_ID (N'dbo.applicant', N'U') IS NOT NULL
23DROP TABLE dbo.applicant;
24GO
25CREATE TABLE dbo.applicant
26(
27app_id SMALLINT not null identity(1,1),
28app_ssn INT NOT NULL check (app_ssn > 0 and app_ssn <= 999999999),
29app_state_id VARCHAR(45) NOT NULL,
30app_fname VARCHAR(15) NOT NULL,
31app_lname VARCHAR(30) NOT NULL,
32app_street VARCHAR(30) NOT NULL,
33app_city VARCHAR(30) NOT NULL,
34app_state CHAR(2) NOT NULL DEFAULT 'FL',
35app_zip INT NOT NULL check (app_zip > 0 and app_zip <= 999999999),
36app_email VARCHAR(100) NULL,
37app_dob DATE NOT NULL,
38app_gender CHAR(1) NOT NULL CHECK (app_gender IN('m','f')),
39app_bckgd_check CHAR(1) NOT NULL CHECK (app_bckgd_check IN('y','n')),
40app_notes VARCHAR(45) NULL,
41PRIMARY KEY (app_id),
42--make sure SSNs and State IDs are unique
43CONSTRAINT ux_app_ssn unique nonclustered (app_ssn ASC),
44CONSTRAINT ux_app_state_id unique nonclustered (app_state_id ASC)
45);
46--nonclustered by default
47
48-- 2. Table dbo.property
49IF OBJECT_ID (N'dbo.property', N'U') IS NOT NULL
50DROP TABLE dbo.property;
51CREATE TABLE dbo.property
52(
53prp_id SMALLINT NOT NULL identity(1,1),
54prp_street VARCHAR(30) NOT NULL,
55prp_city VARCHAR(30) NOT NULL,
56prp_state CHAR(2) NOT NULL DEFAULT 'FL',
57prp_zip int NOT NULL check (prp_zip > 0 and prp_zip <= 999999999),
58prp_type varchar(15) NOT NULL CHECK
59(prp_type IN('house', 'condo', 'townhouse', 'duplex', 'apt', 'mobile home', 'room')),
60prp_rental_rate DECIMAL(7,2) NOT NULL CHECK (prp_rental_rate > 0),
61prp_status CHAR(1) NOT NULL CHECK (prp_status IN('a','u')),
62prp_notes VARCHAR(255) NULL,
63PRIMARY KEY (prp_id)
64);
65-- Table dbo.agreement
66IF OBJECT_ID (N'dbo.agreement', N'U') IS NOT NULL
67DROP TABLE dbo.agreement;
68CREATE TABLE dbo.agreement
69(
70agr_id SMALLINT NOT NULL identity(1,1),
71prp_id SMALLINT NOT NULL,
72app_id SMALLINT NOT NULL,
73agr_signed DATE NOT NULL,
74agr_start DATE NOT NULL,
75agr_end DATE NOT NULL,
76agr_amt DECIMAL(7,2) NOT NULL CHECK (agr_amt > 0),
77agr_notes VARCHAR(255) NULL,
78PRIMARY KEY (agr_id),
79--make sure combination of prp_id, app_id, and agr_signed is unique
80CONSTRAINT ux_prp_id_app_id_agr_signed unique nonclustered
81(prp_id ASC, app_id ASC, agr_signed ASC),
82CONSTRAINT fk_agreement_property
83FOREIGN KEY (prp_id)
84REFERENCES dbo.property (prp_id)
85ON DELETE CASCADE
86ON UPDATE CASCADE,
87CONSTRAINT fk_agreement_applicant
88FOREIGN KEY (app_id)
89REFERENCES dbo.applicant (app_id)
90ON DELETE CASCADE
91ON UPDATE CASCADE
92);
93
94
95-- Table dbo.feature
96IF OBJECT_ID (N'dbo.feature', N'U') IS NOT NULL
97DROP TABLE dbo.feature;
98CREATE TABLE dbo.feature
99(
100ftr_id TINYINT NOT NULL identity(1,1),
101ftr_type VARCHAR(45) NOT NULL,
102ftr_notes VARCHAR(255) NULL,
103PRIMARY KEY (ftr_id)
104);
105
106
107-- 3. Table dbo.prop_feature
108IF OBJECT_ID (N'dbo.prop_feature', N'U') IS NOT NULL
109DROP TABLE dbo.prop_feature;
110CREATE TABLE dbo.prop_feature
111(
112pft_id SMALLINT NOT NULL identity(1,1),
113prp_id SMALLINT NOT NULL,
114ftr_id TINYINT NOT NULL,
115pft_notes VARCHAR(255) NULL,
116PRIMARY KEY (pft_id),
117--make sure combination of prp_id and ftr_id is unique
118CONSTRAINT ux_prp_id_ftr_id unique nonclustered (prp_id ASC, ftr_id ASC),
119CONSTRAINT fk_prop_feat_property
120FOREIGN KEY (prp_id)
121REFERENCES dbo.property (prp_id)
122ON DELETE CASCADE
123ON UPDATE CASCADE,
124CONSTRAINT fk_prop_feat_feature
125FOREIGN KEY (ftr_id)
126REFERENCES dbo.feature (ftr_id)
127ON DELETE CASCADE
128ON UPDATE CASCADE
129);
130-- Table dbo.occupant
131IF OBJECT_ID (N'dbo.occupant', N'U') IS NOT NULL
132DROP TABLE dbo.occupant;
133CREATE TABLE dbo.occupant
134(
135ocp_id SMALLINT NOT NULL identity(1,1),
136app_id SMALLINT NOT NULL,
137ocp_ssn int NOT NULL check (ocp_ssn > 0 and ocp_ssn <= 999999999),
138ocp_state_id VARCHAR(45) NULL,
139ocp_fname VARCHAR(15) NOT NULL,
140ocp_lname VARCHAR(30) NOT NULL,
141ocp_email VARCHAR(100) NULL,
142ocp_dob DATE NOT NULL,
143ocp_gender CHAR(1) NOT NULL CHECK (ocp_gender IN('m', 'f')),
144ocp_bckgd_check CHAR(1) NOT NULL CHECK (ocp_bckgd_check IN('n', 'y')),
145ocp_notes VARCHAR(45) NULL,
146PRIMARY KEY (ocp_id),
147--make sure SSNs and State IDs are unique
148CONSTRAINT ux_ocp_ssn unique nonclustered (ocp_ssn ASC),
149CONSTRAINT ux_ocp_state_id unique nonclustered (ocp_state_id ASC),
150CONSTRAINT fk_occupant_applicant
151FOREIGN KEY (app_id)
152REFERENCES dbo.applicant (app_id)
153ON DELETE CASCADE
154ON UPDATE CASCADE
155);
156
157
158-- 4 Table dbo.phone
159IF OBJECT_ID (N'dbo.phone', N'U') IS NOT NULL
160DROP TABLE dbo.phone;
161CREATE TABLE dbo.phone
162(
163phn_id SMALLINT NOT NULL identity(1,1),
164app_id SMALLINT NOT NULL,
165ocp_id SMALLINT NULL,
166phn_num bigint NOT NULL check (phn_num > 0 and phn_num <= 9999999999),
167phn_type CHAR(1) NOT NULL CHECK (phn_type IN('c','h','w','f')),
168phn_notes VARCHAR(45) NULL,
169PRIMARY KEY (phn_id),
170--make sure combination of app_id and phn_num is unique
171CONSTRAINT ux_app_id_phn_num unique nonclustered (app_id ASC, phn_num ASC),
172--make sure combination of ocp_id and phn_num is unique
173CONSTRAINT ux_ocp_id_phn_num unique nonclustered (ocp_id ASC, phn_num ASC),
174CONSTRAINT fk_phone_applicant
175FOREIGN KEY (app_id)
176REFERENCES dbo.applicant (app_id)
177ON DELETE CASCADE
178ON UPDATE CASCADE,
179CONSTRAINT fk_phone_occupant
180FOREIGN KEY (ocp_id)
181REFERENCES dbo.occupant (ocp_id)
182ON DELETE NO ACTION
183ON UPDATE NO ACTION
184);
185-- Table dbo.room_type
186IF OBJECT_ID (N'dbo.room_type', N'U') IS NOT NULL
187DROP TABLE dbo.room_type;
188CREATE TABLE dbo.room_type
189(
190rtp_id TINYINT NOT NULL identity(1,1),
191rtp_name VARCHAR(45) NOT NULL,
192rtp_notes VARCHAR(45) NULL,
193PRIMARY KEY (rtp_id)
194);
195
196-- 5 Table dbo.room
197IF OBJECT_ID (N'dbo.room', N'U') IS NOT NULL
198DROP TABLE dbo.room;
199CREATE TABLE dbo.room
200(
201rom_id SMALLINT NOT NULL identity(1,1),
202prp_id SMALLINT NOT NULL,
203rtp_id TINYINT NOT NULL,
204rom_size VARCHAR(45) NOT NULL,
205rom_notes VARCHAR(255) NULL,
206PRIMARY KEY (rom_id),
207--can have duplicate room types in same property
208--make sure combination of prp_id and rtp_id is unique
209-- CONSTRAINT ux_prp_id_rtp_id unique nonclustered (prp_id ASC, rtp_id ASC),
210CONSTRAINT fk_room_property
211FOREIGN KEY (prp_id)
212REFERENCES dbo.property (prp_id)
213ON DELETE CASCADE
214ON UPDATE CASCADE,
215CONSTRAINT fk_room_roomtype
216FOREIGN KEY (rtp_id)
217REFERENCES dbo.room_type (rtp_id)
218ON DELETE CASCADE
219ON UPDATE CASCADE
220);
221--show tables;
222SELECT * FROM information_schema.tables;
223-- disable all constraints
224EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
225
226-- disable all constraints
227EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
228
229--6
230-- Data for table dbo.feature
231INSERT INTO dbo.feature
232(ftr_type, ftr_notes)
233VALUES
234('Central A/C', NULL),
235('Pool', NULL),
236('Close to school', NULL),
237('Furnished', NULL),
238('Cable', NULL),
239('Washer/Dryer', NULL),
240('Refrigerator', NULL),
241('Microwave', NULL),
242('Oven', NULL),
243('1 -car garage', NULL),
244('2 -car garage', NULL),
245('Sprinkler system', NULL),
246('Security', NULL),
247('Wi-Fi', NULL),
248('Storage', NULL),
249('Fireplace', NULL);
250-- Data for table dbo.room_type
251INSERT INTO dbo.room_type
252(rtp_name, rtp_notes)
253VALUES
254('Bed', NULL),
255('Bath', NULL),
256('Kitchen', NULL),
257('Lanai', NULL),
258('Dining', NULL),
259('Living', NULL),
260('Basement', NULL),
261('Office', NULL);
262-- Data for table dbo.prop_feature
263INSERT INTO dbo.prop_feature
264(prp_id, ftr_id, pft_notes)
265VALUES
266(1, 4, NULL),
267(2, 5, NULL),
268(3, 3, NULL),
269(4, 2, NULL),
270(5, 1, NULL),
271(1, 1, NULL),
272(1, 5, NULL);
273
274--7
275
276-- Data for table dbo.room
277INSERT INTO dbo.room
278(prp_id, rtp_id, rom_size, rom_notes)
279VALUES
280(1,1, '10" x 10"', NULL),
281(3,2, '20" x 15"', NULL),
282(4,3, '8" x 8"', NULL),
283(5,4, '5O" x 50"', NULL),
284(2,5, '30" x 30"', NULL);
285-- Data for table dbo.property
286INSERT INTO dbo.property
287(prp_street, prp_city, prp_state, prp_zip, prp_type, prp_rental_rate, prp_status, prp_notes)
288VALUES
289('5133 3rd Road', 'Lake Worth', 'FL', '334671234', 'house', 1800.00, 'u', NULL),
290('92 Blab Way', 'Tallahassee', 'FL', '323011234', 'apt', 641.00, 'u', NULL),
291('756 Net Coke Lane', 'Panama City', 'FL', '342001234', 'condo', 2400.00, 'a', NULL),
292('574 Dontos Circle', 'Jacksonville', 'FL', '365231234', 'townhouse', 1942.99, 'a', NULL),
293('2241 W. Pensacola Street', 'Tallahassee', 'FL', '323041234', 'apt', 610.00, 'u', NULL);
294-- Data for table applicant
295INSERT INTO dbo.applicant
296(app_ssn, app_state_id, app_fname, app_lname, app_street, app_city, app_state, app_zip, app_email, app_dob, app_gender, app_bckgd_check, app_notes)
297VALUES
298('123456789', '4122C34556Q78', 'Carla', 'Vanderbilt', '1133 3rd Road', 'Lake Worth', 'FL', '334671234', 'csweeney@yahoo.com', '1961-11-26', 'F', 'y', NULL),
299('590123654', '8123A4560789', 'Amanda', 'Unde', '2241 W. Pensacola Street', 'Tallahassee', 'FL', '323041234', 'acc1Oc4@my.fSu.edu', '1981 - 04- 04', 'F', 'y', NULL),
300('987456321', 'dfed66532sedd', 'Dave', 'Stephens', '1293 Banana Code Dove', 'Panama City', 'FL', '323081234', 'Mjowett@comcast.net', '1965-05-15', 'M', 'n', NULL),
301('365214986', 'dgf9r56597224', 'Chris', 'Thornbough', '987 Lea Drive', 'Tallahassee', 'FL', '323011234', 'landbeck@fsu.edu', '1969-07-25', 'M', 'y', NULL),
302('326598236', 'yadayada4517', 'Spencer', 'Moore', '787 Tharpe Road', 'Tallahassee', 'FL', '323061234', 'spenceromy.(svedu', '1990-08-14', 'F', 'n', NULL);
303-- Data for table dbo.agreement
304INSERT INTO dbo.agreement
305(prp_id, app_id, agr_signed, agr_start, agr_end, agr_amt, agr_notes)
306VALUES
307(3,4, '2011-12-01', '2012-01-01', '2012-12-31', 1000.00, NULL),
308(1, 1, '1983-01-01', '1983-01-01', '1987-12-31', 800.00, NULL),
309(4, 2, '1999-12-31', '2000-01-01', '2004-12-31', 1200.00, NULL),
310(5, 3, '1999-07-31', '1999-08-01', '2004-07-31', 750.00, NULL),
311(2,5, '2011-01-01', '2011-01-01', '2013-12-31', 900.00, NULL);
312-- Data for table dbo.occupant
313INSERT INTO dbo.occupant
314(app_id, ocp_ssn, ocp_state_id, ocp_fname, ocp_lname, ocp_email, ocp_dob, ocp_gender, ocp_bckgd_check, ocp_notes)
315VALUES
316(1, '326532165', 'okd557ig4125', 'Bridget', 'Case -Sweeney', 'bridget.com', '1988-03-23', 'F', 'y', NULL),
317(1, '187452457', 'uht000ld', 'Brian', 'Sweeney', 'briansweeney.com', '1956-07-28', 'M', 'y', NULL),
318(2, '123456780', 'thrstsdoggre', 'Skittles', 'McGoobs', 'skittleswags.com', '2011-01-01', 'F', 'n', NULL),
319(2, '098123664', 'Ihrsrskitty', 'snails', 'Bans', 'smallsemeanie.Com', '1988-03-05', 'M', 'n', NULL),
320(5, '857694032', 'thistsbaby324', 'Baby', 'Girl', 'babygirl.com' , '2013-04-08', 'F', 'n', NULL);
321
322--8.
323
324-- Data for table dbo.occupant
325INSERT INTO dbo.occupant
326(app_id, ocp_ssn, ocp_state_id, ocp_fname, ocp_lname, ocp_email, ocp_dob, ocp_gender, ocp_bckgd_check, ocp_notes)
327VALUES
328(1, '326532265', 'okd557ig412', 'Bridget', 'Case-Sweeney', 'bcs10c@gmail.com', '1988-03-23', 'F', 'y', NULL),
329(1, '187452457', 'uht000ld', 'Brian', 'Sweeney', 'brian@sweeney.com', '1956-07-28', 'M','y', NULL),
330(2, '123456780', 'thisisdoggie', 'Skittles', 'McGoobs', 'skittles@wags.com', '2011-01-01', 'F', 'n', NULL),
331(2, '098123664', 'thisiskitty', 'Smalls', 'Balls', 'smalls@meanie.com' , '1988-03-05', 'M', 'n', NULL),
332(5, '857694032', 'thisisbaby324', 'Baby', 'Girl', 'baby@girl.com', '2013-04-08', 'F', 'n', NULL);
333-- Data for table dbo.phone
334INSERT INTO dbo.phone
335(app_id, ocp_id, phn_num, phn_type, phn_notes)
336VALUES
337(1, NULL, '5615233044', 'H', NULL),
338(2, NULL, '5616859976', 'C', NULL),
339(5,5, '8504569872', 'H', NULL),
340(1,1, '5613080898', 'H', NULL),
341(3, NULL, '8504152365', 'W', NULL);
342-- enable all constraints
343exec sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"
344--NOTE: both CHECKs needed:
345--1) first CHECK belongs with WITH
346--(ensures data gets checked for consistency when activating constraint)
347--2) second CHECK with CONSTRAINT
348--(type of constraint)
349
350--show data
351select * from dbo.feature;
352select * from dbo.prop_feature;
353select * from dbo.room_type;
354select * from dbo.room;
355select * from dbo.property;
356select * from dbo.applicant;
357select * from dbo.agreement;
358select * from dbo.occupant;
359select * from dbo.phone;