Introduction to Database Systems

Prepared by:

Arwie H. Fernando

References

• R. Elmasri & S. B. Navathe, Fundamentals of Database Systems, The Benjamin/Cummings Publishing, 1994.

• T. Conolly, C. Begg & A. Strachan, Database Systems, A Practical Approach to Design, Implementation and Management, Addison-Wesley Publishing Co., 1998.

• F. R. McFaden & J. A. Hoffer, DatabaseManagement, The Benjamin/Cummings Publishing, 1991

• H. F. Korth & A Silberschatz, Database System Concepts, McGraw-Hill, 1991

Introduction to Databases : Objectives

• Examples of database systems

• Characteristics of file-based systems

• Limitations of file-based systems

• Meaning of the term database

• Meaning of the term Database Management System (DBMS)

• Typical functions of a DBMS

• Major Components of the DBMS environment

• People involved in the DBMS environment

• Strengths and weaknesses of DBMSs

Outline: Introduction to Databases

1.0 Some examples of database systems

2.0 Manual Filing System

3.0 File-based Approach

3.1 Characteristics of File-based systems

3.2 Example of a File-based system

3.3 Limitations of File-based systems

prepared by:RdDB

Outline: Introduction (cont)4.0 Database Approach

4.1 Simple database system architecture

4.2 Components of a DBMS environment

4.2.1Software

4.2.2 Hardware

4.2.3 Data

4.2.4 People

4.2.5 Procedures

4.3 Strengths and Weaknesses of Database Systems

At this stage, for purpose of discussion:

• Database is a collection of related data.

• Database Management System (DBMS) is the software that manages and accesses the database.

Can you give some examples of database systems?

• Supermarket

• Credit card

• Travel Agent

• Library

• Insurance

• Hospital

• Bank

• University

Student Database System

enrollment

paymentof fees

processingof grades

DBMS

EAF

official receipt

coursecards

DB

Supermarket Database System

official receipt

DB

milkoil

inventory &price check

DBMSsales update

credit check DBMS DB

card

How were records maintained in the pre-computer period?

Manual Filing System

Projects

Products

Clients

Employees

Tasks Sales

What are File-Based Systems?

• A File-based system is a collection of application programs that perform services for the end users such as the production of reports.

• Each program defines and manages its own data. [CBS98]

• Each user (with the assistance of DP staff) defines and implements (including storage and control) the files needed for a specific application. [CBS98, EN94]

Student System (File-based)

prepared by:RdDB

EAFenrollment

FileRegistrarStudent

Courses

Faculty

ORpayment of fees

FileAccountingStudent

Fees

coursecards

Processing of grades

FileDepartment Student

Courses

Grades

Faculty

Data redundancyData redundancyWhat can be observed?What can be observed?

Student System (File-based)

EAFenrollment

FileRegistrarStudent

Courses

Faculty

ORpayment of fees

FileAccountingStudent

Fees

coursecards

Processing of grades

FileDepartment Student

Courses

Grades

Faculty

Separation and isolation of data

Separation and isolation of dataWhat can be observed?What can be observed?

Student System (File-based)

prepared by:RdDB

EAFenrollment

FileRegistrarStudent

Courses

Faculty

ORpayment of fees

FileAccountingStudent

Fees

coursecards

Processing of grades

FileDepartment Student

Courses

Grades

Faculty

Program-data dependenceProgram-data dependenceWhat can be observed?What can be observed?

struct person

{ char first[20];

char middle[3];

char last[30];

} employees, managers;

struct person

{ char first[20];

char middle[3];

char last[30];

} employees, managers;

Student System (File-based)

prepared by:RdDB

EAFenrollment

FileRegistrarStudent

Courses

Faculty

ORpayment of fees

FileAccountingStudent

Fees

coursecards

Processing of grades

FileDepartment Student

Courses

Grades

Faculty

Incompatibility of filesIncompatibility of filesWhat can be observed?What can be observed?

COBOLCOBOL

CC

Student System (File-based)

prepared by:RdDB

EAFenrollment

FileRegistrarStudent

Courses

Faculty

ORpayment of fees

FileAccountingStudent

Fees

coursecards

Processing of grades

FileDepartment Student

Courses

Grades

Faculty

Incompatibility of filesIncompatibility of filesWhat can be observed?What can be observed?

COBOLCOBOL

CC

Student System (File-based)

prepared by:RdDB

EAFenrollment

FileRegistrarStudent

Courses

Faculty

ORpayment of fees

FileAccountingStudent

Fees

coursecards

Processing of grades

FileDepartment Student

Courses

Grades

Faculty

Fixed queries;

proliferation of application programs

Fixed queries;

proliferation of application programs

What can be observed?What can be observed?

Limitations of File-Based Systems

• Separation and isolation of data

• Duplication of data/ data redundancy

• Program-data dependence

• Incompatibility of files (e.g C vs. COBOL)

• Fixed queries / proliferation of application programs

Factors that limit File-Based System

• The definition of data is embedded in the application programs, rather than being stored separately and independently. [CBS98]

• There is no control over the access and manipulation of data beyond that imposed by the application programs. [CBS98]

Database Approach

What is a database?

• It is a shared collection of logically coherent data with some inherent meaning;

• It is designed , built and populated with data for specific purpose such as meeting the information needs of an organization;

• It represents some aspect of the real-world. [EN94]

• Ex: student database, employee database,library database, air flights database, hospital database, etc.

What is a DBMS?• Database Management System• It is a software system that enables users to :

– define, create and maintain the database• DDL• DML

– provide controlled access to this database• Security• Integrity• Concurrency control• Recovery control• User-accessible catalogue (description of data)

A Simple Database System Environment

ApplicationPrograms/

Queries

DATABASESYSTEM

DataDefinition

Database

DBMS Software

Software to process

programs/queries

Software toaccess stored

data

Components of the DBMS Environment[CBS98]

Hardware

- PC

- mainframe

- network

Software

- DBMS

- OS

- network

software

- application

programs

Procedure- log on

- start/stop

- backup

- handle

failures

- change

structure

People- data admin

- database

admin

- DB designer

- application

programmers

- end users

Machine Human

Bridge

Data

- stored data

- meta-data

- schema

Hardware [CBS96]• Ranges of computer hardware:

– a single personal computer

– a single mainframe

– a network of computers

• Multi-User DBMS Architectures

– Teleprocessing : one computer with a single CPU and a number of terminals

– File-Server : LAN where the file-server acts as a shared hard disk drive for the database, and the applications and DBMS run on each workstation

– Client-Server : the DBMS resides in the server

Teleprocessing topology

DBMS

Application Programs Database

LAN

File-Server

DatabaseRequest for data Files returned

Workstation 1

Workstation 2 Workstation 3

File-server

DBMS

DBMS

DBMS

Client-Server

LAN

DatabaseServer with

DBMS

Request for data Selected data returned

Client 1

Client 2 Client 3

Software

• DBMS with fourth-generation tools

• Application programs

• Operating System

• Network Software

Data

• Operational data

• Meta-data

• Schema (structure)

ProceduresThese refer to the instructions and rules that govern the use

and design of the database:

• log on to the DBMS

• use a particular DBMS facility or application program

• start and stop the DBMS

• make a backup copies of the database

• handle hardware or software failures

• change the structure of a table, reorganize the database, improve performance, archive data to secondary storage

Workers Behind the Scene• DBMS Designers and Implementers

– Data Administrator– Database Administrator– Database Designer

• Logical database designer• Physical database designer

– Application Programmers

• Tool Developers (e.g. performance monitoring, graphical interfaces, etc.)

• Operators and Maintenance Personnel• End Users

• Data Administrator manages the data resource including:

– database planning,

– development and maintenance of standards, policies and procedures and

– logical database design.

• Database Administrator is responsible for the physical realization of the database including

– physical design and implementation,

– security and integrity control,

– maintenance of the operational system and

– ensuring satisfactory performance for the applications and users.

• Database Designers identify the data to store and choose the proper structures.

• System Analysts and Application Programmers deal with development of applications for end users.

• End Users actually access the database contents.

End Users

• Casual end users occasionally access the database, using a query language.

• Naive or parametric end users use standard queries, due to consistent needs from the database, using canned transactions.

• Sophisticated end users are engineers, scientists, business analysts and those who have complex requirements.

• Stand-alone users maintain personal databases, using ready-made program packages (e.g. tax package).

Strengths of Database Systems• Control of data redundancy • Promote data consistency • More information from the same amount of data • Sharing of data• Improved data integrity • Improved security• Enforcement of standards• Economy of scale

Strengths of Database Systems

• Balanced conflicting requirements• Improved data accessibility and

responsiveness• Increased productivity• Improved maintenance through data

independence• Increased concurrency• Improved backup and recovery services

Weaknesses of Database Systems [CBS98]

• Complexity

• Size

• Cost of DBMS

• Additional hardware costs

• Cost of conversion

• Performance

• Higher impact of a failure

Assignment # 1

• Research topic: History of Database Management Systems

• Interview a user of a database system.