1 of 24

Database Design�Dr. Bijoy Bordoloi

Introduction to Database Processing

Chapter 1

2 of 24

Definitions

Data: Meaningful facts, text, graphics, images, sound, video segments
Database: An organized collection of logically related data
Information: Data processed to be useful in decision making
Metadata: Data that describes data

Chapter 1

3 of 24

Figure 1-1a Data in Context

Large volume of facts, difficult to interpret or make decisions based on

Chapter 1

4 of 24

Figure 1-1b Summarized data

Useful information that managers can use for decision making and interpretation

Chapter 1

5 of 24

Table 1-1 Metadata

Descriptions of the properties or characteristics of the data, including data types, field sizes, allowable values, and documentation

Chapter 1

6 of 24

Figure 1-2 Three file processing systems at Pine Valley Furniture

Duplicate Data

Chapter 1

7 of 24

Problems with Data Redundancy

Waste of space to have duplicate data
Causes more maintenance headaches
The biggest Problem:

When data changes in one file, it could cause inconsistencies
Compromises data integrity

Chapter 1

8 of 24

Disadvantages of File Processing

Data Redundancy (Duplication of data)

Different systems/programs have separate copies of the same data

Limited Data Sharing

No centralized control of data

Lengthy Development Times

Programmers must design their own file formats

Program-Data Dependence

All programs maintain metadata for each file they use

Excessive Program Maintenance

80% of of information systems budget

Chapter 1

9 of 24

Problems with Data Dependency

Each application programmer must maintain their own data
Each application program needs to include code for the metadata of each file
Each application program must have its own processing routines for reading, inserting, updating and deleting data
Lack of coordination and central control
Non-standard file formats

Chapter 1

10 of 24

Problems with Data Dependency

Consider the following (partial) COBOL program that produces a simple CUSTOMER SALES REPORT based on the input data as shown.

Carefully examine the structure of the input record.

How many Branches the company currently has at the most?
How many Salesperson the company currently employs at the most?

Chapter 1

11 of 24

Chapter 1

12 of 24

Chapter 1

13 of 24

Chapter 1

14 of 24

Problems with Data Dependency

Assume, the company has grown and has decided to open more branches and employ more salespersons (>99).

Assume, it is your responsibility as a company IS manager to implement these required changes. How will you go about implementing these changes? What major bottlenecks you are likely to encounter in implementing these simple changes?

Chapter 1

15 of 24

SOLUTION: �The DATABASE Approach

Central repository of shared data
Data is managed by a controlling agent
Stored in a standardized, convenient form

Requires a Database Management System (DBMS)

Chapter 1

16 of 24

Database Management System

A DBMS is a data storage and retrieval system which permits data to be stored non-redundantly while making it appear to the user as if the data is well-integrated.

Chapter 1

17 of 24

Database Management System

DBMS manages data resources like an operating system manages hardware resources

DBMS

Database

containing

centralized

shared data

Application

Chapter 1

18 of 24

Advantages of Database Approach

Program-Data Independence

Metadata stored in DBMS, so applications don’t need to worry about data formats
Data queries/updates managed by DBMS so programs don’t need to process data access routines
Results in: increased application development and maintenance productivity

Minimal Data Redundancy

Leads to increased data integrity/consistency

Chapter 1

19 of 24

Advantages of Database Approach

Improved Data Sharing

Different users get different views of the data

Enforcement of Standards

All data access is done in the same way

Improved Data Quality

Constraints, data validation rules

Better Data Accessibility/ Responsiveness

Use of standard data query language (SQL)

Security, Backup/Recovery, Concurrency

Disaster recovery is easier

Chapter 1

20 of 24

Costs and Risks of the �Database Approach

Up-front costs:

Installation Management Cost and Complexity
Conversion Costs

Ongoing Costs

Requires New, Specialized Personnel
Need for Explicit Backup and Recovery

Organizational Conflict

Old habits die hard

Chapter 1

21 of 24

The Range of�Database Applications

Personal Database – standalone desktop database
Workgroup Database – local area network (<25 users)
Department Database – local area network (25-100 users)
Enterprise Database – wide-area network (hundreds or thousands of users)

Chapter 1

22 of 24

Components of the �Database Environment

CASE Tools – computer-aided software engineering
Repository – centralized storehouse of metadata
Database Management System (DBMS) – software for managing the database
Database – storehouse of the data
Application Programs – software using the data
User Interface – text and graphical displays to users
Database Administrators – personnel responsible for maintaining the database
System Developers – personnel responsible for designing databases and software
End Users – people who use the applications and databases

Chapter 1

23 of 24

Figure 1-10 Components of the database environment

Chapter 1

24 of 24

Evolution of DB Systems

Flat files - 1960s - 1980s
Hierarchical – 1970s - 1990s
Network – 1970s - 1990s
Relational – 1980s - present
Object-oriented – 1990s - present
Object-relational – 1990s - present

Chapter 1