Table of Contents Chapter 1: Introduction to DBMS

1

Introduction DBMS overview

1

Advantage & Disadvantage of DBMS

6

Shifting from File System to DBMS

10

Summary

14

Key Terms

15

Important Topic Discussion

16

Chapter 2: DBMS Concepts

18

Components of DBMS

18

DBMS Architecture

21

Database Models

24

Database Administrator (DBA)

27

Summary

31

Key Terms

32

Important Topic Discussion

33

Chapter 3: Relational Model & ER Model

34

Basic Relational DBMS Concepts

34

Codd's Rule for Relational DBMS

35

Relational Algebra

37

Relational Calculus

41

ER Model – Basic Concept

43

Introduction to Database Keys

54

Summary

57

Key Terms

58

Important Topic Discussion

59

Chapter 4: Normalization

61

Overview of Normalization

61

Functional Dependency & Attribute Closure

63

Normalization Rules

68

Relational Decomposition

78

Summary

83

Key Terms

83

Important Topic Discussion

83

Chapter 5: Transaction Management

88

Transaction Concept

88

Transaction Property

89

Schedule

93

Deadlock in DBMS

109

Concurrency Control

112

Concurrency Control Protocols

116

Thomas Write Rule

122

Summary

125

Key Terms

126

Important Topic Discussion

127

Chapter 6: Database File Organization & Indexing

130

Storage & File Structure

130

File Organization

134

Indexing

141

B+ Tree

146

Hashing

148

Summary

155

Key Terms

156

Important Topic Discussion

156

Chapter 7: Structured Query Language (SQL)

159

DBMS Objects & SQL Concept

159

DDL

178

DML

187

DCL & TCL

192

Advance SQL

206

Summary

237

Key Terms

237

Important Topic Discussion

238

Chapter 8: Commonly asked DBMS interview questions 241

SET 1

241

SET 2

256

SET 3

263

SET 4

282

Preface “I said it's impossible to have an amnesty without ID cards and a clean database, because you firstly don't have any incentives for people to actually come up front and register, and make themselves available, and secondly you have no means of tracking them. “ -David Blunkett Database management has evolved from a specialized computer application to a central component of a modern computing environment, and, as a result, knowledge about database systems has become an essential part of an education in computer science. In this text, we present the fundamental concepts of database management. These concepts include aspects of database design, database languages, and database-system implementation. In this book, almost all the conceptual knowledge is being covered with brief problem statement. The one who ever is interested in gaining the familiarity with the database concepts, what database is all about, how to deal with it, this book is appropriate for them. In addition to that, courses introducing students of any discipline to database management system is appropriate for them. This book assumes that students have a vivid knowledge about computer basics and may not have any knowledge of programming. Special Features      

Detailed coverage of Introduction to Database and Database administrator. Functions provided by Database system A clear description about database design A huge concept of normalization Numerous problems solve of relational algebra and relational calculus. Detailed coverage on SQL queries and advance SQL

Acknowledgement I am grateful to get the motivation and guidance from various people during the journey of writing this book. First of all, I am grateful to almighty for protecting me and my family and being kind on me for giving the opportunity to fulfill my dreams. I wish to express my sincere gratitude to my father Mr. Subhabrata Ray, mother Mrs. Kakali Ray and brother Mr. Sukalyan Ray for constant encouragement, emotional and moral support which pushed me closer towards the fulfillment of my dream I would also like to thank Mr. Arunabha Tarafdar, Mrs. Kriti Sarkar and my friends for helping me in this journey. Last but not the least; I would like to thank my department and college administration for their support in this journey. Sukanya Roy

Chapter 1 INTRODUCTION TO DBMS Learning Objective    

Introduction to Basic database terminologies Description of database Explanation of advantages and disadvantages of dbms. Basic questions discussion with answers

Introduction In this chapter, you will get the important references about basic concepts of data and databases which are very important for going into depth in this subject. You will also learn how an employee faces problem in databases. Then you will study about the basic terms related to dbms. Finally you will get to know about the advantages and disadvantages of using dbms along with the basic questions that are frequently asked in the interviews regarding the basic concepts of dbms.

What is Data and Database? Larry Ellison, the co-founder of Oracle was amongst the first few, who realized the need for software based Database Management System. Database is a collection of related data and data is a collection of facts and figures that can be processed to produce information. Mostly data represents recordable facts. Data aids in producing information, which is based on facts. For example, if we have data about marks obtained by all students, we can then conclude about toppers and average marks. Data is nothing but facts and statistics stored or free flowing over a network, generally it's raw and unprocessed. For example: Data becomes information when it is processed, turning it into something meaningful. Like, based on the cookie data saved on user's browser, if a website can analyze that generally men of age 20-25 visit us more, that is information, derived from the data collected.

1

A Database is a collection of related data organized in a way that data can be easily accessed, managed and updated. Database can be software based or hardware based, with one sole purpose, storing data. During early computer days, data was collected and stored on tapes, which were mostly write-only, which means once data is stored on it, it can never be read again. They were slow and bulky, and soon computer scientists realized that they needed a better solution to this problem. Data and Database Schema Fundamental hypothesis of database modeling: the information contained in a database is represented on two levels: (1) data (large, frequently modified) (2) structure of data (small, stable in time) Database schema: A database schema is the skeleton structure that represents the logical view of the entire database. It defines how the data is organized and how the relations among them are associated. It formulates all the constraints that are to be applied on the data. A database schema defines its entities and the relationship among them. It contains a descriptive detail of the database, which can be depicted by means of schema diagrams. It‟s the database designers who design the schema to help programmers understand the database and make it useful. DBMS software is application-independent, it consults the database structure in the data dictionary to understand and execute application programs.

Fig 1 DBMS Schema

A database schema can be divided broadly into two categories −

2

Physical Database Schema − this schema pertains to the actual storage of data and its form of storage like files, indices, etc. It defines how the data will be stored in a secondary storage. Logical Database Schema − this schema defines all the logical constraints that need to be applied on the data stored. It defines tables, views, and integrity constraints. Database Instance: It is important that we distinguish these two terms individually. Database schema is the skeleton of database. It is designed when the database doesn't exist at all. Once the database is operational, it is very difficult to make any changes to it. A database schema does not contain any data or information. A database instance is a state of operational database with data at any given time. It contains a snapshot of the database. Database instances tend to change with time. A DBMS ensures that its every instance (state) is in a valid state, by diligently following all the validations, constraints, and conditions that the database designers have imposed.

DBMS Overview A database management system (DBMS) is a software package designed to define, manipulate, retrieve and manage data in a database. A DBMS generally manipulates the data itself, the data format, field names, record structure and file structure. It also defines rules to validate and manipulate this data. A DBMS relieves users of framing programs for data maintenance. Fourth-generation query languages, such as SQL, are used along with the DBMS package to interact with a database. Some other DBMS examples include:  MySQL  SQL Server  Oracle  dBASE  FoxPro

3

A database management system receives instruction from a database administrator (DBA) and accordingly instructs the system to make the necessary changes. These commands can be to load, retrieve or modify existing data from the system.

Fig 2 Example of Database

A DBMS always provides data independence. Any change in storage mechanism and formats are performed without modifying the entire application. There are four main types of database organization: Relational Database: Data is organized as logically independent tables. Relationships among tables are shown through shared data. The data in one table may reference similar data in other tables, which maintains the integrity of the links among them. This feature is referred to as referential integrity – an important concept in a relational database system. Operations such as "select" and "join" can be performed on these tables. This is the most widely used system of database organization. Flat Database: Data is organized in a single kind of record with a fixed number of fields. This database type encounters more errors due to the repetitive nature of data. Object-Oriented Database: Data is organized with similarity to object-oriented programming concepts. An object consists of data and methods, while classes group objects having similar data and methods.

4

Hierarchical Database: Data is organized with hierarchical relationships. It becomes a complex network if the one-to-many relationship is violated. Characteristics Traditionally, data was organized in file formats. DBMS was a new concept then, and all the research was done to make it overcome the deficiencies in traditional style of data management. A modern DBMS has the following characteristics − 1. Real-world entity − A modern DBMS is more realistic and uses real-world entities to design its architecture. It uses the behavior and attributes too. For example, a school database may use students as an entity and their age as an attribute. 2. Relation-based tables − DBMS allows entities and relations among them to form tables. A user can understand the architecture of a database just by looking at the table names. 3. Isolation of data and application − A database system is entirely different than its data. A database is an active entity, whereas data is said to be passive, on which the database works and organizes. DBMS also stores metadata, which is data about data, to ease its own process. 4. Less redundancy − DBMS follows the rules of normalization, which splits a relation when any of its attributes is having redundancy in values. Normalization is a mathematically rich and scientific process that reduces data redundancy. 5. Consistency − Consistency is a state where every relation in a database remains consistent. There exist methods and techniques, which can detect attempt of leaving database in inconsistent state. A DBMS can provide greater consistency as compared to earlier forms of data storing applications like file-processing systems. 6. Query Language − DBMS is equipped with query language, which makes it more efficient to retrieve and manipulate data. A user can apply as many and as different filtering options as required to retrieve a set of data. Traditionally it was not possible where fileprocessing system was used. 7. Multiuser and Concurrent Access − DBMS supports multi-user environment and allows them to access and manipulate data in 5

Ke yFe a t ur e s Us e r f r i e ndl ya ppr oa c hi ne a s yl a ng u a g e Nu mbe rofs ol v e dpr obl e ms Cl e a r l ye x pl a i ne dpr obl e ms ol v i ngt e c hni q u e s Di s c u s s i onofdi f f e r e ntpr obl e msa ndq u e s t i onswi t he x pl a na t i on S u k a ny aRo yi sAs s i s t a ntpr of e s s orofCompu t e rS c i e nc e Eng i ne e r i ng a tt he Uni v e r s i t y of Eng i ne e r i ng a nd Ma na g e me nt ,Kol k a t a( UEM) .S hehol d saB. Te c ha nd M. Te c hi nI nf or ma t i onTe c hnol og yf r om Go v e r nme nt Col l e g eofEng i ne e r i nga ndCe r a mi cTe c hnol og y ,u nd e r Ma u l a naAbu lKa l a m Az a dUni v e r s i t yofTe c hnol og y ( MAKAUT)a ndaDi pl omai nEng i ne e r i ngf r om A. P . C. Ro yPol y t e c hni c , J a d a v pu rWe s tBe ng a l . S hei st hea u t horofn u me r ou sr e s e a r c hpu bl i c a t i onsa ndj oi ne da sar e v i e we r onI J CVI P( I NTERNATI ONALJ OURNALOFCOMPUTERVI S I ON& I MAGES ENS I NG)i n2 0 1 8 .S heha swor k e dv i v i d l yi nt hef i e l dofI ma g e Pr oc e s s i nga ndt oe x t e ndhe rf u r t he rr e s e a r c hs hei sc u r r e nt l ywor k i ngi nt he a r e a sofDBMS ,Gr a phi c sa ndMu l t i me d i a ,a mong s tot he r s .He rmot i v a t i on f orbe i ngt hea u t horoft hi sbooki st opr o v i d es t u d e nt swi t haha nd book c ompr i s i ngDBMS ,S QLQu e r y ,a ndj obi nt e r v i e wr e l a t e dq u e s t i ons ,a nd t r a i ni ngf orhi g he rs t u d i e s a ndr e l a t e dc ompe t i t i v ee x a mi na t i onsi nt hef i e l dof t e c hnol og y .

ACADEMI C

I NR286/ -