[MySQL] 스터디카페 관리시스템 개발 (4) 시스템 구현 및 운영

azzurri21·2021년 7월 5일
mysql정보시스템설계프로젝트
1

MySQL

목록 보기
4/4

※ 본 시리즈는 정보시스템설계 수업에서 진행된 2인1조 프로젝트의 진행과정을 재구성한 것이며, 작성자는 이벤트 스케쥴러, 날짜 관련 쿼리 구현과 예시 데이터 삽입을 제외한 프로세스 & 데이터 모델링, SQL을 모두 직접 제작했음을 알립니다.

※ 시스템 제작 과정은 Essentials of Systems Analysis and Design 교재에 제시된 SDLC를 참고했습니다.

4. 시스템 구현 및 운영

4.1 MySQL 코딩

Schema & Table 생성

MySQL Workbench를 이용하여 나타낸 스터디카페 시스템의 ERD이다. Relation Schema를 바탕으로 제작했으며, 외래키와 자료형이 정의되어 있다. (한국어 ERD)

그리고 MySQL Workbench의 ERD Diagram 창에서 [Database] 탭 > Forward Engineer 기능을 이용하여 ERD를 MySQL Schema로 변환했다.

Relation Schema를 MySQL의 table과 column으로 구현한 결과가 아래 사진과 같다. 자료형(type), 기본값(default value), Null값 허용여부(nullable) 등의 데이터 무결성(data integrity) 통제 방법에 주목하라.

※ 표 위에서 아래 방향 순서로 설명할 내용을 적었다.

  • time, residual_time의 Type:
    초 단위의 시간이므로 int 자료형을 사용한다.

  • storable의 Type:
    ERD 속성 추가 시에 Type을 boolean으로 입력하면 자동으로 0, 1만 저장하는 정수형 자료형인 tinyint로 바뀌는데, 사용에는 별 차이가 없다.

  • resudual_time의 Default Value:
    스터디카페에 처음 등록한 학생은 아직 이용권을 구매하지 않았으므로 잔여이용시간이 0이다.

  • date, start_date의 Default Value:
    CURRENT_TIMESTAMP는 속성값이 입력될 때의 현재시각을 의미한다.

  • expected_end_date, real_end_date의 Nullable:
    레코드 삽입과 함께 초기화되는 나머지 속성들과 달리, 두 속성은 처음에 null 값으로 존재한다. 이후에 update SQL문으로 속성에 값을 입력한다.

  • ticket_no의 Auto_Increment:
    ticket_no는 Ticket 테이블의 나머지 세 속성의 결합키(composite key)를 대신하여 기본키로 사용하기 위해 만든 대리키(surrogate key)이다. 따라서 레코드 생성 순서에 따라 자동으로 1씩 증가된 값이 저장되도록 설계했다.

예시 데이터 삽입

일반개체(regular entity)인 이용권, 자리, 학생 테이블의 레코드를 삽입한다. 참고로 구매, 대여 테이블의 레코드는 프로세스 진행 도중에 생성된다.

# 이용권(ticket) 데이터 삽입 (time 단위: 초)
insert into ticket(time, storable, price) values(7200, false, 3000);
insert into ticket(time, storable, price) values(14400, false, 5000);
insert into ticket(time, storable, price) values(21600, false, 7000);
insert into ticket(time, storable, price) values(28800, false, 9000);
insert into ticket(time, storable, price) values(43200, false, 10000);
insert into ticket(time, storable, price) values(180000, true, 60000);
insert into ticket(time, storable, price) values(360000, true, 100000);
insert into ticket(time, storable, price) values(540000, true, 160000);
insert into ticket(time, storable, price) values(720000, true, 200000);

# 자리(seat) 데이터 삽입
insert into seat values(1);
insert into seat values(2);
insert into seat values(3);

# 학생(student) 데이터 삽입
insert into student(student_id, name) values('A', '홍길동');
insert into student(student_id, name) values('B', '김철수');

프로세스 구현

앞서 RDB에서의 프로세스를 MySQL 코드로 구현한 결과는 다음과 같다. 구매나 대여 행위와 함께 자동적으로 실행되는 연산은 트리거(trigger)를 이용하여 구현했다.

-- 학생ID가 A인 학생의 이름
call get_student_name('A');   

-- 학생의 잔여이용시간과 모든 이용권 출력
select student_id as 학생ID, name as 이름, time_format(sec_to_time(residual_time), '%k시간 %i분 %s초') as 잔여이용시간 from student; -- where student_id = 'A';
select ticket_no as 이용권번호, time_format(sec_to_time(time), '%k시간 %i분 %s초') as 이용시간, if(storable, 'O', 'X') as 저장가능, format(price, 0) as 가격 from ticket;

-- A학생이 6번 이용권을 구매한다.('구매' 테이블 insert)
insert into purchase(student_id, ticket_no) values('A', 6);
-- 학생의 잔여이용시간 갱신 trigger('학생' 테이블 '잔여이용시간' update)

-- A학생의 이용권 구매내역 출력
call get_purchase_list('A');
select student_id as 학생ID, time_format(sec_to_time(residual_time), '%k시간 %i분 %s초') as 잔여이용시간 from student;

-- 대여 가능한 자리 목록 출력
call show_rentable_seat_list();

-- A학생이 1번 자리를 대여한다.('대여' 테이블 insert)
insert into Rent(student_id, seat_no) values('A', 1);
-- 대여의 예정마감일시 계산 trigger

-- A학생이 대여를 마감한다.('대여' 테이블 '실제마감시간' update)
call end_rent_process('A', now());
-- 학생의 잔여이용시간 갱신 trigger('학생' 테이블 '잔여이용시간' update)

-- A학생의 대여내역 출력
call get_rent_list('A');

이어서 정의된 프로시저와 트리거를 확인하고, 프로세스를 차례로 실행해보도록 하겠다.

1) 학생ID가 A인 학생의 이름

  • 프로시저 정의
# get_student_name 프로시저
drop procedure if exists get_student_name;

create procedure get_student_name(in s_id varchar(45))
    select name as 이름 from Student where student_id = s_id;
  • 프로세스
-- 학생ID가 A인 학생의 이름
call get_student_name('A');
  • 실행결과
이름
홍길동

2) 학생의 잔여이용시간과 모든 이용권 출력

  • 프로세스
-- 학생의 잔여이용시간 출력
select student_id as 학생ID, name as 이름, time_format(sec_to_time(residual_time), '%k시간 %i분 %s초') as 잔여이용시간 from student; # where student_id = 'A';
  • 실행결과
학생ID이름잔여이용시간
A홍길동0시간 06분 00초
B김철수0시간 00분 00초

  • 프로세스
-- 모든 이용권 출력
select ticket_no as 이용권번호, time_format(sec_to_time(time), '%k시간 %i분 %s초') as 이용시간, if(storable, 'O', 'X') as 저장가능, format(price, 0) as 가격 from ticket;
  • 실행결과
이용권번호이용시간저장가능가격
12시간 00분 00초X3,000
24시간 00분 00초X5,000
36시간 00분 00초X7,000
48시간 00분 00초X9,000
512시간 00분 00초X10,000
650시간 00분 00초O60,000
7100시간 00분 00초O100,000
8150시간 00분 00초O160,000
9200시간 00분 00초O200,000

3) 이용권 구매 및 잔여이용시간 갱신

  • 트리거 정의
# update_residual_time_after_purchase 트리거
drop trigger if exists update_residual_time_after_purchase;

create trigger update_residual_time_after_purchase
after insert on purchase
for each row
    update student
    set residual_time = residual_time + (select time from ticket where ticket_no = new.ticket_no)
    where student_id = new.student_id;
  • 프로세스
-- A학생이 6번 이용권을 구매한다.('구매' 테이블 insert)
insert into purchase(student_id, ticket_no) values('A', 6);
-- 학생의 잔여이용시간 갱신 trigger('학생' 테이블 '잔여이용시간' update)

# [점검] 학생 테이블 출력
select student_id as 학생ID, time_format(sec_to_time(residual_time), '%k시간 %i분 %s초') as 잔여이용시간 from student;
  • 실행결과
학생ID잔여이용시간
A50시간 00분 00초
B0시간 00분 00초

4) 학생의 이용권 구매내역 출력

  • 프로시저 정의
# get_purchase_list 프로시저
drop procedure if exists get_purchase_list;

create procedure get_purchase_list(in s_id varchar(45))
    select Purchase.date 일시, time_format(sec_to_time(Ticket.time), '%k시간 %i분 %s초') 이용시간, if(Ticket.storable, 'O', 'X') 저장가능, format(Ticket.price, 0) 가격
    from Purchase, Ticket
    where Purchase.ticket_no = Ticket.ticket_no and Purchase.student_id = s_id
    order by Purchase.date;
  • 프로세스
-- A학생의 이용권 구매내역 출력
call get_purchase_list('A');
  • 실행 결과
일시이용시간저장가능가격
2021-07-02 15:22:3250시간 00분 00초O60,000

5) 대여 가능한 자리 목록 출력

  • 프로시저 정의
# show_rentable_seat_list 프로시저
drop procedure if exists show_rentable_seat_list;

create procedure show_rentable_seat_list()
    select seat_no as 자리번호 from Seat where seat_no
        not in (select seat_no from Rent where isnull(real_end_date));
  • 프로세스
-- 대여 가능한 자리 목록 출력
call show_rentable_seat_list();
  • 실행 결과
자리번호
1
2
3

6) 자리 대여 시작 및 예정마감일시 계산

  • 트리거 정의
# cal_expected_end_date 트리거
drop trigger if exists cal_expected_end_date;

create trigger cal_expected_end_date
before insert on Rent
for each row
    set new.expected_end_date = date_add(new.start_date, 
        interval(select residual_time from Student where student_id = new.student_id) second);
  • 프로세스
-- A학생이 1번 자리를 대여한다.('대여' 테이블 insert)
insert into Rent(student_id, seat_no) values('A', 1);
-- 대여의 예정마감일시 계산 trigger

# [점검] 대여 테이블 출력
select student_id 학생ID, seat_no 자리번호, start_date 시작일시, real_end_date 실제마감일시, expected_end_date 예정마감일시
from rent order by start_date;
  • 실행 결과
학생ID자리번호시작일시실제마감일시예정마감일시
A12021-07-02 15:37:39NULL2021-07-04 17:37:39

7) 자리 대여 마감 및 잔여이용시간 갱신

학생은 대여를 시작할 때 계산된 예정마감일시까지 자리를 대여할 수 있다. 만약 그 이전에 학생이 퇴실을 원한다면, 대여를 마감한다(case1). 시스템은 1초마다 자동으로 대여 중인 모든 학생들의 예정마감일시를 현재시각과 비교하여 대여가 끝났는지 검사한다(case2).

예시 실행에서는 예정마감일시 이전에 학생이 직접 대여를 마감한다.

  • 프로시저, 이벤트 정의

(case1) A학생의 실제마감일시 입력

# end_rent_process 프로시저
drop procedure if exists end_rent_process;

create procedure end_rent_process(in s_id varchar(45), in r_et datetime)
    update Rent	set real_end_date = ifnull(r_et, now())
    where student_id = s_id and isnull(real_end_date);

(case2) 1초마다 자동으로 예정마감일시 검사

# event scheduler ON
show variables like 'event%';
set global event_scheduler=On;
set @@global.event_scheduler=On;

# check_expected_end_date 이벤트
drop event if exists check_expected_end_date;

create event check_expected_end_date
on schedule every 1 second 
starts current_timestamp
enable
do
    update Rent set real_end_date = expected_end_date
    where isnull(real_end_date) and expected_end_date <= current_timestamp();
  • 프로세스
-- A학생이 대여를 마감한다.('대여' 테이블 '실제마감시간' update)
call end_rent_process('A', now());
-- 학생의 잔여이용시간 갱신 trigger('학생' 테이블 '잔여이용시간' update)

# [점검] 학생 테이블 출력
select student_id as 학생ID, time_format(sec_to_time(residual_time), '%k시간 %i분 %s초') as 잔여이용시간 from student;
  • 실행 결과
학생ID잔여이용시간
A49시간 45분 58초
B0시간 00분 00초

8) 학생의 대여내역 출력

  • 프로시저 정의
#  get_rent_list 프로시저
drop procedure if exists get_rent_list;

create procedure get_rent_list(in s_id varchar(45))
    select seat_no 자리번호, start_date 시작일시, real_end_date 실제마감일시 from Rent where student_id = s_id
    order by start_date;
  • 프로세스
-- A학생의 대여내역 출력
call get_rent_list('A');
  • 실행 결과
자리번호시작일시실제마감일시
12021-07-02 15:37:392021-07-02 15:51:41

쿼리 구현

앞서 시스템 분석의 산출물인 시스템 요구사항 중, 프로세스 진행을 위한 쿼리 이외에 저장된 데이터에서 사용자인 스터디카페 운영자가 알고 싶은 정보는 아래와 같다.

  • A학생의 월 대여시간 합계
  • 스터디카페 월 매출

우선 매출이나 대여내역 등 과거의 데이터로부터 추출한 정보를 보여주는 쿼리를 실행하기 위해 구매, 대여 테이블에 레코드를 삽입한다. 데이터 삽입 결과를 확인해보면 아래와 같다.

학생 테이블

학생ID이름잔여이용시간
A홍길동86시간 59분 41초
B김철수0시간 00분 00초

구매 테이블 (이용권 테이블과 join하여 이용권 번호에 따른 이용권 속성을 함께 출력)

일시학생ID이용시간저장가능가격
2020-03-31 17:50:20B50시간 00분 00초O60,000
2020-03-31 23:40:50A4시간 00분 00초X5,000
2020-04-13 15:35:50A2시간 00분 00초X3,000
2020-04-30 13:06:17A6시간 00분 00초X7,000
2020-05-31 21:03:53A6시간 00분 00초X7,000
2020-06-11 09:05:01A100시간 00분 00초O100,000

대여 테이블

학생ID자리번호시작일시실제마감일시예정마감일시
B22020-03-31 17:55:212020-04-01 04:01:222020-04-02 19:55:21
A12020-03-31 23:45:122020-04-01 03:45:122020-04-01 03:45:12
A12020-04-13 15:40:002020-04-13 17:35:002020-04-13 17:40:00
B12020-04-23 09:14:502020-04-23 22:05:152020-04-25 01:08:49
B22020-04-30 15:01:242020-05-01 02:08:242020-05-01 18:04:58
A32020-04-30 18:10:052020-05-01 01:05:552020-05-01 00:10:05
B32020-05-31 20:35:402020-06-01 00:08:242020-06-01 12:32:14
A12020-05-31 21:06:302020-06-01 03:02:342020-06-01 03:06:30
A22020-06-11 09:06:162020-06-11 22:06:352020-06-15 13:06:16
B32020-06-17 09:35:402020-06-17 21:59:302020-06-17 21:59:30

이러한 사용자 요구사항을 MySQL 코드로 구현한 결과는 다음과 같다.

1) 월 대여시간 합계

4월 30일 22시에 대여를 시작하여 5월 1일 2시에 대여를 마감하는 예시처럼 대여기간이 월의 경계를 포함하는 경우가 있다. 이러한 경우에는 대여시간을 분리하여 앞의 3시간(22시~00시)은 4월 대여시간에, 뒤의 2시간은 5월 대여시간에 포함했다.

  • 프로시저 정의
# monthly_rent_time 프로시저
drop procedure if exists monthly_rent_time;

delimiter $$
create procedure monthly_rent_time(in s_id varchar(45), in _year int, in _month int)
begin
    select time_format(sec_to_time(sum(rent_time)), '%k시간 %i분 %s초') as 대여시간 from (
        select sum(timestampdiff(second, convert(last_day(real_end_date - interval 1 month) + interval 1 day, datetime), real_end_date)) as rent_time
            from Rent where month(start_date) < _month and month(real_end_date) = _month and student_id = s_id and year(real_end_date) = _year
        union all
        select sum(timestampdiff(second, start_date, real_end_date)) as rent_time
            from Rent where month(start_date) = _month and month(real_end_date) = _month and student_id = s_id and year(real_end_date) = _year
        union all
        select sum(timestampdiff(second, start_date, convert(last_day(start_date), datetime) + interval '23:59:59' hour_second)) as rent_time
            from Rent where month(start_date) = _month and month(real_end_date) > _month and student_id = s_id and year(start_date) = _year
	) c;

end $$
delimiter ;
  • 쿼리
-- A학생의 월 대여시간 합계
call monthly_rent_time('A', 2020, 4);
  • 실행 결과
대여시간
11시간 30분 06초

2) 월별 총 매출

  • 프로시저 정의
# monthly_total_sales 프로시저
drop procedure if exists monthly_total_sales;

delimiter $$
	create procedure monthly_total_sales(in _year int)
	begin
            select concat(month(Purchase.date), '월') as '기간', concat(format(sum(Ticket.price), 0), '원') as '매출'
            from Purchase, Ticket
            where Purchase.ticket_no = Ticket.ticket_no and year(Purchase.date) = _year
            group by month(Purchase.date)
            order by month(Purchase.date);
	end $$
delimiter ;
  • 쿼리
-- 어떤 연도의 월별 총 매출
call monthly_total_sales(2020);
  • 실행 결과
기간매출
3월65,000원
4월10,000원
5월7,000원
6월100,000원

profile
azzurri21
파이썬 백엔드 개발자
이전 포스트

[MySQL] 스터디카페 관리시스템 개발 (3) 시스템 설계

0개의 댓글