SQL - 데이터베이스 구현 시험 문제, 병원관리 데이터베이스

[DATABASE/Oracle] - 오라클 SQL - 활용 시험

[DATABASE/Oracle] - SQL - 응용 시험

코리아병원은 여러명의 의사와 간호사가 환자를 관리한다. 
병원은 환자를 체계적으로 관리하기 위하여 데이터베이스를 구축하고자 한다.
의사, 환자, 간호사는 원하는 정보를 쉽게 검색, 관리하고자 한다.

 요구사항
① 의사 한명은 여러명의 환자를 맡고, 환자 한명은 한명의 의사에게 진료를 받는다. 또한 의사는 환자에 대한 모두 정보를 검색할 수 있다.
② 환자는 진료 내용등의 정보를 검색할 수 있으며 담당 의사에 대해서도 조회할 수 있다.
③ 간호사는 병원 진료 차트 정보를 검색할 수 있으며 의사와 더불어 환자를 관리한다. 간호사 업무는 여러 환자들의 진료접수, 환자관리, 차트관리를 수행한다.
④ 모든 의사는 의사 ID를 부여하여 식별하여 담당진료과목, 성명, 성별, 전화번호, 이메일, 직급을 가진다.
⑤ 모든 환자는 환자ID를 부여하여 식별하며 담당의사, 담당간호사, 환자 성명, 주민번호, 성별, 주소 전화번호, 이메일 직업을 가진다.
⑥ 모든 간호사는 간호사 ID를 부여하여 식별하며 담당업무, 성명, 성별, 전화번호, 이메일, 직급을 가진다.
⑦ 진로ID는 날짜+진로순번으로 부여하고 진료한 의사ID, 환자ID, 진료내용, 진료날짜를 포함한다.
⑧ 진료를 받은 환자에 대한 내용을 차트에 기록하고 차트는 차트번호를 부여하여 식별하며 담당 의사 ID, 간호사ID, 환자ID와 진료ID 그리고 의사소견을 차트 내용에 기록한다.
 

1. 개념적 설계 - 20점
(1) ENTITY, RELATION, ATTRIBUTE로 구분하세요.
(2) ER 다이어그램 표기하세요

 
2.논리적 설계 - 20점
기본키와 외래키를 표시하기


3. 물리적 모델링(설계) - 20점


4. 테이블을 만들기 - 20점

사용자는 DBTEST 비밀번호 1234

5. 데이터를 3개씩 삽입하기 - 20점

---

 

 

1. 개념적 설계 

(1) ENTITY, RELATION, ATTRIBUTE 

엔티티명 - 포함 속성 

의사 : 의사 번호(PK), 담당진료 과목, 성명, 성별, 전화번호, 이메일, 직급

간호사 : 간호사 번호(PK), 담당 업무, 성명, 성별, 전화번호, 이메일, 직급

환자 : 환자 번호(PK), 담당의사(FK), 담당간호사(FK), 환자 성명, 주민번호, 성별, 주소, 전화번호, 이메일, 직업 

진료 : 진료 번호(PK), 환자번호(PF), 의사번호(PF), 진료 내용, 진료 날짜

차트 : 차트 번호(PK), 진료 번호(FK), 의사 번호(FK), 환자 번호(FK), 간호사 번호(FK), 의사 소견내용 기록 

(2) - ER다이어그램 표기

 

2. 논리적 설계 

DOCTORS TABLE : 기본키(PK)- 의사번호 DOC_ID

NURSE TABLE : 기본키(PK) - 간호사 번호 NUR_ID

PATIENTS TABLE : 기본키(PK) - 환자 번호 PAT_ID

                            외래키(FK) : 의사,간호사 DOC_ID / NUR_ID

TREATMENTS TABLE : 기본키 (PK) - 진료 번호 TREAT_ID , 환자 번호 PAT_ID, 의사 번호 DOC_ID

                                외래키 (FK) - 의사, 환자번호 pat_id,  doc_id  

CHARTS TABLE : 기본키 (PK) - 차트 번호 CHART_ID, 진료 번호 TREAT_ID,  의사번호 DOC_ID , 환자 번호 PAT_ID 

                          외래키 (FK) - 간호사 번호 nur_id, 진료 번호 TREAT_ID, 의사 번호 DOC_ID, 환자 번호 PAT_ID

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

 

 

3. 물리적 모델링 

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

 4. 테이블 만들기

계정 생성

 

DOCTORS TABLE 

CREATE TABLE Doctors(  

    doc_id        NUMBER(10) NOT NULL,  

major_treat   VARCHAR2(25) NOT NULL, 

doc_name      VARCHAR2(20) NOT NULL,  

doc_gen       char(1)     NOT NULL,  

doc_phone     VARCHAR2(15) NULL,  

doc_email     VARCHAR2(50) UNIQUE,  

doc_position  VARCHAR2(20) NOT NULL

); 

ALTER TABLE Doctors   

ADD CONSTRAINT doc_id_pk PRIMARY key(doc_id);

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

NURSE TABLE

CREATE TABLE Nurses -- 간호사

(  nur_id        NUMBER(10) NOT NULL,  

major_treat   VARCHAR2(25) NOT NULL,  

nur_name      VARCHAR2(20) NOT NULL,  

nur_gen       char(1)     NOT NULL,  

nur_phone     VARCHAR2(15) NULL,  

nur_email     VARCHAR2(50) UNIQUE,  

nur_position  VARCHAR2(20) NOT NULL

);

ALTER TABLE Nurses

ADD CONSTRAINT nur_id_pk PRIMARY key(nur_id);

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

PATIENTS TABLE

CREATE TABLE Patients -- 환자

(  

pat_id        NUMBER(10) NOT NULL,  

nur_id        NUMBER(10) NOT NULL,  

doc_id        NUMBER(10) NOT NULL,  

pat_name      VARCHAR2(20) NOT NULL,  

pat_gen       CHAR(1) NOT NULL,  

pat_jumin     VARCHAR2(14) NOT NULL,  

pat_addr      VARCHAR2(100) NOT NULL,  

pat_phone     VARCHAR2(15) NULL,  

pat_email     VARCHAR2(50) UNIQUE,  

pat_job       VARCHAR2(20) NOT NULL 

 );

ALTER TABLE Patients

ADD CONSTRAINT pat_id_pk PRIMARY key(pat_id);

 

ALTER TABLE Patients

ADD CONSTRAINT R_2 FOREIGN KEY(doc_id)

                   REFERENCES Doctors(doc_id);      

            

 ALTER TABLE Patients

ADD CONSTRAINT R_3 FOREIGN KEY(nur_id)

                   REFERENCES Nurses(nur_id);         

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

TREATMENTS TABLE

CREATE TABLE Treatments -- 진료기록

(  

treat_id        NUMBER(15) NOT NULL,  

pat_id          NUMBER(10) NOT NULL,  

doc_id          NUMBER(10) NOT NULL,  

treat_contents  VARCHAR2(1000) NOT NULL,  

treat_date      DATE NOT NULL

);

ALTER TABLE Treatments

ADD CONSTRAINT treat_id_pk PRIMARY key(treat_id, pat_id, doc_id);

 

ALTER TABLE Treatments

ADD CONSTRAINT R_5 FOREIGN KEY(pat_id) 

                 REFERENCES Patients(pat_id);

 

ALTER TABLE Treatments

ADD CONSTRAINT R_6 FOREIGN KEY(doc_id)

                   REFERENCES Doctors(doc_id);

 

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

CHARTS TABLE

CREATE TABLE Charts(  

chart_id      VARCHAR2(20) NOT NULL, 

treat_id      NUMBER(15) NOT NULL,  

doc_id        NUMBER(10) NOT NULL,  

pat_id        NUMBER(10) NOT NULL,  

nur_id        NUMBER(10) NOT NULL,  

chart_content VARCHAR2(1000) NOT NULL

);

 

ALTER TABLE Charts

ADD CONSTRAINT chart_treat_doc_pat_id_pk PRIMARY key(chart_id, treat_id, doc_id, pat_id);

 

ALTER TABLE Charts

ADD CONSTRAINT R_4 FOREIGN KEY(nur_id)

                   REFERENCES Nurses(nur_id);

 

ALTER TABLE Charts

ADD CONSTRAINT R_7 FOREIGN KEY(treat_id, pat_id, doc_id)

                   REFERENCES Treatments(treat_id, pat_id, doc_id);   

 

 

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

5. 데이터 3개씩 각 테이블마다 추가 하여 SELECT 출력

 

 

데이터베이스 구축평가.sql

0.01MB

데이터베이스 구축평가.docx

0.14MB