예약페이지 구현하기

[LKBDev]

by 규봉

[w2strose]

-- 1. 고객 테이블 create sequence Member_seq start with 1; ​ create table Zoo_Member( M_NUMBER number primary key, M_ID VARCHAR2(20) NOT NULL, M_PW VARCHAR2(20) NOT NULL, M_NAME VARCHAR2(20) NOT NULL, M_TEL VARCHAR2(20) NOT NULL, M_ADDRESS VARCHAR2(100) NOT NULL ); SELECT FROM Zoo_Member; ​ insert into Zoo_Member values(Member_seq.nextval,1,1,1,1,1); ​ -- 2. 사업자 테이블 create sequence businessPerson_seq start with 1; ​ create table Zoo_businessPerson( P_NUMBER number primary key, P_ID VARCHAR2(20) NOT NULL, P_PW VARCHAR2(20) NOT NULL, P_NAME VARCHAR2(20) NOT NULL, P_TEL VARCHAR2(20) NOT NULL, P_ADDRESS VARCHAR2(100) NOT NULL ); ​ insert into Zoo_businessPerson values(businessPerson_seq.nextval,2,2,2,2,2); ​ SELECT FROM Zoo_businessPerson; ​ -- 3. 호텔 정보 테이블 ​ create sequence Hotel_seq start with 1; ​ create table Zoo_Hotel( hP_NUMBER number, H_NUMBER NUMBER primary key, H_NAME VARCHAR2(30) NOT NULL, H_TEL VARCHAR2(20) NOT NULL, H_ADDRESS VARCHAR2(100) NOT NULL, H_ROOMTYPE VARCHAR2(200) NOT NULL, H_ROOMNUMBER VARCHAR2(200) NOT NULL, H_ROOMCHARGE VARCHAR2(200) NOT NULL, CONSTRAINT businessPerson_Hotel_FK FOREIGN KEY (hP_NUMBER) REFERENCES Zoo_businessPerson(P_NUMBER) ); ​ insert into Zoo_Hotel values(2,hotel_seq.nextval,3,3,3,3,3,3); drop table Zoo_Hotel; SELECT FROM Zoo_Hotel; ​ select from Zoo_businessPerson,Zoo_Hotel where P_NUMBER = 2; ​ select from zoo_businessperson,Zoo_Hotel where P_NUMBER = 2 and hP_NUMBER = 2; ​ ​ --4. 예약테이블 ​ create sequence reservation_seq start with 1; ​ create table Zoo_Reservation ( rM_NUMBER NUMBER, rH_NUMBER NUMBER, R_NUMBER NUMBER, R_DATE DATE not null, R_ROOM VARCHAR2(30) not null, R_MEMBER VARCHAR2(30) not null, CONSTRAINT MEMBER_RESERVATION_FK FOREIGN KEY (rM_NUMBER) REFERENCES Zoo_MEMBER(M_NUMBER), CONSTRAINT HOTEL_RESERVATION_FK FOREIGN KEY (rH_NUMBER) REFERENCES Zoo_Hotel(H_NUMBER) ); ​ select from Zoo_Reservation; ​ drop table Zoo_Reservation; insert into Zoo_Reservation values(2, 22 , reservation_seq.nextval,sysdate,1,1); ​ -- 5. 이벤트 테이블 create sequence event_seq start with 1; ​ create table Zoo_Event( E_NUMBER NUMBER primary key, E_NAME varchar2(30) not null, E_CONTENT varchar2(30) not null ); ​ insert into Zoo_Event values(event_seq.nextval, 2,2); select from Zoo_Event; ​ ​ -- 6. 유저 게시판 테이블 ​ create sequence Board_seq start with 1; ​ create table Zoo_Board( bM_NUMBER NUMBER, B_NUMBER NUMBER NOT NULL, B_SUBJECT VARCHAR2(30) NOT NULL, B_CONTENT VARCHAR2(500) NOT NULL, B_PASS NUMBER(4) NOT NULL, CONSTRAINT MEMBER_BOARD_FK FOREIGN KEY (bM_NUMBER) REFERENCES Zoo_MEMBER(M_NUMBER) ); ​ drop Table Zoo_Board; insert into Zoo_Board values(2, Board_seq.nextval,2,2,2); ​ select from Zoo_Board; ​ ​ -- 7. 관리자 테이블 create sequence Gm_seq start with 1; ​ create table Zoo_GM( gP_NUMBER NUMBER primary key, G_NUMBER NUMBER, G_ID VARCHAR2(30) NOT NULL, G_PASS NUMBER(4) NOT NULL, G_GRADE VARCHAR2(10) NOT NULL, CONSTRAINT BusinessPerson_BOARD_FK FOREIGN KEY (gP_NUMBER) REFERENCES Zoo_BusinessPerson(P_NUMBER) ); ​ select * from Zoo_GM; DROP TABLE ZOO_GM;

[LKBDev]

테이블 준 확정본

-- 1. 고객 테이블 create sequence Member_seq start with 1;

create table Zoo_Member( M_NUMBER number primary key, M_ID VARCHAR2(20) NOT NULL, M_PW VARCHAR2(20) NOT NULL, M_NAME VARCHAR2(20) NOT NULL, M_PHONE1 VARCHAR2(20) NOT NULL, M_PHONE2 VARCHAR2(20) NOT NULL, M_PHONE3 VARCHAR2(20) NOT NULL, M_EMAIL VARCHAR2(100) NOT NULL, M_BIRTH VARCHAR2 (35) NOT NULL );

insert into Zoo_Member values(Member_seq.nextval,1,1,1,1,1); SELECT * FROM Zoo_Member;

-- 2. 사업자 테이블 create sequence businessPerson_seq start with 1;

create table Zoo_businessPerson( P_NUMBER number primary key, P_ID VARCHAR2(20) NOT NULL, P_PW VARCHAR2(20) NOT NULL, P_NAME VARCHAR2(20) NOT NULL, P_PHONE1 VARCHAR2(20) NOT NULL, P_PHONE2 VARCHAR2(20) NOT NULL, P_PHONE3 VARCHAR2(20) NOT NULL, P_EMAIL VARCHAR2(100) NOT NULL, P_BIRTH VARCHAR2 (35) NOT NULL );

insert into Zoo_businessPerson values(businessPerson_seq.nextval,2,2,2,2,2); SELECT * FROM Zoo_businessPerson;

insert into Zoo_businessPerson values(businessPerson_seq.nextval,'MyID',1234,'김상복','010-1234-1234','서울특별시 영등포구 버드나루로 48');

-- 3. 호텔 정보 테이블

create sequence Hotel_seq start with 1;

create table Zoo_Hotel( P_NUMBER number, H_NUMBER NUMBER primary key, H_NAME VARCHAR2(30) NOT NULL, H_TEL VARCHAR2(20) NOT NULL, H_POSTNUM VARCHAR2(20) NOT NULL, H_ADDRESS VARCHAR2(500) NOT NULL, H_COMENT VARCHAR2(500) NOT NULL, H_BUNJIADDRESS VARCHAR2(500) NOT NULL, H_ROOMNUMBER VARCHAR2(500) NOT NULL, CONSTRAINT businessPerson_Hotel_FK FOREIGN KEY (H_NUMBER) REFERENCES Zoo_businessPerson(P_NUMBER) );

insert into Zoo_Hotel values(2,hotel_seq.nextval,3,3,3,3,3,3); SELECT * FROM Zoo_Hotel;

insert into Zoo_Hotel values(22,hotel_seq.nextval,'퍼플캣','02-743-5249','서울시 종로구 율곡로 223, 3층 퍼플캣','A ROOM','101','35000');

-- 4. 공실 테이블

create table Zoo_operation( H_NUMBER number, O_NUMBER number primary key, O_TYPE VARCHAR2(30) not null, O_DATE VARCHAR2(30) not null, O_CHARGE VARCHAR2(30) not null, O_OK VARCHAR2(2) not null, CONSTRAINT Hotel_Operation_FK FOREIGN KEY (O_NUMBER) REFERENCES Zoo_hotel(H_NUMBER) );

-- 5. 예약 테이블

create sequence reservation_seq start with 1;

create table Zoo_Reservation ( M_NUMBER NUMBER, O_NUMBER NUMBER, R_NUMBER NUMBER, R_DATE DATE not null, R_ROOM VARCHAR2(30) not null, R_MEMBER VARCHAR2(30) not null, CONSTRAINT MEMBER_RESERVATION_FK FOREIGN KEY (M_NUMBER) REFERENCES Zoo_MEMBER(M_NUMBER), CONSTRAINT Operation_RESERVATION_FK FOREIGN KEY (O_NUMBER) REFERENCES Zoo_Hotel(O_NUMBER) );

insert into Zoo_Reservation values(2, 22 , reservation_seq.nextval,sysdate,1,1); select * from Zoo_Reservation;

-- 6. 이벤트 테이블

create sequence event_seq start with 1;

create table Zoo_Event( E_number number(7) primary key, E_name varchar2(30) not null, E_content varchar2(2000) not null, E_image varchar2(30) not null, E_readcount number(5) default 0 not null, E_regdate timestamp(6) default sysdate not null, );

insert into Zoo_Event values(event_seq.nextval, 2,2); select * from Zoo_Event;

-- 7. 유저 게시판 테이블

create sequence Board_seq start with 1;

create table Zoo_Board( M_NUMBER NUMBER, B_NUMBER NUMBER NOT NULL, B_SUBJECT VARCHAR2(30) NOT NULL, B_CONTENT VARCHAR2(500) NOT NULL, B_PASS NUMBER(4) NOT NULL, CONSTRAINT MEMBER_BOARD_FK FOREIGN KEY (M_NUMBER) REFERENCES Zoo_MEMBER(M_NUMBER) );

drop Table Zoo_Board; insert into Zoo_Board values(2, Board_seq.nextval,2,2,2);

select * from Zoo_Board;

-- 8. 관리자 테이블 create sequence Gm_seq start with 1;

create table Zoo_GM( P_NUMBER NUMBER, G_NUMBER NUMBER primary key, G_ID VARCHAR2(30) NOT NULL, G_PASS NUMBER(4) NOT NULL, G_GRADE VARCHAR2(10) NOT NULL, CONSTRAINT BusinessPerson_BOARD_FK FOREIGN KEY (P_NUMBER) REFERENCES Zoo_BusinessPerson(P_NUMBER) );

select * from Zoo_GM; DROP TABLE ZOO_GM;

[LKBDev]

create SEQUENCE event_seq start with 0 INCREMENT by 1 MINVALUE 0 NOCACHE NOCYCLE;