달란트(Talanton)

데이터베이스 SQL문에 대한 테스트를 해볼 수 있는 사이트가 있어서 소개해 본다.

https://school.programmers.co.kr/learn/challenges

Oracle과 MySQL에 대하여 여러가지 레벨로 문제를 풀어볼 수 있다.

한빛아카데미 출판사, 박우창, 남송휘, 이현룡 지음, "MySQL로 배우는 데이터베이스 개론과 실습"에 나오는 모델링 연습(마당대학 데이터베이스)에 대한 물리적 모델링 결과는 다음과 같이 이클립스  ER Master를 이용하여 작성하여 보았다.

또한 데이터베이스 테이블을 생성하기 위하여 DDL로 변환을 한 결과는 다음과 같다.

/* Drop Triggers */
DROP TRIGGER TRI_Dept_dno;
DROP TRIGGER TRI_Graduate_ssn;
DROP TRIGGER TRI_Professor_ssn;
DROP TRIGGER TRI_Project_pid;

/* Drop Tables */
DROP TABLE work_prog CASCADE CONSTRAINTS;
DROP TABLE Graduate CASCADE CONSTRAINTS;
DROP TABLE work_dept CASCADE CONSTRAINTS;
DROP TABLE Dept CASCADE CONSTRAINTS;
DROP TABLE work_in CASCADE CONSTRAINTS;
DROP TABLE Project CASCADE CONSTRAINTS;
DROP TABLE Professor CASCADE CONSTRAINTS;

/* Drop Sequences */
DROP SEQUENCE SEQ_Dept_dno;
DROP SEQUENCE SEQ_Graduate_ssn;
DROP SEQUENCE SEQ_Professor_ssn;
DROP SEQUENCE SEQ_Project_pid;

/* Create Sequences */
CREATE SEQUENCE SEQ_Dept_dno INCREMENT BY 1 START WITH 1;
CREATE SEQUENCE SEQ_Graduate_ssn INCREMENT BY 1 START WITH 1;
CREATE SEQUENCE SEQ_Professor_ssn INCREMENT BY 1 START WITH 1;
CREATE SEQUENCE SEQ_Project_pid INCREMENT BY 1 START WITH 1;

/* Create Tables */
CREATE TABLE Dept
(
	dno number NOT NULL,
	dname varchar2(30) NOT NULL,
	office varchar2(50) NOT NULL,
	ssn number NOT NULL,
	PRIMARY KEY (dno)
);

CREATE TABLE Graduate
(
	ssn number NOT NULL,
	name varchar2(15) NOT NULL,
	age number NOT NULL,
	deg_prog varchar2(10) NOT NULL,
	dno number NOT NULL,
	graduatessn number NOT NULL,
	PRIMARY KEY (ssn)
);

CREATE TABLE Professor
(
	ssn number NOT NULL,
	name varchar2(15) NOT NULL,
	rank varchar2(15) NOT NULL,
	speciality varchar2(30) NOT NULL,
	age number(3,0) NOT NULL,
	PRIMARY KEY (ssn)
);

CREATE TABLE Project
(
	pid number NOT NULL,
	sponsor varchar2(30) NOT NULL,
	start_date date NOT NULL,
	end_date date NOT NULL,
	budget number NOT NULL,
	ssn number NOT NULL,
	PRIMARY KEY (pid)
);

CREATE TABLE work_dept
(
	ssn number NOT NULL,
	dno number NOT NULL,
	pct_time number(3,0) NOT NULL
);

CREATE TABLE work_in
(
	ssn number NOT NULL,
	pid number NOT NULL
);

CREATE TABLE work_prog
(
	pid number NOT NULL,
	ssn number NOT NULL
);

/* Create Foreign Keys */
ALTER TABLE Graduate
	ADD FOREIGN KEY (dno)
	REFERENCES Dept (dno)
;

ALTER TABLE work_dept
	ADD FOREIGN KEY (dno)
	REFERENCES Dept (dno)
;

ALTER TABLE Graduate
	ADD FOREIGN KEY (graduatessn)
	REFERENCES Graduate (ssn)
;

ALTER TABLE work_prog
	ADD FOREIGN KEY (ssn)
	REFERENCES Graduate (ssn)
;

ALTER TABLE Dept
	ADD FOREIGN KEY (ssn)
	REFERENCES Professor (ssn)
;

ALTER TABLE Project
	ADD FOREIGN KEY (ssn)
	REFERENCES Professor (ssn)
;

ALTER TABLE work_dept
	ADD FOREIGN KEY (ssn)
	REFERENCES Professor (ssn)
;

ALTER TABLE work_in
	ADD FOREIGN KEY (ssn)
	REFERENCES Professor (ssn)
;

ALTER TABLE work_in
	ADD FOREIGN KEY (pid)
	REFERENCES Project (pid)
;

ALTER TABLE work_prog
	ADD FOREIGN KEY (pid)
	REFERENCES Project (pid)
;

/* Create Triggers */
CREATE OR REPLACE TRIGGER TRI_Dept_dno BEFORE INSERT ON Dept
FOR EACH ROW
BEGIN
	SELECT SEQ_Dept_dno.nextval
	INTO :new.dno
	FROM dual;
END;

/

CREATE OR REPLACE TRIGGER TRI_Graduate_ssn BEFORE INSERT ON Graduate
FOR EACH ROW
BEGIN
	SELECT SEQ_Graduate_ssn.nextval
	INTO :new.ssn
	FROM dual;
END;

/

CREATE OR REPLACE TRIGGER TRI_Professor_ssn BEFORE INSERT ON Professor
FOR EACH ROW
BEGIN
	SELECT SEQ_Professor_ssn.nextval
	INTO :new.ssn
	FROM dual;
END;

/

CREATE OR REPLACE TRIGGER TRI_Project_pid BEFORE INSERT ON Project
FOR EACH ROW
BEGIN
	SELECT SEQ_Project_pid.nextval
	INTO :new.pid
	FROM dual;
END;

/

홍형경씨가 지은 "오라클 SQL과 PL/SQL을 다루는 기술"의 샘플 스키마를 설치하는데 책에 있는 것과 다르게 사용자의 권한을 부여하려다 보니 고민이 생겼다.

책에서는 사용자로 ora_user 계정을 만들어서 DBA 권한을 부여하고 있다.

그러나 나는 프로그래머가 DBA 권한을 가지는 것이 바람직하지 않아 다음과 같이 최소한의 권한만 부여하고자 한다.

우선은 책에 나와 있는대로 데이터베이스 tablespace를 생성하고, 사용자를 추가한다. 또한 권한도 DBA로 부여한다. 그리고나서 샘플 스키마를 초기화한다.

1. 초기화 파일을 C:\Temp 폴더에 복사

  - empall.dmp

  - empcust.dmp

2. cmd창을 띄운다.

3. C:\Temp 디렉토리로 이동

4. 초기화 실행

4.1 empall.dmp 초기화

  - C:\Temp> imp ora_user/ora_pass file=expall.dmp log=empall.log ignore=y grants=y rows=y indexes=y full=y

  - imp 수행하면서 생성된 log 파일을 첨부해 본다.

4.2 empcust.dmp 초기화

  - C:\Temp> imp ora_user/ora_pass file=expcust.dmp log=empcust.log ignore=y grants=y rows=y indexes=y full=y

  - imp 수행 시 발생한 로그 파일을 첨부해 본다.

5. ora_user의 DBA 권한 회수

  - sql developer에서 dba 계정으로 수행

  - revoke dba from ora_user;

  - commit;

6. 일반 사용자 권한 부여

  - sql developer에서 dba 계정으로 수행

  - grant connect, resource to ora_user;
  - alter user ora_user quota unlimited on myts;

  - commit;