요구사항

개념 ERD

테이블 명세서

데이터 모델링

1. 권한을 부여한다.

https://forest-of-coding.tistory.com/6

위의 객체 및 데이터 조작 권한 부여 참조하여 cmd에서 권한을 부여해야 한다.

2. Oracle SQL Developer에 학사관리시스템으로 접속한다.

테이블이 없음을 확인한다.

3. 테이블을 생성하여 Logical 로 모델링을 완성한다.

https://forest-of-coding.tistory.com/21

위의 ERwin Data Modeler 사용 방법을 토대로 아래와 같이 모델링을 완성한다.

+ zero인 경우가 없기에 전부 One or More로 변경한다.

4. 테이블을 생성하여 Physical 로 모델링을 완성한다.

5. 속성 타입과 길이, Null 여부를 기입한다.

6. Oracle SQL Developer 와 ERWin 설정과 연결을 참조하여 연동한다.

https://forest-of-coding.tistory.com/21
 

위의 페이지 Oracle SQL Developer 와 ERWin 설정과 연결을 참조하여 그대로 진행한다.

데이터 삽입

select * from student;

insert into student (std_no, std_name, std_id, std_telno, std_address, pro_no)
    values ('23AA100001', '김철수', '951201-2014216', '010-0123-3456', '대전광역시 서구', '22A01');
insert into student (std_no, std_name, std_id, std_telno, std_address, pro_no)
    values ('23AB100301', '강서주', '940325-2123452', '010-1234-4567', '대전광역시 동구', '20B03');
insert into student (std_no, std_name, std_id, std_telno, std_address, pro_no)
    values ('23AA100034', '임석일', '991201-2031511', '010-6789-1011', '대전광역시 서구', '19A04');
insert into student (std_no, std_name, std_id, std_telno, std_address, pro_no)
    values ('23BB200099', '곽재우', '961201-2211001', '010-1011-1213', '서울특별시 동작구', '19D02');
insert into student (std_no, std_name, std_id, std_telno, std_address, pro_no)
    values ('23CR600024', '이미지', '970111-2405216', '010-1415-2425', '서울특별시 송파구', '18Z19');
insert into student (std_no, std_name, std_id, std_telno, std_address, pro_no)
    values ('23AC200010', '이성종', '951201-2014216', '010-4569-2013', '대구광역시 중구', '23D06');
insert into student (std_no, std_name, std_id, std_telno, std_address, pro_no)
    values ('21AA100001', '박무현', '971003-2204324', '010-4321-9876', '대구광역시 서구', '20F30');
insert into student (std_no, std_name, std_id, std_telno, std_address, pro_no)
    values ('22CA100012', '강하진', '960511-2042152', '010-5748-0133', '대전광역시 유성구', '20F32');
insert into student (std_no, std_name, std_id, std_telno, std_address, pro_no)
    values ('23CC300012', '김명수', '980220-2112012', '010-5422-0000', '대전광역시 서구', '10A11');
insert into student (std_no, std_name, std_id, std_telno, std_address, pro_no)
    values ('13BD600034', '이경민', '930630-2462021', '010-9999-1234', '대전광역시 서구', '20S62');

--
select * from subject;

insert into subject (sub_no, sub_gubun, sub_name, sub_credit)
    values ('231AB101', 'A', '영어 스피치', 3);
insert into subject (sub_no, sub_gubun, sub_name, sub_credit)
    values ('231AB113', 'B', '영어의 역사', 2);
insert into subject (sub_no, sub_gubun, sub_name, sub_credit)
    values ('202AA206', 'A', '글쓰기', 4);
insert into subject (sub_no, sub_gubun, sub_name, sub_credit)
    values ('132AC303', 'B', '뇌과학', 3);
insert into subject (sub_no, sub_gubun, sub_name, sub_credit)
    values ('221CB302', 'B', '건강 스트레칭', 2);
insert into subject (sub_no, sub_gubun, sub_name, sub_credit)
    values ('192DC209', 'A', '전자회로', 4);
insert into subject (sub_no, sub_gubun, sub_name, sub_credit)
    values ('171DC205', 'A', 'C언어', 4);
insert into subject (sub_no, sub_gubun, sub_name, sub_credit)
    values ('172DC201', 'A', 'C언어 심화', 4);
insert into subject (sub_no, sub_gubun, sub_name, sub_credit)
    values ('201DD103', 'A', '컴퓨터 구조론', 4);
insert into subject (sub_no, sub_gubun, sub_name, sub_credit)
    values ('222AC204', 'B', '미술학', 3);
insert into subject (sub_no, sub_gubun, sub_name, sub_credit)
    values ('161CB303', 'B', '심리의 이해', 2);

--
select * from professor;

insert into professor (pro_no, pro_name, pro_major, pro_deptno, pro_telno)
    values ('22A01', '김승종', '영어영문', '영문', '010-1239-1123');
insert into professor (pro_no, pro_name, pro_major, pro_deptno, pro_telno)
    values ('20B03', '박무진', '영어말하기', '영문', '010-7781-3320');
insert into professor (pro_no, pro_name, pro_major, pro_deptno, pro_telno)
    values ('19A04', '홍길동', '국어국문', '국문', '010-0010-0011');
insert into professor (pro_no, pro_name, pro_major, pro_deptno, pro_telno)
    values ('19D02', '성춘향', '뇌과학', '생명', '010-0000-1111');
insert into professor (pro_no, pro_name, pro_major, pro_deptno, pro_telno)
    values ('18Z19', '장동우', '생활체육', null, '010-3210-3210');
insert into professor (pro_no, pro_name, pro_major, pro_deptno, pro_telno)
    values ('23D06', '김말진', '전기전자', '전자', '010-9999-9150');
insert into professor (pro_no, pro_name, pro_major, pro_deptno, pro_telno)
    values ('20F30', '서모아', '컴퓨터공학', '컴퓨터', '010-3459-0112');
insert into professor (pro_no, pro_name, pro_major, pro_deptno, pro_telno)
    values ('20F32', '이진환', '컴퓨터공학', '컴퓨터', '010-4873-4156');
insert into professor (pro_no, pro_name, pro_major, pro_deptno, pro_telno)
    values ('10A11', '허안현', '정보통신', null, '010-2157-1357');
insert into professor (pro_no, pro_name, pro_major, pro_deptno, pro_telno)
    values ('20S62', '고아섭', '동양화', '예술', '010-3578-0479');

--
select * from class;

insert into class (std_no, sub_no, cls_score)
    values ('23AA100001', '231AB101', 95);
insert into class (std_no, sub_no, cls_score)
    values ('23AB100301', '231AB113', 88);
insert into class (std_no, sub_no, cls_score)
    values ('23AA100001', '231AB113', 60);
insert into class (std_no, sub_no, cls_score)
    values ('23AA100034', '202AA206', 77);
insert into class (std_no, sub_no, cls_score)
    values ('23BB200099', '132AC303', 93);
insert into class (std_no, sub_no, cls_score)
    values ('23CR600024', '221CB302', 5);
insert into class (std_no, sub_no, cls_score)
    values ('23AC200010', '221CB302', 100);
insert into class (std_no, sub_no, cls_score)
    values ('21AA100001', '192DC209', 78);
insert into class (std_no, sub_no, cls_score)
    values ('22CA100012', '171DC205', 67);
insert into class (std_no, sub_no, cls_score)
    values ('23CC300012', '201DD103', 82);

--
select * from counsel;

insert into counsel (cns_date, pro_no, std_no, cns_gubun,cns_remark)
    values ('23-12-14', '22A01', '23AA100001', '1', '성적 향상 관련');
insert into counsel (cns_date, pro_no, std_no, cns_gubun,cns_remark)
    values ('23-12-13', '20B03', '23AB100301', '1', '성적 미달');
insert into counsel (cns_date, pro_no, std_no, cns_gubun,cns_remark)
    values ('23-12-14', '19A04', '23AA100034', '2', '취업 자리 알선');
insert into counsel (cns_date, pro_no, std_no, cns_gubun,cns_remark)
    values ('23-12-14', '19A04', '23AB100301', '2', '취업 알선');
insert into counsel (cns_date, pro_no, std_no, cns_gubun,cns_remark)
    values ('23-11-10', '19D02', '23BB200099', '4', '집안 사정');
insert into counsel (cns_date, pro_no, std_no, cns_gubun,cns_remark)
    values ('23-03-14', '18Z19', '23BB200099', '3', '전과 관련');
insert into counsel (cns_date, pro_no, std_no, cns_gubun,cns_remark)
    values ('23-09-14', '23D06', '23AA100001', '4', '개인사');
insert into counsel (cns_date, pro_no, std_no, cns_gubun,cns_remark)
    values ('20-12-14', '20F30', '23CR600024', '5', '퇴학');
insert into counsel (cns_date, pro_no, std_no, cns_gubun,cns_remark)
    values ('18-10-04', '10A11', '13BD600034', '6', '교환학생');
insert into counsel (cns_date, pro_no, std_no, cns_gubun,cns_remark)
    values ('19-12-14', '10A11', '23CC300012', '6', '교환 학생 정보');

--
select * from lecture;

insert into lecture (pro_no, sub_no, lec_day, lec_time, lec_room, lec_person)
    values ('22A01', '231AB101', '2', '13:00', 'A건물101호', 35);
insert into lecture (pro_no, sub_no, lec_day, lec_time, lec_room, lec_person)
    values ('22A01', '231AB113', '4', '10:00', 'A건물401호', 30);
insert into lecture (pro_no, sub_no, lec_day, lec_time, lec_room, lec_person)
    values ('20B03', '231AB101', '2', '13:00', 'A건물102호', 35);
insert into lecture (pro_no, sub_no, lec_day, lec_time, lec_room, lec_person)
    values ('19A04', '202AA206', '3', '11:00', 'B건물301호', 50);
insert into lecture (pro_no, sub_no, lec_day, lec_time, lec_room, lec_person)
    values ('19D02', '132AC303', '6', '14:00', 'D건물321호', 25);
insert into lecture (pro_no, sub_no, lec_day, lec_time, lec_room, lec_person)
    values ('18Z19', '221CB302', '2', '15:00', 'D건물운동장', 60);
insert into lecture (pro_no, sub_no, lec_day, lec_time, lec_room, lec_person)
    values ('23D06', '192DC209', '5', '15:30', 'D건물431호', 45);
insert into lecture (pro_no, sub_no, lec_day, lec_time, lec_room, lec_person)
    values ('20F30', '171DC205', '3', '09:30', 'F건물131호', 45);
insert into lecture (pro_no, sub_no, lec_day, lec_time, lec_room, lec_person)
    values ('20F32', '171DC205', '2', '13:00', 'F건물135호', 45);
insert into lecture (pro_no, sub_no, lec_day, lec_time, lec_room, lec_person)
    values ('20F32', '172DC201', '5', '16:00', 'F건물211호', 35);
insert into lecture (pro_no, sub_no, lec_day, lec_time, lec_room, lec_person)
    values ('10A11', '201DD103', '3', '12:00', 'B건물213호', 35);
insert into lecture (pro_no, sub_no, lec_day, lec_time, lec_room, lec_person)
    values ('20S62', '222AC204', '4', '13:30', 'S건물101호', 25);
insert into lecture (pro_no, sub_no, lec_day, lec_time, lec_room, lec_person)
    values ('20S62', '161CB303', '6', '11:00', 'S건물101호', 35);

문제

1. 2023년도 1학기 수강생들의 학번, 과목코드, 성적을 내림차순 성적순으로 모두 조회하시오.

2. 과목코드가 231AB101 과목의 수강현황을 학번, 학생명, 과목코드, 과목명, 성적순으로 조회하시오.

3. 2022년도 2학기 총 수강생들은 몇 명인가?

4. 2023년도 1학기 수강생들의 학번, 과목코드, 학점을 조회하시오.

5. 2023년도에 발생한 상담건수를 조회하시오.

6. 수요일에 편성된 강의 목록 전체와 교수명, 교과명을 조회하시오.

7. 12월 중에 이루어진 상담내역과 학생명을 조회하시오.

문제의 해답

1번

-- 1. 2023년도 1학기 수강생들의 학번, 과목코드, 성적을 내림차순 성적순으로 모두 조회하시오.
select std_no 학번, sub_no 과목코드, cls_score 성적 from class 
    where substr(sub_no, 1, 3) = '231'
    order by cls_score desc;

2번

-- 2. 과목코드가 231AB101 과목의 수강현황을 학번, 학생명, 과목코드, 과목명, 성적순으로 조회하시오.
select a.std_no 학번, a.std_name 학생명, c.sub_no 과목코드, c.sub_name 과목명, b.cls_score 성적
    from student a, class b, subject c
    where a.std_no = b.std_no
        and b.sub_no = c.sub_no
        and a.std_no = '23AA100001'
    order by b.cls_score desc;

3번

-- 3. 2022년도 2학기 총 수강생들은 몇 명인가?
select count(*) from subject
    where substr(sub_no, 1, 3) = 222;

4번

-- 4. 2023년도 1학기 수강생들의 학번, 과목코드, 학점을 조회하시오.
select std_no 학번, sub_no 과목코드, 
    case 
        when cls_score >= 95 then 'A+'
        when cls_score >= 90 then 'A0'
        when cls_score >= 85 then 'B+'
        when cls_score >= 80 then 'B0'
        when cls_score >= 75 then 'C+'
        when cls_score >= 70 then 'C0'
        when cls_score >= 65 then 'D+'
        when cls_score >= 60 then 'D0'
        else 'F'
    end as 학점 
    from class
    where substr(sub_no, 1, 3) = 231
    order by cls_score desc;

5번

-- 5. 2023년도에 발생한 상담건수를 조회하시오.
select count(*) from counsel
    where substr(cns_date, 1, 2) = 23;
    -- where cns_date >= '2023-01-01' and cns_date <= '2023-12-23';

6번

-- 6. 수요일에 편성된 강의 목록 전체와 교수명, 교과명을 조회하시오.
select a.*, b.pro_name 교수명, c.sub_name 교과명
    from lecture a, professor b, subject c
    where b.pro_no = a.pro_no
    and c.sub_no = a.sub_no
    and a.lec_day = 4;

7번

-- 7. 12월 중에 이루어진 상담내역과 학생명을 조회하시오.
select a.*, b.std_name 학생명
    from counsel a, student b
    where  a.std_no = b.std_no
    and substr(cns_date, 4, 2) = '12';

-- substr(문자열, 시작위치, 길이)

요구사항

개념 ERD

테이블 명세서

데이터 모델링

1. 권한을 부여한다.

https://forest-of-coding.tistory.com/6

위의 객체 및 데이터 조작 권한 부여 참조하여 cmd에서 권한을 부여해야 한다.

2. Oracle SQL Developer에 다수리시스템으로 접속한다.

테이블이 없음을 확인한다.

3. 테이블을 생성한다.

https://forest-of-coding.tistory.com/21

위의 ERwin Data Modeler 사용 방법을 토대로 아래와 같은 테이블을 우선적으로 생성한다.

4. Logical을 완성한다.

5. 위와 같은 방법으로 Physical을 완성한다.

6. 각자의 타입과 Not NULL 여부를 기입한다.

7. 임의로 다대다 관계로 연결하여 CST_NO을 FK로 가진다.

7번의 경우 위의 개념 ERD와는 다르게 진행한다.

8. 전부 문제가 없다면 3번의 링크를 참조하여 테이블을 Oracle SQL Developer 로 연결하여 확인한다.

데이터 삽입

-- employee 테이블
select * from employee;

insert into employee (emp_no, emp_name, emp_address, emp_telno, emp_salary)
    values ('A101', '김철수', '대전광역시 서구', '010-0123-3456', '120000');
insert into employee (emp_no, emp_name, emp_address, emp_telno, emp_salary)
    values ('A102', '강서주', '대전광역시 동구', '010-1234-4567', '210000');
insert into employee (emp_no, emp_name, emp_address, emp_telno, emp_salary)
    values ('A103', '임석일', '대전광역시 서구', '010-6789-1011', '220000');
insert into employee (emp_no, emp_name, emp_address, emp_telno, emp_salary)
    values ('A104', '곽재우', '서울특별시 동작구', '010-1011-1213', '100000');
insert into employee (emp_no, emp_name, emp_address, emp_telno, emp_salary)
    values ('A105', '이미지', '서울특별시 송파구', '010-1415-2425', '310000');
insert into employee (emp_no, emp_name, emp_address, emp_telno, emp_salary)
    values ('A106', '이성종', '대구광역시 중구', '010-4569-2013', '300000');
insert into employee (emp_no, emp_name, emp_address, emp_telno, emp_salary)
    values ('A107', '박무현', '대구광역시 서구', '010-4321-9876', '205000');
insert into employee (emp_no, emp_name, emp_address, emp_telno, emp_salary)
    values ('A108', '강하진', '대전광역시 유성구', '010-5748-0133', '90000');
insert into employee (emp_no, emp_name, emp_address, emp_telno, emp_salary)
    values ('A109', '김명수', '대전광역시 서구', '010-5422-0000', '410000');
insert into employee (emp_no, emp_name, emp_address, emp_telno, emp_salary)
    values ('A110', '이경민', '대전광역시 서구', '010-9999-1234', '200900');


-- customer 테이블
select * from customer;

insert into customer (cst_no, cst_name, cst_address, cst_telno)
    values ('C101', '김승종', '대전광역시 중구', '010-1239-1123');
insert into customer (cst_no, cst_name, cst_address, cst_telno)
    values ('C102', '박무진', '대전광역시 서구', '010-7781-3320');
insert into customer (cst_no, cst_name, cst_address, cst_telno)
    values ('C103', '홍길동', '대전광역시 유성구', '010-0010-0011');
insert into customer (cst_no, cst_name, cst_address, cst_telno)
    values ('C104', '성춘향', '대전광역시 동구', '010-0000-1111');
insert into customer (cst_no, cst_name, cst_address, cst_telno)
    values ('C105', '장동우', '대전광역시 대덕구', '010-3210-3210');
insert into customer (cst_no, cst_name, cst_address, cst_telno)
    values ('C106', '김말진', '부산광역시 중구', '010-9999-9150');
insert into customer (cst_no, cst_name, cst_address, cst_telno)
    values ('C107', '서모아', '서울특별시 동작구', '010-3459-0112');
insert into customer (cst_no, cst_name, cst_address, cst_telno)
    values ('C108', '이진환', '대전광역시 중구', '010-4873-4156');
insert into customer (cst_no, cst_name, cst_address, cst_telno)
    values ('C109', '허안현', '대전광역시 중구', '010-2157-1357');
insert into customer (cst_no, cst_name, cst_address, cst_telno)
    values ('C110', '고아섭', '대전광역시 동구', '010-3578-0479');


-- car 테이블
select * from car;

insert into car (car_no, car_model, car_year, car_distance, cst_no)
    values ('68오8269', '스즈키', '2014', 62051, 'C101');
insert into car (car_no, car_model, car_year, car_distance, cst_no)
    values ('113서9441', '그랜저820', '2016', 20135, 'C101');
insert into car (car_no, car_model, car_year, car_distance, cst_no)
    values ('222나9651', '모닝', '2016', 182051, 'C102');
insert into car (car_no, car_model, car_year, car_distance, cst_no)
    values ('123두2341', '스파크', '2021', 32105, 'C103');
insert into car (car_no, car_model, car_year, car_distance, cst_no)
    values ('134누9963', '모닝', '2019', 2113, 'C104');
insert into car (car_no, car_model, car_year, car_distance, cst_no)
    values ('123가4568', '쏘나타', '2010', 354021, 'C105');
insert into car (car_no, car_model, car_year, car_distance, cst_no)
    values ('210가1234', '폭스바겐', '2013', 547311, 'C106');
insert into car (car_no, car_model, car_year, car_distance, cst_no)
    values ('152가3018', '볼보', '2004', 543210, 'C107');
insert into car (car_no, car_model, car_year, car_distance, cst_no)
    values ('158가4832', '벌칸', '2013', 210, 'C107');
insert into car (car_no, car_model, car_year, car_distance, cst_no)
    values ('380우4104', '레인지', '2019', 213483, 'C108');
insert into car (car_no, car_model, car_year, car_distance, cst_no)
    values ('201수1474', '오펠 록스', '2010', 123087, 'C109');
insert into car (car_no, car_model, car_year, car_distance, cst_no)
    values ('105가4203', '벌칸', '2020', 456210, 'C110');
insert into car (car_no, car_model, car_year, car_distance, cst_no)
    values ('105가4209', '벤츠', '2023', 10967, 'C110');


-- 서비스 테이블
select * from service;

insert into service (svc_no, svc_time, svc_cost, emp_no, car_no, cst_no)
    values ('201011S001', 10, null, 'A101', '68오8269', 'C101');
insert into service (svc_no, svc_time, svc_cost, emp_no, car_no, cst_no)
    values ('221230S002', 7, null, 'A102', '222나9651', 'C102');
insert into service (svc_no, svc_time, svc_cost, emp_no, car_no, cst_no)
    values ('220101S003', 13, 1000000, 'A103', '123두2341', 'C103');
insert into service (svc_no, svc_time, svc_cost, emp_no, car_no, cst_no)
    values ('201115S004', 1, 50000, 'A101', '134누9963', 'C104');
insert into service (svc_no, svc_time, svc_cost, emp_no, car_no, cst_no)
    values ('230135S005', 3, 100000, 'A104', '123가4568', 'C105');
insert into service (svc_no, svc_time, svc_cost, emp_no, car_no, cst_no)
    values ('230303S006', 50, null, 'A105', '210가1234', 'C106');
insert into service (svc_no, svc_time, svc_cost, emp_no, car_no, cst_no)
    values ('230412S007', 100, 3420000, 'A106', '152가3018', 'C107');
insert into service (svc_no, svc_time, svc_cost, emp_no, car_no, cst_no)
    values ('230501S008', 2160, null, 'A107', '380우4104', 'C108');
insert into service (svc_no, svc_time, svc_cost, emp_no, car_no, cst_no)
    values ('210526S009', 21, 240000, 'A108', '201수1474', 'C109');
insert into service (svc_no, svc_time, svc_cost, emp_no, car_no, cst_no)
    values ('231111S010', 10000, 250000, 'A109', '105가4203', 'C110');
insert into service (svc_no, svc_time, svc_cost, emp_no, car_no, cst_no)
    values ('231213S011', 10, 20000, 'A106', '158가4832', 'C107');

문제

1. 월급이 20만원과 30만원 사이인 직원의 이름, 주소, 연락처, 급여를 구하시오.

2. 고객 'C107'이 수리를 요청한 목록을 수리비의 내림차순으로 출력하시오.

3. 카센터의 직원수와 직원의 평균급여를 출력하시오.

4. 50만원 이상 수리한 자동차 현황을 서비스번호, 자동차번호, 모델, 수리금액, 고객번호, 고객명, 연락처 순으로 조회하시오.

5. 2023년도 수리된 자동차현황을 서비스번호, 자동차번호, 수리시간, 수리금액을 금액이 큰 것부터 조회하시오.

6. 금액기준으로 가장 큰 금액수리를 요청한 고객의 고객번호, 금액을 조회하시오.

문제의 해답

• 1번 문제

-- 1. 월급이 20만원과 30만원 사이인 직원의 이름, 주소, 연락처, 급여를 구하시오.
select emp_name 이름, emp_address 주소, emp_telno 연락처, emp_salary 급여 
    from employee 
    where emp_salary between 200000 and 300000;
    -- where emp_sqlary > 200000 and emp_sqlary < 1300000;

• 2번 문제

-- 2. 고객 'C107'이 수리를 요청한 목록을 수리비의 내림차순으로 출력하시오.
select b.cst_no, b.cst_name, a.svc_cost
    from service a, customer b
    where b.cst_no = 'C107'
        and a.cst_no = b.cst_no
        order by a.svc_cost desc;

• 3번 문제

-- 3. 카센터의 직원수와 직원의 평균급여를 출력하시오.
select count(emp_no) 직원수, round(avg(emp_salary)) 평균급여 from employee;

• 4번 문제

-- 4. 5만원 이상 수리한 자동차 현황을 서비스번호, 자동차번호, 모델, 수리금액, 고객번호, 고객명, 연락처 순으로 조회하시오.
select s.svc_no 서비스번호, c.car_no 자동차번호, c.car_model 모델, s.svc_cost 수리금액, u.cst_no 고객번호, u.cst_name 고객명, u.cst_telno 연락처
    from service s, car c, customer u
    where s.svc_cost >= 50000
        and u.cst_no = c.cst_no
        and c.car_no = s.car_no;

• 5번 문제

-- 5. 2023년도 수리된 자동차현황을 서비스번호, 자동차번호, 수리시간, 수리금액을 금액이 큰 것부터 조회하시오.
select svc_no 서비스번호, car_no 자동차번호, svc_time 수리시간, svc_cost 수리금액
    from service
    where substr(svc_no, 1, 2) = '23'
    order by nvl(svc_cost, 0) desc;

• 6번 문제

-- 6. 금액기준으로 가장 큰 금액수리를 요청한 고객의 고객번호, 금액을 조회하시오.
select asd 고객번호, mpr 금액 
    from 
        (select cst_no asd, max(svc_cost) mpr
            from service
            group by cst_no
            order by nvl(max(svc_cost), 0) desc)
    where rownum = 1;
-- rownum : 가장 첫번째 것을 가져옴.

ERwin Data Modeler

(5장의 테이블 명세서를 기반으로 ER 다이어그램을 작성한다.
사용하는 프로그램은 ERwin Data Modeler 이다.

설정

정보공학 표기법을 사용하기 위해 아래와 같은 방식으로 ERwin을 설정한다.

1. Model 의 Model Properites 을 클릭한다.

2. Notation 을 클릭한 후 Logical Notation과 Physical Notation을 IE로 설정한 후 OK을 클릭한다.

파일 생성

1. File의 New를 클릭한다.

2. Logical/Physical 을 클릭 후 OK를 클릭한다.

3. 생성된 화면을 확인한다.

Entity 생성

1. 상단의 Entity을 클릭한다.

2. 화면에 클릭하여 Entity을 생성한다.
테이블 명, 기본키(PK), 컬럼명 순으로 기입한다.
이때 FK의 경우 기입하지 않는다.

1 : N Relationship

1. 상단의 Identifying relationship 을 클릭한다.

2. 각각의 Entity를 클릭하여 관계를 연결한다.
이때 자재번호가 null일 수 없기에 NULL이 포함되지 않는 관계를 변경한다.

3. 선 위에서 오른쪽 마우스 클릭 후 Relationship Properties 을 클릭한다.

4. One or More (P) 로 변경한 후 OK을 클릭한다.

5. FK의 위치를 변경한다.
이때 FK인 사업장번호가 PK가 아닌 FK이므로 키를 드래그 해 하단으로 내린다.

N : M Relationship

1. 상단의 Many-to-many relationship을 클릭한다.

2. 각각의 Entity에 클릭하여 관계를 연결한다.
이때 N:M 관계는 null이 있을 수 없기에 NULL이 포함되지 않는 관계를 변경하지 않아도 된다.

3. 선 위에서 오른쪽 마우스 클릭 후 Create Association Entity 을 클릭한다.

4. 다음(N) 을 클릭한다.

5. 다음(N) 을 클릭한다.

6. Entity Name 을 입력한 후 다음을 클릭한다.

7. 마침을 클릭한다.

8. 위와 같이 수정한다.
PK 클릭 후 Tab 시 각 속성을 추가, 수정할 수 있다.

논리 모델링 완료

근무와 사원, 근무와 사업장의 경우도 NULL 값이 불가능 하기에 변경하여야 한다.

물리 모델링 작업

1. Logical을 Physical 으로 변경한다.

2. 데이터를 입력한다.

근무와 같은 테이블은 연결을 통해 만들어졌으므로 자동으로 변환되는 요소들이 있다. 그러니 기본이 되는 테이블 먼저 작성해야 한다.

+ 삭제할때는 관련된 테이블 먼저 삭제 후, 기본이 되는 테이블을 삭제해야 한다.

3. Datatype 을 변경한다.

4. Null Option으로 NOT NULL 여부를 변경한 후 모든 컬럼을 변경했다면 OK를 클릭한다.

모든 테이블을 위와 같이 변경한다.

Oracle SQL Developer 와 ERWin 설정과 연결

1. 권한을 부여한다.

https://forest-of-coding.tistory.com/6

위의 객체 및 데이터 조작 권한 부여 참조하여 cmd에서 권한을 부여해야 한다.

2. Database 의 Database Connection을 클릭한다.

3. Database, Authentication, User Name, Password을 입력한다.

4. Value에 127.0.01:1521/ex을 입력한 후 Connect 을 입력한다.

5. Tools에 Forward Engineer > Schema Generation 을 클릭한다.

6. Trigger의 오른쪽 부분을 전부 체크해제한 후 Generate 을 클릭한다.

7. succeeded 가 됐다면 OK를 클릭한다.

8. SQL Developer에서 접속 후 연결된 테이블을 확인한다.