[Data Modeling] 중고장터

 

 

 

Code

 인기 검색어 1위부터 10위까지 스크립트 상에 출력

DECLARE   
    product_name varchar2(50);
    product_count number;
    
BEGIN    
        dbms_output.put_line('인기 검색어 순위!');
        dbms_output.put_line('-------------------------------------');
        
    for i in 1..10 loop
        select search_word, count_in into product_name, product_count from (
            select rownum rw, sr.*
            from (select search_word, count(*) count_in from search
                group by search_word
                order by count(*) desc            
            ) sr)          
            where rw = i; 
            
            if i < 10 then
                dbms_output.put_line(i||'  '||product_name);
                --검색횟수 추가
                dbms_output.put_line('                 '||'총 검색횟수 : '||product_count);
            else
                dbms_output.put_line(i||' '||product_name);
                --검색횟수 추가               
                dbms_output.put_line('                 '||'총 검색횟수 : '||product_count);
            end if;            
    end loop; 
END;
/

 

 

 상품 수정 및 삭제

--수정
update product_info 
    set category_no=1, product_name='갤럭시 탭 S7'
    where product_no=1 and membership_no=10;

--삭제    
delete product_info
where product_no=7;

 

 

 상품 확인

select pi.*, nvl(jj.jjim_count,0) 찜, rv.contents 리뷰
from product_info pi 

left outer join (select product_no, count(*) jjim_count from jjim group by product_no) jj
on pi.product_no = jj.product_no

inner join review rv
on pi.product_no = rv.product_no

order by pi.product_no asc
;

 

 

 검색

select count(*) from product_info
where product_name like '%갤럭시%';

select count_in from(select count(*) count_in from product_info
where product_name like '%갤럭시%');

select count(*)  from product_info
    where product_name like '%'||founded_product_id||'%';

DECLARE    
    find_product_name VARCHAR2(50) := '갤럭시';
    
    row_count number;
    founded_product_id number;
    product_no product_info.product_no%TYPE;
    membership_no product_info.membership_no%TYPE;
    category_no product_info.category_no%TYPE;
    product_name product_info.product_name%TYPE;
    product_price product_info.product_price%TYPE;

BEGIN    
    select count(*) into row_count from product_info
    where product_name like '%'||find_product_name||'%';
    dbms_output.put_line('검색어 : '||find_product_name);
    dbms_output.put_line('검색 일치 개수 : '||row_count);
    --dbms_output.put_line('row_count : '||SQL%ROWCOUNT);

for i in 1..row_count loop --17
    select product_no, membership_no, category_no, product_name, product_price 
    into product_no, membership_no, category_no, product_name, product_price
    from (
        select rownum rw, pi.*
        from product_info pi
        where product_name like '%'||find_product_name||'%')
        where rw=i;
        
        dbms_output.put_line(i||'.');
        dbms_output.put_line('제품 번호   : '||product_no);
        --dbms_output.put_line('판매자 번호 : '||membership_no);
        dbms_output.put_line('상품 이름   : '||product_name);
        dbms_output.put_line('상품 가격   : '||product_price);
        dbms_output.put_line('');
END LOOP;
    
END;
/

 

 

거래 과정

declare
    trading_product_no product_info.product_no%TYPE := 10;
    trading_product_price product_info.product_price%TYPE;
    trading_payment_method product_info.payment_method%TYPE;
    transaction_no_count number := 4;
    
    trading_meeting_place varchar2(50);
    trading_meetind_date date;
    
    trading_seller_accountnumber varchar2(50);
    
begin
    update product_info
    set progress='거래중'
    where trading_product_no = product_no;
    
    select product_price, payment_method into trading_product_price, trading_payment_method from product_info
    where product_no = trading_product_no;
    
    insert into transaction--20
    values (transaction_no_count, trading_product_no, trading_product_price, trading_payment_method);
    
    --직거래 : 거래장소, 시간 입력 or 택배거래 : 판매자계좌번호 입력
    
    dbms_output.put_line('테스트');
    
    
    if trading_payment_method = '직거래' then
        trading_meeting_place := '사당역';
        --trading_meetind_date := to_char('2022/03/25 09:30:00', 'yyyy/mm/dd hh24:mi:ss');
        trading_meetind_date := to_date('2022/03/26 19:30', 'yyyy/mm/dd hh24:mi');
        insert into direct_transaction
        values(transaction_no_count, trading_meeting_place, trading_meetind_date);
        dbms_output.put_line('직거래');

    elsif trading_payment_method = '택배거래' then
        trading_seller_accountnumber := '222-222-222222';    
        dbms_output.put_line('택배거래');
        insert into delivery_transaction
        values(transaction_no_count, trading_seller_accountnumber);
    end if;
end;
/

 

 

* SQL Developer의 Data format을 변경하여 data 자료형이 '시:분:초' 까지 표시하도록 변경

alter session set nls_date_format = 'yyyy/mm/dd hh24:mi:ss';

 

 

 모든 테이블 생성

--*1.회원관리 테이블

CREATE TABLE member_management(
   membership_no NUMBER, -- 회원번호 pk
   id VARCHAR2(20), -- id
   pw VARCHAR2(20), -- pw
   nickname VARCHAR2(20), -- 닉네임
   email VARCHAR2(20), -- e메일
   phoneno varchar2(15), -- 전화번호
   totalsalesamount NUMBER, -- 총판매금액
   totalpurchaseamount NUMBER, -- 총구매금액

    CONSTRAINT mm_member_management_pk PRIMARY KEY(membership_no),
    CONSTRAINT mm_member_management_unique UNIQUE(id, nickname, email, phoneno)
);

alter table member_management
modify totalsalesamount number default 0;
alter table member_management
modify totalpurchaseamount default 0;
alter table member_management
modify phoneno varchar2(15);

drop table member_management cascade constraints;
drop table product_info;
drop table search;

--*2.상품정보 테이블
CREATE TABLE product_info(
    product_no NUMBER,
    membership_no NUMBER,
    --(1.모바일/태블릿 2.패션의류 3.가구인테리어 4.카메라/캠코더 5.무료나눔)
    category_no NUMBER,
    product_name VARCHAR2(50),        
    product_price NUMBER,
    product_description VARCHAR2(100),
    payment_method VARCHAR2(100),
    progress VARCHAR2(100) default '판매중',
    
    CONSTRAINT pi_product_no_pk PRIMARY KEY(product_no),
    CONSTRAINT  pi_membership_no_fk FOREIGN KEY(membership_no)
        references member_management(membership_no)
);
drop table product_info;

alter table product_info
drop column progress;

alter table product_info
add progress VARCHAR2(100) DEFAULT '판매중';

alter table product_info
add product_name varchar2(50);

alter table product_info
drop constraint pi_product_no_fk;

--제약조건 확인
select * from USER_CONSTRAINTS
where table_name = 'PRODUCT_INFO';


--*3. 검색 테이블
CREATE TABLE search(
    membership_no NUMBER,
    search_word VARCHAR2(50),    
    
    CONSTRAINT se_membership_no_fk FOREIGN KEY(membership_no)
        references member_management(membership_no)
);
drop table search;


--*4. 찜 현황
CREATE TABLE jjim(   
    jjim_no NUMBER,
    product_no NUMBER,
    membership_no NUMBER,
    
    CONSTRAINT jj_jjim_no_fk FOREIGN KEY(jjim_no)
        references member_management(membership_no),
    CONSTRAINT jj_product_no_fk FOREIGN KEY(product_no)
        references product_info(product_no)        
);

drop table jjim;

--*5 리뷰 테이블
CREATE TABLE review(
    review_no NUMBER,
    product_no NUMBER,
    contents VARCHAR2(100),
    
    CONSTRAINT rv_review_no_pk PRIMARY KEY(review_no),
    CONSTRAINT rv_product_no_fk FOREIGN KEY(product_no)
        references product_info(product_no)
);


--*6. 블랙리스트 테이블
CREATE TABLE blacklist(
   blacklist_no NUMBER, -- 블랙리스트 등록 번호 pk
   membership_no NUMBER, -- 회원번호 fk
   nickname VARCHAR2(20), -- 닉네임
   name VARCHAR2(20), -- 이름
   phone_no NUMBER, -- 전화번호
   account_no NUMBER, -- 계좌번호
   
   CONSTRAINT bl_blacklist_no_pk PRIMARY KEY(blacklist_no),
   CONSTRAINT bl_membership_no_fk FOREIGN KEY(membership_no)
    references member_management(membership_no)
);


--*7. 거래내역 테이블
CREATE TABLE transaction(
    transaction_no NUMBER,
    product_no NUMBER,
    product_price NUMBER,
    transaction_method VARCHAR2(100),
    
    CONSTRAINT ts_transaction_no_pk PRIMARY KEY(transaction_no),
    CONSTRAINT ts_product_no_fk FOREIGN KEY(product_no)
        references product_info(product_no)
);

drop table transaction;

--*8 직거래
CREATE TABLE direct_transaction(
    transaction_no NUMBER,
    meeting_place VARCHAR2(50),
    meeting_date date,
    
    CONSTRAINT dr_transaction_no_pk PRIMARY KEY(transaction_no),
    CONSTRAINT dr_transaction_no_fk FOREIGN KEY(transaction_no)
        references transaction(transaction_no)
);

drop table direct_transaction;

--*9 택배거래
CREATE TABLE delivery_transaction(
    transaction_no NUMBER,
    seller_accountnumber varchar2(50),
    
    CONSTRAINT dl_transaction_no_pk PRIMARY KEY(transaction_no),
    CONSTRAINT dl_transaction_no_fk FOREIGN KEY(transaction_no)
        references transaction(transaction_no)
);

drop table delivery_transaction;

--oracle column pk fk 조회
--출처 : https://cofs.tistory.com/381
SELECT AA.COLUMN_ID,
         AA.COLUMN_NAME,
         BB.COMMENTS,
         AA.DATA_TYPE,
         AA.DATA_DEFAULT,
         CC.PK,
         AA.NULLABLE,
         CC.FK
    FROM ALL_TAB_COLUMNS@AIDP AA,
         ALL_COL_COMMENTS@AIDP BB,
         (SELECT A.OWNER,
                 A.TABLE_NAME,
                 A.CONSTRAINT_TYPE,
                 COLUMN_NAME,
                 POSITION,
                 CASE WHEN A.CONSTRAINT_TYPE = 'P' THEN 'Y' END AS PK,
                 CASE WHEN A.CONSTRAINT_TYPE = 'R' THEN 'Y' END AS FK
            FROM ALL_CONSTRAINTS@AIDP A, ALL_CONS_COLUMNS@AIDP B
           WHERE     UPPER (A.OWNER) = UPPER ('owner')
                 AND A.TABLE_NAME = UPPER ('table_name')
                 AND A.TABLE_NAME = B.TABLE_NAME
                  AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
                 AND A.CONSTRAINT_TYPE IN ('P', 'F')) CC
   WHERE     UPPER (AA.OWNER) = UPPER ('owner')
         AND UPPER (AA.TABLE_NAME) = UPPER ('table_name')
         AND AA.OWNER = BB.OWNER
         AND AA.TABLE_NAME = BB.TABLE_NAME
         AND AA.COLUMN_NAME = BB.COLUMN_NAME
         AND AA.OWNER = CC.OWNER(+)
         AND AA.TABLE_NAME = CC.TABLE_NAME(+)
         AND AA.COLUMN_NAME = CC.COLUMN_NAME(+)
ORDER BY COLUMN_ID;

 

 

피드백

강사님 피드백 기반으로 ERD를 수정했는데, 아직 감이 잘 오지 않는다.

데이터 모델링이 여전히 추상적이라 느껴져서 그런 것 같고, 구체적으로 이해하기위해 공부를 더 해야될 것 같다...