Transcript ppt - Spatial Database Group

Chapter 1
Introduction to Database
Management
Database Design, Application Development, and
Administration, 5th Edition
Copyright © 2011 by Michael V. Mannino All rights reserved.
Welcome!
 Database technology: crucial to the
operation and management of modern
organizations
 Major transformation in computing skills
 Significant time commitment
 Exciting journey ahead
Chapter 1: Introduction to Database Management
Slide 2
Book Goals
 First course in database management
 Practical textbook





Fundamentals of relational databases
Query formulation
Data modeling, normalization, and physical design
Database application development
Database administration and database processing
environments
 Detailed material
Chapter 1: Introduction to Database Management
Slide 3
Outline




Database characteristics
DBMS features
Architectures
Organizational roles
Chapter 1: Introduction to Database Management
Slide 4
Initial Vocabulary
 Data: raw facts about things and events
 Information: transformed data that has
value for decision making
 Essential to organize data for retrieval and
maintenance
Chapter 1: Introduction to Database Management
Slide 5
Database Characteristics
 Persistent
 Inter-related
 Shared
Chapter 1: Introduction to Database Management
Slide 6
University Database
Registration
Grade
Recording
Entities:
students, f aculty , courses,
of f erings, enrollments
Relationships
:
f aculty teach of f erings,
students enroll in
of f erings, of f erings made
of courses, ...
Faculty
Assignment
Course
Scheduling
Unive rsity Databas e
Chapter 1: Introduction to Database Management
Slide 7
Water Utility Database
Billing
Meter
Reading
Entities:
c us tomers , met ers , bills ,
pay ment s , met er readings
:
R el ation shi ps
bills sent to c us tomers ,
c us tomers make pay ments ,
c us tomers use met ers, . ..
Chapter 1: Introduction to Database Management
Payment
Process ing
Service Start/
Stop
Slide 8
Database Management
System (DBMS)
 Collection of components that support
data acquisition, dissemination, storage,
maintenance, retrieval, and formatting
 Enterprise DBMSs
 Desktop DBMSs
 Embedded DBMSs
 Major part of information technology
infrastructure
Chapter 1: Introduction to Database Management
Slide 9
Database Definition
 Define database structure before using a
database
 Tables and relationships
 SQL CREATE TABLE statement
 Graphical tools
Chapter 1: Introduction to Database Management
Slide 10
University Database
Chapter 1: Introduction to Database Management
Slide 11
Table Definition Window
Chapter 1: Introduction to Database Management
Slide 12
Table Contents (Rows)
StdFirstName StdLastName StdCity
StdState StdZip
HOMER
WELLS
SEATTLE
WA
98121-1111 IS
FR
3.00
BOB
NORBERT
BOTHELL
WA
98011-2121 FIN
JR
2.70
CANDY
KENDALL
TACOMA
WA
99042-3321 ACCT
JR
3.50
WALLY
KENDALL
SEATTLE
WA
98123-1141 IS
SR
2.80
JOE
ESTRADA
SEATTLE
WA
98121-2333 FIN
SR
3.20
MARIAH
DODGE
SEATTLE
WA
98114-0021 IS
JR
3.60
TESS
DODGE
REDMOND WA
98116-2344 ACCT
SO
3.30
Chapter 1: Introduction to Database Management
StdMajor StdClass
StdGPA
Slide 13
University Database (ERD)
Student
Offering
StdNo
StdClass
StdMajor
StdGPA
OfferNo
OffLocation
OffTime
Faculty
Teaches
Has
FacNo
FacSalary
FacRank
FacHireDate
Supervises
Accepts
Course
Registers
Enrollment
EnrGrade
Chapter 1: Introduction to Database Management
CourseNo
CrsDesc
CrsUnits
Slide 14
Nonprocedural Access
 Query: request for data to answer a
question
 Indicate what parts of database to retrieve
not the procedural details
 Improve productivity and improve
accessibility
 SQL SELECT statement and graphical
tools
Chapter 1: Introduction to Database Management
Slide 15
Graphical Tool for
Nonprocedural Access
Chapter 1: Introduction to Database Management
Slide 16
Application Development
 Form: formatted document for data entry
and display
 Report: formatted document for display
 Use nonprocedural access to specify data
requirements of forms and reports
Chapter 1: Introduction to Database Management
Slide 17
Sample Data Entry Form
Chapter 1: Introduction to Database Management
Slide 18
Sample Report
Chapter 1: Introduction to Database Management
Slide 19
Procedural Language Interface
 Combine procedural language with
nonprocedural access
 Why
 Batch processing
 Customization and automation
 Performance improvement
Chapter 1: Introduction to Database Management
Slide 20
Transaction Processing
 Transaction: unit of work that should be
reliably processed
 Control simultaneous users
 Recover from failures
Chapter 1: Introduction to Database Management
Slide 21
Database Technology Evolution
Era
Generation
Orientation
1960s
1st generation File
File structures and proprietary
program interfaces
1970s
2nd generation Network
navigation
Networks and hierarchies of related
records, standard program interfaces
1980s
3rd generation Relational
Nonprocedural languages,
optimization, transaction processing
1990s to 4th generation Object
2000s
Chapter 1: Introduction to Database Management
Major Features
Multi-media, active, distributed
processing, more powerful operators,
data warehouse processing, XML
enabled, cloud computing
Slide 22
DBMS Marketplace
 Enterprise DBMS





Oracle: dominates in Unix; strong in Windows
SQL Server: strong in Windows
DB2: strong in mainframe environment
Teradata: usage as a data warehouse platform
Significant open source DBMSs: MySQL, Progress,
Firebird, PostgreSQL, open source Ingres
 Desktop DBMS
 Access: dominates
 FoxPro, Paradox, Approach, FileMaker Pro
Chapter 1: Introduction to Database Management
Slide 23
Data Independence
 Software maintenance is a large part
(50%) of information system budgets
 Reduce impact of changes by separating
database description from applications
 Change database definition with minimal
effect on applications that use the
database
Chapter 1: Introduction to Database Management
Slide 24
Three Schema Architecture
View 1
External to
Conceptual
Mappings
Conceptual
to Internal
Mappings
View 2
Conceptual
Schema
Internal
Schema
Chapter 1: Introduction to Database Management
View n
Exte rnal
Le ve l
Conce ptual
Le ve l
Inte rnal
Le ve l
Slide 25
Differences among Levels
 External
 FacultyAssignmentFormView: data required for the
form in Slide 18 (Figure 1.9)
 FacultyWorkLoadReportView: data required for the
report in Slide 19 (Figure 1.10)
 Conceptual: tables in Slide 14
 Internal
 Files needed to store the tables
 Extra files to improve performance
Chapter 1: Introduction to Database Management
Slide 26
Client-Server Architecture
a) Client-server processing with database server
b) Client-server processing with middleware and database servers
Database
Database
server
Chapter 1: Introduction to Database Management
Database
Middleware
server
Database
server
Slide 27
Parallel Database Architecture
(a) SD
(b) SN
N
P
P
M
M
N
...
P
P
P
M
M
M
...
...
P
M
...
Legend
P: processor
M: memory
N: high-speed network
SD: shared disk
SN: shared nothing
Chapter 1: Introduction to Database Management
Slide 28
Distributed Database
Architecture
Tokyo
Client
Server
Database
Client
Client
Denver
London
Server
Server
Client
Client
Client
Database
Chapter 1: Introduction to Database Management
Database
Slide 29
Cloud Computing
Chapter 1: Introduction to Database Management
Slide 30
Organizational Roles
Specialization
Functional User
Indirect
Parametric
Information Systems
Power
DBA
Technical
Chapter 1: Introduction to Database Management
Analyst/Programmer
Management
Non Technical
Slide 31
Database Specialists
 Database administrator (DBA)
 More technical
 DBMS specific skills
 Data administrator
 Less technical
 Planning role
Chapter 1: Introduction to Database Management
Slide 32
DBA Responsibilities
Technical
Non-technical
Designing conceptual schemas
Setting database standards
Designing internal schemas
Devising training materials
Monitoring database performance
Promoting benefits of
databases
Consulting with users
Selecting and evaluating database
software
Managing security for database usage
Planning new databases
Troubleshooting database problems
Chapter 1: Introduction to Database Management
Slide 33
Summary
 Databases and database technology vital
to modern organizations
 Database technology supports daily
operations and decision making
 Nonprocedural access is a crucial feature
 Many opportunities to work with databases
Chapter 1: Introduction to Database Management
Slide 34