21장. 데이터베이스 설계지침 테이블과 열에 대한 지침 중복 데이터의 포함 열에 대한 자료형의 선택

Presentation on theme: "21장. 데이터베이스 설계지침 테이블과 열에 대한 지침 중복 데이터의 포함 열에 대한 자료형의 선택"— Presentation transcript:

1 21장. 데이터베이스 설계지침 테이블과 열에 대한 지침 중복 데이터의 포함 열에 대한 자료형의 선택
언제 NOT NULL을 사용해야 하는가? 쉽게 배우는 MySQL 5.x

2 21.1 테이블과 열에 대한 지침 데이터베이스 설계 과정에 필요한 요소 • 사용할 수 있는 기억 공간
• 갱신을 위해 최대 수용할 수 있는 시간 • SELECT 명령문을 위해 최대 수용할 수 있는 시간 • 보안성 설계는 상황에 가장 관련 있는 요소를 결정 • 가능하다면 기억 공간을 절약할 수 있는가? • SELECT 명령문이 거의 3초의 처리 시간을 가질 수 있는가? • 반대로 몇 개의 서로 다른 요소를 고려하는 요구가 있는가? 21.1 테이블과 열에 대한 지침 데이터베이스를 설계에 대한 8개의 기본 지침이 3개의 기본 요소에 미치는 영향 지침 1 : 각 테이블에 대한 기본 키를 정의하라. -만약 여러 개의 후보 키를 가지고 있다면 기본 키를 선택해야 한다. -기본 키의 선택은 항상 열의 번호가 가장 적은 것으로 구성된 후보 키이다. -특히 SELECT 명령문을 사용하여 테이블의 조인 과정을 간단하게 한다. -기억 공간의 양을 가장 적게 사용하는 것이 좋다. 쉽게 배우는 MySQL 5.x

3 지침 2 : 테이블에서 각 결정 요소는 그 테이블의 후보 키이어야 한다.
-결정요소(determinant) : 만약 A라는 열에 있는 각각의 서로 다른 값에 대하여 B라는 열에 관련된 값과 적어도 하나가 다르다면 A라는 열은 B라는 열의 결정요소라 한다. - B는 A에 함수적으로 의존한다는 것이다. -STUDENT 테이블에 있는 STU_NO 열은 모든 다른 열에 대하여 결정요소이다. -두 번째 지침을 따르지 않은 테이블의 단점은 어떤 사실이 여러 번 기록된다. 지침 3 : 열을 조합하지 않아야 한다. -우편번호(POST), 주소(ADDRESS) 열은 ADDRESS라는 하나의 열로 합쳐질 수 있다. 이는 어떤 SELECT 명령문의 사용을 좀 더 쉽게 한다. [예제 21-3] 학번과 이름, 우편번호, 주소로 구성된 뷰 테이블(“ADDRESS”)을 생성하라. mysql> create view address(bunho, irum, post, address) as     -> select s.stu_no, stu_name, s.post_no,     -> concat(rtrim(post_address), rtrim(address))     -> from student s, post p     -> where s.post_no = p.post_no; Query OK, 0 rows affected (0.08sec) 쉽게 배우는 MySQL 5.x

4 여러 개의 열을 하나의 열로 조합하는데 있어서 아주 어려운 문제가 발생된다.
[예제 21-4] 학번 번의 번호와 주소를 출력하라. mysql> select bunho, irum, address     -> from address     -> where bunho = ' '; | bunho    | irum   | address                                       | | | 정인정 | 전라남도 순천시 해룡면부영7차APT 304동 1210호| 1 row in set (0.00 sec) 여러 개의 열을 하나의 열로 조합하는데 있어서 아주 어려운 문제가 발생된다. -학생이 거주하는 도시를 검색하기 위해서는 스칼라 함수를 조합한 아주 복잡한 수식을 사용해야 한다. -만약 도시의 이름 앞에 콤마와 공백이 있다면 다음과 같은 스칼라 함수를 사용해야 한다. RTRIM(ADDRESS) -주소의 이름에 기초를 두고 행을 선택했을 때 위의 수식이 사용되어야 한다. 그리고 이러한 수식을 처리하는데는 긴 시간이 소요될 것이다. -주소의 이름에 기초를 두고 행을 선택하는 것은 LIKE 연산자를 사용해야 한다. 쉽게 배우는 MySQL 5.x

5 21.2 중복 데이터의 포함 -만약 어떤 속성(열)이 한 테이블에만 오직 한번만 기록되어 있다면 많은 조인이 수행되어야 한다.
21.2 중복 데이터의 포함 -만약 어떤 속성(열)이 한 테이블에만 오직 한번만 기록되어 있다면 많은 조인이 수행되어야 한다. -조인 처리와 다른 SELECT 명령문은 많은 시간이 소요된다. -문제를 해결하는 한 방법은 테이블에 중복된 데이터를 포함시키는 것이다. 지침 4 : SELECT 명령문의 실행 시간이 만족스럽지 않을 때에는 중복 데이터를 추가하라. [예제 21-5] 동아리에 가입을 한 학생의 이름과 동아리명을 출력하라.(단, student 테이블에서 이름을  circle 테이블에서는 동아리명을 출력하라.) mysql> select s.stu_name, c.cir_name     -> from student s, circle c     -> where c.stu_no = s.stu_no; | stu_name | cir_name     | | 박정인   | 컴맹탈출     | | 유하나   | 컴맹탈출     | | 김문영   | 컴맹탈출     | | 장수인   | Java길라잡이 | | 정인정   | Java길라잡이 | | 박도준   | Java길라잡이 | | 이상길   | PHP길라잡이  | 7 rows in set (0.03 sec) SQL은 이 명령문을 처리하기 위해서 조인을 수행해야 한다. 조인은 CIRCLE 테이블에 중복되는 데이터로 STU_NAME 열을 저장함으로써 피할 수 있다. 쉽게 배우는 MySQL 5.x

6 [예제 21-6] 동아리에 가입한 학생의 이름과 동아리명을 출력하라.
mysql> select stu_name, cir_name     -> from circle; | stu_name | cir_name     | | 박정인   | 컴맹탈출     | | 유하나   | 컴맹탈출     | | 김문영   | 컴맹탈출     | | 장수인   | Java길라잡이 | | 정인정   | Java길라잡이 | | 박도준   | Java길라잡이 | | 이상길   | PHP길라잡이  | 7 rows in set (0.03 sec) 예제 21-5의 SELECT 명령문 보다 확실히 빠르게 실행된다. -중복된 데이터를 추가하는 것을 비정규화(denormalization)라 한다. -비정규화의 단점은 어떤 요소를 한 번 이상 저장시키는 것이 필요하다. 예를 들면, 학생의 이름이 STUDENT과 CIRCLE 테이블에 기록되어 있으므로, 학생의 이름을 갱신하는 것은 두개의 서로 다른 명령문이 필요하다. -두 개 이상의 위치에 동일한 요소를 기록하므로 기억 공간을 두 배 이상 사용 -갱신의 실행 시간이 더 느리고 필요한 기억 공간에 더 요구 -장점은 SELECT 명령문의 실행 시간은 상당히 빠르다 쉽게 배우는 MySQL 5.x

7 [예제 21-6] 각 학생에 대하여 학생이 받은 장학금의 총액을 출력하라.
mysql> select s.stu_no, sum(jang_total)     -> from student s, fee f     -> where s.stu_no = f.stu_no     -> group by s.stu_no     -> union     -> select stu_no, 0     -> from student     -> where stu_no not in     -> (select stu_no     -> from fee)     -> order by 1; 만약 장학금의 총액이 STUDENT 테이블에 기록되어 있다면 명령문이 간소화된다. 명령문은 다음과 같다. SELECT STU_NO, JANG_TOTAL FROM  STUDENT | stu_no   | sum(jang_total) | | |               0 | | |        | | |               0 | | |               0 | | |               0 | | |               0 | | |         | | |               0 | | |               0 | | |          | | |          | | |          | | |         | | |          | | |         | 15 rows in set (0.17 sec) 쉽게 배우는 MySQL 5.x

8 21.3 열에 대한 자료형의 선택 지침 5 : 다른 열과 비교될 열의 자료형은 동일한 자료형을 사용해야 한다.
21.3 열에 대한 자료형의 선택 지침 5 : 다른 열과 비교될 열의 자료형은 동일한 자료형을 사용해야 한다. [예제 21-8] 성적 테이블의 신청학점(REQ_POINT)과 취득학점(TAKE_POINT)이 같은 경우의 학번, 년도, 학기, 신청학점, 취득학점을 출력하라. mysql> select stu_no, sco_year, sco_term, req_point, take_point     -> from score     -> where req_point = take_point; | stu_no   | sco_year | sco_term | req_point | take_point | | | 2006     |        1 |        18 |         18 | | | 2006     |        2 |        18 |         18 | | | 2007     |        1 |        18 |         18 | | | 2007     |        1 |        18 |         18 | | | 2007     |        1 |        18 |         18 | 5 rows in set (0.00 sec) 쉽게 배우는 MySQL 5.x

9 서로 다른 테이블에 있는 두 개의 열을 서로 비교한다.
mysql> SELECT STU_NAME     ->  FROM STUDENT, FEE     ->  WHERE STUDENT.STU_NO = FEE.STU_NO; 만약 자료형과 정의된 길이가 같다면 두 개의 자료형은 동일하다. 서로 다른 자료형을 가지고 있는 열을 비교하는 명령문은 아주 느리게 처리한다. 지침 6 : 계산에 사용되는 열은 수치 자료형으로 지정하라. -열에 있는 값을 계산해야 한다면 열은 수치 자료형으로 정의 -수치로 구성된 특별한 코드를 기록하기 위해서는 수치 자료형을 열에 부여 -수치 열의 장점은 작은 크기의 기억 공간을 요구 -코드 체계는 아주 빈번하게 변하게 된다. -수치를 영수치 값으로 변환하는 것은 간단하지 않다. 지침 7 : 모든 영수치 열에 대하여 VARCHAR 자료형을 사용하지 않아야 한다. -VARCHAR 자료형은 기억 공간을 절약하기 위해서 설계되었다. -VARCHAR의 단점은 VARCHAR 열에 있는 각 값에 대하여 SQL은 내부적으로 값의 길이를 기록하고 있다. 이는 불필요한 기억 공간이 필요하게 된다. -SELECT와 UPDATE 명령문에서 CHAR 열보다 아주 느리게 처리 -CHAR로 정의했을 때 사용하지 않는 공간이 적어도 15개 이상이 될 때는 VARCHAR로 사용하는 것이 좋다. 쉽게 배우는 MySQL 5.x

10 21.4  언제 NOT NULL을 사용해야 하는가? CREATE TABLE 명령문에서 열 다음에 NOT NULL을 지정할 때는 언제인가? 지침 8 : 모든 행에 대하여 값을 꼭 가지고 있어야 할 때 NOT NULL을 사용 -열에 인위적인 방법으로 NULL 값을 사용하지 않아야 한다. -다른 값을 표현하기 위해서 NULL 값을 사용하지 않아야 한다. -통계 함수를 사용하는 계산에서 NULL 값이 사용되면 아주 이상해진다. -DB2와 SQL/DS와 같은 제품에서는 NOT NULL로 정의되지 않는 열에 있는 각 값에 대하여 SQL은 보이지 않는 특별한 기호를 저장해야 한다. -NOT NULL 열은 NOT NULL이 아닌 동일한 자료형보다는 기억 공간이 절약된다. 쉽게 배우는 MySQL 5.x