[SQL 2일차] 데이터베이스 정규화와 ALTER문

본 포스팅은 데이터베이스 정규화 및 ALTER문에 대해 알아보겠습니다.

---

SQL은 관계형 (테이블을 잘 설계하려면 정보를 나타낼 때 어떤 테이블 사이의 열들이 서로 어떠한 관계에 있는지를 고려해야 합니다.) 데이터베이스입니다.

테이블로 표현하려는 것을 선택하고 그 테이블을 사용하여 얻어야 하는 정보들의 리스트를 작성하며 리스트를 이용하여 테이블을 만들 정보들의 조각으로 나누는 것이 관계형 데이터베이스라 할수 있습니다.

이에 데이터베이스 관계형 정규화 과정을 살펴보고 어떻게 표현되는지 알아보겠습니다.

SQL은 원자적 특성을 갖추고 있습니다. (테이블을 이용하는 사람에 따라 필요한 정보의 최소단위 즉 더 쪼갤 수 없는 쪼갤 필요가 없는 가장 작은 조각을 의미합니다.) 

원자적 데이터로 구성된 열에 같은 타입의 데이터를 여러개 가질 수 없습니다. (INGREDIENTS - TOMATO, EGG, APPLE.. 함유된 내용 중 EGG가 있는 음식을 찾는다고 가정하면 매우 어렵지만 이는 MAIN 함유물 정도로 변경이 필요하다는 의미입니다.) 원자적 데이터로 구성된 테이블은 같은 타입의 데이터를 여러 열에 가질 수 없습니다. (COST1, COST2 OR STUDENT1, STUDENT2 등의 비슷한 열을 나열 할 수 없습니다.)

위와 같이 정규화 처리가 된 테이블은 중복 데이터가 없어서 데이터베이스의 크기를 줄여 주고 찾아야 할 데이터가 적어 쿼리가 더 빨라집니다.

1NF는 각 행의 데이터들은 원자적 값을 가져야 한다는 의미입니다. 각 행은 유일무이한 식별자인 기본키를 가지고 있어야 합니다.

여기서 기본키란 다음과 같은 특징을 갖추고 있습니다.

기본키는 유일한 값을 갖고 있어야 합니다. (주민등록번호와 같은 고유한 값으로 비교됩니다.)

기본키는 NULL이 될 수 없습니다.

기본키는 레코드가 삽입될 때 값이 있어야 합니다.

기본키는 간결해야 합니다.

기본키의 값은 변경 불가합니다.

주민번호나 자동차등록번호등은 유일하나 보안에 문제가 있습니다.

이에 ID와 같은 새로운 열을 만들어 처리하는 SYNTHETIC 방식이 나타났습니다.

CREATE TABLE man_info (

 man_id INT NOT NULL AUTO_INCREMENT,

 name varchar(30) DEFAULT NULL,

 species varchar(30) DEFAULT NULL,

 location varchar(50) DEFAULT NULL,

 weight int(11) DEFAULT NULL,

    PRIMARY KEY (man_id)

);

PRIMARY KEY 지정 및 자동증가 방법으로 AUTO_INCREMENT를 사용하지만 NULL 값을 무시한다는 차이점이 기본키와 다릅니다.

다음으로 ALTER문을 살펴보겠습니다.

ALTER TABLE man_info

ADD column man_id INT NOT NULL AUTO_INCREMENT FIRST,

ADD PRIMARY KEY(man_id);

ALTER TABLE ADD로 삭제가 아닌 업데이트가 가능합니다. FIRST의 경우 열의 몇번째에 위치할 것인지를 지정합니다.

ALTER 명령어로 테이블 수정도 가능합니다.

ALTER TABLE man_info

ADD column man_id INT NOT NULL AUTO_INCREMENT FIRST,

ADD PRIMARY KEY(man_id);

위치 지정은 FIRST, SECOND, THIRD..........., LAST, BEFORE, AFTER를 사용할 수 있습니다.

ALTER TABLE은 RENAME, CHANGE, MODIFY, ADD, DROP 등의 속성을 갖고 있습니다.

TABLE NAME 변경방법

ALTER TABLE projekts

RENAME TO projects_list;

열 이름 변경

ALTER TABLE projects_list

CHANGE COLUMN number proj_id INT(10) NOT NULL AUTO_INCREMENT,

CHANGE COLUMN descriptionofproj desc_proj VARCHAR(50) NOT NULL,

CHANGE COLUMN contractoronjob con_name VARCHAR(10) NOT NULL,

ADD PRIMARY KEY(proj_id);

Primary Key 등록 제거

ALTER TABLE projects_list DROP PRIMARY KEY;

열 타입 변경

ALTER TABLE projects_list

MODIFY COLUMN proj_id INT(5) NOT NULL AUTO_INCREMENT,

MODIFY COLUMN desc_proj VARCHAR(5) NOT NULL;

열 추가

ALTER TABLE projects_list

ADD COLUMN con_phone VARCHAR(11) NOT NULL,

ADD COLUMN start_date VARCHAR(20) NOT NULL,

ADD COLUMN est_cost int(100) NOT NULL;

열 제거

ALTER TABLE projects_list

DROP COLUMN est_cost;

ALTER TABLE 설계 방식으로 원자적이지 않은 열 선택 및 확인 과정이 필요하며, 패턴을 찾아 수정에 용이한 방식을 선택하는 것이 필요합니다.

그밖에 도움되는 문자함수들로는 다음과 같습니다.

패턴이 정해져 있다면, RIGHT() LEFT()를 사용하여 열로부터 정해진 수의 문자들을 선택

SELECT RIGHT(name,2) FROM man_info;

SELECT LEFT(name,2) FROM man_info;

기준이 되는 문자열 앞을 모두 찾음

SELECT SUBSTRING_INDEX(name, 'a', 1) FROM man_info;

문자열의 일부를 반환함 

SELECT SUBSTRING('San Antonio, TX', 5, 3);

대소문자 변환

SELECT UPPER('SELECT * FROM man_info;'); 

SELECT LOWER('SELECT * FROM man_info;');

반대로 출력 

SELECT REVERSE('<wbr />ABCDEFGHIJKLMNOPQRSTUVWXYZ');

공백제거

SELECT LTRIM('   ASEF ASD  QWER    ');

SELECT RTRIM('   ASEF ASD  QWER    ');

문자열 길이 

SELECT LENGTH('NARA`SON! ARE YOU HANDSOME?');

SELECT RIGHT(name,2) FROM man_info;

SELECT LEFT(name,2) FROM man_info;

SELECT SUBSTRING_INDEX(name, 'a', 1) FROM man_info;

SELECT SUBSTRING('San Antonio, TX', 5, 3);

SELECT UPPER('SELECT * FROM man_info;'); 

SELECT LOWER('SELECT * FROM man_info;'); 

SELECT REVERSE('<wbr />ABCDEFGHIJKLMNOPQRSTUVWXYZ');

SELECT LTRIM('   ASEF ASD  QWER    ');

SELECT RTRIM('   ASEF ASD  QWER    ');

SELECT LENGTH('NARA`SON! ARE YOU HANDSOME?');

문자 함수는 테이블에 저장되어 있는 데이터를 바꾸지 않습니다. 쿼리의 결과로 변경 된 문자열을 반환할 뿐입니다.

이러한 문자 함수를 활용하여 다음과 같이 사용이 가능합니다.

UPDATE man_info SET name=RIGHT(name,2);

UPDATE man_info SET first_name=SUBSTRING_INDEX(<wbr />name,' ',1);

UPDATE man_info SET last_name=SUBSTRING(name,5,4);

마지막으로 지금까지 알아본 DML문의 고급구문입니다.

먼저 CASE 문 활용 방법입니다.

UPDATE movie_table

SET category = 

CASE

 WHEN drama='T'

  THEN 'drama'

 WHEN comedy='T'

  THEN 'comedy'

 WHEN action='T'

  THEN 'action'

 WHEN gore='T'

  THEN 'gore'

 WHEN scifi='T'

  THEN 'scifi'

 WHEN for_kids='T'

  THEN 'for_kids'

 WHEN cartoon='T' AND rating='G'

  THEN 'cartoon'

 ELSE 'other'

END WHERE ~~;

다음으로 ORDER BY 문 활용 방법입니다.

• SELECT * FROM movie_table ORDER BY title;

• 순서는 특문 -> 숫자 -> 대문자 -> 소문자

• SELECT * FROM movie_table ORDER BY category,rating,title;

• 정렬을 여러열로 사용할 경우 순서가 중요합니다.

• ASC : Default 오름차순

• DESC : 내림차순

• SELECT * FROM movie_table ORDER BY category DESC,rating DESC,title ASC;

다음으로 SUM & GROUP BY & ORDER BY 문 활용 방법입니다.

• SELECT SUM(sales) FROM movie_table WHERE sales > 2;

• 결과치에 대한 합을 보여줍니다.

• SELECT category, SUM(sales) FROM movie_table GROUP BY category ORDER BY SUM(sales) DESC;

다음으로 AVG & GROUP BY 문 활용 방법입니다.

• SELECT AVG(sales) FROM movie_table GROUP BY category ; 

• 결과치에 대한 평균치를 보여줍니다.

다음으로 MIN & MAX & COUNT•SELECT MIN(sales) FROM movie_table GROUP BY category;문 활용 방법입니다.

• SELECT MAX(sales) FROM movie_table GROUP BY category;

• SELECT COUNT(sales) FROM movie_table GROUP BY category;

다음으로 DISTINCT문 활용 방법입니다.

• SELECT DISTINCT(phone_num) FROM man_info;

• 중복되는 값을 제거 하여 출력합니다.

마지막으로 LIMIT문 활용 방법입니다.

• SELECT DISTINCT(phone_num) FROM man_info ORDER BY phone_num DESC LIMIT 1;

• SELECT DISTINCT(phone_num) FROM man_info ORDER BY phone_num DESC LIMIT 1,1;

• LIMIT은 0부터 COUNT 하며 1,1은 2번째 값에서 하나를 선택하라는 의미입니다.

이번시간에는 데이터베이스 정규화 과정 및 ALTER문 활용 방법에 대해 알아보았습니다.

다음시간에는 설계 방법에 대해 알아보겠습니다.

고맙습니다.