This article is gonna give a brief overview to the phases of database system design which is the kernel of database life cycle. The whole article here is discussing what we need to do and how to do in each phases of database design.
After each phase, we will get the corresponding deliverable–The so called schema. In the following section, we will introduce the data model first, and then the phases of database design. A data model is collection of concepts for describing the data in a database. While A schema is a description of a particular collection of data, using a given data model. The relationship between data model, schema, and phases of design are as follow:
Before we start the design process, we choose a data model for our database. Then, we do the design. In each step of our design process, we obtain the corresponding schema such as conceptual schema, logical schema .etc.
The following figure shows the database design and application development in parallel:
Lets start with data models.
Data Models
There are three kinds of data models whose abstraction level are from high to low:
- Conceptual model [e.g: ER-model]
- Logical model [e.g: Relational-model]
- Physical model
- Conceptual Model
A data model that is independent of all implementation details like target DBMS, storage structure, security, performance issue.
Example: ER model - Logical Model
A logical data model describes the data in as much detail as possible, without regard to how they will be physical implemented in the DBMS.
Examples: By ANSI -> Hierarchical Model, Network Model, Relational Model and Object Oriented Model - Physical Model
Data model that involved data management technology.
The Four Database Design Phases
There are four steps in database design procedure:
1. Requirements collection and analysis
Task: Analyse user requirements.
Deliverables: Data requirements, Functional requirements
2. Conceptual design
Task: Create conceptual model
Deliverables: Conceptual schema(Conceptual model) e.g: ER-Model
3. Logical design(Data model mapping)
Task: Maps conceptual model to the implemented logical model of target DBMS.
Deliverables: Logical schema(Logical model) e.g: Relational Schema
Remarks: What we need to do(focus) on in this phase: Integrity, Constraints, Normalization
Note1: You may assume that the logical schema is just a conceptual schema with more details
Note2: Logical design may or may not DBMS independent.
4. Physical design
Task: Determines how a database is implemented in a DBMS.The internal storage structures, file organizations, indexes, access paths, and physical design parameters are completed in this phase.
Deliverables: Internal schema(Physical model) e.g: File organization, Indexes
Remarks: What we need to do(focus on) in this phase: Denormalization, Indexing, Derived data, Storage structure, Partitioning, View, Security, Query Optimization, Backup, Recovery, Concurrency control, Access control
It’s hard to distinguish conceptual and logical model, the boundary is blur. It will be safe to think logical model as a conceptual model with more details.
Or, we can see it from another point of view:
Different DBMS are using different data model, we can consider these data model as logical model. For example, MongoDB is using document-oriented model, Oracle Berkeley DB XML is using XML model and MySQL is using relational model. These are all so called logical model. Note that the abstraction level of conceptual model is higher than that of logical model. So, it’s hard to transform from one logical model to another logical model. But, transforming from conceptual model to the three logical models above is fairy easy. This is one main difference between conceptual model and logical model.
This concludes the discussion.
Further Discussion
Further discussion of databases are all based on the phases of database design. For example: normalization and constraints checking resides in logical design; while denormalization, indexing and query optimization resides in physical design. We will give more discussion to these topics in other posts of kelvin.ink
近期评论