DBMS(데이터베이스 관리 시스템) 개요

DBMS (Databse Management System)란?

[Structure of DBMS]

A very large, integrated collection of data; software package designed to store and manage databses.

• Entities (e.g., students)
• Relationships (e.g., Eric is taking the class)

A DBMS typically has a layered architecture.

• 각 계층은 Application에서 특정 역할별로 구분된다(“Separation of Concern”) –> 유지보수성 ↑, 테스트 난이도 ↓

Benefits

• Recovery from system crashes
• Concurrent access
• Quick application development
• Data integrity and security.

Data model

A collection of concepts for describing data; 데이터들의 구조도를 그리는 과정

Data model으로는 Relational model of data가 가장 많이 사용된다.

• Relation: a table with rows and columns.
• Every relation has a schema.

---

Schema

A description of a particular collection of data, using the given data model.

Level of Abstraction

ER Model을 이용하여 필요한 데이터들의 구조도를 그리고, 관계 데이터베이스 모델에 따라 스키마를 결정하고, 데이터베이스를 구축한다.

Schema are defined using DDL(데이터 정의어); data is modified using DML(데이터 조작어)

Conceptual Schema(개념적 데이터 모델링)

define logical structure; 데이터들의 구조도를 그리는 과정 (ER Model 사용 多)

• i.e., Students(sid: string, name: string, login: string, age: integer, gpa: real)

Logical Shcema(논리적 데이터 모델링)

데이터 모델(e.g., ER Model)을 선택하고 데이터 스키마를 결정하는 과정

Physical Schema(물리적 데이터 모델링)

논리적 데이터 모델링에서 선택한 데이터베이스 모델(e.g., MySQL)에 따라 물리적인 데이터베이스를 만들어내는 과정

---

DB Application 설계 과정

Step 0: pick an application domain

Step 1: conceptual design (Conceptual Schema)

• discuss what to model in the application domain
• need a modeling language to express what you want
  • ER model is the most popular such language
• output: an ER diagram of the app. domain

Step 2: pick a type of DBMS

• relational DBMS is most popular and is our focus

Step 3: translate ER design to a relational schema (Logical Shcema)

• use a set of rules to translate from ER to rel. schema
• use a set of schema refinement rules to transform the above rel. schema into a good rel. schema

Step 4: Physical Schema

• implement your relational DBMS using a “database programming language” called SQL
• ordinary users cannot interact with the database directly
• and the database also cannot do everything you want
• hence write your application program in C++, Java, Python, etc to handle the interaction and take care of things that the database cannot do

DBMS 특성

Data Independence

Logical data independence: protected from changes in logical structure of data

Physical data independence: protected from changes in physical structure of data

Concurrency Control (동시성)

Disk accesses are frequent and relatively slow –> important to keep the cpu humming by working on several user programs concurrently.

In DBMS, users can pretend they are using a single-user system

• Transaction: atomic sequence of database actions (reads/writes)
  • On each transaction, the DB is in a consistent state (user’s responsibility!)

Integrity constraints(무결성 제약조건): 데이터베이스의 정확성, 일관성을 보장하기 위해 저장, 삭제, 수정 등을 제약하기 위한 조건이다.

Atomicity

DBMS ensures atomicity(all-or-nothing property) even if system crashes in the middle of a Xact

• log(history) of all actions
  • Log는 Xact id와 연계되어 있어서, undo 처리가 수월하다 (i.e., to resolve deadlock)
    • i.e., 충돌 –> 작업 초기화(원래 작업 log로 되돌아감)
  • duplexed and archived on “stable” storage
  • handled transparently by the DBMS
  • i.e., write(log before the changed page), commit/abort
• 만약 log 저장하기 전 충돌 발생한다면?
  • WAL protocol: log 저장하기 전 충돌발생해도 DB에 변경사항 아직 저장 안 시킨다.

다음 시간에는 DBMS 설계 개요에 대해 알아보자.

---

Reference

ER Model

Database Management Systems by Raghu Ramakrishnan and Johannes Gehrke