Database Management System (DBMS)

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.

Database Management System (DBMS)

A database is the back-end of an application. A DBMS 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.

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:

1. 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.

2. 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.

3. 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.

Components of Database System

The database system can be divided into four components.

 Users : Users may be of various type such as DB administrator, System

developer and End users.
 Database application : Database application may be Personal,
Departmental, Enterprise and Internal
 DBMS : Software that allow users to define, create and manages database
access, Ex: MySql, Oracle etc.
 Database : Collection of logical data.
Functions of DBMS

 Provides data Independence

 Concurrency Control
 Provides Recovery services
 Provides Utility services
 Provides a clear and logical view of the process that manipulates data

Advantages of DBMS

 Segregation of applicaion program.

 Minimal data duplicacy.
 Easy retrieval of data.
 Reduced development time and maintainance need.

Disadvantages of DBMS

 Complexity
 Costly
 Large in size
Database Language

SQL statements are divided into two major categories:

Data Definition Language (DDL) and Data Manipulation Language (DML).

1. Data Definition Language (DDL) statements are used to define the

database structure or schema.

Some examples:

* CREATE - to create objects in the database

* ALTER - alters the structure of the database

* DROP - delete objects from the database

* TRUNCATE - remove all records from a table, including all spaces allocated for
the records are removed

* COMMENT - add comments to the data dictionary

* RENAME - rename an object

 2. Data Manipulation Language (DML) statements are used for managing
data within schema objects. Some examples:

* SELECT - retrieve data from the a database

* INSERT - insert data into a table

* UPDATE - updates existing data within a table

* DELETE - deletes all records from a table, the space for the records remain

* MERGE - UPSERT operation (insert or update)

* CALL - call a PL/SQL or Java subprogram

* EXPLAIN PLAN - explain access path to data

* LOCK TABLE - control concurrency

3. Data Control Language (DCL) statements. Some examples:

* GRANT - gives user's access privileges to database

* REVOKE - withdraw access privileges given with the GRANT command

Transaction Control (TCL) statements are used to manage the changes made by
DML statements. It allows statements to be grouped together into logical
transactions.

* COMMIT - save work done

* SAVEPOINT - identify a point in a transaction to which you can later roll back

* ROLLBACK - restore database to original since the last COMMIT

* SET TRANSACTION - Change transaction options like isolation level and what
rollback segment to use

Entity-Relationship Model

There are two techniques used for the purpose of data base designing from the
system requirements. These are:

• Top down Approach known as Entity-Relationship Modeling

• Bottom Up approach known as Normalization.

we will focus on top down approach of designing database. It is a graphical

technique, which is used to convert the requirement of the system to graphical
representation, so that it can become well understandable. It also provides the
framework for designing of database.

The Entity-Relationship (ER) model was originally proposed by Peter in 1976 as a

way to unify the network and relational database views. Simply stated, the ER
model is a conceptual data model that views the real world as entities and
relationships. A basic component of the model is the Entity-Relationship diagram,
which is used to visually represent data objects. For the database designer, the
utility of the ER model is:

• It maps well to the relational model. The constructs used in the ER model can
easily be transformed into relational tables.

• It is simple and easy to understand with a minimum of training. Therefore, the

model can be used by the database designer to communicate the design to the
end user.

• In addition, the model can be used as a design plan by the database developer
to implement a data model in specific database management software.

E-R modeling is a conceptual level model Proposed by P.P. Chen in 1970s

 Entities are real-world objects about which we collect data contant

 Attributes describe the entities

  Relationships are associations among entities

 Entity set – set of entities of the same type

 Relationship set – set of relationships of same type

Relationships sets may have descriptive attributes

Represented by E-R diagrams

 We can also states the overall logical structure of a database using ER
model graphically with the help of an E-R diagram.

ER diagrams are composed of:

 Rectangles showing entity sets.

 Ellipses showing attribute.

 Diamonds showing relationship sets.

Figure: ER diagram of COLLEGE database

AN ARCHITECTURE FOR DATABASE SYSTEM
EXTERNAL LEVEL
How data is viewed by an individual user

CONCEPTUAL LEVEL
How data is viewed by a community of users

INTERNAL LEVEL
How data is physically stored

EXTERNAL External External

VIEW
View View
mappings

CONCEPTUAL Conceptual Schema

LEVEL

mappings
Internal Schema
INTERNAL
LEVEL

STORED DATABASE

THE DATABASE ADMINISTRATOR

 Defines conceptual schema

 Defines internal schema
 Communicates with users
 Defines security and integrity checks
 Defines backup and recovery procedures
 Monitors performance and decide when to reorganize the database
 The Database Users fall into several categories:
What is a Database Users

• Application Programmers are computer professionals interacting with the

system through DML calls embedded in a program written in a host language
(e.g. C, PL/1, Pascal): These programs are called Application Programs. The
DML Precompiled converts DML calls (prefaced by a special character like $, #,
etc.) to normal procedure calls in a host language.

 The host language compiler then generates the object code. Some special
types of programming languages combine Pascal-like control structures
with control structures for the manipulation of a database. These are
sometimes called Fourth-Generation Languages. They often include
features which to generate forms and display data.

• Sophisticated Users interact with the system without writing programs :They
form requests by writing queries in a database query language. These are
submitted to a query processor that breaks a DML statement down into
instructions for the database manager module.

 Specialized Users are sophisticated users writing special database

application programs. These may be CADD systems, knowledge-based and
expert systems, complex data systems (audio/video), etc.
 Naive Users are unsophisticated users who interact with the system by
using permanent application programs (e.g. automated teller machine).