DBST 651 University of Maryland Creating Database for Grand Med Hospital MySQL Task Hello I am attaching the files done from previous and what the professo

DBST 651 University of Maryland Creating Database for Grand Med Hospital MySQL Task Hello I am attaching the files done from previous and what the professor want for step 4. please send me in details so i can make my own sql script by looking at yours and everything that it is required. If you have any questions or concerns, let me know.Thank you,Sneha V. 1. Entity and Attribute Description
a. Patient- This entity is used to store patients’ records in the system. The patient entity has
the following attribute and attributes description.
i.
Patient_ID- This attribute is used to store a Patient ID for the pin in the patient
table. It has an integer attribute description. It is also the primary key for the
entity patient.
ii.
Name- This attribute is used to store the name for the patient in the patient table.
It has Varchar, a length of 25 characters.
iii.
Address- This attribute is used to store the address for the patient in the patient
table. It has a Varchar data type and length of at least 50 characters.
iv.
PhoneNumber- This attribute is used to store a telephone for the patient in the
patient table. It has is set as an Integer.
2
v.
D.O.B- This attribute is used to store the date of birth for the patient in the patient
table. It has an integer datatype.
b. Physician – This entity is used to store physicians’ records in the system. The patient
entity has the following attribute and attributes description.
i.
Physician_ID- This attribute is used to store a Physician ID for the physician in
the physician table. It has an integer attribute description. It is also the primary
key for the entity physicians.
ii.
Name- This attribute is used to store the name for the physicians in the physician
table. The attribute type is Varchar with Length 25 description.
iii.
Position- This attribute is used to store the position for the physician in the
physician table. It has a Varchar data type and 50 lengths.
iv.
Patient_ID- This attribute is used to store a Patient ID for the pin in the physician
table. It has an integer attribute description. It is also a foreign key for the entity
physician.
v.
Location- This attribute is used to store the physician’s location in the physician
table; it is a Varchar attribute type and length of 50.
c. Nurse- This entity is used to store nurses’ records in the system. The nurse entity has the
following attribute and attributes description.
i.
Nurse_ID- This attribute is used to store a Nurse ID for the nurse in the nurse
table. It has an integer attribute description. It is also the primary key for the
entity nurse.
ii.
Name- This attribute is used to store the name for the nurse in the nurse table. It
has a Varchar data type with a length of at least 25 characters.
3
iii.
Address- This attribute is used to store the address for the nurse in the nurse table.
It has a Varchar data type and Boolean attribute description.
iv.
PhoneNumber- This attribute is used to store a phone number for the nurse in the
nurse table. It has an integer attribute description.
v.
PatientName- This attribute is used to store a patient Name for the nurses in the
nurse table. It has a data type of Varchar with a length of 25 descriptions.
d. Payment- This entity is used to store payment’ records in the system. The payment entity
has the following attribute and attributes description.
i.
Payment_ID – This attribute is used to store a payment I.D. for the payment in the
payment table. It has an integer attribute description. It is also the primary key for
the entity payment.
ii.
Amount- This attribute is used to store the payment amount in the payment table.
It has an integer datatype.
iii.
Description – This attribute is used to store the description for the payment in the
payment table. It has a varchar attribute description and length of 50 characters.
iv.
PaymentType – This attribute is used to store the type of payment in the payment
table. It has a Varchar attribute description and a length of 50.
v.
Date – This attribute is used to store the date for the payment in the payment
table. It has a date attribute description.
vi.
Patient_ID – This attribute is used to store a Patient ID for the pin in the patient
table. It has an integer attribute description. It is also a foreign key for entity
payment.
4
e. Drugs – This entity is used to store drugs’ records in the system. The drugs entity has the
following attribute and attributes description.
Drug_ID – This attribute is used to store a drug I.D. for the drug in the drug table.
i.
It has an integer attribute description. It is also the primary key for the entity drug.
DrugName – This attribute is used to store the name of the drug in the drug table.
ii.
It has a Varchar attribute description and a 25 length of characters.
iii.
Quantity – This attribute is used to store the quantity for the drug in the drug
table. It has an integer attribute description.
Description – This attribute is used to store a description of the drugs in the drugs
iv.
table. It has a Varchar attribute description and 50 lengths of description.
ExpirationDate – This attribute is used to store the expiration date for the drugs in
v.
the drugs table. It has a date attribute description.
vi.
PatientName – This attribute is used to store a Patient Name in the patient table. It
has a Varchar with a length of 25 attribute descriptions.
Relationship and Cardinality Description
i.
Patient Physician Information- It is optional for patients to have many physicians, and
it is also optional for physicians to have many patients.
ii.
Patient Payment Plan- It is optional to have many payments for patients, but it is
mandatory to have many payments for patient
iii.
Physician Drug Giver- It is mandatory for physicians to prescribe at least one drug,
and it is also mandatory for one drug to be prescribed by a physician.
iv.
Patient Drug Information- It is optional for the patient to have many drugs, and a drug
is optional to be given to many patients.
5
v.
Patient Seeing Nurse: It is mandatory for the patient to see one nurse in the clinic, but
it is optional for the nurse to see many patients.
Assumptions and Special Considerations
•
Everyone entering the hospital is patient, nurse, or physician.
•
The users have the valid required information for the system.
1. DROP TABLE PAYMENT CASCADE CONSTRAINTS;
➢ TABLE DROPPED.
DROP TABLE PRACTIONER CASCADE CONSTRAINTS;
➢ TABLE DROPPED.
DROP TABLE DRUGS CASCADE CONSTRAINTS;
➢ TABLE DROPPED.
DROP TABLE NURSE CASCADE CONSTRAINTS;
➢ TABLE DROPPED.
DROP TABLE SICKPATIENT CASCADE CONSTRAINTS;
➢ TABLE DROPPED.
DROP SEQUENCE PAYMENT_ID_SEQ;
➢ Sequence PAYMENT_ID_SEQ dropped.
DROP SEQUENCE PRACTIONER_ID_SEQ;
➢ Sequence PRACTIONER_ID_SEQ dropped.
DROP SEQUENCE DRUG_ID_SEQ;
➢ Sequence DRUG_ID_SEQ dropped.
DROP SEQUENCE NURSE_ID_SEQ;
➢ Sequence NURSE_ID_SEQ dropped.
DROP SEQUENCE SICKPATIENT_ID_SEQ;
➢ Sequence SICKPATIENT_ID_SEQ dropped.
2 –PROMPT Creating Table SICKPATIENT’
CREATE TABLE sickpatient (
sickpatient_Id INT PRIMARY KEY,
name VARCHAR(25),
address VARCHAR(50),
phone VARCHAR(15));
➢ Table SICKPATIENT created.
COMMENT ON TABLE sickpatient IS ‘Profile information for a sickpatient.’
/
COMMENT ON COLUMN SICKPATIENT.SICKPATIENT_ID IS ‘The unique ID for a sickpatient.’
/
COMMENT ON COLUMN SICKPATIENT.NAME IS ‘The sickpatient name.’
/
COMMENT ON COLUMN SICKPATIENT.ADDRESS IS ‘The sickpatient street address.’
/
COMMENT ON COLUMN SICKPATIENT.PHONE IS ‘The phone number for the sick patient.’
/
–PROMPT Creating Table ‘NURSE’
CREATE TABLE nurse (
nurse_id INT PRIMARY KEY,
name VARCHAR(25),
address VARCHAR(50),
phone VARCHAR(15),
patient_name VARCHAR (25));
➢ Table NURSE created.
COMMENT ON TABLE NURSE IS ‘Profile information for a NURSE.’
/
COMMENT ON COLUMN NURSE.NURSE_ID IS ‘The unique ID for a NURSE.’
/
COMMENT ON COLUMN NURSE.NAME IS ‘This NURSE”s name.’
/
COMMENT ON COLUMN NURSE.ADDRESS IS ‘This NURSE”s street address.’
/
COMMENT ON COLUMN NURSE.PHONE IS ‘The phone number for this NURSE.’
/
COMMENT ON COLUMN nurse.patient_name IS ‘The NURSE”s patient name’
/
–PROMPT Creating Table ‘DRUGS’
CREATE TABLE drugs(
drug_id INT PRIMARY KEY,
name VARCHAR(25),
quantity INT,
description VARCHAR(80),
patient_name VARCHAR(25));
➢ Table DRUGS created.
COMMENT ON TABLE DRUGS IS ‘Information for DRUGS.’
/
COMMENT ON COLUMN DRUGS.DRUG_ID IS ‘The unique ID for a DRUG.’
/
COMMENT ON COLUMN DRUGS.NAME IS ‘The DRUG”s name.’
/
COMMENT ON COLUMN DRUGS.QUANTITY IS ‘This DRUG”s quantity.’
/
COMMENT ON COLUMN DRUGS.DESCRIPTION IS ‘This DRUG”s description.’
/
COMMENT ON COLUMN DRUGS.PATIENT_NAME IS ‘This DRUG”s patient.’
–PROMPT Creating Table ‘PHYSICIAN’
CREATE TABLE practioner(
practioner_id INT PRIMARY KEY,
name VARCHAR(25),
posititon VARCHAR (30),
patient_ID INT,
location VARCHAR(50));
➢ Table PRACTIONER created.
COMMENT ON TABLE practioner IS ‘Information for practioner.’
/
COMMENT ON COLUMN practioner.practioner_id IS ‘The unique ID for a practioner.’
/
COMMENT ON COLUMN practioner.name IS ‘This practioner”s name.’
/
COMMENT ON COLUMN practioner.posititon IS ‘This practioner”s position.’
/
COMMENT ON COLUMN practioner.location IS ‘This practioner”s location.’
/
COMMENT ON COLUMN practioner.PATIENT_ID IS ‘This practioner”s patient’
–PROMPT Creating Table ‘PAYMENT’
CREATE TABLE payment(
payment_id INT PRIMARY KEY,
patient_id INT,
amount INT,
payment_date DATE,
description VARCHAR(80),
payment_type VARCHAR(20));
➢ Table PAYMENT created.
COMMENT ON TABLE PAYMENT IS ‘Information for PAYMENT.’
/
COMMENT ON COLUMN PAYMENT. PAYMENT_ID IS ‘The unique ID for a PAYMENT.’
/
COMMENT ON COLUMN PAYMENT.AMOUNT IS ‘The payment amount.’
/
COMMENT ON COLUMN PAYMENT.DESCRIPTION IS ‘The payment description.’
/
COMMENT ON COLUMN PAYMENT.PAYMENTTYPE IS ‘The payment type.’
/
COMMENT ON COLUMN PAYMENT.AMOUNT IS ‘The payment amount.’
/
COMMENT ON COLUMN PAYMENT.PAYMENTDATE IS ‘The payment date.’
/
COMMENT ON COLUMN PAYMENT.PATIENT_ID IS ‘The patient paying.’
3
— constraints
–PROMPT Creating Check Constraints on ‘SICKPATIENT’
ALTER TABLE SICKPATIENT
MODIFY(SICKPATIENT_ID CONSTRAINT SIC_PATIENT_ID_NULL NOT NULL)
/
➢ Table SICKPATIENT altered.
–PROMPT Creating Check Constraints on ‘PHYSICIAN’
ALTER TABLE PRACTIONER
MODIFY(PRACTIONER_ID CONSTRAINT PRA_PRACTIONER_ID_NULL NOT NULL)
/
➢ Table PRACTIONER altered.
–PROMPT Creating Check Constraints on ‘PAYMENT’
ALTER TABLE PAYMENT
MODIFY(PAYMENT_ID CONSTRAINT PAY_PAYMENT_ID_NNULL NOT NULL)
/
➢ Table PAYMENT altered
–PROMPT Creating Check Constraints on ‘NURSE’
ALTER TABLE NURSE
MODIFY(NURSE_ID CONSTRAINT NUR_NURSE_ID_NNULL NOT NULL)
/
➢ Table NURSE altered.
–PROMPT Creating Check Constraints on ‘DRUGS’
ALTER TABLE DRUGS
MODIFY(DRUG_ID CONSTRAINT DRG_DRUG_ID_NNULL NOT NULL)
/
➢ Table DRUGS altered.
–PROMPT Creating Primary Key on ‘PATIENT’
ALTER TABLE PATIENT
ADD CONSTRAINT PAT_PK PRIMARY KEY
(PATIENT_ID)
/
–PROMPT Creating Primary Key on ‘NURSE’
ALTER TABLE NURSE
ADD CONSTRAINT NUR_PK PRIMARY KEY
(NURSE_ID)
/
–PROMPT Creating Primary Key on ‘DRUGS’
ALTER TABLE DRUGS
ADD CONSTRAINT DRU_PK PRIMARY KEY
(DRUG_ID)
/
–PROMPT Creating Primary Key on ‘PHYSICIAN’
ALTER TABLE PHYSICIAN
ADD CONSTRAINT PHY_PK PRIMARY KEY
(PHYSICIAN_ID)
/
–PROMPT Creating Primary Key on ‘PAYMENT’
ALTER TABLE PAYMENT
ADD CONSTRAINT PAY_PK PRIMARY KEY
(PAYMENT_ID)
/
–PROMPT Creating Foreign Keys on ‘NURSE’
ALTER TABLE NURSE ADD CONSTRAINT
NUR_DRU_FK FOREIGN KEY
(PATIENT_ID) REFERENCES PATIENT
(PATIENT_ID)
4 — sequences
–PROMPT Creating Sequence ‘SICKPATIENT_ID_SEQ’
CREATE SEQUENCE SICKPATIENT_ID_SEQ
INCREMENT BY 1
START WITH 15
NOMAXVALUE
MINVALUE 1
NOCYCLE
NOCACHE
➢ Sequence PATIENT_ID_SEQ created.
–PROMPT Creating Sequence ‘NURSE_ID_SEQ’
CREATE SEQUENCE NURSE_ID_SEQ
INCREMENT BY 1
START WITH 21
NOMAXVALUE
MINVALUE 1
NOCYCLE
NOCACHE
➢ Sequence NURSE_ID_SEQ created.
–PROMPT Creating Sequence ‘DRUG_ID_SEQ’
CREATE SEQUENCE DRUG_ID_SEQ
INCREMENT BY 1
START WITH 40
NOMAXVALUE
MINVALUE 1
NOCYCLE
NOCACHE
➢ Sequence DRUG_ID_SEQ created.
–PROMPT Creating Sequence ‘PRACTIONER_ID_SEQ’
CREATE SEQUENCE PRACTIONER_ID_SEQ
INCREMENT BY 1
START WITH 140
NOMAXVALUE
MINVALUE 1
NOCYCLE
NOCACHE
➢ Sequence PRACTIONER_ID_SEQ created.
–PROMPT Creating Sequence ‘PAYMENT_ID_SEQ’
CREATE SEQUENCE PAYMENT_ID_SEQ
INCREMENT BY 1
START WITH 65
NOMAXVALUE
MINVALUE 1
NOCYCLE
NOCACHE
➢ Sequence PAYMENT_ID_SEQ created.
5
— update payment type to credit card before an insert on payment table
— update payment_type to credit card before an insert on payment table
create or replace trigger payment_type
before insert on PAYMENT
begin
UPDATE payment
set payment_type=’Credit Card’;
end;
➢ COMPILED
— trigger to ensure autonumber after nurse sequence
create or replace trigger nurse_auto
before insert or update or delete on NURSE
for each row
declare
tmpVar NUMBER;
begin
if inserting then
select NURSE_ID_SEQ.NEXTVAL into tmpVar from dual;
:new.nurse_id := tmpVar;
end if;
end;
➢ COMPILED
6 — trigger to ensure autonumber after drug sequence
create or replace trigger drug_auto
before insert or update or delete on DRUGS
for each row
declare
tmpVar NUMBER;
begin
if inserting then
select DRUG_ID_SEQ.NEXTVAL into tmpVar from dual;
:new.drug_id := tmpVar;
end if;
end;
➢ COMPILED
/
— trigger to ensure autonumber after payment sequence
create or replace trigger payment_auto
before insert or update or delete on PAYMENT
for each row
declare
tmpVar NUMBER;
begin
if inserting then
select PAYMENT_ID_SEQ.NEXTVAL into tmpVar from dual;
:new.payment_id := tmpVar;
end if;
end;
➢ COMPILED
7 — insert statements
ALTER SESSION SET NLS_DATE_FORMAT = ‘DD-MON-RR’;
➢ Session altered.
INSERT INTO nurse (name,address,phonenumber,patientname) VALUES (‘Mike Lowry’,’101-09
120th St.’, 71855555, ‘Dennis Citto’);
INSERT INTO nurse (name,address,phonenumber, patientname) VALUES (‘Janet Bryant’,’155
Union Ave #211′, 18455555, ‘Lynn Lamar’);
INSERT INTO nurse (name,address,phonenumber, patientname) VALUES (‘Jane Vardy’,’29
Elmwood Ave.’, 31926555, ‘Angel DiMaria’);
INSERT INTO nurse (name,address,phonenumber, patientname) VALUES (‘Jude Sanchez’,’51-76
Van Kleeck St.’, 21245595, ‘Judith Noviello’);
INSERT INTO nurse (name,address,phonenumber, patientname) VALUES (‘Ben Fernandes’,’322
Atkins Ave.’, 32900055, ‘Mohamed Abdul’);
commit;
➢ Commit complete.
INSERT INTO practioner (name,position,patient_id,location) VALUES (‘Fernand Hanks’,’Chief
Physician’,15, ‘Baltimore’);
INSERT INTO practioner (name,position,patient_id,location) VALUES (‘Nina Schorin’,’Assistant
Physician’, 16, ‘New York’);
INSERT INTO practioner (name,position,patient_id,location) VALUES (‘Anita Morris’,’Resident
Physician’,17, ‘Houston’);
INSERT INTO practioner (name,position,patient_id,location) VALUES (‘Charles Lowry’,’Chief
Physician’,18, ‘Dallas’);
INSERT INTO practioner (name,position,patient_id,location) VALUES (‘Irene Willig’,’Resident
Physician’, 19, ‘Austin’);
commit;
➢ Commit complete.
INSERT INTO payment (amount,description,payment_type,payment_date,patient_id) VALUES
(200, ‘Medicine’, ‘Credit card’,TO_DATE(’25-MAR-2020 10:18:00′,’DD-MON-YYYY
HH24:MI:SS’),15);
INSERT INTO payment(amount,description,payment_type,payment_date,patient_id) VALUES
(350, ‘Treatment’, ‘Cash’,TO_DATE(’25-MAY-2020 10:18:00′,’DD-MON-YYYY HH24:MI:SS’),16);
INSERT INTO payment (amount,description,payment_type,payment_date,patient_id) VALUES
(250, ‘Consultancy’, ‘Credit card’,TO_DATE(‘5-JUN-2020 10:18:00′,’DD-MON-YYYY
HH24:MI:SS’),17);
INSERT INTO payment (amount,description,payment_type,payment_date,patient_id) VALUES
(300, ‘Medical bill’, ‘Credit card’,TO_DATE(‘4-FEB-2020 10:18:00′,’DD-MON-YYYY
HH24:MI:SS’),18);
INSERT INTO payment (amount,description,payment_type,payment_date,patient_id) VALUES
(150, ‘Consultancy’, ‘Cash’,TO_DATE(‘2-JAN-2020 10:18:00′,’DD-MON-YYYY HH24:MI:SS’),19);
commit;
➢ Commit complete.
INSERT INTO drugs (name,quantity,description,patient_name) VALUES (‘Aspirin’,10, ‘Pain
reliever’, ‘Mohamed Abdul’);
INSERT INTO drugs (name,quantity,description,patient_name) VALUES (‘Insulin’,15, ‘Diabetes’,
‘Judith Noviello’);
INSERT INTO drugs (name,quantity,description,patient_name) VALUES (‘Antibiotics’,20, ‘Bacteria
reducer’, ‘Angel DiMaria’);
INSERT INTO drugs (name,quantity,description,patient_name) VALUES (‘Amoxicillin’,10, ‘Pain
reliever’, ‘Lynn Lamar’);
INSERT INTO drugs (name,quantity,description,patient_name) VALUES (‘Ativan’,10, ‘Pain
reliever’, ‘Dennis Citto’);
commit;
➢ Commit complete.
8 — view of subject count
— view of subject count
CREATE OR REPLACE VIEW sickpatient_count AS
SELECT COUNT(*) as count
FROM sickpatient;
➢ View SICKPATIENT_COUNT created.
— view of all drug records
CREATE OR REPLACE VIEW drug_records AS
SELECT *
FROM DRUGS;
➢ View DRUG_RECORDS created.
9. Output on querying view:
10. Output on querying view:
Overview
The GrandMed hospital offers specialized medical facilities offering unique cancer
screening services, laboratory testing, and organ transplant services. While the hospital has
sophisticated testing devices that could be conveniently programmed to transmit patient
outcomes to a computer, the hospital also depends on manually transcribed issues from its
laboratories; this slows the process leading to long waiting times for the patients as they wait to
pass their results from one department to the next.
Executive Summary
A database needs to be created to store patient details to increase personal efficiency by
reducing waiting times. Computerizing the system will also streamline the processes and help
physicians track patient details easily by lowering the laboratory technicians’ need to engage in
tedious data entry and transcriptions. This reduces prices, increases processing times for the test
procedures. Besides, system computerization will also help to minimize omission and
commission errors. However, the patient details have to be entered into the database through a
barcode that has been linked to the patient’s electronic records. The barcode will be essential for
tracking records of the patient in the database. Therefore, the barcode will provide the medics
with an easier means of accessing patient records via database queries.
Purpose of the Project
This project will play a primary role in developing a database for storing patient
information. The database will interact directly with servers of applications that will receive
electronic data from the patient. Hence, some of the database’s minor goals are to protect private
patient data, improve hospital efficiency, reduce costs, and store larger amounts of data.
Project scope
This database is designed for the hospital, clinic, or dispensary operationalization.
Therefore, it will implement a high-level information system to handle the duties of the hospital,
as it will replace the currently in use manual paper-based system.
Work within this scope
•
Database system creation to store input
•
Create DML scripts that stimulate database interactions with other IT systems.
Work outside this scope
Some work outside this scope includes creating and sending patient records to the
diagnostic laboratory application servers to interact with records of patients and health care
providers.
Database goals deliverables and expectations
The aims include receiving information from the patient, storing results, and generating
reports that are then sent to the physicians for examination. The deliverables include simulation
ERD diagram scripts and table structures.
Benefits
Many of the project’s advantages include quick access to medical information, expense
savings, decreased risk of error, and improved data protection.
Hardware and software
•
Xxx University (VDA)
•
Intel(R) Xeon(R).
•
Windows 10 Operating system
Diagram Tool
The project will use Microsoft Visio 2013
Database
Oracle 11g, with VDA (Virtual Desktop Application)
DDL and DML
The project will use both DDL and DML to define the database tables and perform other
functions such as inserting, deleting, and updating.
SQL and style guide use
SQL layout must be standardized and written, as they would be, for easy readability;
1. Broken down into simpler statements
2. It will have enough white s…
Purchase answer to see full
attachment