3. 데이터베이스의 구조와 사용자 - 보충

이미 정리된 내용을 직접 작성하여 옮기면서 공부하기 위해 글 작성함.

---

데이터베이스 스키마, 테이블 스키마, 데이터베이스 사전

1. 테이블의 스키마와 데이터베이스(디비)의 스키마

1-1. 데이터베이스의 스키마 (Database Schema)

• 데이터베이스 스키마의 예시
  • 데이터베이스 shop에 다음과 같은 구조가 있다고 가정:
    • 테이블: products, customers, orders
    • 관계: orders.customer_id는 customers.id를 참조
  • 이 전체 구조가 데이터베이스 스키마입니다.
• 데이터베이스 스키마는 데이터베이스 전체의 구조와 조직을 정의한다. 데이터베이스에 저장되는 모든 테이블, 뷰, 인덱스, 프로시저, 관계 등을 포함한 큰 그림이다.
• 데이터베이스 스키마의 주요 요소
  • 테이블(Table): 데이터가 저장되는 기본 구조
  • 뷰(View): 하나 이상의 테이블에서 데이터를 조회하는 가상 테이블
  • 관계(Relationship): 테이블 간의 외래 키 관계
  • 인덱스(Index): 데이터 조회를 빠르게 하기 위한 구조
  • 트리거(Triger): 특정 이벤트가 발생할 때 실행되는 동작
  • 저장 프로시저(Stored Procedure): 사전에 정의된 SQL 작업

1-2. 테이블의 스키마 (Table Schema)

• 테이블 스키마 예시
  • CREATE TABLE users (
        id INT AUTO_INCREMENT PRIMARY KEY, -- Primary Key
        name VARCHAR(50) NOT NULL,         -- 이름은 NULL 허용하지 않음
        email VARCHAR(100) UNIQUE,         -- 이메일은 유일해야 함
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP -- 생성 시간
    );
  • 이 예시에서 테이블 users의 스키마는 다음과 같은 정보를 포함한다.
    • 컬럼: id, name, email, created_at
    • 데이터 타입: INT, VARCHAR, TIMESTAMP
    • 제약 조건: Primary Key, NOT NULL, UNIQUE
    • 기본값: created_at의 기본값으로 CURRENT_TIMESTAMP
• 테이블 스키마는 데이터베이스 스키마의 한 부분으로 특정 테이블의 구조와 정의를 나타낸다. 즉, 테이블의 열(컬럼), 데이터 타입, 제약 조건 등을 포함한 설계 정보이다.
• 테이블 스키마의 주요 요소
  • 컬럼 이름(Column Name): 각 필드의 이름
  • 데이터 타입(Data Type): 정수, 문자열, 날짜 등 데이터 형식
  • 제약 조건(Constraints):
    • Primary Key, Foreign Key, NOT NULL, UNIQUE 등
  • 기본값(Default Value): 컬럼에 값이 없을 때 사용되는 기본 값
  • 인덱스(Index): 빠른 검색을 위한 데이터 구조

1-3. 테이블 스키마 vs 데이터베이스 스키마

 

 

1.4 MySQL에서 테이블 스키마와 데이터베이스 스키마 확인

• 테이블의 스키마 확인
  1. 테이블 구조는 DESCRIBE 명령어 또는 SHOW COLUMNS 명령어를 사용하여 확인할 수 있습니다.
     • DESCRIBE users;
  2. 데이터베이스의 스키마 확인
     • 데이터베이스의 전체 구조는 SHOW TABLES, SHOW SCHEMAS와 같은 명령어로 확인하거나, 정보 스키마(information_schema)를 조회할 수 있습니다. 
     • SHOW TABLES FROM my_database;
       -- 또는
       SELECT * FROM information_schema.tables WHERE table_schema = 'my_database';

1-5. 데이터베이스 스키마와 테이블스키마의 관계

• 데이터베이스 스키마는 데이터베이스 전체의 설계를 나타내며
• 테이블 스키마는 데이터베이스 스키마의 일부로, 테이블 단위의 정의를 포함한다.
• 데이터베이스 스키마 설계 예시
  • 데이터베이스 company를 설계한다고 가정
    • 테이블 정의(테이블 스키마들)
    • 테이블 간의 관계(외래 키)
    • 뷰(View)
    • 인덱스(Index)
      • 인덱스의 주요 개념
      • 기본 인덱스 생성
    • 저장 프로시저(Stored Procedure)
  • CREATE TABLE employees (
        id INT PRIMARY KEY,
        name VARCHAR(50),
        department_id INT,
        hire_date DATE
    );
    
    CREATE TABLE departments (
        id INT PRIMARY KEY,
        name VARCHAR(100)
    );
    
    CREATE VIEW employee_department AS
        SELECT e.id AS employee_id, e.name AS employee_name, d.name AS department_name
            FROM employees e
            JOIN departments d ON e.department_id = d.id;
• 요약
  • 테이블의 스키마는 테이블 단위의 구조(컬럼, 데이터 타입, 제약 조건 등를 정의한다.
  • 데이터베이스의 스키마는 데이터베이스 전체의 조직(테이블, 뷰, 관계 등)을 정의한다.
  • MySQL에서는 이 두 개념이 독리적으로 존재하며, 각각 다르게 다룰 수 있다.

2. 데이터베이스 스키마와 **데이터베이스 사전(Data Dictionary)**

데이터베이스 스키마와 **데이터베이스 사전(Data Dictionary)**은 데이터베이스 관리와 구조 정의에서 중요한 개념이지만, 그 역할과 의미는 다르다.

 

2-1. 데이터베이스 스키마(Database Schema)

• 데이터베이스 스키마는 데이터베이스의 구조(설계도)를 정의하는 논리적인 개념이다. 데이터가 어떻게 저장되고, 어떻게 연결되며, 어떤 제약 조건이 있는지에 대한 설계 정보를 제공한다.
• 주요 특징:
  • 정의된 설계: 테이블, 뷰, 인덱스 관계 등의 정의.
  • 구조 중심: 데이터의 조직과 구조를 기술함.
  • 개발자 관점: 데이터베이스를 설계하고 사용하는 논리적 틀.
• 예시:
  • 테이블 이름, 컬럼 이름, 데이터 타입, 키(Primary/Foreign), 인덱스 등이 포함됨.
  • "데이터베이스의 설계도" 역할을 함.
• 스키마 예시 (SQL 코드로 정의):
• CREATE TABLE employees (
      id INT PRIMARY KEY,
      name VARCHAR(50),
      department_id INT,
      hire_date DATE
  );
• 이 테이블 정의가 데이터베이스의 스키마 일부이다.

2-2. 데이터베이스 사전(Data Dictionary)

• 데이터베이스 사전은 데이터베이스 시스템 내부에서 유지되는 메타데이터 저장소다. 즉, 데이터베이스에 대한 정보를 실제로 저장하고 관리하는 물리적인 시스템이다.
• 주요 특징:
  • 메타데이터 저장소: 데이터베이스 구조와 데이터에 대한 정보를 저장.
  • 실제 운영 중심: 데이터베이스의 현재 상태와 정의 정보를 저장하고 관리.
  • DBMS 관점: 데이터베이스 시스템이 내부적으로 사용하며, 사용자가 직접 수정하지 않음.
• 사전에 포함된 정보:
  • 테이블 정의, 컬럼 속성, 제약 조건.
  • 데이터베이스 객체(뷰, 인덱스, 트리거 등)에 대한 정보.
  • 사용 권한, 스토리지 세부 정보, 통계 등.
• 데이터 사전 조회 예시 (MySQL의 information_schema):
  • SELECT table_name, column_name, data_type 
    FROM information_schema.columns 
    WHERE table_schema = 'my_database';
• 차이점 정리

•  요약
  • 스키마는 데이터베이스 설계의 논리적인 정의(설계도).
  • 사전은 데이터베이스 관리 시스템(DBMS)이 스키마 및 메타데이터를 저장하고 관리하는 실제 데이터 구조.
  • 개발자와 사용자는 스키마를 직접 정의하거나 수정하지만, 데이터 사전은 DBMS가 내부적으로 관리하며 읽기 전용으로 조회만 가능.

3. MySQL에서 스키마

MySQL에서 스키마의 정의

• MySQL에서는 **스키마(Schema)**라는 용어를 데이터베이스의 논리적인 이름으로 간주한다.
• CREATE SCHEMA는 CREATE DATABASE와 동일하게 작동한다.
• 즉, MySQL에서는 스키마를 별도로 정의하는 것이 아니라 데이터베이스를 생성하면 그 데이터베이스 자체가 스키마 역할을 한다.

MySQL에서 스키마와 데이터베이스의 관계

• MySQL에서는 데이터베이스와 스키마가 동일하게 작동하므로, 아래 두 명령은 같은 결과를 가져온다:
• CREATE DATABASE my_database;
  -- 동일한 명령
  CREATE SCHEMA my_database;

왜 스키마가 없는 것처럼 보일까?

• 다른 데이터베이스 시스템에서는 데이터베이스 안에 여러 개의 스키마를 정의하고, 각 스키마 안에 테이블을 배치하는 구조를 지원한다. 예를 들어, PostgreSQL에서는 다음과 같이 하나의 데이터베이스에 여러 스키마를 둘 수 있다:
• 데이터베이스: company
  • 스키마 1: sales
    • 테이블: orders, customers
  • 스키마 2: hr
    • 테이블: employees, payroll
• 하지만 MySQL에서는 하나의 데이터베이스에 여러 스키마를 정의할 수 없고, 데이터베이스 자체가 스키마와 동일하게 간주된다.

SHOW SCHEMAS와 SHOW DATABASES

• MySQL에서 SHOW SCHEMAS;와 SHOW DATABASES; 명령은 동일한 결과를 반환한다. 예시:
  • SHOW SCHEMAS;
    -- 또는
    SHOW DATABASES;
    
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | sys                |
    +--------------------+

요약

• MySQL은 스키마의 하위 분할 구조(다중 스키마)를 지원하지 않으며, 대신 여러 데이터베이스를 생성하여 사용한다.
• PostgreSQL이나 Oracle처럼 명확히 스키마와 데이터베이스를 구분하는 시스템과는 설계 철학이 다르다.

4. 스키마란?

• MySQL에서는 데이터베이스와 스키마가 동일한 의미로 사용된다.
• **스키마(schema)**는 데이터베이스의 구조와 조직을 정의하는 청사진 또는 설계도이다. 즉, 데이터베이스에서 데이터를 어떻게 저장하고 구성할지를 명시한 것이다.
• 스키마는 다음을 포함할 수 있다:
  • 테이블 구조
    • 테이블 이름
    • 각 테이블의 컬럼(필드) 이름과 데이터 타입
    • 제약 조건(Primary Key, Foreign Key, Unique 등)
  • 뷰(View)
    • 특정 데이터를 간단히 볼 수 있도록 테이블로부터 파생된 가상 테이블 정의
  • 인덱스(Index)
    • 검색 속도를 높이기 위해 사용하는 데이터 구조
  • 저장 프로시저(Stored Procedure)와 트리거(Trigger)
    • 데이터 조작을 자동화하거나 특정 이벤트 발생 시 실행되는 코드
• 스키마의 특징
  • 물리적 스키마(Physical Schema)
    • 실제 데이터가 어떻게 저장되는지에 대한 정의 (하드웨어, 저장 구조 등)
  • 논리적 스키마(Logical Schema)
    • 데이터베이스의 논리적 설계, 즉 테이블과 관계 구조를 정의
  • 사용자 정의 스키마(User Schema)
    • 특정 사용자가 접근 가능한 테이블과 뷰에 대한 설정
• 예시
  • CREATE TABLE users (
        id INT PRIMARY KEY AUTO_INCREMENT,
        name VARCHAR(100) NOT NULL,
        email VARCHAR(100) UNIQUE NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    );
• 위 예는 데이터베이스에서 users라는 테이블의 스키마를 정의한 것입니다.
  • id: 정수형 기본 키
  • name: 이름 필드 (100자 이하 문자열)
  • email: 고유한 이메일 필드
  • created_at: 데이터 생성 시간 자동 기록
• 요약
  • 스키마는 데이터베이스를 설계하고 관리하는 기본 틀이며, 데이터의 무결성과 효율성을 보장합니다.

 

---

오늘은 보충이라서 크게 무언가 할게 없다.