가상 ERD 실습(SQL) (07.20)

holy one·2023년 7월 20일
0

Study

목록 보기
6/11

가상 관계도

테이블한글
수강생stnt
강사tcher
강의lect
강의실room
매니저mgr
수강신청lect_appl
강의실사진room_phot
멤버memb
주소addr
강의배정lect_tcher

가상 ERD

동일

code

table 구성

- select 테스트 용 테이블 예시

-table 삭제
DROP TABLE IF EXISTS <table> RESTRICT;

-- 수강생
CREATE TABLE stnt (
    mno    INTEGER     NOT NULL COMMENT '수강생번호', -- 수강생번호
    work   CHAR(1)     NOT NULL COMMENT '재직여부', -- 재직여부
    acc_no VARCHAR(20) NULL     COMMENT '통장번호', -- 통장번호
    bank   VARCHAR(50) NULL     COMMENT '은행명' -- 은행명
)
COMMENT '수강생';

-- 수강생
ALTER TABLE stnt
    ADD CONSTRAINT PK_stnt -- 수강생 기본키
        PRIMARY KEY (
            mno -- 수강생번호
        );

-- 수강생 유니크 인덱스
CREATE UNIQUE INDEX UIX_stnt
    ON stnt ( -- 수강생
        acc_no ASC, -- 통장번호
        bank ASC    -- 은행명
    );

-- 강사
CREATE TABLE tcher (
    mno    INTEGER     NOT NULL COMMENT '강사번호', -- 강사번호
    acc_no VARCHAR(20) NULL     COMMENT '통장번호', -- 통장번호
    bank   VARCHAR(50) NULL     COMMENT '은행명', -- 은행명
    hr_pay INTEGER     NULL     COMMENT '시강료' -- 시강료
)
COMMENT '강사';

-- 강사
ALTER TABLE tcher
    ADD CONSTRAINT PK_tcher -- 강사 기본키
        PRIMARY KEY (
            mno -- 강사번호
        );

-- 강사 유니크 인덱스
CREATE UNIQUE INDEX UIX_tcher
    ON tcher ( -- 강사
        acc_no ASC, -- 통장번호
        bank ASC    -- 은행명
    );

-- 강의
CREATE TABLE lect (
    lno     INTEGER      NOT NULL COMMENT '강의번호', -- 강의번호
    titl    VARCHAR(255) NOT NULL COMMENT '강의명', -- 강의명
    sdt     DATE         NOT NULL COMMENT '시작일', -- 시작일
    edt     DATE         NOT NULL COMMENT '종료일', -- 종료일
    qnty    INTEGER      NOT NULL COMMENT '최대수용인원', -- 최대수용인원
    pric    INTEGER      NOT NULL COMMENT '강의료', -- 강의료
    nat_sup CHAR(1)      NOT NULL COMMENT '정부지원여부', -- 정부지원여부
    sup_typ VARCHAR(50)  NOT NULL COMMENT '지원타입', -- 지원타입
    dsct    TEXT         NOT NULL COMMENT '설명', -- 설명
    rno     INTEGER      NULL     COMMENT '강의실번호', -- 강의실번호
    mno     INTEGER      NULL     COMMENT '매니저번호' -- 매니저번호
)
COMMENT '강의';

-- 강의
ALTER TABLE lect
    ADD CONSTRAINT PK_lect -- 강의 기본키
        PRIMARY KEY (
            lno -- 강의번호
        );

-- 강의 인덱스
CREATE INDEX IX_lect
    ON lect( -- 강의
        titl ASC -- 강의명
    );

ALTER TABLE lect
    MODIFY COLUMN lno INTEGER NOT NULL AUTO_INCREMENT COMMENT '강의번호';

-- 강의실
CREATE TABLE room (
    rno  INTEGER     NOT NULL COMMENT '강의실번호', -- 강의실번호
    loc  VARCHAR(50) NOT NULL COMMENT '지점명', -- 지점명
    name VARCHAR(50) NOT NULL COMMENT '강의실명', -- 강의실명
    qnty INTEGER     NOT NULL COMMENT '수용인원' -- 수용인원
)
COMMENT '강의실';

-- 강의실
ALTER TABLE room
    ADD CONSTRAINT PK_room -- 강의실 기본키
        PRIMARY KEY (
            rno -- 강의실번호
        );

-- 강의실 유니크 인덱스
CREATE UNIQUE INDEX UIX_room
    ON room ( -- 강의실
        loc ASC,  -- 지점명
        name ASC  -- 강의실명
    );

ALTER TABLE room
    MODIFY COLUMN rno INTEGER NOT NULL AUTO_INCREMENT COMMENT '강의실번호';

-- 매니저
CREATE TABLE mgr (
    mno  INTEGER     NOT NULL COMMENT '매니저번호', -- 매니저번호
    dept VARCHAR(50) NULL     COMMENT '부서', -- 부서
    posi VARCHAR(50) NULL     COMMENT '직위', -- 직위
    fax  VARCHAR(20) NULL     COMMENT '팩스' -- 팩스
)
COMMENT '매니저';

-- 매니저
ALTER TABLE mgr
    ADD CONSTRAINT PK_mgr -- 매니저 기본키
        PRIMARY KEY (
            mno -- 매니저번호
        );

-- 수강신청
CREATE TABLE lect_appl (
    lano INTEGER  NOT NULL COMMENT '수강신청번호', -- 수강신청번호
    lno  INTEGER  NOT NULL COMMENT '강의번호', -- 강의번호
    mno  INTEGER  NOT NULL COMMENT '수강생번호', -- 수강생번호
    rdt  DATETIME NOT NULL COMMENT '신청일', -- 신청일
    stat INTEGER  NULL     COMMENT '진행상태' -- 진행상태
)
COMMENT '수강신청';

-- 수강신청
ALTER TABLE lect_appl
    ADD CONSTRAINT PK_lect_appl -- 수강신청 기본키
        PRIMARY KEY (
            lano -- 수강신청번호
        );

-- 수강신청 유니크 인덱스
CREATE UNIQUE INDEX UIX_lect_appl
    ON lect_appl ( -- 수강신청
        lno ASC, -- 강의번호
        mno ASC  -- 수강생번호
    );

ALTER TABLE lect_appl
    MODIFY COLUMN lano INTEGER NOT NULL AUTO_INCREMENT COMMENT '수강신청번호';

-- 강의실사진
CREATE TABLE room_phot (
    rpno INTEGER      NOT NULL COMMENT '강의실사진번호', -- 강의실사진번호
    rno  INTEGER      NOT NULL COMMENT '강의실번호', -- 강의실번호
    phot VARCHAR(255) NOT NULL COMMENT '사진' -- 사진
)
COMMENT '강의실사진';

-- 강의실사진
ALTER TABLE room_phot
    ADD CONSTRAINT PK_room_phot -- 강의실사진 기본키
        PRIMARY KEY (
            rpno -- 강의실사진번호
        );

ALTER TABLE room_phot
    MODIFY COLUMN rpno INTEGER NOT NULL AUTO_INCREMENT COMMENT '강의실사진번호';

-- 멤버
CREATE TABLE memb (
    mno      INTEGER      NOT NULL COMMENT '멤버번호', -- 멤버번호
    name     VARCHAR(50)  NOT NULL COMMENT '이름', -- 이름
    tel      VARCHAR(20)  NOT NULL COMMENT '전화', -- 전화
    email    VARCHAR(40)  NOT NULL COMMENT '이메일', -- 이메일
    pwd      VARCHAR(100) NOT NULL COMMENT '암호', -- 암호
    phot     VARCHAR(255) NULL     COMMENT '사진', -- 사진
    ano      INTEGER      NULL     COMMENT '주소번호', -- 주소번호
    det_addr VARCHAR(255) NULL     COMMENT '상세주소', -- 상세주소
    finl_edu VARCHAR(50)  NULL     COMMENT '최종학력', -- 최종학력
    maj      VARCHAR(50)  NULL     COMMENT '전공', -- 전공
    sch_nm   VARCHAR(50)  NULL     COMMENT '최종학교' -- 최종학교
)
COMMENT '멤버';

-- 멤버
ALTER TABLE memb
    ADD CONSTRAINT PK_memb -- 멤버 기본키
        PRIMARY KEY (
            mno -- 멤버번호
        );

-- 멤버 유니크 인덱스
CREATE UNIQUE INDEX UIX_memb
    ON memb ( -- 멤버
        email ASC -- 이메일
    );

-- 멤버 인덱스
CREATE INDEX IX_memb
    ON memb( -- 멤버
        name ASC -- 이름
    );

ALTER TABLE memb
    MODIFY COLUMN mno INTEGER NOT NULL AUTO_INCREMENT COMMENT '멤버번호';

-- 주소
CREATE TABLE addr (
    ano      INTEGER      NOT NULL COMMENT '주소번호', -- 주소번호
    pst_no   VARCHAR(5)   NOT NULL COMMENT '우편번호', -- 우편번호
    bas_addr VARCHAR(255) NOT NULL COMMENT '기본주소' -- 기본주소
)
COMMENT '주소';

-- 주소
ALTER TABLE addr
    ADD CONSTRAINT PK_addr -- 주소 기본키
        PRIMARY KEY (
            ano -- 주소번호
        );

-- 주소 인덱스
CREATE INDEX IX_addr
    ON addr( -- 주소
        pst_no ASC -- 우편번호
    );

ALTER TABLE addr
    MODIFY COLUMN ano INTEGER NOT NULL AUTO_INCREMENT COMMENT '주소번호';

-- 강의배정
CREATE TABLE lect_tcher (
    lno INTEGER NOT NULL COMMENT '강의번호', -- 강의번호
    mno INTEGER NOT NULL COMMENT '강사번호' -- 강사번호
)
COMMENT '강의배정';

관계 PK/FK 구성

-- 강의배정
ALTER TABLE lect_tcher
    ADD CONSTRAINT PK_lect_tcher -- 강의배정 기본키
        PRIMARY KEY (
            lno, -- 강의번호
            mno  -- 강사번호
        );

-- 수강생
ALTER TABLE stnt
    ADD CONSTRAINT FK_memb_TO_stnt -- 멤버 -> 수강생
        FOREIGN KEY (
            mno -- 수강생번호
        )
        REFERENCES memb ( -- 멤버
            mno -- 멤버번호
        );

-- 강사
ALTER TABLE tcher
    ADD CONSTRAINT FK_memb_TO_tcher -- 멤버 -> 강사
        FOREIGN KEY (
            mno -- 강사번호
        )
        REFERENCES memb ( -- 멤버
            mno -- 멤버번호
        );

-- 강의
ALTER TABLE lect
    ADD CONSTRAINT FK_mgr_TO_lect -- 매니저 -> 강의
        FOREIGN KEY (
            mno -- 매니저번호
        )
        REFERENCES mgr ( -- 매니저
            mno -- 매니저번호
        );

-- 강의
ALTER TABLE lect
    ADD CONSTRAINT FK_room_TO_lect -- 강의실 -> 강의
        FOREIGN KEY (
            rno -- 강의실번호
        )
        REFERENCES room ( -- 강의실
            rno -- 강의실번호
        );

-- 매니저
ALTER TABLE mgr
    ADD CONSTRAINT FK_memb_TO_mgr -- 멤버 -> 매니저
        FOREIGN KEY (
            mno -- 매니저번호
        )
        REFERENCES memb ( -- 멤버
            mno -- 멤버번호
        );

-- 수강신청
ALTER TABLE lect_appl
    ADD CONSTRAINT FK_stnt_TO_lect_appl -- 수강생 -> 수강신청
        FOREIGN KEY (
            mno -- 수강생번호
        )
        REFERENCES stnt ( -- 수강생
            mno -- 수강생번호
        );

-- 수강신청
ALTER TABLE lect_appl
    ADD CONSTRAINT FK_lect_TO_lect_appl -- 강의 -> 수강신청
        FOREIGN KEY (
            lno -- 강의번호
        )
        REFERENCES lect ( -- 강의
            lno -- 강의번호
        );

-- 강의실사진
ALTER TABLE room_phot
    ADD CONSTRAINT FK_room_TO_room_phot -- 강의실 -> 강의실사진
        FOREIGN KEY (
            rno -- 강의실번호
        )
        REFERENCES room ( -- 강의실
            rno -- 강의실번호
        );

-- 멤버
ALTER TABLE memb
    ADD CONSTRAINT FK_addr_TO_memb -- 주소 -> 멤버
        FOREIGN KEY (
            ano -- 주소번호
        )
        REFERENCES addr ( -- 주소
            ano -- 주소번호
        );

-- 강의배정
ALTER TABLE lect_tcher
    ADD CONSTRAINT FK_tcher_TO_lect_tcher -- 강사 -> 강의배정
        FOREIGN KEY (
            mno -- 강사번호
        )
        REFERENCES tcher ( -- 강사
            mno -- 강사번호
        );

-- 강의배정
ALTER TABLE lect_tcher
    ADD CONSTRAINT FK_lect_TO_lect_tcher -- 강의 -> 강의배정
        FOREIGN KEY (
            lno -- 강의번호
        )
        REFERENCES lect ( -- 강의
            lno -- 강의번호
        );

대입 데이터 코드


/* 강의실 데이터 준비 */
insert into room(rno, loc, name, qnty) values(1, '강남', '501', 30);
insert into room(rno, loc, name, qnty) values(2, '강남', '502', 30);
insert into room(rno, loc, name, qnty) values(3, '강남', '503', 30);
insert into room(rno, loc, name, qnty) values(4, '종로', '301', 30);
insert into room(rno, loc, name, qnty) values(5, '종로', '302', 30);
insert into room(rno, loc, name, qnty) values(6, '종로', '303', 30);
insert into room(rno, loc, name, qnty) values(7, '서초', '301', 30);
insert into room(rno, loc, name, qnty) values(8, '서초', '302', 30);
insert into room(rno, loc, name, qnty) values(9, '서초', '501', 30);
insert into room(rno, loc, name, qnty) values(10, '서초', '601', 30);

/* 학생 데이터 입력 */
insert into memb(mno, name, tel, email, pwd)
values(100, 's100', '111-1111', 's100@', sha2('1111', 224));
insert into memb(mno, name, tel, email, pwd)
values(101, 's101', '111-1111', 's101@', sha2('1111', 224));
insert into memb(mno, name, tel, email, pwd)
values(102, 's102', '111-1111', 's102@', sha2('1111', 224));
insert into memb(mno, name, tel, email, pwd)
values(103, 's103', '111-1111', 's103@', sha2('1111', 224));
insert into memb(mno, name, tel, email, pwd)
values(104, 's104', '111-1111', 's104@', sha2('1111', 224));

insert into stnt(mno, work, acc_no, bank)
values(100, 'N', '1000', '비트은행');
insert into stnt(mno, work, acc_no, bank)
values(101, 'Y', '1001', '비트은행');
insert into stnt(mno, work, acc_no, bank)
values(102, 'N', '1002', '캠프은행');
insert into stnt(mno, work, acc_no, bank)
values(103, 'Y', '1003', '우리은행');
insert into stnt(mno, work, acc_no, bank)
values(104, 'N', '1004', '국민은행');

/* 강사 데이터 입력 */
insert into memb(mno, name, tel, email, pwd)
values(200, 't200', '111-1111', 't200@', sha2('1111', 224));
insert into memb(mno, name, tel, email, pwd)
values(201, 't201', '111-1111', 't201@', sha2('1111', 224));
insert into memb(mno, name, tel, email, pwd)
values(202, 't202', '111-1111', 't202@', sha2('1111', 224));
insert into memb(mno, name, tel, email, pwd)
values(203, 't203', '111-1111', 't203@', sha2('1111', 224));
insert into memb(mno, name, tel, email, pwd)
values(204, 't204', '111-1111', 't204@', sha2('1111', 224));

insert into tcher(mno, hr_pay, acc_no, bank)
values(200, 10000, '2000', '신한은행');
insert into tcher(mno, hr_pay, acc_no, bank)
values(201, 20000, '2001', '농협');
insert into tcher(mno, hr_pay, acc_no, bank)
values(202, 15000, '2002', '기업은행');
insert into tcher(mno, hr_pay, acc_no, bank)
values(203, 25000, '2003', '우리은행');
insert into tcher(mno, hr_pay, acc_no, bank)
values(204, 30000, '2004', '국민은행');

/* 매니저 데이터 입력 */
insert into memb(mno, name, tel, email, pwd)
values(300, 'm300', '111-1111', 'm300@', sha2('1111', 224));
insert into memb(mno, name, tel, email, pwd)
values(301, 'm301', '111-1111', 'm301@', sha2('1111', 224));
insert into memb(mno, name, tel, email, pwd)
values(302, 'm302', '111-1111', 'm302@', sha2('1111', 224));
insert into memb(mno, name, tel, email, pwd)
values(303, 'm303', '111-1111', 'm303@', sha2('1111', 224));
insert into memb(mno, name, tel, email, pwd)
values(304, 'm304', '111-1111', 'm304@', sha2('1111', 224));

insert into mgr(mno, dept, posi)
values(300, '시설관리', '주임');
insert into mgr(mno, dept, posi)
values(301, '시설관리', '대리');
insert into mgr(mno, dept, posi)
values(302, '회계', '과장');
insert into mgr(mno, dept, posi)
values(303, '교육', '주임');
insert into mgr(mno, dept, posi)
values(304, '교육', '과장');

/* 200번 강사는 교육팀 과장이기도 하다.*/
insert into mgr(mno, dept, posi)
values(200, '교육', '과장');

/* 강의 데이터 입력 */
insert into lect(lno, titl, sdt, edt, qnty, pric, nat_sup, sup_typ, dsct)
values(1, '자바프로그래밍', '2018-1-1', '2018-5-30', 30, 100, 'Y', '미취업자', 'ok');
insert into lect(lno, titl, sdt, edt, qnty, pric, nat_sup, sup_typ, dsct)
values(2, 'IoT프로그래밍', '2018-3-1', '2018-7-30', 30, 200, 'Y', '미취업자', 'ok');
insert into lect(lno, titl, sdt, edt, qnty, pric, nat_sup, sup_typ, dsct)
values(3, '윈도우프로그래밍', '2018-5-1', '2018-10-30', 30, 300, 'Y', '노동부', 'ok');


/* 강의에 매니저 배정 */ 
update lect set mno=303 where lno=1;
update lect set mno=304 where lno=3;

/* 강의에 강의실 배정 */
update lect set rno=1 where lno=1;
update lect set rno=4 where lno=2;

/* 수강신청 데이터 입력 */
insert into lect_appl(lano, lno, mno, rdt) values(1, 1, 100, '2017-11-2');
insert into lect_appl(lano, lno, mno, rdt) values(2, 1, 101, '2017-11-3');
insert into lect_appl(lano, lno, mno, rdt) values(3, 1, 102, '2017-11-4');
insert into lect_appl(lano, lno, mno, rdt) values(4, 2, 104, '2017-12-6');
insert into lect_appl(lano, lno, mno, rdt) values(5, 2, 100, '2017-12-7');
insert into lect_appl(lano, lno, mno, rdt) values(6, 3, 101, '2017-10-8');
insert into lect_appl(lano, lno, mno, rdt) values(7, 3, 102, '2017-11-9');
insert into lect_appl(lano, lno, mno, rdt) values(8, 3, 104, '2017-11-11');

순차적으로 내려가면 구성한 ERD표대로의 환경 세팅 가능

profile
☁️ 좋아요!

1개의 댓글

comment-user-thumbnail
2023년 7월 20일

글이 잘 정리되어 있네요. 감사합니다.

답글 달기
Powered by GraphCDN, the GraphQL CDN