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