What is Database

The database is a collection of inter-related data which is used to retrieve, insert and delete
the data efficiently. It is also used to organize the data in the form of a table, schema, views,
and reports, etc.

For example: The college Database organizes the data about the admin, staff, students and
faculty etc.

Using the database, you can easily retrieve, insert, and delete the information.

Database Management System

o Database management system is software which is used to manage the database. For
example: MySQL, Oracle, etc are a very popular commercial database which is used
in different applications.
o DBMS provides an interface to perform various operations like database creation,
storing data in it, updating data, creating a table in the database and a lot more.
o It provides protection and security to the database. In the case of multiple users, it also
maintains data consistency.
o Database Applications:

• Banking: all transactions

• Airlines: reservations, schedules

• Universities: faculty, students, registration, admission, grades etc.

• Sales: customers, products, purchases

• Manufacturing: production, inventory, orders, supply chain

• Human resources: employee records, salaries, tax deductions

Why do we need database systems?

To solve the data management problem

To bridge the gap and the analogies in the personal information space

DBMS contains information about a particular enterprise.

DBMS allows users the following tasks:

 o Data Definition: It is used for creation, modification, and removal of definition that
defines the organization of data in the database.
o Data Updation: It is used for the insertion, modification, and deletion of the actual
data in the database.
o Data Retrieval: It is used to retrieve the data from the database which can be used by
applications for various purposes.
o User Administration: It is used for registering and monitoring users, maintain data
integrity, enforcing data security, dealing with concurrency control, monitoring
performance and recovering information corrupted by unexpected failure.

Characteristics of DBMS

o It uses a digital repository established on a server to store and manage the

information.
o It can provide a clear and logical view of the process that manipulates data.
o DBMS contains automatic backup and recovery procedures.
o It contains ACID properties which maintain data in a healthy state in case of failure.
o It can reduce the complex relationship between data.
o It is used to support manipulation and processing of data.
o It is used to provide security of data.
o It can view the database from different viewpoints according to the requirements of
the user.

Advantages of DBMS

o Controls database redundancy: It can control data redundancy because it stores all
the data in one single database file and that recorded data is placed in the database.
o Data sharing: In DBMS, the authorized users of an organization can share the data
among multiple users.
o Easily Maintenance: It can be easily maintainable due to the centralized nature of the
database system.
o Reduce time: It reduces development time and maintenance need.
o Backup: It provides backup and recovery subsystems which create automatic backup
of data from hardware and software failures and restores the data if required.
o multiple user interface: It provides different types of user interfaces like graphical
user interfaces, application program interfaces

Disadvantages of DBMS

o Cost of Hardware and Software: It requires a high speed of data processor and large
memory size to run DBMS software.
o Size: It occupies a large space of disks and large memory to run them efficiently.
o Complexity: Database system creates additional complexity and requirements.
o Higher impact of failure: Failure is highly impacted the database because in most of
the organization, all the data stored in a single database and if the database is damaged
due to electric failure or database corruption then the data may be lost

Drawbacks of using file systems to store data:

 Storage space
 500 GB of main memory to hold the data is probably not available, and
therefore needs storage device such as disk or tape and needs to bring relevant
parts into main memory for processing as needed.
 Even if the computer possess 500GB main memory, computer systems with
32-bit addressing, cannot refer directly to more than about 4 GB of data
 To answer to each question by the user about the data, specials programs
needs to be developed which could be complex because large volume needs
to be searched resulting in
 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. i.e., application specific
program has to be generated.

 Data isolation

multiple files and formats

 Integrity problems

 Integrity constraints (e.g. account balance > 0) become part of program code
 It’s Hard to add new constraints or change existing
 The problem to protect the data from inconsistent changes made by different
users accessing the data concurrently.
 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-
 The operating systems provide only password mechanism for security, but
which is not sufficient to enforce security policies in which different users
have permission to access subsets of data.
 Needs to ensure that the data is restored to a consistent state if the file
crashes while the changes are made. i.e.
 Atomicity of updates
Failures may leave the data 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

Database systems offer solutions to all the above problems. DBMS is software
designed to make these problems easier way, where the DBMSs features to
manage the data in a robust and efficient manner

DBMS vs. File System

There are following differences between DBMS and File system:

DESCRIBING AND STORING DATA IN A DBMS

Building blocks of a Database

The following three components form the building blocks of a database. They store
the data that we want to save in our database.
Columns. Columns are similar to fields, that is, individual items of data that we
wish to store. A Student' Roll Number, Name, Address etc. are all examples of
columns. They are also similar to the columns found in spreadsheets (the A, B, C etc.
along the top)
Rows. Rows are similar to records as they contain data of multiple columns (like the
1, 2, 3 etc. in a spreadsheet). A row can be made up of as many or as few columns as
you want. This makes reading data much more efficient - you fetch what you want.
Tables. A table is a logical group of columns. For example, you may have a table that
stores details of customers' names and addresses. Another table would be used to
store details of parts and yet another would be used .for supplier's names and
addresses.
It is the tables that make up the entire database and it is important that we do not
duplicate data at all.

o The DBMS design depends upon its architecture. The basic client/server architecture
is used to deal with a large number of PCs, web servers, database servers and other
components that are connected with networks.
o The client/server architecture consists of many PCs and a workstation which are
connected via the network.
o DBMS architecture depends upon how users are connected to the database to get their
request done.

#Difference between Schema and Instance in DBMS

1. Instances :
Instances are the collection of information stored at a particular moment. The instances can
be changed by certain operations as like addition, deletion of data. It may be noted that any
search query will not make any kind of changes in the instances.
Example –j
Let’s say a table teacher in our database whose name is School, suppose the table has 50
records so the instance of the database has 50 records for now and tomorrow we are going
to add another fifty records so tomorrow the instance have total 100 records. This is called
an instance.
2. Schema :
Schema is the overall description of the database. The basic structure of how the data will
be stored in the database is called schema.

Schema is of three types: Logical Schema, Physical Schema and view Schema.
1. Logical Schema – It describes the database designed at logical level.
2. Physical Schema – It describes the database designed at physical level.
3. View Schema – It defines the design of the database at the view level.

3 -Schema Architecture
The Three Levels Of Abstraction or logical DBMS architecture

o This framework is used to describe the structure of a specific database system.

o The three schema architecture is also used to separate the user applications and
physical database.
o The three schema architecture contains three-levels. It breaks the database down into
three different categories.
Mappings between levels and data independence

The 3 levels of abstraction in the database do not exist separately of each other. There must
be some correspondence, or mapping, among the levels. There are two types of mappings: the
internal / conceptual mapping and the conceptual / external mapping.

The internal / conceptual mapping lies among the internal and conceptual levels, and tells the
correspondence among the records and the data structures of the internal view and fields of
the conceptual view and the files. If the structure of the stored database is changed, then the
internal / conceptual mapping must also be changed accordingly so that the view from the
conceptual level remains similar. It is this mapping that gives physical data independence for
the database. For instance, we may modify the internal view of student relation by breaking
the student file into two files, one containing enrolment, address and name and other
containing enrolment, programme. Therefore, the mapping will make sure that the conceptual
view is restored as original. The storage decision is firstly taken for optimisation purposes.

The external/conceptual view lies between the external and conceptual levels, and tells the
correspondence between a particular external view and the conceptual view. Though these
two levels are same, some elements discover in a particular external view may be different
from the conceptual view. For example, various fields can be combined into a single (virtual)
field, which can also have dissimilar names from the original fields. If the structure of the
database at the conceptual level is changed, then the conceptual / external mapping must
change accordingly so that the view from the external level remains constant. It is this
mapping that gives logical data independence for the database. For instance, we may change
the student relation to have more fields at conceptual level, yet this will never change the two
user views at all.
STRUCTURE OF DBMS
Physical dbms architecture
Components of DBMS are broadly classified as follows :
1. Query Processor :
(a) DML Compiler
(b) Embedded DML pre-compiler
(c) DDL Interpreter
(d) Query Evaluation Engine
2. Storage Manager :
(a) Authorization and Integrity Manager
(b) Transaction Manager
(c) File Manager
(d) Buffer Manager
3. Data Structure :
(a) Data Files
(b) Data Dictionary
(c) Indices
(d) Statistical Data
1. Query Processor Components :
• DML Pre-compiler : It translates DML statements in a query language into low level instructions
that query evaluation engine understands. It also attempts to transform user's request into an
equivalent but more efficient form.
• Embedded DML Pre-compiler : It converts DML statements embedded in an application program
to normal procedure calls in the host language. The Pre-compiler must interact with the DML
compiler to generate the appropriate code.
• DDL Interpreter : It interprets the DDL statements and records them in a set of tables containing
meta data or data dictionary.
• Query Evaluation Engine : It executes low-level instructions generated by the DML compiler.
2. Storage Manager Components :
They provide the interface between the low-level data stored in the database and application programs
and queries submitted to the system.
• Authorization and Integrity Manager : It tests for the satisfaction of integrity constraints checks
the authority of users to access data.
• Transaction Manager : It ensures that the database remains in a consistent state despite the system
failures and that concurrent transaction execution proceeds without conflicting.
• File Manager : It manages the allocation of space on disk storage and the data structures used to
represent information stored on disk.
• Buffer Manager : It is responsible for fetching data from disk storage into main memory and
deciding what data to cache in memory.

3. Data Structures :
Following data structures are required as a part of the physical system implementation.
• Data Files : It stores the database.
• Data Dictionary : It stores meta data (data about data) about the structure of the database.
• Indices : Provide fast access to data items that hold particular values.
• Statistical Data : It stores statistical information about the data in the database. This information is
used by query processor to select efficient ways to execute query.
Data Models

Data Model is the modeling of the data description, data semantics, and consistency
constraints of the data. It provides the conceptual tools for describing the design of a database
at each level of data abstraction. Therefore, there are following four data models used for
understanding the structure of the database:
1) Relational Data Model: This type of model designs the data in the form of rows and
columns within a table. Thus, a relational model uses tables for representing data and in-
between relationships. Tables are also called relations. This model was initially described by
Edgar F. Codd, in 1969. The relational data model is the widely used model which is
primarily used by commercial data processing applications.

2) Entity-Relationship Data Model: An ER model is the logical representation of data as

objects and relationships among them. These objects are known as entities, and relationship is
an association among these entities. This model was designed by Peter Chen and published in
1976 papers. It was widely used in database designing. A set of attributes describe the
entities. For example, student_name, student_id describes the 'student' entity. A set of the
same type of entities is known as an 'Entity set', and the set of the same type of relationships
is known as 'relationship set'.

3) Object-based Data Model: An extension of the ER model with notions of functions,

encapsulation, and object identity, as well. This model supports a rich type system that
includes structured and collection types. Thus, in 1980s, various database systems following
the object-oriented approach were developed. Here, the objects are nothing but the data
carrying its properties.

4) Semistructured Data Model: This type of data model is different from the other three
data models (explained above). The semistructured data model allows the data specifications
at places where the individual data items of the same type may have different attributes sets.
The Extensible Markup Language, also known as XML, is widely used for representing the
semistructured data. Although XML was initially designed for including the markup
information to the text document, it gains importance because of its application in the
exchange of data.
Introduction of ER Model

o ER model stands for an Entity-Relationship model. It is a high-level data model. This

model is used to define the data elements and relationship for a specified system.
o It develops a conceptual design for the database. It also develops a very simple and
easy to design view of data.
o In ER modeling, the database structure is portrayed as a diagram called an entity-
relationship diagram.

For example, Suppose we design a school database. In this database, the student will be an
entity with attributes like address, name, id, age, etc. The address can be another entity with
attributes like city, street name, pin code, etc and there will be a relationship between them.

Component of ER Diagram
1. Entity:
An entity may be any object, class, person or place. In the ER diagram, an entity can be
represented as rectangles.
Consider an organization as an example- manager, product, employee, department etc.
can be taken as an entity.

a. Weak Entity

An entity that depends on another entity called a weak entity. The weak entity doesn't
contain any key attribute of its own. The weak entity is represented by a double
rectangle.
2. Attribute
The attribute is used to describe the property of an entity. Eclipse is used to represent
an attribute.

For example, id, age, contact number, name, etc. can be attributes of a student.

a. Key Attribute

The key attribute is used to represent the main characteristics of an entity. It represents
a primary key. The key attribute is represented by an ellipse with the text underlined.

b. Composite Attribute
An attribute that composed of many other attributes is known as a composite attribute.
The composite attribute is represented by an ellipse, and those ellipses are connected
with an ellipse.

c. Multivalued Attribute

An attribute can have more than one value. These attributes are known as a multivalued
attribute. The double oval is used to represent multivalued attribute.

For example, a student can have more than one phone number.

d. Derived Attribute

An attribute that can be derived from other attribute is known as a derived attribute. It
can be represented by a dashed ellipse.

For example, A person's age changes over time and can be derived from another
attribute like Date of birth.
The complete entity type Student with its attributes can be represented as:

3. Relationship
A relationship is used to describe the relation between entities. Diamond or rhombus is
used to represent the relationship.

Types of relationship are as follows:

a. One-to-One Relationship
When only one instance of an entity is associated with the relationship, then it is known
as one to one relationship.
For example, A female can marry to one male, and a male can marry to one female.

b. One-to-many relationship
When only one instance of the entity on the left, and more than one instance of an entity
on the right associates with the relationship then this is known as a one-to-many
relationship.
For example, Scientist can invent many inventions, but the invention is done by the
only specific scientist.

c. Many-to-one relationship
When more than one instance of the entity on the left, and only one instance of an entity
on the right associates with the relationship then it is known as a many-to-one
relationship.
For example, Student enrolls for only one course, but a course can have many
students.

d. Many-to-many relationship

When more than one instance of the entity on the left, and more than one instance of an
entity on the right associates with the relationship then it is known as a many-to-many
relationship.
For example, Employee can assign by many projects and project can have many
employees.
Notation of ER diagram
Database can be represented using the notations. In ER diagram, many notations are
used to express the cardinality. These notations are as follows: