Database- 데이터베이스 (2일차: 데이터베이스와 테이블 생성)

 

과제 2: 데이터베이스와 테이블 생성

이번 과제는 사용자와 주문 정보뿐만 아니라 재고, 판매 및 매출 정보를 저장하는 데이터베이스와 여러 테이블을 생성하는 것입니다. 이 과정은 MySQL에서 데이터베이스와 테이블을 설계하고 생성하는 기본적인 실습입니다.

1️⃣ 데이터베이스 생성

먼저, 붕어빵 가게의 데이터를 저장할 데이터베이스를 생성합니다. 데이터베이스는 CREATE DATABASE 명령어로 생성할 수 있습니다.

sql

 

CREATE DATABASE fishbread_db;

• 이 명령어로 fishbread_db라는 데이터베이스가 생성됩니다.
• 데이터베이스를 생성한 후, 사용하기 위해 USE 명령어를 사용합니다:

sql

USE fishbread_db;

2️⃣ users 테이블 생성

사용자 정보를 저장할 테이블을 생성합니다. 이 테이블에는 사용자 ID, 이름, 나이, 이메일 및 비즈니스 여부가 포함됩니다.

sql

 

CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    age INT NOT NULL,
    email VARCHAR(100) UNIQUE,
    is_business VARCHAR(10) DEFAULT 'No'
);

• user_id는 사용자의 고유 ID이며, AUTO_INCREMENT로 자동 증가합니다.
• name은 사용자의 이름이며, 필수 입력값으로 NOT NULL 제약을 두었습니다.
• age는 사용자의 나이를 나타냅니다.
• email은 사용자의 이메일로, UNIQUE 제약을 추가하여 중복된 이메일을 방지합니다.
• is_business는 비즈니스 여부를 나타내며, 기본값은 'No'입니다.

3️⃣ orders 테이블 생성

주문 정보를 저장할 테이블을 생성합니다. 이 테이블에는 주문 ID, 사용자 ID, 주문 날짜 및 주문 금액을 저장합니다.

sql

 

CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    order_date DATE,
    amount DECIMAL(10, 2),
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

• order_id는 주문의 고유 ID이며, AUTO_INCREMENT로 자동 증가합니다.
• user_id는 주문한 사용자의 ID로, users 테이블의 user_id 칼럼과 FOREIGN KEY 제약으로 참조 관계를 가집니다.
• order_date는 주문 날짜를 저장합니다.
• amount는 주문 금액을 저장하는 DECIMAL 타입입니다.

4️⃣ inventory 테이블 생성

재고 정보를 저장할 테이블을 생성합니다. 이 테이블에는 재고 항목의 ID, 이름 및 수량이 포함됩니다.

sql

 

CREATE TABLE inventory (
    item_id INT PRIMARY KEY AUTO_INCREMENT,
    item_name VARCHAR(255) NOT NULL,
    quantity INT NOT NULL
);

• item_id는 재고 항목의 고유 ID이며, AUTO_INCREMENT로 자동 증가합니다.
• item_name은 재고 항목의 이름이며, 필수 입력값으로 NOT NULL 제약을 두었습니다.
• quantity는 재고 수량을 나타냅니다.

5️⃣ sales 테이블 생성

판매 정보를 저장할 테이블을 생성합니다. 이 테이블에는 판매 ID, 주문 ID, 재고 항목 ID 및 판매된 수량이 포함됩니다.

sql

 

CREATE TABLE sales (
    sale_id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT,
    item_id INT,
    quantity_sold INT NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (item_id) REFERENCES inventory(item_id)
);

• sale_id는 판매의 고유 ID이며, AUTO_INCREMENT로 자동 증가합니다.
• order_id는 판매가 발생한 주문의 ID로, orders 테이블의 **order_id**와 **FOREIGN KEY**로 참조 관계를 가집니다.
• item_id는 판매된 재고 항목의 ID로, inventory 테이블의 **item_id**와 **FOREIGN KEY**로 참조 관계를 가집니다.
• quantity_sold는 판매된 수량을 나타냅니다.

6️⃣ daily_sales 테이블 생성

일별 매출 정보를 저장할 테이블을 생성합니다. 이 테이블에는 날짜와 해당 날짜의 총 매출이 포함됩니다.

sql

 

CREATE TABLE daily_sales (
    date DATE PRIMARY KEY,
    total_sales DECIMAL(10, 2) NOT NULL
);

• date는 매출이 발생한 날짜를 나타내며, **PRIMARY KEY**로 설정하여 각 날짜에 대한 데이터를 하나만 저장할 수 있게 합니다.
• total_sales는 해당 날짜의 총 매출을 나타내는 DECIMAL 타입입니다.

전체 테이블 생성 예시

sql

 

-- 데이터베이스 생성
CREATE DATABASE fishbread_db;

-- 데이터베이스 사용
USE fishbread_db;

-- users 테이블 생성
CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    age INT NOT NULL,
    email VARCHAR(100) UNIQUE,
    is_business VARCHAR(10) DEFAULT 'No'
);

-- orders 테이블 생성
CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    order_date DATE,
    amount DECIMAL(10, 2),
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

-- inventory 테이블 생성
CREATE TABLE inventory (
    item_id INT PRIMARY KEY AUTO_INCREMENT,
    item_name VARCHAR(255) NOT NULL,
    quantity INT NOT NULL
);

-- sales 테이블 생성
CREATE TABLE sales (
    sale_id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT,
    item_id INT,
    quantity_sold INT NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (item_id) REFERENCES inventory(item_id)
);

-- daily_sales 테이블 생성
CREATE TABLE daily_sales (
    date DATE PRIMARY KEY,
    total_sales DECIMAL(10, 2) NOT NULL
);

요약

1. 데이터베이스: 붕어빵 가게 데이터를 저장할 fishbread_db 생성.
2. 테이블: 사용자 정보, 주문 정보, 재고, 판매 및 일 매출 정보를 저장하는 5개의 테이블 생성.
3. 외래키(Foreign Key): orders, sales 테이블에서 다른 테이블과 관계를 설정.

1️⃣ 외래키 참조 문제 확인

• FOREIGN KEY 제약조건을 사용하면서 참조하는 users, orders, inventory 테이블이 먼저 생성되어 있어야 합니다.
• 참조하는 테이블이 존재하지 않으면 외래키 제약이 오류를 발생시킬 수 있습니다. 예를 들어, orders 테이블은 users 테이블을 참조하고, sales 테이블은 orders와 inventory 테이블을 참조합니다.

2️⃣ 테이블 생성 순서

• 테이블을 생성할 때 참조 관계가 있는 테이블들이 먼저 생성되어야 합니다. 순서가 맞지 않으면 FOREIGN KEY 제약 때문에 테이블이 생성되지 않을 수 있습니다.

따라서 users, orders, inventory, sales, daily_sales 테이블을 생성할 때 순서대로 쿼리를 실행해야 합니다.