[컴퓨터과학 개론] 14강 - 데이터베이스(2)

✅ 1. 관계형 모델

(1) 관계형 데이터 모델

• 관계형 데이터 모델은 실세계 정보를 2차원 테이블("릴레이션") 형식으로 표현하고 구현한 것임.
• 테이블을 이용하여 데이터와 데이터의 관계를 표현하며, 데이터베이스를 2차원 테이블의 집합으로 간주함.
• 실제 데이터가 테이블 형태로 저장되는 것을 의미하지 않음. ( 논리적으로 표현하여, 직관적으로 이해하기 쉽게 표현 )
• 즉, 논리적 표현에 불과하며, DB 내부에서는 테이블이 아닌 디스크 블록, 페이지, B+트리, 해시 구조 등으로 저장됨.

(2) 관계형 모델과 관련된 용어

• 릴레이션: 관계형 데이터 모델에서 테이블을 의미함.
• 투플: 한 행을 의미함.
• 카디널리티 (행의 총 합): 한 릴레이션(테이블) 행의 수를 의미함. ( 투플의 개수를 의미함 )
• 차수(열의 총 합): 한 릴레이션(테이블) 열의 수를 의미함.
• 도메인: 속성이 가질 수 있는 모든 값의 집합이며, 데이터의 허용 범위를 의미함. ( 학번(INT), 이름(VARCHAR2) )
• 릴레이션 스키마: 릴레이션 이름과 속성들을 논리적으로 나타낸것을 의미함.
• 릴레이션 인스턴스: 어느 시점의 튜플들의 집합을 의미함. 조회했을 때 해당 튜플들인듯,...

(3) 릴레이션 - 개념

• DB는 여러 개의 릴레이션(테이블)으로 구성이 됨.
• 릴레이션이 가질 수 있는 모든 속성값의 모든 경우의 수에 대한 부분집합. ( 카테시안 곱의 부분집합 )
• 릴레이션 = 릴레이션 스키마 + 릴레이션 인스턴스

[ 릴레이션 스키마 ]

학생(이름: VARCHAR, 학번: INT, 학과: VARCHAR)

• 릴레이션의 논리적 구조이며, 릴레이션 이름과 속성으로 구성이 되어 있음.
• 시간에 무관하며 단지 속성에 대한 타입 지정 ( 결국 릴레이션 스키마 = 테이블 껍데기로 볼 수 있음 )
• 즉, 릴레이션의 구조적인 정의를 의미하며, 어떤 속성이 있는지, 이름이 무엇인지, 데이터 타입은 어떤 것인지 등을 정의함.
• 정리하면, 릴레이션 스키마는 테이블이 어떤 구조로 되어 있는지만 정의해 놓은 "틀" 임.

[ 릴레이션 인스턴스 ]

• 어느 한 시점에 릴레이션이 가지고 있는 투플의 집합
• 삽입, 삭제, 갱신 등을 통해 시간에 따라 변하는 릴레이션의 값을 의미함.
• 즉, 실제 테이블에 저장된 데이터(행, 투플) 들의 집합임. ( 실제 들어간 데이터들의 집합을 릴레이션 인스턴스라함 )

(4) 릴레이션 - 특징

• 릴레이션(테이블) 은 여러 투플(행)으로 이루어진 집합이며, 4가지 특징을 가지고 있다.
• ( 투플의 유일성, 투플의 무순서성, 속성의 무순서성, 속성값의 원자성 )
• 집합은 기본적으로 중복이 없고, 순서도 없는 특징이 있는데, 릴레이션도 이런 성질을 그대로 따름.

[ 투플의 유일성 ]

• 하나의 릴레이션에는 중복된 투플이 없음 투플의 유일한 식별이 가능함.
• 즉, 각 행은 유일해야 한다는 의미이며, 이걸 보장하기 위해 기본키(Primary Key) 가 필요함.

[ 투플의 무순서성 ]

• 한 릴레이션에 포함된 투플들은 순서를 가지고 있지 않음
• 즉, 테이블에서 행의 위아래 순서가 바뀌어도 의미는 동일하다는 뜻임.

[ 속성의 무순서성 ]

• 하나의 릴레이션을 구성하는 속성들 사이에는 순서가 없음.
• 즉, 컬럼 순서가 달라도 같은 의미라는 뜻임.
• 결국 데이터의 논리적 의미가 같다면, 컬럼 순서가 달라도 문제가 없음

[ 속성값의 원자성 ]

• 모든 속성의 값은 더 이상 분해가 불가능한 하나의 값인 원자값을 가짐.
• 즉, 하나의 셀에는 하나의 값만 들어가야 함을 뜻함. ( 여러개가 있으면 원자성 이라는 특징을 해침 )

(5) 키(Key)

• 릴레이션(테이블) 안에서 투플(각 행)을 구분하기 위한 기준(식별자) 가 키(Key)이다.
• 키의 특성을 만족하려면, 유일성과 최소성을 만족해야만 한다.
• 유일성: 키 값이 각 행을 유일하게 식별할 수 있어야 한다. ( unique )
• 최소성: 키는 투플을 유일하게 식별할 수 있는 최소한의 속성들로만 구성되어야 한다. ( 불필요한 컬럼이 섞이면 안된다는 뜻 )
• 쉽게말해, 하나의 컬럼만으로 키 값을 이용해야하며, 불필요한 컬럼이 섞이면 안된다는 의미임.

(6) 키(Key) 의 종류

• 슈퍼키: 유일성을 만족하는 속성 또는 속성의 집합
• 후보키: 슈퍼키 중에서 최소성까지 만족하는 것
• 기본키: 후보키 중에서 기본적으로 사용할 키로 선탠된 것을 의미함.
• 대체키: 후보키 중에서 기본키로 선택되지 않은 것을 의미함.
• 외래키: 다른 릴레이션의 기본키를 그대로 참조하는 속성 또는 속성의 집합을 의미함.
• ( 상호 관련 있는 릴레이션 사이에서 데이터 일관성을 유지하는 수단임 )

(7) 제약 조건

• 제약 조건은 모든 릴레이션 인스턴스가 만족해야 하는 조건을 의미함.
• 즉, 테이블 안의 데이터가 지켜야 하는 규칙을 말함.
• 이걸 안 지키면 데이터가 잘못되거나 모순될 수 있기 때문에 DBMS가 자동으로 막아줌.

[ 영역 제약 조건 ]

• 각 속성의 값은 반드시 해당 영역(Domain) 에 포함되어야 하며, 원자값이어야 함.
• 쉽게말해, 각 컬럼에는 미리 정해진 자료형(데이터 타입)의 값만 들어올 수 있으며, 한 셀 안에는 분리할 수 없는 하나의 값인 원자값만 들어와야 한다는 의미이다. ( 원자값 예시: "서울, 부산" X / "서울" O )

[ 키 제약 조건 ]

• 서로 다른 두 투플은 모두 속성에 대해 같은 속성값 조합을 가질 수 없음
• 하나의 키가 모든 투플을 유일하게 식별할 수 있어야 함.
• 쉽게말해, 테이블 안에서 두 행이 동일한 키값을 가질 수 없다는 의미(UNIQUE)이다.
• 예를 들면 학번이 키본키라면, 같은 학번을 가진 학생은 두 명 존재 할 수 없는 의미임. 즉, 유일성을 만족해야함.

[ 개체 무결성 제약 조건 ]

• 투플을 유일하게 식별하기 위해서 어떠한 기본키값도 널값이 될 수 없음.
• 즉, 기본키 값은 널(NULL) 일 수 없는 제약 조건임. ( 각 행을 구별해야 하므로, 비워둘 수 없음 )

[ 참조 무결성 제약 조건 ]

• 다른 릴레이션에 의해 참조되는 투플은 반드시 그 릴레이션 내에 존재해야 함. ( 참조 무결성 이어야 한다는 의미임 )
• 쉽게말해, 외래키가 참조하는 값은 반드시 참조된 테이블(부모 테이블)에 존재해야 한다는 규칙이다.

---

✅ 2. 데이터베이스 설계

• 사용자의 요구 조건에서부터 DB 구조를 도출하고 구축하는 과정이다.

• 데이터 처리와 응용 프로그램 설계 관련 활동(Java 등) 과 DB 내용 및 구조 설계 관련 활동 두 가지 설계를 같이 병행함.

(1) 설계 과정 - 사용자 요구 사항 분석

• 사용자의 의도하는 데이터베이스의 목적과 용도를 파악하는 것으로부터 설계가 시작된다.
• 잠정적인 데이터베이스 사용자가 어떤 사용자 인지 범위를 파악하여 식별을 하게 됨.
• 공식적인 요구 조건 명세 정의 - 정적 정보 구조(DB 내용/구조 설계)에 대한 요구 조건 파악
• 공식적인 요구 조건 명세 정의 - 동적 DB 처리(데이터 처리, 응용 프로그램) 요구 조건 파악
• 공식적인 요구 조건 명세 정의 - 범기관적 제약 조건 파악
• 요구 사항 명세서, 요구 사항 정의서 등의 문서화 작업까지 포함

(2) 설계 과정 - 개념적 DB 설계

• 개체 타입과 개체 타입 간의 관계를 나타내기 위해 특정 DBMS와는 무관한 개념적 데이터 모델을 사용하여 데이터베이스에 대한 개념적 구조를 생성하는 과정이다.
• 개념적 구조: 사용자의 요구 사항을 간단히 기술한 것으로, 데이터 타입, 관계, 제약 조건을 설명 한다. ( ERD )
• E-R 모델: 관계형 데이터베이스를 위한 대표적 개념적 모델
• 트랜잭션(응용 프로그램) 모델링 과정도 해당 단계에서 이뤄짐.

(3) 설계 과정 - 논리적 DB 설계

• 상용 DBMS를 사용해서 고수준의 개념적 구조를 목표 DBMS의 논리적 데이터 모델로 변환하는 과정이다.
• 즉, 해당 단계에서부터는 특정 DBMS에 의존하는 단계라는 의미이며, 논리적 데이터 모델링 단계라고 부름.
• 결과적으로, 목표 DBMS의 데이터 정의어로 기술된 스키마가 만들어지는 과정임.
• **쉽게말해, DBMS 마다 다른 부분을 DBMS 를 정하면서 해당 DBMS에 특징에 맞춰서 설계를 하는 과정으로 보면 됨.**
• 해당 단계에서는 입출력과 기능적 형태로만 정의된 트랜잭션에 대한 인터페이스 설계하는 과정이 이루어짐.
• **쉽게말해, 기능(트랜잭션)을 SQL문으로 구체화하는 과정으로 볼 수 있는듯.**

[ 논리적 DB 설계 - 논리적 모델링 ]

• 개념적 구조를 목표 DBMS의 구조로 변환하는 과정이다. ( 쉽게말해, 내가 선택한 DBMS 에 DB에 맞게 설계하는 것 )
• 즉, 개념적 구조란 ERD 를 말하고, ERD 에 그려진 개체(사각형), 관계(마름모)를 실제 테이블 형태로 바꾸는 과정임.
• 아직 구현하는 단계는 아니지만, 개념적 구조를 DBMS에 맞게 더욱 상세히 설계가 된 모델로 볼 수 있음.
• ERD 에서 사각형으로 그려진 개체(Entity) 는 관계형 모델로 바꿀 때 하나의 테이블(개체 릴레이션)이 된다.
• 개체 타입에 속한 속성은 해당 개체 릴레이션의 속성이 됨. 즉, ERD에서 개체에 달려 있던 속성(타원형) 들은 그대로 테이블의 컬럼(속성)이 된다는 뜻임.
• 마름모의 관계 타입은 관계 릴레이션으로 표현이 됨. 즉, 테이블간의 관계에 대한 연결을 하는 상태로 만들어짐.
• 관계 타입에 속한 속성은 해당 관계 릴레이션의 속성이 됨. 즉, 외래키 정의와 관련된 의미임.
• 연관된 개체 타입의 키 속성을 관계 릴레이션의 속성으로 포함시킴.

• 또한, 위와 같이 관계 릴레이션 중간 테이블을 사용하지 않고, 묵시적으로 관계를 표현 할 수도 있음.

(4) 설계 과정 - 물리적 DB 설계

• 논리적 구조로부터 효율적이고 구현 가능한 물리적 데이터베이스 구조를 설계하는 것임.
• 물리적 DB 구조: 데이터베이스에 포함될 여러 파일 타입에 대한 저장 레코드의 양식, 순서, 접근 경로, 저장 공간의 할당 등을 표현한 것이다. ( 즉, 실제 어떻게 저장이 되는가에 대한 저장 관련 내용이 포함이 될 수 있음. )
• 트랜잭션에 대한 세부적인 설계가 포함됨.
• 쉽게말해, 제약조건 구현, 인덱스 설계, 보안/권한 설정 등을 하는 단계이고, 이것들은 구현 단계에서 실행이 됨

(5) 설계 과정 - 구현

• 목표 DBMS의 데이터 정의어로 기술된 명령문(논리적 단계에서 만들어진 것)을 컴파일하고 실행해서 데이터베이스 스키마와 빈 데이터베이스 파일 생성을 하게 된다.
• 이후, 실제 데이터 적재 -> 데이터베이스 실행 및 운영 하는 단계이다.
• 트랜잭션의 구현을 하게 됨.

---

✅ 3. SQL

(1) SQL (Structured Query Language) 란?

• 구조화된 질의 언어(Structured Query Language) 로 관계형 데이터베이스를 다루기 위한 질의어임.
• IBM의 관계형 데이터베이스 시스템인 SYSTEM R을 위해 처음 설계가 되었음.
• 현재는 미국 표준, 국제 표준으로 제정되어 전 세계 거의 모든 관계형 DBMS 에서 사용이 됨.

[ 특징 ]

• SQL은 단순히 검색용 언어가 아닌, 데이터의 정의(DDL), 조작(DML), 제어(DCL) 까지 다룰 수 있는 완전한 DB언어임.
• 대화식으로 직접 터미널에서 사용할 수도 있고, C, Java, Python 같은 언어 안에 삽입해서 함께 사용할 수도 있음.
• SQL 에서는 관계형 모델의 릴레이션, 투플, 속성 대신 테이블, 행, 열 이라는 용어를 더 많이 사용함. ( 선호함 )
• SQL은 사용자가 처리 절차를 정의하지 않고 결과만 요청하는 비절차적 언어이다.

# 절차적 언어: Python
result = [] // # ages 리스트에서 30세 이상만 골라내는 절차
for age in ages:
    if age >= 30:
        result.append(age)

# 비절차적 언어: SQL
SELECT * FROM people WHERE age >= 30;

• 비절차적 언어: 원하는 결과 조건만 명시하면 절차는 DBMS 가 자동으로 처리를 해줌.

(2) 데이터 정의어

• 스키마, 도메인, 테이블, 뷰, 인덱스 를 정의하거나 수정 및 제거하는 문장(명령어)으로 구성이 되어있음.

[ 데이터 정의어 - 기본적인 데이터 타입 ]

• 데이터 정의어 - 테이블을 만드는 과정에는 데이터 타입을 같이 명시를 해줘야 함.

(2) 데이터 정의어 - 테이블 생성

• 위와같이 릴레이션 스키마(릴레이션 이름과 속성) 을 만들기 위해서는, 아래와 같이 SQL 문을 작성하면 된다.
• 각각의 릴레이션 속성에는 이름을 속성명을 명시해주고, 데이터 타입과 크기를 설정해 줄 수 있음.
• PRIMARY KEY 설정을 통해 제약 조건을 걸음으로써, 공급자번호는 unique 를 보장받으므로, 무결성을 만족 할 수 있음.
• 즉, 위와 같이 여러 테이블을 CREATE TABLE 이라는 데이터 정의어를 통해 만들 수 있음.

• 또한, 두 개 이상의 테이블 관계를 맺기 위해서는 FOREIGN KEY(외래키) 제약조건을 통해서, 관계를 맺을 수 있음.
• 해당 외래키 제약조건은 보통 상대 테이블의 PRIMARY KEY(기본키) 를 기반으로 관계를 맺음. ( JOIN 가능 )

(3) 데이터 정의어 - 테이블 수정 및 컬럼 삭제

[ 새로운 열 추가 ]

ALTER TABLE 테이블명 ADD COLUMN 컬럼명 데이터타입 [제약조건];

# employees 테이블의 VARCHAR(100) 데이터 타입을 갖는 email 컬럼 ADD
ALTER TABLE employees ADD COLUMN email VARCHAR(100);

# stock 값이 반드시 존재해야 하며, 기본값은 0으로 설정됩니다.
ALTER TABLE productsADD COLUMN stock INT NOT NULL DEFAULT 0;

• 위와 같은 질의어 구성을 통해 테이블 수정을 할 수 있음.

[ 기존 열 삭제 ]

ALTER TABLE 테이블명 DROP COLUMN 컬럼명;
ALTER TABLE employees DROP COLUMN email;
# RESTRICT, CASCADE options
ALTER TABLE 테이블명 DROP COLUMN 컬럼명 RESTRICT; # RESTRICT 디폴트 옵션
ALTER TABLE 테이블명 DROP COLUMN 컬럼명 CASCADE; # CASCADE 참조 강제 삭제

• CASCADE: 삭제 시 참조하고 있는 모든 제약조건/객체까지 같이 삭제를 해준다.
• RESTRICT: 참조가 있으면 삭제 불가능하게 안전하게 막아줌. ( 보통 이 옵션이 디폴트로 적용되어 있음. )

[ 기존 열에 대한 기본값 지정 및 삭제 ]

ALTER TABLE 테이블명 ALTER 컬럼명 (DROP DEFAULT / SET DEFAULT 기본값)

# SET DEFAULT: 기본값을 지정 질의어 -> age 컬럼의 기본값 18 설정
ALTER TABLE users ALTER COLUMN age SET DEFAULT 18;
# DROP DEFAULT: 기본값 제거 질의어 -> age 컬럼의 기본값 제거
ALTER TABLE users ALTER COLUMN age DROP DEFAULT;

• 속성에 대한 데이터 기본값을 삭제하거나 지정해주는 질의어임.

(4) 데이터 정의어 - 테이블 삭제

DROP TABLE 테이블명 [CASCADE, RESTRICT];
DROP TABLE employees; # 디폴트 RESTRICT

• 테이블에 대한 설명이 카탈로그에서 제거되므로, 기본 테이블에서 정의된 모든 인덱스와 뷰도 자동으로 삭제가 된다.

---

(5) 데이터 조작어

• 기본 테이블, 뷰에 해당하는 데이터를 조작(검색, 갱신, 삭제, 삽입) 하기 위한 명령문이다.

(5) 데이터 조작어 - 데이터 검색 SELETE 문

• ALL: 중복을 허용하며, 모든 데이터를 보여주는 옵션임.
• DISTINCT: 중복을 모두 제거하여 보여주는 옵션임.

• AS 를 통해 거래포인트의 컬럼명을 거래점수로 변경하여 조회가 가능함.
• ORDER BY 를 통해 정렬을 할 수 있고, 옵션으로 DESC(내림차순) ASC(오른차순) 설정이 가능함.
• FROM 에 테이블 두 개를 지정해 두 테이블의 관한 WHERE 조건을 설정해 조회가능.

• SUM() 과 같은 함수를 통해 테이블에 저장된 속성값끼리 연산을 할 수 있음.
• GROUP BY 를 통해 그룹으로 묶을 수 있으며, GROUP BY 에는 HAVING 조건절을 사용 할 수 있음.

• IN: 서버쿼리 결과에 특정 값이 존재하는지 비교
• EXISTS: 서브쿼리 결과가 하나라도 존재하는지 여부만 확인

(6) 데이터 조작어 - 데이터 삽입 INSERT 문

• INSERT INTO 테이블명(컬럼명, 컬럼명, ...) VALUES('컬럼값', '컬럼값' ...) 을 통해 데이터 삽입이 가능함.

(7) 데이터 조작어 - 데이터 삭제 DELETE 문

• DELETE FROM 테이블명; 을 하게 되면 해당 테이블의 속성값들이 모두 삭제가 됨.
• 테이블, 컬럼 등 테이블 구조는 남아 있지만, 테이블의 속성값은 날라가게 됨.
• 아래 DROP TABLE 테이블명; 같은 경우에는 테이블 자체를 삭제하는 것이므로, 구조를 아예 날리게 된다.
• 즉, 테이블이 사라지는 동시에 내부의 컬럼들과 속성값 등이 다 날라감.

(8) 데이터 조작어 - 데이터 갱신 UPDATE 문 

• WHERE 조건을 활용해 어떤 행을 바꿀건지 명시 하며, SET 을 통해 특정 컬럼의 속성값을 넣어서 수정함.

---

✅ 4. SQL - 뷰

CREATE VIEW high_score AS
SELECT name, score FROM student WHERE score >= 90;

SELECT * FROM high_score; -- 뷰는 가상의 테이블처럼 조회됨

• 뷰(View): 하나 이상의 테이블을 기반으로 만들어진 가상 테이블을 의미함.
• 뷰 내용은 물리적으로 구현되어 실제적으로 존재하는 것이 아니라 뷰에 대한 조작을 요구할 때마다 기본 테이블의 데이터를 이용해서 내용을 만들어서 사용자에게 있는 것처럼 보여주는 기능을 수행함.
• 뷰는 함수처럼 재사용 가능하면서, 결과가 테이블처럼 보이는 특성 때문에 가상 테이블 이라고 부름.
• 즉, 뷰의 본질은 쿼리에 이름을 붙여서 재사용 가능하게 하는 것이 핵심이며 본질임. ( 그리고 SELECT 문만 사용? )

(1) 뷰 장점

• DB 재구성(확장, 구조변경) 면에서 어느 정도의 논리적 데이터 독립성을 제공함.
• 동일 데이터에 대해 동시에 여러 사용자에게 다양한 뷰를 제공함
• 특정 사용자가 관심있는 데이터에만 초점을 맞추고 나머지는 무시함
• 사용자의 데이터에 대한 인식과 관리를 단순화시킴
• 감춰진 데이터에 대해 보안이 자동으로 제공이 된다.

(2) 뷰 단점

• 독자적인 인덱스를 가질 수 없고, 정의 변경 불가, 삽입/삭제/갱신 연산에 제약이 따름.