관계 데이터베이스?
이전 포스팅에서 다루지 못한 관계 데이터 모델에 대해 자세하게 설명하겠다.
관계데이터베이스의 구조
릴레이션(Relation) 으로 표현됨
- 릴레이션 이란? 데이터 구조를 테이블 형태로 표현한 것
릴레이션 스키마와 릴레이션 인스턴스로 구성
① 릴레이션 스키마 : 릴레이션의 논리적 구조를 정의하는 정적 구조
② 릴레이션 인스턴스 : 어느 한 시점의 릴레이션 상태, 즉 튜플 전체를 의미하며 동적 구조
- 릴레이션 관련 용어
① 튜플(Tuple) : 릴레이션에서 각각의 행(row)을 의미
② 속성(Attiribute) : 릴레이션에서 각각의 열(column)을 의미
③ 도메인(Domain) : 한 속성이 가질 수 있는 값의 집합
④ 차수(Degree) : 릴레이션에서 속성의 개수
⑤ 카디널리티(Cardinality) : 릴레이션에서 튜플의 개수
이 학생 릴레이션의 degree는 학번, 이름, 학년, 학과, 전화번호 = 5
cardinality는 튜플의 수 = 5 이다.
- 릴레이션의 특징
① 릴레이션의 모든 튜플은 중복되지 않는다. → 튜플의 유일성
② 릴레이션에서 튜플 사이의 순서는 정의되지 않는다. → 튜플의 무순서
③ 릴레이션을 구성하는 속성 사이에는 순서가 없다. → 속성의 무순서
④ 릴레이션의 속성값은 더이상 분해할 수 없는 원자값이다. → 속성값의 원자성
⑤ 릴레이션을 구성하는 속성명은 유일한 이름이여야 한다. → 속성명의 유일성
- 릴레이션의 키
키(Key) : 릴레이션의 모든 튜플들을 유일하게 식별할 수 있는 특성을 가진 속성이나 속성의 집합을 의미
⒜ 슈퍼키(super key) : 릴레이션 내의 모든 튜플에 대하여 유일성만 만족하는 속성
릴레이션 내에서 여러 개의 슈퍼키가 정의 가능
⒝ 후보키(candidate key) : 릴레이션에서 튜플을 유일하게 구별하기 위해 사용하는 속성 혹은 속성들의 집합
릴레이션 내에서 유일성 뿐만 아니라 최소성도 만족해야 하는 속성
릴레이션 내에서 여러 개의 후보키 정의 가능
- 유일성(uniqueness) : 릴레이션에 있는 모든 튜플에 대해 유일하게 식별되어야 한다.
- 최소성(minimality) : 유일성을 가진 키를 구성하는 속성(Attribute) 중 하나라도 제외하는 경우 유일성이 깨지는 것을 의미한다. 즉, 릴레이션의 모든 튜플을 유일하게 식별하는 데 꼭 필요한 속성들로만 구성되어야 한다.
위의 예를 설명하면, 학생의 인적사항 릴레이션에서 모든 학생은 각자 유일한 “학번”을 가지고 있다.
따라서 “학번”은 릴레이션의 후보 키가 될 수 있다.
그다음 “이름”에 대해서는 같은 이름(“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 : 테이블의 행 삽입이나 수정시 유지해야할 무결성 제약조건
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | /*예를 들어 다음 조건에 맞게 '학생'테이블을 만든다고 할 때 조건) 학번은 정수형으로 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) ); | cs |
SQL의 데이터 타입
- 고정길이 문자 : CHAR(n), n은 문자 수
- 가변길이 문자 : VARCHAR(n)
- 정수 : INT, SMALL INT
- 실수 : FLOAT, REAL
- 날짜 : DATE
② ALTER : 테이블 변경
형식
ALTER TABLE <테이블명>
ADD 추가할 속성명 데이터타입;
DROP COLUMN 삭제할 속성명;
1 2 3 4 5 | -- 예를 들어 '학생' 테이블에 새로운 속성, 두문자 크기를 갖는 학점을 추가할 시 ALTER TABLE 학생 ADD 학점 CHAR(2); --학생 테이블에 추가된 학점 속성을 삭제할 시 ALTER TABLE 학생 DROP COLUMN 학점; | cs |
③ DROP : 테이블 삭제
형식
DROP TABLE <테이블명> [CASCADE|RESTRICT];
CASCADE : 테이블과 테이블을 참조하는 모든 제약조건과 뷰들이 자동 삭제
RESTRICT : 제약조건이나 뷰들이 참조하지 않는 테이블인 경우만 삭제
1 2 | --예를 들어 학생 테이블을 삭제하면서 학생테이블을 참조하는 모든 테이블도 같이 삭제할 시 DROP TABLE 학생 CASCADE; | cs |
2. 데이터 조작어
① INSERT : 튜플 삽입
형식
INSERT INTO 테이블명(속성목록) VALUES(상수목록)
INSERT INTO 테이블명(속성목록) SELECT 문 ~
속성목록 작성안하면 테이블의 모든 속성에 값을 삽입한다는 뜻
1 2 3 4 5 6 7 8 9 10 11 12 | --예를 들어 학생 테이블의 학번, 이름 속성에 (9004, 박길동)을 삽입할 시 INSERT INTO 학생(학번, 이름) VALUES(9004, 박길동); --학생 테이블의 각 속성에 대응하여 튜플 (9005, 차길동, 3, 보안)을 삽입할 시 INSERT INTO 학생 VALUES (9005, 차길동, 3, 보안); --학생 테이블에서 전자과 학생의 학번,이름 학년을 검색하여 클래스 테이블로 삽입할 시 INSERT INTO 클래스(학번, 이름, 학년) SELECT 학번,이름,학년 FROM 학생 WHERE 학과 ='전자'; | cs |
② DELETE : 튜플 삭제
형식
DELETE
FROM <테이블명>
[WHERE <조건>]
조건을 기술하지 않으면 테이블의 모든 튜플들이 삭제된다는 뜻
③ UPDATE : 튜플 갱신
형식
UPDATE <테이블명>
SET 속성 = 변경값
[WHERE] <조건>
1 2 3 4 5 6 | --예를 들어 모든 학생의 점수를 1점씩 더할시 UPDATE 학생 SET 점수 = 점수 + 1; --학번이 9003인 학생의 이름을 박지성으로 바꿀시 UPDATE 학생 SET 이름 = '박지성' WHERE 학번 = 9003; | cs |
④ SELECT : 튜플 검색
형식
SELECT [DISTINCT] <속성목록>
FROM <테이블 리스트>
[WHERE <[LIKE] 조건>]
[OREDER BY <속성> [ASC] 또는 [DESC] ]
[GROUP BY <그룹화 필드>]
[HAVING <그룹조건>]
DISTINCT : 중복된 데이터를 제거하고 표시할 때 사용
LIKE : 문자 상수를 명세한 검색 조건
%는 임의개수 문자
_는 한 개의 문자
ORDER BY : 검색결과 에 대해 속성별로 오름차순, 내림차순 지정
GROUP BY : 그룹화 할 기준 속성
HAVING : 그룹들 중에서 선택할 조건 기술
예를 들어 학생 테이블과 성적 테이블이 있다고 할때
학생
학번 |
이름 |
학년 |
학과 |
9001 | 박찬호 | 3 | 전자 |
... | ... | ... | .. |
.
.
성적
학번 |
과목코드 |
중간고사 |
기말고사 |
|
|
|
|
.
.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 | --학생 테이블에서 학과가 '게임'인 학생의 학번, 이름, 학과 검색할시 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; | cs |
**그룹함수**
SUM(속성명) : 지정한 속성값의 합계
AVG(속성명) : 지정한 속성값의 평균
MAX(속성명) : 지정한 속성값 중 최대값
MIN(속성명) : 지정한 속성값 중 최소값
COUNT(속성명) : 지정한 속성의 튜플 값
COUNT(*) : NULL 값도 포함한 모든 튜플 수
3. 데이터 제어어
① GRANT : 특정 데이터와 연산을 특정 사용자만이 수행할 수 있게 권한을 부여
형식
GRANT 권한 ON 데이터 객체 TO 사용자 [WITH GRANT OPTION];
GRANT OPTION : 다른 사용자에게 권한 부여권을 부여
1 2 3 4 5 | --DBA가 사용자1에게 학생 릴레이션에 대한 갱신 권한과 권한 부여권을 부여하는 경우 GRANT UPDATE ON 학생 TO 사용자1 WITH GRANT OPTION; --DBA가 모든 사용자에게 학생 릴레이션 대한 SELECT 권한을 부여하는 경우 GRANT SELECT ON 학생 TO PUBLIC; | cs |
DBA : 데이터베이스관리자 (DataBase Administrator)
데이터 정의어와 데이터 제어어를 사용하여 데이터베이스를 설계하고 관리,운영하는 사용자
② REVOKE : 사용자에게 부여된 권한을 취소
형식
REVOKE 권한 [GRANT OPTION FOR] ON 데이터 객체 FROM 사용자 {CASCADE, RESTRICT};
CASCADE 사용하면 부여된 권한을 연쇄적으로 취소.
RESTRICT 사용하면 하위 권한 없이 사용자만 권한을 취소.
1 2 | --DBA가 사용자2에게 부여한 권한을 취소하는 경우 REVOKE DELETE ON 학생 FROM 사용자2 CASCADE; | cs |
COMMIT과 ROLLBACK은 다음 포스팅에서 트랜잭션을 소개한 후 설명하겠다.