마당대학 데이터베이스의 ER 다이어그램(물리적모델링)

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

madang_university.erm

0.05MB

또한 데이터베이스 테이블을 생성하기 위하여 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;

/

madang_university.sql

0.00MB