16.2. Lesson: 데이터 모델 시행¶

16.2.1. PostgreSQL 설치¶

우분투의 경우,

sudo apt-get install postgresql-9.1

You should get a message like this:

[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 키를 누른 다음, 다운로드 및 설치가 끝날 때까지 기다리십시오.

16.2.2. 도움말¶

PostgreSQL has very good online documentation.

16.2.3. 데이터베이스 사용자 생성¶

우분투의 경우,

After the installation is complete, run this command to become the postgres user and then create a new database user:

sudo su - postgres

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

Now, at the postgres user’s bash prompt, create the database user. Make sure the user name matches your unix login name: it will make your life much easier, as postgres will automatically authenticate you when you are logged in as that user:

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

Now you should leave the postgres user’s bash shell environment by typing:

exit

16.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)

Type q to exit.

16.2.5. 데이터베이스 생성¶

The createdb command is used to create a new database. It should be run from the bash shell prompt:

createdb address -O qgis

You can verify the existence of your new database by using this command:

psql -l

Which should return something like this:

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)

Type q to exit.

16.2.6. 데이터베이스 셸 세션 시작¶

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

psql address

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

\q

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

\?

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

\help

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

\help create table

See also the Psql cheat sheet - available online here.

16.2.7. SQL로 테이블 생성¶

Let’s start making some tables! We will use our ER Diagram as a guide. First, connect to the address db:

psql address

Then create a streets table:

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

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

You will notice that the command ends with a ; - all SQL commands should be terminated this way. When you press Enter, psql will report something like this:

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이 연속되는 명령어를 기다리고 있기 때문입니다. 명령어를 실행하려면 ; 을 입력하십시오.

To view your table schema, you can do this:

\d streets

Which should show something like this:

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)

To view your table contents, you can do this:

select * from streets;

Which should show something like this:

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

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

16.2.8. SQL로 키 생성¶

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

두 가지 방법이 있습니다.

• 테이블 생성 후 키 추가

• 테이블 생성 시 키 정의

Our table has already been created, so let’s do it the first way:

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

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

The more usual way to create a constraint is to do it when you create the table:

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

After adding the constraint, our table schema looks like this now:

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)

16.2.9. SQL로 인덱스 생성¶

We want lightning fast searches on peoples names. To provide for this, we can create an index on the name column of our people table:

create index people_name_idx on people(name);

\d people

Which results in:

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)

16.2.10. SQL로 테이블 드롭¶

If you want to get rid of a table you can use the drop command:

drop table streets;

If you used the same drop table command on the people table, it would be successful:

drop table people;

16.2.11. pgAdmin III에 대해¶

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

Under Ubuntu, you can install it like this:

sudo apt-get install pgadmin3

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

16.2.12. In Conclusion¶

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

16.2.13. What’s Next?¶

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