sun cloud

목차

ERD(Entity Relationship Diagram)

: 관계형 데이터베이스 설계를 위한 다이어그램

(참고 - 데이터베이스 모델링: ERD, 어플리케이션 모델링: UML)

image

ERD에서는 그림과 같이 데이터를 표현합니다. Entity명 - 기본키 - 일반속성영역

데이터 모델링의 종류로 두 가지 방식이 있습니다.

논리 데이터 모델링(Logical data modeling)

  • 논리적인 데이터 관리 및 관계를 정의한 모델입니다.
  • 전체 업무 범위와 업무 구성요소를 정의하고 확인할 수 있다.
  • 분석 설계 단계(초기 단계)에서 진행합니다.

물리 데이터 모델링(Physical data modeling)

  • 논리 데이터 모델을 DBMS 특성에 맞게 구체화시킨 모델을 말합니다.


데이터를 입력해보겠습니다.

image

왼쪽의 Logical로 작성한 사원 Table을 오른쪽에 직접 데이터를 입력해서 중복되는 데이터를 확인했습니다.

사원번호와 이름, 주소, 전화번호는 모두 고유한 값으로 생성될 것 입니다.(동명이인이나 가족관계로 인한 동일한 주소명은 발생할 가능성 자체가 적기 때문에 예외적인 상황으로 상정합니다)

하지만 부서와 관련된 정보인 부서명, 부서전화번호, 부서지역에 해당하는 정보들은 데이터가 쌓이면 쌓일수록 더 많은 중복된 데이터들을 확인할 수 있을 것 입니다.


분리한다면 다음과 같은 형태가 만들어질 것 입니다.

image

이렇게 중복되는 데이터들을 효과적으로 저장한다면 데이터의 양이 많으면 많을수록 더욱 자원을 아낄 수 있을 것입니다.

만약 부서의 정보를 수정해야 한다면 기존에는 사원 한 명의 데이터마다 수정해야 했겠지만 위처럼 중복되는 데이터들을 관리한다면 부서 테이블의 정보만 수정하면 될 것입니다.

관계를 표현한다면 아래의 그림처럼 하나의 독립된 사원 테이블에서 오른쪽과 같이 사원 테이블과 부서 테이블로 데이터를 나누고 관계를 지어줄 수 있습니다.

image

ERD에서는 이를 부모 테이블과 자식 테이블로 구분합니다.

자바의 상속관계와는 다른 모습을 보여주는데, 위의 예시에서는 부서가 부모 테이블이고 사원은 부서를 참조하기에 자식 테이블이 됩니다.

  • 사원 입장(부서 to 사원): 반드시 하나의 부서에 속한다

  • 부서 입장(사원 to 부서): 사원이 있을 수도 없을 수(신설)도 여러 개가 있을 수도 있다.(Zero, One or More)

위의 내용들을 종합해보자면, 데이터 모델링을 잘 해서 데이터를 효과적으로 저장하면 그만큼 자원을 아낄 수 있고, 테이블들 또한 현실 세계에서과 같이 관계를 반영할 수 있게 됩니다.

이 작업을 데이터베이스 정규화라고 합니다.

정규화(Normalization)

: 데이터베이스 설계 시 중복을 최소화하고 참조 무결성을 보장(이상현상을 막아줌)하기 위해 데이터를 구조화하는 작업을 말합니다.(참조 무결성을 보장한다는 말은 참조를 함에 있어서 예외 상황이 일어나지 않도록 막아주는 것을 말합니다. 아래에서 제약 조건과 관련해서 설명드리도록 하겠습니다.)

정규화를 거치면 아래와 같이 테이블을 분리하여 관계성(일대다)을 가지게 됩니다.

` 사원 테이블 -> O—— - 부서 테이블`

image

위 관계를 말로 풀어보자면 사원은 반드시 하나의 부서에 속하고 부서는 0 or 1 or 다수의 사원을 가질 수 있다라고 할 수 있겠습니다.

이렇게 테이블을 분리하여 관리할 때는 참조 무결성 보장을 위해 제약조건 FOREIGN KEY를 지정해야 합니다.

Foreign Key (FK, 외래키)

: 한 테이블의 필드(attribute) 중 다른 테이블의 행(row)을 식별할 수 있는 키를 말합니다. 다음과 같이 FOREIGN KEY를 제약 조건으로 지정함으로써 두 테이블간의 관계를 나타내주고 무결성을 보장해줍니다.

CONSTAINT 제약조건명 FOREIGN KEY(컬럼명) REFERENCES 참조테이블명(컬럼명)

  • 참조대상이 되는 테이블(부서)을 부모테이블이라고 하고 참조하는 테이블(사원)을 자식테이블이라고 합니다.
  • 만약 부모테이블인 부서테이블에 10번 부서번호가 없다면 아래의 insert 구문은 에러가 발생합니다.
    • parent key not found : foreign key 제약조건 위배(참조 무결성 제약조건 위배)

FOREIGN KEY를 통해 연결된 테이블들의 관계는 다음과 같이 두 종류로 나눌 수 있습니다.

식별관계 (identified relationship)

: 부모 테이블의 기본키 혹은 복합키가 자식 테이블의 기본키 혹은 복합키의 구성원으로 전이되는 관계를 말합니다.(실선으로 연결) ( ex - 사원과 신체정보 )

비식별관계 (non-identified relationship)

: 부모 테이블의 기본키 혹은 복합키가 자식 테이블의 일반속성으로 전이되는 관계를 말합니다.(점선으로 연결) (ex - 부서와 사원정보 )

image

사원 테이블로부터 위와 같이 신체 정보 테이블과 부서 테이블을 분리할 수 있습니다.

이를 물리 데이터 모델링와 논리 데이터 모델링으로 나타내면 아래와 같습니다.

image


sun cloud


JOIN SQL

: 여러 테이블 간의 정보를 결합하기 위한 SQL입니다.

(이번에는 기본적인 join SQL 연습만을 하고 상세한 내용은 이후 웹 수업을 진행하며 공부할 예정입니다.)

다음과 같이 두 가지 방식으로 join을 사용해볼 수 있습니다.

  1. 1
2
3

    SELECT 컬럼명, 컬럼명
FROM 테이블명 별칭, 테이블명 별칭
WHERE 별칭.컬럼명=별칭.컬럼명 -- 조인조건

  2. 1
2
3

    SELECT 컬럼명, 컬럼명
    FROM 테이블명 별칭, 테이블명 별칭
    INNER JOIN 테이블명 별칭 ON 별칭.컬럼명=별칭.컬럼명 -- 조인조건

아래는 사원번호가 1인 사원의 sal과 부서번호deptno, 부서명dname, 지역loc을 조회하는 예제입니다. (Join SQL을 이용해서 사원테이블과 부서테이블을 결합하여 정보를 조회합니다 )

1
2
3
4
5
6
7
8
9

SELECT e.ename, e.sal, d.deptno, d.dname, d.loc
FROM k_employee e, department d
WHERE e.deptno=d.deptno -- DB에서 = : 동등연산자
AND e.empno=1
-- 아래와 같이 표현할 수도 있다
SELECT e.ename, e.sal, d.deptno, d.dname, d.loc
FROM k_employee e
INNER JOIN department d ON e.deptno=d.deptno
WHERE e.empno=1


자바에서 위와 같이 사원 번호로 사원정보를 모두 조회해보는 연습을 해보겠습니다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

public EmployeeVO findEmployeeByEmpno(int empno) throws SQLException {
    EmployeeVO employeeVO=null;
    Connection con=null;
    PreparedStatement pstmt=null;
    ResultSet rs=null;
    try {
        con=DriverManager.getConnection(url, username, password);
        StringBuilder sql=new StringBuilder();
        sql.append("select e.ename,e.sal,d.deptno,d.dname,d.loc,d.tel ");
        sql.append("from k_employee e, department d ");
        sql.append("where e.deptno=d.deptno and e.empno=?");
        pstmt=con.prepareStatement(sql.toString());
        pstmt.setInt(1, empno);
        rs=pstmt.executeQuery();
        if(rs.next()) {
            employeeVO=new EmployeeVO();
            employeeVO.setEmpno(empno);
            employeeVO.setEname(rs.getString(1));
            employeeVO.setSal(rs.getInt(2));
            DepartmentVO departmentVO=new DepartmentVO();
            departmentVO.setDeptno(rs.getInt(3));
            departmentVO.setDname(rs.getString(4));
            departmentVO.setLoc(rs.getString(5));
            departmentVO.setTel(rs.getString(6));
            employeeVO.setDepartmentVO(departmentVO);
            /*
				 employeeVO=new EmployeeVO(
				 empno,rs.getString(1),rs.getInt(2),new DepartmentVO(rs.getInt(3),
				 rs.getString(4),rs.getString(5),rs.getString(6)));
				 */
        }
    }finally {
        closeAll(rs, pstmt, con);
    }
    return employeeVO;
}

image


참고사항

ERWIN: ERD(entity-relationship diagram) Tool

  • 일반적인 첫 시작: File -> New -> Logical/Physical 버튼 선택 -> Logical 데이터 모델링부터 구성
  • ERWIN 환경 설정
    • 메뉴 Model - Model Propertie 선택 Model Properties 대화상자- 세 번째 Notation탭에서 Logical Notation Physical Notation영역 모두 IE옵션 버튼을 선택
  • 이미지 추출
    • 메뉴 Tools - Report Template Builder - Report Builder - Report Templates - Picture 선택후 화살표 클릭 - File - run - brower 뜨면 이미지 다운 가능
  • HTML 안나올때는 경로 변경
    • Report Template Builder -> Edit -> Properties ->Export탭-> Generated File 위치변경
  • Physical 모드에서 컬럼타입보기
    • DataType display -> Format Tab -> Table Display -> Column DataType
  • 기본데이터타입변경
    • Model Tab -> Model Properties -> Defaults -> VARCHAR2(100) 으로 변경

Database 카테고리 내 다른 글 보러가기

댓글남기기

참고