HorseFarm(2) - 데이터의 연결

데이터의 연결(Foreign Key)

이번 글에서는 데이터를 서로 어떻게 연결할 수 있는지 다뤄볼 예정이다.

어떤 사육사가 어떤 말을 보유하고 있을까?

앞서 만든 9개의 테이블이 각 객체를 나타냈다면, 이번에는 각 객체를 참조하여 관계를 나타내는 테이블을 만들어볼 것이다. 다른 테이블을 참조하기 위해서는 foreign key(외래키)를 이용해야 된다.

 

1. horse_barn 테이블을 만들어서 horse(말)과 stall(호실)과 pasture(목초지)의 관계를 정의하자.

create table horse_barn (
horse_id int not null, 
stall_id int, 
pasture_id int, 
primary key (horse_id), 
foreign key (horse_id) references horse (ID), 
foreign key (stall_id) references stall (stall_id), 
foreign key (pasture_id) references pasture (pasture_id)
);

2. grain_schedule 테이블을 만들어서 horse(말)과 grain(곡물)의 관계를 정의하자.

create table grain_schedule (
horse_id int not null, 
grain_id int not null, 
grain_amount enum('Full', 'Half', 'Quarter') not null, 
hay_id int not null, 
hay_amount int not null, 
primary key (horse_id), 
foreign key (horse_id) references horse (ID), 
foreign key (grain_id) references grain (ID)
);

3. lesson_schedule 테이블을 만들어서 horse(말)과 student(학생)과 boarder(기수)의 관계를 정의하자.

create table lesson_schedule (
horse_id int not null, 
student_id int, 
boarder_id int, 
lesson_time timestamp not null, 
primary key (horse_id, lesson_time), 
foreign key (horse_id) references horse (ID), 
foreign key (student_id) references student (ID), 
foreign key (boarder_id) references boarder(ID)
);

4. boarder_horse 테이블을 만들어서 boarder(기수)와 horse(말)의 관계를 정의하자.

create table boarder_horse (
horse_id int not null, 
boarder_id int, 
primary key (horse_id), 
foreign key (horse_id) references horse (ID), 
foreign key (boarder_id) references boarder (ID)
);

 

관계 정보 입력(Using ID)

관계를 정의할 테이블을 모두 만들었다면 이제 관계에 대한 정보를 입력하자.

 

1. 어떤 기수가 어떤 말을 보유하고 있는지 ID를 이용해 정의해보자.

a. 기수 Karin의 말들:         i.     Blaze         ii.     Cruise b.  기수 Bruce의 말들:         i.     Timothy         ii.     Jet         iii.     Zico c. 기수 Vanessa의 말 Sag d. 기수 Maria의 말 Johnny e. 기수 Nancy의 말 Thunder f. 기수 Julia의 말 Allie g. 기수 Mystery의 말 Carera h. 기수 Jennifer의 말 Indy i. 기수 Alix의 말 Kamen

Insert into boarder_horse (horse_id, boarder_id) values (1,1);

Insert into boarder_horse (horse_id, boarder_id) values (2,1);

...

Insert into boarder_horse (horse_id, boarder_id) values (5,8);

Insert into boarder_horse (horse_id, boarder_id) values (6,9);

 

정보가 올바르게 들어갔는지 확인하고 싶다면 아래와 같은 쿼리를 입력하면 관계를 확인할 수 있다.

select b.first_name, h.barn_name from boarder b, horse h, boarder_horse bh
where bh.boarder_id = b.id
and bh.horse_id = h.id;

사육사와 말의 관계

 

2. 어떤 말이 어떤 곡물과 건초를 얼마나 먹을지 ID를 이용해 정의해보자.

a. Blaze, ProForce Fuel, Quarter, Alfalfa, 10 b. Cruise, ProForce Fuel, Quarter, Alfalfa, 10 c. Sag, ProForce Senior, Quarter, Peanut, 10 d. Zico, ProForce Fuel, Half, Orchard,10 e. Indy, ProForce XTN, Full, Timothy,15 f. Kamen, ProForce Fuel, Full, Alfalfa,15 g. Thunder, ProForce Fuel, Half, Timothy,10 h. Allie, ProForce Senior, Quarter, Coastal,15 i. Jet, ProForce Fuel, Half, Orchard,10 j. Timothy, ProForce Fiber, Quarter, Alfalfa,8 k. Johnny, ProForce Fuel, Half, Alfalfa,12 l. Carera, ProForce XTN, Full, Alfalfa,13

Insert into grain_schedule (horse_id, grain_id, grain_amount, hay_id, hay_amount) values (1, 1, 'Quarter ', 4, 10);

Insert into grain_schedule (horse_id, grain_id, grain_amount, hay_id, hay_amount) values (2, 1, 'Quarter ', 4, 10);

...

Insert into grain_schedule (horse_id, grain_id, grain_amount, hay_id, hay_amount) values (11, 1, 'Half ', 4, 12);

Insert into grain_schedule (horse_id, grain_id, grain_amount, hay_id, hay_amount) values (12, 2, 'Full ', 4, 13);

 

마찬가지로 각 말의 식사와 관련된 관계를 확인하고 싶다면 아래와 같은 쿼리를 이용하면 된다.

select a.barn_name, b.product, grain_amount, d.`type`, hay_amount 
from horse a, grain b, hay d, grain_schedule abd 
where abd.horse_id = a.id 
and abd.grain_id = b.id 
and abd.grain_amount = abd.grain_amount 
and abd.hay_id = d.id 
and abd.hay_amount = abd.hay_amount;

각 말이 먹는 곡물과 건초의 관계

 

전체 과정을 한번에 진행한다면, 아래와 같이 테이블을 만들어 관계를 바로 입력하면 된다. Job 테이블을 만들어 각 직원의 시급과 역할을 정의하여 staff 테이블의 직원과 연결해보자.

Create table job (
id int not null auto_increment, 
staff_id int, 
hourly_rate float (5,2) not null, 
position enum ('Trainer', 'Groom', 'Barn Staff', 'Maintenance'), 
primary key (id), 
foreign key (staff_id) references staff (ID)
);

enum형식을 이용해 4가지의 고정된 역할을 정의해주자

a.     Staff 맴버 Martin, $20, Maintenance b.     Staff 맴버 Karin, $15, Barn (or Barn Staff) c.      Staff 맴버 Vanessa, $25, Groom d.     Staff 맴버 Vanessa, $50, Trainer e.     Staff 맴버 Bruce, $100, Trainer

Insert into job (staff_id, hourly_rate, position) values (1, 20.00, 'Maintenance');

...

Insert into job (staff_id, hourly_rate, position) values (4, 100.00, 'Trainer');

 

테이블 수정(Alter Table)

만약 중간에 누락시킨 관계가 있다면 alter를 이용해 열과 속성정보를 추가하면 된다. 예시로, 앞서 만든 horse_barn 테이블에 bedding_id를 참조할 수 있게 변경해보겠다.

Alter table horse_barn add column bedding_id int;

Alter table horse_barn add foreign key (bedding_id) references bedding (ID);

bedding_id가 추가되었다.

변경을 완료하였다면, 마찬가지로 각 말이 어디 있는지와 어떤 침구를 이용하는지 입력해보자.

a.     Blaze is in stall 5, bedding is shavings b.     Cruise is in stall 2, bedding is shavings c.      Sag is in stall 1, bedding is shavings d.     Zico is in stall 4, bedding is shavings e.      Indy is in stall 7, bedding is shavings f.      Kamen is in stall 9, bedding is shavings g.     Thunder is in stall 10, bedding is shavings h.     Allie is in pasture 2, no bedding i.       Carera is in pasture 1, no bedding j.      Jet is in stall 3, bedding is shavings k.     Timothy is in stall 8, bedding is shavings l.      Johnny is in stall 6, bedding is shavings

Insert into horse_barn (horse_id, stall_id, bedding_id) values (1, 5, 2);

Insert into horse_barn (horse_id, stall_id, bedding_id) values (2, 2, 2);

...

Insert into horse_barn (horse_id, stall_id, bedding_id) values (10, 8, 2);

Insert into horse_barn (horse_id, stall_id, bedding_id) values (11, 6, 2);

 

종속 관계 설정(Cascade)

이번에는 lesson_schedule에 job_id를 추가해 job 테이블과 연결해주도록 하겠다.

 

여기서 다른 점은 참조 무결성을 만족시키기 위해 cascade를 사용하여 서로 종속 관계를 유지하게 한 것이다. 쉽게 말하면, job 테이블에 있는 ID(PK)가 변경되거나 삭제되면 lesson_schedule의 job_id의 값도 함께 변경되거나 삭제된다는 것이다.

Alter table lesson_schedule 
add job_id int NULL, 
add foreign key(job_id) references job(ID) on update cascade on delete cascade;

 

참조 무결성을 만족시키지 않는다면, 데이터베이스 내에서 많은 오류가 발생할 수 있다. 없는 키를 참조할 수도 있고, 변경된 키를 참조하여 다른 값을 나타낼 수도 있다. 그렇기에 다른 테이블들의 외래키(FK)들도 cascade조건을 추가해 참조 무결성을 만족시킬 수 있도록 하자.

 

job 테이블이 참조 무결성을 만족할 수 있도록 정의

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

horse_barn 테이블이 참조 무결성을 만족할 수 있도록 정의

Alter table horse_barn add foreign key(horse_id) references horse(ID) on update cascade on delete cascade;

Alter table horse_barn add foreign key(stall_id) references stall(stall_id) on update cascade on delete cascade;

Alter table horse_barn add foreign key(pasture_id) references pasture(pasture_id) on update cascade on delete cascade;

Alter table horse_barn add foreign key(bedding_id) references bedding(ID) on update cascade on delete cascade;

boarder_horse 테이블이 참조 무결성을 만족할 수 있도록 정의

Alter table boarder_horse add foreign key(horse_id) references horse(ID) on update cascade on delete cascade;

Alter table boarder_horse add foreign key(boarder_id) references boarder(ID) on update cascade on delete cascade;

grain_schedule 테이블이 참조 무결성을 만족할 수 있도록 정의

Alter table grain_schedule add foreign key(horse_id) references horse(ID) on update cascade on delete cascade;

Alter table grain_schedule add foreign key(grain_id) references grain(ID) on update cascade on delete cascade;

lesson_schedule 테이블이 참조 무결성을 만족할 수 있도록 정의

Alter table lesson_schedule add foreign key(horse_id) references horse(ID) on update cascade on delete cascade;

Alter table lesson_schedule add foreign key(student_id) references student(ID) on update cascade on delete cascade;

Alter table lesson_schedule add foreign key(boarder_id) references boarder(ID) on update cascade on delete cascade;

 

중간에 추가가 안되거나 기존 외래키가 존재한다면 'Alter table (테이블명) drop foreign key (테이블명)_ibfk_(순번)' 쿼리를 이용해 기존 외래키를 제거해주고 다시 새롭게 정의해주면 된다. (여기서 ibfk는 innoDB foreign key를 의미하는 default명칭이다.)

 

최종적으로 정의된 lesson_schedule 테이블의 속성값

모두 변경했다면, 남은 lesson_schedule에 수업 일정 데이터를 넣어주도록 하자.

a.        기수 Karin은 말 Blaze와 조련사 Vanessa와 November 7, 2018 at 10 AM에 수업이 있다. b.       기수 Karin 은 말 Cruise 조련사 Vanessa on November 7, 2018 at 11 AM에 수업이 있다. c.        기수 Maria 은 말 Johnny 조련사 Bruce on November 7, 2018 at 9 AM에 수업이 있다. d.       기수 Nancy 은 말 Thunder 조련사 Vanessa on November 7, 2018 at 8 AM에 수업이 있다. e.        기수 Julia 은 말 Allie 조련사 Bruce on November 8, 2018 at 9 AM에 수업이 있다. f.         기수 Alix 은 말 Kamen 조련사 Bruce on November 8, 2018 at 10 AM에 수업이 있다. g.        학생 Lorissa 은 말 Sag 조련사 Vanessa on November 8, 2018 at 11 AM에 수업이 있다. h.       학생 Lauren 은 말 Sag 조련사 Vanessa on November 9, 2018 at 10 AM에 수업이 있다. i.         학생 Lauren 은 말 Zico 조련사 Bruce on November 9, 2018 at 11 AM에 수업이 있다. j.         학생 Rebecca 은 말 Kamen 조련사 Bruce on November 10, 2018 at 10 AM에 수업이 있다. k.       학생 Catherine 은 말 Thunder 조련사 Vanessa on November 10, 2018 at 11 AM에 수업이 있다.

Insert into lesson_schedule(horse_id, student_id, boarder_id, lesson_time, job_id) values(1,NULL,1, timestamp(“2018-11-07”, “10:00:00”),4);

Insert into lesson_schedule(horse_id, student_id, boarder_id, lesson_time, job_id) values(2,NULL,1, timestamp(“2018-11-07”, “11:00:00”),4);

...

Insert into lesson_schedule(horse_id, student_id, boarder_id, lesson_time, job_id) values(6,3,NULL, timestamp(“2018-11-10”, “10:00:00”),5);

Insert into lesson_schedule(horse_id, student_id, boarder_id, lesson_time, job_id) values(7,8,NULL, timestamp(“2018-11-10”, “11:00:00”),4);

수업 일정이 알맞게 lesson_schedule 테이블에 입력되었다

 

마무리하며

이번 포스트에서는 어떻게 테이블 사이의 관계를 형성하는지에 대해서 다루어 보았다.

다른 테이블의 주 키(PK)를 참조하는 외래키(FK)를 활용해서 관계를 연결하다는 것을 배울 수 있었을 것이다.

 

이어서, 다음 포스트에서는 만들어진 테이블을 활용해 View를 생성하는 방법을 다루어 보겠다.