Chapter 8

Database management System

Concept of Database
A database is an organized collection of inter related data. A DBMS (DataBase ManagementSystem) is a set of
programs used to create, access and maintain a database. The primary goal of DBMS is to provide an environment
that is both convenient and efficient in storing and retrieving database information.

Drawbacks of conventional file management system

* Must keep more copies of same data for different application. This will leads to duplication of data
 * Data inconsistency – different users accessing the data simultaneously may change the data
 * Data is not properly organized , so data retrieval is difficult and time consuming
 * No way to ensure that data is restored to a consistent state if a system crashes occur
 * Provide only password mechanism for security
 * There is no standardisation on data
Advantages of DBMS
Database systems are designed to manage large volumes of information. A DBMS consists of a collection of data
and a set of programs to access those data. A DBMS has the following advantages
1) Data Redundancy can be avoided:-Duplication of data is called data redundancy. In a file management system,
same data may be stored in many files. This duplication of data is called redundancy. Redundancy leads to higher
storage cost of data. A DBMS keeps data at one place and all users and applications access the centrally maintained
database and avoids data redundancy.
2) Data inconsistency can be reduced:-Data redundancy leads to data inconsistency. ie,when the various copies of
the same data does not match each other. An inconsistent database provides incorrect data. Inconsistency can be
controlled by controlling data redundancy.
3) Efficient data access:-A DBMS provides an efficient access to database.
4) Data integrity:-Data integrity refers to correctness, completeness and accuracy of data stored in the database.
Data integrity is maintained through implementing a set of rules and procedures. It can also be done by using error
checking and validation.
5) Data Security:-Data security refers to protecting data against accidental lose or accessing /modifying
data by unauthorized users. Data security can be done by using passwords.
6) Sharing of data:-The data stored in the database can be shared among multiple programs and users.
7) Enforces standard:-The database enforces standard. Some standards include-naming convention,
display format, report structure, access rules [Link] standards may be laid by the organization or
individual who uses data.
8) Crash Recovery:-A DBMS provides a mechanism for data backup and recovery from crash recovery /
hardware failure.

Components of DBMS
A DBMS consists of the following components,
1)Hardware
2)Software
3)Data
4)Users
5)Procedures
Hardware:-Hardware includes computers (Server,PCs) , storage devices(hard disks,magnetic tape),network
devices(hub,switch) etc for data storage and retrieval.
Software:-The Software consists actual DBMS, application programs and utilities .A DBMS acts as a bridge
between the users and database. The users access the database using application programs. Utilities are the software
tools used to help manage the database system.
Data:-It is an important component of DBMS. A database contains operational data and meta-data (data about
data).The data in the DBMS is organized in the form of Field, Record and Files. A DBMS provides a centralised
control of data.
Field:-A field is the smallest unit of stored data. Each field consist of data of a specific type.
For example Roll No,Name,Place etc.
Record:-A record is a collection of related fields.
Files:-A files is a collection of same type of records.
Users:-The users access the data by using application programs. Depending on the mode of interaction with a
DBMS database users are classified into Data Base Administrator (DBA),Application Programmer, Sophisticated
user and Naive user.
Procedure:- Procedures are rules and instructions that govern the design and use of a database. It may include
instruction to start and stop DBMS, backup database, login to database etc.
Data Abstraction
The major purpose of database is to provide an abstract view of data. ie, the system hides details of how data is
stored and maintained. A database system is designed using three levels of abstraction Physical Level, Logical
Level, View Level.
1) Physical Level(Internal Level):-It is the lowest level of abstraction. It describes how data is actually stored in
the storage medium such as disks, tapes etc and which file organization is used . It describes complex low level data
structure in detail.
2) Logical Level(Conceptual Level):-Logical level describes what data are stored in the database and the
relationship between data. It is also called global view and represents the entire database. It is used by database
administrator.
3) View Level(External Level):-This is the highest level of database abstraction and is near to the users. It is
concerned with the way in which individual users view the data. It describes only a part of entire database. This
simplifies the interaction with the system.

Data Independence
The ability to modify the schema definition in one level without affecting the schema definition at the next higher
level is called data independence. There are two levels of data independence
1. Physical data independence
It refers to the ability to modify the schema at the physical level without affecting the schema followed at the logical
level. ie ,application programs remain the same even if the schema at the physical level get changed
2. Logical data independence
It refers to the ability to modify the schema at the logical level without affecting the schema followed at the view
level

Different users in database

Based on the mode of interaction with DBMS the users of a database are classified into four
 Database Administrator (DBA)
 Application Programmers
Sophisticated Users
Naive Users
1. Database Administrator (DBA)
A database administrator is a person who has central control over the database. He is responsible for the installation,
configuration, upgrading, administration, monitoring, maintenance, and security of databases in an organization.
Responsibilities of database administrator are - Design of conceptual and physical schema, Security and
authorization, Data availability and recovery from failure.
2. Application Programmer
Application programmers are computer professionals who interacts with the database through application programs
written in any languages such as C,C++,Java etc. They use DML to interact with the database.
3. Sophisticated Users
Sophisticated users interact with database through queries. They include engineers, scientists, analyst etc.
4. Native users
Native users are unsophisticated users .They interact with database by invoking previously written application
programs. They are not aware of details of DBMS. They deal only with the higher level of abstraction. Clerical
staffs in an organization are naive users.

Relational data model

A relational model represents database as a collection of relations (tables).Each relation has a unique name. A
relational model stores data in a tabular form. The main advantage of relational model is that it is simple than other
models. A relational database management system (RDBMS) is a database management system based on relational
model. A (RDBMS) is a program that lets you create, update and administer a relational database. Some of the
popular RDBMS are Oracle, MYSQL, DB2, Microsoft SQL Server, Informix, Ingress etc
Terminologies in RDBMS
1)Entity:-An entity is a real world object that is distinguishable from others such as student, teacher etc.
2)Relation:-A relation is a collection of data in the form of rows and column. Also called a table
3)Tuple:-The row in a table is called a tuple. It is also called a record.
4)Attribute:-A column in a table(Relation) is called an attribute.
5)Degree:-The number of columns (attributes) in a table is called degree.
6)Cardinality:-The number of rows (tuples) in a table is called cardinality.
7)Domain:-The set of possible values for a column(Attribute) is called domain.
8)Schema:-The overall design(Description) or structure of a database is called schema. In RDBMS schema of a
relation specifies its name, name and type of each column.
9) Instance:-The collection of data stored in the database at a particular moment is called instance. It is also called
snapshot or database state.

Keys
A key is an attribute or collection of attributes that uniquely identifies each record (Tuple) in a table. A key
consisting of one or more attributes is called a composite key (Compound Key).
1) Candidate Key:- A candidate key is a column or set of columns that uniquely identifies each record in the table.
Atable may contain more than one candidate key. For example Rollno and RollNo + batch can be considered as
candidate keys in the Student table.
2) Primary Key:-A primary key is a candidate key which is used to uniquely identify each row in a table. A table
can have only one primary key.
3) Alternate Key:- An alternate key is a candidate key that is not the primary key.
4) Foreign Key:- A foreign key is a field in one table that must match a primary key value in another table. It is used
to join two tables together. It is also called reference key.
Relational Algebra
A relational algebra is a collection of operations used to manipulate table content. Relational algebra consists of a set
of operations that take one or more relations as input and produce a new relation as output. These operations are
performed with the help of special language called query language associated with the relational model. The
fundamental operations in relational algebra are Select, Project, Union, Intersection, set difference Cartesian Product
etc. The select and project are unary operations as they operate on one relation, while other operations are binary
operations ( Cartesian Product, UNION, INTERSECTION and SET DIFFERENCE).
1. SELECT Operation
The SELECT operation selects rows from a table that satisfies a specific condition. It is denoted by the letter Sigma
( σ).The select operation gives horizontal subset of a relation. Its general form is
σ condition (relation)
It uses the comparison operators <, <=, > , >=, =,< >(not equal to) and logical operators V or, ^ and ,! Not to
construct conditions.

2. PROJECT Operation
The PROJECT operation selects attributes (Columns) from a table .It is denoted by the letter Pi( π).The PROJECT
operation gives vertical subset of a relation. Its general form is
π A1,A2,.....,An (relation) Here A1,A2,....,An are various attributes.
3. UNION Operation

The UNION operation returns a relation consisting of all tuples from both the relations. It is denoted by U .The
UNION operation takes place only if two relations are union-compatible. If two relations are union-compatible then
they have same number and types of attributes in order from left to right. Attributes name may be different.

4. INTERSECTION Operation
The INTERSECTION operation returns a relation containing the tuples appearing in both the relations. It is denoted
by R ∩S .This operation takes place only if two relations are union-compatible.

5. SET DIFFERENCE (-)operation

It returns a relation containing the tuples appearing in the first relation but not in the second relation It is denoted by
–(minus) .This operation takes place only if two relations are union-compatible and schema of result will be
identical to the schema of first relation.
[Link] Product Operation
The Cartesian product operation results all the possible combination of rows from both relations. It is a binary
operation and is denoted by X(cross).It is also called cross product. It is noted that the resultant Relation obtained as
part of Cartesian Product has Cardinality equals to the product of the cardinalities of the 2 relations and Degree
equals to the sum of Degrees of of the 2 Relations.