project title clinical system

Table Of Content Page

Background Of The System……………………………………………………4

Introduction…………………………………………………………………….4

Mission………………………………………………………………….……….4

Vision…………………………………………………………………………….4

Strategy…………………………………………………………………………..4
Existing System ………………………………………………………………....5

Statement Of Problem………………………………………………………..…5

Proposed System Project………………………………………………………..5

Objective………………………………………………………………………....6

General Objective……………………………………………………………………....6

Specific Objective……………………………………………………………….6

Scope Of The Project……………………………………………………………6

Project Deliverables……………………………………………………………...6

Methodology……………………………………………………………………....6

Functional Requirements………………………………………………………..7

Database Design………………………………………………………………….7

Time Schedule…………………………………………………………………...10

Entity Relation Ship Diagram…………………………………………………...10

1.1 Regular Entity Mapping……………………………………………………...11

1.2 Mapping Of Binary R/Ship…………………………………………………...12

Schema diagram…………………………………………………………………..14

Physical Design Strategy…………………………………………………………15

Normalization……………………………………………………………………...19

Implementation…………………………………………………………………….22

References ………………………………………………………………………….35

Acknowledgment

We have a special thanks to our instructor Tsegaw. who give us this project and we have a great
thanks for our university Especially Poly Campus students Clinic system stockholders who give
us a detailed information about the system.
 Background Of The System

Bihar Dar University institute of technology clinic system starts its service 1980 [Link] clinic system
was located in Amhara Region, Bahir Dar town, kifle ketema shumeabo, Keble 10, in the north direction
of BDU agricultural faculty building.

This system give a service for the whole students in the university .At this time this system has six nurses,
one lab technician, two clean servant, one information disk, one clinic manager and one
pharmacist ,totally twelve employees involved in the system.

Introduction
We are interested on Bahir Dar University institute of technology clinic system. Our project studies how
this clinic system gives the service for the students and what is its major purpose and limitation. These
things will be included in our project work.

Mission
Crudity focused on protection

Keeping the health condition of the students for safe learning until they graduate

Vision
To see healthy student

Strategy
Presenting drugs within the specified time

Fulfilling aim and vision of the clinic by presenting health masters having high performance and
good health condition
 Existing System

The existing system of the clinic system of poly campus has working with the manual system.
The basic functionalities that are applied on the system is register patient by card officer ,treated
patient by physician ,give drug for patient by pharmacist for patient ,give lab test for patient by
lab_technician,report lab result to physician by lab_ technician. The above tasks are performed
in manual system that have different problem like insert,update ,delete,retrive and search patient
and different workers of the system.

Statement Of Problem
The clinic system of Bahir Dar University gives service for student’s but still now this system has
its own draw back and limitation.

Through giving this service the clinic system uses manual data handling system and labor force
for documentation process.

The main problem of the system are:

• Insertion problem
• Deletion problem
• Update/modification problem
• The system is vulnerable to unauthorized access

Proposed System Project

To solve the above problems in the system we need to change the current system service in to computer
based clinic system service to accomplish tasks effectively and efficiently.
 Objective

General Objective
• As we stated above on statements of problem the main objective of our project is to
computerized the current manual system of Bahir Dar University in order to solve the above
problems.

Specific Objective
• To reviewing the current system and knowing the problem

• To study each activity involved in the system

• To identify the problem

• To analysis the existing system

• To design the system

• To implement the system

Scope Of The Project

There are many systems in Bahir Dar University, Poly campus. From those our project concerned on only
Poly campus clinic system of patient reservation.

Activities that are not included :

• Bed reservation

• Managing staff members

• Refer patients to other hospital

Project Deliverables
• Physician can search registered patients and give appropriate treat

• Pharmacist can search registered patients and ordered drugs then give that drug to that patient
 • Registered patient’s information can stored on the database permanently

• Card officer can register patients to the database

• Lab technician can give appropriate lab test to registered patients

Methodology

During developing this project we use some traditional method of data requirement gathering
listed follow.

• Interview

• Reviewing existing documents

We also use CASE tools such as:

Diagramming tools

• Microsoft Visio

• SQL Server 2008

Functional Requirements

Our functional requirement is the proposed system which will be surrounded on the Entity-relationships.
Therefore our proposed system that will store the following functions. Basically:

Entities (patient,drug,physician,lab_teechnician,pharmacy,card officer)

Relationships (give_card,tes,treat,report_labresult,give_drug,give_patient
information,order,give_prescribed paper)

Time Schedule

NUMBER ACTIVITY START FINISH DURATION

1 SELECTING PROJECT 01/04/05 03/04/05 2DAYES
ENVIROMENT
2 REQUIRMENT 8/04/05 16/04/05 1 WEEK
GATHERING
3 DATA MODELING 17/04/05 24/04/05 1 WEEK
4 MAPPING ER/EER 25/04/05 28/04/05 3DAYES
5 NORMALIZATION 29/04/05 30/04/05 1 DAYE
6 IMPLEMENTATION 02/04/05 12/05/05 1 WEEK and
3DAYES

Database Design

• Conceptual schema design of new system

• Entities with their description

• Attributes with their description

patient-entity
name Data type description
PID Varchar(50) Patient id is
primary key
Fname Varchar(50) patient first
name
Lname Varchar(50) patient last name
Sex Varchar(5) gender ofpatient

Card_officer
name Data type description
OFF_ID Varchar(50) Card officer id is primary key
Fname Varchar(50) Card officer first name
Lname Varchar(50) Card officer last name
Address Varchar(50) Address of card_officer

pharmacist-entity
name Data type description
Phy_ID Varchar(50) physicianid is
primary key
Fname Varchar(50) physicianfirst
name
Lname Varchar(50) physician last
name
Address Varchar(50) address of
physician

drug-entity
name Data type description
Lot int Lot the primary key of drug
Drug type Varchar(50) Drug type of the drug
Manf_date DateTime Manufactur date of drug
Exp_date DateTime Expired date of drug

Lab technician-entity
name Data type description
Tech_ID Varchar(50) Technician id is primary key
Fname Varchar(50) Technician first name
Lname Varchar(50) Technician last name

Take drug_relation ship

name Data type description
P_id Varchar(50) P_id is foreign key of give drug
relation ship
Lot int Lot is foreign key of take drug
relation ship
Date DateTime Date of giving drug for patient

Treat_relation ship
name Data type description
P_id Varchar(50) P_id is foreign key of treat
 relation ship
Phy_id Varchar(50) Physician id is foreign key of
treat relation ship
Appointment date Physician appoints patient

Order _relation ship

name Data type description
Lot int Lot is foreign key of take order
relation ship
Phy_id Varchar(50) Physician id is foreign key of
order relation ship

Entity Relation Ship Diagram

 1.1 Regular Entity Mapping
Regular entities are entities that have its own uniquely identifier called primary key.

For each regular (strong) entity type E in the ER schema, create a relation R includes all the simple
attributes of E.

Choose one of the key attributes of E as the primary key for R.

From the above ER we have the following are regular entities:

• Physician

• Pharmacist

• Lab technician

• Patient

• Card officer

• Drug
Physician

PHY_ID FNAME LNAME

Pharmacist

PHA_ID FNAME LNAME

Lab Technician

TECH_ID FNAME LNAME

Patient

P_ID FNAME LNAME SEX

Card Officer

OFF_ID FNAME LNAME

Drug

Lot DRUGTYPE MANFUCTURE_DATE EXPIRED_DATE

1.2 Mapping Of Binary R/Ship

Binary r/ship is a r/ship between two entity types having its own primary key. The mapping of this binary
r/ship is depending on participating(participation constraint) of entity types and cardinality ratio of the
r/ship.

From the above ER we have the following are binary r/ship mapping:

• Between Card officer and physician => one to one

• Between physician and pharmacist=>0ne to one

• Between Lab technician and Physician=>many to many

• Between physician and drug =>many to many

• Between patient and pharmacist=>many to many

• Between patient and drug=>many to many

 One To One R/N Ship

CardOfficer_Physician

[Link] [Link] FNAME LNAME

Lab technician_ Physician

[Link] [Link] FNAME LNAME

Physician_pharmacist

[Link] [Link] FNAME LNAME

Many To Many R/N

Test

PID [Link] Labtest

Take

Lot PID DATE

Order

[Link] PID Lot

Treat

[Link] PID Appointment

N_Ary Mapping

Give_ Drug
Pid [Link] Lot: Date Cost
 Multivalued Mapping

Address

Address [Link] [Link] [Link] [Link] [Link]

Skill

[Link] [Link] Skill

Physical Design Strategy

Physician table
name Data type description
Phy_id Varchar(50) Physician id is primary key
Fname Varchar(50) Physician first name
Lname Varchar(50) physician last name

pharmacist table
name Data type description
Pha_id Varchar(50) Pharmacist id is primary key
Fname Varchar(50) pharmacist first name
L name Varchar(50) pharmacist last name

Lab_technician table
name Data type description
Tech_id Varchar(50) technician id is primary key
Fname Varchar(50) technician first name
Lname Varchar(50) technician last name

Patient table
name Data type description
P_id Varchar(50) patient id is primary key
Fname Varchar(50) patient first name

Drug table
name Data type Description
Lot int Lot
is primary key
Drug-type Varchar(50) Type of drug
Manf_date DateTime Manufacture date of drug
Exp_date DateTime Expired date of drug

Card officer table

name Data type description
off_id Varchar(50) officer id is primary key
Fname Varchar(50) Officer first name
Lname Varchar(50) 0fficer last name

lab technician_physician table

name Data type description
Phy_id Varchar(50) Foreign key from physician
Tech_id Varchar(50) Foreign key from lab_technician
Fname Varchar(50) Technician first name
Lname Varchar(6) Technician last name

physician_pharmacist table
name Data type description
Phy_id Varchar(50) Foreign key from physician
pha_id Varchar(50) Foreign key from pharmacist
Fname Varchar(50) physician first name

Test table
name Data type description
P_id Varchar(50) Patient id is primary key
Tech_id Varchar(50) technician Foreign key from
technician
Test type Varchar(50) type of lab test

take table
name Data type description
Lot int Lot is foreign key form drug
P_id Varchar(50) Patient id is foreign key from
pateint
Date DateTime Primary key of take
 order table
name Data type description
Lot int Lot is foreign key form drug
Phy_id Varchar(50) physician id is foreign key from
physician

treat table
name Data type description
P_id Varchar(50) Patient id is foreign key form
patient
Phy_id Varchar(50) physician id is foreign key from
physician
Appointment Date time Physician appoints patient

Give drug table

name Data type description
P_id Varchar(50) Patient id is foreign key from
patient
Pha_id Varchar(50) pharmacist Foreign key from
pharmacist
Lot int Lot is foreign key from drug
date date Date of giving drug
Cost float The cost of the service that the
patient take

Skill table
name Data type description
Pha_id Varchar(50) Pharmacist id is foreign key from
pharmacist
Phy_id Varchar(50) Physician id is foreign key from
physician
skill Varchar(50) Skill is primary key of skill
 Address table

name Data type description

P_id Varchar(50) Patient id is foreign key from
patient
Pha_id Varchar(50) pharmacist Foreign key from
pharmacist
Phy_id Varchar(50) Physician id is foreign key from
physician
Off_id Varchar(50) Officer id is foreign key from
officer
Tech_id Varchar(50) Technician id is foreign key from
lab_technician
Address Varchar(50) Primary key of address

Normalization

Un Normalization Form

[Link] Fname Lname A ddress (place,email) Lot Drug. Type [Link] [Link]

P123 Helen Nebu Addis,hel@[Link] 0774 X1 02/04/07 08/09/08

P245 Abel Regen Gonder,abe@[Link] 1342 Y1 02/04/07 08/09/08

P127 Kefale Mamo America ,kef@gmail. 4512 Z1 09/07/009 24/09/09

P456 kebed Alemu South,keb@[Link] 1624 Y2 02/04/07 08/09/08

P245 Abel Regen Gonder,abe@[Link] 1624 Y1 02/04/07 08/09/08

P334 Betel Alemu America,bele@gmail. 1342 X1 02/04/07 08/09/08

P456 Muna kibebe Gonder,mu@[Link] 0774 Y1 02/04/07 08/09/08

 First Normal Form(1st NF)

[Link] Fname Lname `place email Lot Drug. [Link] [Link]

Type

P123 Helen Nebu Addis hel@[Link] 0774 X1 02/04/07 08/09/08

P245 Abel Regen Gonder abel@gmail. 1342 Y1 02/04/07 08/09/08

P127 Kefale Mamo America kef@[Link] 4512 Z1 09/07/009 24/09/09

P456 kebed Alemu South kebe@gmail. 1624 Y2 02/04/07 08/09/08

P456 kebed Alemu B/dr mu@[Link] 6589 Y2 02/04/07 08/09/08

Second Normal Form(2nd NF)

PATIENT, DRUG ([Link], Fname, Lname, Lot,[Link])

[Link] Fname Lname Lot [Link]

P123 Helen Nebu 0774 X1

 P245 Abel Regen 1342 Y1

P127 Kefale Mamo 4512 Z1

P456 kebed Alemu 1624 Y2

Third Normal Form(3rd)

Patient_Drug(Pid,[Link]) Patient

PID FNAME LNAME

P123 HELEN Nebue
P245 ABEL Regen
P127 KEFALE Mamo
P456 KEBEDE Alemu
PID Lot DRUG _TYPE
P123 0774 X1
P245 1342 Y1
P127 4512 Z1
P456 1642 Y2

Drug

DRUG _TYPE Lot [Link] [Link]

X1 0774 02/04/07 08/09/08
Y1 1342 02/04/07 08/09/08
Z1 4512 09/07/009 24/09/09
Y2 1642 02/04/07 08/09/08
 Implementation

--1
create database clinic_PatientReservation
use clinic_PatientReservation
----------patient table------------------
create table patient(
p_id varchar(50) not null primary key,
fname varchar(50) not null,
lname varchar(50) not null,
sex char(1) constraint ck_sexn check(sex='m'or sex='f') not null,
)
update patient set fname='abebe' where p_id='p-12'

-----------sample values of patient-------------------------------------------

insert into patient(p_id,fname,lname,sex) values('p-12','kebede','zebene','m')
insert into patient(p_id,fname,lname,sex) values('p-66','selam','adnew','f')
insert into patient(p_id,fname,lname,sex) values('p-77','yalew','admasie','f')
insert into patient(p_id,fname,lname,sex) values('p-99','debebe','amele','m')
insert into patient(p_id,fname,lname,sex) values('p-54','dagi','sami','f')
insert into patient(p_id,fname,lname,sex) values('p-20','sinidu','nebu','m')

------------------------------------------------------------------------------
-----
select * from patient
------------------------------------------------------------------------------

--------------------physiciantable-------------
create table physician(
phy_id varchar(50) not null primary key,
fname varchar(50) not null,
lname varchar(50) not null,
skill varchar(50) not null,
address varchar(50) not null
)
-------------------------------sample physician values------------------
insert into physician(phy_id,fname,lname,skill,address) values('phy-
1','dor_miskir','adane','doctor','nazret')
insert into physician(phy_id,fname,lname,skill,address) values('phy-
2','ms_besa','zara','nurse','england')
insert into physician(phy_id,fname,lname,skill,address) values('phy-
3','dor_abel','samuel','pharmacist','addis abeba')
---------------------------------------------------------------------------
select * from physician
--------------------------------------------------------------

--lab_technician
create table lab_technician(
tech_id varchar(50) not null primary key,
fname varchar(50) not null,
lname varchar(50) not null,
address varchar(50) not null)
----------------------------sample lab_technician values--------------------
insert into lab_technician(tech_id,fname,lname,address)
values('tech_1','amare','alemu','kombolcha')
insert into lab_technician(tech_id,fname,lname,address)
values('tech_2','fasika','tadese','bdu')
insert into lab_technician(tech_id,fname,lname,address)
values('tech_3','rena','joghn','japan')
------------------------------------------------------------------------------
select * from lab_technician
------------------------------------------------------

--pharmacist
create table pharmacist(
pha_id varchar(50) not null primary key,
fname varchar(50) not null,
lname varchar(50) not null,
address varchar(50) not null,
)
-------------------------sample pharmacist values----------------------
insert into pharmacist(pha_id,fname,lname,address)
values('pha_1','hana','debas','wollo')
insert into pharmacist(pha_id,fname,lname,address)
values('pha_2','abrham','kedir','wolayita')
insert into pharmacist(pha_id,fname,lname,address)
values('pha_3','natanim','kibro','addis abeba')
-----------------------------------------------------------------------------
select * from pharmacist
--------------------------------
---------------------------------------------

--card officer
create table card_officer(
off_id varchar(50) not null primary key,
fname varchar(50) not null,
lname varchar(50) not null,
address varchar(50) not null,
)
--------------------------------------sample card_officer
values--------------------
insert into
card_officer(off_id,fname,lname,address)values('off_1','dagmiaw','tibebu','add
is abeba')
insert into
card_officer(off_id,fname,lname,address)values('off_2','hennok','tibebu','bdu'
)
insert into
card_officer(off_id,fname,lname,address)values('off_3','hayimanot','zelalem','
gonder')
------------------------------------------------------------------------------
--------
select * from card_officer
------------------------------------------------------------------
---
----------------drug----------------------------

--drug
create table drug(
lot int not null primary key,
drug_type varchar(50) not null ,
manu_date datetime not null,
exp_date datetime not null,
)
----------------------------sample drug values--------------------------------
insert into drug(lot,drug_type,manu_date,exp_date)
values(123,'pandol','12/08/2004','02/09/2006')
insert into drug(lot,drug_type,manu_date,exp_date)
values(456,'mezil','12/03/2005','02/04/2008')
insert into drug(lot,drug_type,manu_date,exp_date)
values(876,'amoxaslin','07/04/2004','02/11/2007')

insert into drug(lot,drug_type,manu_date,exp_date)

values(432,'doxy','11/22/2004','02/11/2008')

------------------------------------------------------------------------------
------------
select * from drug
-----------------------------

-----------------------------------------
--lab_technician_physician
create table techphysician(
tech_id varchar(50) not null foreign key references lab_technician(tech_id) on
delete cascade on update cascade,
phy_id varchar(50) not null foreign key references physician(phy_id)on delete
cascade on update cascade,
fname varchar(50) not null,
lname varchar(50) not null,
constraint pk_techphysician primary key(tech_id,phy_id)
)
drop table techphysician
---------------------------------- tech_physician
values--------------------------------
insert into techphysician(tech_id,phy_id,fname,lname) values('tech_1','phy-
1','dor_miskir','adane')
insert into techphysician(tech_id,phy_id,fname,lname) values('tech_1','phy-
3','fasika','tadese')
insert into techphysician(tech_id,phy_id,fname,lname) values('tech_3','phy-
1','fasika','tadese')
------------------------------------------------------------------------------
-----
select * from techphysician
------------------------------------------------------------------------------
---

--cardofficer_physician
create table offcerphysician(
off_id varchar(50) not null foreign key references card_officer(off_id)on
delete cascade on update cascade,
phy_id varchar(50) not null foreign key references physician(phy_id)on delete
cascade on update cascade,
fname varchar(50) not null,
lname varchar(50) not null ,
constraint pk_offcerphysician primary key(off_id, phy_id)
)
----------------------------offcer_physician
values----------------------------------
insert into offcerphysician(off_id,phy_id,fname,lname) values('off_1','phy-
1','dagmiaw','tibebu' )
insert into offcerphysician(off_id,phy_id,fname,lname) values('off_2','phy-
3','hennok','tibebu' )
insert into offcerphysician(off_id,phy_id,fname,lname) values('off_2','phy-
1','hayimanot','zelalem' )
------------------------------------------------------------------------------
---------
select * from offcerphysician
-------------------------------------------------------------

--Test
create table test(
p_id varchar(50) not null foreign key references patient(p_id)on delete
cascade on update cascade,
tech_id varchar(50) not null foreign key references lab_technician(tech_id)on
delete cascade on update cascade,
test_type varchar(50) not null,
constraint pk_pateintlabtechniccian primary key(p_id, tech_id)
)
------------------------------------------------- pateint_labtechniccian
values--------------
insert into test(p_id,tech_id,test_type) values('p-12','tech_1','Gastrics')
insert into test(p_id,tech_id,test_type) values('p-20','tech_2','Stool')
insert into test(p_id,tech_id,test_type) values('p-12','tech_2','bloodtest')
------------------------------------------------------------------------------
------------------
select * from test
-----------------------------------------------

--take associative entity between pateint and drug

create table take(
lot int not null foreign key references drug(lot)on delete cascade on update
cascade,
p_id varchar(50) not null foreign key references patient(p_id)on delete
cascade on update cascade,
date datetime not null,
constraint pk_take primary key(lot, p_id,date)
)
select * from patient
------------------------------------------take values----------------------
insert into take(lot,p_id,date) values(123,'p-12','12/04/2007')
insert into take(lot,p_id,date) values(456,'p-54','10/06/2009')
insert into take(lot,p_id,date) values(876,'p-66','12/06/2009')
insert into take(lot,p_id,date) values(123,'p-20','12/06/2009')
insert into take(lot,p_id,date) values(456,'p-66','12/06/2009')
insert into take(lot,p_id,date) values(123,'p-20','12/23/2009')
insert into take(lot,p_id,date) values(876,'p-66','12/23/2009')

--------------------------------------------------------------------------
select * from take
--------------------------------------

--order associative entity b/n physician and drug

create table ordere(
p_id varchar(50) not null foreign key references patient(p_id)on delete
cascade on update cascade,
phy_id varchar(50) not null foreign key references physician(phy_id)on delete
cascade on update cascade,
lot int not null foreign key references drug(lot)on delete cascade on update
cascade,
constraint pk_ordere primary key(phy_id, lot,p_id)
)
drop table ordere
-----------------------------------------ordere values--------------
insert into ordere(p_id,phy_id,lot) values('p-12','phy-1',123)
insert into ordere(p_id,phy_id,lot) values('p-12','phy-1',456)
insert into ordere(p_id,phy_id,lot) values('p-20','phy-3',876)
------------------------------------------------------------------
select * from ordere
----------------------------------------------------
--

--treat associative entity b/n physician and pateint

create table treat(
Appointment varchar(10) null,
phy_id varchar(50) not null foreign key references physician(phy_id)on delete
cascade on update cascade,
p_id varchar(50) not null foreign key references patient(p_id)on delete
cascade on update cascade,
constraint pk_treat primary key(phy_id, p_id)
)
drop table treat
---------------------treat values----------------------
insert into treat values('1/24/2005','phy-1','p-54')
insert into treat values('','phy-1','p-99')
insert into treat values('','phy-3','p-12')
---------------------triggr-test------------------------
insert into treat(phy_id,p_id,Appointment) values('5/19/200','phy-3','p-66')
insert into treat(phy_id,p_id,Appointment) values('phy-3','p-44')

--------------------------------------------------------
select * from treat
-------------------------------------------

--give drug tertiary r/n ship b/n pharmacist,drug &patient

create table givedruge(
pha_id varchar(50) not null foreign key references pharmacist(pha_id)on
delete cascade on update cascade,
lot int not null foreign key references drug(lot)on delete cascade on update
cascade,
p_id varchar(50) not null foreign key references patient(p_id) on delete
cascade on update cascade,
date datetime not null,
cost float not null
constraint pk_givedruge primary key(pha_id, lot, p_id,date)
)
----------------------------------give_drug values----------------
insert into givedruge(pha_id,lot,date,cost) values('pha_3',123,'p-
54','12/02/2007',30.4)
insert into givedruge(pha_id,lot,p_id,date,cost) values('pha_1',456,'p-
54','12/02/2007',10.5)
insert into givedruge(pha_id,lot,p_id,date,cost) values('pha_3',876,'p-
12','12/02/2007',45.90)
insert into givedruge(pha_id,lot,p_id,date,cost) values('pha_2',123,'p-
77','12/05/2007',20.97)
insert into givedruge(pha_id,lot,p_id,date,cost) values('pha_1',456,'p-
77','12/05/2007',13.75)

--------------------------------------------------------------------
select * from givedruge
--------------------------------------------------

--address multivalude attribut

create table address(
address varchar(50) not null primary key,
phy_id varchar(50) not null foreign key references physician(phy_id) on
delete cascade on update cascade,
pha_id varchar(50) not null foreign key references pharmacist(pha_id) on
delete cascade on update cascade,
off_id varchar(50) not null foreign key references card_officer(off_id) on
delete cascade on update cascade,
p_id varchar(50) not null foreign key references patient(p_id) on delete
cascade on update cascade,
tech_id varchar(50) not null foreign key references lab_technician(tech_id)
on delete cascade on update cascade,
)
-------------------------------------------address
values----------------------------
insert into address(address,phy_id,pha_id,off_id,p_id,tech_id)
values('haremiya','phy-1','pha_2','off_1','p-12','tech_1')
insert into address(address,phy_id,pha_id,off_id,p_id,tech_id)
values('gonder','phy-2','pha_1','off_2','p-77','tech_2')
insert into address(address,phy_id,pha_id,off_id,p_id,tech_id)
values('dessie','phy-3','pha_3','off_3','p-54','tech_3')
------------------------------------------------------------------------------
-------
select * from address
-------------------------------------------

--skill multivalude attribut

create table skill(
skill varchar(50) not null primary key,
phy_id varchar(50) not null foreign key references physician(phy_id)on delete
cascade on update cascade,
pha_id varchar(50) not null foreign key references pharmacist(pha_id)on
delete cascade on update cascade,
)
------------------------------------------------skil values----------------
insert into skill(skill,phy_id,pha_id) values('pharmacist','phy-1','pha_2')
insert into skill(skill,phy_id,pha_id) values('doctor','phy-3','pha_1')
insert into skill(skill,phy_id,pha_id) values('technician','phy-2','pha_3')
--------------------------------------------------------------------------
select * from skill
--------------------------------------------------------------------------

--2 value insertion

select * from patient

select * from givedruge

select * from physician
select * from lab_technician
select * from pharmacist
select * from card_officer
select * from drug
select * from offcerphysician
select * from test
select * from take
select * from ordere
select * from treat
delete from treat where phy_id='phy-2'
select * from givedruge
select * from address
select * from skill
------------------------------------------------------------------------------
---------------------------------
select fname from patient where sex='f'
select fname from patient
select lname from patient
update patient set fname='Amsalw' where fname='kebede'
update patient set lname='chilot' where fname='selam'
update patient set lname='chilot' where p_id='p-12'
delete from patient where p_id='p-66'
------------------------------------------------------------------------------
-
create view vfname as select * from patient where fname like 's%'
select*from vfname

create index ix_pat on patient(p_id)

------------------------------------------------------------------------------
-----
create view phy as select [Link] from physician phy,treat t,patient p where
phy. phy_id=t.phy_id and p.p_id=t.p_id and [Link]=''
select*from phy

create index ix_phy on physician(fname)

update physician set lname='muhe',skill='manager',address='america' where

phy_id='phy-1'
select phy_id ,skill,address from physician where phy_id='phy-2'
-- alter table physician add skill varchar(50)
------------------------------------------------------------------------------
--------
create view lab_t as select [Link] from lab_technician lt,patient p,test ts
where lt.tech_id =ts.tech_id and p.p_id=ts.p_id and ts.test_type='stool'
select*from lab_t

create index ix_lt on lab_technician(tech_id)

update lab_technician set lname='muhe',address='erope' where

tech_id='tech_1'
select tech_id ,address from lab_technician where tech_id='tech_3'

------------------------------------------------------------------------
create view pharm as select * from pharmacist where address like 'w%'
select*from pharm

create index ix_pr on pharmacist(pha_id)

update pharmacist set fname='Amanuel',address='ethiopia' where

pha_id='pha_2'
select pha_id , fname,address from pharmacist where pha_id='pha_3'
------ delete
------------------------------------------------------------------------------
---
create view cardf as select * from card_officer where lname like 't%'
select*from cardf

create index ix_cf on card_officer(off_id)

update card_officer set lname='Amanuel',address='BahirDar' where

off_id='off_2'
select off_id , fname,address from card_officer where off_id='off_3'

---- ------------------------------------------------------------
create view dr as select * from drug where lot ='123'
select*from dr

create index ix_dr on drug(exp_date)

update drug set manu_date='12/08/2004',exp_date='02/04/2008' where

drug_type='mezil'
select drug_type , manu_date from drug where drug_type='mezil'
delete from patient where p_id='p-99'
select * from patient

------------------------------------------------------------------------------
-
create view tecp as select * from techphysician where fname like 'f%'
select*from tecp

create index ix_tph on techphysician(tech_id)

update techphysician set fname='Senite',lname='wolde' where

tech_id='tech_1'and phy_id='phy-1'
select tech_id,phy_id,fname from techphysician where tech_id='tech_1'and
phy_id='phy-1'
delete from patient where p_id='p-99'
-------------------------------------------------------------------

create index ix_pp on pateintpharmacist(pha_id)

update pateintpharmacist set sex1='m',fname='aregahegn' where p_id='p-34'and

pha_id='pha_1'
select p_id,pha_id from pateintpharmacist where sex1='m'
------ delete
------------------------------------------------------------------------------
---------
create view offp as select * from offcerphysician where phy_id ='phy-1'
select*from offp

create index ix_offp on offcerphysician(off_id)

update offcerphysician set fname='sada',lname='abdu' where off_id='off_2'and

phy_id='phy-1'
select off_id,phy_id,fname,lname from offcerphysician where
off_id='off_2'
------------------------------------------------------------------
create view labtech as select [Link] from lab_technician ltch,patient p,test
ts where p.p_id =ts.p_id and ltch.tech_id=ts.tech_id and
ts.test_type='Gastrics'
select*from labtech

create index ix_tk on test(test_type)

update pateintlabtechniccian set fname='mola',lname='alelgn' where p_id='p-

28'
select p_id,tech_id,fname from pateintlabtechniccian where
tech_id='tech_2'

------ delete
------------------------------------------------------------------------------
--------
create view tk as select * from take where lot =123
select*from treat

create index ix_tk on take(p_id)

update take set date='10/05/1998' where p_id='p-12'

select drug_type,date from take where p_id='p-12'
------ delete
------------------------------------------------------------------------------
----------
create view ord as select [Link] from ordere o,patient p,physician phy ,drug
d where p.p_id=o.p_id and phy.phy_id=o.phy_id and [Link]=[Link] and [Link]='m'
select*from ord

create index ix_ord on ordere(phy_id)

update ordere set drug_type='doxy' where phy_id='phy-3'

select phy_id,drug_type from ordere where phy_id='phy-1'
------
------------------------------------------------------------------------------
------------
create view tre as select * from treat where p_id ='p-54'

select * from tre

create index ix_tre on treat(phy_id)

update treat set phy_id='phy-5' where p_id='p-28'

select phy_id,p_id from treat where phy_id='phy-1'

------------------------------------------------------------------------------
----------------------
create view gd as select [Link] from givedruge gd, patient p, drug d
,pharmacist pha where [Link] =[Link] and gd.p_id=p.p_id and
pha.pha_id=gd.pha_id and cost=10.5
select*from gd
create index ix_gd on givedruge(lot)

update givedruge set date='12/06/1789' where pha_id='pha_3'

select pha_id,date from givedruge where lot=876
------ delete
--------------------------------------------------------------------
create view addr as select * from address where address ='gonder'
select*from addr
select*from patient

create index ix_addr on address(phy_id)

update address set address='Addis abeba' where pha_id='pha_3'

select address,phy_id,pha_id from address where p_id='p-77'
------ delete
------------------------------------------------------------------------
create view sk as select * from skill where skill like 't%'
select*from sk

create index ix_skill on skill(skill)

update skill set skill='tibeka' where pha_id='pha_3'

select phy_id,pha_id from skill where skill='tibeka'
------ delete
-------------------------------------------------------------------

select [Link],[Link],[Link] from patient p ,physician phy,treat t where

p.p_id='p-12' and
phy.phy_id=t.phy_id and p.p_id=t.p_id

select [Link],[Link],[Link],[Link]
from patient p ,physician phy,pharmacist pha,drug d,treat t,givedruge gd where
p.p_id='p-54' and
phy.phy_id=t.phy_id and p.p_id=t.p_id and pha.pha_id=gd.pha_id and
p.p_id=gd.p_id and [Link]=[Link]

-------------ADVANCED-------------------
-----scallar function----
create function Tpatient()
returns int
as
begin
declare @tpaient int
set @tpaient=(select count (p.p_id) from patient p,drug d,take te where
d.drug_type='amoxaslin' and p.p_id=te.p_id and [Link]=[Link])
return @tpaient
end
select [Link]()
------------------------------------------------------------------------------
create function Fpatient()
returns int
as
begin
declare @f int
set @f=(select count (p.p_id )from patient p,physician phy, treat t where
[Link]='dor_miskir' and p.p_id=t.p_id and phy.phy_id=t.phy_id)
return @f
end
select [Link]()
drop function Fpatient
------------------------------------------------------------------------------
------------------------
create function costs(@p_id varchar(10))
returns float
as
begin
declare @cost float
set @cost=(select sum(cost) from givedruge gd,patient p, pharmacist pha where
p.p_id=gd.p_id and pha.pha_id=gd.pha_id and p.p_id=@p_id)
return @cost
end
select * from givedruge
select [Link]('p-54')
drop function costs
------------------------------------------------------------------------------
------------------------
------------------------------------------------------------------------------
-------------------------
create function costshering(@pha_id varchar(10))
returns float
as
begin
declare @coustshering float
set @coustshering=(select sum(cost) from givedruge gd,patient p, pharmacist
pha where p.p_id=gd.p_id and pha.pha_id=gd.pha_id and pha.pha_id=@pha_id)
return @coustshering
end
--drop function costshering
select [Link]('pha_3')
select * from givedruge
------------------------------------------------------------------------------
-------------------------

-----------------------------inline valued
table-------------------------------------------------------------------------
--
create function list()
returns table
as
return select [Link],phy.phy_id,[Link] from ordere o,physician phy,drug d
where
phy.phy_id=o.phy_id and [Link]=[Link]

select * from [Link]()

-------------------------------multi statment valued
table------------------------------------------------------------------------
create function result()
returns @tt table ( p_id varchar(30),fname varchar(30),phy_id
varchar(30),pha_id varchar(30),drug_type varchar(30),lot int)
as
begin
insert @tt select p.p_id,[Link],phy.phy_id,pha.pha_id,d.drug_type,[Link] from
patient p,physician phy,pharmacist pha,drug d,treat t,givedruge gd
where p.p_id=t.p_id and p.p_id=gd.p_id and phy.phy_id=t.phy_id and
pha.pha_id=gd.pha_id and [Link]=[Link]
return
end
select *from [Link]()

create function yy()

returns int
as
begin
declare @total int
set @total=(select count(fname)from [Link]() where lot=456)
return @total
end

select [Link]()
------------------------------------------------------------------------------
--------------------------
----------------------------------------------------------stored
procedur-------------------------------
create procedure usp_pharmacist(@sex char)
as
select p.p_id,d.drug_type,[Link] from patient p,take t,drug d where @sex=[Link]
and p.p_id=t.p_id and [Link]=[Link]
execute usp_pharmacist 'f'

create procedure usp_association(@p_id varchar(20))

as
select [Link],p.p_id,d.drug_type,[Link],[Link],[Link]
from patient p,givedruge gd,drug d,treat tr,physician phy,pharmacist pha
where @p_id=p.p_id and p.p_id=tr.p_id and p.p_id=gd.p_id and
phy.phy_id=tr.phy_id and
pha.pha_id=gd.pha_id and [Link]=[Link]
execute usp_association 'p-54'
drop procedure
-------------------------------------------------------------------------
---------------------------------
trigger----------------------------------------------------
go
create trigger pname_update on patient
for update
as
if(update(fname))
 begin
raiserror('patient name shoud not be updated!',17,1)
rollback transaction
end
go
update patient set fname='zelalem' where p_id='p-20'--test fname update
select * from patient
drop trigger pname_update--

create trigger trig_insert on drug

for insert
as
select (d.drug_type) from drug d,inserted i where [Link]=[Link] and
d.exp_date >10/09/2007
begin
raiserror('you can not inserted greter than 10/09/2007',17,1)
rollback transaction
end
drop trigger trig_insert
insert into drug values(22,'shrop','09/14/2005','11/09/2007')--test
select * from drug

go
create trigger trg_insert on take
for insert
as
if(select count([Link]) from take t, inserted i where t.p_id=i.p_id)>2
begin
raiserror('you can not take more than two druges',17,1)
rollback transaction
end
go

drop trigger trg_insert

select * from take
insert into take values(123,'p-12','09/23/2003')
insert into take values(456,'p-12','09/23/2003')

go
create trigger tr_insert on treat for insert
as
if(select count(t.p_id) from treat t, inserted i where t.phy_id=i.phy_id)>2
begin
raiserror('physician can not treat more than two patient perday',17,1)
rollback transaction
end
go
insert into treat values('04/24/2005','phy-3','p-20')
insert into treat values('','phy-3','p-77')

drop trigger tr_insert

select * from treat
------------------------------
security-----------------------------------------------
create login yengusie with password='111'
create user yengusie for login yengusie
create role physicians
sp_addrolemember physicians,yengusie
grant select,insert,update() on treat to physicians

select * from treat

insert into treat values('','phy-2','p-54')
update treat set appointment='04/23/2002' where p_id='p-99'

select * from patient--test

insert into patient values('p16','regen','feven','m')--test
--revoke select,insert on treat from physicians
sp_droprolemember physicians,yengusie--test

create login wubayehu with password='222'

create user wubayehu for login wubayehu
create role pharmacists
sp_addrolemember pharmacists,wubayehu

create view needdruge as select * from drug d where d.drug_type='mezil'

select * from needdruge--test
grant select on needdruge to pharmacists with grant option
select * from drug--test
--revoke select on needdruge from pharmacists

sp_droprolemember pharmacists,wubayehu --test

create login wubit with password='333'

create user wubit for login wubit
create role card_officers
sp_addrolemember card_officers,wubit
sp_addrolemember owner,wubit
grant select,insert,delete on patien to card_officers
select * from patient--test
update patient set fname='zeleke' where p_id='p-54'--test
delete from patient where p_id='p-10'--test
insert into patient values('p-10','wubit','befkadu','f')--test
--revoke select,insert,delete on patien from card_officers

sp_droprolemember card_officers,wubit--test

create login chilot with password='444'

create user chilot for login chilot
create role lab_technicians
sp_addrolemember lab_technicians,chilot

select * from test

select * from patient
select * from lab_technician
insert into patient values('p-2080','demeke','adera','m')
insert into patient values('p-2029','alemitu','regasa','f')
insert into patient values('p-2034','selina','adera','f')

grant insert,delete on test to lab_technicians

insert into test values('p-54','tech_1','HIV')
insert into test values('p-77','tech_1','heartcase')
delete from test where p_id='p-20'
--revoke insert,delete on lab_technician from lab_technicians
sp_droprolemember lab_technicians,chilot

---------------------------Transaction-------------------------------
CREATE PROCEDURE changeval (@oldskill varchar(30), @newskill varchar(30))
as
BEGIN TRANSACTION T1
Update skill set skill=@newskill where skill=@oldskill

If (@@error<>0)
Rollback transaction T1
Commit
update skill set skill='pharmacy' where skill='nurse'--test

select * from skill

CREATE PROCEDURE appointment (@oldappointment varchar(30), @newappoinment

varchar(30))
as
BEGIN TRANSACTION T1
Update treat set appointment=@newappoinment where appointment=@oldappointment

If (@@error<>0)
Rollback transaction T1
Commit
update treat set appointment='01/02/2005' where p_id='p-54'--test
select * from treat

CREATE PROCEDURE checkdate (@oldmanu_date date, @newamanudate

date,@oldexp_date date,@newexp_date date)
as
BEGIN TRANSACTION T3
Update drug set manu_date=@newamanudate where manu_date=@oldmanu_date
Update drug set exp_date=@newexp_date where exp_date=@oldexp_date

If (@@error<>0)
Rollback transaction T3
Commit
update drug set manu_date='05/10/2000' where lot=432--test
update drug set exp_date='09/05/2002' where lot=876--test
select * from drug
select * from drug
create login yengu with password='12'
sp_addsrvrolemember yengu,sysadmin
--------------------------------------------------------------------------

References
Fundamentals of database management system Emissary third and fourth edition text book.
thank you