[DB] 자료테이블을 통해 테이블 구성하기(MySQL 이용)

MySQL을 사용하여 DB생성하기 

테이블만 보고!

  아래에 3개의 테이블이 있다. 이 테이블들을 사용하여 Database를 한번 만들어 볼 것이다. 사용자 컴퓨터에는 MySQL이 깔려있다고 가정한다. 현재로서 윈도우를 사용하는 대부분의 사용자가 MySQL을 가장 쉽게 까는 법은 apmsetup.com이라는 사이트를 통해 까는 것이다. 현재 자신의 컴퓨터를 웹서버로 만드는 것이며, APM은 Apache, PHP, MySQL의 첫 글자를 딴 것이다. 윈도우가 없다면 리눅스기반 운영체제에서도 쉽게 깔리는 편이다.

데이터 분석

  전체 테이블을 보면 한 선박회사의 DB라고 추측이 된다. sailors, boats, reserves가 나와있는 것에서 추론을 하게되면 선원, 보트, 예약이 있으므로 선원과 보트정보, 더 나아가 보트가 누구에게 예약이 되어있는지까지 함께 작성이 된 DB이다.

sailors(sid, Sname, rating, age, sex)

  선원들의 정보를 가지고 있는 테이블이며, sid는 identification을 위한 번호, Sname은 선원의 이름, rating은 선원의 선호도 순위, age는 나이, sex는 성별 이렇게 정보가 담겨진 것으로 추측이 된다.

boats(bid, bname, color, price)

  보트의 정보를 가지고 있는 테이블이며, bid는 identification을 위한 번호, bname은 보트이름, color는 보트의 색상, price는 보트를 대여할 때? 혹은 살 때의 가격의 정보를 저장하고 있는 듯 하다. 단위를 몰라서 진짜 보트의 가격정보인지 대여가격 정보인지는 잘 모르겠다.

reserves(sid, bid, day)

  sid는 sailors 테이블의 sid를 Foreign Key로 가지며, bid는 boats 테이블의 bid를 Foreign Key로 갖는다. 또한 day는 빌리는 날짜를 나타내는 요소일 것이다. sailors와 boat 테이블과는 달리 reserves는 하나의 attribute를 가지고는 Primary Key를 지정할 수 없다는 점이 특징이 되겠다. 적어도 bid와 day를 포함해야하며, 나중에 하루에 두 번 보트를 대여해줄 것까지 대비한 확장성을 생각하면 sid까지 Primary Key로 지정하는 것이 올바른 것이다.

테이블 만들기

sailors

CREATE TABLE sailors(

sid INT,

Sname VARCHAR(20),

rating TINYINT,

age DECIMAL(3,1),

sex VARCHAR(1),

PRIMARY KEY(sid)

); 

Type을 저렇게 정한 이유에 대해 설명을 해보면

1. sid는 PRIMARY KEY로 사용할 것이기 때문에 int로 지정(PK는 주로 int로 사용한다)

2. Sname은 사람 이름이 보통은 20자를 넘지 않을 것이기 때문에 VARCHAR(20)으로 지정

3. rating의 경우 숫자가 크게 나오지 않는 것 같아 TINYINT(0~255)으로 설정. 웬만큼 규모가 크지 않는 이상 이정도로도 충분할 것으로 보인다.

4. age의 경우 DECIMAL타입으로 지정을 하였는데, (3,1)의 뜻은 총 자리수가 3자리이고 그중 소수점이 1자리라는 뜻이다. 보통 선원이 100세를 넘지는 않을 것이므로 Decimal(3,1)로 충분할 것이라고 생각하고 이렇게 지정을 하였다. 

5. 성별 sex의 Type은 간단하게 M, F로 표현할 것이기 때문에 1자리의 VARCHAR로 지정. 사실 저장공간을 더 절약하려면 BOOLEAN타입으로 하면 되지만, 나중에 C++같은 프로그램을 얹는다고 생각하면 VARCHAR로 하는 것이 더 간단하다. (BOOLEAN으로 하면 TRUE가 남자인지 여자인지 명시할 필요성이 생긴다)

boats

CREATE TABLE boats(

bid INT,

bname VARCHAR(20),

color VARCHAR(10),

price INT,

PRIMARY KEY(bid)

);

Type에 대한 설명을 마찬가지로 해보면,

1. bid는 PRIMARY KEY로 사용할 것이기 때문에 int로 설정

2. bname은 보트 이름이고 20자를 보통 넘지 않을 것으로 생각하여 VARCHAR(20)으로 설정

3. color 역시 10자를 거의 넘지 않을 것이라 생각하고 VARCHAR(10)으로 설정

4. Price는 여유롭게 int로 설정

reserves

 

CREATE TABLE reserves(

sid INT,

bid INT,

day DATE,

PRIMARY KEY(sid, bid, day),

FOREIGN KEY(sid) REFERENCES sailors(sid),

FOREIGN KEY(bid) REFERENCES boats(bid)

);

  앞서 구상했던 것처럼 sid와 bid는 각각 sailors 테이블과 boats의 side와 bid를 참조하므로, FOREIGN KEY 설정을 해주어야 한다. 또한 PRIMARY KEY로는 현재 테이블의 내용만으로는 같은 날에 같은 보트를 빌릴 수 없는 것으로 보이므로 bid와 day만으로 지정하여도 되지만, 나중에 시간단위로 나눠서 같은 날에 빌릴 수 있게 할 수도 있으므로 sid, bid, day를 함께 묶어서 PRIMARY KEY로 지정하였다. 

 

Type에 대한 설명을 해보면,

1. sid와 bid는 각각 sailors 테이블과 boats 테이블을 참조하므로, 여기서의 데이터 Type과 동일해야 한다. 따라서 int로 지정하였다.

2. day는 날짜이므로 DATE 타입으로 지정

데이터 삽입

sailors

INSERT INTO sailors VALUES(25, 'NAME', 5, 25.5 'M')

  SQL은 Set 기반 언어이므로 입력한 순서와 관계없이 출력이 되는 모습을 볼 수 있다.

boats

INSERT INTO boats VALUES(101, 'NAME', 'COLOR', 20000)

reserves

INSERT INTO reserves VALUES(10, 102, '2014-10-30')

  이 테이블 자체로는 사람이 알아보기 힘들고 결과값을 가공해서 보여주기 위해서는 다른 테이블과의 추가적인 JOIN과 PROJECTION이 필요할 것이다.