【SQL】-- Chapter2 Database Environment(数据库环境)

         1  The Architecture of DBS

1.1 3-level architecture

1.2 DB Schemas & Instances

1.3 Data Independence

2  Database Languages

2.1 Data Definition Language (DDL)

2.2 Data Manipulation Language (DML)

3  Data Models & Conceptual Modeling  

3.1 Data Model 

3.2 Conceptual Modeling(概念建模)

4  Functions of a DBMS

4.1 Define Data via Data definition language (DDL, 数据定义语言):

4.2 Retrieve & Update Data via Data manipulation language (DML,数据操纵语言):

4.3 Controlled access to database (存取控制) may include:

4.4 Promote Data Independence via view mechanism(利用视图机制提升数据独立性)

4.5 Support for Data Communication(支持数据通信)

4.6 Utility Services(实用工具)

5  Multi-User DBMS Architectures

5.1 Teleprocessing (远程处理)

5.2 File-server(文件服务器)

​​​​​​​5.3  Traditional Two-Tier Client-Server (传统的两层客户机/服务器)

5.4 Three-Tier Client-Server (三层客户机/服务器)

---

1  The Architecture of DBS

1.1 3-level architecture

Schema means ‘the description of data’ (“模式”意为”“数据的描述”).

（1） External level （外模式 external schema）

• Consists of multiple user views (external schemas) of the DB (由多个用户视图(外模式)构成: 解决需要使用哪些数据的问题)
• Each user view includes the part of DB relevant to the user (每个用户视图包含与该用户相关的数据库部分)
• Users are unaware of what is out of their views (用户感觉不到其视图以外的数据)
• Different views may have different representations of the same data (相同数据在不同视图中可能有不同的表示)

（2） Conceptual level （概念模式 conceptual schema）

• Consists of a conceptual schema of  DB (What should be stored?) (由一个数据库概念模式构成, 包含整个数据库应存储的数据. 解决应该存储哪些数据的问题).
• contains logical representation of the entire DB, which includes (包含整个数据库的逻辑表示, 包括):
  • all entities, their attributes, and their relationships;
  • constraints on data;
  • security & integrity info.
• supports each user view, in which any data must be contained in, or derived from, the conceptual schema (支持每个用户视图, 其中的任何数据都必须源自概念模式)
• must not contain any storage-dependent details (概念模式中不应包含任何数据存储细节).

（3）  Internal level （内模式 internal schema）

• Consists of an internal schema of DB (How to store data?) (由一个数据库内模式构成, 解决如何存储数据的问题)
• contains physical representation  of the DB, which includes (包含数据库的物理表示, 包括):
  • storage space allocation for data and indexes(为数据和索引分配存储空间)
  • record description for storage (记录的存储描述)
  • record placement(记录的存放安排)
  • data compression & encryption(数据压缩和加密)

（4）Physical data organization

• Below the internal level there is a physical level, managed by OS and used by DBMS (内模式之下是一个物理层, 它由OS管理, 由DBMS使用)
• in which functions of DBMS and OS are not clear-cut (其中, DBMS和OS在功能上并没有严格界限),
  • Some DBMSs use access methods of OS (很多DBMS直接利用OS提供的数据存取方法(文件系统)),
  • while others create their own file organizations (而另一些DBMS则创建自己的文件组织).
• physical level consists of items only OS knows, such as (物理层由只有OS知道的项目构成, 例如):
  • how to realise sequencing (如何实现先后顺序),
  • whether fields of records are stored contiguously on disk(记录字段是否连续存放在磁盘上).

（5） mapping（映射）

DBMS is responsible for mapping  between these three types of schema (DBMS负责实现这三类模式之间的映射)
               Mapping means ‘transformation of data’ (映射的含义是数据转换)

For a DB, there are two types of mapping (数据库中有两类映射): 

• one conceptual/internal mapping (一个概念模式/内模式映射);
• multiple external/conceptual mappings (多个外模式/概念模式映射).

1.2 DB Schemas & Instances

It is important to distinguish between (注意区分下列两个概念)

• the description of the DB (数据库的描述)
• and the data in the DB (数据库中的数据).

The description of the DB is the DB schema(intension, 内涵) (数据库描述就是数据库模式(又称内涵)),

• which is specified during database design process (它是在数据库设计过程中定义的),
• and does not change frequently. (且不会频繁变化)
• The data in the DB may change frequently (而数据库中的数据通常会频繁变化)

The data in DB at a time is called a DB instance(extension, 外延) (某个时刻存放在数据库中的数据被称为一个数据库实例).

• Many DB instances can correspond to the same DB schema (多个数据库实例可对应同一个数据库模式).

1.3 Data Independence

（1）Logical Data Independence (逻辑数据独立性)

• refers to immunity(免疫) of external schemas to changes in conceptual schema (指外模式对概念模式变化的免疫:概念模式变化不影响外模式).
• Conceptual schema changes should not require to modify external schema or application programs (概念模式变化时不必修改外模式和应用程序).
• This is guaranteed by external/conceptual mappings (逻辑数据独立性由外模式/概念模式映射保证).

 

（2）Physical Data Independence (物理数据独立性)

• refers to immunity(免疫) of conceptual schemas to changes in internal schema. (指概念模式对内模式变化的免疫: 内模式变化不影响概念模式).
• Internal schema changes should not require to modify conceptual or external schemas (内模式变化时不必修改概念模式和外模式).
• This is guaranteed by conceptual/internal mapping (物理数据独立性由概念模式/内模式映射保证).

Data Independence and the 3-Level Architecture (数据独立性和三层体系结构)

2  Database Languages

2.1 Data Definition Language (DDL)

• Allows the DBA or user to describe and name entities, attributes, and relationships required for the app (DBA或用户用DDL描述并命名应用系统所需的实体、属性和联系),
  • plus any associated integrity and security constraints (也用来描述并命名完整性、安全性约束). 
• DDL is used to define a DB schema, or to modify an existing one (DDL用来定义或修改数据库模式).
• The result of executing DDL statements is object definitions stored in system catalog (data dictionary, data directory) (DDL语句的执行结果是存储在系统目录(数据字典、数据目录)中的对象定义).

2.2 Data Manipulation Language (DML)

• Provides basic data manipulation operations on data stored in the database (提供针对数据库中数据的基本数据操纵操作).
• Data manipulation operations usually include  (数据操纵操作通常包括):
  • insertion of new data into the database (向数据库插入新数据);
  • modification of data stored in the database (修改存放在数据库中的数据);
  • retrieval of data contained in the database (在数据库中查询所需数据);
  • deletion of data from the database (删除数据库中的数据).
• The part of a DML that involves data retrieval is called a query language. (DML中涉及数据查询的部分被称为查询语言)

3  Data Models & Conceptual Modeling  

3.1 Data Model 

Integrated collection of concepts for describing data, relationships between data, and constraints on the data, in an organization (描述数据、联系和约束的概念集合).

A data model comprises three components (数据模型三要素):

• structural part:
  • a set of rules by which DBs can be constructed    (结构成分: 构造数据库需遵循的规则集合)
• manipulative part:
  • operations that are allowed on the data  (操纵成分: 在数据上允许的操作集合).
• integrity rules:
  • ensures that the data is correct   (完整性规则: 保证数据的正确性).

 

Purpose: to represent data in an understandable way (用可理解的方式表示数据).
There are 3 categories of data models (按所描述数据的粒度进行分类，有三种数据模型):

• Object-based data models (基于对象的数据模型)  基于实体、属性、联系 概念
  • Entity-Relationship Data Model(实体-联系模型)
    • has emerged as one of the main techniques for database design (已成为数据库设计的主要技术之一),
  • Object-Oriented Data Model(面向对象模型)
    • extends the definition of entity to include not only attributes but also actions(behaviours) (扩充实体定义，使之不仅包含属性，而且包含行为).
• Record-based data models (基于记录的数据模型).
  • Relational Data Model(关系模型)
    • data & relationships are logically represented as tables, each table has some columns with a unique name (数据和联系在逻辑上都用表来表示：表含若干列，在同一张表中列名具有唯一性).
  • Network Data Model(网状模型)
    • data is represented as collections of records(数据表示成记录的集合)
    • relationships are explicitly represented by sets (联系用边集表示：边是记录之间的连接，用指针实现).
    • takes a graphic structure: record ànode, relationshipàedge (呈现图结构：记录→节点, 联系→边).
  • Hierarchical Data Model(层次模型)
    • is a restricted network model(受限网状模型).
    • again, data is represented as collections of records (数据表示成记录的集合),​​​​​​​
    • relationships are represented by sets (联系用边集表示).
    • but allows a node to have only one parent(一个节点只有一个双亲).
    • takes a tree structure:  record ànode , relationshipàedge (呈现树结构：记录→节点，联系→边).
• Physical data models (物理数据模型).
  • describe how data is stored in the computer,
  • represent info such as:
    • record structures(记录结构),
    • record orderings(记录顺序),
    • access paths(存取路径：顺序/随机存取？索引？).

​​​​​​​3.2 Conceptual Modeling(概念建模)

• Conceptual schema is the core of a system supporting all user views (概念模式是系统能够支持所有用户视图的核心机制),
• and should completely and accurately represent  organization’s data requirements (应该完整准确地代表企业的数据需求).
• Conceptual modeling, or conceptual database design, is the process of developing a model of info that is independent of implementation details (概念建模(概念结构设计)是开发与实现细节无关的信息模型的过程).

4  Functions of a DBMS

4.1 Define Data via Data definition language (DDL, 数据定义语言):

• Permits specification of data types(数据类型), data structures(数据结构) and data constraints (数据约束)
• All specifications are stored in the database for users to access: System Catalog (所有描述都存放在该数据库中供用户存取：系统目录)

4.2 Retrieve & Update Data via Data manipulation language (DML,数据操纵语言):

• Query Language (查询语言)

​​​​​​​4.3 Controlled access to database (存取控制) may include:

• A security system(安全性控制子系统)
  • Authorization Services(授权服务) : a mechanism to ensure that only authorized users can access the DB(保证仅合法用户能够存取数据库的机制:支持授权与权限检查)
• An integrity system(完整性控制子系统)
  • DB integrity means correctness & consistency of stored data (数据库完整性指数据的正确性和一致性：支持完整性定义与检查).
• A concurrency control system (并发控制子系统)
  • Transaction Support (事务支持): Transaction is  a series of actions (事务是一个操作序列: 并发控制的基本单位).
  • Concurrency Control Services (并发控制服务): enable multiple users to concurrently access shared data (允许多个用户同时存取共享数据:支持事务的启动、调度、回滚、重做、终止、提交)
• A recovery control system (恢复子系统)
  • a mechanism for recovering the DB when it is damaged (当数据库遭到破坏时恢复数据库的机制)
• A user-accessible catalog (系统目录):存取控制时查询相关信息

4.4 Promote Data Independence via view mechanism(利用视图机制提升数据独立性)

• a view is a subset of DB(视图是数据库的子集):
• Provides users with only the data they want or need to use (作用:只向用户提供其所需数据, 屏蔽不需要的数据)
• Provide a level of security(提供一个安全保护层)

4.5 Support for Data Communication(支持数据通信)

• to exchange info with other systems(与其他系统交换信息).

4.6 Utility Services(实用工具)

• Import facilities (导入工具)：to load the database from flat files(从文本文件装载数据库)
• Export facilities(导出工具)：to unload the database to flat files(将数据库中的数据输出到文本文件)
• Monitoring facilities(监视工具): to monitor database usage and operation(监视数据库的使用和操作)
• statistical analysis programs(统计分析工具): to examine performance or usage statistics(检查运行性能、统计使用频率)

5  Multi-User DBMS Architectures

5.1 Teleprocessing (远程处理)

• Traditional architecture (是传统架构)
• Single mainframe with a number of terminals (单主机、多终端结构)
• Central computer has very heavy burden of running app and DBMS (中央计算机负荷很重:既执行应用程序，又执行DBMS)

5.2 File-server(文件服务器)

• File-server is connected to several workstations across a network(若干工作站通过局域网与文件服务器相连)
• Database resides on file-server (数据库驻留在文件服务器上)
• DBMS and applications run on each workstation (DBMS和应用程序在每个工作站上运行)
• Disadvantages include (缺点):
  • Significant network traffic(网络交通繁忙)
  • Copy of DBMS on each workstation (每个工作站都要复制一个DBMS, 浪费空间)
  • Concurrency, recovery and integrity control are more complex (并发控制、恢复和完整性控制更复杂)

​​​​​​​5.3  Traditional Two-Tier Client-Server (传统的两层客户机/服务器)

• Server holds DB and DBMS (在服务器上存放数据库和DBMS).
• Client manages user interface and runs applications (客户机负责管理用户界面和运行应用程序).
• Advantages include (优点):
  • increased performance(性能高): Client and Server work in parallel (客户机和服务器可并行工作)
  • possible reduction in hardware costs(可能降低硬件开销): only server need to be powerful.
  • reduction in communication costs (降低了通信费用:只需传递结果)
  • increased consistency(增加了一致性:相对文件-服务器)   
• Alternative Client-Server Topologies:
  • (a) single client, single server;
  • (b) multiple clients, single server;
  • (c) multiple clients, multiple servers.

          

5.4 Three-Tier Client-Server (三层客户机/服务器)

• As apps become more and more complex, client side presented two problems preventing true scalability(随着应用程序变得越来越复杂，客户机端遇到两个阻碍系统伸缩性的问题)： 
  • ‘Fat’ client, requiring considerable resources on client’s computer to run effectively (胖客户机: 客户机上需要很多资源才能高效运行)
  • Significant client side administration overhead (客户机端的管理费用明显增加)
• By 1995, three-tier client-server architecture proposed (1995年提出了三层客户机-服务器结构):

• Advantages:
  • ‘Thin’ client, requiring less expensive hardware (瘦客户机，硬件价格低).
  • Application maintenance centralized (可集中维护应用系统).
  • Easier to modify or replace one tier without affecting others (易于修改或替换一层而不影响其他层).
  • Separating business logic from database functions makes it easier to implement load balancing (将商业逻辑和数据库功能分开，有助于实现负载均衡).