3일차

1.개발 진행 및 완료상황

DB - 최종본 생성

피그마 - 이력서, 채용공고 화면 추가

2. 업무, 개발 중 발생한 이슈

수정전 다이어그램

데이터를 테스트해보려고 USER_RESUME테이블의 이력서 1번에서 SKILL_NAME 중 하나인 'KOTLIN'을 삭제했더니 아래와 같은 오류가 발생했다.

ORA-02292: integrity constraint (YOUR_SCHEMA.FK_COMPANY_RECRUIT_SKILL) violated - child record found

 COMPANY_RECRUIT 테이블의 레코드에 의해 참조되고 있다는 것을 의미한다. 따라서 데이터베이스는 데이터 무결성을 보호하기 위해 삭제를 허용하지 않게 된다. 이럴땐 중간 테이블을 생성해서 데이터의 무결성을 보장해야한다. 또한 다대다 관계를 직접 표현 할 수 없는 것은 관계형 데이터베이스의 기본설계원칙이였다.

3. 해결 과정

해결하는데 꽤 시간이 소요되었는데 다대다관계로 직접 연결하지말고 다대일로 연결해야한다는 사실을 깨닫게 되는데 시간이 꽤 걸렸다. SKILL과 각 테이블간에 중간 테이블을 사용함으로써 외래 키 제약 조건을 설정해 일관성과, 무결성을 보장해야한다.

--------------------------------------------------------
--  DDL for Table USER_RESUME
--------------------------------------------------------

  CREATE TABLE "TEAMPROJECT1"."USER_RESUME" 
   (	"USER_RESUME_IDX" NUMBER(4,0), 
	"USER_TITLE" VARCHAR2(100 BYTE), 
	"USER_ID" VARCHAR2(40 BYTE), 
	"USER_NAME" VARCHAR2(40 BYTE), 
	"USER_BIRTH" VARCHAR2(40 BYTE), 
	"USER_PHONE" VARCHAR2(40 BYTE), 
	"USER_EMAIL" VARCHAR2(320 BYTE), 
	"REGION_IDX" NUMBER(4,0), 
	"REGION_ADDRESS" VARCHAR2(320 BYTE), 
	"USER_INTRO" VARCHAR2(2000 BYTE), 
	"USER_RESUME_REGDATE" DATE DEFAULT SYSDATE -- 이력서   등록일

  
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;
--------------------------------------------------------
--  DDL for Table SKILLS
--------------------------------------------------------

  CREATE TABLE "TEAMPROJECT1"."SKILLS" 
   (	"SKILL_STACK" VARCHAR2(40 BYTE), 
	"SKILL_NAME" VARCHAR2(320 BYTE)
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;
--------------------------------------------------------
--  DDL for Table COMMON_USER_RESUME_SKILL
--------------------------------------------------------

  CREATE TABLE "TEAMPROJECT1"."COMMON_USER_RESUME_SKILL" 
   (	"COMMON_USER_IDX" NUMBER(4,0), 
	"SKILL_NAME" VARCHAR2(40 BYTE), 
	"USER_RESUME_IDX" NUMBER(4,0)
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;
REM INSERTING into TEAMPROJECT1.USER_RESUME
SET DEFINE OFF;
Insert into TEAMPROJECT1.USER_RESUME (USER_RESUME_IDX,USER_TITLE,USER_ID,USER_NAME,USER_BIRTH,USER_PHONE,USER_EMAIL,REGION_IDX,REGION_ADDRESS,USER_INTRO,USER_RESUME_REGDATE) values (59,'sef','admin','관리자','2024년 10월 16일','123456','sef@sfe',0,'sef','sefe',to_date('24/10/25','RR/MM/DD'));
Insert into TEAMPROJECT1.USER_RESUME (USER_RESUME_IDX,USER_TITLE,USER_ID,USER_NAME,USER_BIRTH,USER_PHONE,USER_EMAIL,REGION_IDX,REGION_ADDRESS,USER_INTRO,USER_RESUME_REGDATE) values (65,'이력서','admin','관리자','2024년 10월 9일','123456','admin@gmail.com',31,'sef','sef',to_date('24/10/25','RR/MM/DD'));

REM INSERTING into TEAMPROJECT1.SKILLS
SET DEFINE OFF;
Insert into TEAMPROJECT1.SKILLS (SKILL_STACK,SKILL_NAME) values ('백엔드','JAVA');
Insert into TEAMPROJECT1.SKILLS (SKILL_STACK,SKILL_NAME) values ('백엔드','JAVASCRIPT');

--------------------------------------------------------
--  DDL for Index SYS_C008626
--------------------------------------------------------

  CREATE UNIQUE INDEX "TEAMPROJECT1"."SYS_C008626" ON "TEAMPROJECT1"."USER_RESUME" ("USER_RESUME_IDX") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;
--------------------------------------------------------
--  DDL for Index SYS_C008600
--------------------------------------------------------

  CREATE UNIQUE INDEX "TEAMPROJECT1"."SYS_C008600" ON "TEAMPROJECT1"."SKILLS" ("SKILL_NAME") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;
--------------------------------------------------------
--  DDL for Index SYS_C008665
--------------------------------------------------------

  CREATE UNIQUE INDEX "TEAMPROJECT1"."SYS_C008665" ON "TEAMPROJECT1"."COMMON_USER_RESUME_SKILL" ("COMMON_USER_IDX") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;
--------------------------------------------------------
--  Constraints for Table USER_RESUME
--------------------------------------------------------

  ALTER TABLE "TEAMPROJECT1"."USER_RESUME" MODIFY ("USER_TITLE" NOT NULL ENABLE);
  ALTER TABLE "TEAMPROJECT1"."USER_RESUME" MODIFY ("USER_ID" NOT NULL ENABLE);
  ALTER TABLE "TEAMPROJECT1"."USER_RESUME" ADD PRIMARY KEY ("USER_RESUME_IDX")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"  ENABLE;
--------------------------------------------------------
--  Constraints for Table SKILLS
--------------------------------------------------------

  ALTER TABLE "TEAMPROJECT1"."SKILLS" MODIFY ("SKILL_STACK" NOT NULL ENABLE);
  ALTER TABLE "TEAMPROJECT1"."SKILLS" ADD PRIMARY KEY ("SKILL_NAME")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"  ENABLE;
--------------------------------------------------------
--  Constraints for Table COMMON_USER_RESUME_SKILL
--------------------------------------------------------

  ALTER TABLE "TEAMPROJECT1"."COMMON_USER_RESUME_SKILL" MODIFY ("SKILL_NAME" NOT NULL ENABLE);
  ALTER TABLE "TEAMPROJECT1"."COMMON_USER_RESUME_SKILL" MODIFY ("USER_RESUME_IDX" NOT NULL ENABLE);
  ALTER TABLE "TEAMPROJECT1"."COMMON_USER_RESUME_SKILL" ADD PRIMARY KEY ("COMMON_USER_IDX")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"  ENABLE;
--------------------------------------------------------
--  Ref Constraints for Table USER_RESUME
--------------------------------------------------------

  ALTER TABLE "TEAMPROJECT1"."USER_RESUME" ADD CONSTRAINT "FK_USER_RESUME_USERS" FOREIGN KEY ("USER_ID")
	  REFERENCES "TEAMPROJECT1"."USERS" ("USER_ID") ENABLE;
  ALTER TABLE "TEAMPROJECT1"."USER_RESUME" ADD CONSTRAINT "FK_USER_RESUME_REGION" FOREIGN KEY ("REGION_IDX")
	  REFERENCES "TEAMPROJECT1"."REGION" ("REGION_IDX") ENABLE;
--------------------------------------------------------
--  Ref Constraints for Table COMMON_USER_RESUME_SKILL
--------------------------------------------------------

  ALTER TABLE "TEAMPROJECT1"."COMMON_USER_RESUME_SKILL" ADD CONSTRAINT "FK_COMMON_USER_RESUME_SKILL" FOREIGN KEY ("USER_RESUME_IDX")
	  REFERENCES "TEAMPROJECT1"."USER_RESUME" ("USER_RESUME_IDX") ENABLE;
  ALTER TABLE "TEAMPROJECT1"."COMMON_USER_RESUME_SKILL" ADD CONSTRAINT "FK_COMMON_USER_SKILL_NAME" FOREIGN KEY ("SKILL_NAME")
	  REFERENCES "TEAMPROJECT1"."SKILLS" ("SKILL_NAME") ENABLE;

최종 ER DIAGRAM의 일부

COMMON~테이블을 이용해서 다대다 관계를 일대다 관계로 변환하였다. 또한 이과정을 제 3정규화라고 말할 수 있다.

REGION테이블도 얼핏보면 다대다 구조인 것 같지만 한 이력서당 REGION_ID 한 개만 가지고 REGION_ID는 각기 다른 이력서에 두 개 이상 들어갈 수 있기 때문에 일대다 구조가 된다.

 

4. 참고할 만한 레퍼런스들

https://thebook.io

더북(TheBook)

 

5. 회고

기능을 추가 할때마다 해야할 것들도 늘어나고 오류도 늘어나게 되지만 그만큼 새로 배우게 되는 것이 있어서 프로젝트를 진행하면서 점점 실력이 성장하게 되는 거 같다.