오라클 연습 - 24.09.13

오늘은 강사님이 오라클 문제를 내주신날이다. 조원은 나 포함 5명에서 진행하였다.
문제는 다음과 같다.

데이터 딕셔너리와 제약조건
ο 제약 조건 - 종합 문제
1) hr 사용자(스키마)의 다음 7개의 테이블의 구조를 분석한다.
- 테이블명, 컬럼명, 자료형, 기본키, 참조키등의 제약조건 등
- 7개의 테이블
COUNTRIES
DEPARTMENTS
EMPLOYEES
JOBS
JOB_HISTORY
LOCATIONS
REGIONS
2) hrEx 라는 이름으로 사용자를 추가하고 hrEx 사용자에 hr 사용자의 테이블과 동일한 구조(자료형, 제약조건 등)로 7개의 테이블을 작성한다.
- 테이블 하나당 최소 3개 이상의 데이터(레코드)를 추가한다.
3) 작업순서
(1) hr 계정의 테이블 및 제약조건을 확인 한다.
-- 엑셀등을 이용하여 테이블 명세서를 만든다.
컬럼명 컬럼성명 타입 NULL 제약조건 기본값 참조테이블 참조컬럼 비고
(2) SYS 또는 SYSTEM 계정에서 hrEx라는 이름으로 계정을 추가한다.
-- 12C/18C 이상의 버전에서 11g와 같은 방식으로 사용자 추가를 위한 설정 변경
-- hrEx 사용자 추가
-- 패스워드는 "java1234"로 설정
-- CONNECT 및 RESOURCE 권한 설정
-- USERS 테이블스페이스의 용량을 UNLIMITED로 설정
-- hrEx 사용자의 DEFAULT 테이블스페이스를 USERS로 변경
-- hrEx 사용자의 TEMPORARY 테이블스페이스를 TEMP로 변경
(3) hrEx 계정으로 CONNECT 후 테이블을 작성한다.
-- SQL Developer 에 hrEx 계정 추가 후 테이블 작성

• 엔티티를 먼저 작성해보라고 주신 예시
  

실제 내가 작성한 엔티티이다.

조원들과 이 문제를 품으로서
각각의 테이블의 관계를 확인하고, 아버지가 누구인지, 자식인지, 어떤 컬럼의 제약조건이 있느닞 확인할 수 있었다.

CREATE TABLE COUNTRIES (
	COUNTRY_ID CHAR(2) PRIMARY KEY, 
	COUNTRY_NAME VARCHAR2(40), 
	REGION_ID NUMBER -- R
);

ALTER TABLE COUNTRIES ADD CONSTRAINT COUNTR_REG_FK FOREIGN KEY( REGION_ID ) REFERENCES REGIONS( REGION_ID ); 
-------------------------------------------------------------------------------------------------------------------------
create table DEPARTMENTS (
    DEPARTMENT_ID NUMBER(4) PRIMARY KEY, -- 부서 ID
    DEPARTMENT_NAME VARCHAR2(30) NOT NULL, -- 부서명
    MANAGER_ID NUMBER(6), -- 부서 관리자
    LOCATION_ID NUMBER(4) -- 부서 위치 장소? 지역?
);

ALTER TABLE DEPARTMENTS ADD CONSTRAINT DEPT_MGR_FK FOREIGN KEY( MANAGER_ID ) REFERENCES  EMPLOYEES( EMPLOYEE_ID ); 
-- 아버지 테이블 EMPLOYEES , 참조 컬럼 EMPLOYEE_ID

ALTER TABLE DEPARTMENTS ADD CONSTRAINT DEPT_LOC_FK FOREIGN KEY( LOCATION_ID ) REFERENCES  LOCATIONS( LOCATION_ID ); 
-- 아버지 테이블 LOCATIONS , 참조 컬럼 LOCATION_ID
-------------------------------------------------------------------------------------------------------------------------
CREATE TABLE EMPLOYEES(
	EMPLOYEE_ID NUMBER(6) PRIMARY KEY,
	FIRST_NAME VARCHAR2(20),
	LAST_NAME VARCHAR2(25) NOT NULL,
	EMAIL VARCHAR2(25) NOT NULL,
	PHONE_NUMBER VARCHAR2(20),
	HIRE_DATE DATE NOT NULL,
	JOB_ID VARCHAR2(10) NOT NULL,
	SALARY NUMBER(8, 2),
	COMMISSION_PCT NUMBER(2,2),
	MANAGER_ID NUMBER(6),
	DEPARTMENT_ID NUMBER(4)
);

ALTER TABLE EMPLOYEES ADD CONSTRAINT EMP_DEPT_FK FOREIGN KEY(DEPARTMENT_ID) REFERENCES DEPARTMENTS( DEPARTMENT_ID );

ALTER TABLE EMPLOYEES ADD CONSTRAINT EMP_JOB_FK FOREIGN KEY( JOB_ID ) REFERENCES JOBS( JOB_ID );

ALTER TABLE EMPLOYEES ADD CONSTRAINT EMP_MANAGER_FK FOREIGN KEY( MANAGER_ID )REFERENCES EMPLOYEES (EMPLOYEE_ID);
-------------------------------------------------------------------------------------------------------------------------
CREATE TABLE JOBS (
    JOB_ID VARCHAR2(10) PRIMARY KEY,
    JOB_TITLE VARCHAR2(35) NOT NULL,
    MIN_SALARY NUMBER(6),
    MAX_SALARY NUMBER(6)
);
-------------------------------------------------------------------------------------------------------------------------
CREATE TABLE JOB_HISTORY (
   EMPLOYEE_ID NUMBER(6),
   START_DATE DATE NOT NULL, 
   END_DATE DATE NOT NULL,
   JOB_ID VARCHAR2(10),
   DEPARTMENT_ID NUMBER(4),
	PRIMARY KEY(EMPLOYEE_ID, START_DATE)
);

ALTER TABLE JOB_HISTORY ADD CONSTRAINT JHIST_JOB_FK FOREIGN KEY (JOB_ID) REFERENCES JOBS(JOB_ID);

ALTER TABLE JOB_HISTORY ADD CONSTRAINT JHIST_EMP_FK FOREIGN KEY (EMPLOYEE_ID) REFERENCES EMPLOYEES(EMPLOYEE_ID);

ALTER TABLE JOB_HISTORY ADD CONSTRAINT JHIST_DEPT_FK FOREIGN KEY (DEPARTMENT_ID) REFERENCES DEPARTMENTS(DEPARTMENT_ID);
-------------------------------------------------------------------------------------------------------------------------   
CREATE TABLE LOCATIONS (
    LOCATION_ID NUMBER(4) PRIMARY KEY,
    STREET_ADDRESS VARCHAR2(40),
    POSTAL_CODE VARCHAR2(12),
    CITY VARCHAR2(30) NOT NULL,
    STATE_PROVINCE VARCHAR2(25),
    COUNTRY_ID CHAR(2)
);

ALTER TABLE LOCATIONS ADD CONSTRAINT LOC_C_ID_FK FOREIGN KEY(COUNTRY_ID) REFERENCES COUNTRIES (COUNTRY_ID);
-------------------------------------------------------------------------------------------------------------------------
CREATE TABLE REGIONS (
	region_id NUMBER PRIMARY KEY,
	region_name VARCHAR2(25)
);

SELECT * FROM LOCATIONS;

INSERT INTO REGIONS(region_id, region_name) VALUES (1,'홍길동');

INSERT INTO COUNTRIES(COUNTRY_ID, COUNTRY_NAME, REGION_ID) VALUES ('A','나라이름1',1);

INSERT INTO LOCATIONS(LOCATION_ID, STREET_ADDRESS, POSTAL_CODE, CITY, STATE_PROVINCE, COUNTRY_ID)
                VALUES ('1001','다산중앙로','146','남양주시','경기도', 'A');

-- 제약 조건 이름 확인
SELECT * FROM user_constraints WHERE TABLE_NAME = 'EMPLOYEES';


-- EMPLOYEES가 자기 참조함.
SELECT * FROM user_cons_columns WHERE TABLE_NAME = 'DEPARTMENTS';

-- DEPARTMENTS(자식)와 EMPLOYEES(부모) 참조관계 비활성화
ALTER TABLE DEPARTMENTS DISABLE CONSTRAINT DEPT_MGR_FK;

-- DEPARTMENTS 테이블의 LOCATION_ID가 LOCATION 테이블을 참조하고 있으므로, 부모 테이블의 LOCATION_ID 자료값인 1001을 넣어줘야 한다.
-- MANAGER_ID는 현재 EMPLOYESS 테이블과 참조관계 비활성화한 상태!!
INSERT INTO DEPARTMENTS(DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)
                VALUES ('2001','총무부','10','1001');
-- STATUS 칼럼의 ENABLED과 DISABLED로 참조관계가 비활성화 되었는지 확인 가능
SELECT * FROM user_constraints WHERE table_name = 'DEPARTMENTS';

-- EMPLOYEES 테이블의 참조관계
-- 자기참조 : MANAGER_ID(자식) 칼럼이 EMPLOYEE_ID(부모) 칼럼 참조 - EMP_MANAGER_FK
-- JOB_ID가 JOBS(부모 테이블)의 JOB_ID 칼럼 참조   - EMP_JOB_FK
-- DEPARTMENT_ID가 DEPARTMENTS(부모 테이블)의 DEPARTMENT_ID 칼럼 참조  - EMP_DEPT_FK

ALTER TABLE EMPLOYEES DISABLE CONSTRAINT EMP_MANAGER_FK;
	ALTER TABLE EMPLOYEES DISABLE CONSTRAINT EMP_JOB_FK;

SELECT * FROM cols where table_name = 'EMPLOYEES';
INSERT INTO EMPLOYEES(EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
                VALUES (1,'자바','쪼아','abc','1111', TO_DATE('20240701', 'YYYYMMDD'),'abc', 9999.0, 0.4, 4, 2001);
-- 2001 Department id
-- 4 employee id이자 manager id

INSERT INTO EMPLOYEES(EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
                VALUES (4,'오라클','짱짱','def','1111', TO_DATE('20240901', 'YYYYMMDD'),'abc', 9999.0, 0.1, null, 2001);

INSERT INTO EMPLOYEES(EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
                VALUES (10,'고기','조아','def','1111', TO_DATE('20240901', 'YYYYMMDD'),'abc', 9999.0, 0.1, null, 2001);
         
-- JOBS에 자료 추가   
INSERT INTO JOBS(JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY) VALUES ('abc', '쿨뷰티박영진', 999,9999);

SELECT * FROM user_constraints WHERE table_name = 'JOB_HISTORY';
-- JOB_ID가 JOBS의 JOB_ID를 참조 중 JHIST_JOB_FK
-- EMPLOYEE_ID가 EMPLOYEES의 EMPLOYEE_ID 참조 중 JHIST_EMP_FK
-- DEPARTMENT_ID가 DEPARTMENTS의 DEPARTMENT_ID 참조 중 JHIST_DEPT_FK
INSERT INTO JOB_HISTORY (EMPLOYEE_ID, START_DATE, END_DATE, JOB_ID, DEPARTMENT_ID)
    VALUES (4, TO_DATE('20240901', 'YYYYMMDD'), TO_DATE('20250101', 'YYYYMMDD'), 'abc', 2001);
    
ALTER TABLE DEPARTMENTS ENABLE CONSTRAINT DEPT_MGR_FK;
ALTER TABLE EMPLOYEES ENABLE CONSTRAINT EMP_MANAGER_FK;
ALTER TABLE EMPLOYEES ENABLE CONSTRAINT EMP_JOB_FK;