SQL Database | Assignment-1

Running head: Database
DATABASE
Name of the Student
Name of the University
Author Note

1Database
Table of Contents
1. Entity Relationship Diagram:.............................................................................................2
2. Explanations:......................................................................................................................2
3. Data dictionary:...................................................................................................................3
4. Implement the database:...................................................................................................10
5. SQL statements:................................................................................................................21
6. SQL VIEWS:....................................................................................................................23
7. Explanations:....................................................................................................................26
Bibliography:............................................................................................................................28

2Database
1. Entity Relationship Diagram:
Figure 1: The Entity Relationship Diagram of hospital Felicity Stillwell Hospital
(Source: Created by Author)
2. Explanations:
The changes I have made in this ERD is discussed in this section of this report. This
ERD support patient registration form and staff allocation fir each ward. Many changes have
been made in this ERD. Every change is discussed below:
 Short stay and long stay patient entity are removed in this ERD, because inpatient
table capable enough to hold every information for inpatient.
 Outpatient clinic is removed in this ERD. This ERD is mostly support the patient
registration form so outpatient is not necessary part in this ERD.

3Database
 Appointment table hold the information of local doctor as well as patient information.
 Bed number is added in the ward entity. Bed number can indicate the total number of
beds in each ward.
 Charge nurse, director entity has been removed because they are all staff. Staff table
can manage every staff information efficiently. in this scenario generalization is not
mandatory.
 Requisitions entity is removed in this ERD, because It this ERD support only patient
form and ward allocation form.
 Supplier and different items are removed from this ERD.
 Detailed kin information is stored in the next of kin entity.
 Inpatient entity can hold more essential information than before.
 Staff allocation table can store every information that hospital authority required.
3. Data dictionary:
Ward table:
Column_name Data_type Nullabl
e
Key Column id
WARDNO NUMBER NO PRIMARY
KEY
1
WARDNAME VARCHAR2 (50) YES 2
LOCATION VARCHAR2(50) YES 3
EXTENTIONNUMBER NUMBER YES 4
TOTALBEDS NUMBER YES 5
Appointment Table:

4Database
Column_name Data_type Nullabl
e
Key Column
id
APPOINMENTNO NUMBER NO PRIMARY KEY 1
APPOINTDATE DATE YES 2
PROVIDERNO VARCHAR2(50) YES FOREIGN KEY 3
PATIENTNO VARCHAR2(50
BYTE)
YES 4
Inpatient Table:
Column_name Data_type Nullable Key Column
id
PATIENTNO VARCHAR2(50
BYTE)
NO PRIMARY
KEY,
FOREIGN
KEY
1
ADMITDATE_INWARD DATE YES 2
EXPECTED_DURATION DATE YES 3
EXPECTEDLEAVE_DAT
E
VARCHAR2(50
BYTE)
YES 4
LEAVEDATE DATE Yes 5
WARDNO NUMBER YES FOREIGN
KEY
6
LOCALDOCTOR TABLE:

5Database
Column_name Data_type Nullable Key Column
id
PROVIDERNO VARCHAR2(50
BYTE)
NO PRIMARY KEY 1
FULLNAME VARCHAR2(50
BYTE)
YES 2
STREETADDRESS VARCHAR2(50
BYTE)
YES 3
SUBURB VARCHAR2(20
BYTE)
YES 4
STATE VARCHAR2(20
BYTE)
Yes 5
POSTCODE NUNUMBERMBER YES 6
TELEPHONENUMBER NUMBER Yes 7
REFERDATE DATE Yes 8
NEXT OF KIN TABLE:
Column_name Data_type Nullable Key Column
id
PROVIDERNO VARCHAR2(50
BYTE)
NO PRIMARY
KEY,
FOREIGN
KEY
1
NAME VARCHAR2(50 YES 2

6Database
BYTE)
STREETADDRESS VARCHAR2(50
BYTE)
YES 3
SUBURB VARCHAR2(20
BYTE)
YES 4
STATE VARCHAR2(20
BYTE)
Yes 5
POSTCODE NUNUMBERMBER YES 6
RELATIONSHIP VARCHAR2(20
BYTE)
Yes 7
TELEPHONE_NUMBER NUMBER Yes 8
PATIENT TABLE:
Column_name Data_type Nullable Key Column
id
PATIENTNO VARCHAR2(50
BYTE)
NO PRIMARY
KEY, FOREIGN
KEY
1
FAMILYNAME VARCHAR2(50
BYTE)
YES 2
GIVENNAME VARCHAR2(50
BYTE)
YES 3
STREETADDRESS VARCHAR2(50
BYTE)
YES 4

7Database
SUBRUB VARCHAR2(20
BYTE)
Yes 5
STATE NUNUMBERMBER YES 6
POSTCODE NUMBER Yes 7
GENDER VARCHAR2(1
BYTE)
Yes 8
DATEOFBIRTH DATE Yes 9
MARTIALSTATUS VARCHAR2(1
BYTE)
Yes 10
TELEPHONENUMBER NUMBER Yes 11
STAFF TABLE:
Column_name Data_type Nullable Key Column
id
STAFFNO VARCHAR2(50
BYTE)
NO PRIMARY
KEY,
FOREIGN
KEY
1
FAMILYNAME VARCHAR2(50
BYTE)
YES 2
GIVENNAME VARCHAR2(50
BYTE)
YES 3
STREETADDRESS VARCHAR2(50
BYTE)
YES 4

8Database
SUBRUB VARCHAR2(20
BYTE)
Yes 5
STATE NUNUMBERMBER YES 6
POSTCODE NUMBER Yes 7
GENDER VARCHAR2(1
BYTE)
Yes 8
DOB DATE Yes 9
TELEPHONE_NUMBER NUMBER Yes 10
POSITION VARCHAR2(20
BYTE)
Yes 11
WARDNO NUMBER Yes FOREIGN
KEY
12
CURRENTSALARY VARCHAR2(20
BYTE)
Yes 13
HOURSPERWEEK NUMBER Yes 14
SALARYSCALE VARCHAR2(3
BYTE)
Yes 15
PERMANENR_TEMPORARY VARCHAR2(5
BYTE)
Yes 16
PAIDWEEKLYMONTHLY VARCHAR2(5
BYTE)
Yes 17
STAFF QUALIFICATION TABLE:
Column_name Data_type Nullable Key Column

9Database
id
STAFFNO VARCHAR2(50
BYTE)
NO PRIMARY
KEY,
FOREIGN
KEY
1
QUALIFICATION_TYPE VARCHAR2(20
BYTE)
YES 2
DATE_AWARDED DATE YES 3
INSTITUION VARCHAR2(50
BYTE)
YES 4
STAFF WORK EXPERIENCE TABLE:
Column_name Data_type Nullable Key Column
id
STAFFNO VARCHAR2(50
BYTE)
NO PRIMARY
KEY,
FOREIGN
KEY
1
WARDNO NUMBER YES 2
ORGANAIZATION VARCHAR2(20
BYTE)
YES 3
STARTDATE DATE YES 4
FINISHDATE DATE Yes 5

10Database
WARD STAFF ALLOCATION TABLE:
Column_name Data_type Nullable Key Column
id
STAFFNO VARCHAR2(50
BYTE)
NO PRIMARY
KEY,
FOREIGN
KEY
1
WARDNO NUMBER YES 2
STAFF_NAME VARCHAR2(50
BYTE)
YES 3
POSITION VARCHAR2(20
BYTE)
YES 4
SHIFT VARCHAR2(20
BYTE)
Yes 5
4. Implement the database:
Creating ward table:
CREATE TABLE WARD (
WardNo NUMBER, WARDNAME VARCHAR(50),lOCATION VARCHAR(50),
EXTENTIONNUMBER NUMBER, TOTALBEDS NUMBER,
CONSTRAINT WARDNO_PK PRIMARY KEY (WARDNO));

11Database
Creating table PATIENT:
CREATE TABLE PATIENT (
PATIENTNO VARCHAR2(50), FAMILYNAME VARCHAR(50),
GIVENNAME VARCHAR2(50), STREETADDRESS VARCHAR2(50),SUBRUB
VARCHAR2(20),
STATE VARCHAR2(20), POSTCODE NUMBER,GENDER VARCHAR2(1),
DATEOFBIRTH DATE, MARTIALSTATUS VARCHAR2(1),TELEPHONENUMBER
NUMBER,
CONSTRAINT PATIENTNOPK PRIMARY KEY (PATIENTNO)
);

12Database
Creating LOCALDOCTOR table:
CREATE TABLE LOCALDOCTOR (
PROVIDERNO VARCHAR(50),FULLNAME VARCHAR2(50), STREETADDRESS
VARCHAR(50),
SUBURB VARCHAR2(20), STATE VARCHAR2(20), POSTCODE NUMBER,
TELEPHONENUMBER NUMBER, referdate date,
CONSTRAINT LOCALPK PRIMARY KEY (PROVIDERNO));

13Database
Creating NEXT_OF_KIN table:
CREATE TABLE NEXT_OF_KIN (
PATIENTNO VARCHAR(50),NAME VARCHAR2(50),
STREETADDRESS VARCHAR2(50), SUBRUB VARCHAR(20),
STATE VARCHAR2(20), POSTCODE NUMBER, RELATIONSHIP VARCHAR2(20),
TELEPHONE_NUMBER NUMBER,
CONSTRAINT PATIENTPATIENT_PK PRIMARY KEY (PATIENTNO),
CONSTRAINT PATIENTPATIENT_FK FOREIGN KEY (PATIENTNO)
REFERENCES PATIENT (PATIENTNO));

14Database
Creating APPOINMENT Table:
CREATE TABLE APPOINMENT ( APPOINMENTNO NUMBER,
APPOINTDATE DATE, PROVIDERNO VARCHAR2(50),
PATIENTNO VARCHAR2(50),
CONSTRAINT APPOINEMTNNO_PK PRIMARY KEY (APPOINMENTNO),
CONSTRAINT PROVIDERNO_FK FOREIGN KEY (PROVIDERNO)REFERENCES
LOCALDOCTOR(PROVIDERNO),
CONSTRAINT PATIENTNONONO_FK FOREIGN KEY (PATIENTNO) REFERENCES
PATIENT (PATIENTNO));

15Database
Creating INPATIENT table:
CREATE TABLE INPATIENT (
PATIENTNO VARCHAR2(20), ADMIT_DATE_IN_WARD DATE,
expected_DURATION NUMBER, EXPECTED_LEAVE_DATE DATE,
LEAVE_DATE DATE, wardno number,
CONSTRAINT PATIENTNO_INPATIENT_PK PRIMARY KEY (PATIENTNO),
CONSTRAINT PATIENTNO_PATIENT_FK FOREIGN KEY (PATIENTNO)
REFERENCES PATIENT (PATIENTNO)
CONSTRAINT warrddd_FK FOREIGN KEY (wardno)
REFERENCES ward(wardno)
);

16Database
Creating staff table:
CREATE TABLE STAFF (
STAFFNO VARCHAR(50), FAMILYNAME VARCHAR(50),
GIVENNAME VARCHAR(50), STREETADDERESS VARCHAR2(50),
SUBRUB VARCHAR(20),STATE VARCHAR(20),POSTCODE NUMBER, GENDER
VARCHAR2(5),
DOB DATE, TELEPHONE_NUMBER NUMBER, POSITION VARCHAR(20),
WARDNO NUMBER, CURRENTSALARY VARCHAR(20),
HOURSPERWEEK NUMBER, SALARYSCALE
VARCHAR2(3),PERMANENR_TEMPORARY VARCHAR2(5),
PAIDWEEKLYMONTHLY VARCHAR2(5),
CONSTRAINT STAFFNO PRIMARY KEY (STAFFNO),
CONSTRAINT WARD_FK FOREIGN KEY(wardno)
REFERENCES WARD (WARDNO)

17Database
);
Creating STAFFWORK_EXPERIENCE table:
CREATE TABLE STAFFWORK_EXPERIENCE (
staffNo varchar(50), position varchar2(20),
organaization varchar2(20),
startdate date, finishdate date,
CONSTRAINT staffno_PPK primary KEY (STAFFNO),
CONSTRAINT STAFFNOE_EXPERIENCE_STAFF_FK FOREIGN KEY (STAFFNO)

18Database
REFERENCES STAFF (STAFFNO)
);
Creating WARDSTAFF_ALLOCATION table:
CREATE TABLE WARDSTAFF_ALLOCATION (
STAFFNO VARCHAR(20),WARDNO NUMBER,
STAFF_NAME VARCHAR(50), POSITION VARCHAR(20), SHIFT VARCHAR (20),
CONSTRAINT STAFFNO_WARD_PK PRIMARY KEY (STAFFNO),
CONSTRAINT STAFFNO_STAFF_FK FOREIGN KEY (STAFFNO)
REFERENCES STAFF (STAFFNO),
CONSTRAINT WARDNO_WARD_FK FOREIGN KEY (WARDNO)
REFERENCES WARD (WARDNO)
);

19Database
Creating STAFF_QUALIFICATION Table:
CREATE TABLE STAFF_QUALIFICATION (
STAFFNO VARCHAR(20),
QUALIFICATION_TYPE VARCHAR2(20),
DATE_AWARDED DATE, INSTITUION VARCHAR(50),
CONSTRAINT STAFFNO_WORKEXPERIENCE_PK PRIMARY KEY (STAFFNO),
CONSTRAINT DETAILS_STAFF_FK FOREIGN KEY (STAFFNO)
REFERENCES STAFF (STAFFNO)
);

20Database
SQL codes for SELECT, UPDATE and DELETE permissions on all database objects to
the user MARKERTL:
GRANT SELECT,update,DELETE ON WARD TO MARKERTL;
GRANT SELECT,update,DELETE ON PATIENT TO MARKERTL;
GRANT SELECT,update,DELETE ON LOCALDOCTOR TO MARKERTL;
GRANT SELECT,update,DELETE ON NEXT_OF_KIN TO MARKERTL;
GRANT SELECT,update,DELETE ON APPOINMENT TO MARKERTL;
GRANT SELECT,update,DELETE ON INPATIENT TO MARKERTL;
GRANT SELECT,update,DELETE ON STAFF TO MARKERTL;
GRANT SELECT,update,DELETE ON STAFF_WORK_EXPERIENCE TO MARKERTL;
GRANT SELECT,update,DELETE ON WARDSTAFF_ALLOCATION TO MARKERTL;
GRANT SELECT,update,DELETE ON STAFF_QUALIFICATION TO MARKERTL;
GRANT SELECT,update,DELETE ON viewa TO MARKERTL;
GRANT SELECT,update,DELETE ON viewb TO MARKERTL;

21Database
GRANT SELECT,update,DELETE ON viewc TO MARKERTL;
GRANT SELECT,update,DELETE ON viewd TO MARKERTL;
5. SQL statements:
a.
INSERT INTO STAFF VALUES ( 'S4576', 'Samuels', 'Moira', '49 School Road','Bedford',
'WA',6052,'F','30/05/1990',01504563357,'Charge Nurse',11,
'68,760',37.5,'1C','P','M');
INSERT INTO STAFF_QUALIFICATION VALUES ('S4576', 'BSc. (Nursing)',
'22/07/2012','Curtain University');
INSERT INTO STAFFWORK_EXPERIENCE VALUES (
'S4576', 'CHARGE NURSE','EASTERN HOSPITAL','23/01/2011','1/05/2015');
b.

22Database
INSERT INTO PATIENT VALUES ('P10234','Phelps','Anne',
'67 Wellmeaning Way','Wellington','WA',6856,'F','10/12/1955','M',01313324158);
INSERT INTO NEXT_OF_KIN VALUES (
'P10234','James Phelps', '67 Wellmeaning Way','Wellington','WA',
6856,'Spouse',01313324158);
INSERT INTO LOCALDOCTOR VALUES (
'1455784L','Dr Helen Pearson', '47 Kennedy Street','Murrayville','WA',
6855,01313326282, '13/12/2019');
c.
INSERT INTO WARDSTAFF_ALLOCATION VALUES ('S4576', 11,'Samuels','Charge
Nurse','LATE');
INSERT INTO WARDSTAFF_ALLOCATION VALUES ('S0980', 11,'Carol Cummings',

23Database
'Staff Nurse','LATE');
INSERT INTO WARDSTAFF_ALLOCATION VALUES ('S1257', 11,'Morgan Russell',
'Nurse','Late');
INSERT INTO WARDSTAFF_ALLOCATION VALUES ('S1458', 11,'Robin Plevin',
'Staff Nurse','Early');
INSERT INTO WARDSTAFF_ALLOCATION VALUES ('S2356', 11,'Amy O Donnell',
'Consultant','NIGHT');
INSERT INTO WARDSTAFF_ALLOCATION VALUES ('S3649', 11,'Laurence Burns',
'Nurse','Early');
6. SQL VIEWS:
VIEWA
CREATE VIEW ViewA AS
SELECT STAFFNO, (FAMILYNAME ||' '|| GIVENNAME)AS STAFF_FULL_NAME,
WARDNO
FROM STAFF

24Database
GROUP BY WARDNO,STAFFNO,FAMILYNAME,GIVENNAME;
VIEWB:
CREATE VIEW VIEWB AS
SELECT P.PATIENTNO,(P.FAMILYNAME ||' '|| P.GIVENNAME)AS PATIENTNAME
from
patient P
INNER JOIN INPATIENT IP ON P.patientno = IP.patientno
WHERE extract(month from ADMITDATE_INWARD)= extract(month from sysdate);
VIEWC:
CREATE VIEW VIEWC AS
SELECT PATIENT.PATIENTNO,(PATIENT.FAMILYNAME ||' '||
PATIENT.GIVENNAME)AS
PATIENTNAME,inpatient.ADMITDATE_INWARD,inpatient.leavedate from

25Database
patient
INNER JOIN INPATIENT ON patient.patientno = inpatient.patientno
WHERE inpatient.wardno =11 and extract(month from inpatient.ADMITDATE_INWARD)>
extract(month from sysdate-12);
VIEWD:
CREATE VIEW VIEWD AS
SELECT
APPOINMENT.PROVIDERNO,LOCALDOCTOR.streetaddress,LOCALDOCTOR.telepho
nenumber,count(APPOINMENT.PATIENTNO ) as total_reference from
APPOINMENT
INNER JOIN LOCALDOCTOR ON
LOCALDOCTOR.PROVIDERNO=APPOINMENT.PROVIDERNO
WHERE extract(month from LOCALDOCTOR.referdate )> extract(month from sysdate-6)
GROUP by
APPOINMENT.PROVIDERNO,LOCALDOCTOR.streetaddress,LOCALDOCTOR.telepho
nenumber,APPOINMENT.PATIENTNO;

26Database
7. Explanations:
1.
There are many solutions are available to fulfil these criteria. First is manual table,
second is check constraint and third is database trigger. Three methods are discussed below:
Manual table:
we can create one extra table “BED”. That can contain: BED (BEDNO,
BEDOCCUPIED_DATE, WARDNO, PATIENTNO).
Bed table can hold bed information and patient information. ward no and patient is foreign
key in this table. In the in-patient table, we can add bed_no that cannot accept the null
values. Before fill this entry, an admin can check the particular bed_no that is occupied or
not.
Check constraint:
The check constraint in the database can limit the range of a value that is placed in a
table.
Another process is check constraint. A check constraint can check if bed number is
allocated or not. This check constraint can be created in the on the bed table in bed no
column. When a database user provides the input this, they can check constraint will
automatically check the particular id is allocated or not.
Database trigger:

27Database
Trigger is one type of store procedure that is evoked when a special event is occurred.
Most of the trigger is made when a person changes the table data. It can work on DML
statements like insert, update and delete. This functions help a database admin to perform
certain actions.
In this scenario, a database trigger can perform this task. A database trigger can check
the bed id is allocated or not. If bed_no is allocated to another patient, database user can
display a message that “this bed_no is allocated to another patient”.
2.
In this scenario we can create a database user and provide only update privilege.
Without database privilege they are unable to perform other tasks. We can use following
codes to provide the privilege:
GRANT UPDATE ON STAFF TO PERSONALOFFICER;
When the information is updated, the current username can be recorded with the help of a
trigger. Or we can just add a row in the staff table. Where a user can store their username
after updating the field.

28Database
Bibliography:
Arulraj, J., Pavlo, A. and Dulloor, S.R., 2015, May. Let's talk about storage & recovery
methods for non-volatile memory database systems. In Proceedings of the 2015 ACM
SIGMOD International Conference on Management of Data (pp. 707-722).
Coronel, C. and Morris, S., 2016. Database systems: design, implementation, & management.
Cengage Learning.
Das, D., Yan, J., Zait, M., Valluri, S.R., Vyas, N., Krishnamachari, R., Gaharwar, P., Kamp,
J. and Mukherjee, N., 2015. Query optimization in Oracle 12c database in-
memory. Proceedings of the VLDB Endowment, 8(12), pp.1770-1781.
Elmasri, R. and Navathe, S., 2017. Fundamentals of database systems (Vol. 7). Pearson.
Faerber, F., Kemper, A., Larson, P.Å., Levandoski, J., Neumann, T. and Pavlo, A., 2017.
Main memory database systems. Foundations and Trends® in Databases, 8(1-2), pp.1-130.
Gaetjen, S., Knox, D.C. and Maroulis, W., 2015. Oracle Database 12c Security. McGraw-
Hill Education.
Kuhn, D., Alapati, S.R. and Padfield, B., 2016. Expert Oracle Indexing and Access Paths:
Maximum Performance for Your Database. Apress.
Lahiri, T., Chavan, S., Colgan, M., Das, D., Ganesh, A., Gleeson, M., Hase, S., Holloway, A.,
Kamp, J., Lee, T.H. and Loaiza, J., 2015, April. Oracle database in-memory: A dual format
in-memory database. In 2015 IEEE 31st International Conference on Data Engineering (pp.
1253-1258). IEEE.
Larson, P.Å. and Levandoski, J., 2016. Modern main-memory database systems. Proceedings
of the VLDB Endowment, 9(13), pp.1609-1610.

29Database
Li, H., Liu, H., Liu, Y. and Wang, Y., 2016. AN OBJECT-RELATIONAL IFC STORAGE
MODEL BASED ON ORACLE DATABASE. International Archives of the
Photogrammetry, Remote Sensing & Spatial Information Sciences, 41.
Petkovic, D., 2016. Temporal data in relational database systems: A comparison. In New
advances in information systems and technologies (pp. 13-23). Springer, Cham.
Shah, N., 2016. Database Systems Using Oracle. Pearson Education India.