15.2. Lesson: 데이터 모델 시행

이제 모든 이론을 살펴봤으니, 새 데이터베이스를 생성해봅시다. 이 강의에서 생성할 데이터베이스를 이번 모듈 내내 예제로 사용할 것입니다.

이 강의의 목표: 필수 소프트웨어를 설치하고 예제 데이터베이스를 시행하는 데 사용하기.

15.2.1. PostgreSQL 설치

주석

이 교재가 다루는 범위에서는 벗어나지만, 맥 사용자는 홈브루 를 사용해서 PostgreSQL을 설치할 수 있습니다. 윈도우 사용자는 GUI 인스톨러를 여기 에서 다운로드할 수 있습니다. 이 강의에서는 사용자가 우분투에서 QGIS를 실행하고 있다고 가정한다는 사실을 알아두십시오.

우분투에서 다음과 같이 설치하면,

sudo apt-get install postgresql-9.1

다음과 같은 메시지를 받게 될 것입니다.

[sudo] password for qgis:
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following extra packages will be installed:
postgresql-client-9.1 postgresql-client-common postgresql-common
Suggested packages:
oidentd ident-server postgresql-doc-9.1
The following NEW packages will be installed:
postgresql-9.1 postgresql-client-9.1 postgresql-client-common postgresql-common
0 upgraded, 4 newly installed, 0 to remove and 5 not upgraded.
Need to get 5,012kB of archives.
After this operation, 19.0MB of additional disk space will be used.
Do you want to continue [Y/n]?

Y 를 입력하고 Enter 키를 누른 다음, 다운로드 및 설치가 끝날 때까지 기다리십시오.

15.2.2. 도움말

PostgreSQL는 매우 훌륭한 온라인 문서를 갖추고 있습니다.

15.2.3. 데이터베이스 사용자 생성

우분투의 경우,

설치가 완료된 후 다음 명령어를 실행해서 PostgreSQL의 사용자가 된 다음, 새 데이터베이스 사용자를 생성하십시오.

sudo su - postgres

시스템이 비밀번호를 요구하면 사용자의 우분투 비밀번호를 입력하십시오. (사용자에게 sudo 권한이 있어야 합니다)

그 다음 PostgreSQL 사용자의 배시 프롬프트에서 데이터베이스 사용자를 생성하십시오. 이때 사용자명을 우분투 사용자명과 동일하게 하십시오. 해당 사용자로 로그인 시 PostgreSQL이 자동적으로 인증할 것이기 때문에 프로그램 사용이 훨씬 쉬워집니다.

createuser -d -E -i -l -P -r -s qgis

시스템이 비밀번호를 요구하면 입력합니다. 시스템 로그인 비밀번호와는 달라야 합니다.

이 옵션들은 무슨 뜻일까요?

-d, --createdb     role can create new databases
-E, --encrypted    encrypt stored password
-i, --inherit      role inherits privileges of roles it is a member of (default)
-l, --login        role can login (default)
-P, --pwprompt     assign a password to new role
-r, --createrole   role can create new roles
-s, --superuser    role will be superuser

이제 postgres 유저의 bash 쉘 환경을 다음처럼 타이핑 해서 떠납니다:

exit

15.2.4. 새 계정 확인

psql -l

다음과 같은 화면을 보게 될 것입니다.

Name      |  Owner   | Encoding | Collation  |   Ctype    |
----------+----------+----------+------------+------------+
postgres  | postgres | UTF8     | en_ZA.utf8 | en_ZA.utf8 |
template0 | postgres | UTF8     | en_ZA.utf8 | en_ZA.utf8 |
template1 | postgres | UTF8     | en_ZA.utf8 | en_ZA.utf8 |
(3 rows)

q 를 입력해서 벗어나십시오.

15.2.5. 데이터베이스 생성

새 데이터베이스를 생성하려면 createdb 명령어를 사용합니다. 다음과 같이 배시 셸 프롬프트에서 실행해야 합니다.

createdb address -O qgis

다음 명령어를 통해 새 데이터베이스의 존재를 확인할 수 있습니다.

psql -l

다음과 같은 내용을 반환할 것입니다.

Name      |  Owner   | Encoding | Collation  |   Ctype    |   Access privileges
----------+----------+----------+------------+------------+-----------------------
address   | qgis     | UTF8     | en_ZA.utf8 | en_ZA.utf8 |
postgres  | postgres | UTF8     | en_ZA.utf8 | en_ZA.utf8 |
template0 | postgres | UTF8     | en_ZA.utf8 | en_ZA.utf8 | =c/postgres: postgres=CTc/postgres
template1 | postgres | UTF8     | en_ZA.utf8 | en_ZA.utf8 | =c/postgres: postgres=CTc/postgres
(4 rows)

q 를 입력해서 벗어나십시오.

15.2.6. 데이터베이스 셸 세션 시작

다음과 같이 손쉽게 사용자 데이터베이스에 접속할 수 있습니다.

psql address

psql 데이터베이스 셸에서 나오려면 다음과 같이 입력하십시오.

\q

셸 사용법에 대한 도움말을 보려면 다음과 같이 입력하십시오.

\?

SQL 명령어에 대한 도움말을 보려면 다음과 같이 입력하십시오.

\help

특정 명령어에 대한 도움말을 보려면 (예를 들어) 다음과 같이 입력합니다.

\help create table

다음 링크의 `온라인 Psql 참조표<http://www.postgresonline.com/downloads/special_feature/postgresql90_cheatsheet_A4.pdf>`_ 를 참고.

15.2.7. SQL로 테이블 생성

테이블을 만들어보겠습니다! 이전 강의에서 만들었던 ER 도표를 지침으로 삼을 것입니다. 먼저 주소 DB에 접속하십시오.

psql address

그 다음 streets 테이블을 생성합니다.

create table streets (id serial not null primary key, name varchar(50));

serial 과 varchar 는 **데이터형**입니다. serial 은 PostgreSQL이 새 레코드가 추가될 때마다 자동적으로 id 를 정수 시퀀스(자동 숫자)로 채우도록 지정합니다. varchar(50) 은 PostgreSQL이 길이 50글자인 캐릭터 필드를 생성하도록 합니다.

명령어가 ; 문자로 끝난다는 것을 알아차리셨을 겁니다. 모든 SQL 명령어가 이렇게 끝나야 합니다. 엔터를 입력하면, psql이 다음과 같은 내용을 보고할 것입니다.

NOTICE:  CREATE TABLE will create implicit sequence "streets_id_seq" for
         serial column "streets.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "streets_pkey"
         for table "streets"
CREATE TABLE

사용자의 테이블을 streets.id 를 이용하는 기본 키 streets_pkey 와 함께 성공적으로 생성했다는 뜻입니다.

주석 : ; 을 입력하지 않고 엔터를 누르면 address-# 과 같은 프롬프트로 들어가게 됩니다. PostgreSQL이 연속되는 명령어를 기다리고 있기 때문입니다. 명령어를 실행하려면 ; 을 입력하십시오.

테이블 스키마를 살펴보려면 다음처럼 하면 됩니다.

\d streets

그러면 다음과 같은 내용을 반환할 것입니다.

Table "public.streets"
 Column |         Type          |            Modifiers
--------+-----------------------+--------------------------------------
 id     | integer               | not null default
        |                       | nextval('streets_id_seq'::regclass)
 name   | character varying(50) |
Indexes:
  "streets_pkey" PRIMARY KEY, btree (id)

테이블의 내용을 살펴보려면 다음처럼 하면 됩니다.

select * from streets;

그러면 다음과 같은 내용을 반환할 것입니다.

id | name
---+------
(0 rows)

사용자 테이블이 현재 비어 있다는 것을 알 수 있습니다.

15.2.7.1. Try Yourself moderate 중급

앞에서 배운 내용대로 people이라는 테이블을 만드십시오.

phone number , home address , name 등의 항목들을 추가하십시오. (예시한 항목명들이 모두 유효하지는 않습니다. 유효한 명칭으로 변경하십시오.) 앞에서와 마찬가지로 동일한 데이터 유형의 ID 열을 테이블에 추가해야 합니다.

결과 확인

15.2.8. SQL로 키 생성

앞 단계까지의 문제점은 데이터베이스가 people과 streets가 논리적 관계를 가지고 있다는 사실을 모른다는 것입니다. 이 관계를 표현하려면, streets 테이블의 기본 키를 가리키는 외래 키를 정의해야만 합니다.

../../../_images/er-people-streets.png

두 가지 방법이 있습니다.

  • 테이블 생성 후 키 추가

  • 테이블 생성 시 키 정의

이미 테이블을 생성했으므로, 첫 번째 방법을 써봅시다.

alter table people
  add constraint people_streets_fk foreign key (street_id) references streets(id);

이렇게 하면 people 테이블에 street_id 필드가 streets 테이블의 유효한 도로 id 와 일치해야만 한다고 알려주게 됩니다.

제약 조건을 생성하는 좀 더 일반적인 방법은 테이블 생성 시 다음과 같이 하는 것입니다.

create table people (id serial not null primary key,
                     name varchar(50),
                     house_no int not null,
                     street_id int references streets(id) not null,
                     phone_no varchar null);

  \d people

제약 조건을 추가하면, 테이블 스키마가 다음과 같이 변합니다.

Table "public.people"

  Column   |         Type          |            Modifiers
-----------+-----------------------+---------------------------------
 id        | integer               | not null default
           |                       | nextval('people_id_seq'::regclass)
 name      | character varying(50) |
 house_no  | integer               | not null
 street_id | integer               | not null
 phone_no  | character varying     |
Indexes:
  "people_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
  "people_streets_fk" FOREIGN KEY (id) REFERENCES streets(id)

15.2.9. SQL로 인덱스 생성

우리는 인명 검색이 눈 깜짝할 사이에 이뤄지기를 바랍니다. 이렇게 하려면 다음과 같이 people 테이블의 name 열에 인덱스를 생성하면 됩니다.

create index people_name_idx on people(name);

\d people

테이블 스키마가 다음과 같이 변경됩니다.

Table "public.people"

  Column   |         Type          |                      Modifiers
-----------+-----------------------+-----------------------------------
 id        | integer               | not null default nextval
           |                       | ('people_id_seq'::regclass)
 name      | character varying(50) |
 house_no  | integer               | not null
 street_id | integer               | not null
 phone_no  | character varying     |
Indexes:
 "people_pkey" PRIMARY KEY, btree (id)
 "people_name_idx" btree (name)    <-- new index added!
Foreign-key constraints:
 "people_streets_fk" FOREIGN KEY (id) REFERENCES streets(id)

15.2.10. SQL로 테이블 드롭

테이블을 없애버리고 싶은 경우 drop 명령어를 쓸 수 있습니다.

drop table streets;

주석

현재 예제에서 이 명령어는 동작하지 않습니다. 어째서일까요? 이런 이유 때문입니다.

drop table people;

주석

실제로 명령어를 입력해서 people 테이블을 드롭했다면, 다음 예제에 필요하므로 지금 다시 생성하도록 하십시오.

15.2.11. pgAdmin III에 대해

여러분에게 psql 프롬프트에서 SQL 명령을 보여주는 이유는 데이터베이스에 대해 배우는 데 아주 유용한 방법이기 때문입니다. 하지만 여기서 여러분이 배운 작업의 대부분을 더 빠르고 쉽게 할 수 있는 방법이 있습니다. pgAdmin III를 설치하면 GUI 환경에서 ‘포인트 & 클릭’만으로 테이블의 생성, 삭제, 수정 등을 할 수 있습니다.

우분투에서 다음과 같이 설치할 수 있습니다.

sudo apt-get install pgadmin3

다른 모듈에서 pgAdmin III에 대해 더 자세히 배우게 될 것입니다.

15.2.12. In Conclusion

이제 완전히 처음부터 새 데이터베이스를 생성하는 방법을 배웠습니다.

15.2.13. What’s Next?

다음 강의에서 DBMS를 통해 새 데이터를 추가하는 방법을 배울 것입니다.