Database_79_DATABASE_DB 구축 실습

-- DB 구축 실습
------------------------------------------------------
    -- 1. DB 설계
    -- 2. DBMS 설치_DB 생성
    -- 3. 테이블스페이스 생성
    -- 4. 사용자 계정 생성
    -- 5. 사용자 스키마 생성_테이블, 인덱스, 뷰...
------------------------------------------------------

-- 실습 환경 구축
/*
? TABLESPACE Name : SALESDATA
? DATAFILE : C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SALESDATA01.DBF SIZE 
? 용량 : 100M
*/

-----< 관리자 >-----
-- 테이블스페이스를 생성합니다.
CREATE TABLESPACE salesdata
DATAFILE 'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SALESDATA01.DBF'
SIZE 100m;
    /*
    TABLESPACE SALESDATA이(가) 생성되었습니다.
    */


/*
? ROLE Name : R1
? R1에 포함되는 권한
? CREATE SESSION
? CREATE VIEW
? CREATE SYNONYM
*/

-- 롤을 생성합니다.
CREATE ROLE R1;
    /*
    Role R1이(가) 생성되었습니다.
    */

-- 롤에 권한을 추가합니다.
GRANT create session, create view, create synonym TO r1;
    /*
    Grant을(를) 성공했습니다.
    */

/*
? 사용자 이름 및 암호 : SALES/sales1234
? 사용자 기본 테이블스페이스 : SALESDATA
? 롤 : RESOURCE, R1
*/

-- 새 사용자를 생성합니다.
CREATE USER sales IDENTIFIED BY sales1234
DEFAULT TABLESPACE salesdata;
    /*
    User SALES이(가) 생성되었습니다.
    */

-- 권한을 부여합니다.
GRANT resource, r1 TO sales;
    /*
    Grant을(를) 성공했습니다.
    */

-----< sales >-----
-- 테이블을 생성합니다.
CREATE TABLE member (
    member_id NUMBER(6) PRIMARY KEY,
    member_name VARCHAR2(20) CONSTRAINT member_mname_nn NOT NULL,
    passwd VARCHAR2(10) CONSTRAINT member_pw_nn NOT NULL,
    gender CHAR(3),
    phone_no VARCHAR2(13),
    address VARCHAR2(50),
    join_date DATE DEFAULT SYSDATE,
    interest VARCHAR2(15)
    );
    /*
    Table MEMBER이(가) 생성되었습니다.
    */

-- 테이블을 생성합니다.
CREATE TABLE board (
    no NUMBER(4) PRIMARY KEY,
    subject VARCHAR2(200) CONSTRAINT Board_sub_nn NOT NULL,
    content VARCHAR2(2000),
    cre_date TIMESTAMP(0) DEFAULT SYSDATE,
    member_id NUMBER(6) CONSTRAINT Board_mid_fk REFERENCES member(member_id)
    );
    /*
    Table BOARD이(가) 생성되었습니다.
    */

-----< 인사 관리 >-----
-- 뷰 생성
CREATE OR REPLACE VIEW emp_dept_list_vu
AS
SELECT employee_id AS 사원번호,
        CONCAT (CONCAT (first_name, ' '), last_name) AS 사원이름,
        CONCAT (email, '@test.com') AS 이메일,
        phone_number AS 전화번호,
        job_id AS 직급,
        department_name AS 소속부서명
FROM employees JOIN departments
USING (department_id)
WHERE department_id NOT IN (80, 90);
    /*
    View EMP_DEPT_LIST_VU이(가) 생성되었습니다.
    */

-- 뷰 생성
CREATE OR REPLACE VIEW dept_list_sum_vu
AS 
SELECT department_name AS 부서이름, 
            COUNT(*) AS 인원수,
            SUM(salary) AS 총급여,
            TRUNC(AVG(salary)) AS 평균급여
FROM employees JOIN departments
USING (department_id)
GROUP BY department_name;
    /*
    View DEPT_LIST_SUM_VU이(가) 생성되었습니다.
    */

-- 뷰 생성
CREATE OR REPLACE VIEW dept_addr_list_vu
AS
SELECT d.department_id, d.department_name,
            '(' || postal_code || ')' || l.street_address || ' ' || l.city || ' ' || c.country_name AS address
FROM departments d JOIN locations l
ON (d.location_id=l.location_id)
JOIN countries c
ON (l.country_id=c.country_id);
    /*
    View DEPT_ADDR_LIST_VU이(가) 생성되었습니다.
    */

    -- 객체 권한 부여
-- 뷰에 대한 SELECT 권한 부여
GRANT select on hr.emp_dept_list_vu TO sales;
    /*
    Grant을(를) 성공했습니다.
    */

-- 뷰에 대한 UPDATE 권한 부여
GRANT update on hr.emp_dept_list_vu TO sales;
    /*
    Grant을(를) 성공했습니다.
    */

-- 뷰에 대한 SELECT 권한 롤에 부여
GRANT select on hr.dept_list_sum_vu TO r1;
    /*
    Grant을(를) 성공했습니다.
    */

-- 뷰에 대한 SELECT 권한 PUBLIC에 부여
GRANT select on hr.dept_addr_list_vu TO PUBLIC;
    /*
    Grant을(를) 성공했습니다.
    */

-----< 실습 준비 완 >-----
--------------------------------------------------------------

-- PART 1 관리자로 로그인
    -- SALES 사용자의 생성 정보, 사용자의 DEFAULT TABLESPACE인 SALES 테이블페이스에 대한 정보
-----< 관리자 >-----
SELECT username, account_status, default_tablespace, created
FROM dba_users
WHERE username='SALES';
    /*
    USEENAME    ACCOUNT_STATUS    DEFAULT_TABLESPACE    CREATED
    ------------------------------------------------------------------------------------
    SALES          OPEN                      SALESDATA                 23/07/19
    */

-- DBA_TABLESPACES 및 DBA_DATA_FILES를 조회
    -- 사용자의 기본테이블스페이스인 SALESDATA 테이블스페이스에 대한 정보
SELECT tablespace_name, contents, status
FROM dba_tablespaces;
    /*
    TABLESPACE_NAME       CONTENTS              STATUS        
    -----------------------------------------------------------------
    SYSTEM                     PERMANENT          ONLINE        
    SYSAUX                     PERMANENT          ONLINE        
    UNDOTBS1                 UNDO                    ONLINE        
    TEMP                         TEMPORARY          ONLINE        
    USERS                     PERMANENT           ONLINE        
    SALESDATA                 PERMANENT            ONLINE        
    */


SELECT tablespace_name, file_name, bytes/1024/1024 || 'mb' AS file_size
FROM dba_data_files;
    /*
    TABLESPACE_NAME    FILE_NAME                                                                          FILE_SIZE        
    -------------------------------------------------------------------------------------------------------------------
    USERS                  C:\ORACLEXE\APP\ORACLE\ORADATA\XE\USERS.DBF              440mb        
    SYSAUX                  C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSAUX.DBF          680mb        
    UNDOTBS1              C:\ORACLEXE\APP\ORACLE\ORADATA\XE\UNDOTBS1.DBF        380mb        
    SYSTEM                  C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSTEM.DBF          360mb        
    SALESDATA              C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SALESDATA01.DBF    100mb        
    */


-----< sales >-----
-- 게시판의 NO 열에 사용할 board_no_seq라는 이름의 시퀀스를 생성
    -- 시퀀스의 시작번호와 증분값은 각각 1로 설정하고 NOCACHE, NOCYCLE 속성 부여
CREATE SEQUENCE board_no_seq
                            INCREMENT BY 1
                            START WITH 1
                            NOCACHE
                            NOCYCLE;
    /*
    Sequence BOARD_NO_SEQ이(가) 생성되었습니다.
    */
    
-- MEMBER테이블의 ADDRESS 열의 크기를 VARCHAR2(100)으로 수정
ALTER TABLE member MODIFY address VARCHAR2(100);
    /*
    Table MEMBER이(가) 변경되었습니다.
    */

-- BOARD 테이블의 CRE_DATE열에 DEFAULT 값을 SYSDATE로 정의
ALTER TABLE board MODIFY cre_date DEFAULT SYSDATE;
    /*
    Table BOARD이(가) 변경되었습니다.
    */

-- 회원 테이블과 게시판 테이블을 참조하여 무결성 제약조건을 위반하지 않는 데이터를 3건씩 입력하시오. 
    /*
    ? 회원 주소는 시 구 동까지만 입력하시오.
    ? 게시판의 번호는 board_no_seq 시퀀스를 사용하여 입력하시오
    */
INSERT INTO member
VALUES ( '111111', 'NAME01', 'USER1111', '여', '010-1111-1111', '1시 1구 1동', TO_DATE('2020/11/11', 'YYYY/MM/DD'), 'DB');
    /*
    1 행 이(가) 삽입되었습니다.
    */
    
INSERT INTO member
VALUES ( '222222', 'NAME02', 'USER2222', '남', '010-2222-2222', '2시 2구 2동', TO_DATE('2020/11/22', 'YYYY/MM/DD'), 'DB');
    /*
    1 행 이(가) 삽입되었습니다.
    */
    
INSERT INTO member
VALUES ( '333333', 'NAME03', 'USER3333', '남', '010-3333-3333', '3시 3구 3동', TO_DATE('2020/11/03', 'YYYY/MM/DD'), 'INTERNET');
    /*
    1 행 이(가) 삽입되었습니다.
    */
    
INSERT INTO board
VALUES ( board_no_seq.nextval, '배송 문의', '배송 언제?', TO_DATE('2023/05/06', 'YYYY/MM/DD'), '111111');
    /*
    1 행 이(가) 삽입되었습니다.
    */


INSERT INTO board
VALUES ( board_no_seq.nextval, '재입고 문의', '재입고 언제?', TO_DATE('2023/05/27', 'YYYY/MM/DD'), '222222');
    /*
    1 행 이(가) 삽입되었습니다.
    */


INSERT INTO board
VALUES ( board_no_seq.nextval, '교환 문의', '교환 가능?', TO_DATE('2023/06/04', 'YYYY/MM/DD'), '333333');
    /*
    1 행 이(가) 삽입되었습니다.
    */

--  회원 테이블에 email 열을 추가하시오. 
    -- 단, email 열의 데이터 타입은 VARCHAR2(100)이고 UNQUE 제약조건을 함께 정의
ALTER TABLE member ADD email VARCHAR2(100) UNIQUE;
    /*
    Table MEMBER이(가) 변경되었습니다.
    */

-- 회원 테이블에 국적을 나타내는 country 열을 추가
    -- 데이터타입은 VARCHAR2(30), 기본값은 'Korea'로 지정
    -- 열 추가 후 MEMBER 테이블을 조회해서 COUNTRY 열의 상태를 확인
ALTER TABLE member ADD country VARCHAR2(30) DEFAULT 'Korea';

SELECT member_name, country FROM member;
    /*
    MEMBER_NAME    COUNTRY
    -----------------------------------
    NAME01            Korea
    NAME02            Korea
    NAME03            Korea
    */

-- 회원 테이블에서 INTEREST 열을 삭제
ALTER TABLE member DROP COLUMN interest;
    /*
    Table MEMBER이(가) 변경되었습니다.
    */

-- 게시판 테이블의 member_id 열에 대해 board_mid_ix 라는 이름의 인덱스를 생성
CREATE INDEX board_mid_ix ON board(member_id); 
    /*
    Index BOARD_MID_IX이(가) 생성되었습니다.
    */
    
-- 회원테이블에서 회원아이디, 이름, 전화번호, 주소를 볼 수 있는 member_addr_phone_list_vu라는 이름의 뷰를 생성
CREATE OR REPLACE VIEW member_addr_phone_list_vu
AS
SELECT member_id, member_name, phone_no, address
FROM member;
    /*
    View MEMBER_ADDR_PHONE_LIST_VU이(가) 생성되었습니다.
    */

-- MEMBER_ADDR_PHONE_LIST_VU를 통해 222222 회원의 전화번호와 주소를 수정
UPDATE member_addr_phone_list_vu
SET phone_no='010-0002-0002', address='22시 22구 22동'
WHERE member_id='222222';
    /*
    1 행 이(가) 업데이트되었습니다.
    */

-- 정보 조회
SELECT member_id, phone_no, address FROM member;
    /*
    MEMBER_ID    PHONE_NO         ADDRESS
    -------------------------------------------------
    111111         010-1111-1111     1시 1구 1동
    222222         010-0002-0002     22시 22구 22동
    333333         010-3333-3333     3시 3구 3동
    */
    
-- 게시글을 작성한 회원정보를 볼 수 있독록 회원이름, 게시판글번호, 게시글제목을 볼 수 있는 board_member_list_vu라는 이름의 뷰를 생성
CREATE OR REPLACE VIEW board_member_list_vu
AS
SELECT m.member_name, b.no, b.subject
FROM member m JOIN board b
ON (m.member_id=b.member_id);
    /*
    View BOARD_MEMBER_LIST_VU이(가) 생성되었습니다.
    */
    
-- member_addr_phone_list_vu와 board_member_list_vu라는 이름의 뷰에 대하여 각각 m과 b라는 이름의 동의어를 생성
CREATE SYNONYM m FOR member_addr_phone_list_vu;
    /*
    Synonym M이(가) 생성되었습니다.
    */

CREATE SYNONYM b FOR board_member_list_vu;
    /*
    Synonym B이(가) 생성되었습니다.
    */
    
-- board_member_list_vu 라는 뷰에서 작성일(CRE_DATE)가 보이도록 뷰를 수정
CREATE OR REPLACE VIEW board_member_list_vu
AS
SELECT m.member_name, b.no, b.subject, b.cre_date
FROM member m JOIN board b
ON (m.member_id=b.member_id);
        /*
        View BOARD_MEMBER_LIST_VU이(가) 생성되었습니다.
        */

--  USER_TABLES, USER_CONSTRAINTS, USER_INDEXES 및 USER_OBJECTS를 조회
    -- MEMBER와 BOARD 테이블을 비롯한 객체들에 관련된 정보를 파악
SELECT table_name, tablespace_name
FROM user_tables;
    /*
    TABLE_NAME    TABLESPACE_NAME
    ---------------------------------------
    MEMBER         SALESDATA
    BOARD           SALESDATA
    */
    
SELECT table_name, constraint_name, constraint_type, search_condition, status
FROM user_constraints
WHERE table_name IN ('MEMBER', 'BOARD')
ORDER BY 1;
    /*
    TABLE_NAME   CONSTRAINT_NAME       CONSTRAINT_TYPE   SEARCH_CONDITION               STATUS
    -----------------------------------------------------------------------------------------------------------------
    BOARD          BOARD_SUB_NN                C                    "SUBJECT" IS NOT NULL            ENABLED
    BOARD          SYS_C007175                    P                     ( null )                                   ENABLED
    BOARD          BOARD_MID_FK                 R                      ( null )                                  ENABLED
    MEMBER        MEMBER_MNAME_NN        C                    "MEMBER_NAME" IS NOT NULL   ENABLED
    MEMBER        MEMBER_PW_NN              C                    "PASSWD" IS NOT NULL             ENABLED
    MEMBER        SYS_C007173                    P                     ( null )                                   ENABLED
    MEMBER        SYS_C007177                    U                     ( null )                                   ENABLED
    */
    
SELECT table_name, index_name
FROM user_indexes
WHERE table_name IN ('MEMBER', 'BOARD')
ORDER BY 1;
    /*
    TABLE_NAME    INDEX_NAME
    -------------------------------------------
    BOARD            BOARD_MID_IX
    BOARD            SYS_C007175
    MEMBER          SYS_C007177
    MEMBER          SYS_C007173
    */
    
SELECT object_name, object_type, status
FROM user_objects;
    /*
    OBJECT_NAME                            OBJECT_TYPE    STATUS
    -----------------------------------------------------------------------------------
    MEMBER                                  TABLE               VALID
    SYS_C007173                            INDEX               VALID
    BOARD                                    TABLE               VALID
    SYS_C007175                            INDEX               VALID
    SYS_C007177                            INDEX               VALID
    BOARD_NO_SEQ                        SEQUENCE         VALID
    BOARD_MID_IX                          INDEX               VALID
    MEMBER_ADDR_PHONE_LIST_VU   VIEW                 VALID
    BOARD_MEMBER_LIST_VU            VIEW                 VALID
    M                                           SYNONYM         VALID
    B                                            SYNONYM         VALID
    */
    
-- member테이블을 삭제
DROP TABLE member CASCADE CONSTRAINT;  
    /*
    Table MEMBER이(가) 삭제되었습니다.
    */

-- USER_OBJECTS를 조회하여 사용자 소유의 객체의 이름, 타입, 상태 등을 조회하면서 테이블 삭제의 결과 연관된 객체의 상태를 조회
SELECT object_name, object_type, status
FROM user_objects;
    /*
    OBJECT_NAME                            OBJECT_TYPE    STATUS
    -----------------------------------------------------------------------------------
    BOARD                                    TABLE               VALID
    SYS_C007177                            INDEX               VALID
    BOARD_NO_SEQ                        SEQUENCE         VALID
    BOARD_MID_IX                          INDEX               VALID
    MEMBER_ADDR_PHONE_LIST_VU   VIEW                 INVALID
    BOARD_MEMBER_LIST_VU            VIEW                 INVALID
    M                                           SYNONYM         INVALID
    B                                            SYNONYM         INVALID
    */

-- 커밋합니다.   
  COMMIT;
    /*
    커밋 완료.
    */