데이터베이스 5주차 과제

병원용 DB 설계 및 구축 실습 과제

---

이 과제는 6주차 수업 이전까지 제출하여 주세요. (txt 파일 안에 쿼리 형태로 작성하여 제출)

 

과제에 대한 자세한 설명을 5주차 수업중에 공지하겠습니다.

 

 

 

1. 다음과 같은 DB구축 요구사항에 대해

 

적절한 테이블 생성 쿼리를 작성해 제출하세요.

 

(각 테이블의 고유 식별자는 자동증가되는 BIGINT 타입 숫자로 설계)

 

 

 

 * 병원용 DB구축 요구사항  

 

  - 환자 테이블

 

   > 이름, 주민번호, 전화번호, 주소

 

  - 의사 테이블

 

   >  이름, 직책, 담당과목

 

  - 환자 진료 기록 및 예약 테이블

 

   >  환자식별자, 의사식별자, 진료일시, 진료접수사유, 진료비, 진료진행내용

 

 

 

2. 다음과 같은 레코드 추가를 위한

 

  적절한 INSERT 쿼리를 작성해 제출하세요.

 

   

 

  * 병원용 DB구축 완료 후 추가되어야할 레코드

 

  - 환자 정보

 

   > 김철수, 841111-1222222, 010-1234-5678, 서울 강남구

 

   > 손흥민, 920507-1222223, 010-4321-5678, 영국 토트넘

 

   > 김연아, 900905-2222222, 010-8888-7777, 경기도 부천

 

  

 

  - 의사 정보

 

   > 김영진, 과장, 이비인후과

 

   > 손연재, 원장, 외과

 

  

 

  - 진료 정보

 

   > 환자 김연아의식별자, 의사 김영진의식별자, 2023-04-01 11:00:00, 구강염, 5000, 알보칠 처방

 

   > 환자 김철수의식별자, 의사 김영진의식별자, 2023-04-01 12:00:00, 비염, 3000, 항히스타민 처방

 

   > 환자 손흥민의식별자, 의사 손연재의식별자, 2023-04-01 13:00:00, 다리골절, 150000, 다리 깁스 처리

 

   > 환자 손흥민의식별자, 의사 손연재의식별자, 2023-04-03 11:00:00, 골절, NULL, NULL

 

   

 

3. 다음과 같은 요구사항을 달성하기 위한 적절한 쿼리를 작성해 제출하세요.

 

  

 

  * 병원용 DB구축 완료 후 요구사항

 

  - 4월1일자 진료비 총액수를 알고 싶어요

 

  - 손흥민 환자의 모든 과거 진료 기록과 예약 내역을 보여주세요

 

  - 김영진 의사의 모든 과거 진료 기록을 보여주세요

 

  - 각 환자들의 상세정보와 상세 진료기록을 보여주세요

 

 

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

1.

CREATE DATABASE hospital;

USE hospital;

CREATE TABLE patient(
patient_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(10),
id_num VARCHAR(20),
tel VARCHAR(15),
address VARCHAR(100)
);
CREATE TABLE doctor(
doctor_id BIGINT NOT NULL AUTO _INCREMENT PRIMARY KEY,
name VARCHAR(15),
position VARCHAR(10),
subject VARCHAR(15)
);
CREATE TABLE diary(
diary_id BIGINT NOT NULL AUTO _INCREMENT PRIMARY KEY,
patient_id BIGINT, 
doctor_id BIGINT,
date DATE,
why VARCHAR(50),
price BIGINT,
detail VARCHAR(100)
FOREIGN KEY (patient_id) REFERENCES patient(patient_id)
FOREIGN KEY (doctor_id) REFERENCES doctor(doctor_id)
);

2.


INSERT INTO patient(name, id_num, tel, address)
VALUES ('김철수', '841111-1222222', '010-1234-5678', '서울 강남구');
INSERT INTO patient(name, id_num, tel, address)
VALUES ('손흥민', '920507-1222223', '010-4321-5678', '영국 토트넘');
INSERT INTO patient(name, id_num, tel, address)
VALUES ('김연아', '900905-2222222', '010-8888-7777', '경기도 부천');

INSERT INTO doctor(name, position, subject)
VALUES ('김영진', '과장', '이비인후과')
INSERT INTO doctor(name, position, subject)
VALUES ('손연재', '원장', '외과');

INSERT INTO diary(patient_id, doctor_id, date, why, price, detail)
VALUES(3, 1, '2023-04-01 11:00:00', '구강염', 5000, '알보칠 처방');
INSERT INTO diary(patient_id, doctor_id, date, why, price, detail)
VALUES(1, 1, '2023-04-01 12:00:00', '비염', 3000, '항히스타민 처방');
INSERT INTO diary(patient_id, doctor_id, date, why, price, detail)
VALUES(2, 2, '2023-04-01 13:00:00', '다리골절', 150000, '다리 깁스 처방');
INSERT INTO diary(patient_id, doctor_id, date, why, price, detail)
VALUES(2, 2, '2023-04-03 11:00:00', '골절', NULL, NULL);

3.

3-1 
SELECT SUM(price) FROM diary WHERE date = '2023-04-01';

3-2
SELECT * FROM diary WHERE patient_id = 2;

3-3
SELECT date FROM diary WHERE doctor_id =1;

3-4
SELECT * FROM patient LEFT OUTER JOIN diary ON patient.patient_id = diary.patient_id;