Transcript 슬라이드 1

Ch. 1
데이터베이스시스템 (2)
What to Learn



Database System Overview
Entity-Relationship diagram
Relational Data Model



How to use Commercial DBMS’s




Structure & Constraints
SQL language
MS Access
MS SQL Server
Normalization (정규화)
Transaction & Concurrency (동시성)
Ch.1 Database System
데이터베이스시스템
2
What to Learn


Database and Database system
Modeling a real world





Conceptual Modeling
Data Modeling
Data Structuring
File processing system
Advantages of database system
Ch.1 Database System
데이터베이스시스템
3
What to Learn

Components of a Database system






Database
Software (DBMS)
Users
Application Programmers, End users, DBA's
Hardware (computer)
ANSI database architecture
Various types of database system


Central vs. distributed systems
Client/server system
Ch.1 Database System
데이터베이스시스템
4
Components of a database system

Database


Software



Database management system (DBMS)
Handles all the access to the database
Hardware


A collection of data to store
Computers and hard disks
Users



Application programmer
End user
Database administrator (DBA)
Ch.1 Database System
데이터베이스시스템
5
components of a database system
Real World
system boundary
E-R diagram
conceptual
modeling
view
view
view
External view
logical schema
(conceptual view)
DBMS
DDL
DML
physical schema
(internal view)
Operating
system
stored database
Ch.1 Database System
데이터베이스시스템
6
1. Database
Logical database








NAME
ST_NO
ADDRESS
DEPT
GRADE
송치윤
52015
사당동
컴퓨터
3.3
김구완
53116
홍제동
정보통신
3.1
최재석
56034
양재동
정보관리
3.5
Field, Column, Attribute
Record, Row,
Tuple
File,
Table,
Relation
Record type, Table heading, Relation schema (type)
Records,
Table body,
Relation instance (state)
Physical database
Data dictionary (system catalog)
Ch.1 Database System
데이터베이스시스템
7
2. Software (DBMS) (1)

shielding database users from hardwarelevel details
Functions of DBMS

Data definition
Save the schema in the data dictionary
schema
detailed definition of the overall database design
logical schema, physical schema,
external/conceptual mapping, (p.48)
conceptual/internal mapping
Data definition language (DDL) (p.41)
Ch.1 Database System
데이터베이스시스템
8
components of a database system
Real World
system boundary
E-R diagram
conceptual
modeling
view
view
view
External view
logical schema
(conceptual view)
DBMS
DDL
DML
physical schema
(internal view)
Operating
system
stored database
Ch.1 Database System
데이터베이스시스템
9
2. Software (DBMS) (2)

Data manipulation
Data manipulation language (DML, 데이터 조작어) (p.42)
Retrieve, Select (검색), Insert (삽입), Delete (삭제),
Update (수정)

Other functions





Query optimization (질의 최적화)
Concurrency control (동시성 제어)
Recovery (복구)
Security (보안)
Integrity (무결성)
Ch.1 Database System
데이터베이스시스템
10
2. Software (DBMS) (3)

Example of query optimization
Students(St_No, Name, Year), 10,000 rows
Enroll(St_No, Subject, Grade) 50,000 rows
Select the enrollment state of first year students.
1) match Student and Enroll tables 50,000 rows
select first year students
12,500 rows
2) select first year strudents
2,500 rows
match Student and Enroll tables 12,500 rows
Ch.1 Database System
데이터베이스시스템
11
2. Software (DBMS) (4)

Example of concurrency control
Sales(Prod_No, sale_amount), current value = 30
Agent A sold 30, Agent B sold 20
The correct value should be 30+30+20 = 80
1) Agent
Agent
Agent
Agent
A reads the current value 30, calculates 30+30
B reads the current value 30, calculates 30+20
A updates the record to 60,
B updates the record to 50. The sale of 30 is lost.
2) Agent A requests a lock, and reads 30, calculates 30+30
Agent B requests a lock, but waits until the lock is
released.
Agent A updates the record to 60, releases the lock
Agent B gets a lock, and reads 60, calculates 60+20
Agent B updates the record to 80, releases the lock
Ch.1 Database System
데이터베이스시스템
12
2. Software (DBMS) (5) p.55
Ch.1 Database System
데이터베이스시스템
13
3. Users (1)

Application programmers
write database application programs in general programming
language
host language : general programming language like C++,
Visual Basic, C#, Java
embedded language : the language used to send DML to a DBMS

End users
ad hoc (unplanned) query user : use a program with which a user
directly send DML command to DBMS
canned (planned) query user ; use an easy-to-use program which
was developed by application programmers

Database Administrator (DBA)
responsible for the overall control of the database system at a
technical level
c.f. : data administrator
Ch.1 Database System
데이터베이스시스템
14
3. Users (2)
Ch.1 Database System
p.43
데이터베이스시스템
15
3. Users (3)

DBA’s tasks






Define the logical schema
Define the physical schema
Define security and integrity constraints
Define backup/recovery policies
Assist users : provide technical education,
analyze the information requirements of users,
consult application design
balance conflicting user requirements
Monitor performance and respond to changing
requirements
Ch.1 Database System
데이터베이스시스템
16
Goals of database systems






Reduce redundancy through integration
Sharing
Standardization
Enhance security and integrity
Data independence
Data abstraction
Ch.1 Database System
데이터베이스시스템
17
Goals of database systems

Data independence



Immunity of applications to change in physical representation
and access technique
Possible changes in data representation
numbers (real, integer, fixed, floating point)
remember Y2k problem
characters (ASCII, Unicode)
coding system (M or F, 0 or 1)
changes in record structure
product(Prod_No, Prod_Name, cost, price,
monthlyproduction, monthlysales)
Data abstraction

Easy to use (do not worry about physical structures)
select Prod_Name, price, monthlysales from product;
Ch.1 Database System
데이터베이스시스템
18
ANSI/SPARC Architecture p.48
Ch.1 Database System
데이터베이스시스템
19
Centralized vs. Distributed database
Ch.1 Database System
데이터베이스시스템
20
Centralized vs. Distributed database p.58
Ch.1 Database System
데이터베이스시스템
21
Client-Server system p.60

2-tier vs. 3-tier model
Ch.1 Database System
데이터베이스시스템
22