A SQL database design for hospital management and simulate the followings #DBMS4

#PROBLEM STATEMENT

Patients are identified by an SSN, and their names, addresses, and ages must be recorded. Doctors are identified by an SSN. For each doctor, the name, specialty, and years of experience must be recorded. Each pharmaceutical company is identified by name; it has an address and one phone number. For each drug, the trade name and formula must be recorded. Each drug is sold by a given pharmaceutical company, and the trade name identifies a drug uniquely from among the products of that company. Each pharmacy has a name, address, and phone number. Each patient is checked up by some doctor. Every doctor has at least one patient. Each pharmacy sells several drugs and has a price for each. A drug could be sold at several pharmacies, and the price could vary from one pharmacy to another. Doctors prescribe drugs for patients. A doctor could prescribe one or more drugs for several patients, and a patient could obtain prescriptions from several doctors. Each prescription has a date and a quantity associated with it. Pharmaceutical companies have long-term contracts with pharmacies. A pharmaceutical company can contract with several pharmacies, and a pharmacy can contract with several pharmaceutical companies. For each contract, you have to store a start date, an end date, supervisor and the text of the contract.

Queries:

a) List the details of patients who are 20 years old and have been checked by eye-specialist.

 b) List the details of doctors who have given the prescription to more than 20 patients in year 2013.

c) List the details of pharmaceutical companies who supply drug to more than 10 pharmacies in the same city where company is located.

d) List the details of drug supplied by only one pharmaceutical company.

 e) List the details of drug supplied by all pharmaceutical companies.

…………………………………..CREATE TABLES…………………………………………..

create table pharma_company

(

            pc_name                      varchar(20),

            pc_address                  varchar(20),

            pc_city                        varchar(20),

        pc_ph_no                        number(10),

        primary key(pc_name)

);

create table pharmacy

(

            pname                          varchar(20),

            paddress                      varchar(30),

            pcity                            varchar(20),

            p_ph_no                                  number(10),

            primary key(pname)

);

create table drugs

(

            trade_name                 varchar(20)      primary key,

            formula                        varchar(20)

);

create table doctor

(

            dssn                             number(10)      primary key,

            dname                         varchar(20),

            speciality                     varchar(20),

            yoe number(3)

);

create table patient

(

            pt_ssn                          number(10)      primary key,

            pt_name                                  varchar(20),

            pt_address                   varchar(20),

            pt_age                         number(3)

);

create table checks

(

            dssn     number(10)      references        doctor,

            pt_ssn  number(10)      references        patient,

            primary key(dssn,pt_ssn)

);

create table sells

(

            trade_name                 varchar(20)      references        drugs,

            pname              varchar(20)                  references        pharmacy,

            price                number(10),

            primary key(trade_name,pname)

);

create table manufacture

(

            trade_name     varchar(20)      references        drugs,

            pc_name                      varchar(20)      references        pharma_company,

            primary key(trade_name,pc_name)

);

create table prescribes

(

            dssn                 number(10)      references        doctor,

            pt_ssn              number(10)      references        patient,

            trade_name     varchar(20)      references        drugs,

            qty                   number(10),

            pre_date          number(5)       

);

create table contract

(

            pc_name          varchar(20)      references        pharma_company,

            pname              varchar(20)      references        pharmacy,

            st_date            date,

            end_date         date,

            supervisor        varchar(20),

            context            varchar(20)

);

……………………………………INSERT INTO………………………….

insert into pharma_company values ('Drug pharm','Goaves','Belgaum',2531456);

insert into pharma_company values ('Bio pharm','Angol','Belgaum',2487336);

insert into pharma_company values ('Med plus pharm','Bhagyanagar','Hubli',2617890);

insert into pharma_company values ('Micro labs','RPD Cross','Dharwad',2533645);

insert into pharma_company values ('Care labs','Jyothi colony','Dharwad',2987634);

insert into pharmacy values('Dada','Shivaji Nagar','Bangalore',2413698);

insert into pharmacy values('Deepak','RC Nagar','Belgaum',23641);

insert into pharmacy values('Daneshwari','Rajiv Nagar','Dharwad',2413698);

insert into pharmacy values('Kalyanshetti','College Road','Belgaum',2413698);

insert into pharmacy values('Katti','MG Road','Belgaum',2413698);

insert into pharmacy values('Apollo','College Road','Belgaum',2498784);

insert into pharmacy values('Shravan','Yamanapur','Belgaum',2410369);

insert into pharmacy values('Shetty','Kaktivaes','Belgaum',2674859);

insert into pharmacy values('Laxmi','Khade Bazar','Belgaum',28412);

insert into pharmacy values('Sai ram','RPD Cross','Belgaum',2574123);

insert into pharmacy values('KLE','KLE Hospital','Belgaum',228);

insert into pharmacy values('Guru','Shivabasav Nagar','Belgaum',24423);

insert into pharmacy values('Jeeva','Vadagaon','Belgaum',25384);

insert into pharmacy values('Jeevan','SV Road','Belgaum',2413398);

insert into pharmacy values('Ramdev','Dharma circle','Belgaum',2413392);

//

insert into contract values('Drug pharm','Dada','21-Apr-05','12-Sep-15','LKO','a');

insert into contract values('Drug pharm','Deepak','12-Sep-08','15-Nov-18','WER','b');

insert into contract values('Drug pharm','Kalyanshetti','15-Nov-10','10-Jun-20','PKL','c');

insert into contract values('Drug pharm','Shravan','01-Jul-13','10-Jun-25','RAJ','d');

insert into contract values('Drug pharm','Shetty','10-Jun-13','18-Dec-25','WAS','e');

insert into contract values('Drug pharm','Laxmi','30-Nov-06','24-Aug-26','JKL','f');

insert into contract values('Drug pharm','Sai ram','18-Dec-09','18-Dec-14','MNO','g');

insert into contract values('Drug pharm','KLE','19-Aug-10','27-Mar-15','TUV','h');

insert into contract values('Drug pharm','Guru','13-Feb-11','20-Oct-21','RAM','i');

insert into contract values('Drug pharm','Jeeva','14-Jan-14','19-Dec-23','VRS','j');

insert into contract values('Drug pharm','Jeevan','27-Mar-08','29-Jun-18','VKS','k');

insert into contract values('Med plus pharm','Katti','31-May-05','20-Jan-15','ZMD','l');

insert into contract values('Med plus pharm','Medplus','11-Nov-02','01-Jul-32','ASD','m');

insert into contract values('Bio pharm','Shiva','20-Oct-92','05-Jan-15','XYZ','n');

insert into contract values('Bio pharm','Shetty','19-Dec-13','23-Dec-24','VWX','o');

insert into contract values('Micro labs','Shravan','27-Aug-11','27-Aug-16','STU','p');

insert into contract values('Micro labs','Laxmi','29-Jun-08','13-Jul-15','PQR','q');

insert into contract values('Care labs','Katti','26-Feb-04','20-Feb-14','MNO','r');

insert into contract values('Care labs','Apollo','23-May-02','13-May-16','JKL','s');

insert into contract values('Drug pharm','Jeeva','20-Jan-10','20-Jan-20','GHI','t');

insert into contract values('Med plus pharm','Guru','21-Aug-12','21-Aug-22','DEF','u');

insert into contract values('Bio pharm','KLE','11-Apr-09','12-Apr-19','ABC','v');

insert into contract values('Drug pharm','Jeevan','21-Jan-10','25-Jan-20','GHZ','w');

insert into contract values('Drug pharm','Ramdev','26-Jan-10','29-Jan-20','GHX','x');

insert into contract values('Drug pharm','Shiva','31-Jan-10','12-Jan-20','GHI','z');

insert into drugs values('Sinarest','C3C04');

insert into drugs values('Paracetamol','PIBH2');

insert into drugs values('Dolophar','H3HO4');

insert into drugs values('Crocine','HC2O4');

insert into drugs values('Anasin','FEZO3');

insert into doctor values(1,'Shivganga','Eye',3);

insert into doctor values(2,'Pawar','Bone',5);

insert into doctor values(3,'Ravi','Ear',17);

insert into doctor values(4,'Tousif','Eye',32);

insert into doctor values(5,'Ajay','Heart',25);

insert into doctor values(6,'Gokhale','Skin',29);

insert into patient values(11,'Viraj','Rc Nagar Belgaum',25);

insert into patient values(12,'Suyash','Chavat Galli Belgaum',50);

insert into patient values(13,'Arjun','Ravivar peth Belgaum',22);

insert into patient values(14,'Rahim','Kanabargi Belgaum',24);

insert into patient values(15,'Vishal','RT Nagar Belgaum',23);

insert into patient values(16,'Raghu','Gandhi Colony Gadag',50);

insert into patient values(17,'Dixit','RPD Belgaum',20);

insert into patient values(18,'Ramesh','Goavaes Belgaum',20);

insert into patient values(19,'Suresh','Bhagya Nagar Belgaum',20);

insert into patient values(20,'Anand','Rc Nagar Belgaum',25);

insert into patient values(21,'Akash','Chavat Galli Belgaum',50);

insert into patient values(22,'Vijay','Ravivar peth Belgaum',22);

insert into patient values(23,'Veer','Kanabargi Belgaum',24);

insert into patient values(24,'Vishnu','RT Nagar Belgaum',23);

insert into patient values(25,'Nagaraj','Gandhi Colony Gadag',50);

insert into patient values(26,'Rajeev','RPD Belgaum',10);

insert into patient values(27,'Santosh','Goavaes Belgaum',17);

insert into patient values(28,'Samarth','Bhagya Nagar Belgaum',60);

insert into patient values(29,'Shrikanth','Rc Nagar Belgaum',30);

insert into patient values(30,'Zuber','Chavat Galli Belgaum',55);

insert into patient values(31,'Sahil','Ravivar peth Belgaum',42);

insert into patient values(32,'Sujan','Kanabargi Belgaum',34);

insert into patient values(33,'Vivek','RT Nagar Belgaum',32);

insert into patient values(34,'Veerendra','Gandhi Colony Gadag',30);

insert into patient values(35,'Ajay','RPD Belgaum',25);

insert into checks values(1,11);

insert into checks values(2,12);

insert into checks values(3,13);

insert into checks values(5,14);

insert into checks values(5,15);

insert into checks values(1,12);

insert into checks values(5,13);

insert into checks values(5,18);

insert into checks values(3,11);

insert into checks values(1,17);

insert into checks values(5,19);

insert into checks values(6,15);

insert into checks values(1,30);

insert into checks values(5,25);

insert into checks values(6,27);

insert into checks values(3,24);

insert into sells values('Crocine','Shravan',100);

insert into sells values('Sinarest','KLE',150);

insert into sells values('Paracetamol','Apollo',250);

insert into sells values('Anasin','Katti',0);

insert into sells values('Dolophar','Guru',500);

insert into manufacture values('Sinarest','Bio pharm');

insert into manufacture values('Sinarest','Drug pharm');

insert into manufacture values('Sinarest','Care labs');

insert into manufacture values('Crocine','Bio pharm');

insert into manufacture values('Crocine','Med plus pharm');

insert into manufacture values('Sinarest','Med plus pharm');

insert into manufacture values('Anasin','Bio pharm');

insert into manufacture values('Anasin','Drug pharm');

insert into manufacture values('Dolophar','Med plus pharm');

insert into manufacture values('Paracetamol','Micro labs');

insert into manufacture values('Sinarest','Micro labs');

insert into manufacture values('Anasin','Micro labs');

insert into manufacture values('Crocine','Micro labs');

//

insert into prescribes values(1,11,'Sinarest',2,2013);

insert into prescribes values(1,12,'Crocine',2,2013);

insert into prescribes values(1,13,'Paracetamol',5,2013);

insert into prescribes values(1,14,'Dolophar',3,2013);

insert into prescribes values(1,15,'Crocine',4,2013);

insert into prescribes values(1,16,'Anasin',3,2013);

insert into prescribes values(1,17,'Sinarest',10,2013);

insert into prescribes values(1,18,'Crocine',15,2013);

insert into prescribes values(1,19,'Dolophar',3,2013);

insert into prescribes values(1,20,'Paracetamol',8,2013);

insert into prescribes values(1,21,'Anasin',4,2013);

insert into prescribes values(1,22,'Sinarest',6,2013);

insert into prescribes values(1,23,'Dolophar',8,2013);

insert into prescribes values(1,24,'Paracetamol',3,2013);

insert into prescribes values(1,25,'Anasin',9,2013);

insert into prescribes values(1,26,'Crocine',10,2013);

insert into prescribes values(1,27,'Dolophar',6,2013);

insert into prescribes values(1,28,'Anasin',20,2013);

insert into prescribes values(1,29,'Crocine',9,2013);

insert into prescribes values(1,30,'Paracetamol',3,2013);

insert into prescribes values(1,31,'Sinarest',7,2013);

insert into prescribes values(2,26,'Dolophar',8,2013);

insert into prescribes values(3,24,'Paracetamol',3,2013);

insert into prescribes values(3,25,'Anasin',9,2014);

insert into prescribes values(5,23,'Crocine',10,2012);

insert into prescribes values(6,11,'Dolophar',6,2011);

insert into prescribes values(2,15,'Anasin',20,2013);

insert into prescribes values(3,23,'Crocine',9,2014);

insert into prescribes values(3,13,'Paracetamol',3,2011);

insert into prescribes values(5,18,'Sinarest',7,2014);

Queries:

1)

select pt.*

from patient pt,checks ch,doctor d

where pt.pt_ssn=ch.pt_ssn and ch.dssn=d.dssn and d.speciality='Eye';

    PT_SSN PT_NAME              PT_ADDRESS               PT_AGE

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

        11 Viraj                Rc Nagar Belgaum             25

        12 Suyash               Chavat Galli Belgaum         50

        17 Dixit                RPD Belgaum                  20

2)

select d.dssn,d.dname,count(*)

from doctor d,prescribes p

where d.dssn=p.dssn and p.pre_date=2013

group by d.dssn,d.dname

having count(*)>20;

      DSSN DNAME                  COUNT(*)

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

         1 Shivganga                    22

3)

select pc.pc_name,pc.pc_city,count(c.pname)

from pharma_company pc,contract c,pharmacy p

where c.pc_name=pc.pc_name and c.pname=p.pname and p.pcity=pc.pc_city

group by pc.pc_name,pc.pc_city

having count(c.pc_name)>=10;

PC_NAME              PC_CITY              COUNT(C.PNAME)

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

Drug pharm           Belgaum                          10

4)

select d.trade_name,m.pc_name

from drugs d,manufacture m

where m.trade_name=d.trade_name and d.trade_name in

(

select d.trade_name

from drugs d,manufacture m

where m.trade_name=d.trade_name

group by (d.trade_name,formula)

having count(m.pc_name)=1

);

TRADE_NAME           PC_NAME

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

Dolophar             Med plus pharm

Paracetamol          Micro labs

5)

select *

from drugs

where trade_name in

(

select d.trade_name

from drugs d,manufacture m

where d.trade_name=m.trade_name

group by d.trade_name

having count(m.pc_name)=(select count(*) from pharma_company)

);

TRADE_NAME           FORMULA

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

Sinarest             C3C04