관계 데이터베이스?

이전 포스팅에서 다루지 못한 관계 데이터 모델에 대해 자세하게 설명하겠다.

관계데이터베이스의 구조

릴레이션(Relation) 으로 표현됨

 - 릴레이션 이란? 데이터 구조를 테이블 형태로 표현한 것

릴레이션 스키마와 릴레이션 인스턴스로 구성

① 릴레이션 스키마 : 릴레이션의 논리적 구조를 정의하는 정적 구조

② 릴레이션 인스턴스 : 어느 한 시점의 릴레이션 상태, 즉 튜플 전체를 의미하며 동적 구조

 - 릴레이션 관련 용어

① 튜플(Tuple) : 릴레이션에서 각각의 행(row)을 의미

② 속성(Attiribute) : 릴레이션에서 각각의 열(column)을 의미

③ 도메인(Domain) : 한 속성이 가질 수 있는 값의 집합

④ 차수(Degree) : 릴레이션에서 속성의 개수

⑤ 카디널리티(Cardinality) : 릴레이션에서 튜플의 개수

이 학생 릴레이션의 degree는 학번, 이름, 학년, 학과, 전화번호 = 5

   cardinality는 튜플의 수 = 5 이다.

 - 릴레이션의 특징

① 릴레이션의 모든 튜플은 중복되지 않는다. → 튜플의 유일성

② 릴레이션에서 튜플 사이의 순서는 정의되지 않는다. → 튜플의 무순서

③ 릴레이션을 구성하는 속성 사이에는 순서가 없다. → 속성의 무순서

④ 릴레이션의 속성값은 더이상 분해할 수 없는 원자값이다. → 속성값의 원자성

⑤ 릴레이션을 구성하는 속성명은 유일한 이름이여야 한다. → 속성명의 유일성

 - 릴레이션의 키

키(Key) : 릴레이션의 모든 튜플들을 유일하게 식별할 수 있는 특성을 가진 속성이나 속성의 집합을 의미

⒜ 슈퍼키(super key) : 릴레이션 내의 모든 튜플에 대하여 유일성만 만족하는 속성

  릴레이션 내에서 여러 개의 슈퍼키가 정의 가능

⒝ 후보키(candidate key) : 릴레이션에서 튜플을 유일하게 구별하기 위해 사용하는 속성 혹은 속성들의 집합

 릴레이션 내에서 유일성 뿐만 아니라 최소성도 만족해야 하는 속성

 릴레이션 내에서 여러 개의 후보키 정의 가능

• 유일성(uniqueness) : 릴레이션에 있는 모든 튜플에 대해 유일하게 식별되어야 한다.
• 최소성(minimality) : 유일성을 가진 키를 구성하는 속성(Attribute) 중 하나라도 제외하는 경우 유일성이 깨지는 것을 의미한다. 즉, 릴레이션의 모든 튜플을 유일하게 식별하는 데 꼭 필요한 속성들로만 구성되어야 한다.

   인터넷에서 가져온 문제로 후보키를 구해 보겠다.

(ex)  아래와 같은 학생들의 인적사항이 주어졌을 때, 후보 키의 최대 개수를 구하라.

위의 예를 설명하면, 학생의 인적사항 릴레이션에서 모든 학생은 각자 유일한 “학번”을 가지고 있다. 

따라서 “학번”은 릴레이션의 후보 키가 될 수 있다. 

그다음 “이름”에 대해서는 같은 이름(“apeach”)을 사용하는 학생이 있기 때문에, “이름”은 후보 키가 될 수 없다. 

그러나, 만약 [“이름”, “전공”]을 함께 사용한다면 릴레이션의 모든 튜플을 유일하게 식별 가능하므로 후보 키가 될 수 있게 된다. 

물론 [“이름”, “전공”, “학년”]을 함께 사용해도 릴레이션의 모든 튜플을 유일하게 식별할 수 있지만, 

최소성을 만족하지 못하기 때문에 후보 키가 될 수 없다. 

따라서, 위의 학생 인적사항의 후보키는 “학번”, [“이름”, “전공”] 두 개가 된다.

+ 슈퍼키는 "학번", [“학번”, “이름”] ,["학번“,이름”, “전공”],[“이름”, “전공”], [“이름”, “전공”, “학년”] ...등 튜플의 유일성만 만족하면 되므로 다양하다 

⒞ 기본키(primary key) : 여러 개의 후보키 중 선택된 하나의 주키

   기본키 속성값으로 NULL이나 중복되는 값은 결코 가질 수 없음

   *NULL : 공백(space)도 아니고 0(zero)도 아니고 부재정보를 나타냄

   정의되지 않았거나 알려지지 않는 모르는 값

⒟ 대체키(alternate key) : 여러 후보키 중 기본키로 선택되지 못한 후보키

⒠ 외래키(foreign key) : 릴레이션 R1, R2에서 R1의 속성값이 릴레이션 R2의 기본키 값들과 일치함을 요구하는  R1의 속성값을 외래키 라고한다.

   이 경우 외래키는 릴레이션 R2를 참조한다고 하고, R1을 참조 릴레이션, R2를 참조된 릴레이션이라고 한다.

  참조관계인 외래키와 기본키의 속성명은 다를 수 있지만 정의된 도메인은 같아야한다.

  외래키는 참조하는 릴레이션의 기본키에 없는 값은 가질 수 없다.

  외래키는 릴레이션과 릴레이션을 연결(join)하는 도구로 사용된다.

 

 - 무결성 제약조건

① 개체 무결성 제약조건 : 기본키(primary key)의 값은 결코 널(null)값이나 중복값을 가질 수 없다.

② 참조 무결성 제약조건 : 외래키(foreign key) 값은 참조하는 릴레이션의 기본키 값과 일치하거나 NULL 값만 가져야 하고, 참조할 수 없는 값은 가질 수 없다.

관계 데이터베이스의 연산자

1. 관계 대수 : 원하는 정보를 얻기 위해 일련의 연산순서를 명세하는 절차적 언어

일반 집합 연산자

① 합집합(∪)

② 교집합(∩)

③ 차집합(－)

④ 교차곱(×)

ex) 예를 들어 릴레이션 R,S,T가 있다고 할 때

합집합, 교집합, 차집합, 교차곱을 계산하면 아래와 같다

순수 관계 연산자

① Select(σ) : 릴레이션에서 조건식을 만족하는 튜플을 검색하는 연산자

행에 해당하는 튜플만 구함

(ex) 릴레이션 R에서 속성 C값이 c1인 튜플만 추출할 시

② Project(π) : 릴레이션에서 기술된 속성리스트를 추출하는 연산자

  열에 해당하는 속성만 구함

(ex) 릴레이션 R에서 속성 A,B 값만 추출할 시

(ex) 학생 릴레이션에서 컴퓨터 학과 학생의 이름을 추출할 시 관계 대수?

-> ∏이름(σ학과=컴퓨터(학생))

③ Join(⋈) : 두 릴레이션의 공통 속성을 기준으로 하나로 합쳐서 새로운 릴레이션을 만드는 연산자

카디션 곱(교차곱, x) + 선택연산(select)의 형태

조건에 따라 3 가지로구별됨

세타조인(theta join) : 선택연산의 비교연산자가 {＝, ＜, ＞, ≤, ≥ }등이 사용되는 경우

동등조인(equi join) : 세타 조인 중 특별히 비교 연산자가 = 인 경우

자연조인(natural join) : 동등 조인에서 중복 속성 중 하나가 제거 된 경우

(ex) 학생 릴레이션과 과목 릴레이션이 있을 때

'학생’릴레이션과 ‘과목’ 릴레이션을 동등 조인 할 경우

(학생 ⋈학생.과목코드 = 과목.과목코드 과목)

학생’릴레이션과 ‘과목’ 릴레이션을 자연 조인 할 경우(중복 속성 중 하나 제거)

④ Division(÷) : 두 릴레이션 R(X), S(Y) 에 대하여 X ⊇ Y 일때, R의 속성이 S의 속성값을 모두 가지는 튜플에서

    S가 가진 속성을 제외한 속성값만을 구하는 연산자

(ex) 학과목 릴레이션과 과목릴레이션 3개가 있을때 Division예시를 보면

1. SC ÷ C1

나누려는 릴레이션의 원소를 가지고 있어야 하므로 과목1(C1)의 원소 C413을 가진 학번은

100, 300, 400 번.

2. SC ÷ C2

과목2(C2)의 원소 C312을 가진 학번은 300, 400, 500

C413을 가진 학번은 100, 300, 400

두 원소 공통으로 가지고 있는 학번은 300, 400 번.

3. SC ÷ C3

과목3(C3)의 원소 C312, C413, E412 모두 가진 학번은 400 번.

2. 관계 해석 : 원하는 정보가 무엇이라는 것만 선언하는 비절차적 언어

정량자	∀ : 모든 것에 대하여 ∃ : 존재하는 것에 대하여
논리연산자	∧ : and ∨ : or ￢ : not
비교 연산자	＜,＞,≤,≥,＝,≠
기타 연산자	∈ : 원소, ⊂ : ~부분집합,  : 공집합

관계데이터베이스 언어

- SQL(Structed Query Language) : 관계 대수및 관계 해석에 기초한 데이터베이스 언어

구분	명령어	기능
DDL(데이터 정의어)	CREATE ALTER DROP	테이블정의 테이블변경 테이블삭제
DML(데이터 조작어)	INSERT DELETE UPDATE SELECT	데이터삽입 데이터삭제 데이터변경 데이터검색
DCL(데이터 제어어)	GRANT REVOKE COMMIT ROLLBACK	권한부여 권한해제 트랜잭션 완료 트랜잭션 취소

1. 데이터 정의어

① CREATE : 테이블 정의

   형식

CREATE TABLE <테이블명>(

속성명 데이터타입 [NOT NULL] [DEFAULT <값>] [PRIMARY KEY]

[UNIQUE (속성명)]

[FOREIGN KEY (외래키) REFERENCES <참조테이블 이름> (기본키)]

[CHECK (조건식)]

);

UNIQUE : 속성값의 중복을 배제하기 위해 사용

FOREIGN KEY : 외래키 지정시 사용

CHECK : 테이블의 행 삽입이나 수정시 유지해야할 무결성 제약조건

/*예를 들어 다음 조건에 맞게 '학생'테이블을 만든다고 할 때
조건) 
학번은 정수형으로 NULL을 가질 수 없고, 기본키이다.
이름은 가변길이 문자열 10자리
학년은 정수형으로 1~4 사이만 입력되는 제약조건을 가진다.
학과는 고정길이 문자열 15자리
과목코드는 고정길이 문자열 10자리로 과목테이블의 과목코드를 참조하는 외래키이다.*/
 
CREATE TABLE 학생(
 
    학번 INT NOT NULL,
    이름 VARCHAR(10),
    학년 INT,
    학과 CHAR(15),
    과목코드 CHAR(10),
    PRIMARY KEY(학번),
    FOREIGN KEY(과목코드) REFERENCES 과목(과목코드),
    CHECK(학년 >= 1 AND 학년 <= 4)
 
);
 

        

 SQL의 데이터 타입

- 고정길이 문자 : CHAR(n), n은 문자 수

- 가변길이 문자 : VARCHAR(n)           

- 정수 : INT, SMALL INT                   

- 실수 :  FLOAT, REAL                      

- 날짜 : DATE                                 

② ALTER : 테이블 변경

  형식

ALTER TABLE <테이블명>

ADD 추가할 속성명 데이터타입;

DROP COLUMN 삭제할 속성명;

-- 예를 들어 '학생' 테이블에 새로운 속성, 두문자 크기를 갖는 학점을 추가할 시
ALTER TABLE 학생 ADD 학점 CHAR(2);
 
--학생 테이블에 추가된 학점 속성을 삭제할 시
ALTER TABLE 학생 DROP COLUMN 학점;

③ DROP : 테이블 삭제

형식

DROP TABLE <테이블명> [CASCADE|RESTRICT];

CASCADE : 테이블과 테이블을 참조하는 모든 제약조건과 뷰들이 자동 삭제

RESTRICT : 제약조건이나 뷰들이 참조하지 않는 테이블인 경우만 삭제

--예를 들어 학생 테이블을 삭제하면서 학생테이블을 참조하는 모든 테이블도 같이 삭제할 시
 DROP TABLE 학생 CASCADE;

2. 데이터 조작어

① INSERT : 튜플 삽입

형식

INSERT INTO 테이블명(속성목록) VALUES(상수목록)

INSERT INTO 테이블명(속성목록) SELECT 문 ~

속성목록 작성안하면 테이블의 모든 속성에 값을 삽입한다는 뜻

--예를 들어 학생 테이블의 학번, 이름 속성에 (9004, 박길동)을 삽입할 시
INSERT INTO 학생(학번, 이름) VALUES(9004, 박길동);
 
 
--학생 테이블의 각 속성에 대응하여 튜플 (9005, 차길동, 3, 보안)을 삽입할 시
INSERT INTO 학생 VALUES (9005, 차길동, 3, 보안);
 
 
--학생 테이블에서 전자과 학생의 학번,이름 학년을 검색하여 클래스 테이블로 삽입할 시
 
INSERT INTO 클래스(학번, 이름, 학년)
SELECT 학번,이름,학년 FROM 학생 WHERE 학과 ='전자';

② DELETE : 튜플 삭제

형식

DELETE

FROM <테이블명>

[WHERE <조건>]

조건을 기술하지 않으면 테이블의 모든 튜플들이 삭제된다는 뜻

--예를 들어 학생테이블에서 학번이 9004인 학생을 삭제할시
DELETE FROM 학생 WHERE 학번 = 9004;

③ UPDATE : 튜플 갱신

형식

UPDATE <테이블명>

SET 속성 = 변경값

[WHERE] <조건>

--예를 들어 모든 학생의 점수를 1점씩 더할시
UPDATE 학생 SET 점수 = 점수 + 1;
 
 
--학번이 9003인 학생의 이름을 박지성으로 바꿀시
UPDATE 학생 SET 이름 = '박지성' WHERE 학번 = 9003;

④ SELECT : 튜플 검색

형식

SELECT [DISTINCT] <속성목록>

FROM <테이블 리스트>

[WHERE <[LIKE] 조건>]

[OREDER BY <속성> [ASC] 또는 [DESC] ]

[GROUP BY <그룹화 필드>]

[HAVING <그룹조건>]

DISTINCT : 중복된 데이터를 제거하고 표시할 때 사용

LIKE : 문자 상수를 명세한 검색 조건

   %는 임의개수 문자

   _는 한 개의 문자

ORDER BY :  검색결과 에 대해 속성별로 오름차순, 내림차순 지정

GROUP BY : 그룹화 할 기준 속성 

HAVING : 그룹들 중에서 선택할 조건 기술

예를 들어 학생 테이블과 성적 테이블이 있다고 할때

학생

학번	이름	학년	학과
9001	박찬호	3	전자
...	...	...	..

.

.

성적

학번	과목코드	중간고사	기말고사

.

.

--학생 테이블에서 학과가 '게임'인 학생의 학번, 이름, 학과 검색할시
 
SELECT 학번, 이름, 학과
FROM 학생
WHERE 학과 = '게임';
 
 
 
--학생테이블에서 중복된 학과는 한번만 출력할 시
 
SELECT DISTINCT 학과
FROM 학생;
 
 
 
--성적테이블에서 과목코드가 b로 시작하는 튜플을 검색할 시
 
SELECT *
FROM 성적
WHERE 과목코드 LIKE 'B%';
 
 
 
--학생테이블에서 학과가 NULL이 아닌 학생을 검색할시
 
SELECT *
FROM 학생
WHERE 학과 IS NOT NULL;
 
 
 
--학생테이블에서 학번, 이름,학년 검색시 학년은 오름차순으로, 
--같은 학번일 때는 학번으로 내림차순 정렬할 시
 
SELECT 학번, 이름, 학년
FROM 학생
ORDER BY 학년 ASC, 학번 DESC;
 
 
 
--성적테이블에서 중간고사 평균 및 기말 고사 합계 점수를 출력할 시
 
SELECT AVG(중간고사), SUM(기말고사)
FROM 성적;
 
 
 
--성적테이블에서 과목코드별 기말고사 평균을 검색할시
 
SELECT AVG(기말고사)
FROM 성적
GROUP BY 과목코드;
 
 
 
--성적테이블에서 두사람이상 등록한 과목의 중간고사 평균을 검색할시
 
SELECT AVG(중간고사)
FROM 성적
GROUP BY 과목
HAVING COUNT(*) >= 2;
 
 
 
--성적테이블에서 중간고사 점수가 70이상 90이하인 학생의 학번과 중간고사 점수 검색
 
SELECT 학번, 중간고사
FROM 성적
WHERE 중간고사 >= 70 AND 중간고사 <=90;

 **그룹함수**

SUM(속성명) : 지정한 속성값의 합계

AVG(속성명) : 지정한 속성값의 평균

MAX(속성명) : 지정한 속성값 중 최대값

MIN(속성명) : 지정한 속성값 중 최소값

COUNT(속성명) : 지정한 속성의 튜플 값

COUNT(*) : NULL 값도 포함한 모든 튜플 수

3. 데이터 제어어

① GRANT : 특정 데이터와 연산을 특정 사용자만이 수행할 수 있게 권한을 부여

형식

GRANT 권한 ON 데이터 객체 TO 사용자 [WITH GRANT OPTION];

GRANT OPTION : 다른 사용자에게 권한 부여권을 부여

--DBA가 사용자1에게 학생 릴레이션에 대한 갱신 권한과 권한 부여권을 부여하는 경우
GRANT UPDATE ON 학생 TO 사용자1 WITH GRANT OPTION;
 
--DBA가 모든 사용자에게 학생 릴레이션 대한 SELECT 권한을 부여하는 경우
GRANT SELECT ON 학생 TO PUBLIC;

DBA : 데이터베이스관리자 (DataBase Administrator)

   데이터 정의어와 데이터 제어어를 사용하여 데이터베이스를 설계하고 관리,운영하는 사용자

② REVOKE : 사용자에게 부여된 권한을 취소

형식

REVOKE 권한 [GRANT OPTION FOR] ON 데이터 객체 FROM 사용자 {CASCADE, RESTRICT};

CASCADE 사용하면 부여된 권한을 연쇄적으로 취소.

RESTRICT 사용하면 하위 권한 없이 사용자만 권한을 취소.

--DBA가 사용자2에게 부여한 권한을 취소하는 경우
REVOKE DELETE ON 학생 FROM 사용자2 CASCADE;

COMMIT과 ROLLBACK은 다음 포스팅에서 트랜잭션을 소개한 후 설명하겠다.