dbms technology and application

Database technology and application1

DBMS TECHNOLOGY AND DBMS TECHNOLOGY AND APPLICATIONAPPLICATION

Zeng zhongping

Email: [email protected]

Course Web page: http://www.hust.edu.cn

Database technology and application

Why Study Databases??

Shift from computation to information at the “low end”: scramble to webspace (a mess!) at the “high end”: scientific applications

Datasets increasing in diversity and volume. Digital libraries, interactive video, Human Genome p

roject, EOS project ... need for DBMS exploding

DBMS encompasses most of CS OS, languages, theory, “AI”, multimedia, logic

?

Science Computing

Processing Control

Data

Processing(70%)


Why Study DBMS?

We often use DBMS…….. Banking: all transactions Airlines: reservations, schedules Universities: registration, grades Sales: customers, products, purchases Online retailers: order tracking, customized recommendations Manufacturing: production, inventory, orders, supply chain Human resources: employee records, salaries, tax

deductions But DBMS just like a black box.

Try to open the black box Database file system/pages/records/bytes/bits Principles of DBMS and implement technologies

Learn more about DBMS


More opportunities in the job marketMore opportunities in the job market

Career developmentCareer development

Computer skillsComputer skills

Benefits for…


Text Books and References

1. Raghu Ramakrishnan, Database Management Systems, McGraw Hill, 3rd edition, 2003 (text book).

2. R. Elmasri, S.B. Navathe, Fundamentals of Database Systems, 4th edition, Addison-Wesley, 2004.

3. A. Silberschatz, H.F. Korth, S. Sudarshan, Database System Concepts, McGraw Hill, 4th edition, 2002.

4. 萨师煊，王珊：数据库系统概论 ( 第四版 ) ，高等教育出版社， 2000. 中国人民大学

5. 周立柱、冯建华等《 SQL Server 数据库原理 -- 设计与实现》


采用直观的方式描述所有概念，并以结构清晰的图示和例子代替形式化的证明。

以银行数据库实例生动地解释重要概念，增强了本书的易读性。

专门用三章来介绍特定的商品化数据库系统 Oracle 、 Microsoft SQL Server 和 IBM DB2 的细节。

配有关于网状模型和层次模型的联机附录 ( 英文 ) 。


1950 ，负责全校经济和管理类公共数学课的教学工作，文科数学。

83 年，与王珊第一版数据库泰斗多次奖项多次规划教材使用学生众多


Course Outline

Introduction to Database Management Systems

Entity-Relationship Model

The Relational Data Model

Relational Algebra

SQL

Relational Database Design: Normalization

MS SQL server


Grading

Assignments 10 % Experiments 40 %Final Exam 60 %


How to learn well?How to learn well?

网络资源网络资源精品课程精品课程网络视频网络视频


Introduction to DBMSIntroduction to DBMS

What Is a DBMS?What Is a DBMS?

What About DBMS What About DBMS History?History?

Why Use DBMS?Why Use DBMS?

Functions of DBMS?Functions of DBMS?

DBMS Architecture?DBMS Architecture?


What Is a DBMS?What Is a DBMS?

Data (Data ( 数据数据 ))

Database (Database ( 数据库数据库 ))

DBMS (DBMS ( 数据库管理系统数据库管理系统 ))

DBS(DBS( 数据库系统数据库系统 ))


Data

The representative forms of information, including facts, concepts, rules, or any other kind of knowledge.

numbers, characters, images or other methods of recording.

represent values that can be stored, processed, and transmitted by electronic systems ， especially the computer.


Data instance

学生档案中的学生记录

（李明，男， 1972 ，江苏，计算机系， 1990 ）数据的解释

语义：学生姓名、性别、出生年月、籍贯、所在系别、入学时间

解释：李明是个大学生， 1972 年出生，江苏人， 1990

年考入计算机系数据的形式不能完全表达其内容


Database

学生登记表

学号姓名年龄性别系名年级

95004 王小明 19 女社会学 95

95006 黄大鹏 20 男商品学 95

95008 张文斌 18 女法律学 95

… … … … … …

A very large, integrated collection of data stored in the computer.

Low reductdanceShare abilityData independenceIntegrity management and control


A very large, integrated collection of data stored in the computer

Models real-world enterprise. Entities (e.g., students, courses) Relationships (e.g., Tarkan is taking CENG302)

A Database Management System (DBMS) is a software package designed to store and manage databases.

Database Management System (DBMS)


OracleSQL serverSybaseMysqlDB2 INFORMIXVFP

Database management system examples


Data Definition

DDL,Data Definition Language

Data Manipulation

DML,Data Manipulation Language

Operation Management

Setup and Maintain

DBMS FUNCTION


Provide User Interface Provide Data Manipulation Language Provide Data Definition Language Provide some tools

Manage databaseMaintain database

DBMS FUNCTION(continue)


C,VC,C++VBPOWERBUILDERDELPHIJAVAASP/JSPODBC/JDBCDatabase language (DDL,DML)

Development tool kits/language


开放数据库互连（ ODBC ）是 MICROSOFT 提出的数据库访问接口标准。

开放数据库互连定义了访问数据库的 API 一个规范，这些 API 独立于不同厂商的 DBMS ，也独立于具体的编程语言。

Open Database Connect

OracleSql server sybase access

ODBC API

ODBC 驱动

C++ VB PB JAVA


Java Database Connect(JDBC)Java 数据库连接，（ Java Database Connectivity ，简称 JDBC ）是 Java 语言中用来规范客户端程序如何来访问数据库的应用程序接口，提供了诸如查询和更新数据库中数据的方法。 JDBC 也是 Sun Microsystems 的商标。它 JDBC 是面向关系型数据库的。


硬件

操作系统DBMS

应用开发工具

应用系统


Database Management System (DBMS)

DBMS contains information about a particular enterprise Collection of interrelated data -- Database Set of programs to access the data An environment that is both convenient and efficient to use

Database applications Banking: all transactions Airlines: reservations, schedules Universities: registration, grades Sales: customers, products, purchases Online retailers: order tracking, customized

recommendations Manufacturing: production, inventory, orders, supply chain Human resources: employee records, salaries, tax

deductions Databases touch all aspects of our lives


DatabaseDatabase

Operation systemOperation system

DBMSDBMS

Application systemApplication system

DBADBA

UsersUsers

Database System (DBS)

数据库系统一般由数据库、操作系统、数据库管理系统（及其开发工具）、应用系统、数据库管理员（ DBA ）和用户构成。


USER USER USER

Application

DBMS

OS

DB

DBA

用户用户用户

应用系统

数据库管理员

Development Tools kits

开发工具

Database system


Database system Architecture

The architecture of a database systems is greatly influenced by the underlying computer system on which the database is running:

CentralizedClient-serverParallel (multi-processor)Distributed Browser/Server


Centralized （集中式系统） DBMS 和应用程序以及与用户终端进行通信的软件等都运

行在一台宿主计算机上，所有的数据处理都是在宿主计算机中进行。宿主计算机一般是大型机、中型机或小型机。应用程序和 DBMS之间通过操作系统管理的共享内存或应用任务区来进行通信， DBMS利用操作系统提供的服务来访问数据库。终端通常是非智能的，本身没有处理能力。

集中系统的主要优点是：具有集中的安全控制，以及处理大量数据和支持大量并发用户的能力。集中系统的主要缺点是：购买和维持这样的系统一次性投资太大 ,并且不适合分布处理。

PC( 个人计算机系统 ) 与大型系统不同，通常个人计算机（微机）上的 DBMS功

能和数据库应用功能是结合在一个应用程序中的，这类 DBMS （如 FoxPro 、 Acssce ）的功能灵活，系统结构简洁，运行速度快，但这类 DBMS 的数据共享性、安全性、完整性等控制功能比较薄弱。


在客户 / 服务器 ( 简记为 C/S ）结构的数据库系统中，数据处理任务被划分为两部分：一部分运行在客户端，另一部分运行在服务器端。客户端负责应用处理，数据库服务器完成 DBMS 的核心功能。

这种模型中，客户机上都必须安装应用程序和工具，客户端比较庞大、负担比较重，而且系统安装、维护、升级和发布困难，从而影响效率。

Client-server( 客户 / 服务器系统 )


一个分布式数据系统由一个逻辑数据库组成，整个逻辑数据库的数据，存储在分布于网络中的多个结点上的物理数据库中。在当今的客户 / 服务器结构的数据库系统中，服务器的数目可以是一个或多个。当系统中存在多个数据库服务器时就形成了分布系统。

Distributed （分布式系统）


Distributed system


随着 Internet 的迅速普及，出现了三层客户机 / 服务器模型：客户机→应用服务器→数据库服务器。这种系统称为浏览器 / 服务器（ Browser/Server ，简记为 B/S ）系统。

Browser/Server （浏览器 / 服务器系统）


Why We Need a DBMS?

the increasing of the great number of information resourc

es KB,MB,GB,TB,PB,EB,ZB,YB.....

Traditional data management technology can not meet

the increasing needs

Massive benefits through data analysis and processing Weather forecast

airspace

Bank and stocks market

DBMS is the best way to solve the above questions


Why Use a DBMS?

Data independence and efficient access.( 数据独立性和高效访问 ) Reduced application development time.（减少应用程序开发时间） Data integrity and security.（数据完整性和安全性） Uniform data administration.（统一的数据管理） Concurrent access, recovery from crashes.（并发控制和灾难恢复）


Data management history

Data management the control of data handling operations--such as

acquisition, analysis, translation, coding, storage, retrieval, and distribution of data

Development Manual management (1940s—mid 1950s)File system management (1950s—mid 1960s)Database management (1960s --)


Manual management

1940s -1950s Focus on

scientific computing Hardware

no direct access storage device Software system

no operation system Data processing

batch processing


File management

1950s and early 1960s: Data processing using magnetic tapes for storage

Tapes provide only sequential access Punched cards for input

1960s File system （ In the early days, database applications were built directly on top of file systems ）

Demand scientific management

Hardware

disk 、 disk drum Software

file system Processing

OLAP 、 batch processing


Drawbacks of using file systems

Drawbacks of using file systems to store data: Data redundancy and inconsistency

Multiple file formats, duplication of information in different files Difficulty in accessing data

Need to write a new program to carry out each new task Data isolation — multiple files and formats Integrity problems

Integrity constraints (e.g. account balance > 0) become “buried” in program code rather than being stated explicitly

Hard to add new constraints or change existing ones


Drawbacks of using file systems(Cont.)

Atomicity of updates Failures may leave database in an inconsistent state

with partial updates carried out Example: Transfer of funds from one account to

another should either complete or not happen at all Concurrent access by multiple users

Concurrent accessed needed for performance Uncontrolled concurrent accesses can lead to

inconsistencies Example: Two people reading a balance and

updating it at the same time Security problems

Hard to provide user access to some, but not all, data Database systems offer solutions to all the above problems


History of Database Systems

Late 1960s and 1970s: Hard disks allow direct access to data Network and hierarchical data models in widesp

read use Ted Codd defines the relational data model

Would win the ACM Turing Award for this work

IBM Research begins System R prototype UC Berkeley begins Ingres prototype

High-performance (for the era) transaction processing


History (cont.)

1980s: Research relational prototypes evolve into commercial

systems SQL becomes industrial standard

Parallel and distributed database systems Object-oriented database systems

1990s: Large decision support and data-mining applications Large multi-terabyte data warehouses Emergence of Web commerce

2000s: XML and XQuery standards Automated database administration


Database - Advantages

Reduced data redundancy （冗余） Reduced updating errors and increased consistency

（一致性） Greater data integrity and independence from application

s programs （完整性和独立性） Improved data access to users through use of host and q

uery languages （访问能力） Improved data security （数据安全） Reduced data entry, storage, and retrieval costs （减少

数据存取开销） Facilitated development of new applications program

（为开发新程序提供便利


Summary

DBMS - how come today

Demand driven by application requirements

Software development

Hardware development


1 、试述数据、数据库、数据库管理系统、数据库系统的概念；

2 、使用数据库有什么好处？3 、试述文件系统和数据库系统的区别与联系。4 、名词解释：数据定义语言；数据操纵语言。5 、试述数据库系统的功能。6 、以图书馆管理系统为例，试述开发一个数据库

应用程序的大致步骤。

Assignment


概念模型和三级模式结构概念模型和三级模式结构


shift from the real world to Concept schema

客观对象的抽象过程 ---两步抽象

现实世界中的客观对象抽象为概念模型；

把概念模型转换为某一 DBMS 支持的数据模型。

概念模型是现实世界到机器世界的一个中间层次。

数据库中用数据模型来抽象、表示和处理现实世界中的数据。


DBMS DBMS ArchitectureArchitecture

data

User view

Computer view

DBMS- schema and data modelshift from the real world to digital world


Concept schema

概念模型的用途概念模型用于信息世界的建模是现实世界到机器世界的一个中间层次是数据库设计的有力工具数据库设计人员和用户之间进行交流的语言

对概念模型的基本要求较强的语义表达能力，能够方便、直接地表达应用中的各

种语义知识简单、清晰、易于用户理解。


redundant and inconsistent

flexibility

multiple databases


Schemas

External schema for user views defines the view of the data presented to the

application programs

Conceptual schema integrates external schemata the logical structure of the database The primary objective of this conceptual schema is to

provide a consistent definition of the meanings and interrelationship of data which can be used to integrate, share, and manage the integrity of data.

Internal schema that defines physical storage structures describes the internal formats of the data stored in

the database


Advantage of Three Schemas

layered architecture


View of Data

An architecture for a database system


Data Independence

Applications insulated from how data is structured and stored.

Logical data independence: Protection from changes in logical structure of data.

Physical data independence: Protection from changes in physical structure of data.

One of the most important benefits of using a DBMS!


Levels of Abstraction

Many external schemata, single conceptual(logical) schema and physical schema. External schemata describe h

ow users see the data.

Conceptual schema defines logical structure

Physical schema describes the files and indexes used.

Schemas are defined using DDL; data is modified/queried using DML.

Physical Schema

Conceptual Schema

ExternalSchema 1

ExternalSchema 3

ExternalSchema 2


Levels of Abstraction A major purpose of a database system is to provide users with

an abstract view of the data. The system hides certain details of how the data are stored and maintained.

Physical level: describes how a record (e.g., customer) is stored.

Logical level: describes what data are stored in database, and the relationships among the data.

type customer = record

customer_id : string; customer_name : string;customer_street : string;customer_city : integer;

end; View level: application programs hide details of data types.

Views can also hide information (such as an employee’s salary) for security purposes.


Instances and Schemas

Similar to types and variables in programming languages Schema – the logical structure of the database

Example: The database consists of information about a set of customers and accounts and the relationship between them

Analogous to type information of a variable in a program Physical schema: database design at the physical level Logical schema: database design at the logical level

Instance – the actual content of the database at a particular point in time Analogous to the value of a variable

Physical Data Independence – the ability to modify the physical schema without changing the logical schema Applications depend on the logical schema In general, the interfaces between the various levels and

components should be well defined so that changes in some parts do not seriously influence others.


Structure of a DBMS

A typical DBMS has a layered architecture.

This is one of several possible architectures; each system has its own variations.

Query Optimizationand Execution

Relational Operators

Files and Access Methods

Buffer Management

Disk Space Management

DB

These layersmust considerconcurrencycontrol andrecovery


Structure of a DBMS


How data is organized How data is organized in the database?in the database?

Data Models


Data Models

A data model is a collection of concepts for describing data. A schema is a description of a particular collection of data, using the

given data model. requirement

能比较真实地模拟现实世界容易为人所理解便于在计算机上实现

The relational model of data is the most widely used model today. Main concept: relation, basically a table with rows and columns. Every relation has a schema, which describes the columns, or fiel

ds.


Data Models Type

Entity-Relationship data model (mainly for database design)

Relational model Object-based data models (Object-oriented and Obj

ect-relational) Semistructured data model (XML) Other older models:

Network model Hierarchical model


Summary

Concept model External schema Concept schema Internal schema

Data model Relational model


Concept schema

概念模型的用途概念模型用于信息世界的建模是现实世界到机器世界的一个中间层次是数据库设计的有力工具数据库设计人员和用户之间进行交流的语言

对概念模型的基本要求较强的语义表达能力，能够方便、直接地表达应用中的各

种语义知识简单、清晰、易于用户理解。


definitions

实体（ Entity ） “thing” or object

属性（ Attribute ） Property of (the entities of) an entity set. Attributes are simple values, e.g. integers

or character strings. 实体型（ Entity Type ）

a class of entities with the same attributes 实体集（ Entity Set ）

同型实体的集合称为实体集


Example

Entity set Beers has two attributes, name and manf (manufacturer).

Each Beers entity has values for these two attributes, e.g. (Bud, Anheuser-Busch)

Beers

name manf


definitions(conti.)

码（ Key ）唯一标识实体的属性集称为码。

域（ Domain ）属性的取值范围称为该属性的域。


definitions(conti.)

联系（ Relationship ）

an association between two or more entities that is of particular i

nterest

现实世界中事物内部以及事物之间的联系在信息世界中反映为实体内

部的联系和实体之间的联系。

实体型间联系两个实体型一对一联系（ 1:1 ）　

三个实体型一对多联系（ 1:n ）

一个实体型多对多联系（ m:n ）


两个实体型间的联系

一对一联系　如果对于实体集 A 中的每一个实体，实体集 B 中至多有一

个实体与之联系，反之亦然，则称实体集 A 与实体集 B 具

有一对一联系。记为 1:1 。实例

班级与班长之间的联系：

一个班级只有一个正班长

一个班长只在一个班中任职


两个实体型间的联系 ( 续 )

一对多联系如果对于实体集 A 中的每一个实体，实体集 B 中有 n 个实

体（ n≥0 ）与之联系，反之，对于实体集 B 中的每一个实体，实体集 A 中至多只有一个实体与之联系，则称实体集A 与实体集 B 有一对多联系

记为 1:n 实例

班级与学生之间的联系：一个班级中有若干名学生，每个学生只在一个班级中学习


两个实体型间的联系 ( 续 )

多对多联系（ m:n ）如果对于实体集 A 中的每一个实体，实体集 B 中有 n 个实

体（ n≥0 ）与之联系，反之，对于实体集 B 中的每一个实体，实体集 A 中也有 m 个实体（ m≥0 ）与之联系，则称实体集 A 与实体 B 具有多对多联系。记为 m:n

实例课程与学生之间的联系：一门课程同时有若干个学生选修一个学生可以同时选修多门课程


多个实体型间的联系 ( 续 )

多个实体型间的一对多联系若实体集 E1 ， E2 ， ... ， En存在联系，对

于实体集 Ej （ j=1 ， 2 ， ... ， i-1 ， i+

1 ， ... ， n ）中的给定实体，最多只和 Ei

中的一个实体相联系，则我们说 Ei 与 E1 ，E2 ， ... ， Ei-1 ， Ei+1 ， ... ， En 之间的联系是一对多的。


多个实体型间的联系 ( 续 )

实例课程、教师与参考书三个实体型

如果一门课程可以有若干个教师讲授，使用若干本参考书，每一个教师只讲授一门课程，每一本参考书只供一门课程使用课程与教师、参考书之间的联系是一对多的

多个实体型间的一对一联系多个实体型间的多对多联系


Degrees of relationship

ManMan WomanWoman

CustomerCustomer OrderOrder

CourseCourse SubjectSubject

One-to-one (1:1)

One-to-many (1:n)

Many-to-many (n:m)

NOTE: Every many to many relationship consists of two one to many relationships working in opposite directions


概念模型的表示方法

概念模型的表示方法很多

实体－联系方法 (E-R 方法 ) 用 E-R 图来描述现实世界的概念模型 E-R 方法也称为 E-R 模型


Why use ER Diagrams ?

provides a global quick reference to an organization’s data structures.

can be used individually to design an Information System’s (IS) data structure

can be used with Data Flow Diagrams to provide a more comprehensive IS logical design.


ERD Development Process

Identify the entitiesDetermine the attributes for each entitySelect the primary key for each entityEstablish the relationships between the

entitiesDraw an entity modelTest the relationships and the keys


Identify the entities

实体型用矩形表示，矩形框内写明实体名。 Entity set = rectangle.

学生教师


Determine the attributes for each entity

属性用椭圆形表示，并用无向边将其与相应的实体连接起来

Attribute = oval, with a line to the rectangle representing its entity set.

学生

学号年龄性别姓名


Key Attributes

Certain attributes identify particular facts within an entity, these are known as KEY attributes.

The different types of KEY attribute are: Primary Key

Composite Primary Key Foreign Key


Key Definitions

Primary Key: One attribute whose value can uniquely identify a

complete record (one row of data) within an entity.

Composite Primary Key A primary key that consists of two or more

attribute within an entity.Foreign Key

A copy of a primary key that exists in another entity for the purpose of forming a relationship between the entities involved.


Establish the relationships between the entities

A relationship connects two or more entity sets.

It is represented by a diamond, with lines to each of the entity sets involved.


E-R图 ( 续 )

联系联系本身：用菱形表示，菱形框内写明联系名，并用无向边分别与有关实体连接起来，同时在无向边旁标上联系的类型（ 1:1 、 1:n或m:n ）

联系的属性：联系本身也是一种实体型，也可以有属性。如果一个联系具有属性，则这些属性也要

用无向边与该联系连接起来


联系的表示方法

实体型1

联系名

实体型2

1

1

1:1 联系

实体型1

联系名

实体型2

m

n

m:n 联系

实体型1

联系名

实体型2

1

n

1:n 联系


联系的表示方法 ( 续 )

实体型1

联系名

m n

同一实体型内部的 m:n 联系

实体型1

联系名

实体型2

1

m

多个实体型间的 1:n 联系

实体型3

n


联系的表示方法示例

班级

班级 - 班长

班长

1

1

1:1 联系

课程

选修

学生

m

n

m:n 联系

班级

组成

学生

1

n

1:n 联系


联系的表示方法示例 ( 续 )

职工

领导

1 n

同一实体型内部的 1:n 联系

课程

讲授

教师

1

m

多个实体型间的 1:n 联系

参考书

n


联系属性的表示方法

课程

选修

学生

m

n

成绩


ERD Development for a Beer Bar？


Example

Drinkers addrname

Beers

manfname

Bars

name

license

addr

Note:license =beer, full,none

Sells Bars sell somebeers.

Likes

Drinkers likesome beers.Frequents

Drinkers frequentsome bars.


ERD Development for a library？


同一实体集内各实体间的联系

一对多联系实例

职工实体集内部具有领导与被领导的联系某一职工（干部）“领导”若干名职工一个职工仅被另外一个职工直接领导这是一对多的联系

一对一联系多对多联系


Data Models

A collection of tools for describing Data Data relationshipsData semanticsData constraints


Data Models Type

Relational model Entity-Relationship data model (mainly for database

design) Object-based data models (Object-oriented and

Object-relational) Semi-structured data model (XML) Other older models:

Network model Hierarchical model


Relational Model

A relational database is based on the relational model and uses a collection of tables to represent both data and relationships among those data.

Example of tabular data in the relational modelAttributes


Instance of Students Relation

Students( sid: string, name: string, login: string,

age: integer, gpa: real )

sid name login age gpa

53666 Jones jones@cs 18 3.4

53688 Smith smith@ee 18 3.2

53650 Smith smith@math 19 3.8


A Sample Relational Database


Example: University Database

Conceptual schema: Students(sid: string, name: string, login: string,

age: integer, gpa:real) Courses(cid: string, cname:string, credits:integer) Enrolled(sid:string, cid:string, grade:string)

Physical schema: Relations stored as unordered files. Index on first column of Students.

External Schema (View): Course_info(cid:string,enrollment:integer)


Data Manipulation Language (DML) Language for accessing and manipulating the data or

ganized by the appropriate data model. DML also known as query language Retrieval of information stored in the database Insertion of new information into the databse Deletion of information from the database Modification of information stored in the database

Two classes of languages Procedural – user specifies what data is required

and how to get those data Declarative (nonprocedural) – user specifies what

data is required without specifying how to get those data

SQL is the most widely used query language


Data Definition Language (DDL)

Specification notation for defining the database schemaExample: create table account (

account-number char(10), balance integer)

DDL compiler generates a set of tables stored in a data dictionary Data dictionary contains metadata （元数据） (i.e., data about dat

a) Database schema Data storage and definition language

Specifies the storage structure and access methods used Integrity constraints

Domain constraints Referential integrity (references constraint in SQL) Assertions

Authorization


SQL

SQL: widely used non-procedural language Example: Find the name of the customer with customer_id 192-83-7

465select customer.customer_namefrom customerwhere customer.customer_id = ‘192-83-7465’

Example: Find the balances of all accounts held by the customer with customer_id 192-83-7465

select account.balancefrom depositor, accountwhere depositor.customer_id = ‘192-83-7465’ and

depositor.account_number = account.account_number

Application programs generally access databases through one of Language extensions to allow embedded SQL Application program interface (API) (e.g., ODBC/JDBC) which allow

SQL queries to be sent to a database


Database Design

The process of designing the general structure of the database:

Logical Design – Deciding on the database schema. Database design requires that we find a “good” collection of relation schemas. Business decision – What attributes should we record

in the database? Computer Science decision – What relation schemas

should we have and how should the attributes be distributed among the various relation schemas?

Physical Design – Deciding on the physical layout of the database


The Entity-Relationship Model

Models an enterprise as a collection of entities and relationships Entity: a “thing” or “object” in the enterprise that is

distinguishable from other objects Described by a set of attributes

Relationship: an association among several entities Represented diagrammatically by an entity-relationship

diagram:


Object-Relational Data Models

Extend the relational data model by including object orientation and constructs to deal with added data types.

Allow attributes of tuples to have complex types, including non-atomic values such as nested relations.

Preserve relational foundations, in particular the declarative access to data, while extending modeling power.

Provide upward compatibility with existing relational languages.


XML: Extensible Markup Language

Defined by the WWW Consortium (W3C) Originally intended as a document markup language

not a database language The ability to specify new tags, and to create nested

tag structures made XML a great way to exchange data, not just documents

XML has become the basis for all new generation data interchange formats.

A wide variety of tools is available for parsing, browsing and querying XML documents/data


Storage Management

Storage manager is a program module that provides the interface between the low-level data stored in the database and the application programs and queries submitted to the system.

The storage manager is responsible to the following tasks: Interaction with the file manager Efficient storing, retrieving and updating of data

Issues: Storage access File organization Indexing and hashing


Query Processing

1. Parsing and translation

2. Optimization

3. Evaluation


Query Processing (Cont.)

Alternative ways of evaluating a given query Equivalent expressions Different algorithms for each operation

Cost difference between a good and a bad way of evaluating a query can be enormous

Need to estimate the cost of operations Depends critically on statistical information about

relations which the database must maintain Need to estimate statistics for intermediate results

to compute cost of complex expressions


Transaction Management

A transaction is a collection of operations that performs a single logical function in a database application

Transaction-management component ensures that the database remains in a consistent (correct) state despite system failures (e.g., power failures and operating system crashes) and transaction failures.

Concurrency-control manager controls the interaction among the concurrent transactions, to ensure the consistency of the database.


Database Users

Users are differentiated by the way they expect to interact with the system

Application programmers – interact with system through DML calls

Sophisticated users – form requests in a database query language

Specialized users – write specialized database applications that do not fit into the traditional data processing framework

Naive users – invoke one of the permanent application programs that have been written previously Examples, people accessing database over the web, bank

tellers, clerical staff


Database Administrator

Coordinates all the activities of the database system; the database administrator has a good understanding of the enterprise’s information resources and needs.

Database administrator's duties include: Schema definition Storage structure and access method definition Schema and physical organization modification Granting user authority to access the database Specifying integrity constraints Acting as liaison with users Monitoring performance and responding to

changes in requirements


数据库管理员做什么？

配置数据库服务器环境管理数据库的逻辑对象结构配置数据库的对象权限制定数据库的性能优化策略数据库的备份还原策略数据库的异构协同结构

如：安装、升级数据库服务器

如：安装、升级数据库服务器

如：创建数据库、创建表如：创建数据库、创建表为数据库的用户分配权限，监控用户对数据库的存取访问

为数据库的用户分配权限，监控用户对数据库的存取访问监视和优化数据库的性能监视和优化数据库的性能制定数据库备份计划，灾难出现时对数据库信息进行恢复，维护介质上的存档或者备份数据

制定数据库备份计划，灾难出现时对数据库信息进行恢复，维护介质上的存档或者备份数据如数据转换、数据迁移如数据转换、数据迁移


Overall System Structure


Data definition languageData Manipulation Language (DML) Database administrator role

HomeworkHomework


预习预习

Relational database

Sql language

dbms technology and application

Documents