데이터베이스 - 관계형 데이터베이스 보충 내용

데이터베이스 보충 내용 1

데이터베이스 정규화(Database Normalization)

데이터베이스 정규화는 데이터베이스 설계와 관련
→ 어떻게 설계하느냐에 따라 데이터가 어떻게 저장될지 등 그 구조가 결정됨

 

다음 3가지 부분을 잘 고려해야 한다

* Data Redundancy(데이터 중복)

* Data Integrity(데이터 무결성)

* Anomalry(이상 현상)

 

Data Redundancy(데이터 중복)

실제 데이터의 복사본이나, 부분적인 복사본 둘 다 포함하는 뜻
→ 데이터베이스 내에 중복이 있을 시 발생할 수 있는 문제점

* 일관된 자료 처리의 어려움(일관성 측면)
* 저장 공간 낭비
* 데이터 효율성 감소

 

Data Integrity(데이터 무결성)

데이터의 수명 주기 동안 정확성과 일관성을 유지하는 것을 뜻함
→ 데이터가 오염되지 않고, 입력된 그대로 데이터를 사용할 수 있다

 

데이터 정규화는 데이터 무결성을 강화시킴

 

Anomalry(이상 현상)

데이터 이상 현상은 기대한 데이터와 다른 이상 현상을 말함

다음의 3가지 이상 현상이 있다

* 갱신 이상(update anomalry)
* 삽입 이상(insertion anomalry)
* 삭제 이상(deletion anomalry)

 

Update Anomalry(갱신 이상)

여러 행(레코드)에 걸쳐 동일한 데이터가 있을 때, 어떤 행을 갱신해야 하는지 논리적인 일관성이 없는 경우에 발생
아래의 경우와 같이 두 개의 레코드(행)이 동일한 사람일 때, 해당 Employee ID의 레코드를 갱신하는 경우 어느 레코드의 데이터를 갱신할지 알 수 없음

Insertion Anomalry(삽입 이상)

데이터를 삽입하지 못하는 경우를 말한다
아래의 경우 Course Code를 Null값으로 지정하지 않는 이상, Course Code가 있어야만 테이블에 추가할 수 있는 이상 현상이 발생함

deletion anomalry(삭제 이상)

데이터의 특정 부분을 삭제할 때, 의도치 않게 다른 부분도 함께 지우는 이상 현상
어느 한 직원이 담당하는 Couse Code를 삭제하려고 할 때, 이를 삭제하려면 레코드(행) 전체가 삭제됨
→ 즉, 다른 데이터들(Faculty Name, Faculty ID, …) 들도 의도치 않게 삭제되는 현상이 발생

SQL의 종류

SQL의 문법은 다양하게 있는데, 쿼리문을 어떻게 작성하느냐에 따라 그 기능이나 작업이 달라짐
→ 역할에 따른 문법 분류

* Data Definition Language(DDL)

* Data Manipulation Language(DML)

* Data Control Language(DCL)

* Data Query Language(DQL)

* Transaction Control Language(TCL)

 

Data Definition Language(DDL)

DDL은 데이터를 정의할 때 사용하는 언어
→ 주로, 데이터베이스의 테이블과 같은 오브젝트를 정의할 때 사용

ex) CREATE, DROP, …

 

Data Manipulation Language(DML)

DML은 데이터베이스에 데이터를 저장할 때 사용하는 언어

ex) INSERT, DELETE, UPDATE, …

 

Data Control Language(DCL)

DCL은 데이터베이스에 대한 접근 권한과 관련된 문법
→ 어느 유저가 데이터베이스에 접근할 수 있는지 권한 설정

* GRANT: 권한을 수여

* REVOKE: 권한을 몰수

 

Data Query Language(DQL)

DQL은 정해진 스키마 내에서 쿼리(조회)하는 언어
→ 보통 DML의 일부분으로 포함시키기도 함

ex) SELECT

 

Transaction Control Language(TCL)

TCL은 DML을 거친 데이터의 변경사항을 수정할 수 있는 언어

* COMMIT: `DML`이 작업한 내용을 데이터베이스에 커밋

* ROLLBACK: 커밋했던 내용을 다시 롤백

 

SQL 심화 문법

CASE

SQL에서도 프로그래밍 언어의 if 문과 같은 기능 사용이 가능
→ CASE: 특정 조건에 따라 다른 결과를 받을 수 있음

//ex) case의 예시

SELECT CASE
            WHEN CustomerId <= 25 THEN 'GROUP 1'
            WHEN CustomerId <= 50 THEN 'GROUP 2'
            ELSE 'GROUP 3'
        END
    FROM customers

//  CustomerId 필드값에 따라 3개의 그룹으로 나눔

 

SUBQUERY

쿼리문을 작성할 때, 내부에 다른 쿼리문(서브쿼리)을 포함할 수 있다
→ 서브쿼리는 소괄호()로 감싸야 한다

 

서브쿼리의 결과는 개별 값이나 레코드 리스트이다
→ 그 서브쿼리의 결과를 하나의 칼럼으로 사용할 수 있음

// 서브쿼리의 예시

SELECT CustomerId, CustomerId = (SELECT CustomerId FROM customers WHERE CustomerId = 2)
FROM customers
WHERE CustomerId < 6

 

이러한 서브쿼리를 활용하는 방법은 다음과 같다

IN, NOT IN

IN을 통해 특정한 값이 서브쿼리에 있는지 확인(NOT IN은 반대)

SELECT *
FROM customers
WHERE CustomerId IN (SELECT CustomerId FROM customers WHERE CustomerId < 10)

// 서브쿼리에서  CustomerId가 10이하인 데이터를 돌려주므로, 최종 조회되는 데이터도
// 마찬가지로 10이하의 데 이터만 조회됨

 

EXISTS, NOT EXISTS

돌려받은 서브쿼리에 존재하는 레코드를 확인(EXISTS)
→ 리턴값은 true / false이다(존재하면/ 존재하지 않으면)

SELECT EmployeeId
FROM employees e    // 조건을 만족하는 레코드(EmployeeId)를 출력 해줌
WHERE EXISTS (    // 해당 서브쿼리의 조건을 만족하는 레코드가 있다면(true)
    SELECT 1
    FROM customers c
    WHERE c.SupportRepId = e.EmployeeId
    )
ORDER BY EmployeeId

 

FROM

FROM 에서도 서브쿼리를 사용할 수 있다

SELECT *
FROM (
    SELECT CustomerId
    FROM customers
    WHERE CustomerId < 10
    )

 

웹사이트 셋업과 관련된 SQL 치트 시트 레퍼런스

웹사이트 생성시 참조하기 좋은 SQL문 레퍼런스

https://websitesetup.org/wp-content/uploads/2020/08/SQL-Cheat-Sheet-websitesetup.pdf

---

데이터베이스 보충 내용 2

인스타그램 스키마 디자인하기

tip: 구현하려는 기능 단위별로 나눠서 생각하며 짜면 좀 더 수월하다

 

유저(user)와 팔로우(follow) 테이블의 관계?

user와 follower는 다대다(N:N) 관계이다

나는 여러 명을 팔로우할 수 있고, 그 팔로워들도 여러 명을 팔로우할 수 있다

// 팔로우의 팔로워 부분만 생각한 예

users            follow_follower
======          ========
id        (1)   id
username        *user_id
password        *follower_id

포스트(post)와 해시태그(hashtag) 간 관계?

하나의 포스트는 여러 개의 해시태그를 달 수 있고, 해시태그도 여러 개의 포스트를 내용으로 가질 수 있음
→ N:N 관계
→ join table이 필요함!

posts            posts_hashtags        hashtags
======            ======                ======
id        (1)     id                    (1)id
image            *hashtag_id
message          *post_id
…
user_id

 

포스트와 좋아요의 관계? 포스트와 댓글의 관계?

하나의 포스트에는 여러 개의 좋아요, 댓글을 달 수 있다
→ 각각, 1:N의 관계를 가짐

---

CRUD

Create

김코딩이 박코딩을 팔로우하는 예시

INSERT INTO follow_follower(user_id, follower_id)
VALUES (SELECT id FROM users WHERE username=”김코딩”,
SELECT id FROM users WHERE username=”박코딩”);

join을 이용해도 되지만 코드 간결성을 위해 subquery를 이용하면 더 좋다

 

Update

1번 유저가 21번 포스팅에 달았던 코멘트를 ‘hell, word’로 수정하는 예시

UPDATE post_comments
SET comment=’hell, word’
WHERE user_id=1 AND post_id=21;

 

Delete

21번 포스팅에 달린 #맞팔 해시태그를 삭제하는 예시

DELETE FROM posts_hashtags
WHERE posts_id =21 AND 
hashtag_id=(SELECT id FROM hashtags WHERE name=”맞팔”);

 

서브쿼리를 이용하면 편함!