논리 / 물리 데이터 모델링 간단한 예제

ERwin 을 활용하였음 

예제 1 ) 

1. 논리적 ERD

2. 물리적 ERD

 

3. ERD를 기반으로 자동생성한 SQL 

CREATE TABLE STUDENT_T (
       STU_NO               CHAR(9) NOT NULL,
       STU_NAME             VARCHAR2(12) NULL,
       STU_DEPT             VARCHAR2(20) NULL,
       STU_GRADE            NUMBER(1) NULL,
       STU_CLASS            CHAR(1) NULL,
       STU_GENDER           CHAR(1) NULL,
       STU_HEIGHT           NUMBER(5,2) NULL,
       STU_WEIGHT           NUMBER(5,2) NULL,
       constraint pk_student_t PRIMARY KEY (STU_NO)
);


CREATE TABLE SUBJECT_T (
       SUB_NO               CHAR(3) NOT NULL,
       SUB_NAME             VARCHAR2(30) NULL,
       SUB_PROF             VARCHAR2(12) NULL,
       SUB_GRADE            NUMBER(1) NULL,
       SUB_DEPT             VARCHAR2(20) NULL,
       constraint pk_subject_t PRIMARY KEY (SUB_NO)
);


CREATE TABLE ENROL_T (
       SUB_NO               CHAR(3) NOT NULL,
       STU_NO               CHAR(9) NOT NULL,
       ENR_GRADE            NUMBER(3) NULL,
       constraint pk_enrol_t PRIMARY KEY (SUB_NO, STU_NO), 
       constraint fk_stu_no1 FOREIGN KEY (STU_NO)
                             REFERENCES STUDENT_T, 
       constraint fk_sub_no1 FOREIGN KEY (SUB_NO)
                             REFERENCES SUBJECT_T
);

 

4. 실행 결과 

 

5. 데이터 입력 후, 결과 확인 

 

-------------------------------------------------------------------------------------------------------------------------- 

예제 2 ) 

1. 논리적 ERD 

 

2. 물리적 ERD 

 

3. ERD 기반 자동생성 SQL 

CREATE TABLE salgrade_t (
       grade                NUMBER(2) NOT NULL,
       losal                NUMBER(7) NULL,
       hisal                NUMBER(7) NULL,
       constraint pk_salgrade_t PRIMARY KEY (grade)
);


CREATE TABLE dept_t (
       deptno               CHAR(2) NOT NULL,
       dname                VARCHAR2(14) NULL,
       loc                  VARCHAR2(13) NULL,
       constraint pk_dept_t PRIMARY KEY (deptno)
);


CREATE TABLE emp_t (
       empno                CHAR(4) NOT NULL,
       mgr                  CHAR(4) NULL,
       deptno               CHAR(2) NULL,
       ename                VARCHAR2(30) NULL,
       job                  VARCHAR2(20) NULL,
       hiredate             DATE NULL,
       sal                  NUMBER(7) NULL,
       comm                 NUMBER(7) NULL,
       constraint pk_emp_t PRIMARY KEY (empno), 
       constraint fk_mgr1 FOREIGN KEY (mgr)
                             REFERENCES emp_t(empno), 
       constraint fk_deptno1 FOREIGN KEY (deptno)
                             REFERENCES dept_t(deptno)
);

 

4. 실행 결과 

 

5. 데이터 입력 후, 결과 확인