CGr7xtFJ

1SET ANSI_WARNINGS ON;
2
3use master;
4GO
5
6IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'jd19z')
7DROP DATABASE jd19z;
8GO
9
10IF NOT EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'jd19z')
11CREATE DATABASE jd19z;
12GO
13
14use jd19z;
15GO
16
17IF OBJECT_ID(N'dbo.patient', N'U') IS NOT NULL
18DROP TABLE dbo.patient;
19GO
20
21CREATE TABLE dbo.patient
22(
23pat_id SMALLINT not null identity(1,1),
24pat_ssn int NOT NULL check (pat_ssn > 0 and pat_ssn <= 999999999),
25pat_fname VARCHAR(15) NOT NULL,
26pat_lname VARCHAR(30) NOT NULL,
27pat_street VARCHAR(30) NOT NULL,
28pat_city VARCHAR(30) NOT NULL,
29pat_state CHAR(2) NOT NULL DEFAULT 'FL',
30pat_zip int NOT NULL check (pat_zip > 0 and pat_zip <= 999999999),
31pat_phone bigint NOT NULL check (pat_phone > 0 and pat_phone <= 999999999),
32pat_email VARCHAR(100) NULL,
33pat_dob DATE NOT NULL,
34pat_gender CHAR(1) NOT NULL CHECK (pat_gender IN('m','f')),
35pat_notes VARCHAR(45) NULL,
36PRIMARY KEY (pat_id),
37
38CONSTRAINT ux_pat_ssn unique nonclustered (pat_ssn ASC)
39);
40
41IF OBJECT_ID(N'dbo.medication', N'U') IS NOT NULL
42DROP TABLE dbo.medication;
43GO
44
45CREATE TABLE dbo.medication
46(
47med_id SMALLINT NOT NULL identity(1,1),
48med_name VARCHAR(100) NOT NULL,
49med_price DECIMAL(5,2) NOT NULL CHECK (med_price >0),
50med_shelf_life DATE NOT NULL,
51med_notes VARCHAR(255) NULL,
52PRIMARY KEY (med_id)
53);
54
55IF OBJECT_ID(N'dbo.prescription', N'U') IS NOT NULL
56DROP TABLE dbo.prescription;
57GO
58
59CREATE TABLE dbo.prescription
60(
61pre_id SMALLINT NOT NULL identity(1,1),
62pat_id SMALLINT NOT NULL,
63med_id SMALLINT NOT NULL,
64pre_date DATE NOT NULL,
65pre_dosage VARCHAR(255) NOT NULL,
66pre_num_refills VARCHAR(3) NOT NULL,
67pre_notes VARCHAR(255) NULL,
68PRIMARY KEY (pre_id),
69
70CONSTRAINT ux_pat_id_med_id_pre_date unique nonclustered
71(pat_id ASC, med_id ASC, pre_date ASC),
72
73CONSTRAINT fk_prescription_patient
74FOREIGN KEY (pat_id)
75REFERENCES dbo.patient (pat_id)
76ON DELETE NO ACTION
77ON UPDATE CASCADE
78);
79
80IF OBJECT_ID (N'dbo.treatment', N'U') IS NOT NULL
81DROP TABLE dbo.treatment;
82GO
83
84CREATE TABLE dbo.treatment
85(
86trt_id SMALLINT NOT NULL identity(1,1),
87trt_name VARCHAR(255) NOT NULL,
88trt_price DECIMAL(8,2) NOT NULL CHECK (trt_price > 0),
89trt_notes VARCHAR(255) NULL,
90PRIMARY KEY (trt_id)
91);
92
93IF OBJECT_ID (N'dbo.physician', N'U') IS NOT NULL
94DROP TABLE dbo.physician;
95GO
96
97CREATE TABLE dbo.physician
98(
99phy_id SMALLINT not null identity(1,1),
100phy_specialty VARCHAR(25) NOT NULL,
101phy_fname VARCHAR(15) NOT NULL,
102phy_lname VARCHAR(30) NOT NULL,
103phy_street VARCHAR(30) NOT NULL,
104phy_city VARCHAR(30) NOT NULL,
105phy_zip INT NOT NULL CHECK (phy_zip > 0 and phy_zip <= 999999999),
106phy_phone BIGINT NOT NULL CHECK (phy_phone > 0 and phy_phone <= 999999999),
107phy_fax BIGINT NOT NULL CHECK (phy_fax > 0 and phy_fax <= 999999999),
108phy_email VARCHAR(100) NULL,
109phy_url VARCHAR(100) NULL,
110phy_notes VARCHAR(255) NULL,
111PRIMARY KEY (phy_id),
112);
113
114IF OBJECT_ID (N'dbo.patient_treatment', N'U') IS NOT NULL
115DROP TABLE dbo.patient_treatment;
116GO
117
118CREATE TABLE dbo.patient_treatment
119(
120ptr_id SMALLINT NOT NULL identity(1,1),
121pat_id SMALLINT NOT NULL,
122phy_id SMALLINT NOT NULL,
123trt_id SMALLINT NOT NULL,
124ptr_date DATE NOT NULL,
125ptr_start TIME(0) NOT NULL,
126ptr_end TIME(0) NOT NULL,
127ptr_results VARCHAR(255) NULL,
128ptr_notes VARCHAR(255) NULL,
129PRIMARY KEY (ptr_id),
130
131CONSTRAINT ux_pat_id_phy_id_trt_id_ptr_date unique nonclustered
132(pat_id ASC, phy_id ASC, trt_id ASC, ptr_date ASC),
133
134CONSTRAINT fk_patient_treatment_patient
135FOREIGN KEY (pat_id)
136REFERENCES dbo.patient (pat_id)
137ON DELETE NO ACTION
138ON UPDATE CASCADE,
139
140CONSTRAINT fk_patient_treatment_physician
141FOREIGN KEY (phy_id)
142REFERENCES dbo.physician (phy_id)
143ON DELETE NO ACTION
144ON UPDATE CASCADE,
145
146CONSTRAINT fk_patient_treatment_treatment
147FOREIGN KEY (trt_id)
148REFERENCES dbo.treatment (trt_id)
149ON DELETE NO ACTION
150ON UPDATE CASCADE
151);
152
153IF OBJECT_ID (N'dbo.administration_lu', N'U') IS NOT NULL
154DROP TABLE dbo.administration_lu;
155
156CREATE TABLE dbo.administration_lu
157(
158pre_id SMALLINT NOT NULL,
159ptr_id SMALLINT NOT NULL,
160PRIMARY KEY (pre_id, ptr_id),
161
162CONSTRAINT fk_administration_lu_prescription
163FOREIGN KEY (pre_id)
164REFERENCES dbo.prescription (pre_id)
165ON DELETE NO ACTION
166ON UPDATE CASCADE,
167
168CONSTRAINT fk_administration_lu_patient_treatment
169FOREIGN KEY (ptr_id)
170REFERENCES dbo.patient_treatment (ptr_id)
171ON DELETE NO ACTION
172ON UPDATE NO ACTION
173);