AIoT 정규 67일차

대형마트 주차장 관리 키오스크를 위한 데이터베이스 설계

: 대형마트 주차장 관리 키오스크 만들기 프로젝트를 위한 데이터베이스 설계를 진행해보겠다. 전반적으로 차량의 입차 및 출차 정보를 관리하고 주차 요금을 계산하는데 사용할 예정이다.

1.테이블 설계

: Vehicle_parking_manage 테이블 만들기

- vehicle_id: 차량 ID (자동 생성, 기본 키)
- vehicle_owner: 차량 소유자 이름
- vehicle_num: 차량 번호 (고유한 값)
- entrance_or_exit: 입차 또는 출차 정보
- entry_time: 입차 시간 (기본값: 현재 시간)
- exit_time: 출차 시간
- parking_type: 주차 타입 ('정기' 또는 '일반')
- img_name: 차량 사진 파일 이름
- parking_fee: 주차 요금
- purchase_value: 구매 금액

CREATE TABLE Vehicle_parking_manage (
    vehicle_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    vehicle_owner VARCHAR(45) DEFAULT NULL,
    vehicle_num VARCHAR(10) NOT NULL UNIQUE,
    entrance_or_exit VARCHAR(8) NOT NULL,
    entry_time DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
    exit_time DATETIME,
    parking_type VARCHAR(2) DEFAULT '일반' NOT NULL,
    img_name VARCHAR(50),
    parking_fee INT DEFAULT NULL,
    purchase_value INT DEFAULT NULL
);

 

2. 초기 데이터 삽입

INSERT INTO Vehicle_parking_manage (vehicle_id, vehicle_owner, vehicle_num, entrance_or_exit, parking_type, img_name)
VALUES
-- 정기 주차 차량
(1001, '김영수', '123가4567', '입구', '정기', 'img(1).jpg'),
(1002, '이정희', '456나7890', '입구', '정기', 'img(2).jpg'),
-- 일반 주차 차량
(5001, '박성준', '876가5864', '입구', '일반', 'img(11).jpg'),
(5002, '나강훈', '96나5545', '입구', '일반', 'img(12).jpg');

3. 계산 필드 추가: temp_time

: 30분 후의 시간과 입차 시간의 차이를 계산하여 `temp_time` 필드를 추가했다.

 

ALTER TABLE Vehicle_parking_manage
ADD temp_time TIME AS (
    TIMEDIFF(
        DATE_ADD(entry_time, INTERVAL 30 MINUTE),
        entry_time
    )
);

4. 주차 요금 계산

: temp_time`을 기반으로 주차 요금을 계산한다. 구매 금액에 따라 무료 시간을 적용합니다.

UPDATE Vehicle_parking_manage
SET parking_fee = 
    CASE
        WHEN parking_type = '정기' THEN 0
        WHEN parking_type = '일반' THEN 
            (
                (TIME_TO_SEC(temp_time) / 600) * 1000
            ) -
            CASE
                WHEN purchase_value >= 10000 AND purchase_value < 30000 THEN 2000
                WHEN purchase_value >= 30000 AND purchase_value < 50000 THEN 3000
                WHEN purchase_value >= 50000 AND purchase_value < 100000 THEN 5000
                ELSE 0
            END
    END;