20CS07 Data Base Management Systems

UNIT I

Module 1: INTRODUCTION

Program & Semester: B.Tech & III SEM

Academic Year: 2022 - 23

An overview of Database Management System

A database-management system (DBMS) is a collection of interrelated data and a set of programs to access those data. The collection of data, usually referred to as the database. (or)

The DBMS is a general-purpose software system that facilitates the processes of defining, constructing, manipulating, and sharing databases among various users and applications.

​

Database has the following implicit properties:

• A database represents some aspect of the real world, sometimes called the miniworld or the universe of discourse (UoD).
• A database is a logically coherent collection of data with some inherent

Meaning.

• A database is designed, built, and populated with data for a specific purpose. It has an intended group of users and some preconceived applications in which these users are interested.

​

​

Database System Applications

• Banking
• Airlines
• Universities
• Credit card transactions
• Finance
• Sales
• Manufacturing
• Human resources

�Evolution of Database Management Systems�

• File Management System
• Hierarchical database System
• Network Database System
• Relational Database System

​

​

Based on the data model

• Hierarchical database
• Network database
• Relational Database
• Object oriented database
• Object related database

​

Based on the users

• Single user
• Multiple users

Classification of Database Management System

Database Systems versus File Systems

Based on the sites over which network is distributed

• Centralized database system
• Parallel network database system
• Distributed database system

​

​

• One way to keep the information on a computer is to store it in operating system files. File-processing system is supported by a conventional operating system.
• The system stores permanent records in various files, and it needs different application programs to extract records from, and add records to, the appropriate files.
• A file management system is an abstraction to store, retrieve, and management and update a set of files. A File Management System keep track on the files and also manage them

Drawbacks of using file systems

Data redundancy and inconsistency

Multiple file formats, duplication of information in different files

​

​

Difficulty in accessing data

Need to write a new program to carry out each new task

Data isolation

multiple files and formats

Integrity problems

• Integrity constraints (e.g. account balance > 0) become part of program code.
• Hard to add new constraints or change existing ones

Atomicity of updates

• Failures may leave database in an inconsistent state with partial

updates carried out

E.g. transfer of funds from one account to another should either

complete or not happen at all

Concurrent access by multiple users

• Concurrent accessed needed for performance
• Uncontrolled concurrent accesses can lead to inconsistencies

E.g. two people reading a balance and updating it at the same time

Security problems

Advantages of DBMS:

​

• Data Independence
• Efficient data access
• Data Integrity and Security.
• Data Administration.
• Concurrent access and Crash recovery.
• Reduced application development time.

Data Models

A database model shows the logical structure of a database, including the relationships and constraints that determine how data can be stored and accessed. Individual database models are designed based on the rules and concepts of whichever broader data model the designers adopt. Most data models can be represented by an accompanying database diagram.

We have different data models

• The Entity-Relationship Model
• Relational Model
• Object oriented data Model
• Object Relational Model
• Network data Model
• Hierarchical data model

​

The Entity-Relationship(ER) Model

The entity-relationship (E-R) data model is based on a perception of a real world that consists of a collection of basic objects, called entities, and of relationships among these objects.

​

• An entity is a “thing” or “object” in the real world that is distinguishable from other objects.
• Entities are described in a database by a set of attributes. A unique identifiable attribute must be assigned to each entity.
• The set of all entities of the similar type are termed as Entity set.
• A relationship is an association among several entities. Set of all relationship of the similar type are termed as relationship set.
• The overall logical structure (schema) of a database can be expressed graphically by an E-R diagram, which is built up from the following components:

• Rectangles: which represent entity sets

• Ellipses: which represent attributes

• Diamonds: which represent relationships among entity sets

• Lines: which link attributes to entity sets and entity sets to relationships

​

Relational Model

• The relational model uses a collection of tables to represent both data and the relationships among those data. Each table has multiple columns, and each column corresponds to an attribute which has a unique name.
• Together, the attributes in a relation are called a domain.
• A particular attribute or combination of attributes is chosen as a primary key that can be referred to in other tables, when it’s called a foreign key.
• Each row, also called a tuple, includes data about a specific instance of the entity. Relational databases are typically written in Structured Query Language (SQL). The model was introduced by E.F. Codd in 1970.

​

Object oriented model

This model defines a database as a collection of objects, or reusable software elements, with associated features and methods. There are several kinds of object-oriented databases:

A multimedia database incorporates media, such as images, that could not be stored in a relational database.

A hypertext database allows any object to link to any other object. It’s useful for organizing lots of disparate data, but it’s not ideal for numerical analysis.

The object-oriented database model is the best known post-relational database model, since it incorporates tables, but isn’t limited to tables.

​

Object-relational model

This hybrid database model combines the simplicity of the relational model with some of the advanced functionality of the object-oriented database model. In essence, it allows designers to incorporate objects into the familiar table structure.

Network data Model

A network model is nothing but a generalization of the hierarchical data model as this data model allows many to many relationships therefore in this model a record can also have more than one parent.

The network model can be represented as a graph and hence it replaces the hierarchical tree with a graph in which object types are the nodes and relationships are the edges.

Hierarchical data model

The hierarchical data model is one of the oldest data models, developed in the 1950s by IBM. In this data model, the data is organized in a hierarchical tree-like structure. This data model can be easily visualized because each record has one parent and many children (possibly 0) as shown in the image given below.

​

Three Schema Architecture

A database system is a collection of interrelated files and a set of programs that allow users to access and modify these files. It provides users with an abstract view of the data. That is, the system hides certain details of how the data are stored and maintained.

Developers hide the complexity from users through several levels of abstraction, to simplify users’ interactions with the system.

The data in the DBMS is described at three levels of abstraction

• Physical Level (Internal Schema)
• Logical Level (Conceptual Schema)
• View Level (External Schema)

Physical Level

The lowest level of abstraction describes how the data are stored. The physical level has an internal schema which describes the physical storage structure of the database.

Logical Level

• Logical level describes what data are stored in the database, and what relationships exist among those data.
• Logical level has a conceptual schema, which describes the structure of the whole database for a community of users.

The conceptual schema hides the details of physical storage structures and concentrates on describing entities, data types, relationships, user operations, and constraints.

View level

• This is the highest level in data abstraction.
• This level includes number of external schemas or user views. Each external schema describes the part of the database that a particular user group is interested in and hides the rest of the database from that user group.

​

Database Schema and Instances

The description of a database is called the database schema, which is specified during database design and is not expected to change frequently.

​

• A schema diagram displays only some aspects of a schema, such as the names of record types and data items, and some types of constraints.
• The data in the database at a particular moment in time is called a database state or Instance.
• Database schema defines the variable declarations in tables that belong to a particular database, the values of these variables at a particular moment of time is called data instance.
• The distinction between database schema and database state is very important
• The DBMS stores the descriptions of the schema constructs and constraints—also called the meta-data

Data Independence

• Data Independence can be defined as the capacity to change the schema at one level of a database system without having to change the schema at the next higher level.
• Metadata itself follows a layered architecture, so that when we change data at one layer, it does not affect the data at another level. This data is independent but mapped to each other.
• There are two types of Data Independence
• Logical Data Independence
• Physical Data Independence

​

​

Logical data independence is the capacity to change the conceptual schema without having to change external schemas or application programs.

Physical data independence is the capacity to change the internal schema without having to change the conceptual schema. Hence, the external schemas need not be changed as well.

​

​

Database languages are used for read, update and store data in a database. There are several such languages that can be used for this purpose; one of them is SQL (Structured Query Language).

A database system provides a data definition language to specify the database schema and a to express database queries and updates. data manipulation language.

There are three categories of DBMS Languages. Those are

1) Data Definition Language (DDL)

2) Data Manipulation Language (DML)

3) Data Control Language (DCL)

​

Database Languages

Data Definition Language (DDL)

DDL or Data Definition Language actually consists of the SQL commands that can be used to define the database schema. It simply deals with descriptions of the database schema and is used to create and modify the structure of database objects in the database.

​

CREATE – is used to create the database or its objects (like table, index, function, views, store procedure and triggers).

DROP – is used to delete objects from the database.

ALTER-is used to alter the structure of the database.

COMMENT –is used to add comments to the data dictionary.

RENAME –is used to rename an object existing in the database

TRUNCATE–is used to remove all records from a table, including all spaces allocated for the records are removed.

​

Data Manipulation Language (DML)

The SQL commands that deals with the manipulation of data present in the database belong to DML or Data Manipulation Language and this includes most of the SQL statements. 

Examples of DML: 

INSERT – is used to insert data into a table.

UPDATE – is used to update existing data within a table.

DELETE– is used to delete records from a database table.

​

​

Data Control Language (DCL)

DCL includes commands such as GRANT and REVOKE which mainly deal with the rights, permissions and other controls of the database system. 

​

Examples of DCL commands: 

​

GRANT-gives user’s access privileges to the database.

REVOKE-withdraw user’s access privileges given by using the GRANT command.

Database System Structure

A database system is partitioned into modules that deal with each of the responsibilities of the overall system. The functional components of a database system can be broadly divided into the storage manager and the query processor components.

​

Storage manager

• A storage manager is a program module that provides the interface between the low-level data stored in the database and the application programs and queries submitted to the system.
• Thus, the storage manager is responsible for storing, retrieving, and updating data in the database. The storage manager components include:

​

Authorization and integrity manager: which tests for the satisfaction of integrity constraints and checks the authority of users to access data.

Transaction manager: which ensures that the database remains in a consistent (correct) state despite system failures, and that concurrent transaction executions proceed without conflicting.

File manager: which manages the allocation of space on disk storage and the data structures used to represent information stored on disk.

Buffer manager: which is responsible for fetching data from disk storage into main memory, and deciding what data to cache in main memory. The buffer manager is a critical part of the database system, since it enables the database to handle data sizes that are much larger than the size of main memory.

The storage manager implements several data structures as part of the physical system implementation:

• Data files: which store the database itself.

• Data dictionary: which stores metadata about the structure of the database, in particular the schema of the database.

• Indices: which provide fast access to data items that hold particular values.

The Query Processor

The query processor components include

• DML compiler, which translates DML statements in a query language into an evaluation plan consisting of low-level instructions that the query evaluation engine understands.

A query can usually be translated into any of a number of alternative evaluation plans that all give the same result. The DML compiler also performs query optimization, that is, it picks the lowest cost evaluation plan from among the alternatives.

​

• DDL interpreter, which interprets DDL statements and records the definitions in the data dictionary.

• Query evaluation engine, which executes low-level instructions generated by the DML compiler

​