Question

Hospital Patient Reglstration System Hospltal Patient Registration System Part 2: Relational Schema: Description of Tables 1) 2) Patient Table: Contains detail information about patients. Patient Address: Contains list of patients addresses. Each Patient can have more than one Address. However, an address can belong to only one patient. DOD DATETM 3) Provider Table: Contains detail information about Service Provider's, 4) Provider Address: Contains list of provider's addresses. 5) Provider Department: Contains information about which provider are in which department. Provider Schedule: Contains provider work schedule records. 6) Provider iditN VARCHAR,粉 . Each Provider can have more than one Provider Address, can be a member of more than one provider department, and can have more than Provider work schedule in a certain period However, a provider address, provlder department, and provider work schedule can belong to only one Provider 7) Payor Table: Contain Patient insurance information. .Each patient can have more than one Payor. However, Payor can be belonging to only to one Patlent a) Diagnosis Group Table: Contain the list of diagnosis kind or type. 9) List of Diagnosis Table: Contain list of diagnosis that belong in each diagnosis's type and price. .In Each Diagnosis group can have more one kind of diagnosis. However a list of Diagnosis can belong to only one Diagnosis type. 10)Task Process Table: Contain detail information about what kind of service 008 CATETIME that requested by the patient. In Each Task process can have (might request) more than one kind of diagnosis. And also, In each diagnosis can have more than list of diagnasis. However, each diagnosis and diagnosis list belong to specific requester patient In this part of the project you are required to implement your database on MySQL. Your implementation should include the following: 1- Create all the tables that you specified in your relational schema (Part 2, of Step 2). Make sure to include primary keys and foreign keys 2- Populate all tables with data of your choice. Make sure that each table includes at least 10 rows Design and clearly explain at least ten query scenarios that may be useful on your database. You may include the scenarios or questions you described in Step 1 question#4 Write SQL syntax to answer each query. A query like this: select count(*) from tableOne, can only be used as one of the ten. . . .Strongly suggested to include some queries using Table Joins, Aggregates (at least 2) Group By, Order By, and Having (In other words have a good variety that incorporates the material we have been learning) At least one query must use a view (that you created from your application tables) in the FROM clause Note: the SELECT used for in the creation of the view, does not count as one of the 10 queries. What to submit: 1- A word document to explain your query scenarios. Think about the business that you are representing for these scenarios. Also need to create SQL syntax to answer these queries a. SQL statements to create the tables (from Part 1 above) b. SQL statements to populate tables with data (from Part 1 above) c. SQL statements for your query scenarios (from Part 2) d. SQL used to create the view (from Part 2)

Answer 1

Answer a) some of the attributes is not clear in the image so i have not included in table column.

CREATE TABLE Patient (
PatientID int ,
PayerId int,
FirstName varchar(255),
LastName varchar(255),
Gender varchar(255),
Phonenumber varchar(255),
PRIMARY KEY (PatientID),
FOREIGN KEY (PayerId) REFERENCES Payer(PayerId)
);
--------------------------------------------------------------
CREATE TABLE Payer (
PayerId int,
PayerCode varchar(255),
PayerName varchar(255),
PRIMARY KEY (PayerId)
);
----------------------------------------------------------
CREATE TABLE PatientAddress (
PatientID int ,
PatientAddress varchar(255),
City varchar(255),
State varchar(255),
zipcode int,
AssignmentNumber int,
FOREIGN KEY (PatientID ) REFERENCES Patient(PatientID)
);
--------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE Provider (
ProviderID int ,
DeptId int,
Provider_FirstName varchar(255),
Provider_LastName varchar(255),
DOB date,
salary double,
PRIMARY KEY (ProviderID),
FOREIGN KEY (DeptId) REFERENCES Department(DeptId )
);
-------------------------------------------------------------
CREATE TABLE Department(
DeptId int,
Discription varchar(255),
specialization varchar(255),
PRIMARY KEY (DeptId)
);
--------------------------------------------------------------------------
CREATE TABLE ProviderAddress (
ProviderID int,
ProviderStreet varchar(255),
City varchar(255),
State varchar(255),
zipcode int,
FOREIGN KEY (ProviderID) REFERENCES Provider(ProviderID)
);
-----------------------------------------------------------------------------
CREATE TABLE ProviderSchedule (
ProviderID int NOT NULL,
startDate date,
startime time,
endtime time,
FOREIGN KEY (ProviderID) REFERENCES Provider(ProviderID)
);
---------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------

CREATE TABLE diagnosisGroup(
diagnosisID int,
Discription varchar(255),
diagnosisName varchar(255),
PRIMARY KEY (diagnosisID)
);
----------------------------------------------------------------------------

CREATE TABLE diagnosisList(
diagnosisID int,
diagnosisListID int,
diagnosisListName varchar(255),
diagnosisPrice int,
PRIMARY KEY (diagnosisListID ),
FOREIGN KEY (diagnosisID ) REFERENCES diagnosisGroup(diagnosisID )
);

--------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------

TASK PROCESS TABLE

CREATE TABLE TASKProcess(
TASKProcessID int,
diagnosisID int,
PatientID int,
diagnosisListID int,
DateOFProcess DATE,
PRIMARY KEY (TASKProcessID ),
FOREIGN KEY (diagnosisID ) REFERENCES diagnosisGroup(diagnosisID ),
FOREIGN KEY (PatientID) REFERENCES Patient(PatientID ),
FOREIGN KEY (diagnosisListID ) REFERENCES diagnosisList(diagnosisListID)
);

mysql show tables; i Tables_in_hospital department diagnosisgroup i diagnosislist ordePs patient patientaddress payer provider providersc he duie providerschedule i taskprocess ii rove in set <e.01 sec)

i 1 i i 1 mysqǐ〉 desc patient; Field Type Null Key DefaultExtra PatientIDint (11 PayerId FirstNamevarchar 255 YES Last Name Gender Phonenumbervarchar 255)YES NO PRINULL YES MULNULL NULL NULL NULL NULL int 11 : varchar(255〉 varchar(255〉 YES YES : 6 rows in set .06 sec) mysql> desc payer; Field Type Null Key DefaultExtra PayerId int(11> I PayerCode | varchar〈255〉 | YES I PayerNanel varchar 255) YES ! NO PRINULL NULL NULL 3 rows in set 0.01 sec mysql> desc patientaddress Field PatientID State AssignmentNumber int11> Type Null Key DefaultExtra int 11 YES MULNULL NULL NULL NULL NULL NULL PatientAddressuarchar 255 YES : : : City i zipcode 6 rows in set .01 sec) mysql〉 varchar(255〉 varchar〈255〉 YES YES YES YES int 11

*************************************************************************************************************************************

Answer b)

i mysql> select * from patient; PatientID PayerId i FirstName LastNameGender Phonenumber 100 Gaurau 104 Renu 100 Kancharn 100 MD 103 Ricky 103Shubham MishraM 101D 101 Sita 101 kana 101 sripujan Mishra M 8886357571 8896566984 11234567898 11234567898 11234567898 11234567898 11234567898 11234567898 11234567898 11234567898 Kanchan Mishra 10 1 rows in set (.0 sec> mysql select * from payer PayerId PayerCode PayerName 100 MIS100 101 MIS101 102MIS102 103MIS103 104 MIS104 DinaNath Mishra Ganga Mishra angotri Mishr shra S N Mishra 5 rows in set .04 sec) mysql> select * from patientaddress; PatientID PatientAddress City State zipcode AssignmentNumber 1 167 2 I AwasUikas 24Basti 3 167 4 167 5 AwasUikas 24Basti 6 IAwasUikas 24Basti i gorakhpur UP UP i gorakhpur UP i gorakhpur UP UP UP i gorakhpur UP UP UP UP 273016 293016 273016 273016 293016 293016 273016 273016 273016 273016 50 51 52 54 56 57 58 59 60 167 8 167 9 169 LKO LKO LKO 10 169 1 rows in set (.0 sec>

******************************************************************************************************************************************

Answer c)

Below is basic query

nysql select count*from patient; count< 10 1 row in set 〈0.00 sec nysql select FirstName from patient where PatientID -10; FirstName i sripujan 1 row in set 〈0.00 sec

SELECT * FROM patientaddress
WHERE city IN ('basti');

nysql select from patientaddress where city in("basti"; iPatientID PatientAddress City State zipcode AssignmentNumber 51 56 2 AwasUikas 5 AwasUika 6 AwasU asUikas 24Basti ! up Basti UP Uikas 24I Basti UF 293016 293016 293016

query using like keyword

SELECT * FROM patient
WHERE FirstName LIKE 'a%';

inner join query

SELECT Patient.FirstName,Patient.PatientID,Payer.Name
FROM patient,payer
INNER JOIN ON patient.patientID= payer.PatientID;

groupBy clause

SELECT count(*)
FROM patientaddress
WHERE patient id <500
GROUP BY city;

***********************************************************************************************************************

Answer d) create view example

In SQL, a view is a virtual table based on the result-set of an SQL statement.

A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.

CREATE VIEW PatientDetail
SELECT FirstName, LastName,PatientID
FROM Patient
WHERE PatientID= 100;

Now the PatientDetail will be view table

SELECT * FROM PatientDetail ;

*********************************************************************************************************************************************