1.1 프로젝트 개요
저희 프로젝트는 개인 병원 관리 시스템입니다. Oracle DBMS를 사용하여 의사가 환자를 진료한 내용과 처방 내용을 기록하여 DB에 저장하고 시간이 지난뒤에 그 환자의 기록을 다시 확인 할 수 있도록 하는 기능을 할것이며, 담당의사가 바뀐다고 해도 SQL 검색기능으로 그 환자의 진료기록을 확인 할 수 있도록 할 것 입니다. 또한 환자의 진료를 담당했던 간호사, 의사를 확인 할 수 있습니다. 환자와 의사는 예약리스트에서 예약과 확인을 통해 예약시스템이 이루어진다. 마지막으로, 약제실과 진료과에서는 구매팀으로 물품 또는 약품을 요청하여 필요한 물품을 받을 수 있다.
저희 프로젝트의 1차 결과물은 요구사항을 작성하고 그것을 토대로 ER 다이어그램을 작성하고 스키마를 얻어내는 과정입니다. 최종 결과물은 DB시스템을 구성하여 환자의 진료기록을 저장하고 SQL 검색 기능을 통해 언제든지 환자의 진료기록들을 확인 할 수 있도록 할 것입니다.
1.2 개발개요
≫ 프로젝트명 : 병원 관리 시스템
≫ 프로젝트 기간 : 2020. 5. 15 ~ 2020. 06. 08.
≫ 개발 대상 : 병원 통합 관리 시스템
≫ 개발 목적 : 병원에서 의사의 수는 정해져있는데 반면 환자는 수도 없이 많습니다. 의사도 사람이기 때문에 그 환자들의 병명과 진료기록, 처방내용을 모두 기억 할 수 없기 때문에 DB시스템을 구축하여 시간이 지나도 그 환자의 어느 진료과의 의사, 간호사, 약사가 누구였는지 또한 어떠한 진료를 하였고 어떠한 처방을 하였는지 확인 할 수 있게 하기위해 이 프로젝트를 구성하였습니다. 또한 어떤 처방을 하여 어떠한 약이 환자에게 처방되었는지도 확인할 수 있게 하였습니다. 예약은 환자가 신청을 하고 예약리스트에 저장되어, 의사가 예약리스트를 확인하여 진행된다.
≫ 구현 환경 : Oracle 11g, Oracle SQL Developer
1.3 프로젝트 설명
병원 통합 관리 시스템은 환자가 병원에 방문하여 진료받고, 처방을 받았던 내용을 시간이 지나도 확인 할 수 있도록 하는 프로젝트 이며 이전에 진료하였던 의사가 아니여도 그 당시 환자의 진료기록과, 처방내용을 확인 할 수 있으며, 여러번의 치료가 필요한 환자가 있을 시에는 이전에 진료받았던 담당의사를 확인하여 같은 의사에게 진료를 받을 수 있도록 하는 프로젝트 입니다. 또한 약 처방을 받은 약사는 약을 처방해주어 어떠한 약을 수령했는지 또한 확인할 수 있다. 이 프로젝트의 장점은 환자의 진료 기록을 저장하여 얼만큼의 치료방안이 제안되었는지 그리고 이전에는 어떠한 처방을 하였는지 또한 확인 할 수 있기 때문에 환자의 치료에 많은 도움을 줄 수 있습니다. 예약리스트를 이용하여 환자는 예약을 하고, 의사는 예약을 확인하며 진료가 진행된다. 또한, 약사와 진료과는 부족한 물품 또는 약품을 요청하여 받을 수 있다.
1.4 프로젝트 기능 설명
- 환자의 담당의사, 담당간호사, 약제실, 진료내용, 처방내용(최신순) 검색
- 환자의 예약(시간순) 검색
- 의사의 소속(직급순) 검색
- 진료과와 약사의 발주요청(시간순) 검색
- 약제실의 약 보유현황 검색
- 데이터베이스 설계
3.1 요구사항 명세서
1) 병원 통합 관리 시스템을 구축한다.
2) 환자는 환자ID로 구분하며, 이름, 주민번호, 주소, 전화번호 정보는 가진다.
3) 한 명의 환자를 위해 여러명의 의사와 간호사, 약사가 진료를 하며, 진료번호와 진료내용, 진료일자, 처방내용을 기록한다.
4) 의사는 진료과에 소속되어 있으며, 진료과는 과번호로 구분하고, 과이름과 대표전화를 가진다.
5) 의사는 고유의 의사ID로 식별하며, 이름, 전화번호, 이메일, 직급의 정보를 갖는다.
6) 간호사는 고유의 간호사ID로 식별하며, 이름, 성별, 전화번호 정보를 갖는다.
7) 약제실에는 여러명의 약사가 상주하며, 여러가지 약을 관리한다. 각각의 약들은 약번호가 존재하고 약이름, 재고수량, 가격 정보를 갖는다.
8) 약사들은 약사번호로 구분하며, 이름, 전화번호, 주소 정보를 갖는다.
9) 구매팀에는 각각의 사원번호를 부여받은 사원들이 존재하며, 이름, 전화번호, 주소, 직급정보를 가진다.
10) 진료과와 약제실은 구매팀에 발주요청을 할 수 있으며, 요청에는 요청번호, 제품(약품)이름, 제품(약품)번호, 요청수량, 요청일자 정보를 가진다.
11) 예약리스트는 환자가 예약을 하고, 의사가 확인을 한다. 예약리스트에는 예약번호로 구분하고, 예약일자, 예약시간 정보를 갖는다.
3.2.3 E-R 다이어그램
3.3 릴레이션 스키마 //FK는 파란색으로 표시하였습니다.
의사 릴레이션 (의사ID, 성명, 전화번호, 이메일, 직급, 담당진료과번호)
진료과 릴레이션 (과번호, 과이름, 대표전화, 인원)
환자 릴레이션 (환자ID, 이름, 주민번호, 주소, 전화번호, 담당의사ID, 담당간호사ID)
간호사 릴레이션 (간호사ID, 이름, 성별, 전화번호)
약제실 릴레이션 (약사번호, 이름, 전화번호, 주소)
구매팀 릴레이션 (사원번호, 이름, 전화번호, 주소, 직급)
예약리스트 릴레이션 (예약번호, 예약일자, 진료의사ID, 예약환자ID)
약 릴레이션 (약번호, 약이름, 재고수량, 가격, 관리번호)
진료 릴레이션 (진료번호, 진료내용, 진료날짜, 처방내용, 의사ID, 환자ID, 간호사ID, 약사번호)
요청 릴레이션 (요청번호, 제품(약품)이름, 제품(약품)번호, 요청수량, 요청일자, 요청과, 요청약사번호, 사원번호)
3.4 수정사항
1. 의사 테이블에서 전화번호 삭제 => 필요성 X
2. 예약리스트 테이블에서 예약시간 삭제 => 시간을 저장못함.
-
데이터베이스 인스턴스
(1) 의사 테이블
(2) 환자 테이블
(3) 간호사 테이블
(4) 약제실 테이블
(5) 진료과 테이블
(6) 구매팀 테이블
(7) 예약리스트 테이블
(8) 약 테이블
(9) 요청 테이블
(10) 진료 테이블
- 데이터베이스 검색
질의 1번 (간호사가 검색)
￭ 질의 : 어플관리 데이터베이스에서 특정 간호사(본인='이지혜')이 특정 달(6월)에 진료담당한 진료내용을 검색하라
￭ SQL 문 :
SELECT *
FROM 진료
WHERE (진료.진료일자 >= '2021-06-01' AND 진료.진료일자 <= '2021-06-30')
AND 진료.담당간호사 = (SELECT 간호사ID
FROM 간호사
WHERE 간호사.이름 = '이지혜');
￭ 사용한 기능 :
- SELECT = 진료내용출력
- WHERE = (>=, <=) 같은 비교연산자를 사용해 특정값 사이의 조건검색
- WHERE 부속질의 = 통해 특정간호사('이지혜')를 조건검색
질의 2번 (구매팀에서 검색)
￭ 질의 :제품번호가 'q003'인 제품을 주문한 진료과를 진료과 이름 기준 오름차순으로 출력하라
￭ SQL 문 :
SELECT 요청.요청번호, 진료과.과이름, 진료과.대표전화
FROM 요청,진료과
WHERE 요청.제품번호='q003' AND 요청.요청진료과=진료과.과번호
ORDER BY 진료과.과이름 ASC;
￭사용한 기능:
- WHERE문으로 제품번호가 일치하는 요청을 선택하고 이에 일치하는 진료과만을 선택
- ORDER BY로 진료과 이름 기준 오름차순으로 정렬
질의 3번 (구매팀에서 검색)
￭ 질의 : 특정 기간 사이에 요청을 넣지 않은 진료과를 오름차순으로 출력하라
￭ SQL 문 :
SELECT 진료과.과이름
FROM 요청,진료과
WHERE (요청.요청일자>='2021-06-01' AND 요청.요청일자<='2021-06-30')
AND 요청.요청진료과<>진료과.과번호
ORDER BY 진료과.과이름 ASC;
￭사용한 기능:
- WHERE문으로 2021년 6월 1일부터 동년 동월 31일 사이에 들어온 요청을 선택하고, 그 중에 일치하지 않는 과번호를 가진 진료과를 선택
- ORDER BY로 진료과 이름 기준 오름차순으로 정렬
질의 4번 (의사가 검색)
￭ 질의 : 특정날짜(2021-09-21)에 예약한 환자의 이름과 주민번호를 검색하되, 이름을 내림차순으로 검색하라
￭ SQL 문 :
SELECT 환자.환자이름 AS 예약명단, 환자.주민번호
FROM 환자,예약리스트
WHERE 예약리스트.예약일자 = '2021-09-21' AND 환자.환자ID = 예약리스트.예약환자ID
ORDER BY 환자.환자이름 DESC;
￭사용한 기능:
- AS로 환자이름을 예약명단으로 변경
- WHERE문으로 예약일자가 일치하며 이에 해당하는 환자를 선택
- ORDER BY로 환자 이름을 내림차순으로 정렬
질의 5번 (환자가 검색)
￭ 질의 : 특정 환자(최지완)이 자신의 진료정보를 출력하세요
￭ SQL 문 :
SELECT ALL 진료.*
FROM 환자, 진료
WHERE 환자.환자ID = 진료.진료환자 AND 환자.환자이름 = '최지완'
￭사용한 기능:
- *로 진료테이블의 전부를 출력한다.
- WHERE문으로 환자이름을 검색
질의 6번 (진료과가 검색)
￭ 질의 : 특정 진료과(정형외과)에서 진료를 진료번호와 의사이름, 환자아이디, 진료내용, 진료일자를 출력하세요
￭ SQL 문 :
SELECT 진료.진료번호, 의사.이름, 진료.진료환자, 진료.진료내용, 진료.진료일자
FROM 진료,의사,진료과
WHERE 진료.담당의사=의사.의사ID AND 의사.과번호=진료과.과번호 AND 진료과.과이름='정형외과';
￭사용한 기능:
- 진료테이블에 없는 속성인 의사이름 출력.
- WHERE문으로 조인을 이용해 검색
질의 7번 (구매팀이 검색)
￭ 질의 : 특절 날짜(5월)에 요청받은 물품을 처리한 직원의 이름과 전화번호, 요청번호와 요청제품이름을 출력하세요
￭ SQL 문 :
SELECT ALL 요청.요청번호, 구매팀,이름, 구매팀.전화번호, 요청.제품이름
FROM 요청,구매팀
WHERE 요청.요청일자>='2021-05-01' AND 요청.요청일자<='2021-05-31'
AND 요청.담당직원=구매팀.사원번호'
ORDER BY 요청.요청번호 ASC;
￭ 사용한 기능 :
∘ FROM과 WHERE을 이용하여 요청 테이블과 구매팀 테이블 JOIN 사용
∘ WHERE : 요청일자를 비교연산자와 논리연산자를 사용하여 특정기간을 조건검색
∘ ORDER BY : 요청번호를 오름차순으로 검색
질의 8번 (진료과가 검색)
￭ 질의 : 진료과 인원이 6명 이상인 과에서 지금까지 진료한 진료 횟수를 진료과별로 출력하세요
￭ SQL 문 :
SELECT 의사.과번호, count(진료.진료번호)
FROM 진료, 의사
WHERE 의사.의사ID = 진료.담당의사 and 의사.과번호 IN (SELECT 진료과.과번호
FROM 진료과
WHERE 진료과.인원수 >=6)
GROUP BY 의사.과번호;
￭ 사용한 기능 :
∘ SELECT문에서 count 집계대수를 사용해서 진료횟수 출력
∘ WHERE : 부질의를 이용해서 조건 검색
∘ GROUP BY : 진료과별로 검색
질의 9번 (의사가 검색)
￭ 질의 : 특정의사(김준규)에게 진료를 받지않은 환자의 이름과 주민번호를 출력하되 이름은 오름차순 주민번호는 내림차순으로 검색하시오.
￭ SQL 문 :
SELECT DISTINCT 환자.이름, 환자.주민번호
FROM 환자,진료
WHERE 환자.환자ID = 진료.진료환자 AND 의사.이름 NOT IN(SELECT 의사.이름
FROM 의사,진료
WHERE 의사.의사ID = 진료.담당의사
AND 의사.이름 = '김준규')
ORDER BY 환자.이름 ASC, 환자.주민번호 DESC;
￭ 사용한 기능 :
∘ AS를 이용한 출력 헤더의 컬럼명 변경
∘ FROM을 이용해 환자테이블과 진료테이블 조인, 의사테이블 과 진료테이블 조인
∘ WHERE을 특정의사('김준규')에게 진료받지 않은 환자 조건
￭ 질의 결과 :
