DB설계) 직원 테이블과 급여 테이블 설계하기

1. 데이터베이스 설계하기

 db 이름: employee_db
1. 직원 테이블

열 이름	employee_info	데이터타입	최대길이	null 허용 여부
이름	emp_name	char	5	x
사원번호 (PK)	emp_code	char	10	x
생년월일	emp_birth	date	YYYY-MM-DD	o
성별	emp_gender	char	6 (Female)	o
입사일	emp_start	date	YYYY-MM-DD	x
부서번호 (FK)	team_code	char	3	x
직급	emp_level	char	5	x
급여	emp_pay	bigint		x
성과금	emp_bonus	bigint		o

2. 부서 테이블

열 이름	team_info	데이터타입	최대길이	null 허용 여부
부서번호 (PK)	team_code	char	3	x
부서이름	team_name	char	10	x

최종적으로 출력할 급여 테이블

급여 테이블
직원 이름	emp_name
직원 부서	team_name
직원 직급	emp_level
직원 급여	emp_pay
직원 보너스	emp_bonus

 

2. 테이블 만들 순서 정하기

employee_info는 team_info의 PK값을 FK로 받고 있으므로 team_info를 먼저 만들어준다.

 

3. team_info 테이블 만들고 데이터 넣기

create database employee_db;
use employee_db;

create table team_info (
	team_code char(5) not null primary key,
    team_name char(20) not null
);

show columns from team_info;

insert into team_info values ('AAA', '총무팀');
insert into team_info values ('BBB', '인사과');
insert into team_info values ('CCC', '기획팀');

select * from team_info;

 

3. emp_info 테이블 만들고 데이터 넣기

FK를 설정해 주기 위해 마지막 줄에
foreign key (FK이름) references 마더테이블이름(PK이름)
를 추가해 주면 FK가 마더테이블의 PK를 참조하게 된다.

create table employee_info (
	emp_name char(10) not null,
    emp_code char(20) not null primary key,
    emp_birth date,
    emp_gender char(6),
    emp_start date not null,
    team_code char(5) not null,
    emp_level char(10) not null,
    emp_pay bigint not null,
    emp_bonus bigint,    
    foreign key (team_code) references team_info(team_code)
);

show columns from employee_info;

insert into employee_info values ('김사원', 'MMMM', 20000309, 'Female', 20230910, 'AAA', '신입', 10000000, null);
insert into employee_info values ('이대리', 'BBBB', 19891121, 'Female', 20100302, 'AAA', '대리', 100000000, 50000000);
insert into employee_info values ('박부장', 'AAAA', 19701030, 'Male', 20090210, 'BBB', '신입', 500000000, 50000000);
insert into employee_info values ('신인턴', 'XXXX', 20001011, 'Female', 20231010, 'CCC', '인턴', 5000000, null);
insert into employee_info values ('정사원', 'NNNN', 19960501, 'Male', 20230210, 'CCC', '신입', 10000000, null);
select * from employee_info;

 

4. 조인해서 급여 테이블 데이터 보기

만약 Error Code: 1052. Column 'student_number' in field list is ambiguous 가 뜬다면,  value 이름이 겹쳐서 어느 테이블의 value를 가져오는 건지 확실하게 표시해 주면 된다.

select emp_name, team_info.team_name, emp_level, emp_pay, emp_bonus
	from employee_info
    join team_info
		on team_info.team_code = employee_info.team_code;

 
전체코드

create database employee_db;
use employee_db;

-- team_info table
create table team_info (
	team_code char(5) not null primary key,
    team_name char(20) not null
);


insert into team_info values ('AAA', '총무팀');
insert into team_info values ('BBB', '인사과');
insert into team_info values ('CCC', '기획팀');

-- employee_info table
create table employee_info (
	emp_name char(10) not null,
    emp_code char(20) not null primary key,
    emp_birth date,
    emp_gender char(6),
    emp_start date not null,
    team_code char(5) not null,
    emp_level char(10) not null,
    emp_pay bigint not null,
    emp_bonus bigint,    
    foreign key (team_code) references team_info(team_code)
);

insert into employee_info values ('김사원', 'MMMM', 20000309, 'Female', 20230910, 'AAA', '신입', 10000000, null);
insert into employee_info values ('이대리', 'BBBB', 19891121, 'Female', 20100302, 'AAA', '대리', 100000000, 50000000);
insert into employee_info values ('박부장', 'AAAA', 19701030, 'Male', 20090210, 'BBB', '신입', 500000000, 50000000);
insert into employee_info values ('신인턴', 'XXXX', 20001011, 'Female', 20231010, 'CCC', '인턴', 5000000, null);
insert into employee_info values ('정사원', 'NNNN', 19960501, 'Male', 20230210, 'CCC', '신입', 10000000, null);
select * from employee_info;


-- 급여테이블 조인해서 보기
select emp_name, team_info.team_name, emp_level, emp_pay, emp_bonus
	from employee_info
    join team_info
		on team_info.team_code = employee_info.team_code;