Reference
- 내용전반: edwith
✅ Remind
- unique만으로는 not null 기능이 없다
- Referential Integrity을 연결하는 두 Attribute는 이름은 달라도 되지만 도메인은 같아야 한다
Join 연산은 기본적으로 두 개의 Relation을 결합하여 하나의 Relation을 결과로 도출한다. SQL은 기존에 배운 Natural Join 외에도 다양한 형태의 join 연산을 제공한다. Join의 조건을 지정하거나 Natural Join에서는 제거되었던 튜플들을 포함하는 연산 등이 있다
참고로, 기존의 Natural Join에서는 이름이 같은 Attribute가 전부 통합 대상이었지만 Join Condition의 using
을 사용하면 통합 대상을 지정해줄 수 있습니다. on
에 대해선 아래에서 다룹니다
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
을 사용하면 Join의 결과 relation 튜플들에 조건을 설정할 수 있다. where절처럼 사용된다. 위의 두 예제는 동일한 결과를 가진다
join on
은 기존의 Natural Join과 큰 차이점이 하나 있는데, 공통 Attribute를 통합하지 않는다는 것이다. 위 예제에서 join on을 사용했더니 튜플들은 Natural join의 결과와 동일하지만 course_id라는 Attribute가 중복으로 존재하게 된다. 이런 차이가 존재하는 이유는 Outer join에 대한 고려 때문
단, using
을 사용하여 통합시킬 수도 있다
지금까지 다루던 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에 바로 반영할 수 있다
create view ~ as ~
로 생성한다. 위에서 언급했듯이 DB에는 결과 Relation이 아닌 결과를 만드는 query가 저장된다
예제 참고
view로부터 view를 생성할 수도 있다
몇몇 데이터베이스는 Relation을 저장하는 방법을 제공하기도 한다. 이를 Materialized View (실체화 뷰)
라고 한다. 원본 Relation의 변경에 따라 실체화 뷰도 업데이트되도록 해야 한다
Materialized View Maintenance의 3가지 방법
Transaction
이란 쉽게 말해 작업의 단위를 말하며, 여러 개의 쿼리와 업데이트문을 묶어 작성될 수 있다. 데이터 일관성 유지를 위해 함께 수행되어야 하는 쿼리들을 묶어준다
transaction으로 묶인 작업들은 atomic하여 모두 수행되거나 모두 수행되지 않음이 보장된다. 구체적으론, 모두 수행되면 commit
하고 하나라도 수행되지 못하면 roll back
하여 되돌립니다. 또한, transaction은 isolation의 성질은 가져동시에 수행되는 다른 transaction에 의해 영향을 받지 않아야 한다. 그 외에 일관성, 지속성의 성질을 가진다
일반적으로 작성하는 단일 SQL문은 암묵적으로 하나의 transaction으로 동작하며 완료 시 자동으로 auto-commit된다. 하지만 여러 SQL문을 하나의 transaction으로 묶으려는 경우엔 auto-commit을 해제하고 grouping 해주는게 필요하다
Integrity(무결성)란, 데이터에 결함이 없는 상태, 데이터가 정확하고 유효하게 유지되는 상태를 뜻한다. Integrity Constraint
는 데이터를 실수로 인한 손상으로부터 보호하여 무결성을 보장하기 위한 제약조건이다. 예로, 은행잔고는 양수여야 한다, 고객은 연락처 정보를 반드시 가져야 한다 등이 있다
DDL에 설정해준다
기본적으로 null값이 허용되는데, 어떤 Attribute는 null인 것이 부적절한 경우가 있다. 이 경우 not null
을 통해 null 값 삽입을 막는다
primary key
는 기본키를 지정하는 것으로 기본키의 조건인 not null을 자동으로 보장시켜 주고 중복을 금지한다
해당 Attribute 리스트가 candidate key를 구성함을 명시한다. 즉, Attribute 리스트의 모든 값이 일치하는 튜플의 중복을 금지한다. 아직 primary key가 아닌 candidate key이므로 null을 가질 수 있는 상태이다
(참고로, candidate key는 super key 중 minimal한 것. candidate key 중 하나를 primary key로 지정하게 된다)
custom 조건을 설정한다
참조무결성
이란 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 set null
/ on delete set default
Index
는 검색속도를 높이기 위한 방법 중 하나이다. Index를 위한 Attribute를 직접 하나 할당하거나 create index {index제목} on {Relation}({Attribute})
로 생성할 수 있다
index를 이용하면 어떤 값을 찾고자 할 때 모든 튜플을 뒤져보지 않아도 되어 검색속도를 향상시킬 수 있다. 다만, 추가 메모리가 필요하고 유지보수 비용이 든다
Large object (대형객체)
는 사진, 비디오 등과 같은 대용량 객체 저장을 위한 타입이다
대형객체를 저장하고 불러오려면 특별한 방법이 필요하다. query의 결과 튜플에 대형객체가 포함되어 있는 경우, 이를 한 번에 메모리에 올리는 것은 비효율적이므로 객체자체가 아닌 객체의 포인터를 반환한다. 이후 Application은 이 포인터를 사용하여 대형객체를 컨트롤한다
사용자가 DB의 어떤 데이터에 접근하여 SQL문을 실행시키려면 권한이 필요하다. 아래의 권한을 전부 가질수도, 일부만 가질수도 있다. 권한부여는 보통 Relation/View 단위로 설정한다
데이터 뿐만 아니라, DB 스키마 수정에 대한 권한도 존재한다
이러한 권한들은 grant
문을 사용하여 부여해줄 수 있다. grant {권한목록} on {relation/view 이름} to {권한을받을유저목록}
. 권한부여는 이미 권한을 가진 사용자 혹은 DB 관리자만 가능하다
유저목록에 public
을 주면 모든 유저에게 허용함을 뜻한다
view에 대한 권한을 얻는다고 해서 원본 relation에 대한 권한까지 얻는 것은 아니다
grant update ({Attribute}) on {Relation} to {user_id}
)revoke
문을 사용하여 부여했던 권한을 취소할 수 있다
권한목록에 all
을 주면 모든 권한을 취소한다
유저목록에 public
을 주면 모든 사용자가 권한을 잃는다. 단, 직접 하나하나 부여한 권한은 public으로 취소되지 않는다
여러 사용자가 동일한 사용자에게 권한을 각각 부여한 경우, 하나를 취소하더라도 다른 하나가 남아 여전히 권한이 유지될 수 있다
어떤 권한이 취소되면 해당 권한에 의존하여 만들어진 다른 모든 권한도 함께 취소된다
권한은 다른 사용자에게 넘겨주는 것도 가능한데, 넘겨준 사용자가 해당 권한을 잃으면 넘겨받은 사용자도 같이 잃는다
Role
은 여러 사용자들에 대한 권한관리를 한번에 쉽게 하기 사용하는 권한집합의 개념
Role과 Role을 체인처럼 연결할 수도 있다
지금까지 SQL문을 작성하여 DB에 접근했다면, SQL이 아닌 범용 프로그래밍 언어로 DB에 접근하는 방법에 대해 알아보자. SQL을 배제하는 것이 아닌 JDBC와 같은 인터페이스를 사용하는 것을 말하며, 인터페이스 중 가장 대표적인 JDBC를 중점적으로 다룬다
우리의 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문을 프로그래밍 언어로 대체하는 작업을 전처리하고 이후에 프로그래밍 언어 컴파일러로 전체 컴파일합니다
기본적으로 SQL의 기본 데이터 형태는 Relation
인 반면, 프로그래밍 언어는 변수
를 사용하여 작업한다. 따라서 SQL과 프로그래밍 언어를 함께 사용하려면 SQL구문이 결과로 반환하는 relation을 변수로 변환하는 작업이 필요하다. 여기서 JDBC/ODBC의 API를 사용하게 된다
JDBC/ODBC의 역할을 정리해보면
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의 값을 가져온다
createStatement()
대신 매개변수로 SQL문을 받는 prepareStatement()
가 필요한 경우가 있다. 위 예제처럼 일부만 바뀌고 반복적으로 실행되는 경우 더 효율적이다. 그리고 사용자가 입력한 내용을 query에 추가하는 경우 항상 PreparedStatement를 사용해야 한다
prepared statement로 query를 작성할 때 문자열보간법을 사용하면 안된다. 실제 문자열의 따옴표와 SQL에서 예약어로 사용하는 따옴표간 구분이 안되는 등의 문제 여지가 있어, 반드시 set 메서드를 통해 값을 입력해야 한다
DB의 메타데이터를 가져오는 방법. 쿼리의 결과가 ResultSet에 저장되는데 ResultSet 인터페이스 중 메타데이터를 반환하는 getMetaData()
메서드가 있다
JDBC는 기본적으로 각 SQL문을 개별적인 transaction으로 취급하여 자동으로 commit한다. 여러 SQL문으로 이루어진 transaction으로 묶고 관리하기 위한 메서드가 제공된다
conn.setAutoCommit(false)
conn.commit()
/ conn.rollback()
conn.setAutoCommit(true)
두가지 정도를 소개하려 한다