[ DB ] 데이터베이스...5(SQL...1)

본 포스팅은 인하대학교 최원익교수님의 "데이터베이스설계" 수업에서 진행한 프론트, 백엔드 실습 관련 정리하는 포스팅입니다. 백엔드, 프론트엔드는 전문 분야가 아니기에 공부용으로 올리는 포스팅이며 오류사항이 있을 수 있습니다.

 

https://jinwoo-jung.tistory.com/103

[ DB ] 데이터베이스...4(MySQL) - 작성중

 

---

 

다양한 SQL문을 실습 해 보자.

 

• Database 생성

 

show databases; 를 통해 현재 생성된 Database의 목록을 볼 수 있다. 이때, 자동으로 생성되어 있는 information_schema Database는 Meta Data인데, 다른 데이터를 설명해 주는 데이터에 관해 구조화된 데이터이다. 

 

새로운 Database를 생성하기 위해서는 create database DB이름; 으로 생성할 수 있다. 특정 Database에서 Table 생성, Data 추가 등의 작업을 위해서는 use DB이름; 을 통해 해당 Database로 이동해야 한다.

 

• CREATE TABLE

새로운 Relation을 생성하기 위해서는 CREATE TABLE을 사용해야 한다. Relation, Attribute, Value 집합, Not Null 조건 등이 명시되며, Key, 참조 무결성, Entity 무결성 등의 제약 조건 역시 명시된다.

 

새로운 Relation을 생성하는 것은 위와 같다. CREATE TABLE TABEL이름 (); 의 Format을 가진다. ()안에는, 테이블에 드러가는 Attribute, Data Type, NOT NULL 제약조건(빨간색 박스)이 먼저 명시되고, 이후  Key, 참조 무결성, Entity 무결성 등의 제약 조건(노란색)이 명시된다. EMPLOYEE Table의 경우, Ssn을 Primary Key로 가지며, EMPLOYEE Table의 PK인 Ssn을 참조하는 Foreign Key Super_ssn을 가짐을 확인할 수 있다. 

 

이러한 키, 엔티티/참조 무결성 제약조건의 경우 Table을 선언하는데 명시 할 수도 있지만, 각 Table의 관계에 따라 Table 생성 이후 진행될 수도 있다. 

 

 

우리가 생성한 Employee Table이다. 앞서 생성 시에는 Attribute를 간략화 해서 약간의 차이는 있다. 현재 Dno Attribute는 다른 Table의 Attribute를 참조함을 확인할 수 있다. 따라서 현재는 EMPLOYEE Table만 생성하는 과정이기에, 참조한 Attribute를 가지는 Table이 존재하지 않아 EMPLOYEE Table 생성 시 이를 명시할 수 없다. 

 

현재 EMPLOYEE Table이 정상적으로 생성되었음을 확인할 수 있으며, desc 명령어를 통해 해당 테이블의 정보를 확인할 수 있다. desc Table이름; 의 결과 앞서 정의한 각 Attribute와 DataType, NOT NULL 제약조건이 정상적으로 적용되었음을 확인할 수 있다. 

 

 

반면, DEPARTMENT Table은 생성 시 EMPLOYEE Table의 Ssn을 참조하는 Foreign Key Mgr_ssn을 명시하였다. 이미 EMPLOYEE Table이 생성되어 있었기에 정상 동작한다.

 

• ALTER TABLE

만약 테이블을 생성한 뒤, 추가적인 외래키 설정을 원하는 ALTER TABLE을 활용하면 된다. 

alter table Table이름 add foreign key(Field명), references 참조하는Table이름( Field명 ); 를 통해 설정할 수 있다.

 

• CREATE DOMAIN

CREATE DOMAIN은 C++ 언어에서 typedef와 같이, 새로운 도메인(Data Type)을 생성하는데 사용된다. 새롭게 생성한 도메인을 Table 생성 시 Data Type으로 사용할 수 있다. MySQL에서는 지원하지 않는다.

 

• NOT NULL / DEFAULT

기본적으로 Primary Key를 제외한 Attribute는 NULL을 허용한다. 만약 NULL을 제한하고 싶으면, 아래 tmp Table의 ID Attribute와 같이 NOT NULL 제약조건을 지정하면 된다. 

 

만약 NULL을 허용 할 경우, DEFAULT문을 통해 Default Value를 설정할 수 있다. 아래 MAJOR Attribute의 경우 DEFAULT로 'MATH'가 설정되어 tmp Table 설명 시 Default Value가 설정됨을 확인할 수 있다. 

 

• INSERT

생성한 Table에 Tuple을 추가할 때는 INSERT문을 이용한다. 이때, 삽입하는 Tuple Value는 Table 생성 시 지정한 Attribute의 순서와 동일해야 하며, 순서를 다르게 할 경우, Attribute의 이름을 명시해야 한다. 이때, 명시하지 않으면 NULL이나 DEFAULT Value를 가진다.

 

 

INSERT INTO [TABLE 이름]
VALUES (값);

 

위와 같은 구조로 Tuple을 삽입하면 된다. NAME, MAJOR Attribute는 NULL을 허용한다. 따라서 ID가 2인 Tuple의 경우 정상적으로 NULL이 할당되었음을 확인할 수 있다. 만약, Table의 특정 Attribute의 값만 입력하고 싶으면, ID가 3인 Tuple과 같이 Table이름 뒤에 ()안에 입력할 Attribute를 기입하면 된다. 이때, 기입되지 않은 Attribute는 자동으로 NULL 혹은 DEFAULT Value를 가진다. 

 

• 참조 무결성 제약조건 : PRIMARY KEY

릴레이션은 하나 이상의 Primary Key를 구성하는 Attribute가 존재해야 한다. Primary Key는 아래의 SSN Attribute와 같이 PRIMARY KEY(Feild명) 으로 설정할 수 있다.

• 참조 무결성 제약조건 : UNIQUE

UNIQUE의 경우 대체키(or 보조키)를 명시하는 것으로, PK 처럼 유일성이 추가된다. 예를들어, 아래와 같이 ID가 PK 이지만, DNAME Attribute 역시 유일성이 보장된다면 UNIQUE로 설정할 수 있다. 

 

• 참조 무결성 제약조건 : FOREIGN KEY

Foreign Key는 한 Table의 Collumn이 다른 Table의 Primary Key를 참조하는 것을 의미한다. 이때, Database의 일관성을 유지하기 위해 외래케 제약 조건을 설정하는 것을 참조 무결성이라 한다. 외래키 설정 과정에서 만약 참조 무결성의 위반 시 취할 동작을 명시할 수 있다.

 

위반의 종류는 다음과 같다.

1. ON DELETE : 부모 데이터가 삭제되는 경우
2. ON UPDATE : 부모 데이터가 UPDATE(변형)되는 경우

 

그에따라 동작할 수 있는 행동은 크게 다음과 같다.

1. SET NULL : 자식 테이블의 참조 Collumn을 NULL로 UPDATE
2. SET DEFAULT : 자식 테이블의 참조 Collumn을 Default Value로 UPDATE
3. CASCADE : 자식 데이터도 동일하게 수행

아래와 같은 구조의 두 Table이 있다. EMPLOYEE Table의 일부 Attribute는 생략했다. 

 

각 Table에 입력된 Tuples는 다음과 같다.

 

---

 

앞서 우리는 두 Table을 생성하면서 Foreign Key를 설정하였다. Employee Table의 Super_ssn은 Employee Table의 Ssn을 참조한다. 또한, Employee Table의 Dno는 Department Table의 Dnumber를 참조한다. 그리고 Department Table의 Mgr_ssn은 Employee Table의 Ssn을 참조한다. 이러한 관계에서 Tuple을 추가하려면 어떠한 데이터도 추가할 수 없다. Fname이 James인 Tuple을 제외하고는 모두 Super_ssn을 가지기에, Super_ssn을 Ssn으로 하는 Tuple이 먼저 정의되어야 한다. 그렇다고 Fname이 James인 Tuple을 입력하기에는 Dno는 Department Table을 참조하고 있는 복잡한 관계이다. 

 

따라서, 먼저 Employee Table만 생성 후 다음과 같이 Tuples를 입력한다. 

-- EMPLOYEE
INSERT INTO `temp`.`employee` (`Fname`, `Ssn`, `Dno`) VALUES
('James', '888665555', 1);

INSERT INTO `temp`.`employee` (`Fname`, `Ssn`, `Dno`, `Super_ssn`) VALUES
('Franklin', '333445555', 5, '888665555'),
('Jennifer', '987654321', 4, '888665555'),
('John', '123456789', 5, '333445555'),
('Ramesh', '666884444', 5, '333445555'),
('Joyce', '453453453', 5, '333445555'),
('Alicia', '999887777', 4, '987654321'),
('Ahmad', '987987987', 4, '987654321');

 

이후, Department Table을 생성하고 Tuples를 입력한다.

-- DEPARTMENT
INSERT INTO `temp`.`Department` (`Dname`, `Dnumber`, `Mgr_ssn`, `Mgr_start_date`) VALUES
('Research', 5, '333445555', '1988-05-22'),
('Administration', 4, '987654321', '1955-01-01'),
('Headquaters', 1, '888665555', '1981-06-19');

 

이후 Employee Table의 Ssn과 Department Table의 Mgr_ssn의 Foreign Key를 ALTER문으로 입력한다.

---

 

만약 Employee Table의 특정 Tuple의 Ssn이 제거되면, 해당 Tuple의 Ssn을 Super_ssn으로 하는 Employee Table의 Tuples의 Super_ssn은 NULL로 변하며, Department Table의 Tuples의 Mgr_ssn은 Default Value인 "888665555"으로 변함을 확인할 수 있다. 

 

하지만 앞서 정의한 제약조건의 경우 몇가지 문제사항이 있다.

• MySQL은 SET DEFAULT를 지원하지 않는다.

따라서, 해당 제약조건을 SET NULL로 변환하였다. 

ALTER TABLE department
DROP FOREIGN KEY DEPTMGRFK;

ALTER TABLE department
ADD CONSTRAINT DEPTMGRFK
FOREIGN KEY (Mgr_ssn) REFERENCES employee(Ssn)
ON DELETE CASCADE
ON UPDATE CASCADE;

 

그럼에도 발생하는 문제사항은 다음과 같다. 

• Employee Table의 Dno - Department Table의 Dnumber이 ON DELETE/ON UPDATE에서의 제약조건이 구현 안되어있다. 

만약, 만약 Employee Table의 특정 Tuple의 Ssn이 제거되면, 해당 Tuple의 Ssn을 Super_ssn으로 하는 Employee Table의 Tuples은 제거되며, Department Table의 Tuples도 제거된다. 이때, Department Talbe에서 삭제되는 Tuples를 참조하는 Employee Table에 대한 처리가 되어야 하는데 정의되지 않아서 오류가 발생한다.

 

따라서, ON DELETE에 대한 제약조건을 추가 해 주었다. 

ALTER TABLE employee
DROP FOREIGN KEY employee_ibfk_2;

ALTER TABLE employee
ADD CONSTRAINT employee_ibfk_2
FOREIGN KEY (Dno) REFERENCES department(Dnumber)
ON DELETE CASCADE;

 

그러면 이제 특정 Tuple을 삭제 해 보자.

 

Employee Table에서 PK인 Ssn이 '333445555'인 Tuple을 제거하였다. 

 

 

생성한 제약조건에 근거하여 각 Table에서 일부 Tuple이 적절히 삭제되었음을 확인할 수 있다.