[Spring] RDBMS의 이해

1. RDBMS(Relational Database Management System)란?

 RDBMS는 관계형 데이터베이스를 만들고 업데이트하고 관리하는데 사용하는 프로그램이다. RDBMS의 테이블은 서로 연관되어 있으며 일반 DBMS보다 효율적으로 데이터를 저장, 구성 및 관리할 수 있다. 또한, 정규화를 통해서 데이터의 중복성을 최소화하며 transaction을 수행하는 것이 더 쉽다. 또한, 데이터의 원자성, 일관성, 격리 및 내구성을 유지하며 데이터 무결성을 높인다. 

 

2. 테이블들의 연관 관계

2.1 Unique Key

 Unique Key는 테이블의 열에서 중복될 수 없는 값을 지정한다. Primary Key와 유사하게 두 key 모두 테이블 열의 고유성을 보장한다. 다만, 차이점은 Unique Key는 Primary Key와 다르게 NULL 값을 허용한다. Unique Key가 가지고 있는 특징들은 아래와 같다. 

 

특징 

1. 오직 하나의 NULL 값만 허용한다. 
2. 중복된 값을 가질 수 없다.
3. 다른 테이블의 Foreign Key로 이용될 수 있다. 
4. 한 테이블에서 여러 개의 Unique Key를 가질 수 있다. 

 

예시

 아래는 CUSTOMERS라는 이름의 테이블을 생성하는 것이다. 이 테이블은 ID, NAME, AGE, ADDRESS, SALARY라는 다섯 개의 필드를 가지고 있고, 이 중 ID를 Unique Key로 설정하고 있다. 

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL UNIQUE KEY,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2)
);

 

2.2 Primary Key

 Primary Key는 테이블의 각 행을 유일하게 식별해주는 key이다. 이는 데이터를 빠르게 접근하는 것을 도와주고 테이블 간의 관계를 형성하는 역할을 한다. 비록 하나의 테이블에는 하나의 Primary Key만 존재할 수 있지만, 이는 하나 이상의 필드에서 정의될 수 있다. Primary Key가 테이블의 여러 필드에 대해서 생성된 경우 Composite Key라고 부른다. 

 

특징

• 고유한 값을 가진다. 
• NULL 값을 가질 수 없다. 
• 한 테이블에는 오직 하나의 Primary Key만 가질 수 있다. 
• Primary Key의 길이는 900 byte를 넘어갈 수 없다. 

 

예시

 아래는 CUSTOMERS라는 이름의 테이블을 생성하고, 그 안에 여러 필드를 지정하는 코드이다. 이때 ID라는 열을 Primary Key로 추가한 코드이다. 

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),       
   PRIMARY KEY (ID)
);

 

1) 이미 있는 열을 Primary Key로 지정하려는 경우

// ALTER TABLE table_name ADD CONSTRAINT PRIMARY KEY (column_name);
ALTER TABLE CUSTOMERS ADD CONSTRAINT PRIMARY KEY(NAME);

 

2) 지정되어 있는 Primary Key를 해제하려는 경우

// ALTER TABLE table_name DROP PRIMARY KEY;
ALTER TABLE CUSTOMERS DROP PRIMARY KEY;

 

2.3 Foreign Key

 Foreign Key는 한 테이블의 열 중 다른 테이블의 Primary Key와 매칭되는 key이다. 즉, 두 개의 테이블을 연결시켜주는 역할을 한다. 

 

특징

• Foreign Key는 테이블에서의 중복을 줄이기 위해서 이용된다. 
• Foreign Key는 데이터를 여러 개의 테이블에 정규화하는데 도움을 준다. 

 

예시

 CUSTORMERS와 ORDERS라는 테이블 두 개를 생선한다. 이때 CUSTOMERS 테이블의 CUSTOMER_ID를 Foreign Key로 이용한다. 

CREATE TABLE CUSTOMERS(
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25) ,
   SALARY DECIMAL (18, 2),       
   PRIMARY KEY (ID)
);

CREATE TABLE ORDERS (
   ID INT NOT NULL,
   DATE DATETIME, 
   CUSTOMER_ID INT,
   CONSTRAINT FK_CUSTOMER 
   FOREIGN KEY(CUSTOMER_ID) 
   REFERENCES CUSTOMERS(ID),
   AMOUNT DECIMAL,
   PRIMARY KEY (ID)
);

 

1) 이미 있는 열을 Foreign Key로 지정하려는 경우

ALTER TABLE ORDERS 
ADD CONSTRAINT FK_ORDERS 
FOREIGN KEY(ID) 
REFERENCES CUSTOMERS(ID);

 

2) 지정되어 있는 Foreign Key를 해제하려는 경우

ALTER TABLE ORDERS DROP FOREIGN KEY FK_ORDERS;

 

3. Indexing

3.1 Indexes

 SQL에서 indexes는 데이터 추출을 최적화하기 위해 사용되는 특별한 조회 테이블이다. 데이터베이스 테이블에서 데이터를 가리키는 포인터를 저장하여 필요한 레코드를 빠르게 찾을 수 있도록 한다. 

 

3.2 Create Index

CREATE INDEX index_name 
ON table_name (column_name1, column_name2,... column_nameN);

 

Indexes의 종류

1) Unique Index : 테이블에 중복된 값이 들어오는 것을 방지한다. 

CREATE UNIQUE INDEX index_name
on table_name (column_name);

 

2) Single-Column Index : 하나의 테이블에 생성된다. 

CREATE INDEX index_name
ON table_name (column_name);

 

 

3) Composite Index : Index가 두 개 또는 그 이상의 테이블에 생성된다. 

CREATE INDEX index_name
ON table_name (column_name);

 

4) Implicit Index : 객체가 생성되었을 때 데이터베이스 서버에 의해서 자동적으로 생성되는 index이다. 

 

3.3 Drop Index

DROP INDEX index_name ON table_name;

 

1) If가 존재하는 경우

DROP INDEX IF EXISTS index_name
ON table_name;

 

2) Primary Key 또는 Unique에 의해 생성된 index를 삭제하는 경우

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

 

3.4 Show Indexes

 SHOW INDEX는 테이블에 정의된 정보를 불러오기 위한 statement이다. 그러나, 해당 statement는 MySQL RDBMS에서만 작동하고 SQL Server에서는 작동하지 않는다. 

SHOW INDEX FROM table_name;

 

3.5 Unique Index

 Unique Index는 indexed columns 내에서 중복된 행이 생기는 것을 방지한다. 이는 CREATE UNIQUE INDEX statement를 통해서 하나 또는 그 이상의 열에 생성될 수 있다. 

CREATE UNIQUE INDEX index_name
ON table_name (column1, column2, ..., columnN);

 

4. Transaction

 Transaction은 데이터베이스의 상태를 변화시키기 위해서 수행하는 작업의 단위이다. 

 

4.1 특징

• 원자성 (Atomicity) : Transaction이 데이터베이스에 모두 반영되거나 전혀 반영되지 않아야 함
• 일관성 (Consistency) : Transaction의 작업 처리 결과가 항상 일관성이 있어야 함
• 독립성 (Isolation) : Transaction 수행 시에는 다른 Transaction의 연산이 끼어들 수 없음
• 지속성 (Durability) : Transaction이 성공적으로 완료되었을 경우, 결과가 영구적으로 반영되어야 함 

 

4.2 Transactional Control Commands

1) COMMIT 명령어 : Transaction에 의해서 생긴 변화를 데이터베이스에 반영시키기 위한 명령어

 아래와 같은 CUSTOMERS 테이블에서 age가 25인 기록을 삭제하고 변화를 COMMIT하면 삭제 요청이 반영된 것을 확인할 수 있다. 

+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+

SQL> DELETE FROM CUSTOMERS
   WHERE AGE = 25;
SQL> COMMIT;

 

Output

+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+

 

2) ROLLBACK 명령어 : 아직 데이터베이스에 저장되지 않은 명령을 되돌리기, 마지막 COMMIT 이후 모든 변경 사항을 취소하고 이전 상태로 되돌아감 

 위와 동일한 CUSTOMERS 테이블에서 아래와 같이 age가 25인 사람을 삭제하는 명렁어를 입력한 뒤에 ROLLBACK을 하면 이전으로 돌아가서 삭제 행위는 일어나지 않을 것이다. 

SQL> DELETE FROM CUSTOMERS
   WHERE AGE = 25;
SQL> ROLLBACK;

 

Output

+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+

 

3) SAVEPOINT 명령어 : Transaction을 특정 시점으로 되돌릴 수 있게 하는 포인트 

 위와 동일한 CUSTOMERS 테이블에서 아래와 같이 3번의 삭제 명령이 일어날 때마다 SAVEPOINT로 지정해준다. 그 후 두 번째 SAVEPOINT로 돌아가는 명령어를 입력하면 아래와 같은 결과가 나온다. 

SQL> SAVEPOINT SP1;
Savepoint created.
SQL> DELETE FROM CUSTOMERS WHERE ID=1;
1 row deleted.
SQL> SAVEPOINT SP2;
Savepoint created.
SQL> DELETE FROM CUSTOMERS WHERE ID=2;
1 row deleted.
SQL> SAVEPOINT SP3;
Savepoint created.
SQL> DELETE FROM CUSTOMERS WHERE ID=3;
1 row deleted.

SQL> ROLLBACK TO SP2;
Rollback complete.

 

Output

SQL> SELECT * FROM CUSTOMERS;
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+
6 rows selected.

 

4) SET TRANSACTION 명령어 : 데이터베이스 transaction을 초기화하는 명령어, Transaction의 특성을 지정할 수 있음 

SET TRANSACTION [ READ WRITE | READ ONLY ];

---

참고 자료

https://velog.io/@sysop/DBMS-%EC%99%80-RDBMS%EC%9D%98-%EA%B0%9C%EB%85%90 

https://cocoon1787.tistory.com/808