Transcript Chapter 1 introduction

Database System Concepts
Computer science
and technology
department
Liping Zhang
E-mail: [email protected]
Textbook：
Book name: Database System Concepts,Fourth Edition
Author: Abraham Silberschatz,Henry E Korth,S.Sudarshan
Press: China Machine Press
References book：


Book name: An Introduction to Database Systems
Author: C. J.Date
Press: China Machine Press
Book name: A First Course in Database Systems
Author: Jeffrey D.ullman Jennifer Widom
Press: China Machine Press
References book：

Book name : 数据库系统概念（第三版）
Author : 萨师煊 王珊
Press: Higher Education Press

Book name : 数据库系统教程
Author : 王能斌
Press: Electronics industry

Book name : 数据库系统基础
Author : 冯玉才
Press: 华中科技大学
Database system concepts
Teaching outline:
 2.practice in the laboratory
 1. in the classroom
 Requirement:
Chapter 1 Introduction
1.preparation
Chapter 2 Entity-Relationship Model 2.review
Chapter 3 Relational Model
3.learn by yourself
Chapter 4 SQL
Delphi7.0
Chapter 5 Integrity and Security
Chapter 6 Relational-Database Design SQLServer2000
 Scores:
Chapter 7 Query Optimization
70%+30%
Chapter 8 Transactions
absence -3
Chapter 9 Concurrency Control
Chapter 10 Recovery System

Chapter 1 introduction





1.1 What is a database system? 

1.2 What is a database?
1.3 Purpose of database systems.

1.4 Database languages

1.5 The three levels of the
architecture
1.5.1 The three levels
1.5.2 Mappings
1.5.3 Instances and schemas
1.6 Transaction management
1.7 The Database Management
System
1.8 Application Architectures
1.9 Distributed processing
1.1 What is a database system?









Banking:accounts, loans,baking transactions.
Airlines: reservations ,schedule information.
Universities:, student information,course registration, grades.
Credit card transactions: purchases on credit cards, generation
of monthly statements.
Telecommunication: keeping records of calls made.
Finance
Sales:customer,product,purchase information.
Manufacturing:management of supply chain.
Human resources:employees,salaries.
1.1 What is a database system?


A database system is basically a computerized record-keeping
system; i.e.,it is a computerized system whose overall purpose is
to store information and to allow users to retrieve and update that
information on demand.
A database system involves four major components:
data
hardware
software
users
DBS=DB+DBMS+APPLICATION+DBA+USER
1.Data
The data in the database—at least in a large system—will be
both integrated and shared.
Example:
employee
name
enrollment name
employee
address
course
department
…
salary
…
?
department
Personnel Department Use the employee
information for
Education Department different purpose
1.Data
By
integrated: we mean that the database can be
thought of as a unification of several otherwise
distinct files, with any redundancy among those files
at least partly eliminated.
By
shared: we mean that individual pieces of data in
the database can be shared among different users, in
the sense that each of those users can have access to
the same piece of data, possibly for different purpose.
2.Hardware

The hardware components of the system consist of:
1.The secondary storage volumes, device controllers,
I/o channels, and so forth;and
2.The hardware processor(s) and associated main
memory
3.Software
USER
Between the physical database itself—i.e.,
the data as physically stored—and the users
of the system is a layer of software, know
variously as the database manger or
database server or, most commonly,

…
USER
AS
DBMS
OS
the database management system(DBMS).
DATABASE
All requests for access to the database are handled by the
DBMS. One general function provided by the DBMS is thus the
shielding of database users from hardware—level details.

4.Users
① Application programmers: responsible writing database
application programs in some programming language.
② End users: end users interact with the system from online
workstations or terminals.
③ Database administrator(DBA):
Schema definition storage
Structure and access-method definition
Schema and physical-organization modification
Granting of authorization for data access
Routine maintenance
1.2 What is a database
It is customary to refer to the data in a database as “persistent”.
We say that data in the database “persists” because, once it has
been accepted by the DBMS for entry into the database in the first
place, it can subsequently be removed from the database only by
some explicit request to the DBMS,not as a mere side effect of
some program completing execution.
A database is a collection of persistent data that is used by the
application systems of some given enterprise.
1.3 Purpose of database system

Keeping organizational information in a file—processing system
has a number of major disadvantages.
1. Data redundancy and inconsistency
2. Difficulty in accessing data
3. Data isolation
Data are scattered in various
files&files may be in different
formats
4. Integrity problems
5. Atomicity problems
6. Concurrent—access anomalies
7. Security problems
Consistency constraints
Example:balance of
bank>=$25
1. Data redundancy and
inconsistency
name
address
department salary
Hayes
Round Hidd
Account
500
Loan
Johnson
Perryridge
employee
Account
780
Hayes
Loan
Smith
Perryridge
Loan
name
course
department
Hayes
OS
Account
Johnson
DB
Account
Smith
DS
Loan
1200
?
inconsistency
Hayes
Account
enrollment
2. Difficulty in accessing data
Requirement:
Find out the names of all customers who live within
wuhan.(the list of all customers)
resolves
Extract the needed information form the
list of all customers
Write a new necessary application program
Find out the names of all customers who live within
wuhan and have an account balance of $1000 or more.
5. Atomicity problems
(1)
$50
$50
$50$50$50 Account B
$50
Account A
$500
$50 $50 $50
x
$100
(2)
$500-$50=$450
$50
?
$100
6. Concurrent—access anomalies
Account A $500
Husband $100
Wife $50
Read $500
Read $500
$500-$50=$450
$500-$100=$400
Write $450
Write $400
Read $450
Read $400
Write $350
Write $350
$400 / $450 X
$400-$50=$350
$450-$100=$350
1.3 Purpose of database system
Benefits of the database approach:
1. The data can be shared
2. Redundancy can be reduced
3. Inconsistency can be avoided (to some extent)
4. Transaction support can be provided.
5. Integrity can be maintained
6. Security can be enforced
7. Conflicting requirements can be balanced
8. Standards can be enforced
1.4 Database languages

A database system provides two different types of
languages: one to specify the database schema, and
the other to express database queries and updates.
Database Language
Data-Definition Language
Data-Manipulation Language
Procedural DMLs Declarative DMLs
1.5.1 The three levels
The
ANSI/SPARC architecture is divided into three levels— internal,
conceptual, and external
The
external level is the one closest to the users. it is consists of
many occurrences of each of many types of external record.
( external schema)
The
conceptual level is a level of indirection between the other two.
it is consists of many occurrences of each of many types of
conceptual record.(conceptual schema)
The
internal level is the one closes to physical storage.
it is consists of many occurrences of each of many types of internal
record.(internal schema)
1.5.1 The three levels
External level
External
schema
External
schema
External
schema
(individual user views)
Conceptual level
(community user views)
Internal level
(storage views)
Conceptual
Schema
Internal
Schema
Fig 1.1 The three levels of the architecture
1.5.1 The three levels
Example:
External(PL/I)
External(COBOL)
DCL 1 EMPP,
01 EMPC.
2 EMP# CHAR(6),
02 EMPNO PIC X(6).
2 SAL FIXED BIN(31);
02 DEPTNO PIC X(4).
Conceptual
EMPLOYEE
EMPLOYEE NUMBER CHARACTER (6)
DEPARTMENT_NUMBER CHARACTER (4)
SALARY
Internal
STORED EMP BYTES=20
PREFIX TYPE=BYTE(6), OFFSET=0
EMP#
TYPE=BYTE(6), OFFSET=6, INDEX=EMPX
DEPT#
TYPE=BYTE(4), OFFSET=12
PAY
TYPE=FULLWORD, OFFSET=16
1.5.1 The three levels
Fig 1.2
Detailed
system
architecture
1.5.2 Mappings

conceptual/internal mapping and several
external/conceptual mappings：
1.the conceptual/internal mapping defines the
correspondence between the conceptual view and the
stored database; it specifies how conceptual records
and fields are represented and the internal level.
2.an external/conceptual mapping defines the
correspondence between a particular external view and
the conceptual view.
1.5.2 Mappings
External
schema
External
schema
external/conceptual
Conceptual
Schema
conceptual/internal
mapping
Internal
Schema
External
schema
1.5.2 Mappings

Data independence:
1.physical data independence is the ability to modify the
physical schema without causing application programs
to be rewritten. (conceptual/internal mapping)
2.logical data independence is the ability to modify the
logical schema without causing application programs to
be rewritten. (external/conceptual)
1.5.3 Instances and schemas
The collection of information stored in the database at
a particular moment is called an instance of the
database.
 The overall design of the database is called the
database schema.

database schema
int i
instance of the database
i=2
Student={sn,name,sex}
sn
01
student 03
name
carol
sex
female
bob
male
1.6 Transaction Management
Atomicity Consistency Isolation
Durability (ACID)

A transaction is a collection of operations that
performs a single logical function in a database
application.

Each transaction is a unit of both atomicity and
consistency.
1.7 The Database Management
System

DBMS is the software that handles all access to the
database.
1.A user issues an access request, using some
particular data sublanguage (SQL)
2.The DBMS intercepts that request and analyzes it
3.The DBMS inspects, in turn, the external schema for
that user, the corresponding external/conceptual
mapping, the conceptual schema,the
conceptual/internal mapping,and the storage
structure definition.
4.The DBMS execute the necessary operations on the
stored database.
1.7 The Database Management
System

The functions of the DBMS：
Data definition
Data manipulation
Optimization and execution
Data security and integrity
Data recovery and concurrency
Data dictionary
Performance
Fig 1.3 major DBMS functions and
components
1.8 Application Architectures
A database system can be regarded as having a very
simple two-part structure consisting of a server and a
set of clients. The server is just the DBMS itself. The
clients are various applications that run on top of the
DBMS.
1.9 Distributed processing
Distributed processing means that distinct machines
can be connected together into a communication
network such as internet ,such that a single dataprocessing task can span several machines in the
network.
Exercises:
1.Explain the following terms:
DBMS Data inconsistency
Data view Data instance
Database schema Physical schema
Logical schema
Physical data independence
Logical data independence
2. P23 : 1.1 1.3 1.5 1.8