달란트(Talanton)

한빛아카데미 출판사, 박우창, 남송휘, 이현룡 지음, "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;

/

한빛 아카데미에서 나온 박우창, 남송휘, 이현룡 지음, "MySQL로 배우는 데이터베이스 개론과 실습" 에 나오는 마당서점의 물리적 모델링으로 이큽립스에서 제공되는 ER Master 도구를 이용하여 작업을 해보았다.

이를 물리적 SQL 파일로 만들기 위해서는 이클립스에서 오른쪽 마우스를 클릭하여 아래 그림과 같이 "내보내기->DDL" 메뉴를 선택하면 데이터베이스 연동을 위한 sql 파일을 생성할 수 있다.

이를 이용하여 데이터베이스 테이블을 생성할 수 있다.

/* Drop Triggers */
DROP TRIGGER TRI_author_id;
DROP TRIGGER TRI_comment_id;
DROP TRIGGER TRI_tauthor_id;
DROP TRIGGER TRI_tcomment_id;
DROP TRIGGER TRI_topic_id;

/* Drop Tables */
DROP TABLE dormant CASCADE CONSTRAINTS;
DROP TABLE tcomment CASCADE CONSTRAINTS;
DROP TABLE twrite CASCADE CONSTRAINTS;
DROP TABLE tauthor CASCADE CONSTRAINTS;
DROP TABLE topic CASCADE CONSTRAINTS;

/* Drop Sequences */
DROP SEQUENCE SEQ_author_id;
DROP SEQUENCE SEQ_comment_id;
DROP SEQUENCE SEQ_tauthor_id;
DROP SEQUENCE SEQ_tcomment_id;
DROP SEQUENCE SEQ_topic_id;

/* Create Sequences */
CREATE SEQUENCE SEQ_tauthor_id INCREMENT BY 1 START WITH 1;
CREATE SEQUENCE SEQ_tcomment_id INCREMENT BY 1 START WITH 1;
CREATE SEQUENCE SEQ_topic_id INCREMENT BY 1 START WITH 1;

/* Create Tables */
CREATE TABLE dormant
(
	author_id number NOT NULL,
	created date NOT NULL,
	PRIMARY KEY (author_id)
);

CREATE TABLE tauthor
(
	id number NOT NULL,
	name varchar2(15) NOT NULL,
	introduction varchar2(100) NOT NULL,
	created date UNIQUE,
	PRIMARY KEY (id)
);

CREATE TABLE tcomment
(
	id number NOT NULL,
	description varchar2(4000) NOT NULL,
	created date NOT NULL,
	topic_id number NOT NULL,
	author_id number NOT NULL,
	PRIMARY KEY (id)
);

CREATE TABLE topic
(
	id number NOT NULL,
	title varchar2(30) NOT NULL,
	description varchar2(4000) NOT NULL,
	created date NOT NULL,
	PRIMARY KEY (id)
);

CREATE TABLE twrite
(
	author_id number NOT NULL,
	topic_id number NOT NULL,
	created date NOT NULL,
	PRIMARY KEY (author_id, topic_id)
);

/* Create Foreign Keys */
ALTER TABLE dormant
	ADD FOREIGN KEY (author_id)
	REFERENCES tauthor (id)
;

ALTER TABLE tcomment
	ADD FOREIGN KEY (author_id)
	REFERENCES tauthor (id)
;

ALTER TABLE twrite
	ADD FOREIGN KEY (author_id)
	REFERENCES tauthor (id)
;

ALTER TABLE tcomment
	ADD FOREIGN KEY (topic_id)
	REFERENCES topic (id)
;

ALTER TABLE twrite
	ADD FOREIGN KEY (topic_id)
	REFERENCES topic (id)
;

/* Create Triggers */
CREATE OR REPLACE TRIGGER TRI_author_id BEFORE INSERT ON author
FOR EACH ROW
BEGIN
	SELECT SEQ_author_id.nextval
	INTO :new.id
	FROM dual;
END;

/

CREATE OR REPLACE TRIGGER TRI_comment_id BEFORE INSERT ON comment
FOR EACH ROW
BEGIN
	SELECT SEQ_comment_id.nextval
	INTO :new.id
	FROM dual;
END;

/

CREATE OR REPLACE TRIGGER TRI_tauthor_id BEFORE INSERT ON tauthor
FOR EACH ROW
BEGIN
	SELECT SEQ_tauthor_id.nextval
	INTO :new.id
	FROM dual;
END;

/

CREATE OR REPLACE TRIGGER TRI_tcomment_id BEFORE INSERT ON tcomment
FOR EACH ROW
BEGIN
	SELECT SEQ_tcomment_id.nextval
	INTO :new.id
	FROM dual;
END;

/

CREATE OR REPLACE TRIGGER TRI_topic_id BEFORE INSERT ON topic
FOR EACH ROW
BEGIN
	SELECT SEQ_topic_id.nextval
	INTO :new.id
	FROM dual;
END;

/

나는 Erwin 프로그램의 사용에 제약이 있고, 이클립스에 익숙하여 쉽게 데이터베이스 테이블을 생성할 수 있기 때문에 이 방법을 사용하였다. 또한 Data Source Exploror를 같이 이용할 수 있는 장점이 있다.

이클립스에서 ER Master를 설치하는 방법은 Help->Install New Software에서 다음과 같이 입력하여 설치를 한다.

홍형경씨가 지은 "오라클 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;