DBMS와 SQL로 데이터베이스 만드는 법을 간단하게 알아보자

DBMS

DBMS(Data Base Management System)는 데이터베이스를 관리하기 편리하도록 만들어진 프로그램이다. RDB(Relational Database) 형 DBMS로는 MySQL, Oracle, MSSQL, PostgreSQL 등이 있으며, 본 게시글에서는 MySQL 기반의 MariaDB라는 DBMS를 사용한다.

MariaDB 및 SQL 사용법

Accessing MariaDB Server

mysql –u<username> -p<password>

위 명령어로 MariaDB 서버 유저로 로그인할 수 있다.

Managing databases

데이터베이스 조회

MariaDB [(none)]> SHOW DATABASES;

위 SQL 구문으로 현재 DBMS에 있는 데이터베이스 목록을 출력할 수 있다. [] 안에는 현재 선택된 데이터베이스가 표시된다. SQL 구문은 기본적으로 대문자 입력이 원칙이지만, 소문자로 입력해도 정상적으로 인식된다.

데이터베이스 생성

MariaDB [(none)]> CREATE DATABASE kweb_db;
MariaDB [(none)]> USE kweb_db;
MariaDB [kweb_db]>

Managing users

유저 생성

CREATE USER '<username>'@'<host>' IDENTIFIED BY '<password>'

위와 같이 특정 호스트에 속하는 유저를 생성할 수 있으며, 생성된 계정은 해당 호스트에서만 접속 가능하다. 모든 호스트에서 접속 가능한 유저를 생성할 때는 host 값을 % 로 입력하면 된다.

username 이 같더라도 속한 host가 다를 경우 서로 다른 유저로 취급된다.

권한 부여

GRANT ALL PRIVILEGES ON <db-name>.* TO '<username>'@'<host>'

사용자에게 특정 데이터베이스의 모든 테이블에 대해 모든 권한을 부여하는 SQL문이다. 모든 권한이 아닌 특정 권한만 부여할 수도 있다.

Data Types

각 테이블의 모든 column은 자료형(data type)을 가지며, 데이터 유형에 따라 적절한 자료형을 사용해야 한다.

위 표는 MariaDB에서 제공되는 자료형 중 일부를 나타낸 것이다. TINYINT 는 주로 boolean 값을 나타내기 위해 사용되며, VARCHAR 형은 80자가 기본이지만 VARCHAR(n) 과 같이 문자열의 길이를 항상 명시해주는 것이 권장된다.

이진 데이터 자료형은 거의 사용되지 않는다. 이진 데이터를 DB에 넣는 것보다는 path를 저장하고, 필요할 때 파일을 읽어오는 것이 훨씬 효율적이기 때문이다.

Managing Tables

테이블 생성

CREATE TABLE `courses` (
`name` VARCHAR(20) NOT NULL,
`department` VARCHAR(16) NOT NULL,
`code` VARCHAR(8) NOT NULL,
`is_major` TINYINT(1) NOT NULL,
`is_required` TINYINT(1) NOT NULL,
`credit` INT NOT NULL,
`period` INT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

대학의 개설 강의를 나타내는 테이블을 생성하는 SQL문이다.

courses 라는 이름의 테이블을 생성하고, 각 column의 이름과 자료형을 지정한다.

NOT NULL 은 해당 column에 NULL 값을 허용하지 않음을 의미한다.

ENGINE 은 테이블을 저장하는 방식을 지정하며, DEFAULT CHARSET 은 테이블의 기본 문자셋을 지정한다.

테이블 정보 확인

DESC <table-name>

위와 같이 DESC 키워드로 테이블의 설계 정보를 확인할 수 있다.

테이블 삭제

TRUNCATE <table-name>
DROP TABLE <table-name>

TRUNCATE 키워드로 테이블의 구조를 유지한 채 데이터만 삭제하거나, DROP TABLE 키워드로 테이블 자체를 완전히 삭제할 수 있다.

.sql 파일 로드

SOURCE <file-path>

.sql 파일을 로드하여 실행할 수 있다.

RDB 설계 방법론

Avoiding Redundancy

DB를 설계할 때, 구조가 동일한 테이블을 여러 개 생성하여 중복을 초래하는 것은 피해야 한다.

또한, 애플리케이션은 DB의 데이터에 대한 CRUD(Create, Read, Update, Delete) 연산만 수행하여야 하고, 그 외 테이블 또는 column의 수정 등 DB의 구조를 변경하는 작업을 수행해서는 안된다.

column의 경우, 최소한의 유의미한 데이터들로 나누어 저장하는 것이 좋다. 예를 들어, 대학교의 개설 과목 유형을 ‘전공필수’, ‘전공선택’과 같은 값을 갖는 column 하나로 관리하기보다는 ‘전공’과 ‘교양’ 여부, ‘필수’와 ‘선택’ 여부를 각각의 column으로 나누어 저장하는 것이 좋다.

Relational Designing

실제 DB 관리에는 테이블 간 관계가 존재하게 되는 경우가 많다. 이를 표현하기 위해 FOREIGN KEY 를 사용한다.

Primary Key and Foreign Key

Primary Key(기본 키)는 테이블의 각 row에 부여된 고유 id이다. 관계형 데이터베이스에서는 참조되는 테이블이 아니더라도 Priamry Key를 설정하는 것이 권장된다.

기본 키 column 생성

CREATE TABLE `departments` (
`id` INT NOT NULL AUTO_INCREMENT,
`kor_name` VARCHAR(16) NOT NULL,
`eng_name` VARCHAR(50) NOT NULL,
`college` VARCHAR(16) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

위 SQL문에서 id 라는 column을 주목하여 보자. AUTO_INCREMENT 키워드를 사용하면 row 생성 시 자동으로 기존 값에 1을 더한 id값이 부여된다.

다른 테이블의 기본 키를 참조하는 키를 Foreign Key(외래 키) 라고 한다.

On Delete Action

ON DELETE로는 외래 키가 참조하는 기본 키가 삭제되었을 때의 action을 지정할 수 있다.

ON UPDATE는 기본 키가 수정되었을 때의 action을 지정한다.

• cascade(ON UPDATE에서 권장): 참조하는 row를 단계적으로 수정/삭제한다.
• set null: 참조하는 키의 값을 null로 설정한다.
• no action: 아무 행동도 취하지 않는다.
• set default: 참조하는 키의 값을 기본값으로 설정한다.
• restrict(ON DELETE에서 권장): 수정/삭제 대상 row를 참조하는 row가 있으면 수정/삭제가 불가하다.

기본 키 및 외래 키 설정

PRIMARY KEY (`id`),
FOREIGN KEY (`department`)
REFERENCES `departments`(`id`) ON DELETE CASCADE

위 구문은 테이블 생성 시 기본 키와 외래 키를 설정한다.

외래 키 설정 시 참조하는 테이블의 column 명이 같을 경우,

REFERENCES `departments` ON DELETE CASCADE

위와 같이 column 명을 생략 가능하다.

id column을 기본 키로 지정하고, department column을 departments 테이블의 id column을 참조하는 외래 키로 지정한다.

참고문헌

• Kweb 준회원 스터디 강의 자료

• Database System Concepts, 7th Edition (Silberschatz, Korth, Sudarshan)