Chapter 1

Introduction to Database Management

Database Design, Application Development, and Administration, 5 th 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, faculty, courses, offerings, enrollments

Relationships

: faculty teach offerings, students enroll in offerings, offerings made of courses, ...

University Database

Chapter 1: Introduction to Database Management Faculty Assignment Course Scheduling Slide 7

Water Utility Database

Billing Meter Reading

Entities

: customers, meters, bills, payments, meter readings

Relationships

: bills sent to customers, customers make payments, customers use meters, ...

Chapter 1: Introduction to Database Management Payment Processing 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

HOMER WELLS SEATTLE

StdState StdZip

WA

StdMajor StdClass

98121-1111 IS FR BOB CANDY NORBERT KENDALL BOTHELL TACOMA WA WA 98011-2121 FIN 99042-3321 ACCT JR JR WALLY JOE MARIAH TESS KENDALL ESTRADA DODGE DODGE SEATTLE SEATTLE SEATTLE WA WA WA REDMOND WA 98123-1141 IS 98121-2333 FIN 98114-0021 IS 98116-2344 ACCT SR SR JR SO

StdGPA

3.00

2.70

3.50

2.80

3.20

3.60

3.30

Chapter 1: Introduction to Database Management Slide 13

University Database (ERD)

Student

StdNo StdClass StdMajor StdGPA

Offering

OfferNo OffLocation OffTime Accepts Registers

Enrollment

EnrGrade Chapter 1: Introduction to Database Management Teaches Has

Faculty

FacNo FacSalary FacRank FacHireDate Supervises

Course

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

1960s

Generation

1 st generation

Orientation

File 1970s 1980s 1990s to 2000s 2 nd generation Network navigation 3 rd generation Relational 4 th generation Object

Major Features

File structures and proprietary program interfaces Networks and hierarchies of related records, standard program interfaces Nonprocedural languages, optimization, transaction processing Multi-media, active, distributed processing, more powerful operators, data warehouse processing, XML enabled, cloud computing Chapter 1: Introduction to Database Management 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 View 2 External to Conceptual Mappings Conceptual Schema Conceptual to Internal Mappings Internal Schema Chapter 1: Introduction to Database Management View n

External Level Conceptual Level Internal Level

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 server Database Middleware server Database server Database Chapter 1: Introduction to Database Management Slide 27

Parallel Database Architecture

(a) SD N P M P ...

P M M (b) SN N P M P ...

P M 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

Client Client Client Client Denver Server Tokyo Server Database London Server Client Database Chapter 1: Introduction to Database Management Database Client Slide 29

Cloud Computing

Chapter 1: Introduction to Database Management Slide 30

Organizational Roles

Specialization Indirect Functional User Parametric Power DBA Information Systems Analyst/Programmer Management

Chapter 1: Introduction to Database Management

Technical 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

Designing conceptual schemas Designing internal schemas Monitoring database performance Selecting and evaluating database software Managing security for database usage Troubleshooting database problems Chapter 1: Introduction to Database Management

Non-technical

Setting database standards Devising training materials Promoting benefits of databases Consulting with users Planning new databases 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