1 database systems: design, implementation, and management third edition chapter 1 file systems and...

1

Database Systems: Design, Implementation, and Management

THIRD EDITION

Database Systems: Design, Implementation, and Management

THIRD EDITION

CHAPTER 1

File Systems and Databases

2

Chapter ObjectivesChapter Objectives

Understand characteristics of a file system Understand advantages of using a database Understand characteristics of database

systems Understand basic database concepts Understand three different types of

database models

3

Chapter OverviewChapter Overview

1.1 Introducing the Database

1.2 The Historical Roots of the Database: Files and File Systems

1.3 A File System Critique

1.4 Database Systems

1.5 Database Models

4

Introducing the DatabaseIntroducing the Database

Major Database Concepts Data Data management Database Metadata Database management system (DBMS)

5


Importance of DBMS It helps make data management more efficient and e

ffective. Its query language allows quick answers to ad hoc q

ueries. It provides end users better access to more and bette

r-managed data. It promotes an integrated view of organization ope

rations -- ig picture? It reduces the probability of inconsistent data.

6


Figure 1.1 The DBMS Manages the Interaction betweenthe End User and the Database

7


Why Database Design Is Important? A poorly designed database is a breeding ground for

uncontrolled data redundancies. A poorly designed database generates errors that lea

d to bad decisions.

A Practical Approach to Database Design Focus on principles and concepts of practical databa

se design Use of two complete applications through the logica

l design stage

8

Historical RootsHistorical Roots

Why Studying File Systems? It provides historical perspective. It teaches lessons to avoid pitfalls of data

management. Its simple characteristics facilitate understanding of

the design complexity of a database. It provides useful knowledge for converting a file

system to a database system.

9


Table 1.1 Basic File Terminology

Data “Raw” facts that have little meaning unless they have beenorganized in some logical manner. The smallest piece of datathat can be “recognized” by the computer is a single character,such as the letter A, the number 5, or some symbol such as; ‘ ?> * +. A single character requires one byte of computer storage.

Field A character or group of characters (alphabetic or numeric) thathas a specific meaning. A field might define a telephonenumbers, a birth date, a customer name, a year-to-date (YTD)sales value, and so on.

Record A logically connected set of one or more fields that describes aperson, place, or thing. For example, the fields that comprise arecord for a customer named J. D. Rudd might consist of J. D.Rudd’s name, address, phone number, date of birth, credit limit,unpaid balance, and so on.

File A collection of related records. For example, a file might containdata about ROBCOR Company’s vendors; or, a file mightcontain the records for the students currently enrolled atGigantic University.

10


Reports Prepared by DP Specialist Using the CUSTOMER file: Monthly summaries of types and amounts of

insurance sold by each agent Monthly checks to determine which customers must

be contacted for renewal Analysis reports on ratios of insurance types sold by

each agent Customer contact letters with coverage summary

and customer relation bonuses

11


Figure 1.2 A Simple File System

12

File System CritiqueFile System Critique

File System Data Management File systems require extensive programming in 3GL. As file systems become more complex, managing

files gets more difficult. Making changes in existing file structures is

important and difficult. Data access programs are subject to change with file

structure changes (structural dependence). Security features are difficult to implement and are

lacking.

13


Structural and Data Dependence Structural Dependence

A change in any file structure requires the modification of all programs using that file.

Data DependenceA change in any file data characteristics requires changes of all data access programs.

Data dependence makes file systems extremely cumbersome from a programming and data management point of view.

14


Field Definitions and Naming Conventions A good (flexible) record definition anticipates

reporting requirements by breaking up fields into their components.

Example: Customer Name Last Name, First Name, Initial Customer Address Street Address, City, State

Selecting proper field names is very important. Descriptive Self-documenting

15


Data Redundancy:Uncontrolled data redundancy sets the stage for Data Inconsistency (lack of data integrity) Data anomalies

Modification anomalies Insertion anomalies Deletion anomalies

16

Database SystemsDatabase Systems

In a database system, logically related data are stored in a single data repository.

The database represents a change in the way end user data are stored, accessed, and managed.

DBMS makes it easier to eliminate most of the file system data inconsistency, data anomalies, and data structural dependency problems.

Current DBMS stores not only the data structure, but also the relationships.

DBMS takes care of defining all the required access paths.

17


Figure 1.3 Contrasting Database and File System Designs

18


Figure 1.4 The Database System Environment

19


The Database System Components Hardware

Computer Peripherals

Software Operating systems software DBMS software Applications programs and utilities software

20


The Database System Components People

Systems administrators Database administrators Database designers Systems analysts and programmers End users

Procedures Instructions and rules that govern the design and use of the

database system

Data Collection of facts stored in the database

21


Database Systems and Organizational Factors The complexity of database systems depends on

various organizational factors: Organization size Organization function Organization corporate culture Organizational activities and environment

Database solutions must be cost effective and strategically effective.

22


Types of Database Systems Number of Users

Single-user Multi-user

Scope Desktop Workgroup Enterprise

23


Types of Database Systems Location

Centralized Distributed

Use Transactional (Production) Decision support Data warehouse

24


DBMS Functions Data Dictionary Management

Data dictionary stores definitions of the data elements and their relationships (metadata).

It provides data abstraction and removes structural and data dependency from the system.

Data Storage Management DBMS creates data storage structure and relieves us from t

he task of defining and programming physical data characteristics.

25


DBMS Functions Data Transformation and Management

DBMS relieves us from the chore of making distinction between logical format and physical format of data.

Security Management DBMS provides user security and data privacy within the

database. Data security is especially important in multi-user

database.

26


DBMS Functions Multi-User Access Control

DBMS ensures that multiple users can access the database concurrently and still guarantees the integrity of the database.

Backup and Recovery Management DBMS provides backup and recovery procedures to ensure

data safety and integrity.

Data Integrity Management DBMS promotes and enforces integrity rules to eliminate

data integrity problems. Ensuring data integrity is especially important in

transaction-oriented database systems.

27


Managing the Database System: Shift in Focus The role of DP specialists or manager changes from

a programming emphasis to focus on the broader management aspects of the organization data resource and on the administration of complex database software.

Shift in DP Personnel DP Manager Systems Administrator Database Administrator

28

Database ModelsDatabase Models

A database model is a collection of logical constructs used to represent the data structure and the data relationships found within the database.

Two Categories of Database Models Conceptual models focus on the logical nature of

the data representation. They are concerned with what is represented rather than how it is represented.

Implementation models place the emphasis on how the data are represented in the database or on how the data structures are implemented.

29

Database Models Database Models

Three Types of Relationships in Conceptual Database Models One-to-many relationships

30


Three Types of Relationships in Conceptual Database Models Many-to-many relationships

31


Three Types of Relationships in Conceptual Database Models One-to-one relationships

32


Three Types of Implementation Database Models Hierarchical database model Network database model Relational database model

33


Hierarchical Database Model Background

GUAM (Generalized Update Access Method) was developed by North American Rockwell.

It conformed to upside-down tree structure -- hierarchical structure.

Information Management System (IMS) -- Jointly developed by IBM and Rockwell.

Hierarchical database model concepts for the basis for subsequent database development.

Its limitations lead to a different way of looking at database design.

34


Figure 1.5 A Hierarchical Structure

35


Hierarchical Database Model Basic Structure

Collection of records perceived as organized to conform to the upside-down tree structure.

A tree structure is represented as a hierarchical path on the computer storage media.

One-to-Many (1:M) Relationship Each parent can have many children. Each child has only one parent.

36


Figure 1.6 The Basic Components of a Hierarchical Structure

37


Hierarchical Database Model Advantages

Data sharing and security provision Data independence -- Reduced programming and

maintenance effort Database integrity Efficiency dealing with a large database Large installed (mainframe) base Abundant business applications

38


Hierarchical Database Model Disadvantages

Requirement of knowledge of physical level of data storage

Inability to represent relationships that do not conform to the hierarchical 1:M standard

Complex and inflexible to manage Time consuming and complicated application

programming Lack of ad hoc query capability for end users Lack of standard concepts and implementation -- limited

portability Requirement of extensive programming activities

39


Network Database Model Background

CODASYL (Conference on Data Systems Language) group created DataBase Task Group (DBTG) in 1971.

DBTG specified three crucial database components: Network schema defines the conceptual organization of the

entire database as viewed by the database administrator. Subschema defines the portion of the database as seen by th

e applications programs. Data Management Language defines the data characteristi

cs and the data structure and to manipulate the data.

40


Network Database Model Background

Three DBTG data management language components: Schema Data Definition Language (DDL) Subschema Data Definition Language Data Manipulation Language

ANSI SPARC (Standards Planning And Requirements Committee) augmented the database standards in 1975.

41


Network Database Model Basic Structure

Set -- A relationship is called a set. Each set is composed of at least two record types: an owner (parent) record and a member (child) record.

Figure 1.7 A Set

42


Figure 1.8 A Network Model

43


Network Database Model Relationships among the records are decomposed into a

series of sets.

Figure 1.9 Defining Set Components

44


Network Database Model Advantages

Easier implementation of M:N relationships Superior data access type and flexibility Enforced data integrity Sufficient data independence

45


Network Database Model Disadvantages

Difficult to design and use properly Difficult to make changes in a database Very complex structure from the application programmer

point of view Complex navigational data access environment

46


Relational Database Model Background

E. F. Codd developed the relational model in 1970. Conceptually simple but versatile Major breakthrough for both users and designers From tandard transmission?to utomatic transmission

Requires more computing power Considered impractical in the 1970 Modern computers (even PCs) are powerful enough to handl

e relational databases.

47


Relational Database Model Basic Structure

Relational DataBase Management System (RDBMS) RDBMS allows user/designer operate in a human logical en

vironment. Relational database is perceived by the user as a collection o

f tables in which data are stored. Each table consists of series of row/column intersections. Tables (or relations) are related to each other by sharing a co

mmon entity characteristic. The relationship type is often shown in a relational schema. A table yields complete data and structural independence be

cause it is a purely logical structure.

48


Relational Database Model

Figure 1.10 A Relational Schema

49


Relational Database Model Advantages

Data independence and structural independence Easy to design the database and to manage its contents Less programming effort required Powerful and flexible query capability:

Structured Query Language (SQL) Fourth Generation Language (4GL) Specify hat to do?not ow to do Introduced by IBM in 1974 3 parts: (1) User interface, (2) Set of tables (3) SQL en

gine

50


Relational Database Model Disadvantages

RDBMS requires substantial hardware and operating system overhead.

It tends to be slower than other database systems.

1 database systems: design, implementation, and management third edition chapter 1 file systems and...

Documents

database systems1

designed database

database modelsintroducing

database designfocus

customer file

databasewhy database

file systems1

file system critique1