6일차) 주요 내용 복습 및 외래키 정의 20180605_pm

요구사항 분석설계 주요 개념

분석설계의 주요내용

개체(명확, 단어), 속성(개체의 성질), 관계

설계의 3단계

개논물.

개념설계(개체 간 관계), 

논리설계(개체의 성질, 제약사항설정, 정규화), 

물리설계(속성의 자료형, 크기, 물리적인 약속)

데이터베이스의 개념

공유, 통합, 저장, 운영

개체는 데이터베이스로 넘어가면서 테이블로 불린다

테이블의      가로 영역은 행, 레코드, 튜플, 로우

세로 영역은 열, 속성, 어트리뷰트, 필드, 컬럼 

트랜젝션

데이터베이스가 수정(변경)을 하기위해 수행되어야 할 논리적 단위

여러개의 SQL로 구성

특징 - 원 일 고 지          *원자성 all of nothing 

 ( 자 관 립 속 )        *일관성 한방향으로만

키의 종류

후보키, 기본키(PK), 대체키, 복합키

외래키(FK)

SQL 구조화된 질의 언어

DDL 정의         - create, alter, drop

DML               - insert, update, delete, select

DCL 조작         - grant, alter

TCL 트랜젝션    - commit, rollback, savepoint

alter - 컬럼  옵션

add 추가

modify 수정

drop 삭제

rename 이름변경

데이터베이스 구현 개념

테이블(TableName) 개체의 속성에 값 넣기

alter    table    TableName

add(

컬럼명    자료형(크기 값)    N/N 여부

)      //CTRL + ENTER

테이블(TableName) 개체의 속성에 값 넣기

ALTER    TABLE    TableName

ADD(

CONSTRAINT [고유한 유일한 이름 작명] FOREIGN KEY(자식 키 이름)

REFERENCES[부모 테이블명](부모 키 이름);

특정 키 찾기

select    *    from    USER_CONSTRIANS; 

//시스템에서 제공하는 미리 선언한 테이블 명

//사용자가 선언한 제약사항을 검색해서 보여줌

데이터베이스 구현

CREATE TABLE LPROD  --상품분류 테이블

(

    LPROD_ID    NUMBER(5)   NOT NULL,   --순

    LPROD_GU    CHAR(4) NOT NULL,   --상품분류코드

    LPROD_NM    VARCHAR(40) NOT NULL,   --상품분류명

    CONSTRAINT  PK_LPROD PRIMARY KEY(LPROD_GU)  --식별영역(key)

);

SELECT * FROM LPROD;

CREATE TABLE BUYER  --거래정보 테이블

(

    BUYER_ID    CHAR(6) NOT NULL,    --거래코드  

    --//키 정의(제약사항) 추가하기 1) BUYER_ID    CHAR(6) NOT NULL + "PRIMARY KEY" 추가

    -- 하지만 이미 테이블을 create한 이후엔 불가능

    BUYER_NAME  VARCHAR2(40) NOT NULL,  --거래처명

    BUYER_LGU   CHAR(4) NOT NULL,   --취급상품대분류

    BUYER_BANK  VARCHAR2(60),   --은행

    BUYER_BANKNO    VARCHAR2(60),   --계좌번호

    BUYER_BANKNAME  VARCHAR2(15),   --예금주

    BUYER_ZIP   CHAR(7),    --우편번호  

    BUYER_ADD1  VARCHAR2(100),  --주소1

    BUYER_ADD2  VARCHAR2(70),   --주소2

    BUYER_COMTEL    VARCHAR2(14)    NOT NULL,   --전화번호

    BUYER_FAX   VARCHAR2(20)    NOT NULL    --FAX번호

    ----//키 정의(제약사항) 추가하기 2) CONSTRAINT  PK_BUYER_ID PRIMARY KEY(BUYER_ID)  --식별영역(key) //키 정의 추가하기 + PRIMARY KEY

      -- 하지만 이미 테이블을 create한 이후엔 불가능

      -- 그런데 BUYER_ID가 무엇인지 궁금할 수 있다. EX)반장오세요 VS 홍길동 반장 오세요

        -- 기본키는 테이블마다 다 있으니까 중복될 수 있다. 그래서 별도의 명칭을 정의 

        -- => (개체 이름)PK_BUYER_ID 하나의 계정 안에서 중복되면 안된다.

        

    ----//키 정의(제약사항) 추가하기 3) 

        -- sql dev 툴 내에서 

        -- 테이블 우클릭 > 편집> 제약조건 추가 

    

);

SELECT * FROM LPROD;

    ----//키 정의(제약사항) 추가하기 4)

ALTER TABLE BUYER

ADD(

    CONSTRAINT PK_BUYER PRIMARY KEY(BUYER_ID)

);

/*  ALTER TABLE BUYER

    ADD : 컬럼 및 제약사항 추가 가능

    MODIFY : 컬럼 수정

    DROP : 컬럼 삭제

    RENAME : 컬럼명 변경

*/

DESC BUYER; --BUYER 테이블 구조확인

--6월 5일 

--테이블구조변경(컬럼추가)

ALTER TABLE BUYER

ADD(

    BUYER_MAIL  VARCHAR2(60)    NOT NULL,

    BUYER_CHARGER VARCHAR2(20)          ,

    BUYER_TELEXT VARCHAR2(2)

);

--테이블 구조 변경(컬럼변경)

ALTER TABLE BUYER

MODIFY(

    BUYER_NAME  VARCHAR2(60)   -- 오라클은 NOT NULL 가 기본 디폴트라 별도 선언을 안해준다 

);

ALTER TABLE BUYER

ADD(

CONSTRAINT FR_LPLOG_BUYER FOREIGN KEY(BUYER_LGU) --사용자가 FR_LPLOG_BUYER 테이블에 정의한 이름

                REFERENCES LPROD(LPROD_GU)

);

--외래키 제약사항 확인 1)

    -- 해당 테이블 오른쪽 마우스, 편집, 제약사항

--외래키 제약사항 확인 2)

SELECT * FROM USER_CONSTRAINTS; --시스템에서 제공하는 미리 선언한 테이블 명

-- BUYER_TELEXT 컬럼에 입력할 데이터는 오직 두자리 숫자만 가능하도록 CHECK 제약사항을 추가

ALTER TABLE BUYER

ADD CONSTRAINT CHECK_PHONE

CHECK(REGEXP_LIKE(BUYER_TELEXT, '[0-9][0-9]'));

--SELECT 검색속도를 빠르게 하기 위해 인덱스 추가

CREATE INDEX IDX_BUYER -- 엔티티는 아니지만 개체라고 함, 보통 개체 안에 '객체'로 인식. 

ON BUYER(BUYER_NAME, BUYER_ID);

    -- 활용 예

    -- SELECT *

    -- FROM BUYER

    -- WHERE BUYER_NAME = '농심';

    

    --인덱스 삭제

    -- DROP INDEX IDX_BUYER;

    

--상품정보 테이블 생성

CREATE TABLE PROD

(   

    PROD_ID VARCHAR2(10) NOT NULL,

    PROD_NAME VARCHAR2(40) NOT NULL,

    PROD_LGU CHAR(4) NOT NULL,

    PROD_BUYER CHAR(6) NOT NULL,

    PROD_COST CHAR(10) NOT NULL,

    PROD_PRICE NUMBER(10) NOT NULL,

    PROD_SALE NUMBER(10) NOT NULL,

    PROD_OUTLINE VARCHAR2(100) NOT NULL,

    PROD_DETAIL CLOB,   

    PROD_IMG VARCHAR2(40) NOT NULL,

    PROD_TOTALSTOCK NUMBER(10) NOT NULL,

    PROD_INDATE DATE,

    PROD_PROPERSTOCK NUMBER(10) NOT NULL,

    PROD_SIZE VARCHAR2(10),

    PROD_COLOR VARCHAR2(100),

    PROD_DELIVERY VARCHAR2(255),

    PROD_UNIT VARCHAR2(6),

    PROD_QTYSALE NUMBER(10),

    PROD_MILEAGE NUMBER(10),

    CONSTRAINT PK_PROD PRIMARY KEY(PROD_ID),

    CONSTRAINT FR_PROD_LPROD FOREIGN KEY(PROD_LGU)

                                     REFERENCES LPROD(LPROD_GU),

    CONSTRAINT FR_PROD_BUYER FOREIGN KEY(PROD_BUYER)

                                     REFERENCES BUYER(BUYER_ID)

);

--입고상품정보 테이블 생성

CREATE TABLE BUYPROD

(

    BUY_DATE    DATE NOT NULL,

    BUY_PROD    VARCHAR2(10)    NOT NULL,

    BUY_QTY    NUMBER(10)    NOT NULL,

    BUY_COST    NUMBER(10)    NOT NULL,

    CONSTRAINT PK_BUYPROD PRIMARY KEY(BUY_DATE, BUY_PROD),

    CONSTRAINT FR_BUYPROD_FROID FOREIGN KEY(BUY_PROD) REFERENCES PROD(PROD_ID)

);

--회원 정보테이블 생성

CREATE  TABLE MEMBER

(

    MEM_ID  VARCHAR2(15)    NOT NULL,

    MEM_PASS  VARCHAR2(15)    NOT NULL,

    MEM_NAME  VARCHAR2(20)    NOT NULL,

    MEM_REGNO1  CHAR(6)    NOT NULL,

    MEM_REGNO2  CHAR(7)    NOT NULL,

    MEM_BIR  DATE,

    MEM_ZIP  CHAR(7)    NOT NULL,

    MEM_ADD1  VARCHAR2(100)    NOT NULL,

    MEM_ADD2  VARCHAR2(80)    NOT NULL,

    MEM_HOMETEL  VARCHAR2(14)    NOT NULL,

    MEM_COMTEL  VARCHAR2(14)    NOT NULL,

    MEM_HP  VARCHAR2(15)    NOT NULL,

    MEM_MAIL  VARCHAR2(60)    NOT NULL,

    MEM_JOB  VARCHAR2(40)    NOT NULL,

    MEM_LIKE  VARCHAR2(40)    NOT NULL,

    MEM_MEMORIAL  VARCHAR2(40)    NOT NULL,

    MEM_MEMORIALDAY  DATE,

    MEM_MILEAGE  NUMBER(10)    NOT NULL,

    MEM_DELETE  VARCHAR2(1)    NOT NULL,

    CONSTRAINT PK_MEMBER PRIMARY KEY (MEM_ID)

);

--장바구니 정보테이블 생성

CREATE  TABLE CART

(

    CART_MEMBER VARCHAR2(15)    NOT NULL,

    CART_NO CHAR(13)    NOT NULL,

    CART_PROD VARCHAR2(10)    NOT NULL,

    CART_QTY NUMBER(8)    NOT NULL,

    CONSTRAINT PK_CART  PRIMARY KEY(CART_NO, CART_PROD),

    CONSTRAINT FR_CART_MEMBER  FOREIGN KEY(CART_MEMBER)

                            REFERENCES MEMBER(MEM_ID),

    CONSTRAINT FR_CART_PROD  FOREIGN KEY(CART_PROD)

                            REFERENCES PROD(PROD_ID)

);

--우편번호 정보테이블

CREATE TABLE ZIPTB

(

ZIPCODE     CHAR(7)     NOT NULL,

SIDO    VARCHAR2(2 CHAR)    NOT NULL,

GUGUN   VARCHAR2(10 CHAR)    NOT NULL,

DONG    VARCHAR2(30 CHAR)    NOT NULL,

BUNJI   VARCHAR2(10 CHAR),

SEQ     NUMBER(5)   NOT NULL 

);

CREATE INDEX IDX_ZIPTB_ZIPCODE  ON ZIPTB(ZIPCODE);

-- 여기까지DDL  

-- 여기서부터 -> DML 블록지정 후 ctrl enter

insert into LPROD (LPROD_ID, LPROD_GU, LPROD_NM) values (1, 'P101','컴퓨터 제품');

insert into LPROD (LPROD_ID, LPROD_GU, LPROD_NM) values (2, 'P102','전자제품');

insert into LPROD (LPROD_ID, LPROD_GU, LPROD_NM) values (3, 'P201','여성캐쥬얼');

insert into LPROD (LPROD_ID, LPROD_GU, LPROD_NM) values (4, 'P202','남성캐쥬얼');

insert into LPROD (LPROD_ID, LPROD_GU, LPROD_NM) values (5, 'P301','피혁잡화');

insert into LPROD (LPROD_ID, LPROD_GU, LPROD_NM) values (6, 'P302','화장품');

insert into LPROD (LPROD_ID, LPROD_GU, LPROD_NM) values (7, 'P401','음반/CD');

insert into LPROD (LPROD_ID, LPROD_GU, LPROD_NM) values (8, 'P402','도서');

insert into LPROD (LPROD_ID, LPROD_GU, LPROD_NM) values (9, 'P403','문구류');

rollback;

select * from lprod;  --아직 커믹 전이기 때문에, 하드디스크가 아닌, 메모리에 저장됨

--F11 = 커밋 = 하드디스크에 저장

commit;