[Oracle-SQL] 회사 데이터베이스

CREATE TABLE department
(
    deptno      NUMBER(4)        NOT NULL,  -- 부서번호(PK)
    deptname    VARCHAR2(10),               -- 부서이름
    deptloc     VARCHAR2(13),               -- 부서위치
    CONSTRAINT department_pk PRIMARY KEY (deptno)
);

CREATE TABLE employee
(
    enum    	NUMBER(4)  NOT NULL,    -- 사원번호(PK)
    ename   	VARCHAR2(10),           -- 사원이름
    job     	VARCHAR2(9),            -- 직책
    sal     	NUMBER(15),            	-- 급여
    city    	VARCHAR2(10),           -- 시
    gu      	VARCHAR2(10),           -- 구
    dong    	VARCHAR2(10),           -- 동
    edeptname   NUMBER(4),        	-- 소속부서번호(FK)
    CONSTRAINT employee_pk PRIMARY KEY (enum),
    CONSTRAINT employee_fk FOREIGN KEY (edeptname) REFERENCES department(deptno)
);

CREATE TABLE supplyer
(
    snum    NUMBER(4)   NOT NULL,   -- 공급자번호(PK)
    sname   VARCHAR2(10),           -- 공급자이름
    credit  VARCHAR2(10),           -- 신용도
    CONSTRAINT supplyer_pk PRIMARY KEY (snum)
);

CREATE TABLE pro
(
    pnum    	NUMBER(4)   NOT NULL,   -- 프로젝트번호(PK)
    pname   	VARCHAR2(10),           -- 프로젝트이름 
    budget  	NUMBER(10),             -- 예산
    manager 	NUMBER(4),              -- 관리자(FK)
    managedate  NUMBER(8),          	-- 관리시작날짜
    CONSTRAINT pro_pk PRIMARY KEY (pnum),
    CONSTRAINT pro_fk FOREIGN KEY (manager) REFERENCES employee (enum)
);

CREATE TABLE part
(
    partnum     	NUMBER(4)   NOT NULL,	-- 부품번호(PK)
    partname    	VARCHAR2(10),		-- 부품이름
    price       	NUMBER(10),		-- 가격
    includepartnum 	NUMBER(4),			-- 포함부품번호(FK)
    CONSTRAINT part_pk PRIMARY KEY (partnum),
    CONSTRAINT part_fk FOREIGN KEY (includepartnum) REFERENCES part (partnum)
);​

CREATE TABLE family
(
    fnum    NUMBER(4),				-- 부양사원번호(FK)
    fname   VARCHAR(10) NOT NULL,		-- 가족이름(PK)
    gender  VARCHAR(10),			-- 성별
    CONSTRAINT family_pk PRIMARY KEY (fname),
    CONSTRAINT family_fk FOREIGN KEY (fnum) REFERENCES employee (enum)
);

CREATE TABLE performace 
(
    perp        NUMBER(4),      -- 수행프로젝트(FK)
    pmanager    NUMBER(4),      -- 담당사원(FK)
    erole       VARCHAR2(10),   -- 사원역할
    term        NUMBER(10),     -- 근무기간
    CONSTRAINT performance_fk FOREIGN KEY (perp) REFERENCES pro (pnum),
    CONSTRAINT performance_fk2 FOREIGN KEY (pmanager) REFERENCES employee(enum)
);

CREATE TABLE supply
(
    smanager    NUMBER(4),  -- 공급담장자(FK)
    sp          NUMBER(4),  -- 공급프로젝트(FK)
    spart       NUMBER(4),  -- 공급부품(FK)
    amount      NUMBER(4),  -- 공급량
    CONSTRAINT supply_fk FOREIGN KEY (smanager) REFERENCES employee (enum),
    CONSTRAINT supply_fk1 FOREIGN KEY (sp) REFERENCES pro (pnum),
    CONSTRAINT supply_fk2 FOREIGN KEY (spart) REFERENCES supplyer (snum)
);

CREATE TABLE progress
(
    prop    NUMBER(4),       -- 진행프로젝트(FK)
    proloc  VARCHAR2(13),    -- 진행위치(PK)
    CONSTRAINT progress_pk PRIMARY KEY (proloc),
    CONSTRAINT progress_fk FOREIGN KEY (prop) REFERENCES pro (pnum)
);

ALTER TABLE employee ADD (egender VARCHAR(1));
ALTER TABLE employee MODIFY (egender NUMBER(4));
ALTER TABLE employee DROP COLUMN egender;