SQL Schema, Query 디자인과 몇 가지 종류(관계형 DB)

스키마(Schema)란?

스키마(Schema)는 데이터 베이스에서 데이터가 구성되는 방식과 서로 다른 엔티티(entities)간의 관계를 설명한다. 데이터베이스의 청사진과 같다. 

 

 

학교를 위한 데이터베이스를 구축한다고 가정할 경우 간단한 예로 School이라는 스키마 즉 DB의 청사진을 만들었다. 쉽게 교사(Teacher), 수업(Classes), 학생(Student)과 같은 데이터를 저장하기위해 샘플을 준비해뒀다.

 

다시 보는 기본용어

entities: Teacher, Classes, Student같은 테이블을 표현하는 정보의 단위이다.

fields: 각 테이블이 가지고 있는 열들의 집합을 말한다.

record: 행렬에서 행에 해당되고, 각 열이 담고있는 자료를 말한다.

데이터관의 연결 관계

샘플을 예제로 3개의 테이블의 관계를 알아보자. 가장 먼저 Teachers와 Classes의 관계를 먼저 확인 해 보는게 좋을것 같다.

학교나 대학교에서 선생님 또는 교수님이 강의를 하게되는데 하나의 클래스에서 하나 또는 여러 과목을 주로 수업하거나 다른 클래스로 이동하면서 수업을 할것이다.  그렇게 되면 관계는 1:N의 관계가 될 것이다.

관계형성

우선 Teachers 테이블에 Classes 테이블을 담아줬다. 다음과 같은 방법은 좋지 못한 사례이다.

• 열(Column)의 크기는 고정되어있다. 만약 교사 및 교수가 수업 수가 늘어난다면 ID를 담을 공간이 부족할 수 있다.
• 한 열(Column)에 여러 값을 저장할 수 없고, 검색 시간이 매우 오래걸린다. 한 교사의 수업을 담당하고 있는 교사를 검색한다면 Teachers의 classes에 담긴 정보를 모두 검색하게 된다.

이렇기 때문에 위 예시는 문제가있다. 다른 방법을 알아보자

 

차라리 Classes테이블에 Teachers테이블을 담아 주었다.

이렇게 되면 교사나 교수가 담당한 모든 수업과 클래스를 찾아볼 수 있게된다.

 

Classes와 Students의 관계를 알아보자

 

어떤 전공과목을 듣는 학생이 교양과목을 듣는다고 생각해보자, 아니면 수학시간이 있고, 영어 시간도 있는 것처럼 한명의 학생이 다양한 수업, 강의를 들을 수 있고, 하나의 클래스에서는 과목당 다양한 학생들이 듣고있을 것이다. 이런 관점을 통해 classes와 students의 관계는 다대다 N:N(Many to Many) 관계가 될 것이다.

관계형성

 

각 클래스당 여러명의 학생이 있고, 각 학생은 여러 클래스에서 여러개의 수업을 듣는다. 위에서 설명했듯이 각열은 공간이 정해져있고, 검색시간 또한 매우 길어지게 된다..우선 딱 봐도 복잡해보인다.

 

이렇게 각 테이블이 형성되어있다면 각 참조하는 키(외래 키)를 공통으로 묶어 테이블을 생성해서 참조하는 방법이 있다.

이렇게 Classes와 Students 테이블이 1:N으로 참조하는 테이블을 Join Table이라고 한다.

 

최종 형태

데이터 정의 언어 ( DDL : Data Definition Language )

데이터베이스를 정의하는 언어이며, 데이터를 생성, 수정, 삭제하는 등의 데이터 전체 골격을 결정하는 역할을 하는 언어이다.

 

CREATE : 데이터베이스, 테이블 등을 생성하는 역할

ALTER : 테이블을 수정하는 역할

DROP : 데이터베이스, 테이블을 삭제하는 역할

TRUNCATE : 테이블을 초기화 시키는 역할

 

데이터 조작 언어 ( DML : Data Manipulation Language )

정의된 데이터베이스에 입력된 레코드를 조회, 수정, 삭제 등의 역할을 하는 언어이다.

 

SELECT : 데이터를 조회하는 역할 

INSERT : 데이터를 삽입하는 역할

UPDATE : 데이터를 수정하는 역할

DELETE : 데이터를 삭제하는 역할

MERGE : 데이터가 테이블에 존재하지 않으면 INSERT, 존재하면 UPDATE를 수행.

 

 

데이터 제어 언어 ( DCL : Data Control Language )

데이터베이스에 접근하거나 객체에 권한을 주는 등의 역할을 하는 언어이다.

 

GRANT : 특정 데이터베이스 사용자에게 특정 작업에 대한 수행권한 부여한다.

REVOKE : 특정 데이터베이스 사용자에게 특정 작업에 대한 수행권한을 박탈, 회수 한다.

 

( DQL : Data Query Language )

 

트랜잭션 제어 언어 ( TCL : Transaction Control Language)

 

COMMIT :  트랜잭션의 작업을 취소 및 원래대로 복구하는 역할을 한다.

ROLLBACK : 트랜잭션의 작업을 취소 및 원래대로 복구하는 역할을 한다.

SET TRANSACTION : 트랜잭션 지정