-- ## iitp DB Schemas - Initial setup - Creation and Delete if tables exists -- ## ver 0.0.5 last updated data : 2025.07.24 -- ## Only for PostgreSQL -- ## 고용관련 정적 데이터용 테이블 생성 스크립트 -- ## 나라 HR 데이터 제공 데이터 -- ################################################ -- ## 장애인 고용 관련 테이블 생성 -- ################################################ -- #### 01.장애인 구직자 현황 테이블 #### -- # public.emp_dis_jobseeker_status definition -- Drop table if table exists DROP TABLE IF EXISTS public.emp_dis_jobseeker_status; -- Create table CREATE TABLE public.emp_dis_jobseeker_status ( id int4 GENERATED BY DEFAULT AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START 1 CACHE 1 NO CYCLE) NOT NULL, -- 시스템 ID, 고유 식별자 (자동 증가) seq_no int4 NOT NULL, -- 연번 (원본 데이터 순번) reg_date date NOT NULL, -- 구직등록일 (YYYY-MM-DD 형식) age int2 NOT NULL, -- 연령 region varchar(100) NOT NULL, -- 희망지역 (예: 서울 영등포구, 강원 원주시) job_type varchar(100) NOT NULL, -- 희망직종 (예: 안마사, 아파트·빌라 경비원) salary_type varchar(10) NOT NULL, -- 급여타입 (월급/시급) salary_amount int4 NOT NULL, -- 급여금액 (원 단위) disability_type varchar(50) NOT NULL, -- 장애유형 (예: 시각장애, 지체장애) severity varchar(10) NOT NULL, -- 중증여부 (중증/경증) org_type varchar(50) NOT NULL, -- 기관분류 (예: 공단) created_at timestamptz DEFAULT CURRENT_TIMESTAMP NOT NULL, -- 레코드 생성 시각 updated_at timestamptz DEFAULT CURRENT_TIMESTAMP, -- 레코드 수정 시각 created_by varchar(40) NULL, -- 데이터 생성자 (SYS-BACH, SYS-MANUAL, BY-USER, admin name), "sys_work_type" comm code 참조 updated_by varchar(40) NULL, -- 데이터 수정자 CONSTRAINT pkey_emp_dis_jobseeker_status PRIMARY KEY (id) ); -- Create indexes CREATE INDEX idx_emp_dis_jobseeker_region_job ON public.emp_dis_jobseeker_status USING btree (region, job_type); CREATE INDEX idx_emp_dis_jobseeker_disability_age ON public.emp_dis_jobseeker_status USING btree (disability_type, severity, age); -- Table comment COMMENT ON TABLE public.emp_dis_jobseeker_status IS '01. 장애인 구직자 현황 테이블'; -- Column comments COMMENT ON COLUMN public.emp_dis_jobseeker_status.id IS '시스템 ID, 고유 식별자 (자동 증가)'; COMMENT ON COLUMN public.emp_dis_jobseeker_status.seq_no IS '연번 (원본 데이터 순번)'; COMMENT ON COLUMN public.emp_dis_jobseeker_status.reg_date IS '구직등록일 (YYYY-MM-DD 형식)'; COMMENT ON COLUMN public.emp_dis_jobseeker_status.age IS '연령'; COMMENT ON COLUMN public.emp_dis_jobseeker_status.region IS '희망지역 (예: 서울 영등포구, 강원 원주시)'; COMMENT ON COLUMN public.emp_dis_jobseeker_status.job_type IS '희망직종 (예: 안마사, 아파트·빌라 경비원)'; COMMENT ON COLUMN public.emp_dis_jobseeker_status.salary_type IS '급여타입 (월급/시급)'; COMMENT ON COLUMN public.emp_dis_jobseeker_status.salary_amount IS '급여금액 (원 단위)'; COMMENT ON COLUMN public.emp_dis_jobseeker_status.disability_type IS '장애유형 (예: 시각장애, 지체장애)'; COMMENT ON COLUMN public.emp_dis_jobseeker_status.severity IS '중증여부 (중증/경증)'; COMMENT ON COLUMN public.emp_dis_jobseeker_status.org_type IS '기관분류 (예: 공단)'; COMMENT ON COLUMN public.emp_dis_jobseeker_status.created_at IS '레코드 생성 시각'; COMMENT ON COLUMN public.emp_dis_jobseeker_status.updated_at IS '레코드 수정 시각'; COMMENT ON COLUMN public.emp_dis_jobseeker_status.created_by IS '데이터 생성자 (SYS-BACH, SYS-MANUAL, BY-USER, admin name), "sys_work_type" comm code 참조'; COMMENT ON COLUMN public.emp_dis_jobseeker_status.updated_by IS '데이터 수정자'; -- #### 02.발달장애인훈련센터 이용자현황 테이블 #### -- # public.emp_dis_center_usage definition DROP TABLE IF EXISTS public.emp_dis_center_usage; CREATE TABLE public.emp_dis_center_usage ( id int4 GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, -- 시스템 ID, 고유 식별자 (자동 증가) train_org varchar(100) NOT NULL, -- 훈련기관구분 user_count int4 NOT NULL, -- 양성과정 이용자수 employed_count int4 NOT NULL, -- 양성과정 취업자수 created_at timestamptz DEFAULT CURRENT_TIMESTAMP NOT NULL, -- 레코드 생성 시각 updated_at timestamptz DEFAULT CURRENT_TIMESTAMP, -- 레코드 수정 시각 created_by varchar(40) NULL, -- 데이터 생성자 (SYS-BACH, SYS-MANUAL, BY-USER, admin name), "sys_work_type" comm code 참조 updated_by varchar(40) NULL -- 데이터 수정자 ); CREATE INDEX idx_emp_dis_center_usage_train_org ON public.emp_dis_center_usage (train_org); COMMENT ON TABLE public.emp_dis_center_usage IS '02. 발달장애인훈련센터 이용자현황 테이블'; COMMENT ON COLUMN public.emp_dis_center_usage.id IS '시스템 ID, 고유 식별자 (자동 증가)'; COMMENT ON COLUMN public.emp_dis_center_usage.train_org IS '훈련기관구분'; COMMENT ON COLUMN public.emp_dis_center_usage.user_count IS '양성과정 이용자수'; COMMENT ON COLUMN public.emp_dis_center_usage.employed_count IS '양성과정 취업자수'; COMMENT ON COLUMN public.emp_dis_center_usage.created_at IS '레코드 생성 시각'; COMMENT ON COLUMN public.emp_dis_center_usage.updated_at IS '레코드 수정 시각'; COMMENT ON COLUMN public.emp_dis_center_usage.created_by IS '데이터 생성자 (SYS-BACH, SYS-MANUAL, BY-USER, admin name), "sys_work_type" comm code 참조'; COMMENT ON COLUMN public.emp_dis_center_usage.updated_by IS '데이터 수정자'; -- #### 15.장애인 의무고용 - 사업체 현황 테이블 #### -- # public.emp_dis_obligation_fulfillment definition DROP TABLE IF EXISTS public.emp_dis_obligation_fulfillment; CREATE TABLE public.emp_dis_obligation_fulfillment ( id int4 GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, -- 시스템 ID year int NOT NULL, -- 연도 company_count int NOT NULL, -- 사업체수 fulfilled_count int NOT NULL, -- 이행사업체수 created_at timestamptz DEFAULT CURRENT_TIMESTAMP NOT NULL, -- 생성시각 updated_at timestamptz DEFAULT CURRENT_TIMESTAMP, -- 수정시각 created_by varchar(40), -- 데이터 생성자 (SYS-BACH, SYS-MANUAL, BY-USER, admin name), "sys_work_type" comm code 참조 updated_by varchar(40) -- 데이터 수정자 ); CREATE INDEX idx_emp_dis_obligation_fulfillment_year ON public.emp_dis_obligation_fulfillment (year); COMMENT ON TABLE public.emp_dis_obligation_fulfillment IS '15. 장애인 의무고용 - 사업체 현황 테이블'; COMMENT ON COLUMN public.emp_dis_obligation_fulfillment.id IS '시스템 ID'; COMMENT ON COLUMN public.emp_dis_obligation_fulfillment.year IS '연도'; COMMENT ON COLUMN public.emp_dis_obligation_fulfillment.company_count IS '사업체수'; COMMENT ON COLUMN public.emp_dis_obligation_fulfillment.fulfilled_count IS '이행사업체수'; COMMENT ON COLUMN public.emp_dis_obligation_fulfillment.created_at IS '생성시각'; COMMENT ON COLUMN public.emp_dis_obligation_fulfillment.updated_at IS '수정시각'; COMMENT ON COLUMN public.emp_dis_obligation_fulfillment.created_by IS '데이터 생성자 (SYS-BACH, SYS-MANUAL, BY-USER, admin name), "sys_work_type" comm code 참조'; COMMENT ON COLUMN public.emp_dis_obligation_fulfillment.updated_by IS '데이터 수정자'; -- #### 04.장애인 고용컨설팅 테이블 #### -- # public.emp_dis_consulting_his definition -- Drop table if table exists DROP TABLE IF EXISTS public.emp_dis_consulting_his; -- Create table CREATE TABLE public.emp_dis_consulting_his ( id int4 GENERATED BY DEFAULT AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START 1 CACHE 1 NO CYCLE) NOT NULL, -- 시스템 ID, 고유 식별자 (자동 증가) seq_no int4 NOT NULL, -- 순번 (원본 데이터 순번) diagnosis_no varchar(20) NOT NULL, -- 진단번호 (예: E202411190003) receive_date date NOT NULL, -- 접수일자 (YYYY-MM-DD 형식) business_no varchar(20) NOT NULL, -- 사업자등록번호 (예: 647-87-00659) company_name varchar(200) NOT NULL, -- 사업체명 address varchar(500) NOT NULL, -- 소재지주소 business_type varchar(100) NOT NULL, -- 사업체유형 (예: 민간기업, 공공기관, 국가 및 지방자치단체) regional_office varchar(100) NOT NULL, -- 관할 지역본부 및 지사 (예: 강원지사) office_tel varchar(20) NOT NULL, -- 관할 지역본부 및 지사 대표번호 (예: 033-737-6612) created_at timestamptz DEFAULT CURRENT_TIMESTAMP NOT NULL, -- 레코드 생성 시각 updated_at timestamptz DEFAULT CURRENT_TIMESTAMP, -- 레코드 수정 시각 created_by varchar(40) NULL, -- 데이터 생성자 (SYS-BACH, SYS-MANUAL, BY-USER, admin name), "sys_work_type" comm code 참조 updated_by varchar(40) NULL, -- 데이터 수정자 CONSTRAINT pkey_emp_dis_consulting_his PRIMARY KEY (id) ); -- Create indexes CREATE INDEX idx_emp_dis_consulting_his_business_type ON public.emp_dis_consulting_his USING btree (business_type); -- Table comment COMMENT ON TABLE public.emp_dis_consulting_his IS '04. 장애인 고용컨설팅 - 이력 테이블'; -- Column comments COMMENT ON COLUMN public.emp_dis_consulting_his.id IS '시스템 ID, 고유 식별자 (자동 증가)'; COMMENT ON COLUMN public.emp_dis_consulting_his.seq_no IS '순번 (원본 데이터 순번)'; COMMENT ON COLUMN public.emp_dis_consulting_his.diagnosis_no IS '진단번호 (예: E202411190003)'; COMMENT ON COLUMN public.emp_dis_consulting_his.receive_date IS '접수일자 (YYYY-MM-DD 형식)'; COMMENT ON COLUMN public.emp_dis_consulting_his.business_no IS '사업자등록번호 (예: 647-87-00659)'; COMMENT ON COLUMN public.emp_dis_consulting_his.company_name IS '사업체명'; COMMENT ON COLUMN public.emp_dis_consulting_his.address IS '소재지주소'; COMMENT ON COLUMN public.emp_dis_consulting_his.business_type IS '사업체유형 (예: 민간기업, 공공기관, 국가 및 지방자치단체)'; COMMENT ON COLUMN public.emp_dis_consulting_his.regional_office IS '관할 지역본부 및 지사 (예: 강원지사)'; COMMENT ON COLUMN public.emp_dis_consulting_his.office_tel IS '관할 지역본부 및 지사 대표번호 (예: 033-737-6612)'; COMMENT ON COLUMN public.emp_dis_consulting_his.created_at IS '레코드 생성 시각'; COMMENT ON COLUMN public.emp_dis_consulting_his.updated_at IS '레코드 수정 시각'; COMMENT ON COLUMN public.emp_dis_consulting_his.created_by IS '데이터 생성자 (SYS-BACH, SYS-MANUAL, BY-USER, admin name), "sys_work_type" comm code 참조'; COMMENT ON COLUMN public.emp_dis_consulting_his.updated_by IS '데이터 수정자'; -- #### 05.장애인 구인 정보 테이블 #### -- # public.emp_dis_job_posting definition -- Drop table if table exists DROP TABLE IF EXISTS public.emp_dis_job_posting; -- Create table CREATE TABLE public.emp_dis_job_posting ( id int4 GENERATED BY DEFAULT AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START 1 CACHE 1 NO CYCLE) NOT NULL, -- 시스템 ID, 고유 식별자 (자동 증가) seq_no int4 NOT NULL, -- 연번 (원본 데이터 순번) apply_date date NOT NULL, -- 구인신청일자 (YYYY-MM-DD 형식) recruit_period varchar(50) NOT NULL, -- 모집기간 (예: 2024-12-31~2025-01-15) company_name varchar(200) NOT NULL, -- 사업장명 job_type varchar(100) NOT NULL, -- 모집직종 emp_type varchar(20) NOT NULL, -- 고용형태 (상용직/계약직) salary_type varchar(10) NOT NULL, -- 임금형태 (월급/시급) salary_amount int4, -- 임금 (원 단위) hire_type varchar(20) NOT NULL, -- 입사형태 (무관 등) experience varchar(50) NOT NULL, -- 요구경력 (0년개월, 무관 등) education varchar(50) NOT NULL, -- 요구학력 (무관 등) major varchar(100), -- 전공계열 license varchar(200), -- 요구자격증 address varchar(500) NOT NULL, -- 사업장 주소 company_type varchar(50) NOT NULL, -- 기업형태 (중소, 대기업, 공사공공, 개인) office varchar(100) NOT NULL, -- 담당기관 (지사명) reg_date date NOT NULL, -- 등록일 (YYYY-MM-DD 형식) tel varchar(20) NOT NULL, -- 연락처 created_at timestamptz DEFAULT CURRENT_TIMESTAMP NOT NULL, -- 레코드 생성 시각 updated_at timestamptz DEFAULT CURRENT_TIMESTAMP, -- 레코드 수정 시각 created_by varchar(40) NULL, -- 데이터 생성자 (SYS-BACH, SYS-MANUAL, BY-USER, admin name), "sys_work_type" comm code 참조 updated_by varchar(40) NULL, -- 데이터 수정자 CONSTRAINT pkey_emp_dis_job_posting PRIMARY KEY (id) ); -- Create indexes CREATE INDEX idx_emp_dis_job_posting_job_type ON public.emp_dis_job_posting USING btree (job_type); CREATE INDEX idx_emp_dis_job_posting_company_type ON public.emp_dis_job_posting USING btree (company_type); CREATE INDEX idx_emp_dis_job_posting_office ON public.emp_dis_job_posting USING btree (office); -- Table comment COMMENT ON TABLE public.emp_dis_job_posting IS '05. 장애인 구인 정보 테이블'; -- Column comments COMMENT ON COLUMN public.emp_dis_job_posting.id IS '시스템 ID, 고유 식별자 (자동 증가)'; COMMENT ON COLUMN public.emp_dis_job_posting.seq_no IS '연번 (원본 데이터 순번)'; COMMENT ON COLUMN public.emp_dis_job_posting.apply_date IS '구인신청일자 (YYYY-MM-DD 형식)'; COMMENT ON COLUMN public.emp_dis_job_posting.recruit_period IS '모집기간 (예: 2024-12-31~2025-01-15)'; COMMENT ON COLUMN public.emp_dis_job_posting.company_name IS '사업장명'; COMMENT ON COLUMN public.emp_dis_job_posting.job_type IS '모집직종'; COMMENT ON COLUMN public.emp_dis_job_posting.emp_type IS '고용형태 (상용직/계약직)'; COMMENT ON COLUMN public.emp_dis_job_posting.salary_type IS '임금형태 (월급/시급)'; COMMENT ON COLUMN public.emp_dis_job_posting.salary_amount IS '임금 (원 단위)'; COMMENT ON COLUMN public.emp_dis_job_posting.hire_type IS '입사형태 (무관 등)'; COMMENT ON COLUMN public.emp_dis_job_posting.experience IS '요구경력 (0년개월, 무관 등)'; COMMENT ON COLUMN public.emp_dis_job_posting.education IS '요구학력 (무관 등)'; COMMENT ON COLUMN public.emp_dis_job_posting.major IS '전공계열'; COMMENT ON COLUMN public.emp_dis_job_posting.license IS '요구자격증'; COMMENT ON COLUMN public.emp_dis_job_posting.address IS '사업장 주소'; COMMENT ON COLUMN public.emp_dis_job_posting.company_type IS '기업형태 (중소, 대기업, 공사공공, 개인)'; COMMENT ON COLUMN public.emp_dis_job_posting.office IS '담당기관 (지사명)'; COMMENT ON COLUMN public.emp_dis_job_posting.reg_date IS '등록일 (YYYY-MM-DD 형식)'; COMMENT ON COLUMN public.emp_dis_job_posting.tel IS '연락처'; COMMENT ON COLUMN public.emp_dis_job_posting.created_at IS '레코드 생성 시각'; COMMENT ON COLUMN public.emp_dis_job_posting.updated_at IS '레코드 수정 시각'; COMMENT ON COLUMN public.emp_dis_job_posting.created_by IS '데이터 생성자 (SYS-BACH, SYS-MANUAL, BY-USER, admin name), "sys_work_type" comm code 참조'; COMMENT ON COLUMN public.emp_dis_job_posting.updated_by IS '데이터 수정자'; -- #### 06.장애인고용 부담금감면 연계고용사업장 정보 테이블 #### -- # public.emp_dis_burden_workplace definition DROP TABLE IF EXISTS public.emp_dis_burden_workplace; CREATE TABLE public.emp_dis_burden_workplace ( id int4 GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, -- 시스템 ID, 고유 식별자 (자동 증가) year int4 NOT NULL, -- 연도 company_name varchar(200) NOT NULL, -- 사업장명 facility_type varchar(50) NOT NULL, -- 시설구분 address varchar(500) NOT NULL, -- 사업장소재지 work_item varchar(200) NOT NULL, -- 도급품목 worker_count int4 NOT NULL, -- 상시근로자수 disabled_count int4 NOT NULL, -- 장애인수 severe_count int4 NOT NULL, -- 중증장애인수 created_at timestamptz DEFAULT CURRENT_TIMESTAMP NOT NULL, -- 레코드 생성 시각 updated_at timestamptz DEFAULT CURRENT_TIMESTAMP, -- 레코드 수정 시각 created_by varchar(40) NULL, -- 데이터 생성자 (SYS-BACH, SYS-MANUAL, BY-USER, admin name), "sys_work_type" comm code 참조 updated_by varchar(40) NULL -- 데이터 수정자 ); CREATE INDEX idx_emp_dis_burden_workplace_year_facility_type ON public.emp_dis_burden_workplace (year, facility_type); COMMENT ON TABLE public.emp_dis_burden_workplace IS '06. 장애인고용 부담금감면 연계고용사업장 정보 테이블'; COMMENT ON COLUMN public.emp_dis_burden_workplace.id IS '시스템 ID, 고유 식별자 (자동 증가)'; COMMENT ON COLUMN public.emp_dis_burden_workplace.year IS '연도'; COMMENT ON COLUMN public.emp_dis_burden_workplace.company_name IS '사업장명'; COMMENT ON COLUMN public.emp_dis_burden_workplace.facility_type IS '시설구분'; COMMENT ON COLUMN public.emp_dis_burden_workplace.address IS '사업장소재지'; COMMENT ON COLUMN public.emp_dis_burden_workplace.work_item IS '도급품목'; COMMENT ON COLUMN public.emp_dis_burden_workplace.worker_count IS '상시근로자수'; COMMENT ON COLUMN public.emp_dis_burden_workplace.disabled_count IS '장애인수'; COMMENT ON COLUMN public.emp_dis_burden_workplace.severe_count IS '중증장애인수'; COMMENT ON COLUMN public.emp_dis_burden_workplace.created_at IS '레코드 생성 시각'; COMMENT ON COLUMN public.emp_dis_burden_workplace.updated_at IS '레코드 수정 시각'; COMMENT ON COLUMN public.emp_dis_burden_workplace.created_by IS '데이터 생성자 (SYS-BACH, SYS-MANUAL, BY-USER, admin name), "sys_work_type" comm code 참조'; COMMENT ON COLUMN public.emp_dis_burden_workplace.updated_by IS '데이터 수정자'; -- #### 13.장애인 고용장려금 지급 현황 테이블 #### -- # public.emp_dis_emp_incentive definition DROP TABLE IF EXISTS public.emp_dis_emp_incentive; CREATE TABLE public.emp_dis_emp_incentive ( id int4 GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, -- 시스템 ID region varchar(50) NOT NULL, -- 시도구분 industry varchar(100) NOT NULL, -- 업종 company_count int NOT NULL, -- 사업체수 amount bigint NOT NULL, -- 지급액 paid_person int NOT NULL, -- 지급순인원 paid_year_person int NOT NULL, -- 지급연인원 created_at timestamptz DEFAULT CURRENT_TIMESTAMP NOT NULL, -- 생성시각 updated_at timestamptz DEFAULT CURRENT_TIMESTAMP, -- 수정시각 created_by varchar(40), -- 데이터 생성자 (SYS-BACH, SYS-MANUAL, BY-USER, admin name), "sys_work_type" comm code 참조 updated_by varchar(40) -- 데이터 수정자 ); CREATE INDEX idx_emp_dis_emp_incentive_region_industry ON public.emp_dis_emp_incentive (region, industry); COMMENT ON TABLE public.emp_dis_emp_incentive IS '13. 신규고용장려금 지역별 지급 현황 테이블'; COMMENT ON COLUMN public.emp_dis_emp_incentive.id IS '시스템 ID'; COMMENT ON COLUMN public.emp_dis_emp_incentive.region IS '시도구분'; COMMENT ON COLUMN public.emp_dis_emp_incentive.industry IS '업종'; COMMENT ON COLUMN public.emp_dis_emp_incentive.company_count IS '사업체수'; COMMENT ON COLUMN public.emp_dis_emp_incentive.amount IS '지급액'; COMMENT ON COLUMN public.emp_dis_emp_incentive.paid_person IS '지급순인원'; COMMENT ON COLUMN public.emp_dis_emp_incentive.paid_year_person IS '지급연인원'; COMMENT ON COLUMN public.emp_dis_emp_incentive.created_at IS '생성시각'; COMMENT ON COLUMN public.emp_dis_emp_incentive.updated_at IS '수정시각'; COMMENT ON COLUMN public.emp_dis_emp_incentive.created_by IS '데이터 생성자 (SYS-BACH, SYS-MANUAL, BY-USER, admin name), "sys_work_type" comm code 참조'; COMMENT ON COLUMN public.emp_dis_emp_incentive.updated_by IS '데이터 수정자'; -- #### 07.지역별 장애인 고용 현황 테이블 #### -- # public.emp_dis_regional_status definition DROP TABLE IF EXISTS public.emp_dis_regional_status; CREATE TABLE public.emp_dis_regional_status ( id int4 GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, -- 시스템 ID, 고유 식별자 (자동 증가) year int NOT NULL, -- 연도 region varchar(100) NOT NULL, -- 시군구 구분 (예: 서울 종로구, 서울 중구) company_count int4 NOT NULL, -- 사업체수 worker_count int4 NOT NULL, -- 적용대상 근로자수 obligation_count int4 NOT NULL, -- 의무고용 인원 severe_2x_count int4 NOT NULL, -- 중증2배수 적용 장애인 고용인원 severe_2x_rate decimal(5,2) NOT NULL, -- 중증2배수 적용 장애인 고용률 (%) created_at timestamptz DEFAULT CURRENT_TIMESTAMP NOT NULL, -- 레코드 생성 시각 updated_at timestamptz DEFAULT CURRENT_TIMESTAMP, -- 레코드 수정 시각 created_by varchar(40) NULL, -- 데이터 생성자 (SYS-BACH, SYS-MANUAL, BY-USER, admin name), "sys_work_type" comm code 참조 updated_by varchar(40) NULL -- 데이터 수정자 ); CREATE UNIQUE INDEX uidx_emp_dis_regional_status_year_region ON public.emp_dis_regional_status USING btree (year,region); CREATE INDEX idx_emp_dis_regional_status_year ON public.emp_dis_regional_status (year); CREATE INDEX idx_emp_dis_regional_status_region ON public.emp_dis_regional_status (region); COMMENT ON TABLE public.emp_dis_regional_status IS '07. 지역별 장애인 고용 현황 테이블'; COMMENT ON COLUMN public.emp_dis_regional_status.id IS '시스템 ID, 고유 식별자 (자동 증가)'; COMMENT ON COLUMN public.emp_dis_regional_status.year IS '연도 (YYYY)'; COMMENT ON COLUMN public.emp_dis_regional_status.region IS '시군구 구분 (예: 서울 종로구, 서울 중구)'; COMMENT ON COLUMN public.emp_dis_regional_status.company_count IS '사업체수'; COMMENT ON COLUMN public.emp_dis_regional_status.worker_count IS '적용대상 근로자수'; COMMENT ON COLUMN public.emp_dis_regional_status.obligation_count IS '의무고용 인원'; COMMENT ON COLUMN public.emp_dis_regional_status.severe_2x_count IS '중증2배수 적용 장애인 고용인원'; COMMENT ON COLUMN public.emp_dis_regional_status.severe_2x_rate IS '중증2배수 적용 장애인 고용률 (%)'; COMMENT ON COLUMN public.emp_dis_regional_status.created_at IS '레코드 생성 시각'; COMMENT ON COLUMN public.emp_dis_regional_status.updated_at IS '레코드 수정 시각'; COMMENT ON COLUMN public.emp_dis_regional_status.created_by IS '데이터 생성자 (SYS-BACH, SYS-MANUAL, BY-USER, admin name), "sys_work_type" comm code 참조'; COMMENT ON COLUMN public.emp_dis_regional_status.updated_by IS '데이터 수정자'; -- #### 08.고용개발원 교육정보 테이블(장애인고용 전문인력 교육과정) #### -- # public.emp_dis_staff_train_crs definition DROP TABLE IF EXISTS public.emp_dis_staff_train_crs; CREATE TABLE public.emp_dis_staff_train_crs ( id int4 GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, -- 시스템 ID course_type varchar(50) NOT NULL, -- 과정구분 course_name varchar(100) NOT NULL, -- 과정명 course_content text NOT NULL, -- 교육내용 method varchar(50) NOT NULL, -- 교육방법 start_date date NOT NULL, -- 교육시작일 end_date date NOT NULL, -- 교육종료일 recruit_count int NOT NULL, -- 모집인원 apply_start_date date, -- 수강신청 시작일 apply_end_date date, -- 수강신청 종료일 apply_method varchar(200), -- 수강신청방법 location varchar(100), -- 교육장소 target varchar(200), -- 교육대상 created_at timestamptz DEFAULT CURRENT_TIMESTAMP NOT NULL, -- 생성시각 updated_at timestamptz DEFAULT CURRENT_TIMESTAMP, -- 수정시각 created_by varchar(40), -- 데이터 생성자 (SYS-BACH, SYS-MANUAL, BY-USER, admin name), "sys_work_type" comm code 참조 updated_by varchar(40) -- 데이터 수정자 ); CREATE INDEX idx_emp_dis_staff_train_crs_type_name ON public.emp_dis_staff_train_crs (course_type, course_name); COMMENT ON TABLE public.emp_dis_staff_train_crs IS '08. 고용개발원 교육정보 테이블'; COMMENT ON COLUMN public.emp_dis_staff_train_crs.id IS '시스템 ID'; COMMENT ON COLUMN public.emp_dis_staff_train_crs.course_type IS '과정구분'; COMMENT ON COLUMN public.emp_dis_staff_train_crs.course_name IS '과정명'; COMMENT ON COLUMN public.emp_dis_staff_train_crs.course_content IS '교육내용'; COMMENT ON COLUMN public.emp_dis_staff_train_crs.method IS '교육방법'; COMMENT ON COLUMN public.emp_dis_staff_train_crs.start_date IS '교육시작일 (YYYY-MM-DD)'; COMMENT ON COLUMN public.emp_dis_staff_train_crs.end_date IS '교육종료일 (YYYY-MM-DD)'; COMMENT ON COLUMN public.emp_dis_staff_train_crs.recruit_count IS '모집인원'; COMMENT ON COLUMN public.emp_dis_staff_train_crs.apply_start_date IS '수강신청 시작일 (YYYY-MM-DD). 값이 없을 경우 "별도모집안내"로 표시'; COMMENT ON COLUMN public.emp_dis_staff_train_crs.apply_end_date IS '수강신청 종료일 (YYYY-MM-DD). 값이 없을 경우 "별도모집안내"로 표시'; COMMENT ON COLUMN public.emp_dis_staff_train_crs.apply_method IS '수강신청방법'; COMMENT ON COLUMN public.emp_dis_staff_train_crs.location IS '교육장소'; COMMENT ON COLUMN public.emp_dis_staff_train_crs.target IS '교육대상'; COMMENT ON COLUMN public.emp_dis_staff_train_crs.created_at IS '생성시각'; COMMENT ON COLUMN public.emp_dis_staff_train_crs.updated_at IS '수정시각'; COMMENT ON COLUMN public.emp_dis_staff_train_crs.created_by IS '데이터 생성자 (SYS-BACH, SYS-MANUAL, BY-USER, admin name), "sys_work_type" comm code 참조'; COMMENT ON COLUMN public.emp_dis_staff_train_crs.updated_by IS '데이터 수정자'; -- #### 09.발달장애인 지원 기관 및 제공서비스 테이블 #### -- # public.emp_dis_dev_support_org definition DROP TABLE IF EXISTS public.emp_dis_dev_support_org; CREATE TABLE public.emp_dis_dev_support_org ( id int4 GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, -- 시스템 ID seq_no int4 NOT NULL, -- 연번 (원본 데이터 순번) org_name varchar(200) NOT NULL, -- 기관명 region varchar(100) NOT NULL, -- 시군구 day_activity boolean NOT NULL, -- 주간활동 (O/X → true/false) afterschool boolean NOT NULL, -- 청소년방과후활동 (O/X → true/false) indiv_plan boolean NOT NULL, -- 개인별지원계획 (O/X → true/false) parent_edu boolean NOT NULL, -- 부모교육 (O/X → true/false) family_rest boolean NOT NULL, -- 가족휴식 (O/X → true/false) parent_counsel boolean NOT NULL, -- 부모상담 (O/X → true/false) rights_relief boolean NOT NULL, -- 권리구제 (O/X → true/false) public_guardian boolean NOT NULL, -- 공공후견 (O/X → true/false) child_family_sup boolean NOT NULL, -- 장애아가족양육지원 (O/X → true/false) emergency_care boolean NOT NULL, -- 긴급돌봄 (O/X → true/false) created_at timestamptz DEFAULT CURRENT_TIMESTAMP NOT NULL, -- 생성시각 updated_at timestamptz DEFAULT CURRENT_TIMESTAMP, -- 수정시각 created_by varchar(40), -- 데이터 생성자 (SYS-BACH, SYS-MANUAL, BY-USER, admin name), "sys_work_type" comm code 참조 updated_by varchar(40) -- 데이터 수정자 ); CREATE INDEX idx_emp_dis_dev_support_org_region_org_name ON public.emp_dis_dev_support_org (region, org_name); COMMENT ON TABLE public.emp_dis_dev_support_org IS '09. 한국장애인개발원 발달장애인 지원 기관 및 제공서비스 테이블'; COMMENT ON COLUMN public.emp_dis_dev_support_org.id IS '시스템 ID'; COMMENT ON COLUMN public.emp_dis_dev_support_org.seq_no IS '연번 (원본 데이터 순번)'; COMMENT ON COLUMN public.emp_dis_dev_support_org.org_name IS '기관명'; COMMENT ON COLUMN public.emp_dis_dev_support_org.region IS '시군구'; COMMENT ON COLUMN public.emp_dis_dev_support_org.day_activity IS '주간활동 (O/X → true/false)'; COMMENT ON COLUMN public.emp_dis_dev_support_org.afterschool IS '청소년방과후활동 (O/X → true/false)'; COMMENT ON COLUMN public.emp_dis_dev_support_org.indiv_plan IS '개인별지원계획 (O/X → true/false)'; COMMENT ON COLUMN public.emp_dis_dev_support_org.parent_edu IS '부모교육 (O/X → true/false)'; COMMENT ON COLUMN public.emp_dis_dev_support_org.family_rest IS '가족휴식 (O/X → true/false)'; COMMENT ON COLUMN public.emp_dis_dev_support_org.parent_counsel IS '부모상담 (O/X → true/false)'; COMMENT ON COLUMN public.emp_dis_dev_support_org.rights_relief IS '권리구제 (O/X → true/false)'; COMMENT ON COLUMN public.emp_dis_dev_support_org.public_guardian IS '공공후견 (O/X → true/false)'; COMMENT ON COLUMN public.emp_dis_dev_support_org.child_family_sup IS '장애아가족양육지원 (O/X → true/false)'; COMMENT ON COLUMN public.emp_dis_dev_support_org.emergency_care IS '긴급돌봄 (O/X → true/false)'; COMMENT ON COLUMN public.emp_dis_dev_support_org.created_at IS '생성시각'; COMMENT ON COLUMN public.emp_dis_dev_support_org.updated_at IS '수정시각'; COMMENT ON COLUMN public.emp_dis_dev_support_org.created_by IS '데이터 생성자 (SYS-BACH, SYS-MANUAL, BY-USER, admin name), "sys_work_type" comm code 참조'; COMMENT ON COLUMN public.emp_dis_dev_support_org.updated_by IS '데이터 수정자'; -- #### 10.장애인기업종합지원센터 창업넷 일반강좌 정보 테이블 #### -- # public.emp_dis_startup_lecture definition DROP TABLE IF EXISTS public.emp_dis_startup_lecture; CREATE TABLE public.emp_dis_startup_lecture ( id int4 GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, -- 시스템 ID year int NOT NULL, -- 교육년도 online_type varchar(20) NOT NULL, -- 온오프라인 category varchar(50) NOT NULL, -- 교육구분 title varchar(200) NOT NULL, -- 교육제목 start_date date NOT NULL, -- 교육시작일 (YYYY-MM-DD) end_date date NOT NULL, -- 교육종료일 (YYYY-MM-DD) hours int NOT NULL, -- 교육시간 recruit_count int NOT NULL, -- 모집인원 apply_start_date date NOT NULL, -- 신청시작일 (YYYY-MM-DD) apply_end_date date NOT NULL, -- 신청종료일 (YYYY-MM-DD) org_name varchar(100) NOT NULL, -- 교육기관 apply_count int NOT NULL, -- 신청인원 complete_count int NOT NULL, -- 수료인원 created_at timestamptz DEFAULT CURRENT_TIMESTAMP NOT NULL, -- 생성시각 updated_at timestamptz DEFAULT CURRENT_TIMESTAMP, -- 수정시각 created_by varchar(40), -- 데이터 생성자 (SYS-BACH, SYS-MANUAL, BY-USER, admin name), "sys_work_type" comm code 참조 updated_by varchar(40) -- 데이터 수정자 ); CREATE INDEX idx_emp_dis_startup_lecture_year_title ON public.emp_dis_startup_lecture (year, title); COMMENT ON TABLE public.emp_dis_startup_lecture IS '10. 장애인기업종합지원센터 창업넷 일반강좌 정보 테이블'; COMMENT ON COLUMN public.emp_dis_startup_lecture.id IS '시스템 ID'; COMMENT ON COLUMN public.emp_dis_startup_lecture.year IS '교육년도'; COMMENT ON COLUMN public.emp_dis_startup_lecture.online_type IS '온오프라인'; COMMENT ON COLUMN public.emp_dis_startup_lecture.category IS '교육구분'; COMMENT ON COLUMN public.emp_dis_startup_lecture.title IS '교육제목'; COMMENT ON COLUMN public.emp_dis_startup_lecture.start_date IS '교육시작일 (YYYY-MM-DD)'; COMMENT ON COLUMN public.emp_dis_startup_lecture.end_date IS '교육종료일 (YYYY-MM-DD)'; COMMENT ON COLUMN public.emp_dis_startup_lecture.hours IS '교육시간'; COMMENT ON COLUMN public.emp_dis_startup_lecture.recruit_count IS '모집인원'; COMMENT ON COLUMN public.emp_dis_startup_lecture.apply_start_date IS '신청시작일 (YYYY-MM-DD)'; COMMENT ON COLUMN public.emp_dis_startup_lecture.apply_end_date IS '신청종료일 (YYYY-MM-DD)'; COMMENT ON COLUMN public.emp_dis_startup_lecture.org_name IS '교육기관'; COMMENT ON COLUMN public.emp_dis_startup_lecture.apply_count IS '신청인원'; COMMENT ON COLUMN public.emp_dis_startup_lecture.complete_count IS '수료인원'; COMMENT ON COLUMN public.emp_dis_startup_lecture.created_at IS '생성시각'; COMMENT ON COLUMN public.emp_dis_startup_lecture.updated_at IS '수정시각'; COMMENT ON COLUMN public.emp_dis_startup_lecture.created_by IS '데이터 생성자 (SYS-BACH, SYS-MANUAL, BY-USER, admin name), "sys_work_type" comm code 참조'; COMMENT ON COLUMN public.emp_dis_startup_lecture.updated_by IS '데이터 수정자'; -- #### 11.장애인 표준사업장 현황 테이블 #### -- # public.emp_dis_std_workplace definition DROP TABLE IF EXISTS public.emp_dis_std_workplace; CREATE TABLE public.emp_dis_std_workplace ( id int4 GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, -- 시스템 ID cert_no varchar(30) NOT NULL, -- 인증번호 company_name varchar(100) NOT NULL, -- 사업체명 branch varchar(50) NOT NULL, -- 관할지사 ceo varchar(50) NOT NULL, -- 대표자 business_no varchar(20) NOT NULL, -- 사업자등록번호 address varchar(200) NOT NULL, -- 소재지 cert_date date NOT NULL, -- 인증일자 (YYYY-MM-DD) tel varchar(20) NOT NULL, -- 전화번호 business_item varchar(200) NOT NULL, -- 업종 및 주요생산품 type varchar(20) NOT NULL, -- 구분 created_at timestamptz DEFAULT CURRENT_TIMESTAMP NOT NULL, -- 생성시각 updated_at timestamptz DEFAULT CURRENT_TIMESTAMP, -- 수정시각 created_by varchar(40), -- 데이터 생성자 (SYS-BACH, SYS-MANUAL, BY-USER, admin name), "sys_work_type" comm code 참조 updated_by varchar(40) -- 데이터 수정자 ); CREATE INDEX idx_emp_dis_std_workplace_cert_no_company_name ON public.emp_dis_std_workplace (cert_no, company_name); COMMENT ON TABLE public.emp_dis_std_workplace IS '11. 장애인 표준사업장 현황 테이블'; COMMENT ON COLUMN public.emp_dis_std_workplace.id IS '시스템 ID'; COMMENT ON COLUMN public.emp_dis_std_workplace.cert_no IS '인증번호'; COMMENT ON COLUMN public.emp_dis_std_workplace.company_name IS '사업체명'; COMMENT ON COLUMN public.emp_dis_std_workplace.branch IS '관할지사'; COMMENT ON COLUMN public.emp_dis_std_workplace.ceo IS '대표자'; COMMENT ON COLUMN public.emp_dis_std_workplace.business_no IS '사업자등록번호'; COMMENT ON COLUMN public.emp_dis_std_workplace.address IS '소재지'; COMMENT ON COLUMN public.emp_dis_std_workplace.cert_date IS '인증일자 (YYYY-MM-DD)'; COMMENT ON COLUMN public.emp_dis_std_workplace.tel IS '전화번호'; COMMENT ON COLUMN public.emp_dis_std_workplace.business_item IS '업종 및 주요생산품'; COMMENT ON COLUMN public.emp_dis_std_workplace.type IS '구분'; COMMENT ON COLUMN public.emp_dis_std_workplace.created_at IS '생성시각'; COMMENT ON COLUMN public.emp_dis_std_workplace.updated_at IS '수정시각'; COMMENT ON COLUMN public.emp_dis_std_workplace.created_by IS '데이터 생성자 (SYS-BACH, SYS-MANUAL, BY-USER, admin name), "sys_work_type" comm code 참조'; COMMENT ON COLUMN public.emp_dis_std_workplace.updated_by IS '데이터 수정자'; -- #### 12.장애유형별 의무고용 현황 테이블 #### -- # public.emp_dis_obligation_by_type definition DROP TABLE IF EXISTS public.emp_dis_obligation_by_type; CREATE TABLE public.emp_dis_obligation_by_type ( id int4 GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, -- 시스템 ID org_type varchar(50) NOT NULL, -- 사업체유형 category varchar(50) NOT NULL, -- 구분 total int NOT NULL, -- 계 limb_severe int NOT NULL, -- 지체 중증 limb_mild int NOT NULL, -- 지체 경증 brain_severe int NOT NULL, -- 뇌병변 중증 brain_mild int NOT NULL, -- 뇌병변 경증 vision_severe int NOT NULL, -- 시각 중증 vision_mild int NOT NULL, -- 시각 경증 hearing_severe int NOT NULL, -- 청각 중증 hearing_mild int NOT NULL, -- 청각 경증 speech_severe int NOT NULL, -- 언어 중증 speech_mild int NOT NULL, -- 언어 경증 intellectual int NOT NULL, -- 지적 mental_severe int NOT NULL, -- 정신 중증 mental_mild int NOT NULL, -- 정신 경증 autism int NOT NULL, -- 자폐성 kidney_severe int NOT NULL, -- 신장 중증 kidney_mild int NOT NULL, -- 신장 경증 heart_severe int NOT NULL, -- 심장 중증 heart_mild int NOT NULL, -- 심장 경증 lung_severe int NOT NULL, -- 호흡기 중증 lung_mild int NOT NULL, -- 호흡기 경증 liver_severe int NOT NULL, -- 간 중증 liver_mild int NOT NULL, -- 간 경증 face_severe int NOT NULL, -- 안면 중증 face_mild int NOT NULL, -- 안면 경증 stoma_severe int NOT NULL, -- 장루요루 중증 stoma_mild int NOT NULL, -- 장루요루 경증 epilepsy_severe int NOT NULL, -- 뇌전증 중증 epilepsy_mild int NOT NULL, -- 뇌전증 경증 veteran int NOT NULL, -- 국가유공 created_at timestamptz DEFAULT CURRENT_TIMESTAMP NOT NULL, -- 생성시각 updated_at timestamptz DEFAULT CURRENT_TIMESTAMP, -- 수정시각 created_by varchar(40), -- 데이터 생성자 (SYS-BACH, SYS-MANUAL, BY-USER, admin name), "sys_work_type" comm code 참조 updated_by varchar(40) -- 데이터 수정자 ); CREATE INDEX idx_emp_dis_obligation_by_type_org_type_category ON public.emp_dis_obligation_by_type (org_type, category); COMMENT ON TABLE public.emp_dis_obligation_by_type IS '12. 장애인 의무고용 사업체 장애유형별 고용현황 테이블'; COMMENT ON COLUMN public.emp_dis_obligation_by_type.id IS '시스템 ID'; COMMENT ON COLUMN public.emp_dis_obligation_by_type.org_type IS '사업체유형'; COMMENT ON COLUMN public.emp_dis_obligation_by_type.category IS '구분'; COMMENT ON COLUMN public.emp_dis_obligation_by_type.total IS '계'; COMMENT ON COLUMN public.emp_dis_obligation_by_type.limb_severe IS '지체 중증'; COMMENT ON COLUMN public.emp_dis_obligation_by_type.limb_mild IS '지체 경증'; COMMENT ON COLUMN public.emp_dis_obligation_by_type.brain_severe IS '뇌병변 중증'; COMMENT ON COLUMN public.emp_dis_obligation_by_type.brain_mild IS '뇌병변 경증'; COMMENT ON COLUMN public.emp_dis_obligation_by_type.vision_severe IS '시각 중증'; COMMENT ON COLUMN public.emp_dis_obligation_by_type.vision_mild IS '시각 경증'; COMMENT ON COLUMN public.emp_dis_obligation_by_type.hearing_severe IS '청각 중증'; COMMENT ON COLUMN public.emp_dis_obligation_by_type.hearing_mild IS '청각 경증'; COMMENT ON COLUMN public.emp_dis_obligation_by_type.speech_severe IS '언어 중증'; COMMENT ON COLUMN public.emp_dis_obligation_by_type.speech_mild IS '언어 경증'; COMMENT ON COLUMN public.emp_dis_obligation_by_type.intellectual IS '지적'; COMMENT ON COLUMN public.emp_dis_obligation_by_type.mental_severe IS '정신 중증'; COMMENT ON COLUMN public.emp_dis_obligation_by_type.mental_mild IS '정신 경증'; COMMENT ON COLUMN public.emp_dis_obligation_by_type.autism IS '자폐성'; COMMENT ON COLUMN public.emp_dis_obligation_by_type.kidney_severe IS '신장 중증'; COMMENT ON COLUMN public.emp_dis_obligation_by_type.kidney_mild IS '신장 경증'; COMMENT ON COLUMN public.emp_dis_obligation_by_type.heart_severe IS '심장 중증'; COMMENT ON COLUMN public.emp_dis_obligation_by_type.heart_mild IS '심장 경증'; COMMENT ON COLUMN public.emp_dis_obligation_by_type.lung_severe IS '호흡기 중증'; COMMENT ON COLUMN public.emp_dis_obligation_by_type.lung_mild IS '호흡기 경증'; COMMENT ON COLUMN public.emp_dis_obligation_by_type.liver_severe IS '간 중증'; COMMENT ON COLUMN public.emp_dis_obligation_by_type.liver_mild IS '간 경증'; COMMENT ON COLUMN public.emp_dis_obligation_by_type.face_severe IS '안면 중증'; COMMENT ON COLUMN public.emp_dis_obligation_by_type.face_mild IS '안면 경증'; COMMENT ON COLUMN public.emp_dis_obligation_by_type.stoma_severe IS '장루요루 중증'; COMMENT ON COLUMN public.emp_dis_obligation_by_type.stoma_mild IS '장루요루 경증'; COMMENT ON COLUMN public.emp_dis_obligation_by_type.epilepsy_severe IS '뇌전증 중증'; COMMENT ON COLUMN public.emp_dis_obligation_by_type.epilepsy_mild IS '뇌전증 경증'; COMMENT ON COLUMN public.emp_dis_obligation_by_type.veteran IS '국가유공'; COMMENT ON COLUMN public.emp_dis_obligation_by_type.created_at IS '생성시각'; COMMENT ON COLUMN public.emp_dis_obligation_by_type.updated_at IS '수정시각'; COMMENT ON COLUMN public.emp_dis_obligation_by_type.created_by IS '데이터 생성자 (SYS-BACH, SYS-MANUAL, BY-USER, admin name), "sys_work_type" comm code 참조'; COMMENT ON COLUMN public.emp_dis_obligation_by_type.updated_by IS '데이터 수정자'; -- #### 03.장애인 고용의무 현황 통계 테이블 #### -- # public.emp_dis_obligation_status definition DROP TABLE IF EXISTS public.emp_dis_obligation_status; CREATE TABLE public.emp_dis_obligation_status ( id int4 GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, -- 시스템 ID, 고유 식별자 (자동 증가) org_name varchar(100) NOT NULL, -- 기관명 (예: 중앙행정기관, 헌법기관, 지방자치단체) workplace_count int4 NOT NULL, -- 대상 사업체 수 (개소) worker_count int4 NOT NULL, -- 상시 근로자 수 (명) disabled_count int4 NOT NULL, -- 장애인 수 (명) emp_rate decimal(5,2) NOT NULL, -- 고용률 (%) created_at timestamptz DEFAULT CURRENT_TIMESTAMP NOT NULL, -- 레코드 생성 시각 updated_at timestamptz DEFAULT CURRENT_TIMESTAMP, -- 레코드 수정 시각 created_by varchar(40) NULL, -- 데이터 생성자 (SYS-BACH, SYS-MANUAL, BY-USER, admin name), "sys_work_type" comm code 참조 updated_by varchar(40) NULL -- 데이터 수정자 ); CREATE INDEX idx_emp_dis_obligation_org_name ON public.emp_dis_obligation_status (org_name); COMMENT ON TABLE public.emp_dis_obligation_status IS '03. 장애인 고용의무 현황 통계 테이블'; COMMENT ON COLUMN public.emp_dis_obligation_status.id IS '시스템 ID, 고유 식별자 (자동 증가)'; COMMENT ON COLUMN public.emp_dis_obligation_status.org_name IS '기관명 (예: 중앙행정기관, 헌법기관, 지방자치단체)'; COMMENT ON COLUMN public.emp_dis_obligation_status.workplace_count IS '대상 사업체 수 (개소)'; COMMENT ON COLUMN public.emp_dis_obligation_status.worker_count IS '상시 근로자 수 (명)'; COMMENT ON COLUMN public.emp_dis_obligation_status.disabled_count IS '장애인 수 (명)'; COMMENT ON COLUMN public.emp_dis_obligation_status.emp_rate IS '고용률 (%)'; COMMENT ON COLUMN public.emp_dis_obligation_status.created_at IS '레코드 생성 시각'; COMMENT ON COLUMN public.emp_dis_obligation_status.updated_at IS '레코드 수정 시각'; COMMENT ON COLUMN public.emp_dis_obligation_status.created_by IS '데이터 생성자 (SYS-BACH, SYS-MANUAL, BY-USER, admin name), "sys_work_type" comm code 참조'; COMMENT ON COLUMN public.emp_dis_obligation_status.updated_by IS '데이터 수정자'; -- #### 14.산업별 의무고용 현황 테이블 #### -- # public.emp_dis_obligation_by_indust definition DROP TABLE IF EXISTS public.emp_dis_obligation_by_indust; CREATE TABLE public.emp_dis_obligation_by_indust ( id int4 GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, -- 시스템 ID year int NOT NULL, -- 연도 industry varchar(100) NOT NULL, -- 업종 company_count int NOT NULL, -- 사업체수 worker_count int NOT NULL, -- 근로자수 obligation_count int NOT NULL, -- 의무고용인원 emp_rate decimal(5,2) NOT NULL, -- 고용률 (%) created_at timestamptz DEFAULT CURRENT_TIMESTAMP NOT NULL, -- 생성시각 updated_at timestamptz DEFAULT CURRENT_TIMESTAMP, -- 수정시각 created_by varchar(40), -- 데이터 생성자 (SYS-BACH, SYS-MANUAL, BY-USER, admin name), "sys_work_type" comm code 참조 updated_by varchar(40) -- 데이터 수정자 ); CREATE INDEX idx_emp_dis_obligation_by_indust_year_industry ON public.emp_dis_obligation_by_indust (year, industry); COMMENT ON TABLE public.emp_dis_obligation_by_indust IS '14. 산업별 의무고용 현황 테이블'; COMMENT ON COLUMN public.emp_dis_obligation_by_indust.id IS '시스템 ID'; COMMENT ON COLUMN public.emp_dis_obligation_by_indust.year IS '연도'; COMMENT ON COLUMN public.emp_dis_obligation_by_indust.industry IS '업종'; COMMENT ON COLUMN public.emp_dis_obligation_by_indust.company_count IS '사업체수'; COMMENT ON COLUMN public.emp_dis_obligation_by_indust.worker_count IS '근로자수'; COMMENT ON COLUMN public.emp_dis_obligation_by_indust.obligation_count IS '의무고용인원'; COMMENT ON COLUMN public.emp_dis_obligation_by_indust.emp_rate IS '고용률 (%)'; COMMENT ON COLUMN public.emp_dis_obligation_by_indust.created_at IS '생성시각'; COMMENT ON COLUMN public.emp_dis_obligation_by_indust.updated_at IS '수정시각'; COMMENT ON COLUMN public.emp_dis_obligation_by_indust.created_by IS '데이터 생성자 (SYS-BACH, SYS-MANUAL, BY-USER, admin name), "sys_work_type" comm code 참조'; COMMENT ON COLUMN public.emp_dis_obligation_by_indust.updated_by IS '데이터 수정자';