Transcript Chapter 1 of Database Design, Application Development and

Chapter 1
Introduction to Database Management
Welcome!
Database technology: crucial to the
operation and management of modern
organizations
 Major transformation in computing skills
 Significant time commitment
 Exciting journey ahead

McGraw-Hill/Irwin
© 2004 The McGraw-Hill Companies, Inc. All rights reserved.
Book Goals
First course in database management
 Practical textbook

–
–
–
–

Fundamentals of relational databases
Data modeling and normalization
Database application development
Database administration and database
processing environments
Detailed material
McGraw-Hill/Irwin
© 2004 The McGraw-Hill Companies, Inc. All rights reserved.
Outline
Database characteristics
 DBMS features
 Architectures
 Organizational roles

McGraw-Hill/Irwin
© 2004 The McGraw-Hill Companies, Inc. All rights reserved.
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

McGraw-Hill/Irwin
© 2004 The McGraw-Hill Companies, Inc. All rights reserved.
Database Characteristics

Persistent

Inter-related

Shared
McGraw-Hill/Irwin
© 2004 The McGraw-Hill Companies, Inc. All rights reserved.
University Database
Registration
Grade
Recording
Entities:
students, faculty, courses,
offerings, enrollments
Relationships :
faculty teach offerings,
students enroll in
offerings, offerings made
of courses, ...
Faculty
Assignment
Course
Scheduling
University Database
McGraw-Hill/Irwin
© 2004 The McGraw-Hill Companies, Inc. All rights reserved.
Water Utility Database
Billing
Meter
Reading
Entities:
customers, meters, bills,
payments, meter readings
Relationships:
bills sent to customers,
customers make payments,
customers use meters, ...
Payment
Processing
Service Start/
Stop
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

McGraw-Hill/Irwin
© 2004 The McGraw-Hill Companies, Inc. All rights reserved.
Database Definition
Define database before using
 Tables and relationships
 SQL CREATE TABLE statement
 Graphical tools

McGraw-Hill/Irwin
© 2004 The McGraw-Hill Companies, Inc. All rights reserved.
University Database
McGraw-Hill/Irwin
© 2004 The McGraw-Hill Companies, Inc. All rights reserved.
University Database (ERD)
Student
Offering
Faculty
StdSSN
StdClass
StdMajor
StdGPA
OfferNo
OffLocation
OffTime
FacSSN
FacSalary
FacRank
FacHireDate
Teaches
Has
Supervises
Accepts
Course
Registers
Enrollment
EnrGrade
McGraw-Hill/Irwin
CourseNo
CrsDesc
CrsUnits
© 2004 The McGraw-Hill Companies, Inc. All rights reserved.
Nonprocedural Access
Loop
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

McGraw-Hill/Irwin
© 2004 The McGraw-Hill Companies, Inc. All rights reserved.
Graphical Tool for
Nonprocedural Access
McGraw-Hill/Irwin
© 2004 The McGraw-Hill Companies, Inc. All rights reserved.
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

McGraw-Hill/Irwin
© 2004 The McGraw-Hill Companies, Inc. All rights reserved.
Sample Data Entry Form
McGraw-Hill/Irwin
© 2004 The McGraw-Hill Companies, Inc. All rights reserved.
Sample Report
McGraw-Hill/Irwin
© 2004 The McGraw-Hill Companies, Inc. All rights reserved.
Procedural Language Interface
Combine procedural language with
nonprocedural access
 Why

– Batch processing
– Customization and automation
– Performance improvement
McGraw-Hill/Irwin
© 2004 The McGraw-Hill Companies, Inc. All rights reserved.
Transaction Processing
Transaction: unit of work that should be
reliably processed
 Control simultaneous users
 Recover from failures

McGraw-Hill/Irwin
© 2004 The McGraw-Hill Companies, Inc. All rights reserved.
Database Technology Evolution
Era
1960s
Generation
1st Generation
Orientation
File
1970s
2nd Generation
Network
Navigation
1980s
3rd Generation
Relational
1990s
4th Generation
Object
McGraw-Hill/Irwin
Major Features
File structures and
proprietary program
interfaces
Networks and hierarchies
of related records,
standard program
interfaces
Non-procedural
languages, optimization,
transaction processing
Multi-media, active,
distributed processing,
more powerful operators
© 2004 The McGraw-Hill Companies, Inc. All rights reserved.
DBMS Marketplace

Enterprise DBMS
–
–
–
–

Oracle: dominates in Unix; strong in Windows
SQL Server: strong in Windows
Informix: significant Unix marketshare
DB2: strong in mainframe environment
Desktop DBMS
– Access: dominates
– FoxPro, Paradox, Approach, FileMaker Pro
McGraw-Hill/Irwin
© 2004 The McGraw-Hill Companies, Inc. All rights reserved.
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

McGraw-Hill/Irwin
© 2004 The McGraw-Hill Companies, Inc. All rights reserved.
Three Schema Architecture
View 1
External to
Conceptual
Mappings
Conceptual
to Internal
Mappings
View 2
Conceptual
Schema
Internal
Schema
McGraw-Hill/Irwin
View n
External
Level
Conceptual
Level
Internal
Level
© 2004 The McGraw-Hill Companies, Inc. All rights reserved.
Differences among Levels

External
– FacultyAssignmentFormView: data required
for the form in Slide 16 (Figure 1.9)
– FacultyWorkLoadReportView: data required
for the report in Slide 17 (Figure 1.10)
Conceptual: tables in Slide 11
 Internal

– Files needed to store the tables
– Extra files to improve performance
McGraw-Hill/Irwin
© 2004 The McGraw-Hill Companies, Inc. All rights reserved.
Client-Server Architecture
a) Client, server, and
database on the
same computer
b) Mulitple clients and 1 server
on different computers
Client
Client
Server
Server
Client
Client
Database
Database
c) Multiple servers and databases on different computers
Client
Server
Server
Client
Client
McGraw-Hill/Irwin
Client
Database
Database
© 2004 The McGraw-Hill Companies, Inc. All rights reserved.
Organizational Roles
Specialization
Functional User
Indirect
Parametric
Information Systems
Power
DBA
Technical
McGraw-Hill/Irwin
Analyst/Programmer
Management
Non Technical
© 2004 The McGraw-Hill Companies, Inc. All rights reserved.
Database Specialists

Database administrator (DBA)
– More technical
– DBMS specific skills

Data administrator
– Less technical
– Planning role
McGraw-Hill/Irwin
© 2004 The McGraw-Hill Companies, Inc. All rights reserved.
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

McGraw-Hill/Irwin
© 2004 The McGraw-Hill Companies, Inc. All rights reserved.