Transcript 1 Introduction to DBMS

DBMS TECHNOLOGY AND APPLICATION
Zeng zhongping
Email: [email protected]
Course Web page: http://www.hust.edu.cn
1
Database technology and application
Why Study Databases??
?
 Shift from computation to information
at the “low end”: scramble to webspace (a mess!)
Science
 at the “high
end”: Computing
scientific applications
 DatasetsProcessing
increasing in Control
diversity and volume.
 DigitalData
libraries,
interactive video, Human Genome
Processing(70%)
project, EOS project
 ... need for DBMS exploding

 DBMS encompasses most of CS

OS, languages, theory, “AI”, multimedia, logic
Database technology and application
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
Database technology and application
Benefits for…
 More opportunities in the job market
 Career development
 Computer skills
Database technology and application
Text Books and References
1.
2.
3.
4.
5.
Raghu Ramakrishnan, Database Management Systems,
McGraw Hill, 3rd edition, 2003 (text book).
R. Elmasri, S.B. Navathe, Fundamentals of Database
Systems, 4th edition, Addison-Wesley, 2004.
A. Silberschatz, H.F. Korth, S. Sudarshan, Database
System Concepts, McGraw Hill, 4th edition, 2002.
萨师煊，王珊：数据库系统概论(第四版) ，高等教育出版社，
2000.中国人民大学
周立柱、冯建华等《SQL Server数据库原理--设计与实现》
5
Database technology and application
 采用直观的方式描述所有概念，并
以结构清晰的图示和例子代替形式
化的证明。
 以银行数据库实例生动地解释重要
概念，增强了本书的易读性。
 专门用三章来介绍特定的商品化数
据库系统Oracle、Microsoft SQL
Server和IBM DB2的细节。
 配有关于网状模型和层次模型的联
机附录 (英文)。
Database technology and application
 1950，负责全校经济





Database technology and application
和管理类公共数学课
的教学工作，文科数
学。
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
8
Database technology and application
Grading
 Assignments
 Experiments
 Final Exam
10 %
40 %
60 %
9
Database technology and application
How to learn well?
 网络资源
 精品课程
 网络视频
Database technology and application
Introduction to DBMS
What Is a DBMS?
Functions of DBMS?
Why Use DBMS?
What About DBMS History?
DBMS Architecture?
Database technology and application
What Is a DBMS?
 Data (数据)
 Database (数据库)
 DBMS (数据库管理系统)
 DBS(数据库系统)
Database technology and application
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.
Database technology and application
Data instance
 学生档案中的学生记录
（李明，男，1972，江苏，计算机系，1990）
 数据的解释

语义：学生姓名、性别、出生年月、籍贯、所在系别、入
学时间

解释：李明是个大学生，1972年出生，江苏人，1990年
考入计算机系

数据的形式不能完全表达其内容
Database technology and application
Database
A very large, integrated collection of data stored in the computer.
学生登记表
学 号
姓 名
年 龄
性 别
系 名
年 级
95004
王小明
19
女
社会学
95
95006
黄大鹏
20
男
商品学
95
95008
张文斌
18
女
法律学
95
…
…
…
…
…
…
Low reductdance
Share ability
Data independence
Integrity management and control
Database technology and application
Database Management System (DBMS)
 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 technology and application
Database management system examples
 Oracle
 SQL server
 Sybase
 Mysql
 DB2
 INFORMIX
 VFP
Database technology and application
DBMS FUNCTION
 Data Definition

DDL,Data Definition Language
 Data Manipulation

DML,Data Manipulation Language
 Operation Management
 Setup and Maintain
Database technology and application
DBMS FUNCTION(continue)
 Provide User Interface

Provide Data Manipulation Language
Provide Data Definition Language

Provide some tools

 Manage database
 Maintain database
Database technology and application
Development tool kits/language
 C,VC,C++
 VB
 POWERBUILDER
 DELPHI
 JAVA
 ASP/JSP
 ODBC/JDBC
 Database language (DDL,DML)
Database technology and application
Open Database Connect
 开放数据库互连（ODBC）是MICROSOFT提出
的数据库访问接口标准。
 开放数据库互连定义了访问数据库的API一个规
范，这些API独立于不同厂商的DBMS，也独立于
具体的编程语言。
C++
VB
PB
JAVA
ODBC API
ODBC 驱动
Sql server
Database technology and application
Oracle
sybase
access
Java Database Connect(JDBC)
Java数据库连接，（Java Database Connectivity，简称JDBC）
是Java语言中用来规范客户端程序如何来访问数据库的应用程序
接口，提供了诸如查询和更新数据库中数据的方法。JDBC也是
Sun Microsystems的商标。它JDBC是面向关系型数据库的。
Database technology and application
应用系统
应用开发工具
DBMS
操作系统
硬件
Database technology and application
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

Database technology and application
Database System (DBS)
 Database
 Operation system
 DBMS
 Application system
 DBA
 Users
数据库系统一般由数据库、操作系统、数据
库管理系统（及其开发工具）、应用系统、
数据库管理员（DBA）和用户构成。
Database technology and application
Database system
用户
用户
用户
USER
USER
USER
Application
应用系统
开发工具
DBMS
DBA
OS
DB
Database technology and application
Development
Tools kits
数据库管理员
Database system Architecture
The architecture of a database systems is greatly
influenced by the underlying computer system on
which the database is running:
 Centralized
 Client-server
 Parallel
(multi-processor)
 Distributed
 Browser/Server
Database technology and application
 Centralized（集中式系统）
DBMS和应用程序以及与用户终端进行通信的软件等都运
行在一台宿主计算机上，所有的数据处理都是在宿主计算
机中进行。宿主计算机一般是大型机、中型机或小型机。
应用程序和DBMS之间通过操作系统管理的共享内存或应用
任务区来进行通信，DBMS利用操作系统提供的服务来访问
数据库。终端通常是非智能的，本身没有处理能力。
集中系统的主要优点是：具有集中的安全控制，以及处
理大量数据和支持大量并发用户的能力。集中系统的主要
缺点是：购买和维持这样的系统一次性投资太大,并且不适
合分布处理。
 PC(个人计算机系统)
与大型系统不同，通常个人计算机（微机）上的DBMS功
能和数据库应用功能是结合在一个应用程序中的 ，这类
DBMS（如FoxPro、Acssce）的功能灵活，系统结构简洁，
运行速度快，但这类DBMS的数据共享性、安全性、完整性
等控制功能比较薄弱。
Database technology and application
Client-server(客户/服务器系统)
 在客户/服务器(简记为C/S）
结构的数据库系统中，数据
处理任务被划分为两部分：
一部分运行在客户端，另一
部分运行在服务器端。客户
端负责应用处理，数据库服
务器完成DBMS的核心功能。
 这种模型中，客户机上都必
须安装应用程序和工具，客
户端比较庞大、负担比较重，
而且系统安装、维护、升级
和发布困难，从而影响效率。
Database technology and application
Distributed（分布式系统）
一个分布式数据系统由一个逻辑数据库组成，
整个逻辑数据库的数据，存储在分布于网络中的
多个结点上的物理数据库中。在当今的客户/服务
器结构的数据库系统中，服务器的数目可以是一
个或多个。当系统中存在多个数据库服务器时就
形成了分布系统。
Database technology and application
Distributed system
Database technology and application
Browser/Server（浏览器/服务器系统）
随着Internet的迅速普及，出现了三层客户机/服务
器模型：客户机→应用服务器→数据库服务器。这种
系统称为浏览器/服务器（Browser/Server，简记为
B/S）系统。
Database technology and application
Why We Need a DBMS?
 the increasing of the great number of information
resources
 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
Database technology and application
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.
（并发控制和灾难恢复）
Database technology and application
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 --)
Database technology and application
Manual management
 1940s -1950s
 Focus on
 scientific computing
 Hardware
 no direct access storage device
 Software system
 no operation system
 Data processing
 batch processing
Database technology and application
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


Hardware


disk、disk drum
Software


scientific management
file system
Processing

OLAP、batch processing
Database technology and application
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
Database technology and application
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

Database technology and application
History of Database Systems
 Late 1960s and 1970s:




Hard disks allow direct access to data
Network and hierarchical data models in
widespread 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
Database technology and application
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 technology and application
Database - Advantages
 Reduced data redundancy（冗余）
 Reduced updating errors and increased consistency（一





致性）
Greater data integrity and independence from
applications programs （完整性和独立性）
Improved data access to users through use of host and
query languages （访问能力）
Improved data security（数据安全）
Reduced data entry, storage, and retrieval costs（减少数
据存取开销）
Facilitated development of new applications program
（为开发新程序提供便利
Database technology and application
Summary

DBMS - how come today
 Demand
driven by application requirements
 Software
development
 Hardware
development
Database technology and application
Assignment
 1、试述数据、数据库、数据库管理系统、数据库





系统的概念；
2、使用数据库有什么好处？
3、试述文件系统和数据库系统的区别与联系。
4、名词解释：数据定义语言；数据操纵语言。
5、试述数据库系统的功能。
6、以图书馆管理系统为例，试述开发一个数据库
应用程序的大致步骤。
Database technology and application
概念模型和三级模式结构
Database technology and application
shift from the real world to Concept schema
数据库中用数据模型来抽象、表示和处
理现实世界中的数据。
 客观对象的抽象过程---两步抽象

现实世界中的客观对象抽象为概念模型；

把概念模型转换为某一DBMS支持的数据模型。
概念模型是现实世界到机器世界的一个中间层次。
Database technology and application
DBMS- schema and data model
shift from the real world to digital world
DBMS
Architecture
User view
data
Computer
view
Database technology and application
Concept schema
 概念模型的用途

概念模型用于信息世界的建模

是现实世界到机器世界的一个中间层次

是数据库设计的有力工具

数据库设计人员和用户之间进行交流的语言
 对概念模型的基本要求

较强的语义表达能力，能够方便、直接地表达应用中的各
种语义知识

简单、清晰、易于用户理解。
Database technology and application
flexibility
redundant and inconsistent
Database technology and application
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
Database technology and application
Advantage of Three Schemas
layered architecture
Database technology and application
View of Data
An architecture for a database system
Database technology and application
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!
Database technology and application
Levels of Abstraction
 Many external schemata,
single conceptual(logical)
schema and physical
schema.



External schemata describe
how users see the data.
Conceptual schema defines
logical structure
Physical schema describes
the files and indexes used.
External
Schema 1
External
Schema
2
External
Schema 3
Conceptual Schema
Physical Schema
 Schemas are defined using DDL; data is modified/queried using DML.
Database technology and application
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.
Database technology and application
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.

Database technology and application
Structure of a DBMS
 A typical DBMS has a
layered architecture.
 This is one of several
possible architectures;
each system has its
own variations.
These layers
must
consider
concurrency
control and
recovery
Query Optimization
and Execution
Relational Operators
Files and Access Methods
Buffer Management
Disk Space Management
DB
Database technology and application
Structure of a DBMS
Database technology and application
How data is organized in the
database?
Data Models
Database technology and application
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
fields.
Database technology and application
Data Models Type
 Entity-Relationship data model (mainly for database




design)
Relational model
Object-based data models (Object-oriented and
Object-relational)
Semistructured data model (XML)
Other older models:
 Network model
 Hierarchical model
Database technology and application
Summary
 Concept model



External schema
Concept schema
Internal schema
 Data model

Relational model
Database technology and application
Concept schema
 概念模型的用途

概念模型用于信息世界的建模

是现实世界到机器世界的一个中间层次

是数据库设计的有力工具

数据库设计人员和用户之间进行交流的语言
 对概念模型的基本要求

较强的语义表达能力，能够方便、直接地表达应用中的各
种语义知识

简单、清晰、易于用户理解。
Database technology and application
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）

同型实体的集合称为实体集
Database technology and application
Example
name
manf
Beers
 Entity set Beers has two attributes, name and
manf (manufacturer).
 Each Beers entity has values for these two
attributes, e.g. (Bud, Anheuser-Busch)
Database technology and application
definitions(conti.)
 码（Key）
 唯一标识实体的属性集称为码。
 域（Domain）
 属性的取值范围称为该属性的域。
Database technology and application
definitions(conti.)
联系（Relationship）
an association between two or more entities that is of particular
interest
现实世界中事物内部以及事物之间的联系在信息世界中反映为实体内
部的联系和实体之间的联系。
实体型间联系
两个实体型
一对一联系（1:1）
三个实体型
一对多联系（1:n）
一个实体型
多对多联系（m:n）
Database technology and application
两个实体型间的联系
 一对一联系

如果对于实体集A中的每一个实体，实体集B中至多有一个
实体与之联系，反之亦然，则称实体集A与实体集B具有一
对一联系。记为1:1。

实例
班级与班长之间的联系：
一个班级只有一个正班长
一个班长只在一个班中任职
Database technology and application
两个实体型间的联系 (续)
 一对多联系

如果对于实体集A中的每一个实体，实体集B中有n个实体
（n≥0）与之联系，反之，对于实体集B中的每一个实体，
实体集A中至多只有一个实体与之联系，则称实体集A与实
体集B有一对多联系
记为1:n

实例
班级与学生之间的联系：
一个班级中有若干名学生，
每个学生只在一个班级中学习
Database technology and application
两个实体型间的联系 (续)
 多对多联系（m:n）

如果对于实体集A中的每一个实体，实体集B中有n个实体
（n≥0）与之联系，反之，对于实体集B中的每一个实体，
实体集A中也有m个实体（m≥0）与之联系，则称实体集A
与实体B具有多对多联系。记为m:n

实例
课程与学生之间的联系：
一门课程同时有若干个学生选修
一个学生可以同时选修多门课程
Database technology and application
多个实体型间的联系(续)
 多个实体型间的一对多联系

若实体集E1，E2，...，En存在联系，对于实
体集Ej（j=1，2，...，i-1，i+1，...，n）中
的给定实体，最多只和Ei中的一个实体相联
系，则我们说Ei与E1，E2，...，Ei-1，
Ei+1，...，En之间的联系是一对多的。
Database technology and application
多个实体型间的联系(续)

实例
课程、教师与参考书三个实体型
如果一门课程可以有若干个教师讲授，使用若干
本参考书，每一个教师只讲授一门课程，每一本
参考书只供一门课程使用
课程与教师、参考书之间的联系是一对多的
 多个实体型间的一对一联系
 多个实体型间的多对多联系
Database technology and application
Degrees of relationship
One-to-one (1:1)
Man
Woman
One-to-many (1:n)
Customer
Order
Many-to-many (n:m)
Course
Subject
NOTE: Every many to many relationship consists of two one to
many relationships working in opposite directions
Database technology and application
概念模型的表示方法
 概念模型的表示方法很多
 实体－联系方法(E-R方法)


用E-R图来描述现实世界的概念模型
E-R方法也称为E-R模型
Database technology and application
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.
Database technology and application
ERD Development Process
 Identify the entities
 Determine the attributes for each entity
 Select the primary key for each entity
 Establish the relationships between the
entities
 Draw an entity model
 Test the relationships and the keys
Database technology and application
Identify the entities
 实体型


用矩形表示，矩形框内写明实体名。
Entity set = rectangle.
学生
Database technology and application
教师
Determine the attributes for each entity
 属性


用椭圆形表示，并用无向边将其与相应的实体连接
起来
Attribute = oval, with a line to the rectangle
representing its entity set.
学生
学号
Database technology and application
姓名
性别
年龄
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
Database technology and application
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.
Database technology and application
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.
Database technology and application
E-R图(续)
 联系

联系本身：用菱形表示，菱形框内写明联系名，
并用无向边分别与有关实体连接起来，同时在无向
边旁标上联系的类型（1:1、1:n或m:n）

联系的属性：联系本身也是一种实体型，也可以
有属性。如果一个联系具有属性，则这些属性也要
用无向边与该联系连接起来
Database technology and application
联系的表示方法
实体型1
实体型1
实体型1
1
1
m
联系名
联系名
联系名
1
n
n
实体型2
实体型2
实体型2
1:1联系
1:n联系
m:n联系
Database technology and application
联系的表示方法(续)
实体型1
实体型1
1
m
联系名
联系名
m
n
实体型2
实体型3
多个实体型间的1:n联系
Database technology and application
n
同一实体型内
部的m:n联系
联系的表示方法示例
班级
1
班级
m
1
班级-班长
1
课程
组成
选修
n
n
班长
学生
学生
1:1联系
1:n联系
m:n联系
Database technology and application
联系的表示方法示例(续)
课程
职工
1
1
讲授
领导
m
n
教师
参考书
多个实体型间的1:n联系
Database technology and application
n
同一实体型内
部的1:n联系
联系属性的表示方法
课程
m
选修
n
学生
Database technology and application
成绩
ERD Development for a Beer Bar？
Database technology and application
Example
name
addr
name
Bars
Beers
Sells
license
Note:
license =
beer, full,
none
Frequents
name
Drinkers
Database technology and application
manf
Likes
Bars sell some
beers.
Drinkers like
some beers.
Drinkers frequent
some bars.
addr
ERD Development for a library？
Database technology and application
同一实体集内各实体间的联系
 一对多联系

实例
职工实体集内部具有领导与被领导的联系
某一职工（干部）“领导”若干名职工
一个职工仅被另外一个职工直接领导
这是一对多的联系
 一对一联系
 多对多联系
Database technology and application
Data Models
 A collection of tools for describing
 Data
 Data
relationships
 Data semantics
 Data constraints
Database technology and application
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
Database technology and application
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.
Attributes

Example of tabular data in the relational model
Database technology and application
Instance of Students Relation
Students( sid: string, name: string, login: string,
age: integer, gpa: real )
sid
53666
53688
53650
name
Jones
Smith
Smith
Database technology and application
login
jones@cs
smith@ee
smith@math
age
18
18
19
gpa
3.4
3.2
3.8
A Sample Relational Database
Database technology and application
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)
Database technology and application
Data Manipulation Language (DML)
 Language for accessing and manipulating the data
organized 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
Database technology and application
Data Definition Language (DDL)
 Specification notation for defining the database schema
Example: 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
data)
 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
Database technology and application
SQL
 SQL: widely used non-procedural language
Example: Find the name of the customer with customer_id 19283-7465
select customer.customer_name
from customer
where 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.balance
from depositor, account
where 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 technology and application
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
Database technology and application
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:
Database technology and application
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.
Database technology and application
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
Database technology and application
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
Database technology and application
Query Processing
1. Parsing and translation
2. Optimization
3. Evaluation
Database technology and application
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

Database technology and application
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 technology and application
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 technology and application
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
Database technology and application
数据库管理员做什么？
 配置数据库服务器环境
 管理数据库的逻辑对象结构
如：安装、升级数据库服
务器
 配置数据库的对象权限
 制定数据库的性能优化策略
 数据库的备份还原策略
 数据库的异构协同结构
Database technology and application
如：创建数据库、创建表
为数据库的用户分配权限，
监控用户对数据库的存取
访问
监视和优化数据库的性能
制定数据库备份计划，灾
难出现时对数据库信息进
行恢复，维护介质上的存
如数据转换、数据迁移
档或者备份数据
Overall System Structure
Database technology and application
Homework
 Data definition language
 Data Manipulation Language (DML)
 Database administrator role
Database technology and application
预习
 Relational database
 Sql language
Database technology and application