-
Notifications
You must be signed in to change notification settings - Fork 0
/
P5_create_tables1.sql
162 lines (133 loc) · 6.02 KB
/
P5_create_tables1.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
/* IA 643 Database Security and Auditing
Script file for Project 5 Virtual Private Database
Spring 2021
Dr. Jim Q. Chen
Aliana Health Systems
Coding Scheme:
Clinic ID: 3xx; Doctor ID: 4xxx; Patient ID: 5xxxx;
Visit ID: 6xxxxx; Diagnosis ID: 'Rxxxxxx'; Admin_ID:22XX
*/
DROP TABLE APP_USER;
Drop Table Diagnosis;
Drop table Visit;
Drop Table Administrator;
Drop Table Patient;
Drop Table Doctor;
DROP TABLE Clinic;
-- Creating Tables and Inserting values.
CREATE TABLE Clinic (
Clinic_ID NUMBER(3) CONSTRAINT Clinic_Pk PRIMARY KEY,
Clinic_Name VARCHAR2(30) NOT NULL,
Clinic_Address VARCHAR2(50) NOT NULL,
CITY VARCHAR2(30) NOT NULL,
STATE VARCHAR2(2) NOT NULL,
ZIPCODE VARCHAR2(9) NOT NULL,
PHONE VARCHAR2(15) NOT NULL,
FAX VARCHAR2(15) NOT NULL,
EMAIL VARCHAR2(50) NOT NULL,
URL VARCHAR2(50) NOT NULL,
STATUS VARCHAR2(10) NOT NULL,
CTL_SEC_USER VARCHAR2(30) NULL,
CTL_SEC_LEVEL NUMBER NULL);
INSERT INTO Clinic VALUES(
301, 'SML Medical Clinic', '740 South 4th Ave.', 'St. Cloud','MN', '56301','3203084992',
'3203084896','mcs@sml.com', 'www.smlstcloud.com','A','ADMINA', 1);
INSERT INTO Clinic VALUES(
302, 'Riverside Health Center', '278 W. Division Street', 'St.Cloud','MN','56301','3203184997',
'3203185897','shc@sml.com', 'www.smlstcloud.com','A','ADMINB', 1);
CREATE TABLE Doctor(
Doctor_ID NUMBER(4) CONSTRAINT Doctor_PK PRIMARY KEY,
Clinic_ID NUMBER(3) CONSTRAINT Doctor_Clinic_ID_FK REFERENCES Clinic (Clinic_ID),
FIRST_NAME VARCHAR2(20) NOT NULL,
LAST_NAME VARCHAR2(20) NOT NULL,
DOB Date NOT NULL,
Sex CHAR(1),
Speciality Varchar2(30),
CTL_SEC_USER VARCHAR2(30) NULL,
CTL_SEC_LEVEL NUMBER NULL
);
INSERT INTO Doctor VALUES(
4001, 301,'Robert','Davison', '12-Mar-1962','M','Cardio', 'ADMINA', 4);
INSERT INTO Doctor VALUES(
4002, 302,'Stephanie','Seymour','23-Jun-1983', 'F', 'Liver','ADMINB', 4);
CREATE TABLE Patient (
Patient_ID NUMBER(5) CONSTRAINT Patient_PK PRIMARY KEY,
Doctor_ID NUMBER(4) CONSTRAINT Patient_Doctor_ID_FK REFERENCES Doctor (Doctor_ID),
FIRST_NAME VARCHAR2(20) NOT NULL,
LAST_NAME VARCHAR2(20) NOT NULL,
DOB Date NOT NULL,
Sex CHAR(1),
CTL_SEC_USER VARCHAR2(30) NULL,
CTL_SEC_LEVEL NUMBER NULL
);
INSERT INTO Patient VALUES(
51001,4001,'John','Hansen', '12-Mar-1996','M', 'RDAVISON', 4);
INSERT INTO Patient VALUES(
51002,4002,'Stephanie','Smith','23-Jun-1992', 'F', 'SSEYMOUR', 4);
CREATE TABLE Visit (
Visit_ID NUMBER(6) CONSTRAINT Visit_PK PRIMARY KEY,
Doctor_ID NUMBER(4) CONSTRAINT Visit_Doc_FK REFERENCES Doctor,
Patient_ID NUMBER(5) CONSTRAINT Visit_Pat_FK REFERENCES Patient,
Clinic_ID NUMBER(3) CONSTRAINT Visit_Clinic_Fk REFERENCES Clinic,
Visit_Date Date NOT NULL,
Visit_Type CHAR(1) NOT NULL,
CTL_SEC_USER VARCHAR2(30) NULL,
CTL_SEC_LEVEL NUMBER NULL
);
INSERT INTO Visit VALUES(
620001,4001, 51001,301,'8-Oct-16','W', 'RDAVISON', 4);
INSERT INTO Visit VALUES(
620002,4001, 51001,301,'23-Nov-15', 'D', 'RDAVISON', 4);
INSERT INTO Visit VALUES(
620003,4002, 51002, 302,'2-Aug-16','D', 'SSEYMOUR', 4);
INSERT INTO Visit VALUES(
620004,4002, 51002,302, '12-Nov-15','D','SSEYMOUR', 4);
CREATE TABLE Diagnosis (
Diagnosis_ID VARCHAR2(7) CONSTRAINT Diagnosis_Pk PRIMARY KEY,
ICD_Code VARCHAR2(5) ,
Diagnosis_Detail VARCHAR2(260) NOT NULL,
Visit_ID NUMBER(6) CONSTRAINT Diag_Visit_FK REFERENCES Visit,
Date_of_Diagnosis DATE,
CTL_SEC_USER VARCHAR2(30) NULL,
CTL_SEC_LEVEL NUMBER NULL
);
INSERT INTO Diagnosis VALUES(
'R123456','C00', 'Neoplasms of lip; advanced stage; bleeding around the corner of left side', 620001,'12-Oct-2016','RDAVISON',5);
INSERT INTO Diagnosis VALUES(
'R223456','B15.9', 'Hepatitis A; Progressive bowl movement leads to headache.', 620001,'10-Oct-2016','RDAVISON',5);
INSERT INTO Diagnosis VALUES (
'R323456',NULL,'a cranial tumour in the right frontal lobe. The diagnosis explains her symptoms of persistent and worsening headache over the last four weeks.', 620003, '2-Aug-2016','SSEYMOUR',5);
INSERT INTO Diagnosis VALUES (
'R423456',NULL,'Stool bloods spotted three times. Possible column polyps. Colon ascopy exam ordered', 620004, '13-NOV-2016','SSEYMOUR',5);
INSERT INTO Diagnosis VALUES (
'R523456',NULL,'Allergies', 620002, '23-NOV-2015','RDAVISON',5);
CREATE TABLE Administrator (
Admin_ID NUMBER(4) CONSTRAINT Admin_PK PRIMARY KEY,
Clinic_ID NUMBER(3) NOT NULL,
FIRST_NAME VARCHAR2(20) NOT NULL,
LAST_NAME VARCHAR2(20) NOT NULL,
CTL_SEC_USER VARCHAR2(20) NULL,
CTL_SEC_LEVEL NUMBER NULL
);
INSERT INTO Administrator VALUES(
2201, 301, 'Adam','Chen', 'ADMINA', 5);
INSERT INTO Administrator VALUES(
2202, 302, 'Bob','Lewis', 'ADMINB', 5);
CREATE TABLE App_User (
APP_User_ID NUMBER(3) CONSTRAINT User_PK PRIMARY KEY,
EMP_ID NUMBER (4),
USER_TYPE CHAR(1),
APP_USERNAME VARCHAR2(30) NOT NULL,
CTL_SEC_USER VARCHAR2(20) NULL,
CTL_SEC_LEVEL NUMBER NULL
);
INSERT INTO App_User VALUES(
501, 2201, 'A', 'AdminA', 'DBA643', 5);
INSERT INTO App_User VALUES(
502, 2202, 'A','AdminB', 'DBA643', 5);
INSERT INTO App_User VALUES(
503, 4001, 'D', 'RDavison', 'DBA643', 5);
INSERT INTO App_User VALUES(
504, 4002, 'D','SSeymour', 'DBA643', 5);
INSERT INTO App_User VALUES(
555, 4009, 'A','DBA643', 'DBA643', 5);