Transcript Chapter 1: Introduction

Database Principles
LiaoNing ShiHua University
Textbooks
 Required:
 A First Course in Database Systems,2nd, by Jeffrey
D.Ullman Jennifer Widom.
 Recommended:
 王珊,萨师煊. 数据库系统概论(第四版). 高等教育出版社,
2006 年
 Books on SQL Server 2000 or 2005
 You may also want:
 Books on design and programming : Powerbuilder,
PowerDesigner, UML, VB, Delphi, VC, JAVA, C#, ASP,
PHP, JSP etc.
A First Course In Database Systems
1.2
Why Using English Textbook ?
 More computer English words
 More advanced and newest information
 Better information retrieval capability
 More international communication chance
A First Course In Database Systems
1.3
What to learn in this unit ?
 Basic theories (basic)
 System and database design (designer)
 Application Programming (programmer)
 Database management and maintenance
(database manager)
A First Course In Database Systems
1.4
Syllabus
 Background and history of database management
systems
 Fundamentals of using a database management
systems: SQL Server2000.
 Database modeling
 Relational model: theoretical background and
operations
 Database Language SQL
 Fundamentals of database management systems:
transactions and security etc.
 Database system design and practice
A First Course In Database Systems
1.5
Chapter 1
The Worlds of Database Systems
Evolution of Database Systems
Architecture of DBMS and DBS
Future of Database Systems
1.1 Evolution of Database Systems
1.1.1 Some Basic notions
 Data
 Database (DB)
 Database Management System (DBMS)
 Database System (DBS).
 Database Administrator (DBA)
A First Course In Database Systems
1.7
Basic Notions
 Data
 Distinct pieces of information represented in a form suitable
for processing by computer.
 Data can exist in a variety of forms -- as digital numbers, text,
image, sound, video and etc.
 Database (DB)
 In essence, it’s nothing more than a collection of information
that exists over a long period of time.
 In common parlance, it refers to a collection of data managed
by a database management system (DBMS) or just database
system (DBS).
A First Course In Database Systems
1.8
Example of Data and Database
A First Course In Database Systems
1.9
Another example: University Database
Students
Sid
Sname
Ssex
Sage
sdept
0121001
Tom
M
21
Cs
0121002
Mary
F
20
Cs
0121025
Jack
M
18
Cs
0110033
Smith
M
19
Ma
0211010
Alane
F
22
Is
Enrolled
Courses
cid Cname
credit
sid
cid
grade
1
Programmin
g
4
0121002
4
92
0121002
3
88
2
English
6
0110033
1
70
3
Physics
4
0211010
1
85
4
Dbms
4
0121002
2
95
A First Course In Database Systems
1.10
DBMS
 Database Management System (DBMS)
 A collection of programs that enables you to store,
modify, and extract information from a database.
 There are many different types of DBMSs, ranging from
small systems that run on personal computers to huge
systems that run on mainframes.
A First Course In Database Systems
1.11
Basic functions of DBMS
 Data definition
 Allow creating new databases and specify their schema (logical
structure of the data), via data definition language (DDL).
 Data manipulation
 Allow efficient access to the data, such as querying and modifying
the data, via query language or data manipulation language (DML).
 Operation management of DB
 Support the storage of very large amounts of data over a long
period of time, assuring data integrity, keeping data secure from
accident or unauthorized use. Support concurrent, atomic access
to data.
 Creation and maintenance of DB
 Such as inputting and transferring of Data, DB backup and
recovery, performance monitoring, audit and analysis etc.
A First Course In Database Systems
1.12
Database System (DBS)
 Database System (DBS).
 DB, DBMS, Development Tools, DB applications, DB Administrator (DBA)
and users
User
User
Application Systems
DB applications
Developing Tools
Developing Tools
DBMS, Compiling System
Operating System
DBMS
Hardware
DBA
OS
DBMS in Computer System
DB
A First Course In Database Systems
1.13
Database Administrator
 DBA
 Person who is responsible for management and
maintenance of DB.
 Tasks
 Database contents and structures
 Storage structure and access methods
 Database security and integration
 Monitor and improve database performance
A First Course In Database Systems
1.14
1.1.2 History of Data management
 File systems
 Problems: limited support to definition of data schema, no directly support to
DML (Data Manipulate Language), no support to efficient concurrent, secure
access etc.
 Early DBMS
 The first commercial DBMS appeared in the late 1960’s, evolved from file
systems.
 The early DBMS’s used several data models for describing structure of DB:
hierarchical model and network model.
 Problems: no support to high-level query languages.
A First Course In Database Systems
1.15
History of Data management (cont.）
 Relational DBMS
 A famous paper by Ted Codd in 1970, “A relational model for large
shared data banks” in Comm. ACM.
 Data organized as tables called relations.
 Unlike the user of early DBS, user of a relational system won’t be
concerned with the storage structure, and queries are expressed in a
very high-level language (SQL).
 Today used in most DBMS's.
A First Course In Database Systems
1.16
Relational Model
 Based on tables, such as:
Tuples
(rows)
A First Course In Database Systems
Attributes
(column headers)
acct #
name
balance
12345
Sally
1000.21
34567
Sue
285.48
…
…
…
1.17
Query Languages
Department
Employee
Name
Dept
Dept
SELECT Manager
FROM Employee, Department
WHERE Employee.name = "Clark Kent”
AND Employee.Dept = Department.Dept ;
A First Course In Database Systems
1.18
Manager
History of Data Model

Data model:
 abstraction of data features in real world into computer world.
60’s
Hierarchical model
Network model
70's
80's
Choice for most new
applications
Relational model
90’s
Object Bases model
Knowledge Bases model
now
A First Course In Database Systems
1.19
Relational model or not?
 Relational model is good for:
 Large amounts of data —> simple operations
 Navigate among small number of relations
 Difficult Applications for relational model:
 VLSI Design (CAD in general)
 CASE: Computer-Aided Software Engineering
 Graphical Data
 Where number of "relations" is large, relationships are complex
 Object Data Model
 Logic Data Model
A First Course In Database Systems
1.20
Other Data Models
 Object data model
 Usually find objects via explicit navigation, also query
language in some systems
 Complex Objects – Nested Structure (pointers or
references)
 Encapsulation, set of Methods/Access functions
 Object Identity
 Inheritance – Defining new classes like old classes
 Logic data model
 Prolog, Datalog
 More power than relational
A First Course In Database Systems
1.21
The DBMS Marketplace
 Relational DBMS companies – Oracle, Sybase – are among the
largest software companies in the world.
 IBM offers its relational DB2 system. With IMS, a nonrelational
system, IBM is by some accounts the largest DBMS vendor in the
world.
 Microsoft offers SQL-Server, plus Microsoft Access for the cheap
DBMS on the desktop, answered by “lite” systems from other
competitors.
 Relational companies also challenged by “object-oriented DB”
companies.
 But countered with “object-relational” systems, which retain the
relational core while allowing type extension as in OO systems.
A First Course In Database Systems
1.22
1.2 Architecture of DBMS
 Major components of a DBMS
Schema
Modifications
Queries
Modifications
Query Processor
Transaction
Manager
Storage Manager
Data
Metadata
Schema (logical structure of the data); Metadata(data of data)
A First Course In Database Systems
1.23
Architecture of DBS
Three Schema Architecture of DBS
 External Schema: user schema or subschema
 Defines one view of the data as seen by a specific set of application or
end users.
 There may be many external schemas in a DB.
 Schema: conceptual schema, logical schema
 Defines data from perspective systems designer;
 Independent of end users & data storage mechanism
 There is only one conceptual schema in a DB.
 Internal Schema: storage schema
 Defines how data is organized, stored and manipulated inside the
system.
 Totally dependent on particular implementation.
 There is only one internal schema in a DB.
A First Course In Database Systems
1.24
Three Schema Architecture (cont.)
ApplicationApplication Application Application Application
A
B
C
D
E
External
Schema 1
External
Schema 2
Schema
Internal
Schema
DB
A First Course In Database Systems
1.25
External
Schema 3
Independence of Data and Program
 Logical Independence
 Via Reflection of External Schema/Schema
 One Schema corresponds to many External schemas;
every Ex-Schema has an Ex-Schema/Schema Reflection.
 When Schema changes, DBA changes the ExSchema/Schema reflections, so application programs
needn’t been changed.
 Physical Independence
 Via Reflection of Schema/Internal Schema
 Schema/Internal Schema Reflection is unique.
 When Internal Schema changes, DBA changes the
Schema/ Internal Schema Reflection, so application
programs needn’t been changed.
A First Course In Database Systems
1.26
Three Aspects to Studying DBMS's
 Modeling and design of databases
 Allows exploration of issues before committing to an
implementation.
 Application programming
 SQL: queries and DB operations like update.
 Host language and Embedded SQL
 DBMS implementation
 Storage, query processing, transaction, …
A First Course In Database Systems
1.27