DBMS Notes

Database:

A database is a collection of
related data, that are recorded in any medium.

For ex: Consider the names ,
telephone number and address of thepeople you know. We may recorded this data in an address book, or
recorded in a personal computer using Excel sheet . So this record is called
database.

A database have the following
properties:

a)A
database represents some aspect of the real worldsometimes its called mini world or Universe
of Discourse (UoD).

b)A
database is designed , built and associated with data for a specific purpose.

In other words , a database has
some source from which data is derived, some interaction with events and some
users that is interested in the content of database.

DBMS:

DBMS is a collection of programs (
A general purpose software) that provides the facility to the user to define,
construct , manipulate and share databases among various users and application.

Defining means to specify the data
types , structure and contents of the database . DBMS also keep the information
of database itself in database catalog . This information of database is called
Meta Data.

Constructing means to store the
data itself on some storage medium.

Manipulating means to retrieve the
data from the database and updating the data in the database.

Sharing means to allow multiple
users and programs to access the database.

DBMS System

Application Programs/Queries

Users/Programs

Software
to process Queries / Programs

DBMS Software

Software to access stored data

Stored
database

DBMS Architecture

Stored
database definition OR META DETA

Diffence Between DBMS System and DBMS Software:

DBMS system provides theenvironment in which all the DBMS user can
easily work on.In other words it provide an interface to the user through which
user can easily operate the DBMS software.

DBMS software is a collection of
programs which is used to process the queries of user and to access the
database to fulfill the requirement of user queries.

Actors on the Scene(DBMS users) :

There
are many types of users who are related to DBMS . These are:

a)DBA
(Database Administrator): In any organization there will be a leader who
leads all thestructure , manage all the
resources etc. So will be the chief administrator of theorganization. Like chief administrator , in
DBMS DBA is the leader of database. DBA is responsible for authorizing to
access the database, to monitoring its use and for acquiring software and
hardware resources as needed.

b)Database
Designer: Database designer are responsible to select appropriatestructure for represent andstore the data. Before select the structure
,Designer will meet all the database users and find out all the requirements
which they want to fulfill. After collect all the requirement it comes with
their design and structure and this work is completed before database
implementation.

c)End
Users: These are:

1)Casual Users:These are the users who communicate with the
database for little period oftime. They
are occasionally used the database. So these are the temporary users.

2)Parametric Or Naive Users: These are
the users who communicate with the database for a regular period. Their main
job is to constantly querying and updating the database usingstandardqueries , this iscalled Canned
Transaction.

3)Sophisticated End Users: These are
engineers, scientists, business analysts who are familiarize with the database
and when they want to use the database, they used it regularly and when they
don’t want then they are temporally used .

4)Stand alone users: These are the users
who maintain their personal database using ready made software which is
available in the market easily and provide the menu based interface through
which they can easily used the database.

d)System
Analysts and Application Programmers: System analysts find out therequirements of parametric end users and
develop all the analysis for canned transaction through which they can meet all
the requirements of parametric users. Application programmers implement these
analysis asprograms. These are
especially Software engineers.

Advantages of DBMS:

Thereare many advantages of DBMS over to file
system . These are:

a)Controlling
Redundancy: Here redundancy means
repetition . In normal file system we use same thing again and again which
increase the storage area and access cost of data. For ex: In a college , the
students names , their classes and roll no. are used in many sections like in
HR section , Account Section and T & P section. So we used same data again
and again in so many files. This type of redundancy is controlled in DBMS, In
DBMS we make a database of these names, classes and roll no. and it can be
shared among all the sections.

b)Easy
to access the data: In DBMS all data are stored in database in same format,
so to retrieve the data from the database iseasy because we use same application programs to retrieve data from
database whereas in file system data are stored in files andeach file has its own format. So each file
need separate application programs to retrieve data. This is also called Data Isolation.

c)Providing
Backup and Recovery: DBMS provides facilities to recover from hardware and
software failure. The backup and recovery subsystem of DBMS ensured that if a
system fails in the middle of any transaction then database is restored in the
state from where transaction is begin and when system is recovered it also
ensure that transaction is resumed from the point where it is interrupted .
This is called atomicity( To recover all the system automatically).

d)Providing
Multiple user interfaces: Because lot of userswho are used the database have different
types of computer skills or knowledge . Some users are technically strong and
some are weak. So to make easy to use a database to all type of users DBMS
provides many type of interfaces. It provides query languages for casual users,
Programming language for application users, forms and command interfaces for
parametric users and menu driven interfaces for stand alone users. Both forms
and menu driven interfaces are called GUI.

e)Restricted
Unauthorized access: When multiple users use a database then it is not sure
that every user have the permission to access that database . For ex: Financial
data are always be confidential and only limited users can access that data .
So in DBMS DBA(Database Administrator ) is responsible to restrict and
authorized the users to use the database.

f)Use
Specific Storage to easily processed the query: In DBMSa specific storage medium is used to store
the data because when we processed any query to retrieve data then its
processed time isdepend on ,how much
time take data to be searched. So to processed the query efficiently DBMS used
special type of storage medium auxiliary file or index file . By index file
data searching can be fast.

g)Data
Integration: Integration means combined different types ofdata with different length in a unit. In
DBMS we can store all thedata with
different data types and length . For ex: In any table bca we can usefour data types. These are: char, varchar,
numeric or number and date. SoDBMS
provides this facilities to integrate complex data types in one table or
database.

Data Abstraction:

There
are many database users who are not computer trained, so developers hide the
complex portion of database from users through several levels of abstraction.
These levels are:

b)Conceptual
level Or logical level or high level: Describes what data are stored in the
database and the relationship among the data.

c)View
level: The highest level describes only a part of database because
thisis the user level. User do not want
to know that where data is stored and from where data is retrieved so its not
the matter for user. So those part are hidden for users.

View 2

View Level

View n

View 1

Logical Level

PhysicalLevel

Data Model:

A collection of ideas that are used to describe the overall structure of
a database. Structure of database means type of data ,relationship among these
data and restriction which is applied to the data. Most of data models also
describe the basic operationsapplied to
the database (updating and retrieving).

Data model is used to
show that how can we hide some details of database from the users that is not
needed by the user.

Types of Data Model:

There are three type of data model and these model are categorized according
to the capabilities of describe the structure of database. These are:

a)High Level or Conceptual Data Models: Itdescribe that what data is stored and
how can we retrieve that data. It uses concepts to describe what data is stored
are entities , attributes ,and relationships. So E-R modelin the category of conceptual level.

b)Low level or Physical Data Models: It
describe how data is stored in the computer. Because user has no interest to
know that where data is stored so thisportion is always hidden to the users. This models are used specially
for computer specialists.

c)Representational or Implementation Data
Models: Between these two model representational model is come which hide
some details of data storage and somerepresentsto the end users.It represent data by using record structures
so sometime it is called record based
data models .This model is mostly used in traditional DBMS. This model also
include relational data model ,Network
and Hierarchical model(legacy model) ,

Instances:

When an information is added or deleted from a database at a particular
moment of time then it is called instances. In other wordsdatabase changes at a particular moment
iscalled instance.

Schema:

The overall design of a database is called Schema.

The description ofa database is
also called Schema. Schema are frequently changed when any entry in database is
added or deleted .

b)Conceptual level or High level or Logical
levelSchema: It describe databasedesign at conceptual level. It is totally
related to the data so we can say that it describe the overall design ofdata rather than database.

c)External or view level Schema: It
describe the database design at user level. Because at view level there are
manyuser view so at thislevel there are lot of schema is presented ,
one for each user.

EXTERNAL VIEW

EXTERNAL VIEW

EXTERNAL LEVEL.......................

External/Conceptual Mapping

CONCEPTUAL SCHEMA

CONCEPTUAL LEVEL

Conceptual/Internal Mapping

INTERNAL SCHEMA

INTERNAL LEVEL

Stored
Database

Data Independence:

Thecapacity to change the schema
at one level of a database without having to change the schema at the next
higher level is called Data Independence. There are two type of Data
Independence . These are:

a)Logical Data Independence: is the
capacity to change the conceptual schema without having to change the external
schema.

b)Physical Data Independence: is the
capacity to change the physical schema without having to change the conceptual
schema.

Database Languages:

To perform operations on database for every user DBMS provides mainly
three languages. These are:

a)DDL(Data Definition Language): This
language is used to specifythe database
schemas. It is used to create , modify and delete the database structure not
the data . So in this language these queries are used:

1)Create (It is used to create a database
not to create data).

2)Alter (It is used to change the
database structure not the data).

3)Drop(It is used to delete whole table
with its structure not only the data).

4)Comment(It is used to specify the
database not the data).

So DDL totally related to
the database structure not to the data.

b)DML(Data Manipulation language): Manipulation
means retrieval , updation and insertion of data so DML is totally related to
the data not to the database . This language is used to retrieve , update and
insert the data into the database so it uses update, select and insert command.
There are two types of DML.

1)Procedural DML: requires a user to
specify what data are neededand how to
get those data.

2)Non Procedural DML: requires a user to
specify what data are needed without specifying how to get those data.

c)DCL(Data Controlling Language): Is used
to control both data and database so it has therelation to the both. It is used by DBA who specify the authorization
and restriction given to the users. In DCL three commands are come. These are:

COMMIT

ROLLBACK

SAVEPOINT

Interfaces:

Interfaces are the
programs which convert system language to user understandable language and user
language to system understandable language. So its work like a translator which
provide an environment in which a user can easily operate the system. There are
many type of interfaces . These are:

a)Menu
Based interfaces:These
interface are used by the stand alone users.

b)Forms
Based or Command Based Interfaces:These are used by theparametric users.

c)GUI(Graphical
User Interface):These are used by both user , by parametric and by
stand alone user. Both Form based and Menu based are combindly called GUI.

Entity is the real world object
or in other words entity can be
anything in the real world which can be described by its attributes .

Attributes aresome properties which describes the entity.

Relationship describe the
association among two or more entities.

Keys:

Keys are thevalueswhich is used to identify the entities and to separate the entities.
There are many type of keys these are:

a)Super
Key: is the combination of one attribute(field) which has unique value and
any other field in the database. For ex. In any bank ,customer is identified by
customer name and account no. So combination of customer name and account no.
formed super key.

b)Candidate
Key: Because in super key we use some extraneous fieldwhile we can identifyor separate the entity through one fieldwhich hasunique value in the database. This unique attribute is referred as
candidate key.For ex . Customer can
be identified through account no. so there is no need to use extra field
customer name.

c)Primary
Key: Primary key is a candidate key
which is used to restrictduplicity in
any field. In candidate key there is no provision to avoid duplicity so this
concept is used. Primary key always be selected to those field at which we want
to applythe restriction to avoid
duplicity .

d)Composite
key: Composite key is the
combination of all primary key in a database .

e)Foreign
Key: is a field (or collection of fields) in a table whose value is
required to match thevalue of the
primary key for the second table.

Weak Entity: Those entities
which can not formed the primary key are called weak entities.

Strong Entity: Those entities
which can formed the primary key are called strong entities.

Extended(Enhanced ) ER Model:

The ER modeling
concepts are sufficient for representing traditional database application. For
more complex database application such as telecommunications , CAD/CAM , GIS
etc , we need more complex requirements than traditional applications. In late
1970’s database designers have tried to design more accurate ER model , which
reflects the data properties andconstraints more accurately . So extended(Enhanced ) ER model have some
enhanced features than normal ER model. It uses the concepts of Specialization
, Generalization , Aggregation.

Specialization:

Specialization is the
process through which we can relate one entity to more than one entity. In
other words specialization is the process to defining a set of subclasses of an
entity type. This entity is called superclass. For ex. An entity “employee” has
the sub entity , ‘faculty’, ‘Staff’. So employee has the relation with both the
sub entity. This relation name is “IS A”. as shown in fig. So Specialization
follow the process of one to many relationship.

Employee

Staff

Faculty

Specialization

Fig 4.4.8

Generalization:

Generalization is just
reverse of Specialization. Generalization is the process to define a
generalized entity type from the given entity type. For ex. Consider the two
entity CAR and TRUCK . Because both have some common attributes, they can
combindly make a super entity called VEHICLE. So it is the process to identify
the common features (attributes) from

two or more entity and
generalized them into a super entity.

Aggregation:

Aggregation is the
combined part of specialization and generalization. It is the process to define
both specialized and generalized entity. For ex. An employee can be faculty and
staff. It is the process of specialization and ,faculty and staff combindly
formed or generalized an entity ” employee” . So above fig 4.4.8 also show the
concept of aggregation.