MCS-023 Introduction to Database Management Systems

UNIT – 1

What is Database Management System?

Ans: A Database is an organised, persistent collection of data of an organisation.

A database is a persistent collection of logically related data.

DBMS controls access to this data and thus needs to provide features for database creation, data manipulation such as data value modification, data retrieval, data integrity and security etc

What is File Based System?

Computerize manual filing system

What is Limitation of File Based System?

Separation and isolation of data: When the data is stored in separate files it becomes difficult to access.

Duplication of data: Due to the decentralised approach, the file system leads to uncontrolled duplication of data.

Inconsistent Data: The data in a file system can become inconsistent if more than one person modifies the data concurrently,

Data dependence: The physical structure and storage of data files and records are defined in the application code. This means that it is extremely difficult to make changes to the existing structure.

Incompatible File Formats: Since the structure of the files is embedded in application programs, the structure is dependent on application programming languages.

Fixed Queries: File based systems are very much dependent on application programs. Any query or report needed by the organisation has to be developed by the application programmer.

What are the advantages of a DBMS?

Reduction of Redundancies: In database approach data can be stored at a single place or with controlled redundancy under DBMS, which saves space and does not permit inconsistency

Shared Data: A DBMS allows the sharing of database under its control by any number of application programs or users.

Data Independence: Database Management systems separates data descriptions from data. Hence it is not affected by changes. This is called Data Independence, where details of data are not exposed. DBMS provides an abstract view and hides details.

Improved Integrity: Data Integrity means that the data should be accurate and consistent. This is done by providing some checks or constraints.

Efficient Data Access: DBMS utilises techniques to store and retrieve the data efficiently at least for unforeseen queries. A complex DBMS should be able to provide services to end users, where they can efficiently retrieve the data almost immediately.

Multiple User Interfaces: Since many users having varying levels of technical knowledge use a database

Representing complex relationship among data: A database may include varieties of data interrelated to each other in many ways. A DBMS must have the capability to represent a variety of relationships among the data as well as to retrieve and update related data easily and efficiently.

Improved Security: Only authorised users may use the database and their access types can be restricted to only retrieval, insert, update or delete or any of these.

Improved Backup and Recovery: DBMS provides facilities for recovering the hardware and software failures.Support for concurrent transactions : A DBMS also allows multiple transactions to occur simultaneously

Compare and contrast the traditional File based system with Database approach

Three Level Architecture of DBMS or Logical DBMS Architecture

External Or View Level : The external or view level is the highest level of abstraction of database. It provides a window on the conceptual view, which allows the user to see only the data of interest to them. The user can be either an application program or an end user.

Conceptual Level or Global level: The conceptual level presents a logical view of the entire database as a unified whole. It allows the user to bring all the data in the database together and see it in a consistent manner. DBMS provides a data definition language for this purpose. The data definition language used to create the conceptual level must not specify any physical storage considerations.

Internal or physical level: The collection of files permanently stored on secondary storage devices is known as the physical database. The physical or internal level is the one closest to physical storage, and it provides a low-level description of the physical database, and an interface between the operating systems file system and the record structures used in higher levels of abstraction.

Mappings between Levels and Data Independence

There are two types of mappings:

The conceptual/internal mapping: The conceptual/internal mapping lies between the conceptual and internal levels, and defines the correspondence between the records and the fields of the conceptual view and the files and data structures of the internal view.

The external/conceptual mapping: The external/conceptual view lies between the external and conceptual levels, and defines the correspondence between a particular external view and the conceptual view.

May be

External/external mapping: one external view is expressed in terms of other external views. This is useful if several external views are closely related to one another, as it allows you to avoid mapping each of the similar external views directly to the conceptual level.

The need for three level architecture

Support of multiple user views: Each user is able to access the same data, but have a different customized view of the data. Each user should be able to change the way he or she views the data and this change should not affect other users.

Insulation between user programs and data that does not concern them: Users should not directly deal with physical storage details, such as indexing or hashing. The user’s interactions with the database should be independent of storage considerations.

PHYSICAL DBMS ARCHITECTURE

The physical architecture describes the software components used to enter and process data, and how these software components are related and interconnected. Although it is not possible to generalise the component structure of a DBMS, it is possible to identify a number of key functions which are common to most database management systems.

DDL Compiler : The DDL compiler converts the data definition statements (such as CREATE TABLE …. in SQL) into a set of tables containing metadata tables. These tables contain information concerning the database and are in a form that can be used by other components of the DBMS. These tables are then stored in a system catalog or data dictionary.

DML Precompiler :Data Manipulation Language (DML) which defines the set of commands that modify, process data to create user definable output. The DML statements can also be written in an application program. The DML precompiler converts DML statements (such as SELECT…FROM in Structured Query Language (SQL) embedded in an application program to normal procedural calls in the host language. The precompiler interacts with the query processor in order to generate the appropriate code.

File Manager: File manager manages the allocation of space on disk storage. It establishes and maintains the list of structures and indices defined in the internal schema that is used to represent information stored on disk.

Database Manager : A database manager is a program module responsible for interfacing with the database file system to the user queries. In addition, the tasks of enforcing constraints to maintain the consistency and integrity of the data as well as its security are also performed by database manager.

Let us summaries now the important responsibilities of Database manager.

• Interaction with file manager

• Integrity enforcement

• Security enforcement

• Backup and recovery

• Concurrency control

• Authorisation control

• Command Processor

• Integrity checker

• Query Optimiser

• Transaction Manager

• Scheduler

• Recovery Manager

• Buffer Manager

Query Processor: The query language processor is responsible for receiving query language statements and changing them from the English-like syntax of the query language to a form the DBMS can understand. The query language processor usually consists of two separate parts: the parser and the query optimizer.

The parser receives query language statements from application programs or command-line utilities and examines the syntax of the statements to ensure they are correct.

The query optimiser examines the query language statement, and tries to choose the best and most efficient way of executing the query.

Database Administrator: The DBA administers the three levels of the database and defines the global view or conceptual level of the database. The DBA also specifies the external view of the various users and applications and is responsible for the definition and implementation of the internal level, including the storage structure and access methods to be used for the optimum performance of the DBMS.

The DBA is responsible for granting permission to the users of the database and stores the profile of each user in the database.

The DBA is also responsible for defining procedures to recover the database from failures due to human, natural, or hardware causes with minimal loss of data.

Thus, the functions of DBA are:

• Schema definition

• Storage Structure and access method definition

• Schema and Physical organisation modification

• Granting of authorisation for data access

• Integrity constraint specification

Data files Indices : The data is stored in the data files. The indices are stored in the index files. Indices provide fast access to data items. For example, a book database may be organised in the order of Accession number, yet may be indexed on Author name and Book titles.

 and Data Dictionary : A comprehensive data dictionary would provide the definition of data items, how they fit into the data structure and how they relate to other entities in the database. In DBMS, the data dictionary stores the information concerning the external, conceptual and internal levels of the databases. It would combine the source of each data field value, that is from where the authenticate value is obtained. The frequency of its use and audit trail regarding the updates including user identification with the time of each update is also recorded in Data dictionary.