중요
번역은 여러분이 참여할 수 있는 커뮤니티 활동입니다. 이 페이지는 현재 100.00% 번역되었습니다.
15.2. 수업: 데이터 모델 구현하기
이제 모든 이론을 살펴봤으니, 새 데이터베이스를 생성해봅시다. 이 수업에서 생성할 데이터베이스를 이번 강의 내내 예제로 사용할 것입니다.
이 수업의 목표: 필수 소프트웨어를 설치해서 예제 데이터베이스를 구현하는 데 사용하기.
15.2.1. PostgreSQL 설치
참고
https://www.postgresql.org/download/ 에서 여러분의 운영체제를 위한 PostgreSQL 패키지와 설치 지침을 찾을 수 있습니다. 이 문서는 여러분이 우분투에서 QGIS를 실행한다고 가정하고 있다는 사실을 기억하십시오.
우분투의 경우:
sudo apt 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. 데이터베이스 사용자 생성하기
우분투의 경우:
설치가 완료되면, 다음 명령어를 실행해서 ‘postgres’ 사용자가 된 다음 새 데이터베이스 사용자를 생성하십시오:
sudo su - postgres
시스템이 비밀번호를 요구하면 사용자의 우분투 로그인 비밀번호를 입력하십시오. (사용자에게 sudo 권한이 있어야 합니다)
이제 ‘postgres’ 사용자의 배시(bash) 프롬프트에서 데이터베이스 사용자를 생성하십시오. 사용자 이름이 여러분의 유닉스 로그인 이름과 일치하게 하십시오. 여러분이 해당 사용자로 로그인하는 경우 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’ 사용자 배시 셸 환경에서 나가야 합니다:
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
PostgreSQL 치트 시트(cheat sheet) 도 참조하세요.
15.2.7. SQL로 테이블 생성하기
이제 테이블을 만들어봅시다! ER 도표를 지침으로 삼을 것입니다. 먼저, 주소 데이터베이스에 접속하십시오:
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 명령어는 이 방식으로 끝나야 합니다. Enter 키를 누르면 PostgreSQL이 다음과 같은 내용을 보고할 것입니다:
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)
테이블이 현재 비어 있다는 것을 알 수 있습니다.
혼자서 해보세요: ★★☆
앞에서 배운 내용대로 ‘people’이라는 테이블을 만드십시오:
phone number, home address, name 등의 필드들을 추가하십시오. (예시로 든 필드 이름들이 모두 무결하지는 않습니다. 무결한 이름으로 변경하십시오.) 테이블에 앞에서와 동일한 데이터형의 ID 열을 추가해야 합니다.
해답
올바른 ‘people’ 테이블을 생성하기 위해 필요한 SQL은 다음과 같습니다:
create table people (id serial not null primary key,
name varchar(50),
house_no int not null,
street_id int 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)
설명의 편의를 위해 fkey 제약 조건을 일부러 생략했습니다.
15.2.8. SQL로 키 생성하기
앞 단계까지의 문제점은 데이터베이스가 people 과 streets 가 논리적 관계를 가지고 있다는 사실을 모른다는 것입니다. 이 관계를 표현하려면, streets 테이블의 기본 키를 가리키는 외래 키를 정의해야 합니다.
두 가지 방법이 있습니다:
테이블 생성 후 키 추가하기
테이블 생성 시 키 정의하기
우리는 이미 테이블을 생성했으니까, 첫 번째 방법으로 해봅시다:
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 명령어가 작동하지 않는 이유는 people 테이블이 streets 테이블을 가리키는 외래 키 제약 조건을 가지고 있기 때문입니다. 다시 말해 streets 테이블을 드롭(삭제)하면 people 테이블이 존재하지 않는 streets 데이터를 참조하게 되기 때문입니다.
CASCADE 명령어를 사용해서 streets 테이블을 ‘강제로’ 삭제할 수도 있지만, 이렇게 하면 people 테이블은 물론 streets 테이블과 관계를 가지고 있는 다른 모든 테이블도 삭제하게 될 것입니다. 조심해서 사용하세요!
people 테이블에 동일한 drop table 명령어를 사용한 경우, 성공적으로 작동할 것입니다:
drop table people;
참고
실제로 명령어를 입력해서 people 테이블을 삭제했다면, 다음 수업에 필요하므로 지금 다시 생성하도록 하십시오.
15.2.11. pgAdmin III에 대해
여러분에게 ‘psql’ 프롬프트에서 SQL 명령을 보여주는 이유는 데이터베이스에 대해 배우는 데 아주 유용한 방식이기 때문입니다. 하지만 여기서 여러분이 배운 작업의 대부분을 더 빠르고 쉽게 할 수 있는 방법이 있습니다. pgAdmin III를 설치하면 GUI 환경에서 ‘포인트 & 클릭’만으로 테이블의 생성, 삭제, 수정 등을 할 수 있습니다.
우분투에서는 다음과 같이 설치하면 됩니다:
sudo apt install pgadmin3
pgAdmin III에 대해서는 다른 강의에서 더 자세히 배우게 될 것입니다.
15.2.12. 결론
이제 완전히 처음부터 새 데이터베이스를 생성하는 방법을 배웠습니다.
15.2.13. 다음은 무엇을 배우게 될까요?
다음 수업에서 DBMS를 사용해서 새 데이터를 추가하는 방법을 배울 것입니다.