1) 삼성마트에 회원으로 가입하려면 회원아이디, 비밀번호, 이름, 나이, 직업을 입력해야 한다.
2) 가입한 회원에게는 등급과 적립금이 부여된다.
3) 회원은 회원아이디로 식별한다.
(회원아이디(식별), 비밀번호, 이름, 나이, 직업, 등급, 적립금)
동사(관계의 연관성 O, X)
- 입력해야 한다.(X)
- 부여된다.(X)
- 식별한다.(X)
4) 상품에 대한 상품번호, 상품명,재고량,단가 정보를 유지해야 한다.
5) 상품은 상품번호로 식별한다.
(상품, 상품번호(식별), 상품명, 재고량, 단가)
6) 회원은 여러 상품을 주문할 수 있고, 하나의 상품을 여러 회원이 주문할 수 있다.
7) 회원이 상품을 주문하면 주문에 대한 주문번호, 주문수량,배송지,주문일자 정보를 유지해야 한다.
(주문, 상품번호, 회원번호, 주문번호, 주문수량, 배송지, 주문일자)
동사(관계의 연관성 O, X)
- 주문 관계를 추출한다.(회원은 여러 상품을 주문할 수 있고, 하나의 상품을 여러 회원이 주문 할 수 있다. N:M 다대다 관계)
- 회원이 상품을 반드시 주문해야 하는가? 아니다. -선택적 참여
- 회원이 주문하지 않는 상품이 존재할 수 있나? 있다. - 선택적 참여
8) 각 상품은 한 제조업체가 공급하고, 제조업체 하나는 여러 상품을 공급할 수 있다.
9) 제조업체가 상품을 공급하면 공급일자와 공급량 정보를 유지해야 한다.
10)제조업체에 대한 제조업체명, 전화번호, 위치, 담당자 정보를 유지해야 한다.
11)제조업체는 제조업체명으로 식별한다.
(제조업체, 공급일자, 공급량, 제조업체명(식별), 전화번호, 위치, 담당자)
동사(관계의 연관성 O, X)
- 공급 관계를 추출한다.(상품은 1개의 제조 업체만이 공급하며, 제조업체는 여러 상품 공급 가능하기에 1:N 관계이다.)
- 그렇다면 상품은 반드시 제조업체가 공급해야 하는가? 맞다. 필요함 - 필수적 참여
- 제조업체는 상품을 무조건 공급해야 하는가? 아니다. 안하는 제조업체도 있을 수 있음 - 선택적 참여
12) 회원은 게시글을 여러개 작성할 수 있고, 게시글 하나는 한명의 회원만 작성할 수 있다.
13) 게시글에 대한 글번호, 글제목, 글내용,작성일자 정보를 유지해야 한다.
14) 게시글은 글번호로 식별한다.
(게시글, 글 번호(식별), 글제목, 글내용, 작성일자)
동사(관계의 연관성 O, X)
- 작성할 수 있다. 회원과 게시글 작성(회원은 게시글을 여러개 작성할 수 있고, 게시글 하나는 한명의 회원만 작성할 수 있다. 1:N)
- 작성 관계
- 회원은 반드시 게시글 작성해야 하는가? 아니다. 안 작성할 수 있음 - 선택적 참여
- 그렇다면.. 게시글은 반드시 회원이 작성해야 하는가? 맞다. 한명의 회원이 작성해야 함 - 필수적 참여
개념적 모델링
: 데이터 요소를 추출하고 데이터 요소 간의 관계를 파악해서 표현개념적 스키마
= 개념적 구조 : 개념적 설계의 결과물**명사**
**동사**
약한개체가 참여하는
일 대 다 관계는 외래키를 포함해서 기본키로 지정한다.1) 의사 한 명은 여러 명의 환자를 맡고, 환자 한 명은 의사에게 진료를 받는다.
또한 의사는 환자에 대한 모든 정보를 검색할 수 있다.
2) 환자는 진료 내용등의 정보를 검색할 수 있으며, 담당의사에 대해서 조회 가능하다.
3) 간호사는 병원 진료 차트 정보를 검색할 수 있으며, 의사와 더불어 환자를 관리한다.
간호사 업무는 여러 환자들의 진료접수, 환자 접수, 차트 관리 등을 수행하는 것이다.
4) 모든 의사는 의사ID를 부여하여 식별하며 담당진료과목, 성명, 성별, 연락처, 이메일, 직급을
가진다.
5) 모든 환자는 환자ID를 부여하여 식별하며 담당의사, 담당간호사, 환자성명, 주민번호, 성별, 주소, 연락처, 이메일, 직업을
가진다.
6) 모든 간호사는 간호사ID를 부여하여 식별하며, 담당업무, 성명, 성별, 전화번호, 이메일, 직급을 가진다.
7) 진료ID는 날짜+진료순번으로 부여하고 의사ID, 환자ID, 진료내용, 진료 날짜를 포함한다.
8) 진료를 받은 환자에 대한 내용을 차트에 기록하고, 차트는 차트번호를 부여하여 식별하며 담당 의사ID, 간호사 ID, 환장ID, 진료 ID, 의사 소견
을 차트 내용에 기록
⚡ 강사님 내용
의사 : 환자 ⇒ 1:N, 0 OR 1 OR MANY
의사 : 진료 ⇒ 1:N, 0 OR 1 OR MANY
(의사) 진료 : 차트 ⇒ 1:1 ****0 OR 1(차트를 통해 볼 수 있음)간호사 : 환자 ⇒ 1:N, 0 OR 1 OR MANY
간호사 : 차트 ⇒ 1:N, 0 OR 1 OR MANY환자 : 진료 ⇒ 1:N, 0 OR 1 OR MANY(진료를 통해서 차트를 볼 수 있음)
진료 : 차트 ⇒ 1:1 ****0 OR 1 (진료를 안볼 수도 있지만 보면 무조건 있어야 함)
✔️ 내가 한 것
DROW.IO ER다이어그램 작성
ERD CLOUD
SQL문 작성 + 데이터 입력
CREATE TABLE doctors (
doc_id number(10) NOT NULL,
major_treat varchar2(30) NOT NULL,
doc_name varchar2(30) NOT NULL,
doc_gender char(1) NOT NULL,
doc_phone varchar2(15) NULL,
doc_email varchar2(70) UNIQUE,
doc_dept varchar2(30) NOT NULL
);
ALTER TABLE doctors ADD CONSTRAINT doc_id_pk PRIMARY KEY(doc_id);
CREATE TABLE patients (
pat_id number(10) NOT NULL,
nur_id number(10) NOT NULL,
doc_id number(10) NOT NULL,
pat_name varchar2(30) NOT NULL,
pat_gender char(1) NOT NULL,
pat_secur_num varchar2(30) NOT NULL,
pat_address varchar2(100) NULL,
pat_phone varchar2(30) NULL,
pat_email varchar2(70) UNIQUE,
pat_job varchar2(50) NULL
);
drop table patients;
drop table Treatments;
drop table charts;
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));
CREATE TABLE nurses (
nur_id number(10) NOT NULL,
nur_task varchar2(70) NOT NULL,
nur_name varchar2(30) NOT NULL,
nur_gender char(1) NULL,
nur_phone varchar2(30) NOT NULL,
nur_email varchar2(70) UNIQUE,
nur_dept varchar2(30) NOT NULL
);
ALTER TABLE nurses ADD (CONSTRAINT nur_id_pk PRIMARY KEY(nur_id));
CREATE TABLE Treatments (
treat_id number(10) NOT NULL,
pat_id number(10) NOT NULL,
doc_id number(10) NOT NULL,
treat_content varchar2(100) NOT NULL,
treat_date DATE NOT NULL
);
-- 이 모든 아이디들이 같이 묶여 있다. => 연결이 된다.
ALTER TABLE Treatments ADD CONSTRAINT treat_doc_id_pk PRIMARY KEY(treat_id,pat_id,doc_id);
ALTER TABLE Treatments ADD (CONSTRAINT R_4 FOREIGN KEY (pat_id) REFERENCES patients(pat_id));
ALTER TABLE Treatments ADD (CONSTRAINT R_5 FOREIGN KEY (doc_id) REFERENCES Doctors(doc_id));
CREATE TABLE charts (
cha_id VARCHAR2(50) 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,
cha_content varchar2(100) NOT NULL
);
drop table charts;
ALTER TABLE charts ADD (CONSTRAINT chart_treat_doc_pat_id_pk PRIMARY KEY(cha_id,pat_id,doc_id,treat_id));
ALTER TABLE charts ADD (CONSTRAINT R_6 FOREIGN KEY (nur_id) REFERENCES nurses(nur_id));
ALTER TABLE charts ADD (CONSTRAINT R_7 FOREIGN KEY (doc_id, pat_id,treat_id) REFERENCES Treatments(doc_id, pat_id,treat_id));
-- doctors 데이터 입력
INSERT INTO doctors VALUES(980312, '소아과', '이태정', 'M', '010-333-1340', 'ltj@hanhh.com', '과장');
INSERT INTO doctors VALUES(0000601, '내과', '안성기', 'M', '011-222-0987', 'ask@hanhh.com', '과장');
INSERT INTO doctors VALUES(001208, '외과', '김민종', 'M', '010-333-8743', 'kmj@han.com', '과장');
INSERT INTO doctors VALUES(020403, '피부과', '이태서', 'M', '019-777-3764', 'lts@hanhh.com', '과장');
INSERT INTO doctors VALUES(050900, '소아과', '김연아', 'F', '010-555-4746', 'kya@hanhh.com', '전문의');
INSERT INTO doctors VALUES(050101, '내과', '차태현', 'M', '011-222-7643', 'cth@hanhh.com', '전문의');
INSERT INTO doctors VALUES(062019, '소아과', '전지현', 'F', '010-999-1265', 'jjh@hanhh.com', '전문의');
INSERT INTO doctors VALUES(070576, '피부과', '홍길동', 'M', '016-333-7263', 'hgd@hanhh.com', '전문의');
INSERT INTO doctors VALUES(080543, '방사선과', '유재석', 'M', '010-222-1263', 'yjs@hanhh.com', '과장');
INSERT INTO doctors VALUES(091001, '외과', '김병만', 'M', '010-555-3542', 'kbm@hanhh.com', '전문의');
COMMIT;
SELECT *
FROM DOCTORS;
-- NURSES 데이터 입력
INSERT INTO nurses VALUES(050302, '소아과', '김은영', 'F', '010-555-8751', 'key@hanhh.com', '수간호사');
INSERT INTO nurses VALUES(050021, '내과', '윤성애', 'F', '016-333-8745', 'ysa@hanhh.com', '수간호사');
INSERT INTO nurses VALUES(040089, '피부과', '신지원', 'M', '010-666-7676', 'sjw@hanhh.com', '주임');
INSERT INTO nurses VALUES(070605, '방사선과', '유정화', 'F', '010-333-4588', 'yjh@hanhh.com', '주임');
INSERT INTO nurses VALUES(070804, '내과', '라히나', 'F', '010-222-1340', 'njn@hanhh.com', '주임');
INSERT INTO nurses VALUES(071018, '소아과', '김화경', 'F', '010-888-4116', 'khk@hanhh.com', '주임');
INSERT INTO nurses VALUES(100356, '소아과', '이선용', 'M', '010-777-1234', 'lsy@hanhh.com', '간호사');
INSERT INTO nurses VALUES(104145, '외과', '김현', 'M', '010-999-8520', 'kh@hanhh.com', '간호사');
INSERT INTO nurses VALUES(120309, '피부과', '박성완', 'M', '010-777-4996', 'psw@hanhh.com', '간호사');
INSERT INTO nurses VALUES(130211, '외과', '이서연', 'F', '010-222-3214', 'lsy2@hanhh.com', '간호사');
SELECT *
FROM nurses;
COMMIT;
-- patients 데이터 삽입
INSERT INTO patients VALUES(2345, 050302, 980312, '안상건', 'M', 232345, '서울', '010-555-7845', 'ask@ab.com', '회사원');
INSERT INTO patients VALUES(3545, 040089, 020403, '김성룡', 'M', 543545, '서울', '010-333-7812', 'ksr@ab.com', '자영업');
INSERT INTO patients VALUES(3424, 070605, 080543, '이종진', 'M', 433424, '부산', '019-888-4859', 'ljj@ab.com', '회사원');
INSERT INTO patients VALUES(7675, 100356, 050900, '최광석', 'M', 677675, '당진', '010-222-4847', 'cks@ab.com', '회사원');
INSERT INTO patients VALUES(4533, 070804, 000601, '정한경', 'M', 744533, '강릉', '010-777-9630', 'jhk@ab.com', '교수');
INSERT INTO patients VALUES(5546, 120309, 070576, '유원현', 'M', 765546, '대구', '016-777-0214', 'ywh@ab.com', '자영업');
INSERT INTO patients VALUES(4543, 070804, 050101, '최재정', 'M', 454543, '부산', '010-555-4187', 'cjj@ab.com', '회사원');
INSERT INTO patients VALUES(9768, 130211, 091001, '이진희', 'F', 119768, '서울', '010-888-3675', 'ljh@ab.com', '교수');
INSERT INTO patients VALUES(4234, 130211, 091001, '오나미', 'F', 234234, '속초', '010-999-6541', 'onm@ab.com', '학생');
INSERT INTO patients VALUES(7643, 071018, 062019, '송성묵', 'M', 987643, '서울', '010-222-5874', 'ssm@ab.com', '학생');
SELECT *
FROM patients;
COMMIT;
-- Treatments 데이터 삽입
INSERT INTO Treatments VALUES(130516023, 2345, 980312, '감기, 몸살', '2013-05-16');
INSERT INTO Treatments VALUES(130628100, 3545, 020403, '피부 트러블 치교', '2013-06-28');
INSERT INTO Treatments VALUES(131205056, 3424, 080543, '목 디스크로 MRI 촬영', '2013-12-05');
INSERT INTO Treatments VALUES(131218024, 7675, 050900, '중이염', '2013-12-18');
INSERT INTO Treatments VALUES(131224012, 4533, 000601, '장염', '2013-12-24');
INSERT INTO Treatments VALUES(140103001, 5546, 070576, '여드름 치료', '2014-01-03');
INSERT INTO Treatments VALUES(140109026, 4543, 050101, '위염', '2014-01-09');
INSERT INTO Treatments VALUES(140226102, 9768, 091001, '화상치료', '2014-02-26');
INSERT INTO Treatments VALUES(140303003, 4234, 091001, '교통사고 외상 치료', '2014-03-03');
INSERT INTO Treatments VALUES(140308087, 7643, 062019, '장염', '2014-03-08');
SELECT *
FROM Treatments;
COMMIT;
-- charts 데이터 삽입
INSERT INTO charts VALUES('p_130516023', 130516023, 980312, 2345, 050302, '감기 주사 및 약 처방');
INSERT INTO charts VALUES('d_130628100', 130628100, 020403, 3545, 040089, '피부 감염 방지 주사');
INSERT INTO charts VALUES('r_131205056', 131205056, 080543, 3424, 070605, '주사 처방');
INSERT INTO charts VALUES('p_131218024', 131218024, 050900, 7675, 100356, '귓속 청소 및 약 처방');
INSERT INTO charts VALUES('i_131224012', 131224012, 000601, 4533, 070804, '장염 입원 치료');
INSERT INTO charts VALUES('d_140103001', 140103001, 070576, 5546, 120309, '여드름 치료약 처방');
INSERT INTO charts VALUES('i_140109026', 140109026, 050101, 4543, 070804, '위내시경');
INSERT INTO charts VALUES('s_140226102', 140226102, 091001, 9768, 130211, '화상 크림약 처방');
INSERT INTO charts VALUES('s_140303003', 140303003, 091001, 4234, 130211, '입원 치료');
INSERT INTO charts VALUES('p_140308087', 140308087, 062019, 7643, 071018, '장염 입원 치료');
SELECT *
FROM charts;
COMMIT;
-- 문제 1. 홍길동 의사가 맡고 있던 담당 진료과목이 피부과에서 소아과로 변경되어
-- 내일부터 진료를 시작할 예정입니다. 이 정보에 대한 테이블 정보를 변경하고 변경된 결과를 출력하세요.
UPDATE doctors
SET major_treat = '소아과'
WHERE doc_name = '홍길동';
COMMIT;
SELECT *
FROM doctors
WHERE doc_name = '홍길동';
-- 문제2. 김은영 간호사는 대학원 진학으로 오늘까지만 근무하고 퇴사합니다.
-- 이 정보에 대한 테이블 정보를 변경하고, 변경된 정보를 출력하세요.
SELECT *
FROM nurses;
-- 1.
UPDATE NURSES SET nur_dept ='퇴사 예정'
WHERE NUR_NAME = '김은영';
commit;
SELECT * FROM NURSES;
-- 2. 데이터 전부를 바꾸기
SELECT *
FROM patients
WHERE nur_id = 50302;
UPDATE patients
SET nur_id = '71018'
WHERE nur_id = 50302; -- 김은영이 담당하는 환자를 같은 소아과 간호사와 바꿔줌
UPDATE charts
SET nur_id = '71018'
WHERE nur_id = 50302; -- 김은영이 담당하는 환자차트 또한 같은 소아과 간호사와 바꿔줌
commit;
DELETE nurses
WHERE nur_id = 50302;
COMMIT;
SELECT *
FROM nurses;
-- 문제3. 담당 진료과목이 '소아과' 의사인 의사에 대한 정보를 출력하세요
SELECT *
FROM doctors
WHERE major_treat='소아과';
-- 문제4. 홍길동 의사에게 진료 받은 환자에 대한 모든 정보를 출력하세요.
SELECT *
FROM patients;
SELECT doc_id
FROM doctors
WHERE doc_name='홍길동'; -- 홍길동의 doc_id는 70576
-- 결과 내기
SELECT *
FROM patients
WHERE doc_id=(
SELECT doc_id
FROM doctors
WHERE doc_name='홍길동'
);
-- 문제5. 진료날짜가 2013년 12월인 환자에 대한 모든 정보를 오름차순 정렬하여 출력하세요
SELECT *
FROM treatments;
SELECT *
FROM patients , treatments
WHERE treat_date IN(
SELECT treat_date
FROM treatments
WHERE treat_date BETWEEN '13/12/01' AND '13/12/31'
)
ORDER BY treat_date ASC;
-- 문제6. 간호사ID가 05로 시작하는 모든 간호사 정보를 출력하세요
SELECT *
FROM nurses
WHERE nur_id LIKE '5%';
SELECT *
FROM nurses
WHERE lpad(nur_id, 6, 0) LIKE '05%';
-- 문제7. 의사별로 차트 내용과 환자의 정보를 출력하세요
SELECT d.doc_name, c.cha_id, c.doc_id, c.nur_id, c.pat_id, c.treat_id, c.cha_content, p.*
FROM doctors d, charts c, patients p
WHERE d.doc_id=c.doc_id AND d.doc_id=p.doc_id;
-- 문제8. 외과 '김병만' 전문의가 진료한 환자는 몇 명인가요?
-- 1번 방법
SELECT COUNT(p.pat_name)
FROM patients p, doctors d
WHERE d.doc_id=p.doc_id AND doc_name='김병만' AND d.major_treat='외과';
-- 2번 방법
SELECT COUNT(pat_name)
FROM patients
WHERE doc_id IN (SELECT doc_id
FROM doctors
WHERE doc_name='김병만' AND major_treat='외과');
-- 문제9. 환자번호 7643 환자의 진료내용과 날짜 ,담당 간호사와 담당 의사는 누구인가요?
SELECT t.treat_content, t.treat_date, n.nur_name, d.doc_name
FROM patients p, treatments t, nurses n, doctors d
Where p.pat_id = 7643 AND p.pat_id=t.pat_id AND p.nur_id = n.nur_id AND p.doc_id=d.doc_id;
-- 확인해보기
SELECT *
FROM patients
WHERE pat_id = 7643 ; -- NUR_ID = 71018 DOC_ID = 62019
SELECT *
FROM doctors
WHERE doc_id = 62019 ;
SELECT *
FROM nurses
WHERE nur_id = 71018;
SELECT *
FROM treatments
WHERE pat_id = 7643;
-- 문제10. 환자번호 9768 환자는 처방전을 재발행을 신청하였다. 진료받은 결과를 출력하세요.
SELECT *
FROM charts
WHERE pat_id IN(SELECT pat_id
FROM patients
WHERE pat_id=9768);
모델링이 여태 배운 것중에 제일 헤갈렸다 휴... 그래도 조금 이해가 된 듯