엘비

2차 저장 공간

업데이트:

태그: , ,

카테고리:

REF: fundamentals of database systems 7th edition

Intro

  • 관계형 DBMS끼리의 변환은 시간적-비용적 측면에서 훨씬 효율적이다.
    • 왜냐하면 동일한 언어 기준을 따르기때문이다.
  • 동일한 언어 기준을 따른다는 것은 2개이상의 관계형 DBMS 내에서
    • Database Sublanguage를 바꿀 필요 없이 데이터에 접근할 수 있다는 것을 의미한다.
  • 관계대수 연산은 관계형 DB에서 다양한 요청을 이해하는데에 필수적이나, 너무 low-level하다.
  • 반면, SQL은 higher-level 선언적 언어이므로, 사용자들은 어떤 결과를 원하는지만 정의하면된다.
  • SQL은 관계대수의 특성을 가지고있지만 tuple-relational-calculus에 더 깊은 관계를 가진다.

SQL

  • 1970년대에 IBM이 SYSTEM R로 개발
  • ANSI와 ISO가 첫번째 표준(SQL-86, SQL1)을 만들었다.
  • 다음 표준은 SQL-92, SQL2
  • 다음 표준은 SQL-99, SQL3로 불린다.

SQL Data Definition, Types

  • SQL은 table, row, column을 관계형 모델의 relation, tuple, attribute에 대응시킨다.

Schema, Catalog Concept

SQL Schema

  • SQL schema
    1. schema name으로 정의
    2. authorization identifier 스키마를 소유한 사용자
    3. descriptors 스키마의 각 원소(table, view, domain 등)
CREATE SCHEMA COMPANY AUTHORIZATION 'Jsmith';
COMPANY <- schema name
`Jsmith` <- authorization identifier


⭐️Catalog

  • Catalog : named collection of schemas

  • ⭐️INFORMATION_SCHEMA(special schema) : catalog의 모든 schema에 대한 정보를 제공하며 이 schema들의 원소 descriptors를 제공한다.
  • share integrity constraints : integrity constraints는 동일한 catalog내에 있는 스키마에 존재하는 relation사이에서만 정의된다.
  • share elements: 같은 catalog내의 스키마는 type, domain과 같은 원소를 공유할 수 있다.



CREATE TABLE

  • 새로운 relation을 정의한다.
  • relation 이름, attribute, constraints를 정의
    1. attribute : attribute name, domain, data type을 정의
    2. attribute정의 의후, key, entity integrity, referential integrity 등을 정의한다.
  • 일반적으로, relation이 생성된 SQL schema는 암시적으로 CREATE TABLE이 불러와진 위치의 환경에 정의되는데, CREATE TABLE로 TABLE로 TABLE을 생성할때, schema의 이름을 줄 수 있다.
CREATE TABLE COMPANY.EMPLOYEE
  • COMPANY schema의 EMPLOYEE table을 정의하겠다는 의미이다.

특징

  • 속성의 데이터 형을 정의할때는 create domain으로 가능하다.
    • CREATE DOMAIN SSN_TYPE AS CHAR(9)
  • not null constraint
    • null값이 들어오는 것을 허용하지 않겠다는 제약조건, primary key에 보통 적용된다.
  • default value
    • 설정하지 않을 경우 넣을 기본값을 의미.
  • base table
    • create table로 선언된 relation.
      • 물리적으로 존재하는 relation이다.
  • virtual table
    • create view로 선언된 relation.
      • 물리적으로 존재하지 않는다.



Data Types, Domains

  • numeric
    • int
    • real(float)
    • formatted number(fixed-point number)
  • character
    • CHAR(n)
    • VARCHAR(n)
  • bit-string data
    • BIT(n)
    • BIT VARYING(n)
  • date
    • YYYY-MM-DD
    • valid한 값만 인정
  • time
    • HH:MM:SS
    • valid한 값만 인정
  • timestamp
    • date + time + time zone qualifier
  • interval
    • date, time의 값 증감과 관련.



Specifying Constraints

Key Constraints

  1. Primary Key
    • relation의 PK를 정의한다.
  2. Unique
    • 대체키(후보키)를 정의한다.
  3. Foreign Key
    • Referential Integrity를 정의하는 FK를 정의한다..


Referential Integrity

SQL/92에서 Referential Integrity를 어기는 경우, 4가지의 옵션으로 행동을 정의할 수 있다.

  1. ⭐️NO ACTION(REJECT) <- 연산을 거부
  2. ⭐️CASCADE <- 삭제되는 튜플을 참조하는 모든 튜플을 제거
  3. ⭐️SET DEFAULT <- 해당 튜플을 참조하는 튜플의 값을 DEFAULT로
  4. ⭐️SET NULL <- 해당 튜플을 참조하는 튜플의 값을 NULL로
CREATE TABLE Enrolled
(
  sid CHAR(20),
  cid CHAR(20),
  grade CHAR(2),
  PRIMARY KEY(sid, cid), <- 복합키
  FOREIGN KEY(sid)
    REFERENCES Students
      ON DELETE CASCADE
      ON UPDATE SET DEFAULT
);



DROP, ALTER TABLE

DROP

  • DROP SCHEMA/DROP TABLE
    1. DROP SCHEMA COMPANY CASCADE
      • SCHEMA 내부의 모든 테이블, 도메인 등의 원소를 삭제한다.
    2. DROP SCHEMA COMPANY RESTRICT
      • SCHEMA 내부에 원소가 있다면 삭제를 거부
    3. DROP TABLE DEPENDENT CASCADE
      • TABLE을 참조하는 모든 테이블을 삭제
    4. DROP TABLE DEPENDENT RESTRICT
      • TABLE을 참조하는 테이블이 있으면 삭제 거부


ALTER

  • ALTER TABLE의 경우, 새로이 정의하는 테이블이 있고, 이 테이블의 특정한 attribute가 FK일때, 해당 FK가 참조하는 테이블이 아직 정의되지 않았을 경우 FK을 CREATE TABLE단계에서 바로 정의하지 않고, 참조하는 테이블이 정의된 이후, ALTER TABLE을 통해 FK 제약을 정의해줄 수 있다.
  1. add attribute
    • ALTER TABLE COMPANY.EMPLOYEE ADD JOB VARCHAR(12);
  2. drop an attribute
    • ALTER TABLE COMPANY.EMPLOYEE DROP JOB CASCADE;
  3. drop and add default value
    • ALTER TABLE COMPANY.DEPARTMENT ALTER MGRSSN DROP DEFAULT;
    • ALTER TABLE COMPANY.DEPARTMENT ALTER MGRSSN SET DEFAULT "31231";
  4. drop named constraint
    • ALTER TABLE COMPANY.EMPLOYEE DROP CONSTRAINT EMPSUPERFK CASCADE;



Basic Retrieval Queries

스크린샷 2022-10-10 오후 3 04 04

  1. FROM : table 식별
  2. WHERE : 표시할 tuple 선택
  3. SELECT : 표시할 attribute 선택
  4. GROUP BY : 특정 attribute로 GROUP BY
  5. HAVING : GROUP BY의 조건문
  6. DISTINCT : 중복 제거
  • 일반적으로 쿼리를 읽을때도 이 순서대로 읽어야 편하다.


쿼리 예제

스크린샷 2022-10-10 오후 3 46 37


SELECTION

  1. Research 부서 근무하는 직원의 주소와 이름
SELECT Fname, Lname, Address
FROM EMPLOYEE, DEPARTMENT
WHERE DNO = DNUMBER AND DNAME = "Research";


  1. Stafford에 위치한 모든 과제의 과제번호, 관리부서번호, 부서장 성, 주소, 생일 
    select pnumber, dnumber, fname, address bdate FROM 
 PROJECT, EMPLOYEE, DEPARTMENT
WHERE	DEPARTMENT.Mgr_ssn = EMPLOYEE.Ssn AND --join
     PROJECT.Dnum = DEPARTMENT.Dnumber AND --join
     PROJECT.Plocation = 'Stafford';


ALIASING

1-A. Research 부서 근무하는 직원의 주소와 이름

SELECT Fname, Lname, Address
FROM EMPLOYEE, DEPARTMENT
WHERE EMPLOYEE.DNO = DEPARTMENT.DNUMBER AND DEPARTMENT.DNAME = "Research";


  1. 각 직원의 성과 이름, 관리자의 성과 이름 
    SELECT E.Fname, E.Lname, S.Fname, S.Lname FROM
EMPLOYEE AS E, EMPLOYEE AS S
WHERE E.SUPERSSN = S.SSN;


Unspecified WHERE clause, *

  1. SSN만 표현
SELECT SSN
FROM EMPLOYEE;
  • SQL은 중복되는 값을 그대로 표현한다.
  • 관계대수에서 SQL의 selection에 대응되는 projection의 경우, relation으로 표현하므로, 중복을 표현하지 않는다.
  • SQL에서 중복값을 생략하려면, DISTINCT를 사용해야한다.


  1. 직원, 부서테이블의 직원번호와 부서이름 카테시안 곱으로 표현
SELECT SSN, DNAME
FROM EMPLOYEE, DEPARTMENT


  1. 부서번호 5인 직원의 모든 정보 표현
SELECT *
FROM EMPLOYEE
WHERE DNO = 5;


  1. 부서이름이 research인 직원과 부서의 모든 정보 출력
SELECt *
FROM EMPLOYEE, DEPARTMENT
WHERE EMPLOYEE.DNO = DEPARTMENT.DNUMBER
AND DNAME = 'Research';


  1. 부서와 직원 카테시안 곱
SELECT *
FROM EMPLOYEE, DEPARTMENT;


Table as SET

  • SQL은 기본적으로 중복된 튜플 표현(table로 표현한다.)
  • distinct를 사용해 중복없이 표현가능(relation으로 표현한다.)
  1. SALARY를 table로 표현해라
  • 중복표현
SELECT SALARY
FROM EMPLOYEE;


  1. SALARY를 relation으로 표현해라
  • distinct사용, 중복제거
SELECT DISTINCT SALARY
FROM EMPLOYEE;


  1. 성이 smith인 직원이 수행하는 과제번호나, smith 성을갖는 부장이 속한 부서에서 관리하는 과제번호
SELECT DISTINCT pno --smith직원이 수행하는 과제번호
	FROM WORKS_ON, EMPLOYEE
	WHERE EMPLOYEE.Ssn = WORKS_ON.Essn AND --각 직원이 담당하는 프로젝트
		EMPLOYEE.Lname = 'Smith' --smith직원 추출
UNION
SELECT DISTINCT Pnumber --smith 부서장이 담당하는 과제
	FROM DEPARTMENT, EMPLOYEE, PROJECT
	WHERE DEPARTMENT.Mgr_ssn = EMPLOYEE.Ssn AND --부서장 join
		EMPLOYEE.Lname = 'Smith' AND --smith 부서장 추출
		DEPARTMENT.Dnumber = PROJECT.Dnum --프로젝트 담당 부서


SET OPERATION

  • UNION, INTERSECT, EXCEPT
  • 위 3개 연산자는 집합연산자로써, UNION-COMPATIBLE해야한다.
    • relational algebra에서는 이 조건을
      1. attribute의 갯수가 같아야하고
      2. 대응되는 attribute의 domain이 모두 같아야한다고 표현했다.
  • 하지만, SQL에서는 아래와 같은 조건이 추가되어야한다.
    1. attribute의 순서가 같아야한다.
  • SQL이 기본적으로 table로 나타내어 중복이 모두 표현되나,
    • 집합연산자의 경우, 중복이 표현되지 않는 relation으로 표현된다.


Substring

  • % : multiple char
  • ‘_’ : single char
  1. Houston,TX에 사는 직원의 이름을 구해라
SELECT Fname, Lname
FROM EMPLOYEE
WHERE Address LIKE '%Houston,TX%';


Arithmetic

  1. ProductX에서 일하는 직원의 봉급을 10%인상했을때, 직원의 이름과 봉급을 출력해라
SELECT Fname, Lname, 1.1 * Salary
FROM EMPLOYEE, PROJECT, WORKS_ON
WHERE PROJECT.Pname = 'ProductX' AND
	PROJECT.PNUMBER = WORKS_ON.PNO AND
	WORKS_ON.ESSN = EMPLOYEE.SSN;


  1. 부서5에서 일하면서 봉급이 30000에서 40000사이인 직원 출력
SELECT * FROM EMPLOYEE
WHERE SALARY BETWEEN 30000 AND 40000 AND
DNO = 5;


ORDERING

  • ordering은 아무런 조건이 없는 경우 기본적으로 ASC가 설정된다.
  1. 부서, 직원, 진행과제, 프로젝트 테이블을 join시켜 부서명, 성, 이름 오름차순 정렬
SELECT Dname, Lname, Fname, pname
FROM EMPLOYEE, DEPARTMENT, WORKS_ON, PROJECT
WHERE EMPLOYEE.Dno = DEPARTMENT.Dnumber AND
EMPLOYEE.Ssn = WORKS_ON.Essn AND
WORKS_ON.Pno = PROJECT.Pnumber
ORDER BY Dname, Lname, Fname
--또는,
ORDER BY Dname DESC, Lname ASC, Fname ASC


Nested Query

정의

  • 사용
    • DB에 존재하는 값을 패치하고 비교에 사용해야하는 쿼리가 있다.
    • 이런 쿼리들은 Nested query로 표현될 수 있다.
  • 특징
    1. 모든 nested query는 sigle-blocked query로 치환할 수 있다. ->18, 20
      • nested query에서 비교대상 attribute를 sigle-blocked-query에서 join조건으로 주면된다.
    2. 다른 SQL쿼리 내에서 select-from-where절로 묶여있다.
    3. 이때, 외부 쿼리를 outer query, 내부 쿼리를 inner query로 부튼다.
    4. IN이라는 비교연산자를 사용하는데,
      • value v를 집합 V와 비교하고, 만약 v가 V에 속한다면, TRUE로 평가한다.
    5. 이때 집합 V는 v와 union-compatible해야한다.
      • v와 V의 각 튜플이 속성 수가 같아야하며, 도메인이 같아야하고, 순서도 같아야한다.


  1. 성이 smith인 직원이 수행하는 과제번호나, smith 성을갖는 부장이 속한 부서에서 관리하는 과제번호
  • co-related되어있지 않다.
SELECT DISTINCT Pnumber
	FROM PROJECT
	WHERE 
		Pnumber IN
		(SELECT Pno FROM WORKS_ON, EMPLOYEE
			WHERE EMPLOYEE.Ssn = WORKS_ON.Essn AND
			EMPLOYEE.Lname = 'Smith')
	OR
		Pnumber IN
		(SELECT P.Pnumber  FROM EMPLOYEE, DEPARTMENT, PROJECT P
			WHERE DEPARTMENT.Mgr_ssn = EMPLOYEE.Ssn AND
				PROJECT.Dnum = DEPARTMENT.Dnumber AND
				EMPLOYEE.Lname = 'Smith')
  • 위의 쿼리를 읽으면,
    1. FROM : PROJECT
    2. WHERE 내부 inner query평가
    3. PROJECT 의 Pnumber 각각을 평가된 inner query의 값 하나하나와 비교.
    4. DISTINCT로 복수 결과 삭제


  1. ssn이 123456789인 직원과 동일한 프로젝트에 동일한 시간을 투자한 직원을 찾아라
  • co-related되어있지 않다.
SELECT DISTINCT Essn
    FROM WORKS_ON w1
    WHERE (w1.Pno, w1.hours) IN
        (SELECT w2.Pno, w2.Hours
            FROM WORKS_ON w2 
            WHERE Essn = 123456789)


innermost nested query

- outer query와 nested query(sub, inner)가 사용하는 속성 이름이 같을때이다.
- `unqualified attribute`(테이블 이름 없이 속성이름만 사용)의 경우, `innermost nested query의 table을 참조`한다.


  1. 부양가족의 성과 성별이 같은 직원의 이름을 출력하라.
SELECT E.Fname, E.Lname
FROM EMPLOYEE E
WHERE E.Ssn IN
(
	SELECT D.ESsn
	FROM DEPENDENT
	WHERE Sex = E.Sex AND
		Dependent_name = E.Fname
)
  • 위 쿼리에서는 Sex의 속성명이 겹치므로, EMPLOYEE의 테이블 명을 aliasing해줘야한다.
  • Dependent의 속성은 innermost 규칙에따라 DEPENDENT 테이블을 참조한다.


ALL

  • ALL operator를 사용하면 평가된 innerquery의 결과 V의 모든 값들과 comparison value v 를 평가한다.
    • 모든 평가값이 TRUE여야한다.
  1. 부서5에 속한 모든 직원보다 봉급이 높은 직원의 이름을 출력해라.
SELECT Fname, Lname
FROM EMPLOYEE
WHERE EMPLOYEE.Salary > ALL
(
	SELECT E2.Salary 
	FROM EMPLOYEE E2
	WHERE E2.Dno = 5
)


  • 정의
    • nested query에서 중요한 것은 outer query와 inner query가 되어있는지 아닌지 이다.
    • co-related되어있지 않은 nested query는 inner query를 먼저 평가한 후에 outer query의 v가 V에 있는지 확인한다.
    • co-related된 nested query는 outer query의 각 튜플의 값을 inner query에서 한 번씩 비교해보게된다.


  • 특징
    1. table aliasing을 반드시 해주어야한다.관련 글
    2. outer query의 tuple이 각각 inner query의 모든 튜플과 한 번씩 평가되게되므로, 느리다.


  1. 부양가족의 성과 성별이 같은 직원의 이름을 출력하라.
SELECT Fname, Lname
FROM EMPLOYEE E, DEPENDENT D
WHERE E.Ssn = D.Essn AND
E.Sex = D.Sex AND
E.Fname = D.Dependent_name;
  • 위 쿼리의 경우에는 nested query가아닌, single blocked query를 사용해 표현한다.
  • Sex attribute를 innermost 규칙에따라 EMPLOYEE, DEPENDENT 모두에서 찾는데,
  • Dependent의table을 aliasing해주지 않으면 어느 테이블을 참조할지 애매해 오류가 발생한다.


EXIST

  • co-related 그리고 nested query가 비어있는지 안 비어있는지 확인한다.
  1. 가족과 동일한 이름과 성별을 갖는 직원의 이름
  • co-related되어있으므로, outer query의 employee tuple하나하나를 subquery에 넣어 비교한다.
  • 각 outer query의 tuple하나하나를 inner query로 평가하며 true인지 false인지 확인한다.
    • EXISTS -> NULL이 아니면, 즉 1개라도 존재하면 TRUE
    • NOT EXISTS -> NULL이면 TRUE이다.
SELECT Lname, Fname
FROM EMPLOYEE E
WHERE EXISTS
	(
		SELECT *
		FROM DEPENDENT
			WHERE E.Ssn = Essn AND
			E.Sex = Sex AND
			E.Fname = Dependent_name
	);


NOT EXISTS

  1. 가족이 없는 직원의 이름
SELECT Fname, Lname
FROM EMPLOYEE E
WHERE NOT EXISTS (
    SELECT * FROM DEPENDENT
    WHERE E.Ssn = Essn
);


  1. 적어도 1 가족이 있는 부장의 이름
SELECT Fname, Lname
FROM EMPLOYEE E
WHERE
EXISTS
(
	SELECT * FROM DEPARTMENT
	WHERE E.Ssn = Mgr_ssn
)
AND EXISTS
(
	SELECT * FROM DEPENDENT
	WHERE E.Ssn = Essn
)
  • 위 쿼리는 부서장 조건과 부양가족조건을 EXISTS로 평가한다.
  • Employee의 각 튜플에 대해 1번조건에서 부서장인지를 확인하고,
  • 2번조건에서 부양가족 테이블에서 부양가족의 가족직원번호를 확인하며
  • 둘 다 NULL이 아닐때만 이름을 출력한다.


  1. 부서 5에서 진행하는 과제를 모두 수행하는 직원의 이름을 출력해라
SELECT Fname, Lname 
FROM EMPLOYEE E
WHERE NOT EXISTS (
	(
		SELECT Pnumber 
		FROM PROJECT
		WHERE Dnum = 5)
	EXCEPT
	(
		SELECT pno 
		FROM WORKS_ON
		WHERE WORKS_ON.Essn = E.Ssn)
)
  • 부서5에서 진행하는 과제는 not co-related이므로, 한 번에 평가된다. {1,2,3}
  • 직원 테이블에서 tuple 1개를 선택하고, 해당 tuple의 ssn이 WORKS_ON에 있는지 없는지 확인한다.
  • 있으면 WORKS_ON의 pno를 가져와서 위의 {1,2,3} 집합에서 하나씩 삭제한다.
  • {1,2,3} 집합이 모두 삭제된다면, NOT EXISTS가 TRUE가되어 선택했던 EMPLOYEE의 이름이 출력된다.


  1. 2 level-nesting을 이용한 표현
SELECT Lname, Fname
FROM EMPLOYEE
WHERE NOT EXISTS
(
	SELECT * 
	FROM WORKS_ON B
	WHERE B.Pno IN (
		SELECT Pnumber FROM PROJECT
		WHERE Dnum = 5  --{1,2,3}
	) AND
		NOT EXISTS
		(
			SELECT *
			FROM WORKS_ON C
			WHERE C.Essn = Ssn --nested in outer most query
			AND C.Pno = B.Pno --nested in outer query
		)
);
  • 2 level nested query는 복잡하니까 하나씩 천천히 확인해보자.
    1. EMPLOYEE의 1번 tuple선택.
    2. PROJECT QUERY에서 {1,2,3} 결과얻음
    3. outer WORKS_ON 쿼리에서는 Pno가 1,2,3인 tuple 순서대로 평가.
    4. WORKS_ON query C 에서는 EMPLOYEE의 1번튜플과 outer WORKS_ON query에서 Pno로 C의 튜플 1개씩 평가.
    5. 1~4를 EMPLOYEE의 모든 튜플에 대해 수행.


Explicit set and Nulls

  1. 과제 1,2,3을 수행하는 직원의 SSN
SELECT DISTINCT ESSN
FROM WORKS_ON
WHERE PNO IN (1,2,3);


  1. 관리자가 없는 직원들의 이름
SELECT Lname, Fname
FROM EMPLOYEE 
WHERE Super_ssn IS NULL


Rename attr and Join table

  • Inner join : Default type의 join
  1. Research 부서에서 일하는 직원의 이름과 주소 출력
SELECT Fname, Lname, Address
FROM EMPLOYEE JOIN DEPARTMENT ON Dno = Dnumber
WHERE Dname = 'Research';


  • Natural join : join되는 테이블에 동일명으로 존재하는 속성으로 암시적인 Equijoin 조건이 생긴다.
    • resulting relation에서 equijoin된 attribute는 1개만 표현된다.
  1. natural join 사용
SELECT Lname, Fname
FROM EMPLOYEE NATURAL JOIN 
DEPARTMENT AS DEPT(Dname, Dno, Mgr_ssn, Mgr_start_date)
WHERE Dname = 'Research'


Aggregate functions, Grouping

  • count, sum, max, min, avg 등
  • SELECT clause나 Having Clause에 넣어서 표현한다.
  • 단, Group by로 묶는 순간, 집계함수를 사용하지 않은 속성만(Group by로 선택한 속성)만 일반적 속성값으로 나타낼 수 있다.
  1. 직원의 연봉합, 최고연봉, 최소연봉, 평균연봉을 구하라
SELECT SUM(salary), MAX(salary), MIN(salary), AVG(salary)
FROM EMPLOYEE;
  1. 2인이상의 가족을 가지는 직원의 이름을 구하라
SELECT Lname, Fname
FROM EMPLOYEE
WHERE (
    (SELECT Count(*) 
    FROM DEPENDENT
    WHERE Ssn = Essn) >= 2
)
  • 위 쿼리는 group by를 사용한 single blocked query에서 아래와같이 나타낼 수 있다.
SELECT Fname, Lname
FROM EMPLOYEE
WHERE Ssn IN (
	SELECT Ssn
	FROM EMPLOYEE, DEPENDENT
	WHERE EMPLOYEE.Ssn = DEPENDENT.Essn
	GROUP BY Ssn 
	HAVING count(*) >= 2
	)



Assertion

CHECK

  • 사용자는 선언적 Assertion을 통해 일반적인 제약을 정의할 수 있다.
  • CHECK 키워드 뒤에 조건이 오게되는데, 이 조건은 모든 데이터베이스 state가 만족해야한다.
  • WHERE 절도 사용할 수 있으나, 일반적으로는 EXISTS, NOT EXISTS를 사용한다.


DOMAIN과 차이점

  • 개별의 속성, 도메인 그리고 튜플의 제약조건을 정의할때도 CHECK절과 constraint 조건을 사용했는데,
  • CREATE ASSERTION과의 차이점은
    • 1 relation 내에서 개별 속성, 도메인, 튜플에 대한 CHECK절은 tuple이 삽입, 수정될때만 확인된다.
  • CREATE ASSERTION은 1개 relation 이상이 관련되는 제약에 사용한다.
    • 실세계의 제약조건을 더 잘 반영할 수 있게해준다.


예시

  • 직원의 월급은 소속 부서장의 월급보다 작아야한다.

스크린샷 2022-10-18 오후 1 07 05

  • 직원과 소속부서장 정보를 연결하기위해서는, 부서장 정보가 부서테이블에 있으므로,
    • 직원과 직원이 소속한 부서 연결 -> E.Dno = D.Dnumber
    • 부서장(직원테이블 M)과 부서테이블의 부서장 번호 연결 -> M.Ssn = D.Mgr_ssn

  • 이후, 직원과 부서장 월급으로 연결 E.Salary > M.Salary

  • 이렇게 선택된 테이블이 존재하지 않아야 조건을 만족한다.
CREATE ASSERTION SALARY_CONSTRAINT
CHECK (NOT EXISTS (
	SELECT * FROM EMPLOYEE E, EMPLOYEE M, DEPARTMENT D
	WHERE E.Dno = D.Dnumber AND -- 직원 부서번호가 부서번호가 같아야함
			D.Mgr_ssn = M.Ssn AND -- Manager만 M에서 가져오기
			E.Salary > M.Salary 
))



Trigger

정의

  • 특정 조건를 만족하거나, 이벤트가 발생할때 수행될 액션을 정의한다.
  • 데이터베이스를 모니터링할때 주로 사용된다.


예시

DEFINE TRIGGER <TRIGGER_NAME> ON <TABLE_NAME>
<CONDITION>
<ACTION_PROCEDURE> <PROCEDURE NAME>
  • 새로운 직원의 급여가 직원관리자의 급여보다 높을때 수행될 해당 정보를 관리자에게 알려라.
CREATE TRIGGER SALARY_VIOLATION
	BEFORE INSERT OR UPDATE OF SALARY, SUPERVISOR_SSN
		ON EMPLOYEE
	FOR EACH ROW
		WHEN ( NEW.SALARY > (
			SELECT SALARY FROM EMPLOYEE
			WHERE SSN = NEW.SUPERVISOR.SSN)
			)
		INFORM_SUPERVISOR(NEW.Supervisor_ssn, NEW.Ssn)



View

정의

  1. 다른 table이나 view로부터 유도됨
  2. DB에 물리적으로 저장되지 않는다.
  3. 자주 사용하는 정보에 사용
  4. 필요한 정보만 projection하므로 보안에 좋음.
  5. 최근 정보에 접근할 수 있다.


CREATE, DROP

CREATE

CREATE VIEW WORKS_ON1
AS SELECT Fname, Lname, Pname, Hours
FROM EMPLOYEE, PROJECT, WORKS_ON
WHERE Ssn = Essn AND Pno = Pnumber;

CREATE VIEW DEPT_INFO(Dept_name, No_of_emps, Total_sal)
AS SELECT D.Dname, COUNT(*), SUM(Salary)
FROM EMPLOYEE E, DEPARTMENT D
WHERE E.Dno = D.Dnumber
GROUP BY D.Dname


DROP

DROP VIEW WORKS_ON1;


VIEW UPDATE

  • VIEW에서 인지해야하는 중요한 것 중 하나.

  • UPDATABLE
    • 집계함수가 없는 단일 테이블이 관련된 VIEW
    • 칼럼 값을 업데이트할때, 어떤 테이블의 칼럼을 수정해야하는지 쉽게 알 수 있기때문.
  • NOT UPDATABLE
    • 집계함수가 있거나, 복수의 테이블이 관련되어있는데, 칼럼이름이 중복된 VIEW
    • 어떤 칼럼을 선택해 수정할 지 쉽게 선택할 수 있기때문.


VIEW IMPLEMENTATION

  1. query modification
    • VIEW의 query를 base table에 적용시켜 VIEW를 가져온다.
    • 시간이 오래걸리는 query는 매번 수행시간이 오래걸린다는 장점이있다. 그래서 materialization을 한다.
  2. view materialization
    • VIEW가 처음 질의되거나, 계속사용될 것 같을때, VIEW를 물리적으로 저장한다.
    • update되면 자동으로 업데이트한다.
      • 관련된 table의 값이 변경되면 materialization을 다시 수행해야한다.



Index

  • 결국에 DB의 정보는 모두 물리적 파일 형태로 존재한다.
  • 따라서, data가 어떻게 물리적으로 저장되어있느냐에따라 DB의 성능에서 차이가 생긴다.
  • 자료구조를 통해 빠르게 값을 찾을 수 있도록한다. = B tree, B+ tree, 등으로 구현

  • 인덱스에 대해선 표준 SQL이 없다.

  • Index에 대해서 제대로 알기위해선 물리적으로 실제 DB가 어떻게 저장되는지 알아야하므로, ch17. File Structure에 대해 공부하고 다시 정리.
CREATE [UNIQUE] INDEX <INDEX_NAME>
ON <TABLE_NAME> <TABLE_ATTR>;
[CLUSTER]
  • UNIQUE -> candidate key
  • clustering(정렬된) and unique(1개의 값만 있는) - primary key
  • clustering and not unique - inverted list - nonclustering - secondary index

댓글남기기

참고