[DB개념] :: Define on SQL about kinds of Relations (다양한 릴레이션에 대한 SQL)

IT/Database Concepts 2018. 4. 4. 10:51

Define on SQL about kinds of Relations

Database Modifications

지금까지 결과를 출력하는 query 에 대해 알아보았는데,
이번에는 DB의 State(상태)를 바꾸는 여러가지 작업(Modification)들에 대해 알아보자.

Insert tuples into a Relation.
Delete certain tuples from a Relation.
Update values of certain components of certain existing tuples.

이 세 가지를 'MODIFICATIONS' 라 부른다.

INSERTION

삽입 연산으로, Relation에 tuple을 삽입한다.
INSERT INTO R(C1, C2, ..., Cn) VALUES (V1, V2, ..., Vn);

R : Relation
C : Column
V : Value

Relation의 모든 columns를 포함하지 않으면, Default로 NULL 값이 입력된다.
(Default 값에 대해선 밑에서 다시 다루겠다.)
모든 column에 대해 하는 연산의 경우, List는 생략이 가능하다.
하.지.만 이 경우 column순서나 목록을 정확히 알아야 하기 때문에 주의가 필요하다.
정확히 알지 못하는 경우에는 일일이 명시하도록 하자.

예 :

Movie ( title, year, length, inColor, studioName )

INSERT INTO Movie ( title, year ) VALUES ( 'THOR : Ragnarok', '2017' );

  -> Relation "Movie"에 title이 토르:라그나로크, year이 2017인 tuple을 삽입
     나머지 column ( length, inColor 등등 )의 값들은 Default인 NULL로 처리된다.

또한 삽입할 tuple을 하위쿼리(subquery)로 작성할 수도 있다.

예 :

Movie ( title, year, length, inColor, studioName )
Studio ( name, address )

INSERT INTO Studio ( name )
     SELECT DISTINCT  studioName
     FROM  Movie
     WHERE  studioName NOT IN ( SELECT  name  FROM  Studio );

"Movie"에는 있고, "Studio"에는 없는,
다시말해 "Studio"에서 빼먹은 studio_name이 있으면 그것을 삽입

DELETION

삭제 연산으로, Relation으로부터 tuple을 삭제한다.
DELETE FROM R WHERE < condition >;

R : Relation

INSERT 와는 다르게, 튜플을 지정할 순 없지만 WHERE절에서 조건을 줄 수 있다.

예 :

Students ( s_id, name, dept_name, grade )

DELETE FROM  Students 
WHERE  name = 'Bohyun' AND  dept_name = 'Comp. Sci.';

Relation "Students"에서
이름이 보현이고 학과가 컴퓨터공학인 tuple을 삭제

UPDATE

갱신 연산으로, Relation에 존재하는 tuple의 component를 수정한다.
흔히 '갱신'이라 함은 삽입/삭제와 같은 것을 생각할 수 있지만,
SQL에서의 Update는 따로 정의된 Modification이다.
UPDATE R SET < New-value assignments > WHERE < C >;

R : Relation
New-value assignment : R의 column을 원하는 New-value로 바꿈
C : Condition 

예 :

People ( name, age, gender )

UPDATE  People SET  gender = 'M'
WHERE name  LIKE  '%철수';

Relation "People"에서 이름에 철수가 포함되면 성별을 남성으로 갱신

갱신이 복잡할 경우, CASE 조건문(WHEN, THEN, ELSE, END)을 사용하여
갱신할 수 있다.

예 :

table ( name, id, C1, C2 )

UPDATE  table  SET  C1 = CASE id
				WHEN 1 THEN 2
				WHEN 3 THEN 4
				ELSE C1
				END,
		        C2 = CASE id
				WHEN 4 THEN 10
				WHEN 9 THEN 20
				ELSE C2
				END
	  	  WHERE  id IN ( ~  ( 생략 ) );

Relation "table"에서 C1의 값을 id에 따라 갱신, C2의 값을 id에 따라 갱신     < Figure 1 참조 >

또한 UPDATE를 DELETE & INSERT로 다루기도 한다.

Defining a Relation Schema in SQL

SQL에서 다루는 Relation schema가 어떻게 정의되는지 살펴보자.

Data Type : column을 이루는 Data types
CREATE / DROP / ALTER : Table 생성, 삭제, 변경
Defalut Value
INDEX
View : Virtual relation

Data Types

모든 columns는 Data type을 가지고 있어야 한다.

Character strings ( fixed or varying length )
- CHAR(n) : 길이 n을 갖는 문자열 ( 예 : CHAR(5)의 she -> 'she　' )
- VARCHAR(n) : 최대길이 n을 갖는 문자열

Bit strings ( fixed or varying length )
- BIT(n) : 길이 n을 갖는 비트
- BIT VARYING(n) : 최대길이 n을 갖는 비트

Boolean type
- BOOLEAN : TRUE, FALSE, UNKNOWN으로 나오는 타입

Integer type
- INT / INTEGER : 정수 값
- SHORTINT : 작은 정수 값

Floating-point numbers
- FLOAT / REAL : 소수, 4byte
- DOUBLE PRECISION : 소수, 8byte, 문자열로 저장
- DECIMAL / NUMERIC(n, d) : n개의 10진수, d개의 소수

Dates and Times
- DATA
- TIME
- TIMESTAMP

CREATE / DROP / ALTER

CREATE

CREATE를 통해 simple하게 Relation schema를 선언할 수 있다.
괄호 뒤에 column list와 그들의 type도 선언한다.

예 :

CREATE  TABLE  Member  (
     name  VARCHAR ( 10 ),
     address  VARCHAR ( 50 ),
     gender  CHAR ( 1 ),
     birthday  DATE
);

Relation "Member"를 생성, column은 이름 / 주소 / 성별 / 생년월일

DROP / ALTER

DROP : R은 더이상 Database의 schema가 아니게 되는, 완전한 삭제 연산 (Table을 통으로 삭제)

예 :

T1 ( C1, C2, C3 )                                      T2 ( C4, C5, C6 )
T2 ( C4, C5, C6 )                  ->

DROP  TABLE  T1;

Relation "T1"을 삭제

ALTER : ( ADD / DROP ) column name (ADD의 경우 Data type도 선언)

예 :

Member ( name, address, gender, birthday )

< ADD >
ALTER  TABLE  Member  ADD  phoneNumber CHAR ( 13 );

Relation "Member"에 휴대폰번호 column을 추가


< DROP >
ALTER  TABLE  Member  DROP  birthday;

Relation "Member"에 생년월일 column을 제거

Default Values

Tuple을 생성하거나 modify할 때, 모든 component를 명시하지 않을 때 도 있다.
이 문제를 해결하기 위해 SQL은 NULL 값을 제공한다. 즉, 기본적으로 Default값은 ' NULL ' 값이다.
Data type 뒤에 DEFAULT를 사용하여 원하는 값을 Defalut값으로 지정할 수도 있다.

예 :

CREATE  TABLE  Member  (
     name  VARCHAR ( 10 ),
     address  VARCHAR ( 50 ),
     gender  CHAR ( 1 )  DEFAULT  '?',
     birthday  DATE  DEFAULT  '0000 - 00 - 00'
);

Relation "Member" 생성 시 Data type 뒤에 Default 값을 줌

INDEX

Why we use it?

많은 Tuples을 전부 스캔하여 condition에 맞는, 몇 안되는 Tuples을 찾는 건 굉장히 expensive하다.
즉, Large Relations의 Find 효율성을 위해 INDEX를 부여함으로써 효율적으로 관리할 수 있다.

Definition of INDEX

특정 Relation과 Logical한 연관을 두어, 해당 column을 기준으로 INDEX라는 object를 생성한다.
이러한 Index는 B-tree 등 다양한 기법들이 존재하지만, 우선은 기본적인 것들을 살펴보도록 하자.

인덱스 생성하기

CREATE INDEX Index_name ON R ( C );

R : Relation
C : column

이 때, 1개 이상의 column으로 Index 구성 시, column 순서대로 우선순위가 결정되어 Order한다.

Selection of INDEXes

Index의 Selection은 DB designer에게 Trade-off를 요구하며 실제로
이 선택은 DB 설계가 acceptable한 지 영향을 미치는 중요한 요인 중 하나이다.
두 가지 고려사항은,
1. Index의 존재는 Query속도를 크게 향상시키며 경우에따라 관련된 Join 또한 향상시킬 수 있다.
2. However, 모든 Index는 Modifications (Insertion, Deletion, Update)를 더 복잡하고 느리게 만든다.
그래서, Index selection은 DB 설계에서 가장 어려운 것 중 하나이다.
(modify가 자주 일어나면 그에따라 Index도 계속 바뀌어야 하기 때문)
WHERE절에서 상수와 비교가 자주 일어나는 column이나,
Join condition으로 자주 언급되는 column으로 Index를 선택하는 게 유용하다.
Modification 빈도수에 따라 Index 생성도 고려하는게 맞지만,
그럼에도 불구하고 Index 생성은 무척 효율적이다. (사실 Modification 자체도 굉장히 조심스럽게 해야함)
Index 사용이 full-scan 보다 ~~훠어어얼씬~~ 더 빠르다.

View

Why & Definition

Table을 만들면 그 Table은 modification하지 않는 한
변화가 없는상태로 물리적 공간에 지속적으로 저장되어 있다.
View라는 Relation은 물리적으로 존재치않고 query같은 expression으로 정의된다.
물리적으로 존재하지 않아도 마치 물리적으로 존재하는 것처럼 query받을 수 있으며
일부는 modify도 할 수 있다.

VIEW 생성하기

CREATE VIEW < view-name >  AS  < view-definition >;

Q : Query. It is Definition of View.

Querying Views

View가 참조하는 Relation 내용이 바뀌면, View도 자동으로 갱신된다.

Relations, Tables, and Views
Relation을 구별하기 위해 물리적 : table, 가상 : view로 구분하여 사용한다.
또한 stored되는 Relation임을 강조하고 싶을 땐 'Base Relation'또는 'Base Table' 이라 나타낸다.
View는 영구히 저장되는 것도 아니고, 단지 subquery에 사용되는 일시적인 결과물일 뿐이다.
그래도, Relation 취급을 한다.

Renaming View
- Query에서 View의 column명을 본래의 Table과 구별하기 위해 Renaming 또한 가능하다.

예 :

T1 ( C1, C2 )

CREATE VIEW V1 ( A1, A2 ) AS 
     SELECT  C1, C2
     FROM  T1;

V1 ( A1, A2 )

Relation "T1"을 참조한 View "V1"은, column 이름이 T1의 column과 다르지만, 그 안의 값은 같음

Modifying Views

일단, view에 modify한다는 건 의미가 이상하다.
왜? 이녀석은 단지 참조하는 가상의 Relation일 뿐인데?
Insert의 경우 그 데이터는 어디에 저장되는가?
Answer is -> '할 수 없다' 이다.
하지만, SQL에서의 Updatable View라는(일반 view는 불가) View는 modify 가능하다.
View에 modify를 행하면, 그 결과 역시 Base Table에도 적용 된다.

View modify에 있어 중요한 두 가지는,

WHERE clause는 subquery에서 R을 포함하면 안된다.
SELECT clause의 column list는 view에 삽입된 모든 tuple을 가질만한 충분한 column이 포함되어야 한다.
그래야지만 다른 column에 NULL로 채운다던가, 삽입된 view의 tuple을 base relation으로 가져온다던가 할 수 있다.

Students ( ID, NAME, AGE, DEPT, GRADE )

CREATE  VIEW  V1  AS
   SELECT  NAME, AGE, DEPT
   FROM  Students
   WHERE  GRADE ≥ 3;

Insert

예 :

INSERT  INTO  V1
VALUES  ( '민남기', 30, 'Business Ad' );

Update

예 :

UPDATE  V1
SET  DEPT = 'Legacy'
WHERE  AGE ≥ 25;

Delete

예 :

DELETE  FROM  V1
WHERE  DEPT = 'Legacy';

DROP ( table과 같지만 굳이 언급한 이유는, View가 Updatable이든 아니든 상관없이 가능하기 때문 )

예 :

DROP  VIEW  V1;

DROP Is Powerful Modification
앞서 설명했듯 DROP은 Table에도 가능한데, 이 Table에서 참조된 View가 있다면
그 View마저도 사용불가로 만든다. (물리적 저장을 하지 않았기 때문)

Why Some Views Are Not Updatable?
두 개의 relation에서 생성한 view같은 경우,
Insert하면 빈값들은 NULL이 될텐데, Join연산에서
NULL끼리의 연산은 불가하기 때문에 insertion 자체가 불가능하다.

마침.

저작자표시 비영리

'IT > Database Concepts' 카테고리의 다른 글

[DB개념] :: Representing Data Elements (데이터 엘리먼트의 표현) (0)	2018.04.10
[DB개념] :: Constraints and Triggers (제약과 트리거) (0)	2018.04.06
[DB개념] :: SQL (Structured Query Language, 구조화 질의어) (0)	2018.04.03
[DB개념] :: Extended Operators of Relational Algebra (확장 관계대수) (1)	2018.04.02
[DB개념] :: Relational Algebra (관계대수) (0)	2018.03.29

차트

소소한 일상 C코드 DB 항상 행복하게^-^★