[SQLD] 6강. SQL 실습 환경 구성

Hyunjun Kim·2024년 11월 13일

SQL Developer (자격증)

목록 보기

6/11

06. SQL 실습 환경 구성

관계형 데이터베이스 개요

데이터베이스란?

데이터베이스 (Database)

데이터베이스는 통상 넓은 의미로 '정보의 모음'을 의미합니다. 핸드폰이 없던 시절 우리는 어떤 방식으로 전화번호를 기억했을까요? 전화번호를 노트에 하나하나 적어두고 필요할 때마다 다시 확인했습니다. '전화번호'라는 정보를 한곳에 모아서 관리한다고 볼 수 있습니다. 다들 학창 시절 알림장을 썼던 기억이 있으실 텐데요, 필요한 준비물을 까먹지 않기 위해 필요한 정보를 저장하는 용도로 사용했습니다. 알림장도 한 곳에 정보를 모아서 관리한다는 측면에서 데이터베이스로 볼 수 있습니다.

이번에는 데이터베이스의 의미를 일상에서 보다 업무적인 영역으로 옮겨와서 생각해 보겠습니다. 온라인 플랫폼에서 물건을 구매하는 행위는 판매자의 데이터베이스에 접근하여 특정한 데이터를 저장하는 것이라고 볼 수 있습니다. 만약 구매한 물건의 목록을 확인한다면 저장된 데이터를 조회하는 행위라고 볼 수 있겠죠. 온라인 서비스에 로그인하거나 은행 계좌에서 돈을 인출하는 행위 모두 본질적으로는 같은 방식으로 데이터베이스와 소통하는 과정입니다. 결과적으로 일상에서 하는 수많은 행위는 정보로 저장되고 해당 정보를 여러 가지 방식으로 활용하는 과정은 모두 데이터베이스와 소통한다고 볼 수 있습니다. 매 순간 만들어지는 정보를 그저 쌓아두기만 하는 것이 아니라 효율적으로 관리하는 것이 필요합니다. 그러면 여기서 말하는 '효율적인 관리'란 어떤 것을 의미하는지 알아보도록 하겠습니다.

관계형데이터 베이스 (Relational Database)

현재 사용하는 보편적인 데이터베이스가 등장하기 이전에는 파일 시스템을 활용하여 데이터를 관리했습니다. 파일 시스템은 데이터를 파일 단위로 관리하기 위해 파일을 생성, 삭제, 수정, 조회할 수 있는 기능을 제공해 주는 소프트웨어를 의미합니다. 파일 1개가 수많은 데이터를 저장할 수 있는 구조인데, 이러한 데이터의 관리에는 몇 가지 문제점이 있었습니다.

우선 구조화되지 않고 데이터를 일방적으로 저장하기 때문에 동일한 내용의 정보가 여러 파일에 반복적으로 저장되는 문제가 있습니다. 중복되는 정보로 인해 불필요한 저장 공간을 사용해야 했고 원하는 데이터를 어디서 찾아야 하는지 알 수 없어 비효율적입니다. 해당 파일을 사용하는 프로그램에 종속적이라 프로그램이 바뀌면 정보를 저장하는 데이터의 형식도 바꿔야 하는 불편함이 있습니다. 나아가 저장해야 하는 데이터의 형식 등을 통제할 수 없어 축적된 데이터를 원하는 방향으로 사용하기 위해서는 많은 비용을 들여 처리하는 과정이 필요합니다. 결론적으로 파일 시스템을 통한 정보의 관리는 중복되는 데이터를 관리하기 어렵고 일관성 있는 데이터의 축적에 큰 한계가 있습니다.

여러 문제점을 고려하는 과정에서 데이터를 조금 더 체계적으로 정의하고 조작하는 방법을 고민하게 되었고 IBM사에 의해 관계형 데이터 베이스가 등장했습니다. 관계형 데이터 베이스는 데이터베이스를 체계적으로 관리하고 운영하는 소프트웨어입니다. 사용자가 정해진 방식으로 요청하면 행과 열의 2차원 관계로 정의된 데이터의 생성, 수정, 삭제, 그리고 조회가 가능합니다. 그럼 구체적으로 어떤 방식으로 데이터베이스 관리 시스템의 주요 기능을 활용할 수 있는지 알아보겠습니다.

관계형 데이터베이스 관리 시스템

관계형 데이터베이스 구조에 기반한 관계형 데이터베이스 관리 시스템 (Relational Database Management System, RDBMS)는 메타 데이터를 총관 관리할 수 있기 때문에 데이터의 성격, 속성 또는 표현 방법 등을 체계화할 수 있습니다. 또한 데이터 표준화를 통해 데이터 품질을 확보할 수 있는 장점을 가지고 있습니다.
RDBMS는 인증된 사용자만이 참조할 수 있도록 보안 기능을 제공합니다. 사용자가 실수로 조건에 위배되는 행동을 할 경우 이를 방지하여 데이터 무결성(Intergrity)을 보장합니다.
RDBMS는 시스템의 갑작스러운 장애로부터 사용자가 입력, 수정, 삭제하던 데이터가 제대로 반영될 수 있도록 보장해주는 기능과 시스템 다운, 재해 등의 상황에서도 데이터를 회복/복구할 수 있는 기능을 제공합니다.

SQL

SQL 이란?

지금까지 왜 데이터베이스를 사용해야 하는지, 나아가 관계형 데이터베이스를 통해 어떤 것이 가능해졌는지 알게 되었습니다. 이 시점에서 한 가지 궁금한 점이 생길 수 있습니다. 데이터를 보관하는 방법이 어떤 형태로 구성되어 있는지는 알겠는데, 새로운 정보를 데이터베이스에 넣고 수정하고 삭제하는 등의 행위는 어떤 과정을 통해 가능할까요? 이런 일을 수행하기 위해 고안된 언어가 바로 SQL입니다.

SQL은 Structured Query Language의 약자로 관계형 데이터베이스와 통신할 때 사용하는 구조화된 질의 언어를 의미합니다. 여기서 '질의(query)'라고 하는 것은 '질문'을 떠올리시면 됩니다. 궁금한 점을 누군가에게 질문하듯 SQL이라는 언어를 사용하여 질문하면 그에 대한 응답으로 어떠한 결과를 받을 수 있습니다. 질문은 데이터를 생성, 수정, 삭제, 그리고 조회하는 문장으로 구성되어 있으며 특정 권한을 부여받은 사람만 이러한 행동을 할 수 있도록 제어할 수도 있습니다. 다음과 같은 형태로 분류 가능합니다.

SQL 언어 분류

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

관계형 데이터베이스의 구조를 정의하는 데 사용하는 언어입니다.

데이터를 저장하기 위한 데이터베이스의 기본 구조를 잡는데 사용하는 언어입니다.
데이터를 저장하는 테이블을 만들거나 수정할 수 있습니다.
테이블을 생성(CREATE), 변경(ALTER, RENAME), 삭제(DROP) 할 수 있습니다.

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

테이블에 저장된 데이터를 조작하는 데 사용하는 언어입니다

데이터 정의 언어로 생성된 구조를 실제 데이터로 채우는데 사용하는 언어입니다.

데이터를 삽입(INSERT), 조회(SELECT), 수정(UPDATE), 삭제(DELETE) 할 수 있습니다.

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

데이터베이스에 접근하여 이를 제어하기 위한 권한을 관리하는 데 사용하는 언어입니다.

특정한 사용자의 데이터베이스 사용 권한을 부여하거나 박탈할 수 있습니다.

데이터베이스 접근하고 객체를 사용하도록 권한을 부여(GRANT)하거나 회수(REVOKE)할 수 있습니다.

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

논리적인 작업의 단위를 묶어서 DML에 의해 조작된 결과를 작업단위(트랜잭션)별로 제어하는 언어입니다.

논리적인 작업의 단위를 묶어서 DML에 의해 조작된 결과를 작업단위(트랜잭션) 별로 제어할 수 있습니다. 이때 COMMIT, ROLLBACK 등의 명령어를 수행할 수 있습니다.
트랜잭션은 다음과 같은 특징을 갖습니다. (ACID)

원자성(Atomicity)
하나의 논리적인 단위로 묶여있는 트랜잭션이 데이터베이스에 모두 적용되거나 아니면 적용되지 않거나 둘 중 하나의 선택지만 있어야 합니다.

일관성(Consistency)
트랜잭션의 작업 처리 결과는 항상 일관되어야 합니다.

독립성(Isolation)
둘 이상의 트랜잭션이 동시에 진행되고 있는 상황에서 하나의 트랜잭션의 결과가 다른 트랜잭션의 결과에 영향을 줄 수 없습니다.

지속성(Durability)
트랜잭션을 통해 특정한 작업이 마무리된 경우 결과는 데이터베이스에 영구적으로 반영되어야 합니다.

SQL은 특정한 규칙에 맞춰서 구문을 작성해야 하는 자체 표준이 존재합니다. 다만, 관계형 데이터베이스를 제공하는 회사마다 특징이 있기 때문에 완전하게 통일된 형태로 제공하지 않습니다. 따라서 데이터베이스를 제공하는 회사에 따라 다른 처리가 필요하다는 점을 숙지하고 있어야 합니다.

SQL Syntax

모든 SQL 문(statement)는 SELECT, INSERT, UPDATE 등과 같은 키워드로 시작하고, 하나의 statement는 세미콜론(;)으로 끝납니다.

SQL 문 (Statement)

독립적으로 실행할 수 있는 완전한 SQL 코드 조각
즉, 문(Statement)은 절(Clause)로 구성됨

SQL 절(Clause)

문(Statement)의 하위 단위

테이블

관계형 데이터베이스는 데이터베이스의 기본 단위인 테이블의 형태로 데이터를 저장합니다. '테이블'이라는 말이 다소 생소하지만 우리가 자주 사용하는 MS사의 Excel 프로그램을 떠올리면 비교적 쉽게 이해할 수 있습니다. Excel에서 제공하는 시트를 데이터베이스에서 하나의 테이블에 대응시켜 생각해 볼 수 있습니다. 시트는 행과 열로 구성되어 있으며 열은 어떤 정보가 들어갈지 정의하고 행은 실제 데이터가 담기는 구조입니다. 이는 관계형 데이터베이스 테이블의 구조와 동일하며 행과 열의 2차원의 형태로 어떤 정보를 어떻게 담을지 통해 구조화 시킨 것이라고 할 수 있습니다. 정리하면 테이블은 관계형 데이터베이스에서 데이터를 저장하는 기본 단위이며 관계형 데이터베이스에서 모든 데이터는 칼럼과 행의 2차원 구조로 표현할 수 있습니다. 세로 방향을 칼럼(Column), 가로 방향을 행(Row)이라고 부르고 이들이 교차하는 하나의 공간을 필드(Field)라고 부릅니다.

2차원 관계형 테이블을 통해서 어떠한 데이터를 저장한다고 하더라도 그 데이터가 완전한 형태로 저장되었다고 확신할 수는 없습니다. 저장 과정에서 중복된 정보가 들어갈 수도 있고 불필요한 칼럼이 존재하여 저장 공간의 낭비가 발생할 수 있습니다. 이러한 문제점 등을 해결하여 데이터의 불필요한 중복을 줄이는 것을 정규화(Normalization)라고 합니다.

테이블은 어떠한 관계를 가질 수 있습니다. 예시를 하나 들어보겠습니다. 게시판 서비스를 많이 활용해 보셨을 텐데, 게시글에는 보통 댓글이 있습니다. 게시글은 게시글을 표현하기 위한 정보가 있고 댓글을 표현하기 위한 정보가 존재합니다. 그럼 각자 이런 정보를 가지고 있는 것만으로 두 정보의 관계가 '연결'되어 있다고 볼 수 있을까요? 아닙니다. 단순하게 보면 그저 정보가 나열되어 있는 것이라고 볼 수 있습니다. 관계 설정을 위해서는 몇 가지 추가적인 작업이 필요합니다.

우선 특정한 정보를 담고 있는 테이블에서 특정한 row (데이터)를 구분할 수 있는 구분자가 필요합니다. 테이블에 있는 특정한 데이터에서는 그래야 다른 정보를 담고 있는 테이블에서 특정 정보를 참고할 때 이를 유일하게 식별할 수 있는 어떤 값을 찾아낼 수 있습니다. 대한민국 사람은 모두 주민등록번호라는 고유한 값을 통해 구분될 수 있는 것을 생각해 보면 쉽게 이해할 수 있습니다. 이렇게 특정한 데이터를 유일하게 구분하는 식별자를 기본키(Primary Key)라고 합니다. 기본키(Primary Key)는 테이블에 존재하는 하나의 행(row)을 한 가지 의미로 특정할 수 있는 한 개 이상의 칼럼을 의미합니다. 하나의 테이블에서 중복되지 않으며 고유하게 식별할 수 있어야 합니다. 예를 들어, 게시글 테이블에서 게시글 번호는 행을 구분하는 고유하고 중복되지 않은 값이기 때문에 기본키라고 할 수 있습니다.

다음으로 이 식별 정보를 활용해서 연결 관계를 표현하는 과정이 필요합니다. 관계 표현을 위해서는 외부키(Foreign Key)에 대한 이해가 필요합니다. 외부키(Foreign Key)는 다른 테이블의 기본키(Primary Key)로 활용되고 있는 열과의 관계를 연결하는 칼럼을 의미합니다. 예를 들어, 게시글 테이블에서 게시글 번호는 댓글 테이블에서 해당 댓글이 어떤 게시글에 달린 댓글인지 구분하는 역할을 하기 때문에 외부키라고 할 수 있습니다. 결과적으로 댓글 테이블에 있는 게시글 번호 칼럼(외부키)을 통해 테이블 간 연결 정보를 표현할 수 있습니다.

ERD

테이블 간 상관 관계를 이미지로 도식화한 것을 'Entity-Relationship Diagram'이라고 하며 간략하게 ERD로 줄여서 부릅니다. ERD는 Entity(개체), Relationship(관계), 그리고 Attribute(속성)으로 구성되어 있으며 현실 세계의 데이터는 이렇게 3가지 구성 요소로 모두 표현 가능합니다. SQL의 표준이 존재하기는 하지만 DBMS를 제공하는 회사에 따라 상세 내용은 상이한 것처럼 ERD도 이를 제공하는 회사에 따라 조금씩 다르게 표현합니다. 대표적으로 바커 표기법과 I/E 표기법으로 구분되는데, 이에 대해 하나씩 알아보겠습니다.

바커 표기법(Baker Notation)

바커 표기법은 영국 컨설팅 회사 CACI에 의해 처음 개발되었고 리처드 바커에 의해 지속적으로 개선되어 왔습니다. 바커 표기법에서 Entity(개체)는 데이터베이스를 사용하는 주체가 지속적으로 보관하고 관리하는 대상을 의미합니다. 조금 더 넓은 의미로 표현하면 세상에 존재하는 무언가를 지칭합니다. 예를 들어, 사람, 회사의 사원, 학생, 친구 등이 될 수 있습니다. 그리고 데이터베이스 상에서 Entity가 의미를 갖기 위해서는 반드시 두 개 이상의 속성을 가져야 합니다. Attribute(속성)는 하나의 Entity에 종속되는 명사적 단어를 의미합니다. 일반적으로 명사적 단어 중에서 구성 요소를 포함하고 있는 명사들은 Entity가 되고 그렇지 않은 명사는 Attribute가 됩니다. 어떤 속성에 특정한 값을 반드시 저장해야 하는 경우는 *(Mandatory)을 표시해야 하며 값이 존재할 수도 있고 아닐 수도 있는 경우는 o(Optional)로 표시해야 합니다. Relationship(관계)은 두 개의 Entity 간의 조건 관계를 표기한 후 해당하는 Entity의 가까운 위치에 관계 명칭을 표기하고 관계(Relationship)는 실세계의 해당 Entity에서 발생하는 동사적 단어들을 표기합니다.

I/E 표기법(Information Engineering Notation)

Information Engineering(I/E)은 정보 시스템을 구축하는 데 있어 데이터 분석과 데이터베이스의 설계 과정에 매우 중요한 기법으로 자리 잡은 표기법입니다. 이 모델은 관계를 표현하는 데 있어 다수(many)에 해당하는 대상을 나타내기 위해 까마귀 발 모양을 사용하기 때문에 까마귀 발 모델(Crow's Foot Model)이라고 부르기도 합니다. 많은 문서에서 까마귀 발 표기법을 사용하기 때문에 정확하게 알아 두어야 합니다.

Entity(개체)는 바커 표기법과 마찬가지로 사용자가 추적하고자 하는 어떤 대상을 의미합니다.

Attribute(속성) 또한 바커 표기법과 마찬가지로 Entity의 특징을 기술해 주는 여러 개의 attribute를 가질 수 있습니다. 속성은 Entity 안에 위치해야 합니다.

Relationship(관계)는 까마귀 발 부호를 통해서 나타낼 수 있습니다. 관계의 다(Many)를 보여주는데 까마귀 발이 사용되며 타원(Oval), 해쉬 마크 등을 활용한 다양한 조합은 아래 표에 나온 것처럼 나타낼 수 있습니다.