Posting 3: DBMS Architectures

Introduction

Architecture of a database system presents the design principles of DBMS , including process examples, parallel structure, storage system design, transaction system implementation, query application, and so on. It is helpful reference to database researchers, practitioners, and other fields' people, who are interested in database system design techniques. The achitecture of DBMS can be broken down into two major structures: logical and physical architectures. Here, I only discuss the logical architecture of DBMS.

Logical Architecture of DBMS

Based on suggestion of the ANSI/SPARC Study Group, logical architecture of DBMS can be divided into three levels: external level, conceptual level, and internal level.

Logical DBMS Architecture
(www.dbmaker.com.tw/reference/manuals/tutorial/tutorial_03.html)

• External Level

External level can be also called as view level. It functions as a window on which application programs can work and end users can capture information and manipulate data what they want. The users of the level do not directly associate with the storage and system administration of data except for the system and database adminstrators. In the level, application programmer uses a HOST LANGUAGE: COBOL, PL/1, C and embedded in the host language is a DATA SUBLANGUAGE DSL.

For example:

External (PL/1)

DCL 1 EMPP,

2 EMP# CHAR(6),

2 SAL FIXED BIN(31);

External (COBOL)

01 EMPC.

02 EMPNO PIC X(6).

02 DEPTNO PIC X(4).

• Conceptual Level

Conceptual level, which is the first design phase of a DBMS, presents a logical view of the entire database as a whole. All the data from external level are collected in the central database in the level. There is a data definition language to manipulate the data based on what you need.
Data in the level is independent. Data definition language define the information content.

For example:

EMPLOYEE

EMPLOYEE_NUMBER CHARACTER (6)

DEPARTMENT_NUMBER CHARACTER (4)

SALARY NUMERIC (5)

• Internal Level

Internal level may also be referred to as the physical level. The collection of files permanently stored on secondary storage devices is known as physical database. The level is the closest to physical storage, and it provides a low-level description of the physical database and an interface between the operating system's file system and the record structures used in higher levels of abstraction. In the level, both record types and methods of storage are defined as well as how stored fields are represented, what physical sequence the stored records are in, and what other physical structures exist.

For example:

STORED_EMP LENGTH-18

REFIX TYPE=BYTE(6), OFFSET=0

EMP# TYPE=BYTE(6), OFFSET=6,INDEX=EMPX

DEPT# TYPE=BYTE(4), OFFSET=12

PAY TYPE=FULLWORD, OFFSET=16

Mappings

The above three levels are not separate but associated with each other. There are two types of mappings: conceptual/internal mapping and external/conceptual mapping.

Conceptual/internal mapping defines the association between the records and the fields of the conceptual level and the files and data structures of the internal level. If the structure of the stored database is changed, then the conceptual/ internal mapping must also be changed accordingly so that the view from the conceptual level remains constant.

External/conceptual mapping defines the association between a particular external view and the conceptual view. Although these two levels are similar, some elements found in a particular external view may be different from the conceptual view.

Reference:

http://en.wikipedia.org/wiki/ANSI-SPARC

http://www.dbmaker.com.tw/reference/manuals/tutorial/tutorial_03.html