DB 생성과 정규화

DB생성과 정규화

 

목차

• DB생성
• 제 1정규화
• 제 2정규화
• 제 3정규화
• SQL 생성 및 로드
• 추가 및 삭제

DB생성

SELECT USER	() AS '현재사용자';

CREATE TABLE student (
	hakbun INT,
	NAME VARCHAR(5),
	mobile CHAR(11),
	address VARCHAR(100));
	
SHOW TABLES;
DESCRIBE student;

1. SELECT USER ()  AS '현재사용자';:
   • 현재 데이터베이스 시스템에서 실행 중인 사용자를 조회하는 SQL 쿼리입니다.
   • AS '현재사용자'는 조회 결과를 '현재사용자'라는 열의 이름으로 표시하도록 하는 것입니다.
2. CREATE TABLE student (
   • 'student'라는 테이블을 생성하는 SQL 쿼리입니다.
   • 테이블의 구조를 정의하는 부분으로, 괄호 안에 각 열의 이름과 데이터 형식을 지정합니다.
3. hakbun INT,
   • 'hakbun'이라는 열을 정수형(INT)으로 선언합니다.
4. NAME VARCHAR(5),
   • 'NAME'이라는 열을 최대 5자까지의 가변 길이 문자열(VARCHAR)로 선언합니다.
5. mobile CHAR(11),:
   • 'mobile'이라는 열을 11자의 고정 길이 문자열(CHAR)로 선언합니다.
6. address VARCHAR(100));:
   • 'address'라는 열을 최대 100자까지의 가변 길이 문자열(VARCHAR)로 선언합니다.
   • 선언이 끝나면 세미콜론(;)으로 문을 마무리합니다.
7. SHOW TABLES;:
   • 현재 데이터베이스에 존재하는 테이블의 목록을 조회하는 SQL 쿼리입니다.
8. DESCRIBE student;:
   • 'student' 테이블의 구조를 조회하는 SQL 쿼리입니다.
   • 이를 통해 각 열의 데이터 형식, 길이 등의 정보를 확인할 수 있습니다.

 

 

1정규화

 

1. 각 컬럼이 하나의 속성만을 가져야한다.
2. 하나의 컬럼은 같은 종류나 타입(type)의 값을 가져야한다.
3. 각 컬럼이 유일한 이름을 가져야한다.
4. 칼럼의 순서가 상관없어야한다.

 

이와 같은 테이블이 있다고 가정해본다

현재 테이블은 속성 하나는 하나의 속성값만을 가져야 한다는 1차 정규형에 위배된다.

학생ID	이름	과목
1	홍길동	수학, 영어, 과학
2	김철수	국어, 수학

​

과목을 보면 홍길동과 김철수가 과목에서 2개 ~ 3개 속성값 여러 개를 가지고 있음을 알 수 있다.

​

이를 쪼개주는 것을 원자값을 갖는다고 하며 이것이 제 1정규화이다.

​

제 1정규화를 거치면 아래와 같다.

학생ID	이름	과목
1	홍길동	수학
1	홍길동	영어
1	홍길동	과학
2	김철수	국어
2	김철수	수학

 

이제 각 학생과 해당 학생이 수강한 각 과목이 별도의 행으로 나뉘어져 있다.

이는 원자값으로 구성된 정규화된 형태이며, 1정규화를 만족한다.

이렇게 함으로써 데이터의 중복을 줄이고 효율적인 데이터 관리가 가능해진다.

 

제 2정규화

 

1. 제 1정규화를 만족해야한다.

2. 모든 컬럼이 부분적 종속(Partial Dependency)이 없어야한다. 모든 칼럼이 완전 함수 종속을 만족해야한다.

 

예시를 통해 제 2정규화를 이해해 보겠습니다.

가정컨데, 다음과 같은 테이블이 있다고 가정해 봅시다.

 

비정규화된 테이블

주문번호	제품	고객ID	고객 이름	고객 주소
1	노트북	101	홍길동	서울시 강남구
2	스마트폰	102	김철수	서울시 종로구
3	프린터	101	홍길동	서울시 강남구

 

이 테이블에서 주문번호가 기본 키이고, 고객 정보가 주문과 관련되어 있습니다.

그러나 '고객 이름'과 '고객 주소'는 고객ID에만 종속되어 있습니다. 이는 부분 함수 종속을 나타냅니다.

제 2정규화를 적용하기 위해, '고객' 정보를 별도의 테이블로 분리할 수 있습니다.

 

제 2정규화 적용 후 테이블

 

주문번호 테이블

주문번호	제품	고객ID
1	노트북	101
2	스마트폰	102
3	프린터	101

 

고객ID 테이블

고객ID	고객 이름	고객 주소
101	홍길동	서울시 강남구
102	김철수	서울시 종로구

 

이제 '고객' 테이블은 주문과 직접 관련된 정보만을 포함하고 있습니다. 이것으로써 부분 함수 종속이 제거되었고, 제 2정규화를 만족하게 되었습니다.

 

제 3정규화

 

추후에 수정하겠습니다.

 

연습

cd  /tmp/sql

 

SQL 생성

/* st.sql - student  */
create table st (
        hakbun char(7) not null,
        name varchar(10) not null,
        tel varchar(14),
        address varchar(60),
        primary key(hakbun)
)engine=innodb;


/* kwamok.sql */
create table kwamok (
        kwamok_code char(3) not null,
        kwamok_name varchar(20) not null,
        primary key(kwamok_code)
)engine=innodb;

/* sungjuk.sql */
create table sungjuk(
        hakbun char(7) not null,
        kwamok_code char(3) not null,
        jumsu int not null default 0,
);

 

생성한 SQL 연결

 

mysql -u root -p

MariaDB [test]> source /tmp/sql/kwamok.sql

MariaDB [test]> source /tmp/sql/st.sql

MariaDB [test]> source /tmp/sql/sungjuk.sql

MariaDB [test]> load data local infile '/tmp/sql/sungjuk.data' into table sungjuk;
Query OK, 16 rows affected (0.001 sec)               
Records: 16  Deleted: 0  Skipped: 0  Warnings: 0

MariaDB [test]> load data local infile '/tmp/sql/kwamok.data' into table kwamok; 
Query OK, 10 rows affected (0.001 sec)               
Records: 10  Deleted: 0  Skipped: 0  Warnings: 0

MariaDB [test]> load data local infile '/tmp/sql/student.data' into table st;
Query OK, 12 rows affected (0.001 sec)               
Records: 12  Deleted: 0  Skipped: 0  Warnings: 0

 

생성한 테이블에 데이터 추가 및 삭제

== 추가
MariaDB [test]> insert into st (name,hakbun) values('이선희','2023013');
Query OK, 1 row affected (0.001 sec)

== 변경
MariaDB [sample]> select * from kwamok;
+-------------+-------------+
| kwamok_code | kwamok_name |
+-------------+-------------+
| A01         | korean      |
| A02         | english     |
| B03         | math        |
| B04         | math2       |
| P01         | java        |
| P02         | DBMS        |
| P03         | linux       |
| P04         | python      |
| P05         | php         |
| P06         | gcc         |
+-------------+-------------+
10 rows in set (0.000 sec)

MariaDB [sample]> update kwamok set kwamok_code='C03' where kwamok_code='B03';
Query OK, 1 row affected (0.003 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [sample]> select * from kwamok;
+-------------+-------------+
| kwamok_code | kwamok_name |
+-------------+-------------+
| A01         | korean      |
| A02         | english     |
| B04         | math2       |
| C03         | math        |
| P01         | java        |
| P02         | DBMS        |
| P03         | linux       |
| P04         | python      |
| P05         | php         |
| P06         | gcc         |
+-------------+-------------+
10 rows in set (0.000 sec)