INFS Assignment 2: Database Query Report - Student Database, Oct 2016

This document presents a comprehensive database query report, offering a solution for INFS Assignment 2. It begins by outlining the create table statements for various entities such as ONLINE_CLINICIAN, HOME_USER, QUESTION, ANSWER, TREATMENT, CONSULTATION, DIAGNOSIS, DISEASE, USER_SIGNS_SYM, HCHT, TEST_RECOMENDATION, TEST_RESULT, and DIAG_DISEASE, defining their attributes and relationships. Following this, the report includes select statements to retrieve data from each table. The core of the assignment involves several SQL queries demonstrating data manipulation and retrieval, including inserting new records, selecting specific data based on conditions, updating existing records, and deleting records. Furthermore, the document presents SQL views, such as home_user_view, clinical_summary_view, and Consultation_View, which are used to simplify complex queries and provide summarized data. These views combine data from multiple tables to offer insights into the relationships between different entities within the database. This assignment showcases the practical application of SQL in designing, querying, and managing a relational database for an online healthcare system.

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