[CS] 데이터베이스 - 3. SQL 심화

J.Noma·2022년 1월 29일
0

컴퓨터 공학

목록 보기
3/18

Reference

✅ Remind

  • unique만으로는 not null 기능이 없다
  • Referential Integrity을 연결하는 두 Attribute는 이름은 달라도 되지만 도메인은 같아야 한다

🌀 Join 연산

Join 연산은 기본적으로 두 개의 Relation을 결합하여 하나의 Relation을 결과로 도출한다. SQL은 기존에 배운 Natural Join 외에도 다양한 형태의 join 연산을 제공한다. Join의 조건을 지정하거나 Natural Join에서는 제거되었던 튜플들을 포함하는 연산 등이 있다

🔸 Join 관계란

  • Join Operation : 두 relation을 가지고 하나의 relation을 도출하는 연산. 보통 from절의 subquery로 많이 사용됨
  • Join Condition : 어떤 튜플을 매치시킬 것인지, 어떤 Attribute를 결과에 포함시킬지
  • Join Type : 서로 중복되지 않는 튜플을 어떻게 처리할지

참고로, 기존의 Natural Join에서는 이름이 같은 Attribute가 전부 통합 대상이었지만 Join Condition의 using을 사용하면 통합 대상을 지정해줄 수 있습니다. on에 대해선 아래에서 다룹니다

🔸 Outer Join

Outer Join이란, Natural Join에서 서로 중복되지 않아 탈락시키던 튜플을 살려주는 join입니다 (채울 수 없는 Attribute를 NULL로 채움). 반대로 기존에 알고 있던 Natural Join처럼 겹치지 않는 튜플을 삭제하는 경우를 Inner Join이라고 한다

Outer Join에는 join 연산자를 기준으로 왼쪽 Relation만 살리는 Left Outer Join, 오른쪽만 살리는 Right Outer Join, 모두 살리는 Full Outer Join이 있습니다

🔸 on 조건문

on을 사용하면 Join의 결과 relation 튜플들에 조건을 설정할 수 있다. where절처럼 사용된다. 위의 두 예제는 동일한 결과를 가진다

join on은 기존의 Natural Join과 큰 차이점이 하나 있는데, 공통 Attribute를 통합하지 않는다는 것이다. 위 예제에서 join on을 사용했더니 튜플들은 Natural join의 결과와 동일하지만 course_id라는 Attribute가 중복으로 존재하게 된다. 이런 차이가 존재하는 이유는 Outer join에 대한 고려 때문

단, using을 사용하여 통합시킬 수도 있다

🌀 View

🔸 View란?

지금까지 다루던 Relation은 DB 내에 실제로 저장되어 있는 것으로 가정하였다. 하지만 보안 상의 문제로 어떤 사용자에게 DB의 모든 Relation을 있는 그대로 공개하는 것은 바람직하지 않다. 또한, 사용자 목적에 더 잘 부합하는 형태로 Relation을 가공하여 제공하는 것이 효율적일 수 있다

view는 DB 내 실제 Relation을 기반으로 가공된 가상 Relation이다. 예로, 위 예제는 instructor라는 relation을 그대로 제공하지 않고 salary를 제외한 ID,name,dept_name으로만 이루어진 가상 relation을 제공하려는 시도이다

이렇게 만들어진 view는 DB에 테이블 형태가 아닌 Query 형태로 저장된다. 즉, Swift의 computed 프로퍼티처럼 view를 사용할 때마다 query를 수행해서 가상 relation을 만들어내어 사용하게 된다. 이런 방식으로 인해 원본 relation이 변경되더라도 가상 relation에 바로 반영할 수 있다

🔸 View 생성

create view ~ as ~로 생성한다. 위에서 언급했듯이 DB에는 결과 Relation이 아닌 결과를 만드는 query가 저장된다

예제 참고

view로부터 view를 생성할 수도 있다

🔸 View Relation 저장

몇몇 데이터베이스는 Relation을 저장하는 방법을 제공하기도 한다. 이를 Materialized View (실체화 뷰)라고 한다. 원본 Relation의 변경에 따라 실체화 뷰도 업데이트되도록 해야 한다

Materialized View Maintenance의 3가지 방법

  • 원본 Relation이 수정될 때마다 실체화 뷰 업데이트
  • 원본이 수정되더라도 그대로 두다가 사용자가 실체화 뷰에 접근할 때 업데이트
  • 원본 수정과 무관하게 정기적으로 업데이트

🌀 Transaction

Transaction이란 쉽게 말해 작업의 단위를 말하며, 여러 개의 쿼리와 업데이트문을 묶어 작성될 수 있다. 데이터 일관성 유지를 위해 함께 수행되어야 하는 쿼리들을 묶어준다

transaction으로 묶인 작업들은 atomic하여 모두 수행되거나 모두 수행되지 않음이 보장된다. 구체적으론, 모두 수행되면 commit하고 하나라도 수행되지 못하면 roll back하여 되돌립니다. 또한, transaction은 isolation의 성질은 가져동시에 수행되는 다른 transaction에 의해 영향을 받지 않아야 한다. 그 외에 일관성, 지속성의 성질을 가진다

일반적으로 작성하는 단일 SQL문은 암묵적으로 하나의 transaction으로 동작하며 완료 시 자동으로 auto-commit된다. 하지만 여러 SQL문을 하나의 transaction으로 묶으려는 경우엔 auto-commit을 해제하고 grouping 해주는게 필요하다

🌀 참조무결성 제약조건

🔸 Integrity Constraint이란?

Integrity(무결성)란, 데이터에 결함이 없는 상태, 데이터가 정확하고 유효하게 유지되는 상태를 뜻한다. Integrity Constraint는 데이터를 실수로 인한 손상으로부터 보호하여 무결성을 보장하기 위한 제약조건이다. 예로, 은행잔고는 양수여야 한다, 고객은 연락처 정보를 반드시 가져야 한다 등이 있다

DDL에 설정해준다

🔸 not null

기본적으로 null값이 허용되는데, 어떤 Attribute는 null인 것이 부적절한 경우가 있다. 이 경우 not null을 통해 null 값 삽입을 막는다

🔸 primary key

primary key는 기본키를 지정하는 것으로 기본키의 조건인 not null을 자동으로 보장시켜 주고 중복을 금지한다

🔸 unique

해당 Attribute 리스트가 candidate key를 구성함을 명시한다. 즉, Attribute 리스트의 모든 값이 일치하는 튜플의 중복을 금지한다. 아직 primary key가 아닌 candidate key이므로 null을 가질 수 있는 상태이다

(참고로, candidate key는 super key 중 minimal한 것. candidate key 중 하나를 primary key로 지정하게 된다)

🔸 check

custom 조건을 설정한다

🔸 Referential Integrity (참조무결성)

참조무결성이란 foreign key constraint라고도 하며, 두 relation 간 참조의 일관성을 보장해주는 것. dept_name을 공통으로 가지는 instructor와 department가 있고 department의 Foreign key는 dept_name이다. 이 때 instructor의 dept_name에 존재하는 모든 값은 department에 하나는 있어야 한다는 조건을 뜻한다

위는 Foreign key로 dept_name을 지정하는 예제로, references절을 사용하여 어떤 Relation을 참조하는지 명시할 수 있다

원래는 department 뒤에 어떤 Attribute를 연결할지 정해야 하는데 이번 예제는 Attribute의 이름이 같아서 (dept_name) 생략한듯 하다. 즉, Attribute의 이름이 같을 필요는 없다. 단, 두 Attribute의 도메인은 같아야 한다

cascading action이란게 있다. 제약조건을 위반하는 갱신/삭제를 완전히 거부하지 않고, 그 대신 참조를 사용 중인 튜플을 변경하는 것을 말한다

  • on delete cascade
    course와 department는 dept_name이라는 Foreign key로 연결된다. 여기서 원본인 department의 튜플을 삭제하면 원래는 이를 참조중인 course에 무결성 문제가 생겨 거부하지만, on delete cascade 처리를 하면 department의 삭제에 따라 course의 대응되는 튜플까지 삭제한다
  • on update cascade
    원래는 course의 튜플을 갱신하면 department가 원본이므로 갱신을 거부한다. 하지만 on update cascade를 사용하면 course 뿐만 아니라 department까지 연쇄적으로 갱신을 해준다
  • on delete set null / on delete set default
    이는 연쇄적인 삭제대신 null이나 도메인 기본값으로 설정하는 것이다. set null의 경우 대상 Attribute들이 전부 null을 허용해야만 한다

🌀 내장형 SQL 데이터 타입

  • date
    연,월,일 순으로 저장
  • time
    시,분,초를 저장하며 초는 소수점까지 가능
  • timestamp
    date와 time을 합친 것
  • interval
    시간의 간격을 말한다. interval은 date/time/timestamp에 더하거나 빼기 연산이 가능하다

🌀 Index

Index는 검색속도를 높이기 위한 방법 중 하나이다. Index를 위한 Attribute를 직접 하나 할당하거나 create index {index제목} on {Relation}({Attribute})로 생성할 수 있다

index를 이용하면 어떤 값을 찾고자 할 때 모든 튜플을 뒤져보지 않아도 되어 검색속도를 향상시킬 수 있다. 다만, 추가 메모리가 필요하고 유지보수 비용이 든다

🌀 대형객체

Large object (대형객체)는 사진, 비디오 등과 같은 대용량 객체 저장을 위한 타입이다

  • blob(binary large object)
    대용량의 해석되지 않은 바이너리 데이터 모음. 이후 외부 Application에 의해 해석된다
  • clob(character large object)
    대용량의 문자 데이터 모음. 대용량 문서 데이터 저장에 사용된다

대형객체를 저장하고 불러오려면 특별한 방법이 필요하다. query의 결과 튜플에 대형객체가 포함되어 있는 경우, 이를 한 번에 메모리에 올리는 것은 비효율적이므로 객체자체가 아닌 객체의 포인터를 반환한다. 이후 Application은 이 포인터를 사용하여 대형객체를 컨트롤한다

🌀 권한부여 (Authorization)

🔸 Authorization이란?

사용자가 DB의 어떤 데이터에 접근하여 SQL문을 실행시키려면 권한이 필요하다. 아래의 권한을 전부 가질수도, 일부만 가질수도 있다. 권한부여는 보통 Relation/View 단위로 설정한다

  • Read : 읽기만 가능
  • Insert : 신규 데이터 삽입만 가능. 수정/삭제 불가
  • Update : 수정만 가능. 삭제 불가
  • Delete : 삭제가능

데이터 뿐만 아니라, DB 스키마 수정에 대한 권한도 존재한다

  • Index : index 추가/삭제
  • Resources : relation 생성
  • Alteration : Attribute 추가/삭제
  • Drop : relation 삭제

🔸 권한부여

이러한 권한들은 grant문을 사용하여 부여해줄 수 있다. grant {권한목록} on {relation/view 이름} to {권한을받을유저목록}. 권한부여는 이미 권한을 가진 사용자 혹은 DB 관리자만 가능하다

유저목록에 public을 주면 모든 유저에게 허용함을 뜻한다

view에 대한 권한을 얻는다고 해서 원본 relation에 대한 권한까지 얻는 것은 아니다

🔸 SQL 표준 권한

  • select : relation을 읽거나, view를 만드는 query를 작성할 권한
  • insert : 튜플을 삽입할 권한. 일부 Attribute만 허용할 수도 있다
  • update : 튜플을 수정할 권한. 일부 Attribute만 허용할 수도 있다
    (ex. grant update ({Attribute}) on {Relation} to {user_id})
  • delete : 튜플을 수정할 권한. 일부 Attribute만 허용할 수도 있다
  • all privileges : 모든 권한. 사용자는 자신이 만든 relation에 대해선 all privilege를 자동으로 가진다

🔸 권한취소

revoke문을 사용하여 부여했던 권한을 취소할 수 있다

권한목록에 all을 주면 모든 권한을 취소한다

유저목록에 public을 주면 모든 사용자가 권한을 잃는다. 단, 직접 하나하나 부여한 권한은 public으로 취소되지 않는다

여러 사용자가 동일한 사용자에게 권한을 각각 부여한 경우, 하나를 취소하더라도 다른 하나가 남아 여전히 권한이 유지될 수 있다

어떤 권한이 취소되면 해당 권한에 의존하여 만들어진 다른 모든 권한도 함께 취소된다

권한은 다른 사용자에게 넘겨주는 것도 가능한데, 넘겨준 사용자가 해당 권한을 잃으면 넘겨받은 사용자도 같이 잃는다

🔸 Role

Role은 여러 사용자들에 대한 권한관리를 한번에 쉽게 하기 사용하는 권한집합의 개념

  1. instructor라는 role 생성
  2. 유저 Amit에게 instructor의 권한을 부여
  3. Relation takes의 select 권한을 instructor에게 부여
  4. 2번에서의 연결 덕에 유저 Amit이 takes의 select 권한을 받음

Role과 Role을 체인처럼 연결할 수도 있다

  1. teaching_assistant라는 role 생성
  2. teaching_assistant를 instructor에 연결
  3. 이후 teaching_assistant에 부여하는 권한은 자동으로 instructor도 갖게 된다

🌀 JDBC

지금까지 SQL문을 작성하여 DB에 접근했다면, SQL이 아닌 범용 프로그래밍 언어로 DB에 접근하는 방법에 대해 알아보자. SQL을 배제하는 것이 아닌 JDBC와 같은 인터페이스를 사용하는 것을 말하며, 인터페이스 중 가장 대표적인 JDBC를 중점적으로 다룬다

🔸 프로그래밍 언어와 DB를 연동

우리의 Application에서 DB 작업을 수행하려면 SQL만으로는 표현력이 부족하여 범용 프로그래밍 언어와 함께 사용해야 한다. SQL은 선언형 언어로 "무엇을" 찾을지를 기술할 뿐 "어떻게"에 대한 기술이 없다. 예로 보고서출력, 유저상호작용 등의 비선언형 동작처럼 SQL로는 표현할 수 없는 query가 존재한다

결국 더 표현력이 높은 언어에 SQL을 내장시키는 방향으로 가야 한다. 이런 구조를 위해 프로그래밍 언어와 SQL을 연동하는 방법이 필요하고 크게 2가지가 있다

Dynamic SQL
하나는 Dynamic SQL으로 이는 프로그래밍 언어로 관련 SQL API를 호출하는 식. SQL만을 위한 별도의 컴파일러가 필요하지 않다. 동작방식을 살펴보면, 런타임에 SQL 쿼리를 문자열로 생성하여 전달하고 그 결과를 한 번에 하나의 튜플씩 가져온다. 따라서 SQL구문을 런타임에 생성/해석하므로 SQL오류가 런타임에서야 발견된다. 대표적인 예로 JDBC와 ODBC가 있다

Embedded SQL
다른 하나는 Embedded SQL로 Application에 내장된 SQL문을 말합니다. dynamic은 "SQL문을 생성하는" 인터페이스가 제공되는 반면, embedded는 App에 "SQL문 자체를" 직접 포함시켜 다른 코드와 동시에 컴파일합니다. SQL문을 프로그래밍 언어로 대체하는 작업을 전처리하고 이후에 프로그래밍 언어 컴파일러로 전체 컴파일합니다

🔸 JDBC / ODBC의 역할

기본적으로 SQL의 기본 데이터 형태는 Relation인 반면, 프로그래밍 언어는 변수를 사용하여 작업한다. 따라서 SQL과 프로그래밍 언어를 함께 사용하려면 SQL구문이 결과로 반환하는 relation을 변수로 변환하는 작업이 필요하다. 여기서 JDBC/ODBC의 API를 사용하게 된다

JDBC/ODBC의 역할을 정리해보면

  • DB 서버와의 연결
  • DB 서버에 SQL 명령을 보낼 때
  • 쿼리 결과를 프로그래밍 언어 변수로 저장할 때

🔸 JDBC란?

JDBC는 Application과 DB 간의 연결을 위한 JAVA API이다. Oracle, mySQL 등과 같은 DBMS 종류에 상관없이 쉽게 SQL문을 작성/수행/결과처리하도록 설계되었다

🔸 기본 메서드

  • Class.forName();
    자바에서 해당 DB로 접근할 수 있게 해주는 JDBC 드라이버를 로드해야 한다

  • Connection conn = DriverManager.getConnection(url, user_id, password);
    DB 접속을 위해 connection을 open해야 한다. url은 "프로토콜 + DBMS가 있는 url 혹은 머신이름 + DBMS가 사용하는 포트번호 + 사용하려는 DB이름"으로 구성된다

  • Statement stmt = conn.createStatement();
    본격적으로 SQL문을 작성/수행하기 위해 statement 객체를 생성. 이후부터 이 statement 객체를 통해 insert/delete/update 등을 수행. create 대신 prepareStatement() 메서드를 사용할 수도 있다

  • stmt.close(); conn.close();
    시스템 자원 반환. 종료를 알리기 위함

  • stmt.executeUpdate()
    result set을 돌려받지 않고 SQL문 실행. 원하는 SQL문을 문자열로 전달

  • stmt.executeQuery()
    result set을 돌려받는 SQL문 실행

  • rset.next()
    result set은 커서라는 기능을 갖는데, 커서란 결과 relation의 첫번째 튜플 바로 앞부분을 가리키는 포인터를 말함. 따라서 next를 통해 다음 튜플로 옮겨가며 각 튜플을 가져올 수 있다

  • rset.getString()
    결과 relation의 값을 가져온다

🔸 PreparedStatement

createStatement() 대신 매개변수로 SQL문을 받는 prepareStatement()가 필요한 경우가 있다. 위 예제처럼 일부만 바뀌고 반복적으로 실행되는 경우 더 효율적이다. 그리고 사용자가 입력한 내용을 query에 추가하는 경우 항상 PreparedStatement를 사용해야 한다

prepared statement로 query를 작성할 때 문자열보간법을 사용하면 안된다. 실제 문자열의 따옴표와 SQL에서 예약어로 사용하는 따옴표간 구분이 안되는 등의 문제 여지가 있어, 반드시 set 메서드를 통해 값을 입력해야 한다

🔸 Metadata

DB의 메타데이터를 가져오는 방법. 쿼리의 결과가 ResultSet에 저장되는데 ResultSet 인터페이스 중 메타데이터를 반환하는 getMetaData() 메서드가 있다

🔸 Transaction Control

JDBC는 기본적으로 각 SQL문을 개별적인 transaction으로 취급하여 자동으로 commit한다. 여러 SQL문으로 이루어진 transaction으로 묶고 관리하기 위한 메서드가 제공된다

  • conn.setAutoCommit(false)
    여러 SQL문을 하나의 transaction으로 묶기 위해 자동 commit을 비활성화. 이러면 반드시 명시적으로 commit 혹은 roll back을 해주어야 한다
  • conn.commit() / conn.rollback()
    DB에 반영하거나 / 지금까지 수행된 것들을 되돌리거나
  • conn.setAutoCommit(true)
    다시 자동 commit을 활성화시켜준다

🔸 기타 JDBC 기능들

두가지 정도를 소개하려 한다

  • DB 시스템에 저장된 코드블럭/함수를 호출하는 것을 지원
  • 대형 객체를 다룰 수 있도록 지원
profile
J.Noma
노션으로 이사갑니다 https://tungsten-run-778.notion.site/Study-Archive-98e51c3793684d428070695d5722d1fe
이전 포스트

[CS] 데이터베이스 - 2. SQL

다음 포스트

[CS] 데이터베이스 - 4. E-R Model

0개의 댓글