数据库建模与设计：逻辑设计第5版

5星 · 超过95%的资源需积分: 16 75 浏览量更新于2024-07-28 收藏 3.28MB PDF 举报

"Database Modeling and Design 5th Edition" 数据库建模与设计是应用设计师的主要任务，其核心是根据数据处理和结构化查询的需求来设计数据库的逻辑结构。在这个过程中，为特定供应商定义数据库表格被视为本书中的逻辑设计部分，尽管许多数据库从业者将这一步称为物理设计。这一领域涵盖了如何有效地组织和表示数据，以支持高效的数据操作和查询。在数据库建模中，主要涉及以下知识点： 1. **概念建模**：首先，需要通过实体-关系（ER）模型来表达业务领域的概念，如实体、属性和关系。这有助于理解数据的结构和业务规则。 2. **逻辑建模**：在概念模型的基础上，转化为关系模型，即确定表格、字段和键的关系。这一阶段涉及到范式理论，如第一范式（1NF）、第二范式（2NF）和第三范式（3NF），以消除数据冗余和保证数据一致性。 3. **物理建模**：虽然描述中提到的逻辑设计不完全等同于物理设计，但这一阶段包括选择适当的索引策略、分区、存储类型等，以优化性能。 4. **标准化与反规范化**：标准化是减少数据冗余的过程，但可能会增加查询复杂性。反规范化有时被用来优化查询性能，即使得某些数据在多个表中重复。 5. **数据库设计原则**：包括数据完整性（实体完整性、参照完整性和用户定义的完整性）、安全性、并发控制和事务管理，这些都是保证数据库稳定和可靠的关键因素。 6. **性能调优**：数据库设计不仅要考虑正确性，还要考虑效率。这可能涉及到查询优化、索引策略、数据分布和硬件配置。 7. **数据仓库与OLAP**：在大数据和分析背景下，数据库设计也包括数据仓库和联机分析处理（OLAP）的设计，这些通常需要特殊的数据建模技术，如星型或雪花型模式。 8. **XML和非关系型数据库**：随着NoSQL数据库的流行，数据库建模也扩展到了XML和JSON等非结构化数据的处理，需要理解和应用不同的查询语言，如XQuery和XPath。 9. **数据挖掘与分析**：现代数据库设计还需要考虑数据准备和预处理，以便进行数据挖掘和分析，这通常涉及到数据清洗、转换和整合。 10. **业务智能和元数据**：数据库设计与业务流程紧密相关，元数据管理可以帮助理解数据的来源、含义和使用方式，支持企业决策。《Database Modeling and Design 5th Edition》这本书可能深入探讨了以上这些方面，为读者提供了全面的数据库设计理论和实践指导。通过学习，读者可以提升在数据库设计领域的专业技能，更好地满足应用需求和性能优化。

a. Conceptual data modeling. The data requirements are

analyzed and modeled by using an ER or UML dia-

gram that includes many features we will study in

Chapters 2 and 3, for example, semantics for optional

relationships, ternary relationships, supertypes, and

subtypes (categories). Processing requirements are

typically specified using natural language expressions

or SQL commands along with the frequency of occur-

rence. Figure 1.2 (S

tep II.a) shows a

possible ER

Information Requirements

Determine requirements

Logical Design

[multiple views]

Model Integrate views

[single view]

Transform to SQL tables

Normalize

Physical Design

Select indexes

[special requirements]

Denormalize

[else]

Implementation

Implement

[else]

Monitor and detect changing requirements

[defunct]

Figure 1.1 The database life

cycle.

4 Chapter 1 INTRODUCTION

c. Transformation of the conceptual data model to SQL

tables. Based on a categorization of data modeling con-

structs and a set of mapping rules, each relationship

and its associated entities are transformed into a set of

DBMS-specific candidate relational tables. We will

show these transformations in standard SQL in Chapter

5. Redundant tables are eliminated as part of this pro-

cess. In our example, the tables in Step II.c of Figure 1.2

are the r

esult of transformation of the integrated ER

model in Step II.b .

d. Norm

aliza tion of tables. Given a

table (R), a set of

attributes (B) is functio nally dependent on another

set of attributes (A) if, at each instant of time, each

A value is associated with exactly one B value. Func-

tional dependencies (FDs) are derived from the con-

ceptual d ata m ode l d iagra m a nd the semantics of

data relationships in the requirements analy sis. They

represent the dependencies among data elements

that are unique i dent ifiers (keys ) of e ntiti es. Addi-

tional FDs, which repre sen t the dependencies

between key and nonkey attributes within entities,

canbederivedfromtherequirementsspecification.

Candidate relational tables associated with all

derived FDs are normalized (i.e., modified by

decomposing or spli tting tables into smaller tables)

using standard normalization techniques. Finally,

redundancies in the data that occur in normalized

candidate tables are analyzed further for possibl e

elimination, with the constraint that data integrity

must be preserved. An example of normalization of

the Salesperson table into the new Salesperson and

SalesVacations tables is shown in

Figure 1.2 from

tep II.c t

o Step II.d.

We note here that database tool vendors tend to use

the term logical model to refer to

the conceptual data

model, and they use the term physical model to refer

to the DBMS-specific implementation model (e.g.,

SQL tables). We also note that many conceptual data

models are obtained not from scratch, but from the

process of reverse engineering from an existing

DBMS-specific schema (Silberschatz et al., 2010).

Chapter 1 INTRODUCTION 7

III. Physical design. The physical design step involves the

selection of indexes (access methods), partitioning,

and clustering of data. The logical design methodology

in Step II simplifies the approach to designing large rela-

tional databases by reducing the number of data

dependencies that need to be analyzed. This is accom-

plished by inserting the conceptual data modeling and

integration steps (Steps II.a and II.b of Figure 1.2)

into

the t

raditional relational design approach. The objective

of these steps is an accurate representation of reality.

Data integrity is preserved through normalization of the

candidate tables created when the conceptual data

model is transformed into a relational model. The pur-

pose of physical design is to then optimize performance.

As part of the physical design, the global schema can

sometimes

be refined in limited

ways to reflect pro-

cessing (query and transaction) requirements if there

are obvious large gains to be made in efficiency. This

is called denormalization. It consists of selecting domi-

nant processes on the basis of high frequency, high vol-

ume, or explicit priority; defining simple extensions to

tables that will improve query performance; evaluating

total cost for query, update, and storage; and consider-

ing the side effects, such as possible loss of integrity.

This is particularly important for online analytical pro-

cessing (OLAP) applications.

IV.Database implementation, monitoring, and modifica-

tion. Once the design is completed, the database can be

created through implementation of the formal schema

using the data definition language (DDL) of a DBMS. Then

the data manipulation language (DML) can be used to

query and update the database, as well as to set up indexes

and establish constraints, such as referential integrity.

The language SQL contains both DDL and DML con-

structs; for example, the create table command represents

DDL, and the select command represents DML.

As the database begins operation, monitoring

indicates whether performance requirements are being

met. If they are not being satisfied, modifications should

be made to improve performance. Other modifications

may be necessary when requirements change or end

8 Chapter 1 INTRODUCTION

剩余334页未读，继续阅读

songhefei2010

粉丝: 4
资源: 10

数据库建模与设计：逻辑设计第5版

database modeling and design

数据库系统概念 第五版

Database Modeling and Design, Fifth Edition: Logical Design

Database Modeling and Design.5th.2011

Database Modeling And Design , 4ed(2005) - PDF

Microsoft Excel Data Analysis and Business Modeling, 5th Edition.pdf

英文原版-Microsoft Excel Data Analysis and Business Modeling 5th Edition

Data Modeling And Relational Database Design

DBeaver Data Modeling and Design Guide

robot modeling and control 2nd edition pdf

最新资源

数据库系统概念第五版