HorseFarm(3) - View생성

자주 사용하는 정보를 정리해두자

앞서 데이터 테이블을 정의하고 각 테이블과의 관계를 정의해보았다.

이번에는 필요한 정보를 보다 빠르고 간편하게 확인할 수 있는 View를 만들어 볼 예정이다.

View를 미리 생성함으로써 갖는 장점은, 자주 사용하는 정보를 별도의 데이터베이스의 긴 탐색 과정 없이 바로 볼 수 있다는 점과 정보를 보기 편한 순서로 정리하여 한 눈에 확인할 수 있다는 점이다.

 

참고로 View는 생성한 뒤에 Alter로 수정이 불가능하다는 점을 알두자. (Drop만 가능)

 

뷰 생성(Create View)

1. 말의 주인에 대한 정보를 View로 만들어 보자

Create view horse_owner as 
select b.first_name, b.last_name, b.primary_phone, h.barn_name 
from boarder_horse bh, boarder b, horse h where bh.horse_id = h.id and bh.boarder_id = b.id 
order by b.last_name;

말 주인에 대한 이름, 전화번호, 위치를 바로 확인할 수 있다

 

2. 말의 식단에 대한 정보를 View로 만들어 보자

Create view feeding as 
select a.barn_name as horse, grain_amount, hay_amount, h.product, j.`type` 
from grain_schedule ahj, horse a, grain h, hay j where ahj.horse_id = a.id and ahj.grain_id = h.id and ahj.hay_id = j.id 
order by a.barn_name;

어떤 말이 어떤 식단을 가지는지 한 눈에 확인할 수 있다.

 

3. 학생들의 수업료에 대한 정보를 View로 만들어 보자

Create view student_bill as 
select b.first_name as student, c.hourly_rate as fee, d.lesson_time as date 
from student b, job c, lesson_schedule d where b.id = d.student_id and c.id = d.job_id;

학생들이 얼마를 내야되는지 확인할 수 있다

두 개의 테이블을 합치자(Union)

Union을 사용하면, 두 개의 select문을 이용해 view를 생성할 수 있다. 이때 view를 생성하기 위해서는 두 개의 select문이 모두 같은 테이블 형식을 따라야 한다는 것을 명심하자.

 

1. 기수(boarder)와 학생(student) 테이블을 합쳐서 조련사(trainer)의 전체 훈련 일정을 만들어 보자.

Create view training_schedule as 
select a.first_name as trainer, b.first_name as student, c.barn_name as horse, d.lesson_time 
from staff a, boarder b, horse c, lesson_schedule d, job e 
where c.id = d.horse_id and b.id = d.boarder_id and a.id = e.staff_id and e.id = d.job_id

Union 
select f.first_name as trainer, g.first_name as student, h.barn_name as horse, i.lesson_time 
from staff f, student g, horse h, lesson_schedule i, job j 
where h.id = i.horse_id and g.id = i.student_id and f.id = j.staff_id and j.id = i.job_id;

기수와 학생이 모두 포함된 훈련 일정

2. 말의 위치정보를 호실(stall)과 목초지(pasture)를 포함해 보여주자.

Create view horse_information as 
select a.barn_name as horse, b.stall_number as location, c.`type` as bedding 
from horse a, stall b, bedding c, horse_barn d 
where a.id = d.horse_id and b.stall_id = d.stall_id and c.id = d.bedding_id

Union 
select e.barn_name as horse, f.pasture_number as location, 'NONE' as bedding 
from horse e, pasture f, horse_barn g 
where e.id = g.horse_id and f.pasture_id = g.pasture_id;

말에 대한 정보를 모두 확인할 수 있다.

 

그룹으로 묶어서 계산하자(Group By)

Group By를 이용하면 원하는 그룹으로 나눠서 필요한 연산을 수행할 수 있다. 참고로, Group By를 사용하는 방법은 중복된 명칭의 행들을 하나로 합쳐서 계산하고 싶은 열을 선택해 지정해주면 된다.

 

1. sum함수를 이용해 각 학생의 전체 수업료가 얼마인지 계산해보자 

Select student, sum(fee) as total_fee from student_bill group by student;

Lauren의 수업료가 합쳐진 것을 확인할 수 있다

 

다양한 연산 활용 방법

select문을 사용할 때, 다양한 연산을 통해 원하는 값을 도출해 낼 수 있다. 사용할 수 있는 연산으로는 사칙연산과 조건별 연산이 모두 가능하다. 조건문의 예시로, case when 'a' then '1' when 'b' then '2' else '3' end와 같은 방식으로 연산이 가능하다. 괄호를 잘 사용하면 복잡한 연산도 모두 가능하다.

 

1. 복잡한 연산을 통해 각 말에 들어가는 비용들을 연산해보자

Create view boarder_bill as 
select a.barn_name as horse, ((b.Cost / b.Weight) * (c.hay_amount * 30)) as hay_charge, d.base_price as board_fee, 
((case when c.grain_amount = 'Quarter' then 0.25 when c.grain_amount = 'Half' then 0.50 else 1.0 end) * (e.cost)) as grain_charge, f.cost as bedding_charge 
from horse a, hay b, grain_schedule c, stall d, grain e, bedding f, horse_barn g 
where b.id = c.hay_id and c.horse_id = a.id and g.horse_id = a.id and g.stall_id = d.stall_id and e.id = c.grain_id and f.id = g.bedding_id

Union 
select h.barn_name as horse, ((i.Cost / i.Weight) * (j.hay_amount * 30)) as hay_charge, k.base_price as board_fee, 
((case when j.grain_amount = 'Quarter' then 0.25 when j.grain_amount = 'Half' then 0.50 else 1.0 end) * (l.cost)) as grain_charge, 0 as bedding_charge 
from horse h, hay i, grain_schedule j, pasture k, grain l, bedding m, horse_barn g 
where i.id = j.hay_id and j.horse_id = h.id and g.horse_id = h.id and g.pasture_id = k.pasture_id and l.id = j.grain_id;

각 말의 건초 비용, 승마 비용, 곡물 비용, 침구 비용을 모두 확인할 수 있다.

 

2. '+' 기호를 이용한 연산이 가능하다

select horse, (hay_charge + board_fee + grain_charge + bedding_charge) as total_bill from boarder_bill;

각 말에 들어가는 총 비용을 계산하였다

마지막 정리

모두 잘 따라왔다면, 마지막으로 전과정을 한번에 진행해보도록 하겠다. 

이번에는 각 직원들이 특정 기간 동안 일한 내역을 담은 테이블과 해당 테이블을 정리해 급여표를 뷰로 만들어 보겠다.

 

1. 직원별 활동 내역(payroll) 테이블을 만들어보자

Create table payroll (
staff_id int not null, 
job_id int not null, 
hours_worked decimal (4,2) not null, 
period_start date not null, 
period_end date not null, 
primary key (staff_id, job_id), 
foreign key (staff_id) references staff (id), 
foreign key (job_id) references job (id)
);

Alter table payroll drop foreign key payroll_ibfk_1;
Alter table payroll drop foreign key payroll_ibfk_2;

Alter table payroll add foreign key(staff_id) references staff(ID) on delete cascade on update cascade;
Alter table payroll add foreign key(job_id) references job(ID) on delete cascade on update cascade;

2. 테이블 안에 각 직원이 어떤 일을 얼마나 했는지 입력하자

시작일자 : 12/4/2018 마감일자 : 12/10/2018 a.     Martin이 20시간 동안 Maintenance work를 일함 b.     Martin이 12시간 동안 Barn work를 일함 c.      Karin이 20시간 동안 Barn work를 일함 d.     Vanessa이 12시간 동안 Groom work를 일함 e.      Vanessa이 15시간 동안 Trainer로 $50시급을 받고 일함 f.      Bruce이 10시간 동안 Trainer로 $50시급을 받고 일함 g.     Bruce이 20시간 동안 Trainer로 $100시급을 받고 일함

Insert into payroll (staff_id, job_id, hours_worked, period_start, period_end) values (1,1,20.00, “2018-12-04”, “2018-12-10”);

Insert into payroll (staff_id, job_id, hours_worked, period_start, period_end) values (1,2,12.00, “2018-12-04”, “2018-12-10”);

...

Insert into payroll (staff_id, job_id, hours_worked, period_start, period_end) values (4,4,10.00, “2018-12-04”, “2018-12-10”);

Insert into payroll (staff_id, job_id, hours_worked, period_start, period_end) values (4,5,20.00, “2018-12-04”, “2018-12-10”);

직원별 활동 내역과 급여를 확인할 수 있다.

3. 정산서(paycheck)를 만들어 보자

Create view paycheck as 
select a.first_name, a.last_name, sum(b.hourly_rate * c.hours_worked) as total_pay 
from staff a, job b, payroll c 
where c.job_id = b.id and c.staff_id = a.id group by last_name order by last_name;

각 직원에게 지급해야 되는 급여를 확인할 수 있다.

 

마무리하며

이로써 모든 과정을 마무리하였다. 

 

만약 여러분이 이 과정을 모두 잘 따라서 실행해봤다면, 여러분은 말 농장을 운영할 수 있을 것이다!

(물론 말 농장이 먼저 필요하겠지만말이다..하하)

 

포스트를 작성하면서 크게 어렵거나 복잡한 부분은 없었을거라 생각한다. 또한, 지금까지 진행한 부분 외에도 조인과 인덱스 기능을 활용한다면 더욱 효율적인 DB를 구축할 수 있을 거라 본다.

 

하지만, 이번 과정을 통해서 어떻게 하나의 사업을 DB화 할 수 있는지를 조금이나마 배울 수 있었을거라 생각한다. 그리고 꼭 복잡한 기능이 없더라도, 보기 쉽고 중복성을 최소화하면서 무결성을 만족한다면 충분히 좋은 DB라고 생각한다.

 

말 농장 DB는 여기까지이며, 다음에는 조인 기능과 인덱스 기능을 다뤄보도록 하겠다.