ER Model For Hospital Management System:
1.Patient table as entity and pat_id,pat_name,pat_disease as fields:
SQL> conn scott/tiger
Connected.
SQL> create table patient(pat_id number(3),pat_name varchar2(11),pat_disease varchar2(22));
Table created.
SQL> desc patient
Name Null? Type
---------------------------------- -------- ----------------------------
PAT_ID NUMBER(3)
PAT_NAME VARCHAR2(11)
PAT_DISEASE VARCHAR2(22)
SQL> insert into patient values(101,'raju','tb');
1 row created.
SQL> insert into patient (pat_id,pat_disease) values(102,'malaria');
1 row created.
SQL> insert into patient values(&pat_id,'&pat_name','&pat_disease');
Enter value for pat_id: 103
Enter value for pat_name: rani
Enter value for pat_disease: dengue
old 1: insert into patient values(&pat_id,'&pat_name','&pat_disease')
new 1: insert into patient values(103,'rani','dengue')
1 row created.
SQL> /
Enter value for pat_id: 104
Enter value for pat_name: vani
Enter value for pat_disease: fever
old 1: insert into patient values(&pat_id,'&pat_name','&pat_disease')
new 1: insert into patient values(104,'vani','fever')
1 row created.
SQL> select * from patient;
PAT_ID PAT_NAME PAT_DISEASE
---------- ----------- ----------------------
101 raju tb
102 malaria
103 rani dengue
104 vani fever
SQL> update patient set pat_name='sita' where pat_id=102;
1 row updated.
SQL> select * from patient;
PAT_ID PAT_NAME PAT_DISEASE
---------- ----------- ----------------------
101 raju tb
102 sita malaria
103 rani dengue
104 vani fever
SQL> ttitle patient
SQL> select * from patient;
Tue Nov 08 page 1
patient
PAT_ID PAT_NAME PAT_DISEASE
---------- ----------- ----------------------
101 raju tb
102 sita malaria
103 rani dengue
104 vani fever
SQL> set colsep |
SQL> select * from patient;
Tue Nov 08 page 1
patient
PAT_ID|PAT_NAME |PAT_DISEASE
----------|-----------|----------------------
101|raju |tb
102|sita |malaria
103|rani |dengue
104|vani |fever
SQL> set underline =
SQL> select * from patient;
Tue Nov 08 page 1
patient
PAT_ID|PAT_NAME |PAT_DISEASE
======|===========|======================
101|raju |tb
102|sita |malaria
103|rani |dengue
104|vani |fever
SQL> drop table patient;
Table dropped.
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
-------------- ----------------------- ------------ -------------
PATIENT BIN$DIFMHBErRTe9I+nlpHbQdA==$0 TABLE 2022-11-08:17:13:08
SQL> select * from patient;
select * from patient
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> flashback table patient to before drop;
Flashback complete.
SQL> select * from patient;
Tue Nov 08 page 1
patient
PAT_ID|PAT_NAME |PAT_DISEASE
========|===========|====================
101|raju |tb
102|sita |malaria
103|rani |dengue
2.Doctor table as entity and doc_id,doc_name,pat_id as fields:
SQL> create table doctor(doc_id number(3),doc_name varchar2(11),pat_id number(3));
Table created.
SQL> desc doctor
Name Null? Type
------------------------------------- -------- -------------------------
DOC_ID NUMBER(3)
DOC_NAME VARCHAR2(11)
PAT_ID NUMBER(3)
SQL> insert into doctor values(201,'rk',101);
1 row created.
SQL> insert into doctor values(202,'pk',102);
1 row created.
SQL> insert into doctor values(203,'kk',103);
1 row created.
SQL> insert into doctor values(204,'ck',104);
1 row created.
SQL> ttitle doctor
SQL> select * from doctor;
Tue Nov 08 page 1
doctor
DOC_ID|DOC_NAME | PAT_ID
==========|===========|==========
201|rk | 101
202|pk | 102
203|kk | 103
204|ck | 104
3.Ward table as entity and ward_id,pat_id as fields:
SQL> create table ward(ward_id number(3),pat_id number(3));
Table created.
SQL> desc ward;
Name Null? Type
-------------------------------- -------- ----------------------------
WARD_ID NUMBER(3)
PAT_ID NUMBER(3)
SQL> insert into ward values(301,101);
1 row created.
SQL> insert into ward values(302,102);
1 row created.
SQL> insert into ward values(303,103);
1 row created.
SQL> insert into ward values(304,104);
1 row created.
SQL> ttitle ward
SQL> select * from ward;
Tue Nov 08 page 1
ward
WARD_ID| PAT_ID
==========|==========
301| 101
302| 102
303| 103
304| 104
SQL> create table nurse(nurse_id number(3),ward_id number(3),pat_id number(3));
Table created.
SQL> desc nurse;
Name Null? Type
---------------------------- -------- --------------------------
NURSE_ID NUMBER(3)
WARD_ID NUMBER(3)
PAT_ID NUMBER(3)
SQL> insert into nurse values(401,301,101);
1 row created.
SQL> insert into nurse values(402,302,102);
1 row created.
SQL> insert into nurse values(403,303,103);
1 row created.
SQL> insert into nurse values(404,304,104);
1 row created.
SQL> select * from nurse;
NURSE_ID WARD_ID PAT_ID
---------- ---------- ----------
401 301 101
402 302 102
403 303 103
404 304 104
SQL> ttitle nurse
SQL> select * from nurse;
Wed Nov 09 page 1
nurse
NURSE_ID WARD_ID PAT_ID
---------- ---------- ----------
401 301 101
402 302 102
403 303 103
404 304 104
SQL> set colsep |
SQL> select * from nurse;
Wed Nov 09 page 1
nurse
NURSE_ID| WARD_ID| PAT_ID
----------|----------|----------
401| 301| 101
402| 302| 102
403| 303| 103
404| 304| 104
SQL> set underline =
SQL> select * from nurse;
Wed Nov 09 page 1
nurse
NURSE_ID| WARD_ID| PAT_ID
==========|==========|==========
401| 301| 101
402| 302| 102
403| 303| 103
404| 304| 104
Queries:
SQL> ttitle "Hospital Management System"
SQL> select * from patient;
SQL> select * from patient;
Wed Nov 09 page 1
Hospital Management System
PAT_ID|PAT_NAME |PAT_DISEASE
==========|===========|======================
101|raju |tb
102|sita |malaria
103|rani |dengue
104|vani |fever
SQL> select * from doctor;
Wed Nov 09 page 1
Hospital Management System
DOC_ID|DOC_NAME | PAT_ID
==========|===========|==========
201|rk | 101
202|pk | 102
203|kk | 103
204|ck | 104
SQL> select * from ward;
Wed Nov 09 page 1
Hospital Management System
WARD_ID| PAT_ID
==========|==========
301| 101
302| 102
303| 103
304| 104
SQL> select * from nurse;
Wed Nov 09 page 1
Hospital Management System
NURSE_ID| WARD_ID| PAT_ID
==========|==========|==========
401| 301| 101
402| 302| 102
403| 303| 103
404| 304| 104
SQL> select * from patient where pat_disease='tb';
Wed Nov 09 page 1
Hospital Management System
PAT_ID|PAT_NAME |PAT_DISEASE
==========|======|===================
101|raju |tb
SQL> select * from patient where pat_name like 'r%';
Wed Nov 09 page 1
Hospital Management System
PAT_ID|PAT_NAME |PAT_DISEASE
==========|========|===================
101|raju |tb
103|rani |dengue
SQL> select * from doctor where doc_name like '%k';
Wed Nov 09 page 1
Hospital Management System
DOC_ID|DOC_NAME | PAT_ID
==========|===========|==========
201|rk | 101
202|pk | 102
203|kk | 103
SQL> select * from patient where pat_name like '%i';
Wed Nov 09 page 1
Hospital Management System
PAT_ID|PAT_NAME |PAT_DISEASE
==========|=========|=================
103|rani |dengue
104|vani |fever
