Transcript Database Relational Model

Database
Relational Model
SPARCS’11 undead
What is Database?
• Data
• 정보를 작성하기 위해 필요한 자료
• Database
• 논리적으로 연관된 하나 이상의 자료의 모음
• 데이터를 잘 정리해두고, 필요할 때 정리된 정보를 취득하기 위함
• DMBS (Database Management System)
•
•
•
•
데이터를 효과적으로 이용할 수 있도록 정리/보관하기 위한 소프트웨어
데이터의 추가, 변경, 삭제, 검색 등의 기능을 집대성한 소프트웨어 패키지
MySQL, PostgreSQL, SQLite, Microsoft SQL Server, Microsoft Access, Oracle 등
다수의 사용자 또는 프로그램의 요구를 처리하고 적절히 응답하여 데이터를 사용할 수 있게
해주는 역할
2013-04-09
SPARCS'11 undead
2
DMBS Functions
• Definition (정의)
• 데이터에 대한 형식, 구조, 제약조건들을 명세하는 기능
• Construction (구축)
• DBMS가 관리하는 기억장치에 데이터를 저장하는 기능
• Manipulation (조작)
• 특정한 데이터를 검색하기 위한 쿼리, 데이터베이스의 갱신, 보고서 생성 기능 등
• Sharing (공유)
• 여러 사용자와 프로그램이 데이터베이스에 동시에 접근하도록 하는 기능
• Protection (보호)
• 하드웨어나 소프트웨어의 오동작 또는 권한이 없는 악의적인 접근으로부터 시스템을 보호
• Maintenance (유지보수)
• 시간이 지남에 따라 변화하는 요구사항을 반영할 수 있도록 하는 기능
2013-04-09
SPARCS'11 undead
3
Database Models
•
•
•
•
•
•
•
•
•
Hierarchical database model
Network model
Relational model
Entity-relationship model
Enhanced entity-relationship model
Object model
Document model
Entity-attribute-value model
Star schema
a
2013-04-09
SPARCS'11 undead
4
Database Models
•
•
•
•
•
•
•
•
•
Hierarchical database model
Network model
Relational model
Entity-relationship model
Enhanced entity-relationship model
Object model
Document model
Entity-attribute-value model
Star schema
다 필요 없고 일단 Relational model부터 공부합시다
2013-04-09
SPARCS'11 undead
5
Relational Model
• Entity
• 실체
• 실세계에서 개별적으로 인식될 수 있는 것
• 데이터베이스에 저장되는 데이터의 대상
• Attribute
• 어떤 entity에 대해서 관심을 가질만한 가치가 있는 속성
• Relation
• 둘 이상의 entity들 간의 관계
2013-04-09
SPARCS'11 undead
6
Relational Model
2013-04-09
SPARCS'11 undead
7
Relational Model
한 번 설계해 봅시다.
2013-04-09
SPARCS'11 undead
8
Designing KAIST Student DB
Student
2013-04-09
SPARCS'11 undead
9
Designing KAIST Student DB
• 이름
• 학번
• 학과
• 출신고교
• 생년월일
2013-04-09
Student
SPARCS'11 undead
10
Designing KAIST Student DB
이름
학번
학과
출신고교
생년월일
샤바칸
20111064
전산학과
해외고
921112
찰제
20110846
전산학과
일반고
931125
만렙물개
20110054
산업디자인학과
일반고
920620
커피
20120308
전산학과
KSA
940206
채종욱
20120522
전산학과
KSA
940411
마재의
20120123
전산학과
한성과고
930503
짭덕
20100135
전기 및 전자공학과
서울과고
920626
호떡
20100982
전산학과
서울과고
920926
2013-04-09
SPARCS'11 undead
11
Desgning KAIST Student DB
• Key
•
•
•
•
어떤 entity를 고유하게 만들어주는 attribute(s)
하나의 필드가 될 수도 있고, 여러 필드의 조합일 수도 있습니다.
null이 될 수 없는 필드여야 함
Unique해야 함
• Key attribute
2013-04-09
SPARCS'11 undead
12
Designing KAIST Student DB
이름
학번
학과
출신고교
생년월일
샤바칸
20111064
전산학과
해외고
921112
찰제
20110846
전산학과
일반고
931125
만렙물개
20110054
산업디자인학과
일반고
920620
커피
20120308
전산학과
KSA
940206
채종욱
20120522
전산학과
KSA
940411
마재의
20120123
전산학과
한성과고
930503
짭덕
20100135
전기 및 전자공학과
서울과고
920626
호떡
20100982
전산학과
서울과고
920926
2013-04-09
• Key가 될 수 있는 필드는?
SPARCS'11 undead
13
Designing KAIST Student DB
이름
학번
학과
출신고교
생년월일
샤바칸
20111064
전산학과
해외고
921112
찰제
20110846
전산학과
일반고
931125
만렙물개
20110054
산업디자인학과
일반고
920620
커피
20120308
전산학과
KSA
940206
채종욱
20120522
전산학과
KSA
940411
마재의
20120123
전산학과
한성과고
930503
짭덕
20100135
전기 및 전자공학과
서울과고
920626
호떡
20100982
전산학과
서울과고
920926
2013-04-09
• Key가 될 수 있는 필드는?
SPARCS'11 undead
14
Designing KAIST Student DB
이름
학번
학과
출신고교
생년월일
샤바칸
20111064
전산학과
해외고
921112
찰제
20110846
전산학과
일반고
931125
만렙물개
20110054
산업디자인학과
일반고
920620
커피
20120308
전산학과
KSA
940206
채종욱
20120522
전산학과
KSA
940411
마재의
20120123
전산학과
한성과고
930503
짭덕
20100135
전기 및 전자공학과
서울과고
920626
호떡
20100982
전산학과
서울과고
920926
2013-04-09
• Key가 될 수 있는 필드는?
SPARCS'11 undead
15
Designing KAIST Student DB
Department
2013-04-09
SPARCS'11 undead
16
Designing KAIST Student DB
• 학과 이름
• 학과 코드
• 창립 년도
• 홈페이지
Department
2013-04-09
SPARCS'11 undead
17
Designing KAIST Student DB
학과 이름
학과 코드
창립년도
홈페이지
전산학과
cs
1972
cs.
산업디자인학과
id
1986
id.
전기 및 전자공학과
ee
1973
ee.
수리과학과
mas
1972
mathsci.
경영과학과
msb
2009
ms.
2013-04-09
SPARCS'11 undead
18
Designing KAIST Student DB
Department
Student
학과 이름
학과 코드
창립년도
홈페이지
이름
학번
학과
출신고교
생년월일
전산학과
cs
1972
cs.
샤바칸
20111064
전산학과
해외고
921112
산업디자인학과
id
1986
id.
찰제
20110846
전산학과
일반고
931125
전기 및 전자공학과
ee
1973
ee.
만렙물개
20110054
산업디자인학과
일반고
920620
수리과학과
mas
1972
mathsci.
커피
20120308
전산학과
KSA
940206
경영과학과
msb
2009
ms.
채종욱
20120522
전산학과
KSA
940411
마재의
20120123
전산학과
한성과고
930503
짭덕
20100135
전기 및 전자공학과
서울과고
920626
호떡
20100982
전산학과
서울과고
920926
그렇다면 이 둘의 관계는?
- 학생은 학과에 소속되어 있다 -
2013-04-09
SPARCS'11 undead
19
Departme
nt
include
Studen
t
샤바칸
cs
찰제
…
만렙물개
id
…
…
2013-04-09
SPARCS'11 undead
20
include
Department
Department
1..n
1
Department
2013-04-09
Student
Student
Student
SPARCS'11 undead
21
Designing KAIST Student DB
Department
Student
학과 이름
학과 코드
창립년도
홈페이지
이름
학번
학과
출신고교
생년월일
전산학과
cs
1972
cs.
샤바칸
20111064
cs
해외고
921112
산업디자인학과
id
1986
id.
찰제
20110846
cs
일반고
931125
전기 및 전자공학과
ee
1973
ee.
만렙물개
20110054
id
일반고
920620
수리과학과
mas
1972
mathsci.
커피
20120308
cs
KSA
940206
경영과학과
msb
2009
ms.
채종욱
20120522
cs
KSA
940411
무학과
???
???
???
마재의
20120123
cs
한성과고
930503
짭덕
20100135
ee
서울과고
920626
호떡
20100982
cs
서울과고
920926
김영석
20121023
무학과
해외고
0531
2013-04-09
SPARCS'11 undead
22
Designing KAIST Student DB
Department
Student
학과 이름
학과 코드
창립년도
홈페이지
이름
학번
학과
출신고교
생년월일
전산학과
cs
1972
cs.
샤바칸
20111064
cs
해외고
921112
산업디자인학과
id
1986
id.
찰제
20110846
cs
일반고
931125
전기 및 전자공학과
ee
1973
ee.
만렙물개
20110054
id
일반고
920620
수리과학과
mas
1972
mathsci.
커피
20120308
cs
KSA
940206
경영과학과
msb
2009
ms.
채종욱
20120522
cs
KSA
940411
마재의
20120123
cs
한성과고
930503
짭덕
20100135
ee
서울과고
920626
호떡
20100982
cs
서울과고
920926
김영석
20121023
null
해외고
930531
삭ㅋ제ㅋ
2013-04-09
SPARCS'11 undead
23
Department
include
1
2013-04-09
Student
1..n
SPARCS'11 undead
24
Department
include
0..1
2013-04-09
Student
1..n
SPARCS'11 undead
25
include
Department
Department
1..n
0..1
Department
2013-04-09
Student
Student
Student
SPARCS'11 undead
26
Departme
nt
include
Studen
t
샤바칸
cs
찰제
…
만렙물개
id
…
…
ms
b
2013-04-09
김영석
SPARCS'11 undead
27
Department
include
0..1
2013-04-09
Student
1..n
SPARCS'11 undead
28
Department
include
0..n
2013-04-09
Student
1..n
SPARCS'11 undead
29
include
Department
Department
1..n
0..n
Department
2013-04-09
Student
Student
Student
SPARCS'11 undead
30
Designing KAIST Student DB
Department
Student
학과 이름
학과 코드
창립년도
홈페이지
이름
학번
학과
출신고교
생년월일
전산학과
cs
1972
cs.
샤바칸
cs
해외고
921112
산업디자인학과
id
1986
id.
2011106
4
전기 및 전자공학과
ee
1973
ee.
찰제
cs
일반고
931125
수리과학과
mas
1972
mathsci.
2011084
6
경영과학과
msb
2009
ms.
만렙물개
2011005
4
id
일반고
920620
커피
2012030
8
cs
KSA
940206
채종욱
2012052
2
cs
KSA
940411
마재의
2012012
3
cs
한성과고
930503
짭덕
2010013
5
ee
서울과고
920626
호떡
2010098
2
cs
서울과고
920926
김영석
2012102
3
null
해외고
930531
2013-04-09
SPARCS'11 undead
31
Designing KAIST Student DB
Department
Student
학과 이름
학과 코드
창립년도
홈페이지
이름
학번
학과
복수전공
출신고교
생년월일
전산학과
cs
1972
cs.
샤바칸
cs
msb
해외고
921112
산업디자인학과
id
1986
id.
2011106
4
전기 및 전자공학과
ee
1973
ee.
찰제
cs
null
일반고
931125
수리과학과
mas
1972
mathsci.
2011084
6
경영과학과
msb
2009
ms.
만렙물개
2011005
4
id
null
일반고
920620
커피
2012030
8
cs
null
KSA
940206
채종욱
2012052
2
cs
null
KSA
940411
마재의
2012012
3
cs
null
한성과고
930503
짭덕
2010013
5
ee
null
서울과고
920626
호떡
2010098
2
cs
mas
서울과고
920926
김영석
2012102
3
null
null
해외고
930531
2013-04-09
SPARCS'11 undead
32
Designing KAIST Student DB
Student
이름
학과
복수전공 1
복수전공 2
부전공
샤바칸
cs
msb
null
(경제)
찰제
cs
null
null
null
만렙물개
id
null
null
null
짭덕
ee
null
null
null
호떡
cs
mas
null
null
김영석
null
null
null
null
안재만
cs
mas
ee
null
조형
id
msb
null
cs
2013-04-09
SPARCS'11 undead
33
Designing KAIST Student DB
Student
이름
학과
복수전공 1
복수전공 2
부전공
샤바칸
cs
msb
null
(경제)
찰제
cs
null
null
null
만렙물개
id
null
null
null
짭덕
ee
null
null
null
호떡
cs
mas
null
null
김영석
null
null
null
null
안재만
cs
mas
ee
null
조형
id
msb
null
cs
Scenario:
어떤 신입생이 들어옵니다.
이 사람은 오로지 DB에 트롤링을 하려는 목적으로
카이스트의 모든 학부 프로그램을 복수전공과 부전공을 적절히 섞어서 신청합니다.
2013-04-09
SPARCS'11 undead
34
Designing KAIST Student DB
Student
이름
학과
복수전공 1
복수전공 2
복수전공 3
복수전공 4
복수전공 5
복수전공 6
복수전공 7
부전공 1
부전공 2
부전공 3
부전공 4
샤바칸
cs
msb
null
null
null
null
null
null
(경제)
null
null
null
찰제
cs
null
null
null
null
null
null
null
null
null
null
null
만렙물개
id
null
null
null
null
null
null
null
null
null
null
null
짭덕
ee
null
null
null
null
null
null
null
null
null
null
null
호떡
cs
mas
null
null
null
null
null
null
null
null
null
null
김영석
null
null
null
null
null
null
null
null
null
null
null
null
안재만
cs
mas
ee
null
null
null
null
null
null
null
null
null
조형
id
msb
null
null
null
null
null
null
cs
null
null
null
헤르메온느
cs
id
mas
bs
bis
nqe
ie
ch
msb
(경제)
stp
me
어엌ㅋㅋㅋㅋㅋ 닝겐노 모래시계와 강려크데스네
2013-04-09
SPARCS'11 undead
35
Department
DepartmentStudent
Student
이름
학과이름
학과코
드
창립년
도
홈페이
지
바이오및뇌공학
과
bis
2001
bioeng.
생명과학과
bs
1972
bs.
김영석
전산학과
cs
1972
cs.
안재만
수리과학과
mas
1972
mathsci.
조형
2013-04-09
샤바칸
찰제
만렙물개
SPARCS'11 undead
36
Department
DepartmentStudent
Student
학과코드
이름
이름
cs
샤바칸
샤바칸
cs
찰제
찰제
id
만렙물개
만렙물개
학과이름
학과코
드
창립년
도
홈페이
지
바이오및뇌공학
과
bis
2001
bioeng.
생명과학과
bs
1972
bs.
cs
안재만
김영석
전산학과
cs
1972
cs.
id
조형
안재만
수리과학과
mas
1972
mathsci.
2013-04-09
조형
SPARCS'11 undead
37
Department
DepartmentStudent
Student
학과코드
이름
이름
cs
샤바칸
샤바칸
cs
찰제
찰제
id
만렙물개
만렙물개
학과이름
학과코
드
창립년
도
홈페이
지
바이오및뇌공학
과
bis
2001
bioeng.
생명과학과
bs
1972
bs.
cs
안재만
김영석
전산학과
cs
1972
cs.
id
조형
안재만
수리과학과
mas
1972
mathsci.
msb
샤바칸
조형
ee
안재만
mas
안재만
msb
조형
cs
조형
2013-04-09
SPARCS'11 undead
38
ERD
2013-04-09
SPARCS'11 undead
39
Entity Relation Diagram
2013-04-09
SPARCS'11 undead
40
dep_name
foundation
homepage
dep__id
Department
2013-04-09
include
SPARCS'11 undead
std_name
std_number
Student
41
schema
2013-04-09
SPARCS'11 undead
42
Department
include
Student
dep_id
char(3), not null
std_id
integer, not null
dep_id
char(3), primary key,
unique
std_id
integer, primary key,
unique
dep_nam
e
varchar(20), unique, not
null
std_name
varchar(20), unique, not
null
foundatio
n
integer, not null
2013-04-09hompage
varchar(20)
SPARCS'11 undead
43
Department
include
Student
dep_id
char(3), not null
std_id
integer, not null
dep_id
char(3), primary key,
unique
std_id
integer, primary key,
unique
dep_nam
e
varchar(20), unique, not
null
std_name
varchar(20), unique, not
null
foundatio
n
integer, not null
2013-04-09hompage
varchar(20)
SPARCS'11 undead
44
Department
include
Student
dep_id
char(3), not null
std_id
integer, not null
dep_id
char(3), primary key,
unique
std_id
integer, primary key,
unique
dep_nam
e
varchar(20), unique, not
null
std_name
varchar(20), unique, not
null
foundatio
n
integer, not null
2013-04-09hompage
varchar(20)
SPARCS'11 undead
45
Department
include
Student
dep_id
char(3), not null
std_id
integer, not null
dep_id
char(3), primary key,
unique
std_id
integer, primary key,
unique
dep_nam
e
varchar(20), unique, not
null
std_name
varchar(20), not null
foundatio
n
integer, not null
2013-04-09hompage
varchar(20)
SPARCS'11 undead
46
MySQL
• Structured query language / 구조화 질의어
• 특정한 DB 시스템에 한정되지 않아 널리 사용됨
• Functions
• Query
• Data definition
• Manipulation
2013-04-09
SPARCS'11 undead
47
$mysql [–h host] [–u user] [–p[password]] [dbname]
$mysql -u zzongaly -p
Enter password: ********
Welcome to the MySQL monitor. Commands end with ; or
\g.
Your MySQL connection id is 14 to server version:
3.23.34a
Type 'help;' or '\h' for help. Type '\c' to clear the
buffer
mysql>
2013-04-09
SPARCS'11 undead
48
>mysql [–h host] [–u user] [–p[password]] [dbname]
>mysql -u zzongaly –ppassword
Welcome to the MySQL monitor. Commands end with ; or
\g.
Your MySQL connection id is 14 to server version:
3.23.34a
Type 'help;' or '\h' for help. Type '\c' to clear the
buffer
mysql>
2013-04-09
SPARCS'11 undead
49
mysql> show databases;
mysql> show databases;
+--------------------+
| Database
|
+--------------------+
| information_schema |
| mysql
|
+--------------------+
2 rows in set (0.00 sec)
2013-04-09
SPARCS'11 undead
50
mysql> show databases;
mysql> show databases;
+--------------------+
| Database
|
+--------------------+
| information_schema |
| mysql
|
+--------------------+
2 rows in set (0.00 sec)
2013-04-09
SPARCS'11 undead
51
mysql> create database [dbname];
mysql> create database zzongaly
-> ;
Query OK, 1 row affected (0.04 sec)
2013-04-09
SPARCS'11 undead
52
mysql> use [dbname];
mysql> use zzongaly
Database changed
2013-04-09
SPARCS'11 undead
53
mysql> show tables;
mysql> show tables;
Empty set (0.00 sec)
2013-04-09
SPARCS'11 undead
54
Department
include
Student
dep_id
char(3), not null
std_id
integer, not null
dep_id
char(3), primary key,
unique
std_id
integer, primary key,
unique
dep_nam
e
varchar(20), unique, not
null
std_name
varchar(20), not null
foundatio
n
integer, not null
2013-04-09hompage
varchar(20)
SPARCS'11 undead
55
Student
2013-04-09
SPARCS'11 undead
std_id
integer, primary key,
unique
std_name
varchar(20), not null
56
mysql> CREATE TABLE tablename(
fieldname fieldtype [NOT NULL],
[fieldname fieldtype [NOT NULL],]
UNIQUE(fieldname[, fieldname]),
PRIMARY KEY(fieldname[, fieldname])
);
2013-04-09
SPARCS'11 undead
57
std_id
integer, primary key,
unique
std_name
varchar(20), not null
mysql> CREATE TABLE tablename(
fieldname fieldtype [NOT NULL],
[fieldname fieldtype [NOT NULL],]
UNIQUE(fieldname[, fieldname]),
PRIMARY KEY(fieldname[, fieldname])
);
mysql>
->
->
->
->
2013-04-09
CREATE TABLE Student(
std_id INTEGER,
std_name VARCHAR(20) NOT NULL,
PRIMARY KEY(std_id)
);
SPARCS'11 undead
58
std_id
integer, primary key,
unique
std_name
varchar(20), not null
mysql> CREATE TABLE tablename(
fieldname fieldtype [NOT NULL],
[fieldname fieldtype [NOT NULL],]
UNIQUE(fieldname[, fieldname]),
PRIMARY KEY(fieldname[, fieldname])
);
mysql>
->
->
->
->
->
2013-04-09
CREATE TABLE Student(
std_id INTEGER,
std_name VARCHAR(20) NOT NULL,
PRIMARY KEY(std_id),
CHECK (std_id >= 19000000 AND std_id< 30000000)
);
SPARCS'11 undead
59
std_id
integer, primary key,
unique
std_name
varchar(20), not null
mysql> INSERT INTO tablename VALUES(value [,value]);
mysql> INSERT INTO Student VALUES(20070764, ’정진근’);
2013-04-09
SPARCS'11 undead
60
std_id
integer, primary key,
unique
std_name
varchar(20), not null
mysql> INSERT INTO tablename (fieldname [, fieldname])
VALUES (value [,value]);
mysql> INSERT INTO Student(std_id, std_name)
-> VALUES(20070764, ’정진근’);
2013-04-09
SPARCS'11 undead
61
std_id
integer, primary key,
unique
std_name
varchar(20), not null
mysql> SELECT * FROM tablename;
mysql> SELECT * FROM Student;
2013-04-09
SPARCS'11 undead
62
std_id
integer, primary key,
unique
std_name
varchar(20), not null
mysql> SELECT fieldname FROM tablename;
mysql> SELECT std_id FROM Student;
2013-04-09
SPARCS'11 undead
63
std_id
integer, primary key,
unique
std_name
varchar(20), not null
mysql> SELECT * FROM tablename WHERE condition;
mysql> SELECT * FROM Student WHERE std_id > 20110000;
2013-04-09
SPARCS'11 undead
64
std_id
integer, primary key,
unique
std_name
varchar(20), not null
mysql> DELETE FROM tablename WHERE condition;
mysql> SELECT FROM Student WHERE std_id = 20110125;
2013-04-09
SPARCS'11 undead
65
Department
include
Student
dep_id
char(3), not null
std_id
integer, not null
dep_id
char(3), primary key,
unique
std_id
integer, primary key,
unique
dep_nam
e
varchar(20), unique, not
null
std_name
varchar(20), not null
foundatio
n
integer, not null
2013-04-09hompage
varchar(20)
SPARCS'11 undead
66
include
2013-04-09
dep_id
char(3), not null
std_id
integer, not null
SPARCS'11 undead
67
dep_id
char(3), not null
std_id
integer, not null
mysql> CREATE TABLE tablename(
fieldname fieldtype [NOT NULL],
[fieldname fieldtype [NOT NULL],]
UNIQUE(fieldname[, fieldname]),
PRIMARY KEY(fieldname[, fieldname])
);
mysql>
->
->
->
->
2013-04-09
CREATE TABLE Dep_Stu(
dep_id char(3) NOT NULL,
std_id INTEGER NOT NULL,
PRIMARY KEY(
);
SPARCS'11 undead
)
68
dep_id
char(3), not null
std_id
integer, not null
mysql> CREATE TABLE tablename(
fieldname fieldtype [NOT NULL],
[fieldname fieldtype [NOT NULL],]
UNIQUE(fieldname[, fieldname]),
PRIMARY KEY(fieldname[, fieldname])
);
mysql>
->
->
->
->
2013-04-09
CREATE TABLE Dep_Stu(
dep_id char(3) NOT NULL,
std_id INTEGER NOT NULL,
PRIMARY KEY(dep_id, std_id)
);
SPARCS'11 undead
69
dep_id
char(3), not null
std_id
integer, not null
mysql> CREATE TABLE tablename(
fieldname fieldtype [NOT NULL],
[fieldname fieldtype [NOT NULL],]
UNIQUE(fieldname[, fieldname]),
PRIMARY KEY(fieldname[, fieldname])
);
mysql>
->
->
->
->
->
->
2013-04-09
CREATE TABLE Dep_Stu(
dep_id char(3) NOT NULL,
std_id INTEGER NOT NULL,
PRIMARY KEY(dep_id, std_id),
FOREIGN KEY(dep_id) REFERENCES Department,
FOREIGN KEY(std_id) REFERENCES Student
);
SPARCS'11 undead
70
Help
• 궁금하면 엔간하면 Google신께 기도를 올리면 됩니다.
2013-04-09
SPARCS'11 undead
71
Database Tuning
2013-04-09
SPARCS'11 undead
72
Database Tuning
Golden rule:
Good database performance starts with good database design. No amount of
fine tuning will make a poorly designed database perform as well as a well-designed
database.
2013-04-09
SPARCS'11 undead
73
Database Design
• Conceptual design – DBMS independent
• Database analysis and requirements
• Determine end-user views, outputs, and transaction-processing requirements
• Entity relationship modeling and normalization
• Define entities, attributes, and relationships. Draw DR diagrams. Normalize tables.
• Data model verification
• Identify main processes, insert, update, and delete rules
• Logical design – DBMS dependent
• Translate the conceptual model into definitions for tables, views, and so on.
• Physical design – Hardware dependent
• Define storage structures and access paths for optimum performance
2013-04-09
SPARCS'11 undead
74
Database Design
• ACID test
• Atomicity
• 모든 작업들이 수행되었는지를 보장하는 능력
• Consistency
• 실행을 성공적으로 완료하면 언제나 일관성 있는 데이터베이스 상태로 유지하는 것
• Isolation
• 작업을 수행할 때 다른 작업이 끼어들지 못하도록 보장하는 것
• Durability
• 성공적으로 수행된 작업은 영원히 반영되어야 함
• Serializability
• Multiuser 혹은 distributed database에서 중요시됨
• 여러 가지의 작업을 동시에 실행할 때 일관성 있는 결과를 내놓아야 함
2013-04-09
SPARCS'11 undead
75
Database Tuning
쿼리를 처리할 때 CPU, RAM, Secondary Storage, Network, 어플리케이션의 코드(라 쓰고
똥이라 읽습니다) 때문에 병목현상이 일어남!
2013-04-09
SPARCS'11 undead
76
Database Tuning
쿼리를 처리할 때 CPU, RAM, Secondary Storage, Network, 어플리케이션의 코드(라 쓰고
똥이라 읽습니다) 때문에 병목현상이 일어남!
두 가지 접근:
• SQL performance tuning
• 서버의 자원을 최대한 적게 사용하는 SQL 쿼리를 보내는 것
• DBMS performance tuning
• 존재하는 자원을 최대한 효율적으로 사용하는 것
2013-04-09
SPARCS'11 undead
77
Database Tuning
• Index를 사용한다
•
•
•
•
Create indexes for each single attribute used in a WHERE, HAVING, ORDER BY, GROUP BY
Do not use indexes in small tables or tables with low sparsity
Declare primary and foreign keys so the optimizer can use the indexes in join operations.
Declare indexes in join columns other than PK or FK
2013-04-09
SPARCS'11 undead
78
Database Tuning
• WHERE, HAVING 등 조건문 사용에 주의를 기한다.
• Use simple columns or literals as operands in a conditional expression
• Avoid the use of conditional expressions with functions whenever possible
• Numeric field comparisons are faster than character, date, and NULL comparisons
• Equality comparisons are faster than inequality comparisons
• Whenever possible, transform conditional expressions to use literals
• price – 10 = 7  price = 17
• P_QOH < P_MIN AND P_MIN = P_REORDER AND P_MIN > 10
 P_QOH = 10 AND P_MIN = P_REORDER AND P_MIN > 10
•
•
•
•
When using multiple conditional expressions, write the equality conditions first
If you use multiple AND conditions, write the condition most likely to be false first
When using multiple OR conditions, put the condition most likely to be true first
Whenever possible, try to avoid the use of the NOT logical operator
• NOT (P_PRICE > 10.00)  P_PRICE <= 10.00
• NOT(EMP_SEX = ‘M’)  EMP_SEX = ‘F’
2013-04-09
SPARCS'11 undead
79
Database Tuning
• Query Formulation
• Identify what columns and computations are required
• Simple expressions?
• Aggregate functions? (GROUP BY, subquery)
• Identify the source tables
• Determine how to join the tables
• Determine what selection criteria is needed
•
•
•
•
Simple comparison
Single value to multiple values (IN)
Nested comparisons ( PRICE >= ( SELECT AVG(PRICE) FROM PRODUCT )
Grouped data selection (HAVING)
• Determine in what order to display the output (ORDER BY)
2013-04-09
SPARCS'11 undead
80
Database Tuning
• DBMS performance tuning
•
•
•
•
Data cache
SQL cache
Sort ache
Optimizer mode (cost-based vs. rule-based)
* cost-based optimizer: determine the best approach to execute a
query
* rule-based optimizer: uses preset rules and points to determine the
best approach to execute a query
2013-04-09
SPARCS'11 undead
81