[Database System] Basic SQL

RDBMS가 수학에 기반한 모델이기에 견고하게 구성되어 있고 모델 뿐 아니라 모델을 잘 활용할 수 있는 인터페이스가 중요했다. 그 인터페이스 중 SQL로 수렴이 됐고 잘 만들어진 언어이다.

 

SQL은 술어논리로 이루어져 있는데 그래서 닫힘 성질을 가지고 있다. operation을 수행했을 때 input과 output이 같은 집합에 속해있다는 것이다.

 

예를 들어 어떤 집합에 SQL 연산을 수행하면 결과 또한 집합이다.

이러한 닫힘 성질 덕분에 operation set 하나만 디자인하면 된다. 만약 결과가 집합이 아니라면 그 결과에 따른 다른 operation set을 만들어야 한다.

1. SQL Data Definition and Data Types

SQL 에서는 Table, Row, Column 용어를 사용하고 relational model에서는 relation, tuple, attribute 용어를 사용한다.

SQL은 스탠다드가 정해져있지만 언어마다 조금 다를 수 있다. SQL에 존재하는 쿼리가 RDBMS에서 어디까지 커버하는지를 확인해야 한다.

 

SQL schema

• 데이터베이스 이름을 의미한다. 데이터베이스 안에는 Table이 존재하고 Table 안에는 record, index 등이 존재한다.
• authorization identifier, descriptors가 각 elements 마다 존재한다.

스키마의 element는 테이블, 제약사항, view, domain 등을 포함한다.

SQL은 세미콜론으로 끝나야 한다.

 

예를 들어 CREATE SCHEMA COMPANY AUTHORIZATION 'Jsmith'; 와 같이 사용한다.

데이터베이스를 만들면 Catalog를 관리한다고 했는데 Catalog는 데이터베이스에 관한 정보가 모두 담겨있다. Catalog 또한 집합 = relation으로 관리된다. 닫힘 성질 덕분에 operation을 여러 개 만들 필요 없이 Catalog와 Sema를 다루는 연산은 하나만 있어도 된다.

닫힘 성질은 operation을 디자인할 때 recursive 하게 적용할 수 있는 굉장히 중요한 성질이다. 잘 알아두자.

 

TABLE을 만드는 방법도 알아보자. table의 이름, attributes, types, initial constraints를 명세해야 한다.

 

CREATE TABLE COMPANY.EMPLOYEE ...

혹은

CREATE TABLE EMPLOYEE ..

 

위와 같이 사용한다. COMPANY는 사용할 데이터베이스인데 후자의 경우는 이미 사전에 어떤 데이터베이스를 사용할지 정의하였기에 생략한 예시이다.

 

Base tables

• base relations
• 실제 테이블을 말한다.
• DB가 파일로 관리되기에 파일에 데이터가 저장되어 있다.
• 학사 관리 테이블을 생각해보면 학생에 대해 민감한 정보가 굉장히 많이 포함되어 있다. 그런데 교수가 학생들에 대한 모든 정보를 봐버려선 안된다. base table에는 모든 정보가 포함되어있기에 base table로는 충분하지 않다.

Virtual relations

• views
• view라고 하는 것은 민감한 정보는 가리고 base table의 일부 정보만 보여주는 것을 말한다.
• view에서 일부만 보여주는 정보가 어디 따로 저장되어 있는 것이 아니라 Base table에서 일부만 꺼내는 것이다.
• 여기서 일부만 꺼내는 과정이 SQL로 꺼내게 되고, 꺼내서 나온 view 또한 Relation이다.
• 즉, view가 따로 데이터를 가지고있는 게 아니라 base table에서 SQL로 일부만 꺼내서 오는 것이고 결과 또한 집합 즉, relation이 된다. 그래서 virtual relation이라 하는 것이다.

CREATE TABLE Example

ER diagram에서 table을 만들게 된다.

이렇게 table을 만들었으면 SQL로 생성해야 한다.

먼저 EMPLOYEE 테이블부터 보자.

VARCHAR : 최대 15자리 CHAR

CHAR(9) : 딱 9자리 CHAR

CHAR : 딱 1자리 CHAR

그림에서 틀린 부분이 있는데, PRIMARY KEY (Ssn),를 끝에 );를 붙여서 세미콜론으로 맺어줘야 한다.

 

DEPARTMENT 테이블을 보면 Dnumber도 PRIMARY KEY인데 Dname도 중복이 되어서는 안되기에 UNIQUE이다. UNIQUE 속성은 사실 candidate key이다.

DEPARTMENT의 Mgr_ssn이 EMPLOYEE의 Ssn을 참조하고 있기에 FORIEN KEY (Mgr_ssn) REFERENCES EMPLOYEE(Ssn) 과 같이 적어준다.

 

그런데 위의 SQL을 보면 EMPLOYEE의 Dno가 DEPARTMENT의 Dnumber를 참조하고 있어야 하는데 그렇지 않다.

왜냐하면 DEPARTMENT가 생성되기 전에 EMPLOYEE를 정의하였기에 DEPARTMENT의 속성을 참조할 수 없고 따라서 일단 정의한 뒤에 ALERT 명령어로 테이블을 바꿔줘야 한다.

 

DEPT_LOCATIONS를 보면 Dnumber, Dlocation 두 개가 PRIMARYY KEY가 된다.

MySQL Example

use company;로 어떤 데이터베이스를 사용할건지 설정하고 명령을 날리면 된다.

show tables; 는 데이터베이스의 Catalog를 보는 명령어이다.

 

 

생성된 테이블은 desc로 볼 수 있다.

Attribute Data Types

Basic data types

• Numeric
  • Integer : INTEGER, INT, SMALLINT
  • Flaoting-point : FLOAT, REAL, DOUBLE PRECISION
• Char
  • Fixed : CHAR(n), CHARACTER(n)
  • Varying : VARCHAR(n), CHAR VARYING(n), CHARACTER VARYING(n)
    • 길이가 고정되지 않은 자료형을 구현하게 되면 레코드 길이가 달라질 수 있어서 하단의 구현이 굉장히 어렵다. 그렇다고 Fixed를 쓰자니 내부 단편화가 걱정된다.
• Bit-string
  • Fixed : BIT(n)
  • Varying length : BIT VARYING(n)
• TRUE, FALSE, NULL
• DATE
  • Components are YEAR, MONTH, DAY in the form YYYY-MM-DD

Additional data types

• Timestamp
• INTERVAL
• DATE, TIME, Timestamp, INTERVAL은 string에서 변환될 수 있다.
• Domain
  • 값의 범위를 정할 수 있다.
  • CREATE DOMAIN SSN_TYPE AS CHAR(9);
• TYPE
  • 사용자 정의 Type을 만들 수 있다.
  • CREATE TYPE

---

2. Specifying Constraints in SQL

Relaional Model은 3 종류의 제약사항, Implict, Explict, Semantic을 가지고 있고 SQL에서 Explict 제약사항 중 4가지 제약 사항을 지원한다.

• Key : PK는 중복되어서는 안된다.
• Entity Integrity : PK는 null이 되어서는 안된다.
• Referential Integrity : FK는 다른 존재하는 엔터티의 PK를 가지거나 null을 가져야 한다.
• Domain : 값의 범위가 domain을 지켜야 한다.

SQL로 제약사항을 지정할 수 있다.

• attribute의 default value를 설정할 수 있다.
  • DEFAULT <value>
  • NULL을 허용하고싶지 않다면 NOT NULL
• CHECK clause
  • Dnumber INT NOT NULL CHECK (Dnumber> 0 AND Dnumber< 21);
  • 데이터를 insertion 할 때 정해진 값의 범위를 check 해주는 기능인데, DBMS에서 처리하게 되면 어플리케이션의 구현이 훨씬 수월해진다.
• PRIMARY KEY clause
  • Dnumber INT PRIMARY KEY;
• UNIQUE clause
  • relational model에서 CANDIDATE key를 의미한다.
  • Dname VARCHAR(15) UNIQUE;
• FOREIGN KEY clause
  • 존재하지 않는 값을 참조하거나 참조되고 있는 값을 바꾸려면 기본적으로 reject 한다.
  • 혹은 잘못된 값이 들어오면 다음과 같이 설정할 수 있다.
    • SET NULL
    • CASCADE
    • SET DEFAULT
  • CASCADE는 전파를 의미한다. FK는 두 테이블이 관계를 가졌을 때 존재하게 되는데 참조하는 값이 바뀌었다면 default는 reject 시키지만 CASCADE는 참조하고 있는 값을 모두 바꿔준다.
• Using the Keyword CONSTRAINT
  • CONSTRAINT에 이름을 부여하는 것이다.

EMPSUPERFK를 보면 Super_ssn이 삭제되면 Super_ssn을 참조하고있는 모든 값을 NULL로 만들고 업데이트된다면 CASCADE로 일괄 다 변경한다.

EMPDEPTFK를 보면 Dno가 삭제되면 DEFALUT로 설정되는데 DEFALUT는 Dno를 선언할 때 1로 해두어서 1로 설정된다.

여기에 있는 쿼리가 무슨 의미인지 알아야하고 짤 수도 있어야 한다.

각 행에 CHECK clause를 적용해서 추가적인 제약을 걸 수 있다.

CHECK clause는 CREATE TABLE statement 가장 끝에 정의하고 각 튜플에 개별로 적용된다. <, > 등을 사용해서 조건을 추가하는 것이다.

• CHECK(Dept_create_date <= Mgr_start_date);

---