15.2. Lesson: 데이터 모델 시행
이제 모든 이론을 살펴봤으니, 새 데이터베이스를 생성해봅시다. 이 강의에서 생성할 데이터베이스를 이번 모듈 내내 예제로 사용할 것입니다.
이 강의의 목표: 필수 소프트웨어를 설치하고 예제 데이터베이스를 시행하는 데 사용하기.
15.2.1. PostgreSQL 설치
참고
You can find PostgreSQL packages and installation instructions for your operating system at https://www.postgresql.org/download/. Please note that the documentation will assume users are running QGIS under Ubuntu.
우분투의 경우,
sudo apt 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 키를 누른 다음, 다운로드 및 설치가 끝날 때까지 기다리십시오.
15.2.2. 도움말
PostgreSQL has very good online documentation.
15.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
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)
Type Q to exit.
15.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.
15.2.6. 데이터베이스 셸 세션 시작
다음과 같이 손쉽게 사용자 데이터베이스에 접속할 수 있습니다.
psql address
psql 데이터베이스 셸에서 나오려면 다음과 같이 입력하십시오.
\q
셸 사용법에 대한 도움말을 보려면 다음과 같이 입력하십시오.
\?
SQL 명령어에 대한 도움말을 보려면 다음과 같이 입력하십시오.
\help
특정 명령어에 대한 도움말을 보려면 (예를 들어) 다음과 같이 입력합니다.
\help create table
See also the Psql cheat sheet.
15.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)
사용자 테이블이 현재 비어 있다는 것을 알 수 있습니다.
Try Yourself 
중급
앞에서 배운 내용대로 people이라는 테이블을 만드십시오.
phone number , home address , name 등의 항목들을 추가하십시오. (예시한 항목명들이 모두 유효하지는 않습니다. 유효한 명칭으로 변경하십시오.) 앞에서와 마찬가지로 동일한 데이터 유형의 ID 열을 테이블에 추가해야 합니다.
Answer
The SQL required to create the correct people table is:
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 );
The schema for the table (enter \\d people) looks like this:
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)
For illustration purposes, we have purposely omitted the fkey constraint.
15.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)
15.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)
15.2.10. SQL로 테이블 드롭
If you want to get rid of a table you can use the drop command:
drop table streets;
In our current example, the above command would not work. Why not?
Answer
The reason the DROP command would not work in this case is because the people table has a Foreign Key constraint to the streets table. This means that dropping (or deleting) the streets table would leave the people table with references to non-existent streets data.
It is possible to ‘force’ the streets table to be deleted by using the
CASCADE command, but this would also delete the people and any other table
which had a relationship to the streets table. Use with caution!
If you used the same drop table command on the people table, it would be successful:
drop table people;
참고
실제로 명령어를 입력해서 people 테이블을 드롭했다면, 다음 예제에 필요하므로 지금 다시 생성하도록 하십시오.
15.2.11. pgAdmin III에 대해
여러분에게 psql 프롬프트에서 SQL 명령을 보여주는 이유는 데이터베이스에 대해 배우는 데 아주 유용한 방법이기 때문입니다. 하지만 여기서 여러분이 배운 작업의 대부분을 더 빠르고 쉽게 할 수 있는 방법이 있습니다. pgAdmin III를 설치하면 GUI 환경에서 ‘포인트 & 클릭’만으로 테이블의 생성, 삭제, 수정 등을 할 수 있습니다.
Under Ubuntu, you can install it like this:
sudo apt install pgadmin3
다른 모듈에서 pgAdmin III에 대해 더 자세히 배우게 될 것입니다.
15.2.12. In Conclusion
이제 완전히 처음부터 새 데이터베이스를 생성하는 방법을 배웠습니다.
15.2.13. What’s Next?
다음 강의에서 DBMS를 통해 새 데이터를 추가하는 방법을 배울 것입니다.