Oracle 이용한 DataBase 설계

WEB-INF

-jsp

-정적자원을 넣으면 안됨-외부에서 접근이 불가능함

-Controller를 통해서만 접근이 가능함

 

webapp=/

http://localhost:8888/

 

resources

-정적자원 

-http://localhost:8888/resources

 

 

Reverse Engineering

쇼핑몰에서 가장 중요한것= 상품관리

 

쿼리스크립트

create table  topcategory(
   topcategory_id  number primary key 
 , name varchar(25) 
 , rank number 
);
create table subcategory(
  subcategory_id number primary key
 , topcategory_id number
 , name varchar(25)
 , constraint fk_topcategorysubcategory  foreign key(topcategory_id) references topcategory(topcategory_id)
);
create table product(
 product_id number primary key
 , subcategory_id number
 , product_name varchar(30)
 , price number default 0
 , brand varchar(39)
 , detail clob
 , constraint fk_subcategoryproduct foreign key(subcategory_id) references subcategory(subcategory_id)
);

create table psize(
   psize_id number primary key
 , product_id number 
 , fit varchar(8)
 , constraint fk_productpsize foreign key(product_id) references product(product_id) 
);
create table color(
   color_id number primary key
 , product_id number 
 , picker varchar(20)
 , constraint fk_productcolor foreign key(product_id) references product(product_id) 
);
create table cart(
   cart_id number primary key
 , product_id number 
 , quantity number default 0
 , constraint fk_productcart foreign key(product_id) references product(product_id) 
);
create table score(
   score_id number primary key
 , product_id number 
 , star number default 0
 , constraint fk_productscore foreign key(product_id) references product(product_id) 
);
create table image(
   image_id number primary key
 , product_id number 
 , filename varchar(40)
 , constraint fk_productimage foreign key(product_id) references product(product_id) 
);



insert into topcategory (topcategory_id, name, rank) values( seq_topcategory.nextval,'top wear', 1);
insert into topcategory (topcategory_id, name, rank) values( seq_topcategory.nextval,'down wear', 2);
insert into topcategory (topcategory_id, name, rank) values( seq_topcategory.nextval,'accessory', 3);
insert into topcategory (topcategory_id, name, rank) values( seq_topcategory.nextval,'shoes', 4);

insert into subcategory (subcategory_id, topcategory_id, name) values( seq_subcategory.nextval, 1, '가디건');
insert into subcategory (subcategory_id, topcategory_id, name) values( seq_subcategory.nextval, 1, '티셔츠');
insert into subcategory (subcategory_id, topcategory_id, name) values( seq_subcategory.nextval, 1, '점퍼');
insert into subcategory (subcategory_id, topcategory_id, name) values( seq_subcategory.nextval, 1, '니트');
insert into subcategory (subcategory_id, topcategory_id, name) values( seq_subcategory.nextval, 2, '청바지');
insert into subcategory (subcategory_id, topcategory_id, name) values( seq_subcategory.nextval, 2, '반바지');
insert into subcategory (subcategory_id, topcategory_id, name) values( seq_subcategory.nextval, 2, '면바지');
insert into subcategory (subcategory_id, topcategory_id, name) values( seq_subcategory.nextval, 2, '정장바지');
insert into subcategory (subcategory_id, topcategory_id, name) values( seq_subcategory.nextval, 3, '귀걸이');
insert into subcategory (subcategory_id, topcategory_id, name) values( seq_subcategory.nextval, 3, '목걸이');
insert into subcategory (subcategory_id, topcategory_id, name) values( seq_subcategory.nextval, 3, '팔찌');
insert into subcategory (subcategory_id, topcategory_id, name) values( seq_subcategory.nextval, 3, '반지');
insert into subcategory (subcategory_id, topcategory_id, name) values( seq_subcategory.nextval, 4, '운동화');
insert into subcategory (subcategory_id, topcategory_id, name) values( seq_subcategory.nextval, 4, '구두');
insert into subcategory (subcategory_id, topcategory_id, name) values( seq_subcategory.nextval, 4, '샌들');
insert into subcategory (subcategory_id, topcategory_id, name) values( seq_subcategory.nextval, 4, '슬리퍼');