DB_관계대수와 SQL

CamScanner 12-04-2022 20.56.pdf

2.91MB

< 관계대수와 SQL > 

1. SQL 개요

1) SQL의 중요성

: 사용자는 SQL을 사용하여 관계 데이터베이스에 릴레이션을 정의하고, 여러가지 무결성 제약조건들을 명세하고,

관계 데이터베이스를 갱신하며, 관계 데이터베이스에서 필요정보를 검색할 수 있음

 

2) SQL 개요

- SQL은 IBM 연구소에서 1974년 System R이라는 관계 DBMS 시제품을 연구할 때

관계 대수, 관계 해석을 기반으로 집단함수, 그룹화, 갱신 연산 등을 추가하여 개발된 언어

- 다양한 상용관계 DBMS마다 지원하는 SQL 기능에 다소 차이가 있음.

- SQL : 비절차적 언어(선언적 언어)이므로 사용자는 자신이 원하는(What)을 명시하며,

원하는 것을 처리하는 방법(How)은 기본적으로 명시하지 않음

- 관계 DBMS는 사용자가 입력한 SQL문을 번역하여, 사용자가 요구한 데이터를 찾는데 필요한 모든 과정을 담당

- 내포된 SQL(embedded SQL)

 

3)  SQL 구성요소

- 데이터 정의어 DDL [VIEW(뷰를 생성), INDEX(인덱스를 생성)]

(CREATE, ALTER, DROP) - RESTRICT, CASCADE

- 데이터 조작어 DML

(SELECT, INSERT, DELETE, UPDATE)

- 데이터 제어어 DCL

 

2. 데이터 정의어와 무결성 제약조건

1) 도메인 생성

CREATE DOMAIN DEPTNAME CHAR(10) DEFAULT '개발';

2) 릴레이션 제거

DROP TABLE DEPARTMENT;

3) ALTER TABLE

ALTER TABLE EMPLOYEE ADD PHONE CHAR(13);

4) 인덱스 생성

CREATE INDEX EMPNO_IDX ON EMPLOYEE(DNO);

5) 제약조건

CHECK (MANAGER_SALARY > SALARY);

6) 참조 무결성 제약조건 유지

ON DELETE NO ACTION

ON DELETE CASCADE

ON DELETE SET NULL

ON DELETE SET DEFAULT

7) 무결성 제약조건의 추가 및 삭제

ALTER TABLE STUDENT ADD/DROP CONSTRAINT STUDENT_PK

         PRIMARY KEY (STNO)

 

3. INSERT, DELETE, UPDATE문

1) INSERT문

- 참조되는 릴레이션에 투플이 삽입되는 경우에는 참조 무결성 제약조건의 위배가 발생하지 않으나

참조하는 릴레이션에 투플이 삽입되는 경우에는 참조 무결성 제약조건을 위배할 수 있음

- 릴레이션에 한번에 여러개의 투플들을 삽입하는 INSERT문

INSERT

INTO 릴레이션(애튜리뷰트1, 애트리뷰트 2)

SELECT ... FROM ... WHERE ...;

2) DELETE문

- 참조되는 릴레이션의 삭제 연산의 결과로 참조 무결성 제약조건이 위배될 수 있으나,

참조하는 릴레이션에서 투플을 삭제하면 참조 무결성 제약조건을 위배하지 않음

3) UPDATE문

- 기본키나 외래키에 속하는 애트리뷰트의 값이 수정되면, 참조무결성 제약조건을 위배할 수 있음

 

4. 관계대수

1) 관계해석 : 원하는 데이터(WHAT)만 명시하고 질의를 어떻게 수행할 것인가는 명시하지 않는 선언적인 언어

2) 관계대수 : 어떻게 질의(HOW)를 수행할 것인가를 기술하는 절차적 언어

- 관계 대수는 SQL을 구현하고 최적화하기 위해 DBMS의 내부 언어로 사용됨

- 기존의 릴레이션들로부터 새로운 릴레이션을 생성함

- 기본적인 연산자들의 집합으로 이루어짐

<관계대수> 예시

EMPLOYEE (EMPNO, EMPNAME, TITLE, MANAGER, SALARY, DNO)

DEPARTMENT (DEPTNO, DEPTNAME, FLOOR)

* π (프로젝션) : SELECT 역할 (구하는 것)

* σ (실렉션) : WHERE 역할 (조건)

* ⋈(조인) : 연결고리

* (EMPLOYEE) OR (DEPARTMENT) : 맨끝 (테이블)

 

질의 1) 2번 부서나 3번 부서에 근무하는 모든 사원들의 이름과 급여를 계산하라

π [EMPNAME, SALARY] (σ DNO = 2 OR DNO = 3 (EMPLOYEE)) 

질의 2) 개발 부서에서 근무하는 모든 사원들의 이름을 검색하라

π [EMPNAME] (EMPLOYEE ⋈ DNO = DEPTNO (σ DEPTNAME = "개발" (DEPARTMENT)))

Q. 아래와 같은 관계데이터베이스 스키마를 보고 각 질의를 관계대수식으로 표현하라.
CUSTOMER(CUSTOMER_ID, NAME, ADDRESS, PHONE)
VIDEO(VIDEO_ID, TITLE, GENRE)
RESERVED(CUSTOMER_ID, VIDEO_ID, DATE)

a. 제목이 '반지의제왕'인 비디오테이프의 장르를 검색하라.

π [GENRE](σ TITLE='반지의제왕')(VIDEO)

b. 예약된 비디오테이프의 ID를 검색하라.

π [VIDEO_ID](RESERVED)

c. 예약되지 않은 비디오테이프의 ID를 검색하라.

π[VIDEO_ID](VIDEO) - π[VIDEO_ID](RESERVED)

d. 예약된 모든 비디오테이프의 제목을 검색하라.

π [TITLE](VIDEO (* VIDOEID=VIDEO_ID) RESERVED)

e. 비디오테이프를 예약한 고객의 이름을 검색하라.

π[NAME](CUSTOMER * RESERVED)

f. 장르가 ‘액션＇인 비디오테이프를 예약한 고객의 주소를 검색하라.

π[ADDRESS](CUSTOMER (RESERVED (σ GENRE='액션'(VIDEO)))

g. 장르가 ‘다큐멘터리’인 비디오테이프를 모두 예약한 고객의 이름을 검색하라.

π[NAME](CUSTOMER * (π[CUSOMER_ID,VIDEO_ID](RESERVED) / π[VIDEO_ID](σ GENRE='다큐멘터리'(VIDEO))))