Background
- SQL (Structural Query Language)
- tuple calculus로부터 유래
- 옛 이름은 SEQUEL(Structed English QUEry Language)
- IBM에서 실사용
- DBMS마다 SQL 지원 문법이 조금씩 다르다.
SQL DATA DEFINITION AND DATA TYPES
Schema and Catalog Concepts in SQL (Data Definition Language: DDL)
SQL schema
- 몇 시스템에서는 database라고 한다.
- 같은 DB에 있어야 하는 table + relationship을 의미
- schema name으로 식별되고, 누가 schema를 소유하고 있는지를 나타내는 (i) authorization identifier와 schema에 있는 각 element에 대한 (ii) descriptors를 포함
Schema elements
- Tables(formal하게는 relations), Constraints, Views, Domains(column의 data type) and other construct를 포함
CREATE SCHEMA
- schema를 생성하는 구문
- 모든 schema elements' definitions을 포함가능
- Example:
CREATE SCHEMA COMPANY AUTHORIZATION 'Joshua'
하지만 모든 사용자가 schemas & schema elements를 생성할 수 있는 권한이 있는 것은 아니다.
Catalog
: 명명된 schemas의 collection, 모든 schema의 정보 저장
- Integrity constraints는 relations가 동일한 catalog 내에 존재하는 경우에만 정의될 수 있다.
- 동일한 catalog 내에서, schemas는 특정 elements(type/domain definitions)를 공유할 수 있다.
- 다른 schema 참조 시에 catalog 사용
The CREATE TABLE Command in SQL
새로운 relation R을 정의
- R에 이름 부여
- R의 attributes, types(domains), and intial constraints(NOT NULL or UNIQUE)를 정의
Atfer attribute declaration
- key, entity integrity, and referential integrity constraints는 attribute declaration이후에 정의될 수 있다.
- 특정 referential의 경우, 참조할 다른 table의 선언 이후 선언해야 한다. 따라서
ALTER TABLE
을 사용하여 이후에 제약조건 추가 (혹은 선언 순서를 바꿔주기)
Base tables(base relations)
CREATE TABLE
- tables(relations)와 table의 rows(tuples)는 physically, actually하게 생성되고, DBMS에 의해 '파일'로서 저장된다.
Virtual relations(views)
CREATE VIEW
- 어떠한 물리적 파일과도 일치하지 할 수도, 하지 않을 수도 있다.
- view에 속한 관련 tuples는 물리적 구체화가 되지 않는 한 view가 참조될 때만 계산된다.
- Why needed?
- 디스크 낭비를 막기 위함 (하지만 계산 시간이 오래 걸리면 table화 하는 게 이득일 수 있음)
- 권한을 분리하기 위함
Recall the COMPANY Relational Schema
One Possible Database State
SQL CREATE TABLE Data Definition Statements for Defining a COMPANY Schema
Attribute Data Types and Domains in SQL (1/7)
Basic data types - Numeric data types
- Integer numbers of various sizes: INTEGER, INT and SMALLINT
- Oracle에서는 NUMBER 사용(DECIMAL을 대체가능)
- Floating-point (real) numbers: FLOAT, or REAL, and DOUBLE PRECISION
- DECIMAL(i,j) (or DEC(i,j) or NUMBERIC(i,j))로 정형화된 숫자 표현 가능
Attribute Data Types and Domains in SQL (2/7)
Basic data types - Character-string data types
- 고정 길이: CHAR(n), CHARACTER(n)
- n: 최대 문자 길이
- 왼쪽부터 문자를 채우고, 빈 공간은 공백
- 가변 길이: VARCHAR(n), CHAR VARYING(n), CHARACTER VARYING(n)
- n은 4K로 한정
- CLOB(CHARACTER LARGE OBJECT): "document"와 같이 매우 긴 문자열 값을 위함
- size: K, M, G
- Ex) CLOB(20M): 20MB까지
Attribute Data Types and Domains in SQL (3/7)
Basic data types - Bit-string data types (이진 코드)
- 고정 길이: BIT(n)
- n: 최대 bits 수, 4K로 한정
- 값 지정을 위해서, B`10101`와 같은 형식을 취해야 함
→ 일반 문자열과 구분짓기 위해 B로 bit임을 알려주고, ``안에 있는 값을 사용
- 가변 길이: BIT VARYING(n)
- BLOB(BINARY LARGE OBJECT): 실행가능한 코드나 java 객체와 같은 매우 큰 binary 값을 위함
- size: K, M, G
- Ex) BLOB(20M): 20MB까지
Attribute Data Types and Domains in SQL (4/7)
Basic data types - Boolean data type
Basic data types - Date data type
- 10자리
- Components: YEAR, MONTH, and DAY -> YYYY-MM-DD
- E.g., DATE '2014-09-27': Oracle에서는 to_date() 함수를 이용해서 전달받은 인자를 날짜형으로 변환
- Ex) TO_DATE('2021-10-17', 'yyyy-mm-dd')
Basic data types - Time data type
- 적어도 8자리
- Components: HOUR, MINUTE, and SECOND -> HH:MM:SS
- E.g., TIME '09:12:47'
Date & TIme data types에서, 유효한 days 와 times만 수용될 수 있다.
- '>', '<'로 date/time 비교 연산 가능
Attribute Data Types and Domains in SQL (5/7)
Basic data types - TimeStamp data type
- DATE & TIME fields를 포함
- seconds의 소수점 이하 자릿수에 대해 최소 6자리 추가
- WITH TIME ZONE option
- E.g., TIMESTAMP '2014-09-27 09:12:47.648302'
Basic data types - INTERVAL data type
- Date, time, or timestamp의 절댓값을 증가 또는 감소시키는데 사용되는 상대값을 지정
- time의 기간을 저장하기 위해 사용:
e.g., INTERVAL '40' MONTH = INTERVAL '3-4' YEAR TO MONTH
- YEAR TO MONTH: 연과 월의 기간을 저장하는 type
- DAY TO SECOND: 일과 시간, 분, 초를 저장하는 type
Date, Time, Timestamp, INTERVAL data types는 비교 연산을 위해 string formats으로 cast or convert될 수 있다.
Attribute Data Types and Domains in SQL (6/7)
Domain:
- 공통의 Domain을 지정 (Data type을 만드는 것과 유사?)
- column마다 data type을 지정하는 대신 선언될 수 있다.
- Name은 attribute specification과 함께 사용될 수 있다.
- 이점
1) domain에 대한 data type을 변경하는 것이 쉬워진다.
2) schema 가독성 향상
- Ex)
CREATE DOMAIN SSN_TYPE AS CHAR(9);
- CHAR, NUMBER와 같은 basic data types 대신 사용
- EMPLOYEE의 Super_ssn, DEPARTMENT의 Mgr_ssn, WORKS_ON의 Essn, DEPENDENT의 Essn과 같이 동일한 data type을 가지는 attribute에 사용할 수 있다.
Attribute Data Types and Domains in SQL (7/7)
Type
- 객체를 저장하는 용도
- UDTs(User Defined Types)를 생성하는데 사용
- 객체 기반 applications 지원
CREATE TYPE
구문으로 생성
SPECIFYING CONSTRAINTS IN SQL
Specifying Constraints in SQL
Basic constraints
Key constraints
: primary key의 값은 중복될 수 없다.
Entity integrity constraints
: primary key의 값은 NULL일 수 없다.
Referential integrity constraints
: foreign key는 참조하는 table에 존재하는 primary key의 값을 가져야 하고, 혹은 NULL 값을 가질 수도 있다.
- NULL값이 가능한 경우? EMPLOYEE의 Super_ssn이 NULL인 경우=boss
SQL에서 이를 기술하는 방법?
- PRIMARY KEY(Key constraints, +UNIQUE), NOT NULL(Entity integrity constraints), FOREIGN KEY(Referential integrity constraints) 키워드 사용
Specifying "Attribute" Constraints
Attribute의 기본값 설정
DEFAULT <value>
구문
- NULL
- 특정 attribute에 대해서는 적용되지 않는다. (primary key -> NOT NULL 사용)
CHECK
구문
Dnumber INT NOT NULL CHECK (Dnumber > 0 and Dnumber < 10)
Age INT NOT NULL CHECK (Age >= 15 and Age < 65)
- DBMS가 제약조건에 대한 충족여부를 확인 -> 충족해야만 data insert 가능
- schema-based constraint로 인해 지원
Specifying "Key" Constraints
PRIMARY KEY 구문
- relation의 primary key를 구성하는 하나 이상의 attributes를 지정
Dnumber INT PRIMARY KEY
PRIMARY KEY(State, Number)
UNIQUE 구문
- relational model에서, candidate keys로 불리는 alternate(secondary) keys를 지정 (중복되면 안되는 attribute)
Dnumber VARCHAR(15) UNIQUE
UNIQUE(State, Number)
Specifying "Referential Integrity" Constraints
FOREIGN KEY 구문
- Default operation: violation을 일으키는 update를 "reject"
- referential triggered action 구문 추가 (참조 트리거 액션 절)
- Options: SET NULL | CASCADE | SET DEFAULT
- SET DEFAULT는 oracle에서 지원하지 않는다.
- SET NULL or SET DEFAULT을 처리하는 DBMS의 과정은
ON DELETE & ON UPDATE에서 동일하다.
- CASCADE는 참조하는 tuples를 모두 삭제한다.
- 일관성을 유지하는 것이 중요한 relationship relations에 적절
Giving Names to Constraints
CONSTRAINT 키워드
- constraint에 이름 부여: 후에 altering시 유용
- Ex1)
CONSTRAINT reg_no_u UNIQUE(State, Number)
- Ex2)
dno CONSTRAINT fk_deptno REFERENCE Department(Dnumber)
Specifying Constraints on Tuples Using CHECK
- relation에 있는 개별 tuples에 대한 추가적인 constraint는
CHECK
를 사용하여 지정 가능하다.
CREATE TABLE
문 끝에 CHECK
- 각 tuple에 개별적으로 적용; row-based constraints
- tuple이 삽입 또는 수정될 때마다 check
Example of Specifying Constraints
BASIC RETRIEVAL QUERIES IN SQL
Basic SQL Query Block
condition -> column 이름 operation value
ORDER BY -> DESC: 내림차순, ASC: 오름차순 (default)
Basic Retrieval Queries in SQL
SQL은 how가 아닌 what을 요구하는 non-procedural, declarative language (비절차적, 선언적 언어)
SELECT statement
: database로부터 정보 검색 시 사용하는 basic statement
(relational algebra(procedual language)의 "selection" operation과 동일하지 않음)
Practical model(or, SQL)과 Relational model(or, relational calculus/algebra) 사이의 중요한 구별
- SQL은 query 결과로 table이 모든 attribute 값에서 동일한 두 개 이상의 tuple을 가지는 것을 허용한다. (결국 중복을 허용한다는 의미)
- tuple들의 multiset (a.k.a resultset) (bag이라고도 부른다)
- query 결과에서 unique하게 row를 식별하기 위해 row ID(artificial identifier)가 사용될 수 있다.
The SELECT-FROM-WHERE Structure of Basic SQL Queries
SELECT statement의 기본 형태
SELECT <attribute list>
FROM <table list>
[WHERE <condition>]
-
SELECT 절:
- <attribute list>: query를 통해 탐색하고자 하는 values를 가지고 있는 attribute names의 list
-
FROM 절:
- <table list>: query를 처리하는데 필요한 table(relation) names의 list
-
WHERE 절:
- condition을 충족한 tuple들만 result에 추가
- <condition>: query를 통해 탐색하려는 rows(tuples)를 식별하는 조건식
-
Logical comparison operators: =,<,≤,>,≥,<>
-
Projection attributes
- 탐색된 값들을 가지고 있는 attributes
-
Selection condition -> 'WHERE'
- 어떠한 탐색된 tuple은 항상 TRUE여야 하는 Boolean 조건문
- 여러 relations이 포함될 때는 "join" condition을 포함
Basic Retrieval Quries - Simple (No Join)
- Q0. Retrieve the birth date and address of the EMPLOYEE(s) whose name is ‘John B. Smith’
- Q0은 WHERE절의 조건을 만족하는 FROM절의 EMPLOYEE의 개별 tuples을 선택하고, SELECT절에 있는 Bdate, Address attributes를 project한다.
- Result:
Basic Retrieval Queries - 2-way Join
2개의 table이 join에 참여
- Q1. Retrieve the name and address of all EMPLOYEEs who work for the ‘Research’ DEPARTMENT.
- selection condition: 어떤 table을 선택할 것인지 명시
- join condition: 2개 이상의 table을 join
위 예시에서는 Dnumber(DEPARTMENT의 PK), Dno(EMPLOYEE의 FK)
- Select-Project-Join(SPJ) query
- Result:
Basic Retrieval Queries - 3-way Join
- Q2. For every project located in ‘Stafford’, list
- the project number,
- the controlling department number, and
- the department manager’s last name, address, and birth date.
- Dnum(PROJECT's FK), Dnumber(DEPARTMENT's PK) => 첫 번째 join
- Mgr_ssn(DEPARTMENT's FK), Ssn(EMPLOYEE's PK) => 두 번째 join
Ambiguous Attribute Names
- 2개 또는 그 이상의 attribute에 대해, 동일한 이름이 사용될 수도 있다.
- 서로 다른 tables에 있는 경우에도 마찬가지
- 어떠한 문제가 발생할 수 있는가? Join하여 query 작성 시 어떤 attribute인지 구별이 어려움
- 이러한 모호성을 없애기 위해, table name과 함께 fully-qualified attribute name을 사용해야 한다.
- 아래의 경우를 생각해보자.
- In EMPLOYEE: Dno -> Dnumber | Lname -> Name
- In DEPARTMENT: Dname -> Name
=> Join 시에 구분이 어려움
- 모호성 제거를 위해 아래와 같이 기술
- Table name을 붙임으로써 모호성을 제거되었지만, Query를 작성하는 성능의 저하를 초래 => "Aliasing" 필요
Aliasing, and Renaming
모호성 제거를 위해 별칭을 사용
Unspecified WHERE clause in a Query
WHERE 절이 없는 경우
- tuple selection에서 아무 조건 없이 적용함을 의미
- FROM 절에서 참조된 table의 Cartesian product의 결과
- Outcome: 모든 가능한 tuple combinations
Use of the Asterisk(*)
- “Retrieve all the attribute values of the selected tuples.”
- * 는 relation name이나 alias의 접두사로 사용할 수 있다.
Table as Sets(a Multiset) in SQL (1/4)
SQL은 자동적으로 query results에 있는 중복 tuples를 제거하지 않는다.
- 중복제거의 비용이 크기 때문
- Formal한 SQL에서는 중복을 제거
Table as Sets(a Multiset) in SQL (2/4)
- 왜 중복된 tuple을 제거하지 않는가 ?
1) 중복 제거는 비용이 크다. tuple들을 먼저 정렬하고, 그 이후에 중복을 제거
→ tuple의 양이 많은 경우, 정렬 + 제거의 비용이 증가
2) query의 결과에 있는 중복된 tuples을 사용자가 원하는 경우가 존재할 수 있다.
3) aggregate function(집계 함수: sum, avg, max, distinct, ...)이 tuples에 적용될 때, 대부분의 경우에 중복 제거를 원치 않는다.
- 중복 제거를 원하는 경우,
DISTINCT
키워드를 사용
Table as Sets(a Multiset) in SQL (3/4)
Set operations
: UNION(set union), EXCEPT(set difference), INTERSECT(set intersection)
- multiset operations(중복 다중 집합): UNION ALL, EXCEPT ALL(or MINUS), and INTERSECT ALL
- 이러한 set operations가 유효하기 위해서, resultset tables가 서로 Type-compatible해야 한다.
1) 두 relations가 동일한 attributes(names)를 가지고 있어야 하고, (AS 키워드로 rename이 필요할 수도 있다.)
2) 두 relations에서 attributes가 같은 순서로 나타나야 한다.
Table as Sets(a Multiset) in SQL (4/4): A Query Example
- “Make a list of all project numbers for projects that involve an employee whose last name is ‘Smith’, either as a worker or as a manager of the department that controls the project."
Several More Features of SQL: Substring Pattern Matching
부분 문자열 패턴 매칭
LIKE
비교 연산자: 문자열 패턴 매칭을 위해 사용
- 부분 문자열은 2개의 문자로 명시될 수 있다:
1) '%'는 0개 이상의 문자를 대체
E.g., “Retrieve all employees whose address is in Houston, Texas."
2) '_'는 1개의 문자를 대체
E.g., “Retrieve all male employees that were born during 1990s.”
- attribute value가 '%'나 '_'를 포함하고 있는 경우 '\'문자 사용
Several More Features of SQL: Arithmetic Operations
산술 연산
- 기본 arithmetic operators: +, -, *, /
- “Show the resulting salaries if every employee working on the ‘ProductX’ project is given a 10 percent raise.”
- BETWEEN 비교 연산자:
Ordering of Query Results
질의 결과 정렬
ORDER BY
구문 사용
- DESC: 내림차순, ASC: 오름차순
- 개별적 정렬방식 지정 가능
Summary: Basic SQL Query Block
INSERT, DELETE, AND UPDATE STATEMENTS IN SQL
Commands for Database Modification
INSERT
relation에 tuple을 insert
UPDATE
조건을 만족하는 relation에 있는 tuples를 update (DELETE+INSERT로 구현 가능)
DELETE
조건을 만족하는 relation에 있는 tuples를 update하는 것과 유사
The INSERT Command
CREATE TABLE
command로 생성한 attributes의 순서와 동일하게 insert해야 함
- data types에 대한 제약조건은 자동적으로 관찰되어, 위반하는 경우 insertion을 reject
- NULL값을 포함한 모든 값들이 attributes 순서에 맞게 제공되어야 한다.
- 혹은 아래와 같이, 원하는 attributes에 대한 값만을 insert할 수도 있다.
- 명시되지 않은 attributes에 대해서는
DEFAULT
or NULL
로 set
SELECT
의 결과를 아래와 같이 INSERT
할 수도 있다.
- table이 없는 경우 아래와 같이 생성하고
SELECT
의 결과를 적재할 수도 있다.
- 많은 tuples를 "bulk-loading"(대용량 적재)하는 경우에도 사용될 수 있다.
- 새로운 table(say, D5EMPS)는 이미 존재하는 table(say, EMPLOYEE)과 동일한 attributes를 가진다. 이때,
LIKE
& WITH DATA
를 이용해서 생성할 수 있다.
- Table 생성과 적재를 동시에 수행
- EMPLOYEE table을 그대로 이용하고, query의 결과를 data로 사용하여
INSERT
The DELETE Command
relation에서 tuples 삭제
- 삭제할 tuples를 선택하기 위해 WHERE 절 포함
- WHERE 절을 만족하는 tuple의 수에 따라 삭제되는 tuple 수가 결정됨
- tuples는 한 번에 한 table에서만 삭제가 가능
- Referential integrity만 위반될 가능성이 존재
- 만약 CASCADE가 referential integrity constraint에 의해 명시되었다면, 참조하는 모든 tuple들 또한 삭제된다.
- WHERE 절이 없는 경우
- table의 모든 tuples가 삭제되고 텅 빈 table만 남게 된다.
- table을 제거하는 방법은 ?
- Ex)
The UPDATE Command
- 하나 이상의 선택된 tuples의 attribute value를 수정하기 위해 사용
- 각 command는 동일한 relation에 있는 tuples를 수정
- WHERE 절로 수정할 tuples를 선택
- 추가적으로 붙는 SET-절은 UPDATE할 값을 지정하는 역할
- referential triggered action이 DDL의 referential integrity constraint에서 지정된 경우, PK 값을 update하는 것은 다른 tables에 있는 tuples의 FK 값에도 전파될 수 있다.
- data의 일관성을 유지하기 위함
- Ex) Change the location and controlling department number of project number 10 to ‘Bellaire’ and 5, respectively.
P.Dnum = 5
-> 참조하는 다른 tuple의 attribute 값도 변경해야 함.