테이블 | 한글 |
---|---|
수강생 | stnt |
강사 | tcher |
강의 | lect |
강의실 | room |
매니저 | mgr |
수강신청 | lect_appl |
강의실사진 | room_phot |
멤버 | memb |
주소 | addr |
강의배정 | lect_tcher |
동일
- 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 '강의배정';
-- 강의배정
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표대로의 환경 세팅 가능
글이 잘 정리되어 있네요. 감사합니다.