Greenwich Health Centre (GHC) Database System Analysis and SQL Queries

This assignment focuses on the design and implementation of a database system for the Greenwich Health Centre (GHC). The assignment includes the creation of various tables such as REGISTER, SPECIALIST, BOOKING, PATIENT, ROOM, SESSION, PAYMENT, and PAYMENTTYPE, along with the insertion of sample data into these tables. The core of the assignment involves formulating and executing SQL queries to retrieve specific information from the database. These queries cover a range of operations, including listing specialists, counting patients, viewing patients checked by specific specialists, listing patients visited during a specific time period, listing rooms booked by doctors, listing available rooms, listing specialists older than 50, calculating statistics of male and female specialists, calculating specialist daily income, listing card and cash payments, calculating total money, and listing specialists who have not paid room charges. The assignment also includes a discussion of data models and database technologies, including conceptual, logical, and physical data models, as well as record-based logical data models such as relational, network, and hierarchical models. Furthermore, the assignment requires a critical discussion of different types of database management system technologies, illustrating their benefits and limitations. The provided solution on Desklib demonstrates the practical application of database concepts and SQL queries in a healthcare setting.

Greenwich Health Centre (GHC)

Introduction
“Greenwich Health Centre (GHC)” is aimed to deliver a medical treatment
services for patients is operated by a group of health professionals and
located at Greenwich, South East London. GHC has 20 rooms equipped with
necessary health equipment. The administration department of the center is
responsible for managing the specialist’s room booking and patient’s
appointment system as well as other related issues. There are no restrictions
in booking rooms. However, a specialist who wants to book rooms must
compile the following rules.
1. Analysis, Design and Implement a database system
REGISTER (REGISTERID, REGISTERDATE, SPECIALISTID,
FULLNAME, MARITALSTATUS, SEX, DATEOFBIRTH,
CONTACTADDRESS, CONTACTNUMBER,
EMERGENCYCONTACTDETAILS, HIGHESTEDUCATIONS,
SPECIALIZATIONAREA, TRAININGCERTIFICATES,
MEMBERSHIPOFPROFESSIONAL, VALIDDOCUMENTS,
PHOTOGRAPHS, VALIDCRB, REFERENCES)
SPECIALIST(SPECIALISTID, FULLNAME, ADDRESS, GENDER,
PHONE, EMAIL, STATE, ZIP, REGISTERID)
BOOKING (BOOKINGID, BOOKINGDATE, AVAILABLESTATUS,
SPECIALISTID, ROOMID)
PATIENT(PATIENTID, FULLNAME, ADDRESS, GENDER, PHONE,
EMAIL, STATE, ZIP, REGISTERID )
ROOM (ROOMID, DESCRIPTION, AVAILABLESTATUS)
SESSION (SESSIONID, SESSIONNAME, TIMESLOT, ROOMID)
PAYMENT (PAYMENTID, PAYMENTDATE, CHARGE, PAYMENTTYPE,
PATIENTID, SPECIALISTID )
PAYMENTTYPE (PAYMENTTYPEID, DESCRIPTION)

2. Record entry in table
CREATE TABLE booking (
BOOKINGID int NOT NULL,
BOOKINGDATE date NOT NULL,
AVAILABLESTATUS varchar(150) NOT NULL,
SPECIALISTID int NOT NULL,
ROOMID int NOT NULL
) ;
INSERT INTO booking (BOOKINGID, BOOKINGDATE, AVAILABLESTATUS, SPECIALISTID,
ROOMID) VALUES
(1, '2016-11-03', 'Y', 1, 1),
(2, '2016-11-02', 'Y', 2, 1),
(3, '2016-11-01', 'N', 1, 1),
(4, '2016-10-03', 'Y', 3, 1),
(5, '2016-10-03', 'Y', 1, 2),
(6, '2016-09-03', 'Y', 4, 1),
(7, '2016-08-02', 'Y', 1, 3),
(8, '2016-10-07', 'Y', 2, 1),
(9, '2016-10-08', 'Y', 1, 1),
(10, '2016-10-09', 'N', 5, 2),

(11, '2016-10-12', 'Y', 1, 1),
(12, '2016-10-23', 'Y', 6, 2),
(13, '2016-10-25', 'Y', 1, 1),
(14, '2016-10-22', 'N', 7, 3),
(15, '2016-10-21', 'Y', 8, 1);
CREATE TABLE patient (
PATIENTID int NOT NULL,
FULLNAME varchar(100) NOT NULL,
ADDRESS varchar(150) NOT NULL,
GENDER char(1) NOT NULL,
PHONE varchar(50) NOT NULL,
EMAIL int NOT NULL,
STATE varchar(30) NOT NULL,
ZIP varchar(30) NOT NULL,
REGISTERID int NOT NULL
) ;

INSERT INTO patient (PATIENTID, FULLNAME, ADDRESS, GENDER, PHONE, EMAIL, STATE,
ZIP, REGISTERID) VALUES
(1, 'Jackulin', 'New York', 'M', '882-123-4563', 0, 'NY', '25631', 1),
(2, 'Janet', '203 Rupert Drive', 'M', '882-123-4563', 0, 'NY', '25631', 1),
(3, 'James', 'New York', 'M', '882-123-4563', 0, 'NY', '25631', 1),
(4, 'Lamb', '78 The Trees', 'M', '882-123-4563', 0, 'NY', '25631', 1),
(5, 'Frances', 'New York', 'M', '882-123-4563', 0, 'NY', '25631', 1),
(6, 'Glew', 'New York', 'M', '882-123-4563', 0, 'NY', '25631', 1),
(7, 'Gallimore', 'New York', 'M', '882-123-4563', 0, 'NY', '25631', 1),
(8, 'Julie', '55 Penny Lane', 'M', '882-123-4563', 0, 'NY', '25631', 1),
(9, 'Evans', 'New York', 'M', '882-123-4563', 0, 'NY', '25631', 1),
(10, 'Duncan', 'New York', 'M', '882-123-4563', 0, 'NY', '25631', 1),
(11, 'Hayter', '707 Oxford Road', 'M', '882-123-4563', 0, 'NY', '25631', 1),
(12, 'Arthur', 'New York', 'M', '882-123-4563', 0, 'NY', '25631', 1),
(13, 'Everard', '22 Rudding Park View', 'M', '882-123-4563', 0, 'NY', '25631',
1),
(14, 'Donald', 'New York', 'M', '882-123-4563', 0, 'NY', '25631', 1),
(15, 'Dennison', '29 Gelder Street', 'M', '882-123-4563', 0, 'NY', '25631', 1);

CREATE TABLE payment (
PAYMENTID int NOT NULL,
PAYMENTDATE date NOT NULL,
CHARGE decimal(10,0) NOT NULL,
PAYMENTTYPE varchar(50) NOT NULL,
PATIENTID int NOT NULL,
SPECIALISTID int NOT NULL
) ;
INSERT INTO payment (PAYMENTID, PAYMENTDATE, CHARGE, PAYMENTTYPE, PATIENTID,
SPECIALISTID) VALUES
(1, '2016-11-03', 200, '1', 1, 1),
(2, '2016-11-02', 300, '2', 3, 2),
(3, '2016-11-01', 400, '1', 1, 4),
(4, '2016-11-04', 500, '1', 2, 3),
(5, '2016-11-05', 200, '2', 7, 9),

(6, '2016-11-06', 600, '1', 8, 10),
(7, '2016-11-08', 700, '1', 1, 5),
(8, '2016-11-07', 800, '1', 9, 12),
(9, '2016-11-09', 900, '2', 4, 11),
(10, '2016-11-10', 1200, '1', 1, 6),
(11, '2016-11-11', 2200, '1', 1, 13),
(12, '2016-11-12', 2000, '1', 5, 7),
(13, '2016-11-13', 350, '2', 10, 15),
(14, '2016-11-14', 250, '1', 6, 8),
(15, '2016-11-15', 120, '1', 11, 14);
CREATE TABLE paymenttype (
PAYMENTTYPEID int NOT NULL,
DESCRIPTION varchar(100) NOT NULL
) ;

CREATE TABLE register (
REGISTERID int NOT NULL,
REGISTERDATE date NOT NULL,
SPECIALISTID int NOT NULL,
FULLNAME varchar(100) NOT NULL,
MARITALSTATUS char(1) NOT NULL,
SEX char(1) NOT NULL,
DATEOFBIRTH date NOT NULL,
CONTACTADDRESS varchar(150) NOT NULL,
CONTACTNUMBER varchar(30) NOT NULL,
EMERGENCYCONTACTDETAILS varchar(150) NOT NULL,
HIGHESTEDUCATIONS varchar(150) NOT NULL,
SPECIALIZATIONAREA varchar(150) NOT NULL,
TRAININGCERTIFICATES varchar(150) NOT NULL,
MEMBERSHIPOFPROFESSIONAL varchar(150) NOT NULL,
VALIDDOCUMENTS varchar(150) NOT NULL,
PHOTOGRAPHS varchar(250) NOT NULL,
VALIDCRB varchar(100) NOT NULL,
REFERENCE varchar(200) NOT NULL
) ;

INSERT INTO register (REGISTERID, REGISTERDATE, SPECIALISTID, FULLNAME,
MARITALSTATUS, SEX, DATEOFBIRTH, CONTACTADDRESS, CONTACTNUMBER,
EMERGENCYCONTACTDETAILS, HIGHESTEDUCATIONS, SPECIALIZATIONAREA,
TRAININGCERTIFICATES, MEMBERSHIPOFPROFESSIONAL, VALIDDOCUMENTS, PHOTOGRAPHS,
VALIDCRB, REFERENCE) VALUES
(1, '2016-11-03', 1, 'Mikasa ', 'M', 'F', '2000-11-02', '65, Jackson Height',
'852-236-5632', '001-256-5632', 'University Certificate', 'Gastroenterology',
'Medical Council', 'Medical Council', 'Yes', '', 'Yes', 'Dr. Jaccky'),
(2, '2016-11-03', 1, 'Frances', 'M', 'F', '2000-11-03', '65, Jackson Height',
'852-236-5633', '001-256-5632', 'University Certificate', 'ENT', 'Medical
Council', 'Medical Council', 'Yes', '', 'Yes', 'Dr. Robin'),
(3, '2016-11-03', 1, 'Glew', 'M', 'F', '2000-11-01', '65, Jackson Height', '852-
236-5634', '001-256-5632', 'Training Certificate', 'Orthopedic', 'Medical
Council', 'Medical Council', 'Yes', '', 'Yes', 'Dr. Jaccky'),
(4, '2016-11-03', 1, 'Julie', 'M', 'F', '2000-11-04', '65, Jackson Height', '852-
236-5635', '001-256-5632', 'University Certificate', 'Cardiology', 'Medical
Council', 'Medical Council', 'Yes', '', 'Yes', 'Dr. Jaccky'),
(5, '2016-11-03', 1, 'Evans', 'M', 'F', '2000-11-05', '65, Jackson Height', '852-
236-5636', '001-256-5632', 'Medical Board Certificate', 'Endocrinology', 'Medical
Council', 'Medical Council', 'Yes', '', 'Yes', 'Dr. Jaccky'),
(6, '2016-11-03', 1, 'Hayter', 'M', 'F', '2000-11-05', '65, Jackson Height',
'852-236-5637', '001-256-5632', 'Training Certificate', 'Gastroenterology',
'Medical Council', 'Medical Council', 'Yes', '', 'Yes', 'Dr. Evans'),
(7, '2016-11-03', 1, 'Duncan', 'M', 'F', '2000-11-06', '65, Jackson Height',
'852-236-5638', '001-256-5632', 'University Certificate', 'Neurology', 'Medical
Council', 'Medical Council', 'Yes', '', 'Yes', 'Dr. Jaccky'),
(8, '2016-11-03', 1, 'Arthur', 'M', 'F', '2000-11-07', '65, Jackson Height',
'852-236-5639', '001-256-5632', 'Medical Board Certificate', 'Urology', 'Medical
Council', 'Medical Council', 'Yes', '', 'Yes', 'Dr. Jaccky'),
(9, '2016-11-03', 1, 'Harris', 'M', 'F', '2000-11-07', '65, Jackson Height',
'852-236-5620', '001-256-5632', 'University Certificate', 'Paediatrics', 'Medical
Council', 'Medical Council', 'Yes', '', 'Yes', 'Dr. Jaccky'),
(10, '2016-11-03', 1, 'Cartright', 'M', 'F', '2000-11-09', '65, Jackson Height',
'852-236-5642', '001-256-5632', 'University Certificate', 'Rheumatology',
'Medical Council', 'Medical Council', 'Yes', '', 'Yes', 'Dr. Evans'),
(11, '2016-11-03', 1, 'Barry', 'M', 'F', '2000-11-10', '65, Jackson Height',
'852-236-5652', '001-256-5632', 'Training Certificate', 'Cardiology', 'Medical
Council', 'Medical Council', 'Yes', '', 'Yes', 'Dr. Jaccky'),
(12, '2016-11-03', 1, 'Everard', 'M', 'F', '2000-11-11', '65, Jackson Height',
'852-236-5644', '001-256-5632', 'University Certificate', 'Cardiology', 'Medical
Council', 'Medical Council', 'Yes', '', 'Yes', 'Dr. Jaccky'),
(13, '2016-11-03', 1, 'Roberts', 'M', 'F', '2000-11-12', '65, Jackson Height',
'852-236-5732', '001-256-5632', 'Medical Board Certificate', 'Rheumatology',
'Medical Council', 'Medical Council', 'Yes', '', 'Yes', 'Dr. Jaccky'),

(14, '2016-11-03', 1, 'John', 'M', 'F', '2000-11-13', '65, Jackson Height', '852-
236-5332', '001-256-5632', 'University Certificate', 'Urology', 'Medical
Council', 'Medical Council', 'Yes', '', 'Yes', 'Dr. Jaccky'),
(15, '2016-11-03', 1, 'Mika', 'M', 'F', '2000-11-14', '65, Jackson Height', '852-
236-5232', '001-256-5632', 'University Certificate', 'Gastroenterology', 'Medical
Council', 'Medical Council', 'Yes', '', 'Yes', 'Dr. Evans');
CREATE TABLE room (
ROOMID int NOT NULL,
DESCRIPTION varchar(100) NOT NULL,
AVAILABLESTATUS char(1) NOT NULL
) ;

INSERT INTO room (ROOMID, DESCRIPTION, AVAILABLESTATUS) VALUES
(1, 'Single', 'Y'),
(2, 'Double', 'Y'),
(3, 'Master', 'Y'),
(4, 'Classic', 'Y');

CREATE TABLE session (
SESSIONID int NOT NULL,
SESSIONNAME varchar(150) NOT NULL,
TIMESLOT varchar(150),
ROOMID int NOT NULL
) ;