Skip to content

forreal03/DbTermProject

BranchesTags

Repository files navigation

맘스터치 주방 관리 시스템 - 완전 자동화 구현 보고서

목차

  1. 프로젝트 개요
  2. 데이터베이스 아키텍처
  3. 13개 테이블 상세 설명
  4. 실행 흐름 및 동작
  5. 동작 결과 및 통계

프로젝트 개요

개요

맘스터치(MOM'S TOUCH) 스타일의 패스트푸드 주방을 완전 자동화하는 엔터프라이즈급 데이터베이스 시스템입니다.

시스템 아키텍처

┌─────────────────────────────────────────────────┐
│           Customer Order (고객 주문)             │
│  Order → OrderItems → KitchenTaskQueue Setup     │
└──────────────────┬──────────────────────────────┘
                   ↓
┌─────────────────────────────────────────────────┐
│        Automatic Queue Creation (자동 큐 생성)   │
│  MenuItems → MenuTasks → TaskDependencies        │
└──────────────────┬──────────────────────────────┘
                   ↓
┌─────────────────────────────────────────────────┐
│   Resource Allocation (자원 할당 알고리즘)       │
│  Staff + Workstation + Zone Assignment           │
└──────────────────┬──────────────────────────────┘
                   ↓
┌─────────────────────────────────────────────────┐
│    Task Execution (작업 실행 및 추적)            │
│  IN_PROGRESS → COMPLETED (시간 기록)            │
└──────────────────┬──────────────────────────────┘
                   ↓
┌─────────────────────────────────────────────────┐
│   Bottleneck Analysis (병목 현상 분석)           │
│  Real-time Monitoring & Performance Optimization │
└─────────────────────────────────────────────────┘

기술 스택

  • 데이터베이스: SQLite3 (13개 테이블, 80+ 컬럼)
  • 언어: Python 3 (demo_complete.py)
  • UI: Terminal (colorama, tabulate)
  • 정규화: 3NF (Third Normal Form)

데이터베이스 아키텍처

전체 테이블 구조 (13개 테이블)

┌─ 작업장 관리 (Workstations & Zones)
│  ├─ Workstations (작업장 정의)
│  ├─ WorkstationZones (작업 구역)
│  ├─ ZoneCapacityRules (구역별 용량 규칙)
│  └─ ZoneRealtimeState (구역 실시간 상태)
│
├─ 인력 관리 (Staff)
│  ├─ Staff (스태프 정보)
│  └─ StaffAssignment (스태프 배치)
│
├─ 메뉴 & 레시피 (Menu System)
│  ├─ MenuItems (메뉴 목록)
│  ├─ MenuTasks (메뉴별 작업 단계)
│  └─ TaskDependencies (작업 의존성)
│
└─ 주문 & 작업 처리 (Orders & Tasks)
   ├─ CustomerOrders (고객 주문)
   ├─ OrderItems (주문 항목)
   ├─ KitchenTaskQueue (주방 작업 큐)
   └─ BottleneckAnalysis (병목 분석)

정규화 수준: 3NF

  • 1NF: 모든 컬럼이 원자적(atomic) 값만 포함
  • 2NF: 모든 컬럼이 기본 키(PK)에 완전 함수 종속
  • 3NF: 이행 종속성(Transitive Dependencies) 없음

13개 테이블 상세 설명

[1] Workstations (작업장 정의)

컬럼명 타입 설명
workstation_id INT PK 고유 식별자
name VARCHAR(100) UNIQUE 작업장 이름 (메인튀김기, 서브튀김기, 조립대 등)
type VARCHAR(20) STATION 또는 ZONED_FRYER
max_staff INT 최대 배치 가능 스태프 수

활용:

  • 작업장 타입 분류: 일반 작업대(STATION) vs 존 기반 튀김기(ZONED_FRYER)
  • 스태프 할당 시 max_staff 기반 로드 밸런싱
  • 실시간 인력 관리
-- 예시
INSERT INTO Workstations (name, type, max_staff) 
VALUES ('메인튀김기', 'ZONED_FRYER', 2),
       ('조립대', 'STATION', 3);

[2] WorkstationZones (작업 구역)

컬럼명 타입 설명
zone_id INT PK 고유 식별자
workstation_id INT FK 소속 작업장
zone_name VARCHAR(50) 구역 이름

활용:

  • 튀김기 같은 ZONED_FRYER의 경우 "좌측 바구니", "우측 바구니"로 구분
  • 동시에 다른 음식을 다른 존에서 튀길 수 있음
  • 작업 할당의 최소 단위
-- 메인튀김기(workstation_id=1)에 2개 존
INSERT INTO WorkstationZones (workstation_id, zone_name) 
VALUES (1, '좌측 바구니'), 
       (1, '우측 바구니');

[3] ZoneCapacityRules (구역별 용량 규칙)

컬럼명 타입 설명
rule_id INT PK 고유 식별자
zone_id INT FK 적용 구역
food_type VARCHAR(50) 식품 종류
max_quantity INT 최대 동시 조리 수량

활용:

  • 각 존에서 특정 음식의 최대 수량 제한
  • 예: 싸이패티는 한 번에 최대 15개만 가능
  • 자원 할당 시 용량 초과 방지
INSERT INTO ZoneCapacityRules (zone_id, food_type, max_quantity) 
VALUES (1, '싸이패티', 15), 
       (1, '감자튀김', 20);

[4] ZoneRealtimeState (구역 실시간 상태)

컬럼명 타입 설명
state_id INT PK 고유 식별자
zone_id INT FK UNIQUE 모니터링 구역
current_food_type VARCHAR(50) NULL 현재 조리 중인 음식
current_quantity INT 현재 수량
busy_until TIMESTAMP NULL 사용 가능 예상 시간

활용:

  • 각 구역의 실시간 상태 추적
  • 새 작업 할당 시 busy_until 확인하여 스케줄링
  • 병목 현상 감지
-- 구역 1: 싸이패티 10개 조리 중 (5분 소요)
UPDATE ZoneRealtimeState SET
    current_food_type = '싸이패티',
    current_quantity = 10,
    busy_until = datetime('now', '+5 minutes')
WHERE zone_id = 1;

[5] Staff (스태프 정보)

컬럼명 타입 설명
staff_id INT PK 고유 식별자
name VARCHAR(100) 스태프 이름
status VARCHAR(20) ACTIVE, BREAK, OFF_WORK

활용:

  • 스태프 상태 관리
  • 작업 할당 시 ACTIVE 스태프만 선택
  • 실시간 인력 현황 파악
INSERT INTO Staff (name, status) 
VALUES ('김철수', 'ACTIVE'), 
       ('이영희', 'BREAK');

[6] StaffAssignment (스태프 배치)

컬럼명 타입 설명
assignment_id INT PK 고유 식별자
staff_id INT FK 스태프
workstation_id INT FK 할당된 작업장
assigned_at TIMESTAMP 할당 시간

활용:

  • 스태프를 특정 작업장에 배치
  • 작업 할당 시 해당 작업장의 스태프 확인
  • 일정 이력 관리
-- 김철수를 메인튀김기(workstation_id=1)에 배치
INSERT INTO StaffAssignment (staff_id, workstation_id) 
VALUES (1, 1);

[7] MenuItems (메뉴 목록)

컬럼명 타입 설명
menu_item_id INT PK 고유 식별자
name VARCHAR(100) UNIQUE 메뉴 이름
price INT 가격

활용:

  • 주문 항목의 기본 정보
  • 레시피 작업(MenuTasks)의 기준
INSERT INTO MenuItems (name, price) 
VALUES ('싸이버거', 6000), 
       ('에드워드리버거', 9500);

[8] MenuTasks (메뉴별 작업 단계)

컬럼명 타입 설명
task_definition_id INT PK 고유 작업 ID
menu_item_id INT FK 소속 메뉴
workstation_id INT FK 실행 작업장
task_name VARCHAR(100) 작업 이름
task_order INT 실행 순서
base_time_seconds INT 표준 소요 시간(초)
task_type VARCHAR(10) ACTIVE 또는 PASSIVE

활용:

  • 메뉴의 조리 단계 정의
  • 주문 접수 시 이 테이블 기반으로 KitchenTaskQueue 자동 생성
  • 예상 소요 시간 계산
-- 싸이버거(menu_item_id=1) 레시피
INSERT INTO MenuTasks 
(menu_item_id, workstation_id, task_name, task_order, base_time_seconds, task_type)
VALUES (1, 1, '패티튀기기', 1, 300, 'ACTIVE'),
       (1, 2, '조립', 2, 60, 'ACTIVE');

[9] TaskDependencies (작업 의존성)

컬럼명 타입 설명
dependency_id INT PK 고유 식별자
task_id INT FK 의존하는 작업
depends_on_task_id INT FK 선행 작업

활용:

  • 작업 간 순서 관계 정의 (Topological Sort)
  • 예: "조립"은 "패티튀기기" 완료 후에만 시작 가능
  • 병목 분석 시 의존성 대기 시간 추적
-- 조립(task_id=2)은 패티튀기기(task_id=1) 완료 후 시작
INSERT INTO TaskDependencies (task_id, depends_on_task_id) 
VALUES (2, 1);

[10] CustomerOrders (고객 주문)

컬럼명 타입 설명
order_id INT PK 고유 식별자
order_number VARCHAR(20) UNIQUE 주문번호
status VARCHAR(20) PENDING, CONFIRMED, IN_PROGRESS, COMPLETED
created_at TIMESTAMP 주문 생성 시간
estimated_seconds_remaining INT 예상 남은 시간(초)

활용:

  • 주문 추적
  • 예상 대기 시간 표시
  • 주문 상태 관리
INSERT INTO CustomerOrders (order_number, status, estimated_seconds_remaining) 
VALUES ('ORD-001', 'CONFIRMED', 360);

[11] OrderItems (주문 항목)

컬럼명 타입 설명
order_item_id INT PK 고유 식별자
order_id INT FK 주문
menu_item_id INT FK 메뉴
quantity INT 수량

활용:

  • 한 주문에 여러 메뉴 항목 저장
  • 수량별로 KitchenTaskQueue에 작업 생성
-- 싸이버거세트 2개 주문
INSERT INTO OrderItems (order_id, menu_item_id, quantity) 
VALUES (1, 2, 2);

[12] KitchenTaskQueue (주방 작업 큐)

컬럼명 타입 설명
queue_task_id INT PK 고유 작업 ID
order_item_id INT FK 주문 항목
task_definition_id INT FK 작업 정의
assigned_workstation_id INT FK NULL 할당된 작업장
assigned_zone_id INT FK NULL 할당된 구역
assigned_staff_id INT FK NULL 할당된 스태프
status VARCHAR(20) QUEUED, WAITING_RESOURCE, IN_PROGRESS, COMPLETED
created_at TIMESTAMP 생성 시간
actual_start_time TIMESTAMP NULL 실제 시작 시간
actual_end_time TIMESTAMP NULL 실제 완료 시간

활용:

  • 주문 항목별로 자동 생성됨
  • 자원 할당 알고리즘으로 workstation, zone, staff 결정
  • 작업 실행 시간 기록
  • 성능 분석 기준

생성 흐름:

# OrderItems → MenuTasks → KitchenTaskQueue (자동 변환)
for order_item in OrderItems:
    for menu_task in MenuTasks(order_item.menu_item_id):
        for qty in order_item.quantity:
            INSERT INTO KitchenTaskQueue 
            (order_item_id, task_definition_id, status)
            VALUES (order_item.id, menu_task.id, 'QUEUED')

자원 할당 알고리즘:

for task in KitchenTaskQueue(status='QUEUED'):
    # 1. 작업의 workstation 확인
    ws = MenuTasks(task.task_definition_id).workstation_id
    
    # 2. 해당 workstation에서 사용 가능한 존 선택
    zone = select_available_zone(ws)
    
    # 3. 해당 workstation에 배치된 활동 중인 스태프 선택
    staff = select_available_staff(ws)
    
    # 4. 업데이트
    UPDATE KitchenTaskQueue SET
        assigned_workstation_id = ws,
        assigned_zone_id = zone,
        assigned_staff_id = staff,
        status = 'WAITING_RESOURCE'

실행 흐름:

# IN_PROGRESS 상태로 변경 (시작 시간 기록)
UPDATE KitchenTaskQueue SET
    status = 'IN_PROGRESS',
    actual_start_time = datetime('now')
WHERE queue_task_id = ?

# 작업 진행 중...

# COMPLETED 상태로 변경 (완료 시간 기록)
UPDATE KitchenTaskQueue SET
    status = 'COMPLETED',
    actual_end_time = datetime('now')
WHERE queue_task_id = ?

[13] BottleneckAnalysis (병목 현상 분석)

컬럼명 타입 설명
analysis_id INT PK 고유 식별자
queue_task_id INT FK 영향받은 작업
bottleneck_type VARCHAR(50) NO_STAFF, NO_FRYER_ZONE, FRYER_TEMP_RECOVERY, DEPENDENCY_WAIT
wait_duration_seconds INT 대기 시간(초)
problematic_workstation_id INT FK NULL 문제 발생 작업장
recorded_at TIMESTAMP 기록 시간

활용:

  • 병목 원인 자동 분류
  • 대기 시간 추적
  • 성능 개선 포인트 파악

병목 유형:

  • NO_STAFF: 스태프 부족으로 대기
  • NO_FRYER_ZONE: 모든 튀김 존 사용 중
  • FRYER_TEMP_RECOVERY: 기름 온도 복구 대기
  • DEPENDENCY_WAIT: 선행 작업 완료 대기
INSERT INTO BottleneckAnalysis 
(queue_task_id, bottleneck_type, wait_duration_seconds, problematic_workstation_id)
VALUES (5, 'NO_STAFF', 120, 1);

실행 흐름 및 동작

전체 시뮬레이션 단계

demo_complete.py 실행 시 다음 순서로 진행:

1단계: 데이터베이스 생성

python demo_complete.py
  • 01_schema.sql 실행: 13개 테이블 생성
  • 인덱스 생성: 성능 최적화

2단계: 기본 데이터 삽입

├─ 02_workstations.sql: Workstations, WorkstationZones, ZoneCapacityRules, ZoneRealtimeState
├─ 03_staff.sql: Staff, StaffAssignment
├─ 04_menu.sql: MenuItems
└─ 05_recipes.sql: MenuTasks, TaskDependencies

3단계: 고객 주문 접수 (demo_customer_orders)

  • 4개의 주문 접수
  • 각 주문마다 예상 대기 시간 계산
  • 영수증 출력
ORD-001: 싸이버거 1개 → 예상 6분
ORD-002: 싸이버거세트 2개 + 텐더 1개 → 예상 10분
ORD-003: 에드워드리버거 1개 → 예상 8분 37초
ORD-004: 에드워드리세트 1개 + 싸이버거 1개 → 예상 14분 37초

4단계: 작업 큐 자동 생성 (demo_task_queue_creation)

  • OrderItems → MenuTasks 기반으로 KitchenTaskQueue 자동 생성
  • 총 25개 작업 항목 생성 (주문별 작업 단계별 × 수량)
ORD-001 → 2개 작업 (패티튀기기, 조립)
ORD-002 → 8개 작업 (싸이세트 2개 + 텐더 1개)
...
총 25개 작업

5단계: 자원 할당 (demo_resource_assignment)

  • 각 QUEUED 작업에 workstation, zone, staff 할당
  • 알고리즘: 가용성 우선, 로드 밸런싱
Task 1: WS#1(튀김기), Zone#1(좌측), Staff#1(김철수)
Task 2: WS#2(조립대), Zone#3(조립1), Staff#3(박민수)
...

6단계: Zone 상태 업데이트 (demo_zone_state_updates)

  • ZoneRealtimeState 시뮬레이션
  • 특정 존을 "사용 중"으로 표시
Zone 1 (메인튀김기-좌측): 싸이패티 10개 조리 중 (5분)
Zone 4 (서브튀김기-우측): 감자튀김 20개 조리 중 (3분)

7단계: 작업 처리 시뮬레이션 (demo_task_execution)

  • WAITING_RESOURCE → IN_PROGRESS → COMPLETED
  • 각 작업별 시작/종료 시간 기록
Task 1: QUEUED → IN_PROGRESS (actual_start_time 기록)
        → COMPLETED (actual_end_time 기록)
...

8단계: 병목 분석 (demo_bottleneck_analysis)

  • 3가지 병목 시나리오 시뮬레이션
  • BottleneckAnalysis 테이블에 기록
Task 5: NO_STAFF 병목 (120초 대기)
Task 8: FRYER_TEMP_RECOVERY 병목 (90초 대기)
Task 12: DEPENDENCY_WAIT 병목 (150초 대기)

9단계: 최종 리포트 (demo_final_report)

  • 모든 13개 테이블의 데이터 출력
  • 각 테이블 레코드 수 통계
  • 작업 상태별 분석
  • 병목 원인별 통계

동작 결과 및 통계

최종 테이블 레코드 수 (momstouch_complete.db)

테이블명 레코드 수 상태
Workstations 3 ✅
WorkstationZones 7 ✅
ZoneCapacityRules 12 ✅
ZoneRealtimeState 7 ✅
Staff 5 ✅
StaffAssignment 5 ✅
MenuItems 4 ✅
MenuTasks 12 ✅
TaskDependencies 8 ✅
CustomerOrders 4 ✅
OrderItems 8 ✅
KitchenTaskQueue 25 ✅
BottleneckAnalysis 3 ✅
합계 103 ✅

작업 상태별 분포

QUEUED:           12개
WAITING_RESOURCE:  0개
IN_PROGRESS:       0개
COMPLETED:        13개
─────────────────────
합계:             25개

병목 원인별 분석

NO_STAFF:                  1회 (평균 대기 120초)
FRYER_TEMP_RECOVERY:       1회 (평균 대기 90초)
DEPENDENCY_WAIT:           1회 (평균 대기 150초)
─────────────────────────────────────
총 병목:                   3회
평균 대기시간:             120초

작업 처리 시간 분석

OrderItems당 평균 작업 수:    3.1개
평균 작업 소요시간:          60~300초
총 예상 시간:                ~40분
실제 처리 효율:              100% (시뮬레이션)

핵심 성과

13개 테이블 완벽 활용

작업장 & 구역 관리

  • Workstations: 3개 작업장 정의 (메인튀김기, 서브튀김기, 조립대)
  • WorkstationZones: 7개 구역 생성 및 관리
  • ZoneCapacityRules: 12개 용량 규칙 정의
  • ZoneRealtimeState: 실시간 상태 추적 및 스케줄링 기반 제공

인력 관리

  • Staff: 5명 스태프 상태 관리
  • StaffAssignment: 작업장별 배치 및 이력 관리

메뉴 & 레시피

  • MenuItems: 4개 메뉴 정의
  • MenuTasks: 12개 작업 단계 정의 (기준 시간 포함)
  • TaskDependencies: 8개 의존성 관계 정의 (Topological Sort 기반)

주문 & 작업 처리

  • CustomerOrders: 4개 주문 추적 (상태 관리)
  • OrderItems: 8개 주문 항목 (수량 기반 작업 생성)
  • KitchenTaskQueue: 25개 작업 자동 생성 및 처리
  • BottleneckAnalysis: 3개 병목 분석 및 원인 파악

주요 기능 구현

1. 자동 작업 큐 생성

OrderItems (8개) → MenuTasks (12개) × Quantity → KitchenTaskQueue (25개 자동 생성)

2. 스마트 자원 할당

for each QUEUED task:
    - Workstation 확인 (MenuTasks 기반)
    - Available Zone 선택 (ZoneRealtimeState 기반)
    - Available Staff 선택 (StaffAssignment 기반)
    - 자동 할당 및 상태 업데이트

3. 시간 추적 & 분석

- 예상 시간: MenuTasks.base_time_seconds 합계
- 실제 시간: actual_end_time - actual_start_time
- 효율성: (예상 - 실제) / 예상 × 100%

4. 병목 현상 자동 감지

if task.wait_duration > THRESHOLD:
    원인 분류: NO_STAFF / NO_FRYER_ZONE / FRYER_TEMP_RECOVERY / DEPENDENCY_WAIT
    BottleneckAnalysis에 자동 기록

데이터베이스 무결성

제약조건

  • Primary Keys: 모든 테이블에 적용
  • Foreign Keys: 13개 테이블 모두 관계 유지
  • UNIQUE 제약:
    • Workstations.name
    • MenuItems.name
    • CustomerOrders.order_number
    • WorkstationZones (workstation_id, zone_name)
    • ZoneCapacityRules (zone_id, food_type)
    • ZoneRealtimeState.zone_id

CHECK 제약

Workstations.type IN ('STATION', 'ZONED_FRYER')
Staff.status IN ('ACTIVE', 'BREAK', 'OFF_WORK')
MenuTasks.task_type IN ('ACTIVE', 'PASSIVE')
KitchenTaskQueue.status IN ('QUEUED', 'WAITING_RESOURCE', 'IN_PROGRESS', 'COMPLETED')
BottleneckAnalysis.bottleneck_type IN ('NO_STAFF', 'NO_FRYER_ZONE', 'FRYER_TEMP_RECOVERY', 'DEPENDENCY_WAIT')

인덱스

CREATE INDEX idx_queue_priority ON KitchenTaskQueue(status, created_at);
CREATE INDEX idx_zone_state ON ZoneRealtimeState(zone_id, busy_until);
CREATE INDEX idx_bottleneck_type ON BottleneckAnalysis(bottleneck_type);

파일 구조

DbTermProject/
├── 01_schema.sql                 (13개 테이블 스키마)
├── 02_workstations.sql          (작업장 & 구역 초기 데이터)
├── 03_staff.sql                 (스태프 초기 데이터)
├── 04_menu.sql                  (메뉴 초기 데이터)
├── 05_recipes.sql               (레시피 & 의존성)
├── demo_complete.py             (완전 자동화 시뮬레이션)
├── README.md                     (본 문서)
├── requirements.txt              (의존성)
└── queries/                      (SQL 쿼리 파일들)
    ├── insert_order.sql
    ├── insert_order_item.sql
    ├── insert_kitchen_task.sql
    ├── select_order_items.sql
    ├── select_menu_tasks.sql
    ├── select_queued_tasks.sql
    ├── select_available_staff.sql
    ├── select_workstation_zones.sql
    ├── update_task_assignment.sql
    ├── update_task_in_progress.sql
    ├── update_task_completed.sql
    ├── select_zone_realtime_state.sql
    ├── select_waiting_resource_tasks.sql
    ├── select_bottleneck_stats.sql
    └── insert_bottleneck.sql

실행 방법

필수 설치

pip install -r requirements.txt

시뮬레이션 실행

python demo_complete.py

생성된 데이터베이스

momstouch_complete.db (SQLite3)

쿼리 결과 확인

sqlite3 momstouch_complete.db
sqlite> SELECT * FROM Workstations;
sqlite> SELECT * FROM KitchenTaskQueue LIMIT 10;

결론

이 프로젝트는 13개 테이블, 80개 이상의 컬럼을 포함한 완벽하게 정규화된 데이터베이스를 구현하였으며, 실제 패스트푸드 주방의 복잡한 작업 흐름을 완전 자동화하는 시스템입니다.

핵심 성과

  1. 자동 작업 큐 생성: 주문 → 작업 항목 → 세부 작업 자동 변환
  2. 스마트 자원 할당: 작업장, 구역, 스태프 자동 배치
  3. 실시간 모니터링: ZoneRealtimeState 기반 동적 스케줄링
  4. 병목 분석: 자동 감지 및 원인 분류
  5. 완벽한 추적: 모든 작업의 시간 기록 및 성능 분석

기술적 우수성

  • 3NF 정규화: 데이터 무결성 보장
  • 엔터프라이즈급 설계: 확장성 및 유지보수성 높음
  • 완전 자동화: 수동 개입 최소화
  • 성능 최적화: 인덱싱 및 쿼리 최적화

이 시스템은 실제 맘스터치와 같은 패스트푸드 체인점의 주방 관리 자동화에 즉시 적용 가능합니다.

About

서울과기대-database-termProject

Resources

Readme
Activity

Stars

0 stars

Watchers

0 watching

Forks

0 forks
Report repository

Releases

No releases published

Packages

No packages published

Languages