Introduction To Database Management System

Introduction to

DatabaseManagement

Systems

Atul Kahate

ALWAYS LEARNING PEARSON

Introduction

to

Database

Management Systems

Introduction

to

Database

Management Systems

mr

'w

\ GO /

o—-

——o

\ sa J

a

Atul Kahate

PROJECT MANAGER

/-I/ex Solutions Limited

Pane

Pearson

Education

This page is intentionally left blank.

Copyright © 2004 by Pearson Education (Singapore) Pte. Ltd.

Licensees of Pearson Education in South Asia

No part ofthis eBook may be used or reproduced in any manner whatsoever without the publisher's prior written consent.

This eBook may or may not include all assets that were part ofthe print version. The publisher reserves the right to remove any

material in this eBook at any time.

ISBN 9788131700785

elSBN 9788131775820

Head Office; A-8(A), Sector 62, Knowledge Boulevard, 7th Floor, NOIDA 201 309, India

Registered Office; 11 Local Shopping Centre, Panchsheel Park, New Delhi 110 017, India

This page is intentionally left blank.

To

My Wife Anita

For being the best life partner one could ever hope to find

Daughter Jui

For being so wonderful, lovely, mischievous and cheerful

Son Harsh

For adding some spice to our small but brilliant family

Jorezuord

Like many technologies in the computer industry, the evolution of databases can be tracked back to

research into automating office functions in the 1960s and 1970s. Firms discovered it was becoming

far too expensive to hire people to do certain jobs such as storing and indexing files. They began

investing in research into cheaper and more efficient mechanical solutions.

In 1970, an IBM researcher named Ted Codd published the first article on relational databases. It

outlined an approach that used relational calculus and algebra to allow non-technical users to store

and retrieve large amounts of information. Codd envisaged a system where the user would be able to

access information stored in tables with "natural language" commands.

The article's significance was not recognised because it was far too technical and relied heavily

on mathematics. However, it did lead to IBM starting a research group known as "System R".

Eventually, System R evolved into SQL/DS which later became DB2. The language created by the

System R group, SQL (Structured Query Language) has become the industry standard for relational

databases and is now an ISO standard.

The first database systems built upon the SQL standard appeared at the beginning of the 1980s

from Oracle with Oracle Version 2, and later SQL/DS from IBM, as well as a host of other systems

from other companies.

Relational database technology was continually refined during the 1980s. This was due to feedback

from customers, the development of systems for new industries and the increased use of personal

computers and distributed systems.

By the middle of the 1980s, it had become obvious that there were several fields where relational

databases were not practical due to the types of data they used. These areas included medicine,

multimedia and high energy physics and they needed more flexibility in how their data was

represented and accessed.

This led to research into object oriented databases where users could define their own methods of

access to data and how it was represented and manipulated. This coincided with the introduction of

Object Oriented Programming languages such as C-H- which started to appear in industry at the same

time. Since then, in the late 1990s and the early years of this decade, we have seen huge advances

in the technology behind running databases and securing them.

Since their arrival, databases have expanded in size from the 8MB of data that System R had

been tested with to terabytes of data used for mailing lists, credit card information for banks and so

on. With each increase in the amount of storage available, we have seen a corresponding increase in

the size and complexity of database systems in operation.

In today's scenario, databases are ubiquitous in all modern corporations, so much so, that

professionals carry smaller databases on palmtops or mobile phones. It will be hard to find any

successful organisation that does not deploy databases for their key operations. This subject is now

viii Foreword

being taught and discussed in primary schools in urban areas. This area of technology has spun off

a huge cottage industry around it, spanning from the technical support novice to highly specialised

very large database management gurus.

It is in light of this background that Atul's book is very significant. Effective database management

has become a critical success factor in corporate IT strategy. Although, there are a number of books

on this subject available in the market today, Introduction to Database Management Systems stands

out because of the wide range of topics that it covers and the simple and lucid manner in which it

explains them. It will be of tremendous value to students of this subject, who can also use it as a

reference tool.

While working with Atul over the years, I have always been impressed with his thorough under￾standing of database management and other related topics. He also has outstanding ability to present

his thoughts in the most palatable fashion. I am delighted that he has come out with this book that

explains the theory behind database systems, their architectures and issues in a way that even a

layman can understand. Atul has put to good use his practical experience while working with us at

i-flex, mixed with his very good grasp of theory of databases.

I wish him all the best in his future endeavours.

Deepak Ghaisas

CEO (India Operations)

i-flex Solutions Limited

Pune

(Preface

The traditional mechanism for storing computer data was data files. Data files have been immensely

popular since the 1960s. The earlier commercially successful programming languages such as COBOL

had extensive features related to file processing. In fact, even today, many major computer

applications run on file-based computer systems.

However, all this has also changed in the last few decades. Database Management Systems

(DBMS) has become a subject of great significance in the Information Technology industry. Most

serious business applications need the presence of DBMS in some form or the other. DBMS is

replacing files as the de facto standard for storing data that is medium/long term in nature. This is

especially true in the case of most newly developed applications.

DBMS is a fascinating subject. Many people confuse it with Structured Query Language (SQL).

However, SQL is just one aspect of DBMS technology. Understanding how DBMS technology really

works involves the study of many theoretical concepts, such as database design, modelling, transaction

management, security, concurrency, and so on.

I have read a number of books on DBMS. Most of them explain DBMS technology quite well,

but there is one aspect that I find quite intimidating, and that is the complexity of these books. I

have a strong and sincere belief that without using almost any jargon and mathematics, we can study

most aspects of the DBMS technology. It is intimidating to see complex mathematics and cryptic

symbols one page after the other when learning DBMS. That is the sole reason why I have

attempted to keep the material as simple as it can get. The idea is that even a person with very

little background in computers will be able to grasp the main concepts in DBMS, and if he/she is

interested, can go ahead and study the more complex features.

Here are the main features of the book.

Orientation: An attempt has been made to cover the topics in an appropriate sequence, so that

it is sufficient for the managers/professionals/teachers/students to refer to this book alone for

learning about DBMS.

Contents'. The book covers all the major concepts in the DBMS technology that a reader at

any level needs to understand.

Style: An attempt has been made to keep the language very simple and to make the

explanations extremely lucid.

Visual approach: The book features a number of illustrations and diagrams (close to 400) to

enable an easier grasp of the subject.

Pedagogical features: Every chapter contains a list of Key Terms and Concepts, Chapter

Summary and self-study questions in the form of True/False questions, Multiple-choice

questions and Detailed questions. In addition, Exercises are also provided at the end of every

chapter.

The chapter-wise organisation of the book is as follows.

Chapter 1 provides a lot of background material to the subject of DBMS. This chapter covers the

x Preface

basics of storing data. We study the meaning and puipose of fde systems with simple examples. We

also discuss the various types of data structures used in fde systems. The chapter also covers various

kinds of fde systems/organisations.

Chapter 2 introduces the concept of DBMS. It examines why we need DBMS in the first place.

We also study the advantages of DBMS over file-based systems. We then study the basics of SQL.

The key concepts in SQL, in the form of Data Definition Language (DDL), Data Manipulation

Language (DML), and Data Control Language (DCL) are explained here. We also study dynamic and

embedded SQL. The chapter also covers the various DBMS models, namely Hierarchical, Network,

and Relational.

Chapter 3 focuses on the most popular of the three DBMS models: the Relational Database

Management Systems (RDBMS). After a primer of RDBMS theory, we study relational algebra, and

relational calculus. We also take a look at the concepts of database integrity, constraints, keys, and

views.

Chapter 4 covers the topic of database design. We take a look at the concepts of functional and

transitive dependencies to start with. We then move on to the topic of Normalisation. All the five

important Normal Forms are covered in great detail with lots of examples and theory. Then we move

on to Entity/Relationship (E/R) modelling.

Chapter 5 explains the beauty of transactions. Transaction management is a very important

concept in DBMS. Without transactions, most serious business applications would simply not work.

The chapter details what we mean by Transactions, the concept of Recovery, Transaction models,

Two phase commit protocol, Concurrency problems, and how Locking can solve them. We also study

the idea of Deadlocks and Two phase locking.

Chapter 6 deals with the aspects of Database security. We cover the classification of data and

then move on to the kinds of risks we face in computer systems. The chapter moves on to the

technical details of Glyptography and Digital signatures. It also covers User and Object privileges.

The discussion concludes with an overview of Statistical databases.

Chapter 7 focuses on Query execution and optimisation. We study the impact of using indexes on

query performance. There are various ways in which the SELECT queries in SQL can be

implemented. We study all the background material related to this. Certain optimisation

recommendations are also provided. The chapter also covers the topic of Database statistics.

Chapter 8 explains the idea of Distributed databases. We study the Advantages, Architecture,

Issues, Techniques, Query processing, Concurrency control, Recovery, and Deadlocks related to

distributed databases. The chapter also covers the idea of Client/Server computing. We end with a

small note on C.J. Date's 12 rules related to distributed databases.

Chapter 9 begins with the basic concepts such as data, information, its quality, and value. It then

covers the relatively new topics of Decision support systems. Data warehousing, and Data mining. We

also discuss the concept of Online Analytical Processing (OLAP).

Chapter 10 deals with the marriage between Object technology and DBMS. We cover the key

Object oriented (00) concepts such as abstraction, encapsulation, and inheritance. We then study their

relevance to RDBMS and later on provide a detailed coverage of Object Oriented Database

Management Systems (OODBMS).

Chapter 11 covers some modem concepts in DBMS, such as Deductive databases, Internet and

DBMS, Multimedia databases, Digital libraries, and Mobile databases.

A number of appendices provide a solid base of Data structures, Case studies, Programming

examples, Searching and Sorting techniques, etc.

Preface xi

The following symbols have been used in the book.

Fundamental Concepts

Significant Points

I hope that the book has a significant use for the professionals, teachers and students of DBMS.

All the same, I am aware that there may be areas where we can improve. You are most welcome to

write to me at [email protected] for any comments or suggestions.

Atui. Kahate

1.

This page is intentionally left blank.

Acfqiozukdge

TtfMMi

Many people have done so much for me over the years that it is just not possible to mention all of

them here.

My wife Anita has been the best friend and critic of the manuscript, as ever. She has helped me

in so many ways that I cannot simply put it all in words. All I can say is that I am extremely

lucky to have such a loving, caring, supporting and understanding partner. I cannot describe the joy

that my two small kids Jui and Harsh have brought to my life. Life was so incomplete without

them! My parents Dr. Meena and Dr. Shashikant Kahate and all relatives have been standing behind

me like a wall, which is very reassuring.

I respect Mr Achyut Godbole more than anyone else for so many reasons. He has always

provided encouragement and constructive criticism.

There are so many friends, both at the workplace and outside that I would like to mention,

especially the Tea group at i-flex, which make the day at the workplace great. All my colleagues

have been very understanding, supportive, and encouraging.

1 would also like to thank Mr Nandu Kulkami and Mr Madhusudhan Magadi for allowing me to

use my spare time and facilities at the workplace for the development of this book. Colleagues like

Ravi Jadhav, Subhabrata Sanyal, Atul Edlabedkar, Ruchi Bhargava, Sandeep Jagnade, Dhananjay

Barve, Abhijit Agashe, Sachin Limkar, Mithoo Chakraborty, Javed Attar, Dayakar Yummadi, Amar

Gokhale, Richard Saldanha, Subramanian Krishnan, Sunil Gokhale, Bhupendra Porwal, and many

others make my day at work wonderful!

Last, but not the least, it is the expertise, enthusiasm and persistence of my Editor K Srinivas of

Pearson Education, which has kept me on my toes to try and complete this book in record time. I

cannot thank him enough. I would also like to express my gratitude to Shadan Perween of Pearson

Education, the Copy Editor of this book. And finally, thanks Deepak for your lively foreword.

Atul Kahate

This page is intentionally left blank.