Define on SQL about kinds of Relations

Database Modifications

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

1. Insert tuples into a Relation.
2. Delete certain tuples from a Relation.
3. 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가 어떻게 정의되는지 살펴보자. 

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

Data Types

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

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

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

3. Boolean type
   • BOOLEAN : TRUE, FALSE, UNKNOWN으로 나오는 타입

4. Integer type
   • INT / INTEGER : 정수 값
   • SHORTINT : 작은 정수 값

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

6. 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에 있어 중요한 두 가지는, 

1. WHERE clause는 subquery에서 R을 포함하면 안된다. 
   
2. 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 자체가 불가능하다.

마침.