[DB] 병원 관리 프로그램 (1)

당당·2023년 4월 18일

db oracle sql 포토폴리오

DB

목록 보기

1/4

https://github.com/dangdangs0/hospital

📆개발 시기

2021.05.11.-2021.06.08.

📝개발 목적

Oracle DBMS를 사용한 병원 데이터베이스이다.

이를 통해 병원에 있는 모든 의사들 및 간호사들의 데이터를 확인할 수 있으며, 담당하는 진료과를 확인할 수 있다.

또한, 환자의 담당의사 뿐만 아니라 입원 정보를 확인할 수 있으며, 환자 및 의사 사이의 진료 내용을 확인할 수 있는 데이터베이스를 설계하여, 데이터를 구축한 후 질의를 통해 원하는 정보 검색 기능을 제공한다.

병원의 환자, 의사, 간호사, 진료과 등의 정보를 쉽게 찾아보고
각각의 진찰 내용이나 기록을 찾아볼 수 있는 시스템이다.

병원에는 의사, 진료과, 간호사, 환자, 입원이 존재하는데 그 중 의사가

환자를 진료하면, 진료일, 처방내용, 진료과, 진료 번호를 검색할 수 있다.

또한 환자가 병원에 입원했을 때, 입원번호, 입원일, 퇴원일, 입원실 정보를 확인 할 수 있다.

간호사는 환자를 처치해주면서 처치일, 처치내용을 확인할 수 있다.

또한 의사와 간호사는 진료과에 속해있으며

각각의 의사, 간호사의 진료과목이나 의사번호, 간호사번호 등을 통하여 의사나 간호사를 조회할 수 있다.

또한 환자번호를 통해 환자를 검색할 수 있으며 진료과는 과번호를 통해 상세히 검색할 수 있다.

주민등록번호는 랜덤으로 생성하였다.

🔍개발 환경

Windows 10
Oracle 11g xe
Oracle SQL Developer

📕프로젝트 기능

특정 의사에게 진찰받은 환자 중 조건을 만족하는 환자의 수 검색
환자가 가장 많이 입원한 진료과 조회
특정 환자의 진료 기록과 진료 내용 검색
진료 받은지 6개월이 경과한 퇴원한 환자의 이름, 전화번호 검색
특정한 날짜에 입원한 환자를 진료한 간호사의 과이름 검색
특정 간호사에게 처치받은 환자의 이름, 전화번호, 주소 검색
특정한 날짜에 진료받은 환자의 진료과의 전화번호 검색

🔍요구사항 명세서

의사는 고유의 의사번호, 진료과목, 이름, 전화번호, 주소를 가진다.
간호사는 고유의 간호사번호, 직급, 이름, 전화번호, 주소, 진료과목, 과번호를 가진다.
환자는 고유의 환자번호, 이름, 주민등록번호, 전화번호, 주소, 신장(키), 몸무게, 혈액형, 성별, 진료내용을 가진다.
입원은 고유의 입원일, 퇴원일, 입원실 정보를 갖는다.
진료과는 고유의 과번호, 과이름, 전화번호 정보를 갖는다.
의사는 여러 명의 환자를 진찰할 수 있고, 환자도 여러 명의 의사에게 진료받을 수 있다.
진료를 받으면 진료번호, 진료일이 저장된다.
간호사는 여러 명의 환자를 처치할 수 있으며 이때 처치일, 처치내용이 저장된다.
진료내용은 다중값을 가질 수 있다. (ex. 약1은 해열제, 약2는 진통제..etc)
의사와 간호사는 하나의 전공(진료과목)을 가진다.
환자는 입원할 수 있으며 입원은 환자가 존재할 때에만 있을 수 있는 약성개체이다.
환자는 여러 명 입원할 수 있다.

📊E-R 다이어그램

개체와 속성 추출을 하면 다음과 같다.

개체	속성
진료과	과번호, 과이름, 전화번호
의사	의사번호, 이름, 주소, 전화번호
간호사	간호사번호, 주소, 이름, 전화번호
환자	환자번호, 주민등록번호, 신장(키), 혈액형, 주소, 성별, 몸무게, 전화번호, 이름, 진료내용
입원	입원실, 퇴원일, 입원일

관계 추출을 하면 다음과 같다.

관계	참여 개체	관계 유형	속성
속함	진료과(선택), 의사(선택)	일대다	-
진료	의사(선택), 환자(필수)	다대다	진료번호, 진료일
처치	간호사(선택), 환자(선택)	일대다	처치일, 처치내용
입원	입원(필수), 환자(선택)	일대다	-
속함	진료과(선택), 간호사(선택)	일대다	-

🎨ERD

병원 관리 프로그램 ERD

🎫릴레이션 스키마

진한 글씨 : 기본키
기울임 글씨 : 외래키

진료과 릴레이션 ( 과번호, 과이름, 전화번호 )
진료 릴레이션 ( 진료번호, 의사번호, 환자번호, 진료일 )
의사 릴레이션 ( 의사번호, 이름, 전화번호, 주소, 과번호 )
간호사 릴레이션 ( 간호사번호, 이름, 전화번호, 주소, 과번호)

입원 릴레이션 ( 환자번호, 입원일, 퇴원일, 입원실 )
환자 릴레이션 (환자번호, 이름, 주소, 전화번호, 주민등록번호, 신장(키), - - 몸무게, 혈액형, 성별, 간호사번호, 처치일, 처치내용)
환자-진료내용 릴레이션 (환자번호, 진료내용)

🔑테이블 생성 및 데이터 삽입

테이블 생성

create table 진료과(
    과번호 INT NOT NULL,
    과이름 VARCHAR(20) NOT NULL,
    전화번호 CHAR(20),
    PRIMARY KEY(과번호)
);

create table 의사(
    의사번호 INT NOT NULL,
    이름 CHAR(10) NOT NULL,
    전화번호 CHAR(20),
    주소 CHAR(10),
    과번호 INT,
    PRIMARY KEY(의사번호),
    FOREIGN KEY(과번호) REFERENCES 진료과(과번호)
);

create table 간호사(
    간호사번호 INT NOT NULL,
    이름 CHAR(10) NOT NULL,
    전화번호 CHAR(20),
    주소 CHAR(10),
    과번호 INT,
    PRIMARY KEY (간호사번호),
    FOREIGN KEY(과번호) REFERENCES 진료과(과번호)
);

create table 환자(
    환자번호 INT NOT NULL,
    이름 CHAR(10) NOT NULL,
    전화번호 CHAR(20),
    주소 CHAR(10),
    주민등록번호 CHAR(20),
    신장 NUMBER(5,1),
    몸무게 NUMBER(5,1),
    혈액형 VARCHAR(4),
    성별 CHAR(6),
    처치일 DATE,
    처치내용 VARCHAR(20),
    간호사번호 INT,
    PRIMARY KEY (환자번호),
    FOREIGN KEY(간호사번호) REFERENCES 간호사(간호사번호),
    CHECK (혈액형 IN ('A','B','O','AB'))
);

create table 진료(
    진료번호 INT NOT NULL,
    진료일 DATE,
    의사번호 INT,
    환자번호 INT, 
    PRIMARY KEY(진료번호, 의사번호, 환자번호),
    FOREIGN KEY(의사번호) REFERENCES 의사(의사번호),
    FOREIGN KEY(환자번호) REFERENCES 환자(환자번호)
);

create table 입원(
    입원일 DATE NOT NULL,
    퇴원일 DATE,
    입원실 INT,
    환자번호 INT,
    PRIMARY KEY(환자번호, 입원일),
    FOREIGN KEY(환자번호) REFERENCES 환자(환자번호)
);

create table 진료내용(
    진료내용 VARCHAR(20) NOT NULL,
    환자번호 INT,
    PRIMARY KEY(진료내용, 환자번호),
    FOREIGN KEY(환자번호) REFERENCES 환자(환자번호)
);

데이터 삽입

INSERT 
INTO 진료과(과번호, 과이름, 전화번호)
SELECT 1, '가정의학과', '031-8040-5584' FROM DUAL UNION ALL
SELECT 2, '내과', '031-8040-5747' FROM DUAL UNION ALL
...
SELECT 18, '치과', '031-8040-5880' FROM DUAL UNION ALL
SELECT 19, '안과', '031-8040-5588' FROM DUAL;

INSERT 
INTO 의사(의사번호, 이름, 전화번호, 주소, 과번호)
SELECT 1, '박동은', '010-4342-7608','전북', 1 FROM DUAL UNION ALL
SELECT 2, '신진', '010-5368-1045','인천', 2 FROM DUAL UNION ALL
...
SELECT 51, '김기언', '010-8305-2637','세종', 19 FROM DUAL;

INSERT 
INTO 입원(입원일, 퇴원일, 입원실, 환자번호)
SELECT '2020-03-28',NULL,301, 1 FROM DUAL UNION ALL
SELECT '2020-08-14','2021-01-03',105, 7 FROM DUAL UNION ALL
...
SELECT '2020-08-08','2020-08-30',301, 100 FROM DUAL;

INSERT 
INTO 간호사(간호사번호, 이름, 전화번호, 주소, 과번호)
SELECT 1, '손우혁', '010-2655-3175','제주도', 1 FROM DUAL UNION ALL
SELECT 2, '임종환', '010-7532-4653','인천', 1 FROM DUAL UNION ALL
...
SELECT 50, '홍재범', '010-2236-3626','제주도', 19 FROM DUAL;

INSERT 
INTO 진료(진료번호, 진료일, 의사번호, 환자번호)
SELECT 1,'2020-03-27',1, 1 FROM DUAL UNION ALL
SELECT 2,'2020-08-14', 39, 7 FROM DUAL UNION ALL
SELECT 3,'2015-03-13',13, 38 FROM DUAL UNION ALL
SELECT 4,'2017-10-09',17, 39 FROM DUAL UNION ALL
...
SELECT 78,'2018-02-22',15, 51 FROM DUAL UNION ALL
SELECT 79,'2017-07-23',12, 47 FROM DUAL UNION ALL
SELECT 80,'2021-06-03',48, 79 FROM DUAL;

INSERT 
INTO 진료내용(진료내용, 환자번호)
SELECT '고혈압', 1 FROM DUAL UNION ALL
SELECT '응급', 7 FROM DUAL UNION ALL
SELECT '수술마취', 38 FROM DUAL UNION ALL
...
SELECT '관절경',68 FROM DUAL UNION ALL
SELECT '자궁내막증',51 FROM DUAL UNION ALL
SELECT '천식', 47 FROM DUAL UNION ALL
SELECT '피부', 79 FROM DUAL;

Insert
INTO 환자(환자번호, 이름, 전화번호, 주소, 주민등록번호, 신장, 몸무게, 혈액형, 성별, 처치일, 처치내용, 간호사번호)
values (1, '오은수', '010-5387-0364','전북', '761011-2641892', 140.9, 50, 'AB', '여자', '2019-05-17', '고혈압',1);
...
Insert
INTO 환자(환자번호, 이름, 전화번호, 주소, 주민등록번호, 신장, 몸무게, 혈액형, 성별, 처치일, 처치내용, 간호사번호)
values (100, '남지해', '010-3778-4821','울산', '051130-3641892', 175, 50, 'B', '여자', '2015-10-27','자궁내막증',18);

등으로 각각 테이블을 생성 및 데이터를 삽입한다.
(의사 및 간호사 등의 개인정보 문제가 있을까봐 일부 공개)

📑데이터베이스 인스턴스

1.진료과 테이블

2.의사 테이블

3.간호사 테이블

4.환자 테이블

5.입원 테이블

6.진료 테이블

7.환자-진료내용 테이블

📒결과

1.

'박영민' 의사에게 진료받은 환자 중, 진료 내용이 ‘응급’인
환자의 이름, 환자번호,전화번호를 환자의 이름 속성명을 응급환자로 하여,
이름을 기준으로 오름차순으로 출력하라.

SELECT 환자.환자번호, 환자.이름 AS 응급환자, 환자.전화번호
FROM 환자,의사,진료내용,진료
WHERE 의사.의사번호=진료.의사번호
	and 진료.환자번호=진료내용.환자번호
	and 진료.환자번호=환자.환자번호
	and 의사.이름='박영민'
	and 진료내용.진료내용='응급'
ORDER BY 환자.이름;

AS를 이용하여 출력 헤더의 컬럼명 변경
FROM과 WHERE을 사용하여 진료, 환자, 진료내용, 진료 테이블 조인
WHERE을 이용하여 의사 이름, 진료 내용으로 조건 설정
ORDER BY : 환자의 이름으로 오름차순

2.

‘문훈’ 환자의 진료내용과 환자의 환자번호,
이름, 전화번호, 주소, 주민등록번호, 성별, 신장, 몸무게, 혈액형, 그리고 진료내용을 출력하라.

SELECT 환자.환자번호, 환자.이름, 환자.전화번호, 환자.주소, 환자.주민등록번호, 환자.성별, 환자.신장, 환자.몸무게, 환자.혈액형, 진료내용.진료내용
FROM 환자, 진료내용
WHERE 환자.이름 = '문훈' AND 환자.환자번호 = 진료내용.환자번호;

FROM과 WHERE을 사용하여 환자, 진료내용 테이블을 조인
WHERE을 이용하여 환자 이름으로 조건 설정

3.

‘2020-08-13’에 진료받은 환자를 검색하고,
그 환자를 진료한 의사의 진료과 과이름과 전화번호를 검색하라.

SELECT 진료과.과이름, 진료과.전화번호
FROM 진료과
WHERE 과번호 IN (SELECT 의사.과번호
            FROM 진료, 환자, 의사
            where 진료.진료일='2020-08-13'
                and 진료.환자번호=환자.환자번호
                and 의사.의사번호=진료.의사번호);

부속질의문에서 특정 날짜에 진료받은 환자를 진료한 의사의 과번호를 추출
FROM과 WHERE을 사용하여 환자, 진료, 의사를 조인
WHERE을 이용하여 과번호를 통해 과이름, 전화번호 추출

4.

각각의 진료과를 기준으로 입원한 환자들의 수를 진료과의
과이름, 입원환자수로 내림차순을 기준으로 나타내어라.

SELECT 과이름, COUNT(과이름) AS 입원환자수
FROM 입원, 진료, 의사, 진료과
WHERE 입원.환자번호=진료.환자번호
    and 진료.의사번호=의사.의사번호
    and 의사.과번호=진료과.과번호
group by 과이름
order by count(과이름) desc;

AS를 이용하여 출력 헤더의 컬럼명 변경
ORDER을 통해 내림차순으로 정렬
FROM과 WHERE을 사용하여 환자, 진료, 의사, 입원을 조인
GROUP BY를 통해 각각의 과이름으로 입원한 환자의 수를 추출

5.

최근 6개월내에 진료받은 환자의 이름, 전화번호를 검색하라.

SELECT 환자.이름, 환자.전화번호
                    FROM 환자, 진료
                    WHERE 진료.진료일 > TO_CHAR(ADD_MONTHS(sysdate,-6),'YYYY-MM-DD')
                        and 환자.환자번호=진료.환자번호;

WHERE을 최근 6개월내에 진료받은 환자의 이름, 전화번호를 추출
SYSDATE를 이용하여 현재 컴퓨터 시스템의 날짜를 반환
ADD_MONTHS()를 이용하여 최근 6개월 내의 날짜 데이터를 반환
TO_CHAR()를 이용하여 날짜 데이터를 문자열로 변환

6.

‘성우람’ 간호사에게 처치받은 환자의 이름, 전화번호, 주소를 검색하라

select  환자.이름, 환자.전화번호, 환자.주소
From 환자, 간호사
Where 간호사.이름='성우람'
   and 간호사.간호사번호 = 환자.간호사번호;

WHERE을 이용하여 ‘성우람’간호사로 조건 설정
From과 where을 이용하여 환자, 간호사 테이블 조인

7.

‘2020-03-28’에 입원한 환자를 처치한 간호사의 과이름을 검색하라.

SELECT 진료과.과이름
FROM 진료과
WHERE 과번호 IN(SELECT 간호사.과번호
   FROM 간호사, 입원, 환자
   WHERE 입원.입원일='2020-03-28'
      and 입원.환자번호 = 환자.환자번호
      and 환자.간호사번호 = 간호사.간호사번호);

부속질의문에서 특정 날짜에 입원한 환자를 처치한 간호사의 과번호 추출
FROM과 WHERE을 사용하여 간호사, 입원, 환자 테이블 조인
WHERE을 이용하여 진료과의 과이름을 추출

🧸담당 역할

Oracle DBMS 환경 구축
모든 테이블(7개) 생성 및 모든 데이터 입력
간호사 데이터 50개 입력
의사 데이터 51개 입력
입원 데이터 40개 입력
진료 데이터 80개 입력
진료과 데이터 19개 입력
진료 내용 데이터 80개 입력
환자 데이터 100개 입력

각각의 테이블의 컬럼을 통하여 특정한 조건을 만족하는
데이터를 추출해내는 SQL을 작성 (1번, 3번 질의)

당당

MySQL DBA 신입

다음 포스트