'영화예매 db'에 해당되는 글 2건

1. ERD 모델링



- 모델링에서 어려웠던 점


좌석을 등록하게 되면 SEAT테이블에 등록이 되는데 SEAT테이블에서는 일정에 따른 좌석의 사용여부를 관리할 수 없다.

SEAT에 사용여부 컬럼을 넣게 되면 TIME테이블에서 일정코드를 외래키로 받아서 따로 관리해야 되는데, 그렇게 되면 관리자가 좌석을 등록할 때 일정도 함께 등록해야 하는 모순이 생겨버린다. 설령 일정을 NULL허용을 해놓고 SEAT을 등록한다 하더라도 NULL값이 들어가게 되면 나중에 일정등록할 때 SEAT테이블에 UPDATE를 해주면서 다시 등록을 해야하는데 이 쿼리가 좀 만만치 않다.

그래서 PERFORMANCE_SEAT 테이블을 만들어 놓고 SEATSTATUS라는 사용여부 컬럼을 두었다. 관리자는 SEAT테이블에 좌석만 등록하고 일정을 등록할 시에 일정에 따른 좌석정보를 PERFORMANCE_SEAT테이블에 등록하는 데 이 때 다음과 같은 쿼리를 쓴다.




- 코딩시 어려웠던 점


예약할때 BOOKING테이블MEMBER테이블PERFORMANCE_SEAT 테이블, BOOKED_SEATS 테이블 이렇게 4개에 INSERT하고 UPDATE할 때 쿼리가 복잡하다. 이를 프로시저를 통해 해결하고자 한다(추후작성)


2. TEST ROWS


insert into movie values('M1','미쓰홍당무','코믹','200분','김종환','12세','서울시립대','한국','공효진','미쓰홍당무.jpg','2015/04/01','정말 끝내주게 웃깁니다.');

insert into movie values('M2','킹스맨','액션','150분','강주희','15세','서울시립대','미국','Dan Brown','킹스맨.jpg','2015/04/02','멋진액션!');

insert into movie values('M3','살인의추억','스릴러','300분','최윤주','19세','서울시립대','한국','송강호,김상경','살인의추억.jpg','2015/04/25','과연 누가 살인자일까?');

insert into movie values('M4','포화속으로','전쟁','150분','김종환','15세','서울시립대','한국','탑,권상우','포화속으로.jpg','2015/04/21','6.25 현장');

insert into movie values('M5','신세계','느와르','200분','김종환','19세','서울시립대','한국','이정재,황정민,최민식','신세계.jpg','2015/03/24','황정민 이정재 최민식 세남자의 느와르');

insert into movie values('M6','파파로티','드라마','150분','강주희','전체','서울시립대','한국','이제훈,한석규','파파로티.jpg','2015/03/21','테너의 감동적인 실화');

insert into movie values('M7','완득이','드라마','300분','최윤주','전체','서울시립대','한국','김윤석,유아인','완득이.jpg','2015/05/23','사제지간을 그린 드라마');

insert into movie values('M8','화이','스릴러','100분','김종환','19세','서울시립대','한국','여진구,김윤석','화이.jpg','2015/04/21','여진구를 다시보게 되는 작품');



insert into screen values('S1','1관');

insert into screen values('S2','2관');

insert into screen values('S3','3관');

insert into screen values('S4','4관');

insert into screen values('S5','5관');

insert into screen values('S6','6관');

insert into screen values('S7','7관');

insert into screen values('S8','8관');



insert into screen_movie values('S1','M1','2015-04-01','2015-05-29');

insert into screen_movie values('S1','M2','2015-03-28','2015-04-29');

insert into screen_movie values('S1','M3','2015-04-25','2015-06-02');

insert into screen_movie values('S1','M4','2015-03-17','2015-05-10');

insert into screen_movie values('S2','M1','2015-04-01','2015-05-29');

insert into screen_movie values('S2','M2','2015-03-28','2015-04-29');

insert into screen_movie values('S2','M3','2015-04-25','2015-06-02');

insert into screen_movie values('S2','M4','2015-03-17','2015-05-10');

insert into screen_movie values('S3','M1','2015-04-01','2015-05-29');

insert into screen_movie values('S3','M2','2015-03-28','2015-04-29');

insert into screen_movie values('S3','M3','2015-04-25','2015-06-02');

insert into screen_movie values('S3','M4','2015-03-17','2015-05-10');

insert into screen_movie values('S4','M1','2015-04-01','2015-05-29');

insert into screen_movie values('S4','M2','2015-03-28','2015-04-29');

insert into screen_movie values('S4','M3','2015-04-25','2015-06-02');

insert into screen_movie values('S4','M4','2015-03-17','2015-05-10');

insert into screen_movie values('S5','M1','2015-04-01','2015-05-29');

insert into screen_movie values('S5','M2','2015-03-28','2015-04-29');

insert into screen_movie values('S5','M3','2015-04-25','2015-06-02');

insert into screen_movie values('S5','M4','2015-03-17','2015-05-10');

insert into screen_movie values('S6','M1','2015-04-01','2015-05-29');

insert into screen_movie values('S6','M2','2015-03-28','2015-04-29');

insert into screen_movie values('S6','M3','2015-04-25','2015-06-02');

insert into screen_movie values('S6','M4','2015-03-17','2015-05-10');

insert into screen_movie values('S7','M1','2015-04-01','2015-05-29');

insert into screen_movie values('S7','M2','2015-03-28','2015-04-29');

insert into screen_movie values('S7','M3','2015-04-25','2015-06-02');

insert into screen_movie values('S7','M4','2015-03-17','2015-05-10');

insert into screen_movie values('S8','M1','2015-04-01','2015-05-29');

insert into screen_movie values('S8','M2','2015-03-28','2015-04-29');

insert into screen_movie values('S8','M3','2015-04-25','2015-06-02');

insert into screen_movie values('S8','M4','2015-03-17','2015-05-10');



insert into time values('S12015-05-011','07:20','09:20','2015-05-01',1,'S1','M1');

insert into time values('S12015-05-012','10:20','12:20','2015-05-01',2,'S1','M2');

insert into time values('S12015-05-013','13:20','15:20','2015-05-01',3,'S1','M3');

insert into time values('S12015-05-014','16:20','18:20','2015-05-01',4,'S1','M4');



insert into time values('S22015-05-011','07:20','09:20','2015-05-01',1,'S2','M1');

insert into time values('S22015-05-012','10:20','12:20','2015-05-01',2,'S2','M2');

insert into time values('S22015-05-013','13:20','15:20','2015-05-01',3,'S2','M3');

insert into time values('S22015-05-014','16:20','18:20','2015-05-01',4,'S2','M4');

insert into time values('S22015-05-015','18:40','20:20','2015-05-01',5,'S2','M1');



insert into time values('S32015-05-011','07:20','09:20','2015-05-01',1,'S3','M1');

insert into time values('S32015-05-012','10:20','12:20','2015-05-01',2,'S3','M2');

insert into time values('S32015-05-013','13:20','15:20','2015-05-01',3,'S3','M3');

insert into time values('S32015-05-014','16:20','18:20','2015-05-01',4,'S3','M4');



insert into time values('S42015-05-011','07:20','09:20','2015-05-01',1,'S4','M1');

insert into time values('S42015-05-012','10:20','12:20','2015-05-01',2,'S4','M2');

insert into time values('S42015-05-013','13:20','15:20','2015-05-01',3,'S4','M3');

insert into time values('S42015-05-014','16:20','18:20','2015-05-01',4,'S4','M4');



insert into seat values('A1','S1','A','1');

insert into seat values('A2','S1','A','2');

insert into seat values('A3','S1','A','3');

insert into seat values('A4','S1','A','4');

insert into seat values('A5','S1','A','5');

insert into seat values('A6','S1','A','6');

insert into seat values('A7','S1','A','7');

insert into seat values('A8','S1','A','8');

insert into seat values('A9','S1','A','9');

insert into seat values('A10','S1','A','10');

insert into seat values('A11','S1','A','11');

insert into seat values('A12','S1','A','12');

insert into seat values('A13','S1','A','13');

insert into seat values('A14','S1','A','14');

insert into seat values('A15','S1','A','15');

insert into seat values('A16','S1','A','16');

insert into seat values('A17','S1','A','17');

insert into seat values('A18','S1','A','18');

insert into seat values('B1','S1','B','1');

insert into seat values('B2','S1','B','2');

insert into seat values('B3','S1','B','3');

insert into seat values('B4','S1','B','4');

insert into seat values('B5','S1','B','5');

insert into seat values('B6','S1','B','6');

insert into seat values('B7','S1','B','7');

insert into seat values('B8','S1','B','8');

insert into seat values('B9','S1','B','9');

insert into seat values('B10','S1','B','10');

insert into seat values('B11','S1','B','11');

insert into seat values('B12','S1','B','12');

insert into seat values('B13','S1','B','13');

insert into seat values('B14','S1','B','14');

insert into seat values('B15','S1','B','15');

insert into seat values('B16','S1','B','16');

insert into seat values('B17','S1','B','17');

insert into seat values('B18','S1','B','18');



블로그 이미지

종환 Revolutionist-JongHwan

github.com/alciakng 항상 겸손하자.

 

 

영화예매 db를 짜고있다...

 

test row들..

 

insert into movie values('m1','미쓰홍당무','코믹','200분','김종환','정말 끝내주게 웃깁니다.','미쓰홍당무',0,'서울시립대');
insert into movie values('m2','킹스맨','액션','150분','강주희','액션 좋아요.','킹스맨',0,'서울시립대');
insert into movie values('m3','살인의추억','스릴러','300분','최윤주','정말 스릴있어요','미쓰홍당무',0,'서울시립대');
insert into movie values('m4','천재','드라마','100분','김종환','이사람 천잰가?','천재',0,'서울시립대');

insert into screen values('s1','1관');
insert into screen values('s2','2관');
insert into screen values('s3','3관');
insert into screen values('s4','4관');
insert into screen values('s5','5관');
insert into screen values('s6','6관');
insert into screen values('s7','7관');
insert into screen values('s8','8관');


insert into screen_movie values('s1','m1','2015-04-01','2015-05-29');
insert into screen_movie values('s1','m2','2015-03-28','2015-04-29');
insert into screen_movie values('s1','m3','2015-04-25','2015-06-02');
insert into screen_movie values('s1','m4','2015-03-17','2015-05-10');
insert into screen_movie values('s2','m1','2015-04-01','2015-05-29');
insert into screen_movie values('s2','m2','2015-03-28','2015-04-29');
insert into screen_movie values('s2','m3','2015-04-25','2015-06-02');
insert into screen_movie values('s2','m4','2015-03-17','2015-05-10');
insert into screen_movie values('s3','m1','2015-04-01','2015-05-29');
insert into screen_movie values('s3','m2','2015-03-28','2015-04-29');
insert into screen_movie values('s3','m3','2015-04-25','2015-06-02');
insert into screen_movie values('s3','m4','2015-03-17','2015-05-10');
insert into screen_movie values('s4','m1','2015-04-01','2015-05-29');
insert into screen_movie values('s4','m2','2015-03-28','2015-04-29');
insert into screen_movie values('s4','m3','2015-04-25','2015-06-02');
insert into screen_movie values('s4','m4','2015-03-17','2015-05-10');
insert into screen_movie values('s5','m1','2015-04-01','2015-05-29');
insert into screen_movie values('s5','m2','2015-03-28','2015-04-29');
insert into screen_movie values('s5','m3','2015-04-25','2015-06-02');
insert into screen_movie values('s5','m4','2015-03-17','2015-05-10');
insert into screen_movie values('s6','m1','2015-04-01','2015-05-29');
insert into screen_movie values('s6','m2','2015-03-28','2015-04-29');
insert into screen_movie values('s6','m3','2015-04-25','2015-06-02');
insert into screen_movie values('s6','m4','2015-03-17','2015-05-10');
insert into screen_movie values('s7','m1','2015-04-01','2015-05-29');
insert into screen_movie values('s7','m2','2015-03-28','2015-04-29');
insert into screen_movie values('s7','m3','2015-04-25','2015-06-02');
insert into screen_movie values('s7','m4','2015-03-17','2015-05-10');
insert into screen_movie values('s8','m1','2015-04-01','2015-05-29');
insert into screen_movie values('s8','m2','2015-03-28','2015-04-29');
insert into screen_movie values('s8','m3','2015-04-25','2015-06-02');
insert into screen_movie values('s8','m4','2015-03-17','2015-05-10');

insert into time values('s1','m1','07:20','09:20','2015-04-01',1);
insert into time values('s1','m2','10:20','12:20','2015-04-01',2);
insert into time values('s1','m3','13:20','15:20','2015-04-01',3);
insert into time values('s1','m4','16:20','18:20','2015-04-01',4);

insert into time values('s2','m1','07:20','09:20','2015-04-01',1);
insert into time values('s2','m2','10:20','12:20','2015-04-01',2);
insert into time values('s2','m3','13:20','15:20','2015-04-01',3);
insert into time values('s2','m4','16:20','18:20','2015-04-01',4);

insert into time values('s3','m1','07:20','09:20','2015-04-01',1);
insert into time values('s3','m2','10:20','12:20','2015-04-01',2);
insert into time values('s3','m3','13:20','15:20','2015-04-01',3);
insert into time values('s3','m4','16:20','18:20','2015-04-01',4);

insert into time values('s4','m1','07:20','09:20','2015-04-01',1);
insert into time values('s4','m2','10:20','12:20','2015-04-01',2);
insert into time values('s4','m3','13:20','15:20','2015-04-01',3);
insert into time values('s4','m4','16:20','18:20','2015-04-01',4);

 



insert into seat values('s1','a','1');

insert into seat values('s1','a','2');

insert into seat values('s1','a','3');

insert into seat values('s1','a','4');

insert into seat values('s1','a','5');

insert into seat values('s1','a','6');

insert into seat values('s1','a','7');

insert into seat values('s1','a','8');

insert into seat values('s1','a','9');

insert into seat values('s1','a','10');

insert into seat values('s1','a','11');

insert into seat values('s1','a','12');

insert into seat values('s1','a','13');

insert into seat values('s1','a','14');

insert into seat values('s1','a','15');

insert into seat values('s1','a','16');

insert into seat values('s1','a','17');

insert into seat values('s1','a','18');

insert into seat values('s1','b','1');

insert into seat values('s1','b','2');

insert into seat values('s1','b','3');

insert into seat values('s1','b','4');

insert into seat values('s1','b','5');

insert into seat values('s1','b','6');

insert into seat values('s1','b','7');

insert into seat values('s1','b','8');

insert into seat values('s1','b','9');

insert into seat values('s1','b','10');

insert into seat values('s1','b','11');

insert into seat values('s1','b','12');

insert into seat values('s1','b','13');

insert into seat values('s1','b','14');

insert into seat values('s1','b','15');

insert into seat values('s1','b','16');

insert into seat values('s1','b','17');

insert into seat values('s1','b','18');


commit;

 

부트스트랩 기반으로 영화 리스트 ui를 만들고 이에 필요한 query를 짜야한다.

 

(1) 특정 상영관과 날짜를 선택했을때 영화목록을 받아오고 (선호도, 개봉일자) 중 하나를 택하여 정렬할수 있는 쿼리

 

(2) (1)에 더하여 영화를 카테고리별로 받아오는 쿼리

 

 

ex)

//스크린코드가 s1인 스크린에 상영중인 영화를 출력하는 쿼리.
select * from movie where moviecode IN (select moviecode from screen_movie where SCREENCODE='s1');
//스크린코드가 s1이고 현재날짜가 해당영화의 시작일과 종료일 사이에 있을때 해당하는 영화를 출력하는 쿼리.
select * from movie where moviecode IN (select moviecode from screen_movie where SCREENCODE='s1' and '2015-05-20' > STARTDATE and '2015-05-20'< ENDDATE);
//스크린코드가 s1이고 현재날짜가 해당영화의 시작일과 종료일 사이에 있고 장르가 코믹인 영화를 출력하는 쿼리.
select * from movie where moviecode IN (select moviecode from screen_movie where SCREENCODE='s1' and '2015-05-20' > STARTDATE and '2015-05-20'< ENDDATE) and genre='코믹';
//스크린코드가 s1이고 현재날짜가 해당영화의 시작일과 종료일 사이에 있고 장르가 코믹인 영화를 선호도가 높은 영화부터 정렬하여 출력하는 쿼리.
select * from movie where moviecode IN (select moviecode from screen_movie where SCREENCODE='s1' and '2015-05-20' > STARTDATE and '2015-05-20'< ENDDATE) and genre='코믹' order by rating DESC;

블로그 이미지

종환 Revolutionist-JongHwan

github.com/alciakng 항상 겸손하자.