1.현재 날짜를 표시하는 질의를 작성하고 열 레이블을 Data로지정하십시오.

 

SELECT sysdate as "DATA" FROM dual;

-현재 날짜는 dual의 sysdate에 있다.


2.각 사원에 대해 사원 번호,이름, 급여및15% 인상된급여를정수로표시하십시오. 인상된 급여열의 레이블을 New Salary로 지정하십시오.

 

SELECT employee_id, last_name, salary*1.15 as "New Salary" FROM employees;

-애트리뷰트들을 나열하는 부분에 곱하기와 같은 사칙연산자가 들어갈 수 있다.


3.이름이 J, A 또는 M으로 시작하는 모든 사원의 이름(첫 글자는 대문자로, 나머지 글자는 소문자로 표시) 및 이름 길이를표시하는 질의를 작성하고 각 열에 적합한 레이블을 지정하십시오. 결과를 사원의 이름에 따라 정렬하십시오.

 
SELECT INITCAP(last_name) as "LAST_NAME",length(last_name) as "LENGTH OF LAST NAME" FROM employees WHERE last_name like 'J%' or last_name like 'A%' or last_name like 'M%' order by last_name;

-INITCAP은 첫글자만 대문자, 나머지는 소문자로 표시해주는 함수이고, length는 길이를 표시해주는 함수이다.

 

4.각 사원의 이름을 표시하고 근무 달 수(입사일로부터 현재까지의 달 수)를 계산하여 열 레이블을 MONTHS_WORKED로 지정하십시오. 결과는 정수로 반올림하여 표시하고 근무 달 수를 기준으로 정렬하십시오

 

SELECT last_name, ROUND(MONTHS_BETWEEN(sysdate,hire_date)) as "MONTHS_WORKED" FROM employees ORDER BY MONTHS_WORKED;

-MONTHS_BETWEEN은 두 날짜 사이에 차이를 나타내주며 ROUND로 정수로 반올림이 가능하다.


5.각 사원에 대해 다음 항목을 생성하는 질의를 작성하십시오. earn monthly but wants <3 times salary> 열레이블을Dream Salaries로 지정하십시오 

 

SELECT last_name ||' earn ' || salary || ' monthly but wants '|| 3*salary as "Dream Salaries" FROM employees;

-특정한 문장으로 질의를 만들어야 하므로 이는 || 를 사용해서 이을 수 있다.

 

6.모든 사원의 이름과 급여를 표시하는 질의를 작성하십시오. 급여는 15자 길이로 왼쪽에 $기호가 채워진 형식으로 표기하고 열레이블을 SALARY로 지정하십시오. 

 

SELECT last_name,LPAD(salary,15,'$') as "SALARY" FROM employees;

-글자의 길이를 설정해주고 남은 글자들을 원하는 것으로 채워주는 함수가 LPAD이다. LPAD는 왼쪽에 빈 공간을 채우고 RPAD는 오른쪽에 빈 공간을 채워준다.


7.사원의 이름, 입사일 및 급여 검토일을 표시하십시오. 급여 검토일은 여섯달이 경과한 후 첫번째 월요일입니다. 열레이블을 REVIEW로 지정하고 날짜는 “Monday, the Thirty-First of July, 2000”과 같은 형식으로 표시되도록 지정하십시오.

 

SELECT last_name,hire_date, To_CHAR(NEXT_DAY(ADD_MONTHS(hire_date,6),'월요일'),'DAY,DD "of the" MONTH,YYYY') as "REVIEW" FROM employees;

-ADD_MONTHS를 통해서 6달이 지난 것을 알 수 있고, NEXT_DAY로 그 다음 첫번째 월요일을 얻을 수 있다. 또한 날짜를 특정 형식으로 만들어 줘야 하므로 이를 위해 TO_CHAR로 변환한다.

 

8. 이름, 입사일 및 업무 시작 요일을 표시하고 열레이블을 DAY로 지정하십시오. Monday를 시작으로 해서 요일을 기준으로 결과를 정렬하십시오. 

 

SELECT last_name,hire_date,To_CHAR(hire_date,'DY') as "DAY"  FROM employees ORDER BY TO_CHAR(hire_date-1,'D');

-윗 문제처럼 날짜에서 특정 시간만 가져오려 하면 TO_CHAR를 사용하도록 한다. 또한 요일을 정렬할 때 ORDER BY를 사용하는데 이 때 월요일을 기준으로 맞춰주려면 hire-date에서 하루를 빼야한다.

 

9.사원의 이름과 커미션을 표시하는 질의를 작성하십시오. 커미션을 받지 않는 사원일 경우 “No Commission”을 표시하십시오. 열레이블은 COMM으로 지정하십시오.

 

SELECT last_name,NVL(To_CHAR(commission_pct,'0.99'),'No Comission') as "COMM" FROM employees;

-커미션을 받지 않는 사원은 null일 것 이므로 NVL를 통해서 바꿔주도록 한다.


10.사원의 이름을 표시하고 급여 총액을 별표(*)로 나타내는 질의를 작성하십시오. 각 별표는 1,000달러를 나타냅니다. 급여를 기준으로 데이터를내림차순으로정렬하고열레이블을EMPLOYEES_AND_THEIR_SALARIES로지정하십시오.*/

 

SELECT last_name, salary, RPAD(' ',salary/1000+1,'*') as "EMPLOYEES_AND_THEIR_SALARIES" FROM employees ORDER BY salary DESC;

-RPAD를 사용해서 급여를 별표로 바꾸었다.


11. DECODE 함수를 사용하여 다음 데이터에 따라 JOB_ID 열의 값을 기준으로 모든 사원의 등급을 표시하는 질의를 작성하십시오. 업무등급 AD_PRES A, ST_MAN B, IT_PROG C, SA_REP D, ST_CLERK E, 기타0 

 

SELECT last_name,DECODE (job_id,'AD_PRES','A','ST_MAN','B','IT_PROG','C','SA_REP','D','ST_CLERK','E',0) as "GRADE" FROM employees;

-일정 기준으로 값을 부여하는 방법에는 크게 두 가지가 있는데 이 방법은 DECODE를 사용한 예시이다.

 

12. 11번 문제의 명령문을 CASE 구문을 사용하여 재작성하십시오.


SELECT last_name, 
  CASE job_id
   WHEN 'AD_PRES' THEN 'A'
   WHEN 'ST_MAN' THEN 'B'
   WHEN 'IT_PROG' THEN 'C'
   WHEN 'SA_REP' THEN 'D'
   WHEN 'ST_CLERK' THEN 'E'
  ELSE '0' END AS GRADE FROM employees;

-일정 기준으로 값을 부여하는 방법에는 크게 두 가지가 있는데 이 방법은 CASE를 사용한 예시이다.

 

1. 모든사원의이름, 부서번호, 부서이름을표시하는질의를작성하십시오. 

 

select e.last_name,d.department_id,d.department_name from employees e,departments d;

- 두 개의 릴레이션에 정보를 가져오고자 할 때에는 릴레이션에도 별칭을 부여한다.


 2. 부서80에속하는모든업무의고유목록을작성하고출력결과에부서의위치를포함시키십시오. (중복제거)

 

select distinct e.job_id , d.location_id from employees e INNER JOIN departments d ON e.department_id=80;

- 조인을 사용하였고, 중복을 제거하기 위해서 distinct를 사용하였다.


3. 커미션을 받는 모든 사원의 이름, 부서이름, 위치 ID 및 도시를 표시하는 질의를 작성하십시오.


select e.last_name,d.department_name,d.location_id,l.city from employees e, departments d, locations l where e.department_id=d.department_id and d.location_id=l.location_id and e.commission_pct is not null;

-JOIN 연산자가 아닌 where 조건으로 조인을 하였다.


4. 이름에 a(소문자)가 포함된 모든 사원의 이름과 부서 이름을 표시하는 질의를 작성하십시오.

 

select e.last_name,d.department_name from employees e,departments d where e.last_name like '%a%' and e.department_id=d.department_id;


5.Toronto에서 근무하는 모든 사원의 이름,업무,부서 번호 및 부서 이름을 표시하는 질의를 작성하십시오. (join,on 키워드 사용)

 
select e.last_name,e.job_id,d.department_id,d.department_name
from employees e Inner Join departments d on (e.department_id=d.department_id) Inner Join locations l on (d.location_id=l.location_id) where l.city= 'Toronto';

 

6.사원의 이름 및 사원 번호를 관리자의 이름 및 관리자 번호와 함께 표시하고, 각각의 열 레이블을 Employee,Emp#, Manager,Mgr#로 지정하십시오.(관리자가 없는 사원도 포함)

 

select e1.last_name as "Employee", e1.employee_id as "Emp#", e2.last_name as "Manager", e2.employee_id as "Mgr#"
from employees e1 left outer join employees e2 on e1.manager_id = e2.employee_id;

 

7.지정한 사원의 이름, 부서 번호 및 지정한 사원과 동일한 부서에서 근무하는 모든 사원을 표시하도록 질의를 작성하고, 각 열에 적합한 레이블을 지정하십시오. 또한 부서번호, 사원 이름, 동일한 부서에서 근무하는 사원의 이름으로 오름 차순 정렬하시오.

 
select e1.department_id as "DEPARTMENT", e1.last_name as "EMPLOYEE", e2.last_name as "COLLEAGUE"
from employees e1,employees e2 where e1.department_id=e2.department_id and not e1.last_name=e2.last_name
Order by DEPARTMENT, EMPLOYEE, COLLEAGUE;

 

8.Davies라는 사원보다 늦게 입사한 사원의 이름과 입사일을 표시하는 질의를 작성하십시오.

 

select last_name,hire_date from employees where hire_date >=(select hire_date from employees where last_name='Davies');

 

9.관리자보다 먼저 입사한 모든 사원의 이름 및 입사일을 관리자의 이름 및 입사일과 함께 표시하는 질의를 작성하십시오.


select e1.last_name,e1.hire_date,e2.last_name,e2.hire_date from employees e1 Inner Join employees e2 on (e1.manager_id=e2.employee_id)
where e1.hire_date < e2.hire_date;

 

'Lecture Note > DataBase' 카테고리의 다른 글

[강의노트_DB]15. SQL-5  (0) 2019.07.09
[강의노트_DB]14. SQL-4  (0) 2019.07.04
[강의노트_DB]12. SQL-2  (0) 2019.06.27
[강의노트_DB]11. SQL-1  (0) 2019.06.25
[강의노트_DB]10. 관계 대수-2  (0) 2019.05.09

INSERT, DELETE, UPDATE문

INSERT문은 기존의 릴레이션에 투플을 삽입하는 질의이다. 참조되는 릴레이션에 투플이 삽입되는 경우에는 참조 무결성 제약조건의 위배가 발생하지 않으나 참조하는 릴레이션에 투플이 삽입되는 경우에는 참조 무결성 제약조건을 위배할 수 있다. 

 

INSERT

INTO 릴레이션(애트리뷰트1, ...., 애트리뷰트n)

VALUES (값1, ... , 값n);

 

DELETE문은 한 릴레이션으로부터 한 개 이상의 투플들을 삭제한다. 참조되는 릴레이션의 삭제 연산의 결과로 참조 무결성 제약조건이 위배될 수 있으나, 참조하는 릴레이션에서 투플을 삭제하면 참조 무결성 제약조건을 위배하지 않음

DELETE FROM 릴레이션 WHERE 조건;

 

UPDATE문은 한 릴레이션에 들어 있는 투플들의 애트리뷰트 값들을 수정한다. 기본 키나 외래 키에 속하는 애트리뷰트의 값이 수정되면 참조 무결성 제약조건을 위배할 수 있다.

UPDATE 릴레이션

SET 애트리뷰트 = 값 또는 식[ , ...]

WHERE 조건;

 

트리거와 주장

트리거란 명시된 이벤트가 발생할 때마다 DBMS가 자동적으로 수행하는, 사용자가 정의하는 문이다. 데이터베이스의 무결성을 유지하기 위한 도구이며, 트리거를 이벤트-조건-동작(Event-Condition, Action) 규칙이라고도 부른다.

CREATE TRIGER <트리거 이름>

AFTER <트리거를 유발하는 이벤트> ON <릴레이션> [WHEN <조건>]

BEGIN <SQL문(들)> END;

 

이벤트의 가능한 예는 테이블에 삽입, 삭제, 수정등이 있다.

 

주장이란 제약조건을 위반하는 연산이 수행되지 않도록 하는 것이다.

CREATE ASSERTION 이름 CHECK 조건;

일반적으로 두 개 이상의 테이블에 영향을 미치는 제약조건을 명시하기 위해 사용된다.

 

SQL문 실습

1. 급여가 $12,000를 넘는 사원의 이름과 급여를 표시

 

...더보기

SELECT last_name, salary FROM employees WHERE salary>=12000; 

- 급여가 $12,000를 넘어야 하므로 이를 위한 WHERE 조건절을 추가하였다.

 

2. 사원 번호가 176인 사원의 이름과 부서 번호를 표시

 

...더보기

SELECT last_name,department_id FROM employees WHERE employee_id=176;

- 사원 번호가 176이어야 하므로 이를 위한 WHERE 조건절을 추가하였다.

 

3.급여가 $5,000에서 $12,000 사이에 포함되지 않는 모든 사원의 이름과 급여를 표시

 

...더보기

SELECT last_name,salary FROM employees WHERE salary<5000 or salary > 12000;

- 5,000미만 12,000초과이므로 or로 조건을 엮어주었다.

 

4. 2007년 2월 20일과 2007년 5월 1일 사이에 입사한 사원의 이름, 업무 ID 및 시작일을 표시하되, 시작일을 기준으로 오름차순으로 정렬하는 질의

 

...더보기

SELECT last_name,job_id,hire_date FROM employees WHERE hire_date between DATE'2007-02-20' and DATE'2007-05-01' ORDER BY hire_date;

-날짜는 DATE'YYYY-MM-DD'와 같은 꼴로 사용을 해야한다. 따라서 두 범위를 between으로 나타내었고, 이를 order by로 정렬하였다.

 

5. 부서 20 및 50에 속하는 모든 사원의 이름과 부서 번호를 이름을 기준으로 영문자순으로 표시

 

...더보기

SELECT last_name,department_id FROM employees WHERE department_id=20 or department_id=50 ORDER BY last_name;

-부서 번호 20, 50에 속하는 사원들을 출력해야 해서 WHERE문을 사용했고, 영문자순으로 표시해야하므로 order by를 사용했다.

 

6.급여가 $5,000와 $12,000 사이이고 부서 번호가 20 또는 50인 사원의 이름과 급여를 나열하고, 열 레이블을 Employee와 Monthly Salary로 각각 지정

 

...더보기

SELECT last_name "Employee", salary "Monthly Salary" FROM employees WHERE (salary between 5000 and 12000 ) and (department_id=20 or department_id=50);

- 열 레이블을 지정할 때 As를 사용하긴 하지만 위와 같이 생략을 했다. 위에서 계속 사용했던 조건들을 and를 사용해서 동시에 해당하도록 하였다.


7.1994년에 입사한 모든 사원의 이름과 입사일을 표시

...더보기


SELECT last_name,hire_date FROM employees WHERE hire_date between DATE'1994-01-01' and DATE'1994-12-31';

- 1994년만 지정해주려고 했으나 딱히 떠오르지 않아 1월 1일부터 12월 31일까지 범위로 진행하였다.

 

8.관리자가 없는 모든 사원의 이름과 업무 ID를 표시

 

...더보기

SELECT last_name,job_id FROM employees WHERE manager_id is NULL;

- 관리자 없다는 것은 관리자 번호가 NULL임을 의미한다.

 

9.커미션을 받는 모든 사원의 이름,급여 및 커미션을 급여 및 커미션을 기준으로 내림차순으로 정렬하여 표시

 

...더보기

SELECT last_name,salary,commission_pct FROM employees WHERE commission_pct is not NULL ORDER BY salary,commission_pct DESC;

- 커미션을 받지 않는 사람들은 NULL로 표시되어 있기에 is not NULL로 값을 찾았고 내림차순으로 정렬해야 했으므로 DESC를 사용했다.

 

10.이름의 세 번째 문자가 a인모든사원의이름을표시

 

...더보기

SELECT last_name FROM employees WHERE last_name like '__a%';

- 문자열 매칭에서 %는 여러개를 뜻하고, _는 한 글자를 뜻한다.

 

11.이름에 a와 e가 있는 모든 사원의 이름을 표시

 

...더보기

SELECT last_name FROM employees WHERE last_name like '%a%' and last_name like '%e%';

-이름에 a나 e가 있기만 하면 되므로 양 쪽에 %를 사용해서 찾았다.

 

12. 업무가 영업 사원(SA_REP)또는 사무원(ST_CLERK)이면서급여가 $2,500, $3,500 또는$7,000가 아닌 모든 사원의 이름, 업무 및 급여를 표시

 

...더보기

SELECT last_name,job_id,salary FROM employees WHERE job_id='SA_REP' or job_id='ST_CLERK')  and (salary not in (2500,3500,7000));

- 급여의 조건이 2,500 , 3,500 , 7,000과 같이 되어 있으므로 in을 사용해서 조건을 주었다.

 

13.커미션 비율이 20%인 모든 사원의 이름, 급여 및 커미션을 표시

 

...더보기

SELECT last_name,salary,commission_pct FROM employees WHERE commission_pct=0.2;

-커미션 비율이 20%라는 것을 where 절에 주도록 한다.

'Lecture Note > DataBase' 카테고리의 다른 글

[강의노트_DB]14. SQL-4  (0) 2019.07.04
[강의노트_DB]13. SQL-3  (0) 2019.07.02
[강의노트_DB]11. SQL-1  (0) 2019.06.25
[강의노트_DB]10. 관계 대수-2  (0) 2019.05.09
[강의노트_DB]9. 관계 대수 - 1  (1) 2019.05.07

SQL은 비절차적 언어이므로 사용자는 자신이 원하는(what)만 명시하며, 원하는 것을 처리하는 방법(how)은 명시할 수 없다. 또한 SQL은 대화식 SQL , 내포된 SQL 두 가지의 인터페이스를 가지고 있으며, 데이터 검색, 데이터 조작어, 데이터 정의어, 트랜잭션 제어, 데이터 제어어로 구성되어 있다.

 

데이터 정의어

스키마의 생성과 제거의 기능을 제공하며 다음과 같이 정의한다.

 

CREATE TABLE STUDENTS

(STUNO NUMBER NOT NULL,

STUNAME CHAR(10),

GRADE NUMBER,

CLASS NUMBER,

PRIMARY KEY(STUNO), [ 제약조건 ] )

 

STUDENTS의 스키마를 만드는데 그 안에 STUNO, STUNAME, GRADE, CLASS 와 같은 애트리뷰트를 가지는 것을 의미한다. 각 애트리뷰트명 옆에 써져 있는 것은 데이터 타입이고 다음 표와 같은 것들이 있다. 그리고 마지막에 PRIMARY KEY(STUNO)와 같이 기본키를 명시하고 제약 조건들을 명시해줄 수 있다.

데이터 타입

의미

INTEGER or INT 정수형
NUMBER(n , s) 소수점을 포함한 n개의 숫자에서 소수 아래 숫자가 s개인 십진수
CHAR(n) n바이트 문자열 n을 생략하면 1
VARCHAR(n) 최대 n바이트 까지 가변 길이 문자열
BIT(n) n개의 비트열 또는 최대 n개까지의 가변 비트열
DATE 날짜형, 날짜와 시간을 저장

 

이 밖에도 릴레이션 제거(DROP TABLE CLASSROOM), ALTER TABLE, 인덱스 생성 (CREATE INDEX)과 같은 기능들을 지원한다.

 

SELECT문

관계 데이터베이스에서 정보를 검색하는 SQL문으로 관계 대수의 실렉션과 의미가 완전히 다르다.

관계 대수의 실렉션, 프로젝션, 조인, 카티션 곱 등을 결합한 것과 같다고 볼 수 있으며 가장 많이 사용된다.

기본적인 SQL 질의에서 SELECT 절과 FROM절만 필수적인 절이고, 나머지는 선택 사항이다.

 

SELECT  [DISTINCT] 애트리뷰트(들)

FROM 릴레이션(들)

[WHERE 조건 [중첩 질의]] [GROUP BY 애트리뷰트(들)] [HAVING 조건] [ORDER BY 애트리뷰트(들) [ASC|DESC]];

 

다음은 SELECT문에서 사용할 수 있는 기능들이다.

 

별칭(alias)

서로 다른 릴레이션에 동일한 이름을 가진 애트리뷰트가 속해 있을 때 애트리뷰트의 이름을 구분하는 방법

FROM EMPLOYEE AS E, DEPARTMENT AS D

 

모든 애트리뷰트 검색

SELECT  * FROM DEPARTMENT;

DISTINCT

모든 상이한 값들만 검색하고자 할 때 사용한다. SELECT DISTINCT TITLE FROM EMPLOYEE;

 

WHERE

특정한 조건의 투플들만 검색하고자 할 때 사용한다.

 

2번 부서에 근무하는 사원들의 정보를 검색해라 == SELECT * FROM EMPLOYEE WHERE DNO=2;

 

문자열 비교

이씨 성을 가진 사원들의 이름, 직급, 소속 부서 번호를 검색하라.

SELECT EMPNAME, TITLE, DNO FROM EMPLOYEE WHERE EMPNAME LIKE '이%' ;

 

ORDER BY절

사용자가 SELECT문에서 질의 결과의 순서를 명시하지 않으면 릴레이션에 투플들이 삽입된 순서대로 사용자에게 제시된다. ORDER BY 절을 명시하면 해당 애트리뷰트를 기준으로 검색 결과를 정렬한다. SELECT문에 가장 마지막에 사용되며 디폴트 정렬 순서를 오름차순(ASC)이며, DESC를 지정하며 내림차순으로 정렬할 수 있다.

단, SELECT절에 명시한 애트리뷰트들을 사용해서 정렬해야 한다.

 

그룹화

GROUP BY절에 사용된 애트리뷰트에 동일한 값을 갖는 투플들이 하나의 그룹으로 묶임 이 애트리뷰트를 그룹화 애트리뷰트라고 한다. SELECT 절에는 집단 함수, 그룹화 애트리뷰트들만 나타날 수 있다.

HAVING절

어떤 조건을 만족하는 그룹들에 대해서만 집단 함수를 적용할 수 있다. 따라서 각 그룹마다 하나의 값을 갖는 애트리뷰트를 사용해서 각 그룹이 만족해야 하는 조건을 명시한다. 그룹화 애트리뷰트에 같은 갖는 투플들의 그룹에 대한 조건을 나타내고, 이 조건을 만족하는 그룹들만 질의 결과에 나타난다.

조인

두 개 이상의 릴레이션으로부터 연관된 투플들을 결합한다. 일반적인 형식은 FROM절에 두 개 이상의 릴레이션들이 열거되고, 두 릴레이션에 속하는 애트리뷰트들을 비교하는 조인 조건이 WHERE절에 포함된다. 흔히 =(비교 연산자)로 연결한다.

 

이 밖에도 많은 기능이 있으나 실습편에서 소개하겠습니다.

중첩 질의

질의의 where 또는 FROM절에 다시 SELECT문이 포함되는 것이며 부 질의라고 한다.

 

부 질의에서 한 개의 스칼라 값이 반환되는 경우 where절에서 상수 또는 애트리뷰트가 사용될 위치에 나타날 수 있다.

만약 릴레이션이 반환되는 경우에는 IN,  ANY, ALL, EXISTS와 같은 연산자를 사용해야 한다.

 

 

 

'Lecture Note > DataBase' 카테고리의 다른 글

[강의노트_DB]13. SQL-3  (0) 2019.07.02
[강의노트_DB]12. SQL-2  (0) 2019.06.27
[강의노트_DB]10. 관계 대수-2  (0) 2019.05.09
[강의노트_DB]9. 관계 대수 - 1  (1) 2019.05.07
[강의노트_DB]8. 무결성 제약조건  (0) 2019.05.02

카티션 곱 연산자

R x S와 같은 형식으로 사용하며 카디날리티가 i인 릴레이션 R(A1, A2,... , An)과 카디날리티가 j인 릴레이션 S(B1, B2,...., Bm)의 카티션 곱 R x S는 차수가 n+m이고, 카디날리티가 i*j이고, 애트리뷰트가 (A1, A2, ..., An, B1, B1, ..., Bm)이며 R과 S의 투플들의 모든 가능한 조합인 릴레이션, 카티션 곱의 결과 릴레이션은 크기가 매우 클 수 있다. 또한 사용자가 원하는 값은 카티션 곱의 일부분인 경우가 대다수이므로 유용한 연산자는 아니다.

 

카티션 곱 연산자 예시

관계 대수의 완전성

실렉션, 프로젝션, 합집합, 차집합, 카티션 곱은 관계 대수의 필수적인 연산자이다. 다른 관계 연산자들은 필수적인 관계 연산자를 두 개 이상 조합하여 표현 할 수 있다.

임의의 질의어가 필수적인 관계 대수 연산자들만큼의 표현력을 갖고 있으면 관계적으로 완전하다고 말한다.

 

조인 연산자

두 개의 릴레이션으로부터 연관된 투플들을 결합하는 연산자이다. 관계 데이터베이스에서 두 개 이상의 릴레이션들의 관계를 다루는데 매우 중요한 연산자이다.

 

  • 세타 조인 - 두 릴레이션의 카티션 곱의 결과 중에서 조인 조건을 만족하는 투플들로 이루어진 릴레이션, 조인 조건은 =, <>, <=,<,>=,> 가 있다.
  • 동등 조인 - 세타 조인의 조인 조건 중 = 인 조인
  • 자연 조인 - 두 릴레이션의 공통된 애트리뷰트에 대해 동등 조인을 수행하고, 동등 조인의 결과 릴레이션에 있는 두 개의 조인 애트리뷰트 중 하나를 제외한 조인, 가장 자주 사용된다.

조인 연산자 예시

디비전 연산자

차수가 n+m인 릴레이션R 과 차수가 m인 릴레이션 S의 디비전 R / S는 차수가 n이고, S에 속하는 모든 투플 u에 대하여 투플 tu가 R에 존재하는 투플 t들의 집합 " 모든... 에 대해 ~하는" 형태의 질의에 사용될 수 있다.

디비젼 연산자 예시

 기존에는 관계 대수는 집단함수와 그룹화를 제공하지 않았으나 지금은 제공을 하고 있다.

(ex, AVG, SUM, MIN, MAX, COUNT,...)

 

외부 조인

상대 릴레이션에서 대응되는 투플을 갖지 못하는 투플이나 조인 애트리뷰트에 널값이 들어 있는 투플들을 다루기 위해서 조인 연산을 확장한 조인이다. 두 릴레이션에서 대응되는 투플을 갖지 않는 투플과 조인 애트리뷰트에 널값을 갖는 투플도 결과에 포함시킨다. 왼쪽 외부 조인, 오른쪽 외부 조인, 완전 외부 조인이 있다.

 

관계 데이터 모델에서 지원하는 언어에는 크게 두 가지가 있다.

 

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

 

2. 관계 대수 - 어떻게 질의를 수행할 것인가를 명시하는 절차적 언어, SQL의 이론적인 기초

 

관계 대수

기존의 릴레이션들로부터 새로운 릴레이션을 생성한다. 즉 입력값, 출력 값이 모두 릴레이션이다.

연산자들을 적용하여 보다 복잡한 관계 대수식을 점차적으로 만들 수 있음. 기본적인 연산자들의 집합으로 이루어짐.

결과 릴레이션은 또 다른 관계 연산자의 입력으로 사용될 수 있다.

 

관계 연산자들의 종류와 표기법

실렉션 연산자

σ<실렉션 조건>(릴레이션) 과 같이 사용하며 한 릴레이션에서 조건을 만족하는 투플들의 부분 집합을 생성한다.

단항 연산자이며 결과 릴레이션의 차수는 항상 입력 릴레이션의 차수와 같으며 카디날리티는 결과가 항상 작거나 같다.

 

실렉션 연산자 예시

프로젝션 연산자

π<애트리뷰트 리스트>(릴레이션) 과 같이 사용하며 한 릴레이션의 애트리뷰트들의 부분 집합을 구한다.

결과 릴레이션은 애트리뷰트 리스트에 명시된 애트리뷰트들만 가진다. 실렉션의 결과로는 중복 투플이 존재할 수 있지만 프로젝션의 결과에는 중복된 투플이 존재하지 않음

 

프로젝션 연산자 예시

집합 연산자

릴레이션이 투플들의 집합이기 때문에 기존의 집합 연산이 릴레이션에 적용된다. 집합 연산자에는 합집합, 교집합, 차집합이 있다. 집합 연산자의 입력으로 사용되는 두 개의 릴레이션은 합집합 호환이어야 한다.

 

*합집합 호환

두 릴레이션 R1(A1, A2,..., An)과 R2(B1, B2,... , Bm)이 합집합 호환일 필요충분조건은 n=m이고, 모든 1 <=i <=n에 대해 domain(Ai)=domain(Bi)이다.

 

합집합 연산자

릴레이션 1 ∪ 릴레이션 2와 같이 사용하며 릴레이션1에 있거나, 릴레이션2에 있는 투플들로 이루어진 릴레이션을 반환

결과 릴레이션에서는 중복된 투플들은 제거한다.

 

합집합 연산자 예시

교집합 연산자

릴레이션1 ∩ 릴레이션2 와 같이 사용하며 릴레이션 1, 릴레이션 2에 동시에 속하는 투플들로 이루어진 릴레이션

 

교집합 연산자 예시

차집합 연산자

릴레이션 R, S에 대해 차집합 R-S는 R에는 속하지만 S에는 속하지 않은 투플들로 이루어진 릴레이션

 

차집합 연산자 예시

데이터 무결성

데이터의 정확성 또는 유효성을 의미한다. 일관된 데이터베이스 상태를 정의하는 규칙들을 묵시적, 명시적으로 정의한다. 데이터베이스가 갱신될 때마다 자동적으로 검사하므로 따로 검사할 필요가 없다.

 

도메인 제약조건

애트리뷰트 값이 반드시 원자값이어야 한다. 데이터 형식을 통해 값들의 유형을 제한하고, check 제약 조건을 통해 값들의 범위를 제한할 수 있음

 

키 제약조건

키 애트리뷰트에 중복된 값이 존재해서는 안된다는 것이다.

 

엔티티 무결성 제약조건

기본 키가 각 투플들을 식별하기 위해 사용되기 때문에 릴레이션의 기본 키를 구성하는 어떤 애트리뷰트도 널값을 가질 수 없다는 제약조건이다. 단 대체 키에는 적용되지 않는다.

 

참조 무결성 제약조건

참조 무결성 제약조건은 두 릴레이션의 연관된 투플들 사이의 일관성을 유지하는 데 사용된다. 릴레이션 R2의 외래 키가 릴레이션 R1의 기본 키를 참조할 때 참조 무결성 제약조건은 아래의 두 조건 중 하나가 성립되면 만족된다.

1. 외래 키의 값은 R1의 어떤 투플의 기본 키 값과 같음.

2. 널 값을 가진다.(외래 키가 자신의 릴레이션에서 기본키가 아니다.)

 

 

데이터베이스를 갱신할 때마다 무결성 제약조건을 유지시켜줘야 한다. 데이터베이스에 대한 갱신 연산은 삽입, 삭제, 변경 연산으로 구분하는데 DBMS는 각 갱신, 연산마다 필요한 조치를 취한다.

 

삽입

새로 삽입되는 투플의 기본 키 애트리뷰트의 값에 따라 도메인 제약조건, 키 제약조건, 엔티티 무결성 제약조건을 위배할 수 있다. 참조되는 릴레이션에 새로운 투플이 삽입되면 참조 무결성 제약조건은 위배되지 않으나, 참조하는 릴레이션에 삽입될 경우에는 위배될 수 있다.

 

삭제

참조하는 릴레이션에서 투플이 삭제되면 도메인 제약조건, 키 제약조건, 엔티티 무결성 제약조건, 참조 무결성 제약조건을 위배하지 않는다. 하지만 참조되는 릴레이션에서 투플이 삭제된다면 참조 무결성 제약조건을 위배하는 경우가 생긴다.

수정

DBMS는 수정하는 애트리뷰트가 기본 키인지 외래 키인지 검사한다. 수정하려는 애트리뷰트가 둘 다 아니면 참조 무결성 제약조건을 위배하지 않는다. 기본 키나 외래 키를 수정하는 것은 삽입이나 삭제를 하는 것과 같으므로 동일하게 적용된다.

 

참조 무결성 제약조건을 만족시키기 위한 DBMS가 제공하는 옵션

제한(restricted)

위배를 야기한 연산을 단순히 거절한다.

 

연쇄(cascade)

참조되는 릴레이션에서 투플을 삭제하고, 참조하는 릴레이션에서 이 투플을 참조하는 투플들도 함께 삭제해버린다.

 

널값(nullify)

참조되는 릴레이션에서 투플을 삭제하고, 참조하는 릴레이션에서 이 투플을 참조하는 투플들의 외래 키에 널값을 삽입한다.

 

디폴트 값

널값을 넣는 대신에 디폴트 값을 넣는다.

 

 

릴레이션의 특성

릴레이션은 동일한 튜플이 두 개 이상 존재하지 않는다. 또한 한 튜플의 각 애트리뷰트는 원자값을 가진다. 즉 리스트, 집합, 튜플 값이 들어갈 수 없는 것이다.

 

릴레이션의 키

각 투플을 고유하게 식별할 수 있는 하나 이상의 애트리뷰트들의 모임이다. 

슈퍼 키

한 릴레이션 내의 특정 투플을 고유하게 식벽하는 하나의 애트리뷰트 또는 애트리뷰트 집합이다. 예를 들면 신용카드 회사에서 고객 릴레이션에서 (신용카드번호, 주소) 또는 (주민등록번호)가 해당 된다. 하지만 투플들을 고유하게 식별하는데 꼭 필요하지 않은 애트리뷰트들을 포함할 수 있다.

 

후보 키 

각 투플을 고유하게 식별하는 최소한의 애트리뷰트들의 모임이며 앞으로 추가될 값도 생각해야 한다. 모든 릴레이션에는 최소한 한 개 이상의 후보 키가 있다. 

 

기본 키

한 릴레이션에 후보 키가 두 개 이상 있으면 설계자 혹은 데이터베이스 관리자가 이들 중에서 하나를 기본 키로 선정 한다. 만약 자연스러운 기본 키를 찾을 수 없다면 인덱스 번호와 같이 인위적인 키를 추가할 수 있다.

 

대체 키

기본 키가 아닌 후보키를 뜻한다.

 

외래 키

어떤 릴레이션의 기본 키를 참조하는 애트리뷰트이다. 여기서 어떤 릴레이션은 다른 릴레이션일수도 있고, 자신의 릴레이션일수도 있다. 관계 데이터베이스에서 릴레이션들 간의 관계를 나타내기 위해서 사용된다.

 

 

관계 데이터 모델은 지금까지 제안된 데이터 모델 중에서 가장 개념이 단순한 데이터 모델 중 하나이다. 동일한 구조의 관점에서 모든 데이터를 논리적으로 구성하며, 선언적인 질의어를 통해 데이터 접근을 제공한다.

 

기본적인 용어

1. 릴레이션 : 2차원의 테이블

2. 레코드 : 릴레이션의 각 행

3. 튜플 : 레코드를 공식적으로 부르는 말

4. 애트리뷰트 : 릴레이션에서 이름을 가진 하나의 열

 

도메인(domain)

도메인은 한 애트리뷰트에 나타날 수 있는 값들의 집합을 뜻한다. 각 애트리뷰트의 도메인의 값들은 원자값을 가지고 동일한 도메인이 여러 애트리뷰트에서 사용될 수 있다. 프로그래맹 언어의 데이터 타입과 유사하다고 생각하면 된다.

 

차수(degree)

차수는 한 릴레이션에 들어 있는 애트리뷰트들의 수이다. 최소 차수는 1이여야 하며 차수가 자주 변하지는 않는다.

 

카디날리티(cardinality)

카니날리티는 릴레이션의 투플 수이다. 유효한 릴레이션은 카니날리티 0을 가질 수 있다.(릴레이션에 아무런 값이 없는 것이 가능하므로) 카디날리티는 시간이 지남에 따라 자주 변할 수 있다.

 

널값(null value)

'알려지지 않음' ,  '적용할 수 없음'을 나타내기 위해 널값을 사용한다. 하지만 널값이 숫자 0을 의미하거나 공백을 의미하지는 않는다. 

 

릴레이션 스키마(relation schema)

릴레이션의 이름과 릴레이션의 애트리뷰트들의 집합이다. 릴레이션을 위한 틀이라고 생각하면 된다. 기본키 애트리뷰트에는 밑줄로 표시해 준다.

릴레이션 이름( 애트리뷰트 1 , 애트리뷰트 2,....., 애트리뷰트 N)과 같이 표기한다.

 

릴레이션 인스턴스(relation instance)

릴레이션에 어느 시점에 들어 있는 튜플들의 집합이다. 시간의 흐름에 따라 계속 변화한다.

 

 

현재 대부분의 상용 DBMS 구현에서 사용되는 일반적인 아키텍처는 ANSI/SPARC 아키텍처이다. ANSI/SPARC 아키텍처를 데이터 독립성을 제공하고 3단계(물리적, 개념적, 외부)로 이루어져 있다.

 

외부 단계

데이터베이스의 각 사용자가 갖는 뷰를 뜻하며 여러 부류의 사용자들을 위해 동일한 개념 단계로부터 다수의 서로 다른 뷰가 제공될 수 있다. 일반적으로 사용자가 자신이 관심 있는 분야만 본다.

 

개념 단계

조직체의 정보 모델로서, 물리적인 구현은 고려하지 않으면서 조직체 전체에 관한 스키마를 포함한다. 어떤 데이터가 저장되어 있고, 데이터간의 어떤 관계가 존재하고, 어떤 무결성 제약조건들이 명시되어 있는가를 기술한다. 사용자 공동체의 뷰를 나타내며 하나의 데이터베이스당 하나의 개념 스키마가 존재한다.

 

내부 단계

실제의 물리적인 데이터 구조에 관한 스키마이고, 데이터베이스에 어떤 데이터가 어떻게 저장되어 있는지를 기술한다. 인덱스, 해싱 등과 같은 접근 경로, 데이터 압축 등을 기술한다.

 

예시

지하철 노선도에서 불광동에 사는 학생이 청량리에 있는 학교에 통학하기 위해 불광역, 종로3가역, 청량리역에만 관심을 가진다. 

 

지하철 노선도가 어떤 역이 있는지 나타나있고, 역간 어떤 관계가 존재하는지 나타나 있기 때문에 개념 단계이며, 사용자가 갖는 뷰인 불광역, 종로3가역, 청량리역은 외부 단계가 된다. 내부 단계는 지하철이 상행선, 하행선인지 확인하는 것이다.

 

데이터 독립성

상위 단계의 스키마 정의에 영향을 주지 않으면서 어떤 단계의 스키마 정의를 변경할 수 있음을 의미한다.

논리적인 데이터 독립성

개념 스키마의 변화로부터 외부 스키마가 영향을 받지 않는다.

물리적인 데이터 독립성

내부 스키마의 변화가 개념적 스키마에 영향을 미치지 않는다.

DBMS 언어

DBMS 언어에는 크게 3가지가 있는데 데이터 정의어, 데이터 조작 어, 데이터 제어 어 이다.

 

데이터 정의어(DDL: Data Definition Language)

사용자는 데이터 정의어를 사용해서 데이터 베이스 스키마를 정의한다. 데이터 정의어로 명시된 문장이 입력되면 DBMS는 사용자가 정의한 스키마에 대한 명세를 시스템 카탈로그에 저장한다.

데이터 정의어의 기본적인 기능은 생성(CREATE TABLE), 변경(ALTER TABLE), 삭제(DROP TABLE), 특정 애트리뷰트 위의 인덱스 정의(CREATE INDEX)이다. ( () 안은 SQL 기준)

 

데이터 조작어(DML:Data Manipulation Language)

사용자는 데이터 조작어를 사용해서 데이터베이스 내의 원하는 데이터에 대해 검색, 수정, 삽입, 삭제가 가능하다. 절차적인 언어와 비절차적인 언어로 구분이 되는데 관계 DBMS에서 사용되는 SQL은 대표적인 비절차적 언어이다. 대부분의 데이터 조작어는 SUM, COUNT, AVG와 같은 그룹 내장 함수들을 가지고 있다. 데이터 조작어의 기본적인 기능은 검색(SELECT), 수정(UPDATE), 삭제(DELETE), 삽입(INSERT)이 있다.

 

데이터 제어어(DCL: Data Control Language)

사용자는 데이터 제어어를 사용해서 데이터베이스 트랜잭션을 명시하고 권한을 부여하거나 취소한다. 주로 데이터의 보안, 무결성, 데이터 회복, 병행 수행 제어 등을 정의하는데 사용한다. 데이터 제어어의 기본적인 기능은 COMMIT, ROLLBACK, GRANT, REVOKE를 가지고 있다.

 

+ Recent posts