관리자 계정 mysql은 root가 기본 계정이다. postgresql은 postgres
데이터베이스가 작거나, DBA가 없을 때, 어플리케이션 개발자가 DB 권한 관리도 해야할 때 사용할 수도 있다. 대부분 분업되어 있지만, 사용할 가능성도있다.
DCL(Data Control Language)
SQL문의 하위 집합으로
- 데이터베이스를 다룰 수 있는 권한을 부여하거나 박탈한다.
- 트랜잭션(Transction)을 관리한다. 트랜잭션이란 하나의 기능을 수행하기 위한 하나의 논리적인 작업 단위이다.
REVOKE, GRANT
데이터 접근 권한을 제어하기 위한 SQL
REVOKE
REVOKE [권한] ON [테이블명] FROM [권한을 박탈할 사용자]- 슈퍼유저(관리자) 권한을 가진 postgres라는 계정으로 test_db에 접속
\du명령어로 사용자 목록을 확인- 관리자 계정으로 테스트를 위한 test_user라는 임시 사용자를 생성(
\du명령어로 test_user 라는 사용자가 생성되었음을 확인) - REVOKE 구문을 이용해 students라는 테이블에서 SELECT를 할 수 없도록 권한을 회수(REVOKE 구문에서 PUBLIC은 모든 사용자를 의미 (관리자 제외)
- test_user 사용자로 접속하기 위해 psql을 하나 더 실행
- test_user 계정으로 students 테이블을 SELECT하면 권한이 없다는 오류가 발생
GRANT
모든 사용자에게 students 테이블에 SELECT를 할 수 있도록 권한을 부여
GRANT [권한] ON [테이블명] TO [권한을 받을 사용자]- test_user 계정으로 접속 후 students 테이블에 SELECT 쿼리를 사용할 수 있는지 확인
GRANT, REVOKE에서 부여하고 박탈할 수 있는 권한의 종류
| SELECT | 조회 권한 |
|---|---|
| UPDATE | 수정 권한 |
| INSERT | 삽입 권한 |
| DELETE | 삭제 권한 |
| ALL | 모든 권한 |
COMMIT, ROLLBACK
트랜잭션(Transaction)을 관리하기위해 사용
트랜잭션
기능을 수행하기 위한 하나의 작업 단위, 세션은 데이터베이스에 연결된 상태를 의미한다. 2개의 사용자가 데이터에 접근한다는 것과 같음 → 2개 열어둠
COMMIT
트랜잭션을 최종적으로 모든 세션에 반영하기 위한 SQL 문
COMMIT;AUTO COMMIT
BEGIN; -- 트랜잭션 시작
INSERT inro students VALUES();
COMMIT;
END;
자동 커밋 설정 버튼
기본적으로
- DML : 사용자 수동 커밋
- DDL : 자동 커밋
되어 있다.
사용자 수동 커밋 모드에서는
INSERT INRO students
VALUES(1, "JO");위 쿼리를 실행했다고 했을 때 ROLLBACK 을 사용하여 원상복구가 가능하지만, 자동 커밋 모드에서는 원상 복구가 불가하다. 앞에 있던 DML이 실행되기 위해서는 COMMIT으로 저장을 해야 하고, 원상 복구를 할 때는 ROLLBACK을 통하여 진행하면 된다. 테이블을 생성했을 때 DDL 관련된 명령어들은 AUTO COMMIT 이기 때문에 ROLLBACK 이 불가하다. 커밋, 롤백 명령어들이 의미가 없다. DDL에서는 명령어를 수행하는 순간 자동 커밋이 되기 때문이다.
ROLLBACK
현재 트랜잭션을 반영하지 않고 되돌리는 명령어
ROLLBACK;BEGIN;
INSERT INTO accounts(name, balance) VALUES('B', 10000);
ROLLBACK;데이터 모델링과 ERD
정규화
데이블의 구조를 효율적으로 작성할 수 있도록 데이터의 구조를 최적화하는 과정이며 데이터의 중복을 최소화하는데 목적이 있다.
- 1정규화 → 1정규형 테이블
- 2정규화 → 2정규형 테이블
- 3정규화 → 3정규형 테이블
이후에도 다양한 정규화 방식들이 있지만, 실제로는 대부분 3정규화까지 진행한다.
정규화의 장점
데이터를 관리하기 쉬워지고, 새로운 요구 사항에 유연하게 대응할 수 있고, 공간의 비용을 절감하기 위해 사용한다.
- 일관성
정규화된 테이블은 각 테이블이 하나의 목적을 가지고 있기 때문에 데이터를 더 쉽게 관리할 수 있다. 데이터를 수정하거나 삭제할 때 오류가 발생할 가능성이 줄어들고, 데이터의 일관성을 유지하기가 더 쉬워진다.
- 유연성
데이터 구조를 변경하거나 새로운 요구 사항을 쉽게 수용할 수 있다. 새로운 속성이나 테이블을 추가하는데 더 유연하며, 변경 사항이 다른 부분에 미치는 영향을 최소화한다.
- 저장 공간 최적화
정규화로 저장 공간을 효율적으로 사용할 수 있다. 중복 데이터가 제거되므로 데이터 크기를 줄일 수 있고, 저장 공간과 관련된 비용을 절감할 수 있다.
정규화의 종류
제 1 정규화(1NF)
각 열(column)이 하나의 값을 가지게 하는 과정, 하나의 열에 여러 개의 값이 들어가면 안된다.
예상치 못하게 여러 개의 row를 변경시키게 될 경우가 생기기 때문이다.
원본데이터
| 주문번호 | 제품명 | 제품구성 |
|---|---|---|
| 1 | 노트북 | CPU, RAM, SSD |
| 2 | 스마트폰 | CPU, RAM, 배터리 |
| 3 | 스마트워치 | CPU, 배터리 |
정규화된 데이터 (제 1 정규형)
| 주문번호 | 제품명 | 제품구성 |
|---|---|---|
| 1 | 노트북 | CPU |
| 1 | 노트북 | RAM |
| 1 | 노트북 | SSD |
| 2 | 스마트폰 | CPU |
| 2 | 스마트폰 | RAM |
| 2 | 스마트폰 | 배터리 |
| 3 | 스마트워치 | CPU |
| 3 | 스마트워치 | 배터리 |
1정규화까지 진행된 1정규형 테이블은 아직 좋은 설계는 아니다.
- 반복되는 요소들이 있기 때문
- pk설정이 안되어 있다
- 주문번호와 제품들은 다른 부분
제 2 정규화(2NF)
테이블의 비주요 속성이 기본키에 대해 완전 함수에 종속되도록 하는 것으로 모든 열이 기본키(Primary key)에 의존해야 한다. 현재 테이블의 주제와 관련없는 컬럼을 다른 테이블로 분리하는 작업이다.
원본 데이터
| 주문번호 | 제품명 | 제조사 |
|---|---|---|
| 1 | 노트북 | 삼성전자 |
| 2 | 스마트폰 | 엘지전자 |
| 3 | 스마트워치 | 애플 |
정규화된 데이터 (제 2 정규형)
- 주문 테이블
| 주문번호 | 제품명 |
|---|---|
| 1 | 노트북 |
| 2 | 스마트폰 |
| 3 | 스마트워치 |
- 제품 테이블
| 제품명 | 제조사 |
|---|---|
| 노트북 | 삼성전자 |
| 스마트폰 | 엘지전자 |
| 스마트워치 | 애플 |
하나의 테이블은 하나의 주제를 가지도록 테이블을 분리, 완전 함수 종속도 만족하는데, 예를 들어 제품 테이블에서는 제품명이 기본키(primary key)이며, 다른 열(제조사)은 제품명에 종속된다.
제 3 정규화 (3NF)
제2 정규화를 완료한 테이블에서 이행적 함수 종속성을 제거하는 과정이다. 이행적 함수 종속이란 A -> B 종속, B -> C 종속, A -> C 종속인 관계를 말하며 즉, 일반 컬럼에만 종속된 컬럼은 다른 테이블로 빼는 것이다.
원본 데이터
| 주문번호 | 고객명 | 주소 | 제품명 | 제품 카테고리 |
|---|---|---|---|---|
| 1 | 홍길사 | 서울 | 노트북 | 전자제품 |
| 2 | 홍길삼 | 부산 | 스마트폰 | 전자제품 |
| 3 | 홍길이 | 제주 | 카메라 | 전자제품 |
- 주소 → 고객 종속
- 고객 → 주문번호 종속 (주소가 주문번호에도 종속됨)
- 제품 카테고리 → 제품명 종속
- 제품명 → 주문번호 종속(제품 카테고리도 종속됨)
이러한 종속 관계를 끊어내기위해 주문과 제품 정보는 별도의 테이블로 분리한다.
정규화된 데이터
- 주문 테이블 (고객명 pk 1, 2. 3)
| 주문번호 | 고객명 | 제품명 |
|---|---|---|
| 1 | 홍길사 | 노트북 |
| 2 | 홍길삼 | 스마트폰 |
| 3 | 홍길이 | 카메라 |
- 제품 테이블 (제품명 pk 1, 2. 3)
| 제품명 | 제품 카테고리 |
|---|---|
| 노트북 | 전자제품 |
| 스마트폰 | 전자제품 |
| 카메라 | 전자제품 |
- 고객 테이블 (고객명 pk 1, 2. 3)
| 고객명 | 주소 |
|---|---|
| 홍길사 | 서울 |
| 홍길삼 | 부산 |
| 홍길이 | 제주 |
각 테이블이 하나의 주제를 가지도록 분리한다.
서비스 성격마다 정규화를 할 수도 있고 안할 수도 있으나, 대부분 정규화를 진행한다.
역정규화를 하는 경우도 있는데 로그를 남기고, 출력하는 히스토리를 남기고 싶을 때가 있다. 정규화를 하게 되면 JOIN 을 사용하는 경우가 많아진다. 각각 다른 컬럼의 값을 한번에 출력하고 싶을 때.
데이터 모델링
데이터베이스를 구축하기 위해 데이터의 구조와 관계를 시각적으로 표현하는 것
여러 개의 테이블이 존재하고 테이블 사이의 관계를 시각적으로 표현한 예시로
데이터 모델링의 장점은
- 데이터의 전체적인 구조를 파악하는데 도움이 된다.
- 협업하는 동료들과의 소통할 때 유용하다.
개념적 모델링
비즈니스 요구 사항을 파악하고 이를 데이터 모델로 표현하는 단계, 데이터를 의미 있는 단위로 분류하고 관계를 정의한다.
회색의 사각형은 개체(테이블), 파랑색의 마름모는 관계를 나타낸다. 회원 정보와 주문 정보는 주문이라는 관계를 가지고 있음을 확인할 수 있다.
논리적 모델링
개념적 모델을 기반으로 데이터의 구조와 관계를 구체화하는 단계로 개념적 모델보다 구체화된 부분으로는 각 테이블의 필드 정보, 테이블 간의 관계 (1:1, 1:N, N:M)가 있다.
위의 예시에서 member와 order는 1:N 관계,한 명의 회원이 여러 개의 주문을 할 수 있다는 의미이며, member와 order 테이블 양쪽에 모두 member_id 필드가 존재함을 주목한다. 두 테이블의 관계는 member_id라는 key로 맺어져 있음을 알 수 있다.
order와 item은 1:1 관계로 하나의 상품만 주문이 가능하다는 의미, item과 order 테이블 양쪽에 item_id 필드가 존재함을 주목한다. 두 테이블은 item_id 필드로 관계가 맺어짐을 알 수 있다.
ERD를 논리적 모델링 단계에서도 작성할 수 있다. 그림을 그려보면서 모델링이 완료되면 물리적 모델링을 하게 된다.
물리적 모델링
논리적 모델을 기반으로 실제 데이터베이스로 구현하는 단계, 시각적으로 표현했던 모델을 SQL문을 통해 실제 테이블로 만든다.
CREATE TABLE `member` (
`member_id` VARCHAR(30) NOT NULL,
`email` VARCHAR(30) NULL
);
CREATE TABLE `order` (
`order_id` VARCHAR(30) NOT NULL,
`member_id` VARCHAR(30) NOT NULL,
`item_id` VARCHAR(30) NOT NULL
);
CREATE TABLE `item` (
`item_id` VARCHAR(30) NOT NULL,
`item_name` VARCHAR(30) NOT NULL
);데이터 모델링의 과정은
- 요구 사항이 무엇인지 분석
- 요구 사항을 충족하기 위해 필요한 데이터를 정하고, 그들의 관계를 설정하는 개념적 모델링
- 개념적 모델을 구체적인 표로 만드는 논리적 모델링
- 실제 데이터베이스 테이블로 만드는 물리적 모델링
순서로 진행된다.
ERD 그리기
ERD란?
Entity-Relationship Diagram(개체-관계 다이어그램)의 약자로 개체(Entity) 간의 관계(Relationship)를 시각적으로 표현할 때 사용한다. 논리적 모델링 방법 중 하나로 개념적 모델을 기반으로 데이터의 구조와 관계를 구체화하는 단계이다. 실제 데이터베이스를 구현하기 전에 데이터 요구 사항을 이해하고 문서화하는데 사용한다.
ERD의 구성 요소
학생(student), 수강(enrollment) 개체가 있고 각 개체는 고유의 속성을 가진다. 두 개체가 선으로 연결되어 있음을 알 수 있는데 이는 관계를 표현한 것
- 개체(Entity)
개체는 고유하게 식별할 수 있는 사물, 개념, 사건 등을 의미
예를 들어 사용자, 제품, 주문 등이 개체가 될 수 있다.
개체는 데이터베이스에서 테이블로 표현된다.
- 속성(Attribute)
속성은 개체의 특징이나 성질을 나타낸다. 하나의 개체는 여러 개의 속성을 가질 수 있다.
예를 들어, 학생 개체의 속성으로는 학번, 이름, 주소 등이 있을 수 있다.
속성은 데이터베이스에서 테이블의 column(열)로 표현된다.
- 관계(Relationship)
관계는 개체 간의 연결을 나타낸다. 관계에는 일대일(1:1), 일대다(1:N), 다대다(N:M) 유형이 있다.
예를 들어, 학생이라는 개체와 수강이라는 개체가 있다고 가정했을 때, 한 학생이 여러 강의를 수강할 수 있으므로 학생과 수강 간의 관계는 일대다(1:N)라고 할 수 있다.
관계(Relationship) 표현 방법
일대일(1:1) 관계 : 한 개체가 다른 개체와 일대일로 연결된다. 학생(student)과 신체정보(physical_info)는 일대일 관계이며, 한 명의 학생은 하나의 신체 정보를 가진다.
일대다(1:N) 관계 : 한 개체가 다른 여러 개체와 연결된다. 한 명의 학생(student)은 여러 개의 취미(hobby)를 가질 수 있다. (선이 여러개로 갈라진 것은 학생과 취미의 관계가 1:N인 것을 표현한다.)
다대다(N:M) 관계 : 여러 개체가 여러 개체와 연결, 제품(product)과 제조업체(manufacturer)의 관계를 말할 수 있다. TV라는 제품은 여러 제조업체에서 만들 수 있고, 삼성이라는 제조업체에서는 TV뿐만 아니라 냉장고, 세탁기 등의 다양한 제품을 만들 수 있다. 하나의 제품이 여러 제조업체와 관계가 있고, 하나의 제조업체가 여러 제품과 관계가 있는 모습을 다대다 관계라고 할 수 있다.
