SQL Database Design and Querying: A Case Study

The provided assignment content consists of SQL commands to create tables, insert data, and execute queries in a database system. It involves multiple tables, including HOME_USER, ONLINE_Clinician, HCHT, QUESTION, ANSWER, TREATMENT, USER_SIGNS_SYM, DIAGNOSIS, TEST_RECOMENDATION, TEST_RESULT, DISEASE, and several SQL views named home_user_view, clinical_summary_view, and Consultation_View. The queries include SELECT statements to retrieve data from these tables and views.

1 | P a g e
INFS ASSIGNMENT 2
2016
DATABASE QUERY REPORT
STUDENT
28 October, 2016

Contents
CREATE TABLE STATEMENTS.................................................................................................................2
SELECT STATEMENTS.............................................................................................................................7
SQL QUERIES........................................................................................................................................10
SQL VIEWS...........................................................................................................................................11
2 | P a g e

CREATE TABLE STATEMENTS
Create table ONLINE_CLINICIAN
(
OID int primary key,
Phone double,
eMail varchar(20),
FName varchar(20),
LName varchar(20),
Address varchar(50)
);
create table HOME_USER
(
UID varchar(20) primary key,
Phone double,
eMail varchar(20),
FName varchar(20),
LName varchar(20),
Address varchar(50),
OID int,
constraint h_fk foreign key(OID) references ONLINE_CLINICIAN(OID) on delete set null
);
create table QUESTION
(
QID int primary key,
QText varchar(30),
Type varchar(20),
QTimestamp timestamp,
3 | P a g e

UID varchar(20),
constraint q_fk foreign key(UID) references HOME_USER(UID)on delete cascade
);
create table ANSWER
(
AID varchar(5) primary key,
AText varchar(50),
Type varchar(20),
ATimestamp timestamp,
QID int,
OID int,
constraint a_fk1 foreign key(QID) references QUESTION(QID) on delete set null,
constraint a_fk2 foreign key(OID) references ONLINE_CLINICIAN(OID) on delete set null
);
create table TREATMENT
(
TID varchar(5) primary key,
Texts varchar(20),
Names varchar(20),
Types varchar(20),
Pref_Order int,
UID varchar(20),
OID int,
constraint t_fk1 foreign key(UID) references HOME_USER(UID) on delete cascade,
constraint t_fk2 foreign key(OID) references ONLINE_CLINICIAN(OID) on delete set null
);
4 | P a g e

create table CONSULTATION
(
CID varchar(5) primary key,
Time_val time,
UID varchar(20),
OID int,
constraint c_fk1 foreign key(UID) references HOME_USER(UID) on delete cascade,
constraint c_fk2 foreign key(OID) references ONLINE_CLINICIAN(OID) on delete set null
);
create table DIAGNOSIS
(
UID varchar(20),
OID int,
DTimestamp timestamp,
DText varchar(50),
primary key(UID, OID, DTimestamp),
constraint d_fk1 foreign key(UID) references HOME_USER(UID) on delete cascade,
constraint d_fk2 foreign key(OID) references ONLINE_CLINICIAN(OID) on delete set null
);
create table DISEASE
(
ICD10CODE varchar(10) primary key,
Description varchar(50)
);
5 | P a g e

create table USER_SIGNS_SYM
(
UID varchar(20),
Times time,
Severity varchar(20),
Description varchar(50),
constraint chk_us1 check (severity IN ('low', 'medium', 'high')),
primary key(UID,Times),
constraint us_fk1 foreign key(UID) references HOME_USER(UID) on delete cascade
);
create table HCHT
(
TESTID varchar(5) primary key,
Conditions varchar(20),
Process varchar(30),
Toolname varchar(20),
Method varchar(20)
);
create table TEST_RECOMENDATION
(
UID varchar(20),
OID int,
test_Time time,
Description varchar(30),
Type varchar(10),
TESTID varchar(5),
constraint tr_fk1 foreign key(TESTID) references HCHT(TESTID) on delete set null,
6 | P a g e

constraint tr_fk2 foreign key(UID) references HOME_USER(UID) on delete cascade,
constraint tr_fk3 foreign key(OID) references ONLINE_CLINICIAN(OID) on delete set null
);
create table TEST_RESULT
(
TRID int primary key,
Result varchar(30),
Description varchar(50),
TTime time,
UID varchar(20),
TESTID varchar(5),
constraint trs_fk1 foreign key(TESTID) references HCHT(TESTID) on delete set null,
constraint trs_fk2 foreign key(UID) references HOME_USER(UID) on delete cascade
);
create table DIAG_DISEASE
(
UID varchar(20),
OID int,
DTimestamp timestamp,
ICD10CODE varchar(10),
primary key(UID, OID, DTimestamp, ICD10CODE)
);
ALTER TABLE DIAG_DISEASE ADD constraint dd_fk1 foreign key(ICD10CODE) references
DISEASE(ICD10CODE);
ALTER TABLE DIAG_DISEASE ADD constraint dd_fk2 foreign key(UID)references DIAGNOSIS(UID) ;
ALTER TABLE DIAG_DISEASE ADD constraint dd_fk3 foreign key(OID)references DIAGNOSIS(OID) ON
DELETE SET NULL;
7 | P a g e

SELECT STATEMENTS
SELECT * FROM ANSWER;
SELECT * FROM CONSULTATION;
SELECT * FROM DIAGNOSIS;
SELECT * FROM DIAG_DISEASE;
8 | P a g e

SELECT * FROM DISEASE;
SELECT * FROM HCHT;
SELECT * FROM HOME_USER;
SELECT * FROM ONLINE_CLINICIAN;
9 | P a g e

SELECT * FROM QUESTION;
SELECT * FROM TEST_RECOMENDATION;
SELECT * FROM TEST_RESULT;
SELECT * FROM TREATMENT;
SELECT * FROM USER_SIGNS_SYM;
10 | P a g e

SQL QUERIES
1. insert into HOME_USER (UID, Phone, eMail, FName, LName, Address, OID) values ('U6',
777777898, '[e-mail]', 'john', 'stevenson', '22 park view road, LA', 1);
SELECT * FROM HOME_USER;
2. select u.uid, u.Severity, u.Description, q.QText as question, d.DText as diagnose, t.TRID as
test_result_ID , t.Result, t.Description from USER_SIGNS_SYM u, QUESTION q, DIAGNOSIS d,
TEST_RESULT t, HOME_USER h where h.uid = u.uid and q.uid= h.uid and t.uid = h.uid and
h.uid = d.uid;
3. select * from HOME_USER where oid = 1;
11 | P a g e

4. select d.UID as home_user_id, d.DText as diagnose, t.TID as treatment_id, t.names as
treatment_name, t.Texts as treatment_info, tr.Description as test_recommendation from
TREATMENT t, DIAGNOSIS d, TEST_RECOMENDATION tr where d.uid = tr.uid and tr.uid =
t.UID AND D.UID = 'U2';
5. update HOME_USER set phone = 111111110 where uid = 'U1';
SELECT * FROM HOME_USER WHERE uid = 'U1';
6. delete from ONLINE_CLINICIAN where oid = 4;
SELECT * FROM ONLINE_CLINICIAN;
SQL VIEWS
1. create view home_user_view as
select h.UID, h.Phone, h.eMail, h.FName, QID as question_id, QText as question_text,
QTimestamp, u.Severity as symptom_severity, u.Description as symptom
from HOME_USER h, QUESTION q , USER_SIGNS_SYM u where h.uid = q.uid and h.uid = u.uid
order by QTimestamp, u.Description, QID ;
SELECT * FROM home_user_view;
12 | P a g e

2. create view clinical_summary_view as
select OID as clinician, count(uid) as summary, UID, Phone, eMail, FName, LName, Address
from HOME_USER group by oid;
SELECT * FROM clinical_summary_view;
3. create view Consultation_View as
select q.QID, q.QText, q.Type, QTimestamp, UID as user_id, a.AID as answer_id, AText as
answers, a.Type as answer_type, ATimestamp, a.OID as clinician_id from QUESTION q,
ANSWER a where q.qid = a.qid order by QTimestamp, q.QID;
SELECT * FROM Consultation_View;
13 | P a g e