Management Information System

Management Information System with the help of technologies produces quality

information for the managers by which they take the quality decisions for the
organization.

MIS can be classified as performing three functions:

(1) To generate reports-for example, financial statements, inventory status reports,

or performance reports needed for routine or non-routine purposes.

(2) To answer what-if questions asked by management. For example, questions such
as "What would happen to cash flow if the company changes its credit term for its
customers?" can be answered by MIS. This type of MIS can be called Simulation.

(3) To support decision making. This type of MIS is appropriately called Decision
Support System (DSS). DSS attempts to integrate the decision maker, the data base,
and the quantitative models being used.
• Data are facts and figures that are not currently being used in a
decision process but may take form of historical record that are
filled to refer in future for decision making.

• Information is a processed form of data that is meaningful

and useful to human being. Information is knowledge that one
derives from facts for effective functioning of systems placed
in the right context with the purpose of reducing uncertainty
regarding the alternative courses of action as they are based on
description and measurement of attributes of various entities
associated with the enterprise.

• Data------Processing---------Information
Characteristics of Quality Information
• Accurate
• Complete
• Economical
• Flexible
• Reliable
• Relevant
• Simple
• Timely
• Verifiable
• Accessible
• Secure
Important roles of the MIS
• The MIS satisfies the diverse needs through
variety of systems such as query system, analysis
system, modeling system and decision support
system.
• The MIS helps the top level management in goal
setting, strategic planning and evolving the
business plans and their implementation
• The MIS helps in strategic planning, management
control, operational control and transaction
processing. The MIS helps in the clerical personal
in the transaction processing and answers the
queries on the data pertaining to the transaction,
the status of a particular record and reference on a
variety of documents.
• The MIS helps the junior management personnel
by providing the operational data for planning,
scheduling and control , and helps them further in
decision-making at the operation level to correct
an out of control situation.
• The MIS helps the middle management in short
term planning, target setting and controlling the
business functions. It is supported by the use of
the management tools of planning and control.
• The MIS plays the role of information generation,
communication, problem identification and helps
in the process of decision-making. The MIS,
therefore, plays a vital role in the management,
administration and operation of an organization.
Advantages
• Companies are able to highlight their strengths and
weaknesses due to the presence of revenue reports,
employees' performance record etc. The identification of
these aspects can help the company improve their business
processes and operations.
• Giving an overall picture of the company and acting as a
communication and planning tool.
• The availability of the customer data and feedback can help
the company to align their business processes according to
the needs of the customers. The effective management of
customer data can help the company to perform direct
marketing and promotion activities.
• Management Information Systems can help a company gain
a competitive advantage. Competitive advantage is a firm’s
ability to do something better, faster, cheaper, or uniquely,
when compared with rival firms in the market.
IMPORTANCE OF MIS
There are so many factors responsibility for enhancing the importance of
Management Information System such as:
1.Dynamic and competitive nature of market place.
2.Globalization of resources/economy.
3.Increasing complexities of business organization (i.e. sole organization to large
corporate system approach).
4.The information revolution (Credit card, overnight package delivery).

Traditionally, the Management Information System operated manually in

which human being were major source for providing information and all the
working was carried out through pen and paper. This approach posed certain
drawbacks:

1.It did not provide timely and accurate information.

2.It could not store volume of information for future use.
3.Paper work involved was vast.
4.Costly in terms of cost as well as time consuming.
5.Sometimes secrecy was not compromised.
Types of Information

Source of Information Type of decision

Strategic
External Internal Operation

Tactical
Competitive
Environment
Types of Information

A manager has to perform various activities in a business organization. So, he

needs to perform these activities effectively. This information is differentiated
mainly on behalf of their source of generating information and types of decision
taken by manager at different level. This is given below:

Source of Information
On the basis of source of generating information, we can divide information in
two broad types:

External Information: The information generated from outside business

organization is known as external information. For e.g. the data regarding
industry sales and trends may be collected through chamber of commerce. It is
further divided into two parts. One is information regarding environment and
other is of competitors.

Competitive Information
This category includes information regarding the competitor’s product and their
demand, their strengths and weakness, different policies regarding pricing
promotion etc.
Environmental Information

Environmental Information includes the following:

Government Policies: Government Policies with regard to tax benefits,
concessions quota’s etc.
Factor Production: information regarding labors, capital, and material, about their
source, cost and availability.
Economic Trends: Information regarding consumer income, capital investment,
monetary policy etc.
Sociological Information: Information regarding preference and consumer
moment, customer value etc.
Political Information: Information regarding political scene of the country, what is
the political policy of the ruling party etc.

Internal Information
The internal information is generated with in company like that company pricing
policies, promotion policy, financial, and production information, strength and
weakness of company.
Types of Decision
We can divide information (types of decision) in three types.

Operational Information:
Low level management is used operational information. So their information need is
known as operational information which relates to the daily operations like daily or
weekly sales statement, daily cash flow and daily general expenses etc.

Tactical Information:
Middle level management is used tactical information. So they require information
for allocating resource and establishing control to implement the top level strategy.

Strategic Information:
Top level management is used strategic information. Strategic information related is
analytical in nature and have long term view. It is retrieved not only from external
source but to some extent internal resource and also explored. Strategic information
helps evaluating these options so that manager can find alternative strength than his
competitors.
Types of Decisions
Operational Tactical Strategic

Un-structured Cash Re-engineering a New e-business

Management process, formulation initiatives, Company re-
of budget, long term organization
forecasting
Semi- Production Employee Mergers, introduction of
structured Scheduling, Performance new products
inventory Evaluation, Capital
management Budgeting Site Location

Structured Payroll, Budget Analysis Warehouse Location

order
processing,
Accounts
Receivable
Level of Management and their information requirement
To run a business organization successfully, a manager has to work different
functional areas of planning, organizing, staffing, directing, and controlling. To
perform these activities effectively and efficiently he needs information.
There are three mainly level of Management in every business organization which
are top level, middle level and lower level management.

TOP

MIDDLE

LOWER

Top level Management: Top Level Management is the highest level of

management and they are the policy maker of any business organization.
Middle level Management: Middle level Management is the next level of
management which is responsible for implementation these plans and getting
work done by the subordinates.
Lower level Management: Lower level Management relates to the work force of
any business organization these are the people responsible to convert the plan
and policies into actual product and services.
External

TOP

MIDDLE

LOWER Internal

Level of Management Source of Information

The main job profile for any level of manager is to make the decisions.
Information is generated from both sources i.e. internal and external. The work
of Top manager is related to making of plans for which he needs detailed
information relating to external environment. So its trust is on external source.
The information needs to top management from external environment is more
and this external environmental information need is increasingly summarized at
it moves to the middle and lower level. At the same time, the information need
of lower level is satisfied from information and it is generated within the
organization.
Classification of Information System
We have categorized information systems on the basis of their roles in the
operations and management of a business. Accordingly information systems have
been primarily categorized as under:
Operation Support System
Management Support System

Information System

Operating Support System Management Support System

Transaction Process Control Enterprises Management Decision Executive

Processing System Collaboration Information Support System Information
System (PCS) System System (DSS) System
(TPS) (ECS) (MIS) (EIS)
Operation Support System
Every business organization requires information system to process data
generated by business organization and used in business operations. These
systems produce various types of information product for internal and external
use. The Operation Support Systems are further divided into three basic
categories.

Transaction Processing System

Transaction Processing System is responsible for recording and processing of
data resulting from business transaction. For example Information System that
process sale, purchases, and inventory are changes which help in updating the
customer, inventory and other organizational database.
The product of transaction processing system provides a base or input fro MIS,
DSS and Executive Information System.
Process Control Systems:
Process Control Systems means use of computer to control on current
physical process. Process Control Systems also make routine decisions that
control operational process. Examples are automatic inventory recorder
decisions and production control decisions.
The petroleum refinery uses electronic sensor linked to computer to
continually monitor chemical processes. The computer monitors a chemical
process, capture and processes data detected by sensor and make instant
adjustments to appropriate refinery process.
Process Control Systems are OSS that monitors and controls industrial
processes eg. Petroleum Refining, power generation and steel production
system
Enterprises Collaboration System:
Enterprises Collaboration Systems is an information system which uses
information technology to help people help together. This system helps us to
communicate, ideas, share, recourses coordinate our co-operative work
efforts.
Office Automation System eg. E-mail, chat, videoconferencing and
groupware systems.
Characteristics of Management Information System

• Management Information System supports structured and semi-structured

decisions at the middle level of management.

• Management Information System is useful for planning purpose for senior

managers.

• Management Information System is generally reporting and control oriented.

• Management Information System helps in decision making using past and

present data.

• Information requirements are known and only then are stable.

• Management Information System has an internal orientation.

Management
Transaction Processing
Points of Comparison Information
System
System
Output from TPS, High
Input Transaction, Events
Volume data
Data entry, listing, Routing reports, simple
Processing sorting, merging and models, Low Level
updating analysis
Detailed reports, lists, Summary and exception
Output
summary of transactions reports.
Operations personnel,
Users Low Level Managers, Middle-Level-Managers
Supervisor
Record and process Production of summary
Goal
transactions and exception reports
Provide decision support
Provide decision support
Decision and Support to Lower Level
to tactical level manager.
Managers
Decision Support System:
They comprise of major category of management support systems. They are
computer based information system that provide interactive information support
during the decision making process.

Characteristics of Management

•DSS offers users flexibility, adaptability and quick response.

•DSS are intelligent support systems designed to provide information to top and
middle level managers to make decisions that require judgment and intuition.

•DSS allow users to generate and control the inputs and outputs.

•DSS uses sophisticated analysis and modeling tools.

•DSS operates with little or no assistance from professionals such as

programmers etc.

•DSS provides support for decisions and problems whose solutions can not be
pre-specified.
Management
Decision Support
Points of Comparison Information
System
System
Output of TPS, high- Output from TPS and MIS
Input
volume data low-volume data
Extraction and
Analytical modeling of
Processing manipulation of business
business data
data
Periodic, Exceptions,
Interactive queries and
Output demand and reports and
responses
responses.
Top-level managers,
Users Middle-level managers professionals, Information
workers.
Provide information about Provide decision support
Goal the performance of the techniques to analyze
organization specific problems.
Production of summary
Decision and Support Ad-hoc query handling
and exception reports.
Executive Information System: This “Information System” product serves the
strategic information needs of top management and it is basically a “Hybrid Information
System” of both Management Information System and DSS. The basic purpose of EIS is
to provide top management with immediate and easy access to information about firm’s
critical success factors.
Type of Input Process Output Users Examples
System
Transaction Transaction, Data Entry, Detailed lower level Sales
Processing Events Listing, reports, managers, Transaction,
System Sorting, lists, supervisors Purchases
(TPS) Merging, summary of transaction,
Updating transactions Claims etc.
Management Output from Routine Summary Middle Level Monthly
Information TPS, high Reports, and Managers Sales
System volume of Simple Exception Reports
(MIS) data model, low reports
level analysis
Decision Output from Analytical Interactive Top-level Investment
Support TPS, and Modeling of queries and managers, Portfolio,
System MIS, Low business responses Professionals Plant
(DSS) level data data Expansion

Executive Aggregate Graphics, Projections, Executive Enterprises-

Information data Interactive responses Directors Wide
Systems (External & to queries performances
(EIS) Internal)
Information Technology emerging
trends
• Cloud Computing
Cloud computing is a technology that allows users to access
computing resources over the internet, rather than through
local servers or personal devices.

• Artificial Intelligence (AI) and Machine Learning

(ML)-
AI and ML are technologies that enable machines to learn
from data and make decisions based on that learning

• Internet of Things (IoT)

Network of connected devices and sensors that collect and
transmit data over the internet. E.g. smart homes, wearables,
and industrial automation
• Blockchain
Blockchain is a distributed ledger technology
that enables secure and transparent transactions
between parties. E.g. used in industries,
including finance, healthcare, and supply chain
management.
IT Capabilities and their
organizational impact
• Enterprise Resource Planning (ERP)
ERP systems integrate all of an organization's business functions into a single
system

• Customer Relationship Management (CRM)

Can use it to track customer interactions, provide personalized service, and
identify opportunities for cross-selling or up-selling.

• Business Intelligence (BI):

BI systems enable organizations to analyze and interpret their data to make
better-informed decisions.

• Collaboration Tools
A company that implements collaboration tools such as video conferencing,
instant messaging, and document sharing can improve communication and
collaboration between teams, reduce travel costs, and enable remote work.

• Data Analytics
IT enabled services
• IT-enabled services (ITES) refer to the use of information technology (IT) to deliver
services to customers.

1. Business Process Outsourcing (BPO)

Involves outsourcing business processes to third-party providers. These providers use
IT systems to perform tasks such as data entry, customer service, and accounting.

2. Knowledge Process Outsourcing (KPO)

These providers use IT systems to perform tasks such as research, analysis, and
consulting.

3. Customer Service
This can include services such as live chat, email support, and social media
management.

4. E-commerce
Include online marketplaces, online shopping carts, and payment gateways.

5. Telemedicine
To provide medical services remotely, such as consultations, diagnosis, and treatment.
• Information is a processed form of data that is
meaningful and useful to human being. Information is
knowledge that one derives from facts for effective
functioning of systems placed in the right context with the
purpose of reducing uncertainty regarding the alternative
courses of action as they are based on description and
measurement of attributes of various entities associated
with the enterprise.

Data------Processing---------Information
Types of Information

A manager has to perform various activities in a business organization. So, he

needs to perform these activities effectively. This information is differentiated
mainly on behalf of their source of generating information and types of decision
taken by manager at different level. This is given below:

Internal Information
The internal information is generated with in company like that company pricing
policies, promotion policy, financial, and production information, strength and
weakness of company.

External Information: The information generated from outside business

organization is known as external information. For e.g. the data regarding
industry sales and trends may be collected through chamber of commerce. It is
further divided into two parts. One is information regarding environment and
other is of competitors.

Competitive Information
This category includes information regarding the competitor’s product and their
demand, their strengths and weakness, different policies regarding pricing
promotion etc.
Environmental Information

Environmental Information includes the following:

Government Policies: Government Policies with regard to tax benefits,
concessions quota’s etc.
Factor Production: information regarding labors, capital, and material, about their
source, cost and availability.
Economic Trends: Information regarding consumer income, capital investment,
monetary policy etc.
Sociological Information: Information regarding preference and consumer
moment, customer value etc.
Political Information: Information regarding political scene of the country, what is
the political policy of the ruling party etc.
We can divide information (types of decision) in three types.

Operational Information:
Low level management is used operational information. So their information need is
known as operational information which relates to the daily operations like daily or
weekly sales statement, daily cash flow and daily general expenses etc.

Tactical Information:
Middle level management is used tactical information. So they require information
for allocating resource and establishing control to implement the top level strategy.

Strategic Information:
Top level management is used strategic information. Strategic information related is
analytical in nature and have long term view. It is retrieved not only from external
source but to some extent internal resource and also explored. Strategic information
helps evaluating these options so that manager can find alternative strength than his
competitors.
Decision Making Process: Steps
Decision Making- Rational Model/
Classical Model:
• Rational Model/Classical Model: This model is
first attempt to know decision making.
• This classical model gave various steps that have
to be discuss.
1. Problems are clear
2. Objectives are clear
3. People agrees on criteria and weights
4. All alternatives are known
5. All consequences are anticipated
6. Decision makes are rational
Characteristics of Rational Model
• They are not biased in recognizing problems.
• They are capable of processing all relevant
information.
• They anticipate present and future
consequence of decisions.
• They search for all alternatives that maximizes
the desired results.
Classical Approach/ Rational Model
Bounded Rationality / Administrative
Man Model
• Decision making involves achievement of a goal
• Rationality demands that the decision maker
should properly understand the alternative
course of action for reaching a goal.
• He should also have full information and ability to
analyze properly various alternative course of
action in the light of goal sought.
• They should also have a desire to select the best
solution by selecting the alternative which satisfy
the goal achievement.
Characteristics of Bounded
Rationality Model
• Focus on sequential attention on developing
alternative solution.
• Heuristic Approach: focus on guide the search for
alternatives into areas that have a high
probability for yielding success.
• Satisfying: the picking a course of action that is
satisfactory or good enough under the
circumstances when time is constraint and other
alternative requires more time with similar
results.
Problem Solving Model Herbert
Simon’s Model
Characteristics of Herbert Simon’s
Model
Retrospective Decision Model/
Implicit Favorite Model
Decision Making: Biases
Management Support Systems (MSS) are
those information systems that focus on
providing information and support in
efficient and effective decision making by
all types of business managers.
• Management Information Systems are
such MSS that provides information in
report format and displays to support
business decision making eg. Sales analysis
report, production performance and cost
trend reporting system.
Management Information Systems
Some examples of MIS
• Sales management systems
• Inventory control systems
• Budgeting systems
• Management Reporting Systems (MRS)
• Personnel (HRM) systems
The role of MIS
• Based on internal information flows
• Support relatively structured decisions
• Inflexible and have little analytical capacity
• Used by lower and middle managerial levels
• Deals with the past and present rather than the future
• Efficiency oriented?
MIS
Management Information System

• Data + Organization
• Set of formal business systems designed to
provide information for an organization
• Computers are typical components
Management Information System
• A management information system (MIS) provides information that
organizations require to manage themselves efficient.
• Management information systems are typically computer systems used for
managing and effectively.
• Management information systems are distinct from other information
systems because they are used to analyze and facilitate strategic and
operational activities.
• Academically, the term is commonly used to refer to the study of how
individuals, groups, and organizations evaluate, design, implement, manage,
and utilize systems to generate information to improve efficiency and
effectiveness of decision making,
• The five primary components:
1.)Hardware,
2.) Software,
3.) Data (information for decision making),
4.) Procedures (design, development and documentation), and
5.) People (individuals, groups, or organizations).
Management information systems (MIS), produce fixed, regularly scheduled
reports based on data extracted and summarized from the firm’s
underlying transaction processing systems to middle and operational level
managers to identify and inform structured and semi-structured decision
problems.

Characteristics of Management Information System

• Management Information System supports structured and semi-structured
decisions at the middle level of management.
• Management Information System is useful for planning purpose for senior
managers.
• Management Information System is generally reporting and control oriented.
• Management Information System helps in decision making using past and
present data.
• Information requirements are known and only then are stable.
• Management Information System has an internal orientation.
Working of MIS
Transaction Processing Management Information
Points of Comparison
System System

Output from TPS, High

Input Transaction, Events
Volume data

Data entry, listing, sorting, Routing reports, simple

Processing
merging and updating models, Low Level analysis

Detailed reports, lists, Summary and exception

Output
summary of transactions reports.
Operations personnel, Low
Users Level Managers, Middle-Level-Managers
Supervisor
Record and process Production of summary and
Goal
transactions exception reports

Provide decision support to Provide decision support to

Decision and Support
Lower Level Managers tactical level manager.
Decision Support System:
They comprise of major category of management support systems. They are
computer based information system that provide interactive information
support during the decision making process.
Decision Support Systems (DSS) are computer program applications used by
middle and higher management to compile information from a wide range of
sources to support problem solving and decision making.DSS is majorly used for
semi-structured and unstructured decision problems.
Characteristics of Management

•DSS offers users flexibility, adaptability and quick response.

•DSS are intelligent support systems designed to provide information to top and
middle level managers to make decisions that require judgment and intuition.

•DSS allow users to generate and control the inputs and outputs.

•DSS uses sophisticated analysis and modeling tools.

•DSS operates with little or no assistance from professionals such as

programmers etc.
Decision Support Systems

• Supplements an MIS
• Pulls information from variety of databases
• Interactive
• Non-routine decision-making
• Model – mathematical representation of real-life
system
• Simulation – using a computer model to reach a
decision about a real-life situation
Decision Support System
• A Decision Support System (DSS) is a computer-based
information system that supports business or
organizational decision-making activities.
• DSSs serve the management, operations, and planning
levels of an organization (usually mid and higher
management) and help to make decisions, which may be
rapidly changing and not easily specified in advance
(Unstructured and Semi-Structured decision problems).
• Decision support systems can be either fully computerized,
human or a combination of both.
Characteristics
• DSS tends to be aimed at the less well structured,
underspecified problem that upper
level managers typically face;
• DSS attempts to combine the use of models or
analytic techniques with traditional data
access and retrieval functions;
• DSS specifically focuses on features which make
them easy to use by non computer people in an
interactive mode; and
• DSS emphasizes flexibility and adaptability to
accommodate changes in the environment and
the decision making approach of the user.
• Adaptable and flexible
• Interactive and ease of use and efficiency
• Human control of the process
• Ease of development by end user
• Modeling and analysis
• Data access
• Support varieties of decision processes
• Support varieties of decision trees
• Quick response
Management
Decision Support
Points of Comparison Information
System
System
Output of TPS, high- Output from TPS and MIS
Input
volume data low-volume data
Extraction and
Analytical modeling of
Processing manipulation of business
business data
data
Periodic, Exceptions,
Interactive queries and
Output demand and reports and
responses
responses.
Top-level managers,
Users Middle-level managers professionals, Information
workers.
Provide information about Provide decision support
Goal the performance of the techniques to analyze
organization specific problems.
Production of summary
Decision and Support Ad-hoc query handling
and exception reports.
Executive Information System: This “Information System”
product serves the strategic information needs of top
management and it is basically a “Hybrid Information System” of
both Management Information System and DSS.

The basic purpose of EIS is to provide top management with

immediate and easy access to information about firm’s critical
success factors.

Executive Information Systems (EIS) is a reporting tool that

provides quick access to summarized reports coming from all
company levels and departments such as accounting, human
resources and operations.
• Executive Information Systems are strategic-level
information systems that are found at the top of
the Pyramid.
• They help executives and senior managers
analyze the environment in which the
organization operates, to identify long-term
trends, and to plan appropriate courses of action.
• The information in such systems is often weakly
structured and comes from both internal and
external sources. Executive Information System
are designed to be operated directly by
executives without the need for intermediaries
and easily tailored to the preferences of the
individual using them.
Some examples of EIS
• Executive Information Systems tend to be highly
individualized and are often custom made for a
particular client group; however, a number of off-the-
shelf EIS packages do exist and many enterprise level
systems offer a customizable EIS module.
The role of EIS
• Are concerned with ease of use
• Are concerned with predicting the future
• Are effectiveness oriented
• Are highly flexible
• Support unstructured decisions
• Use internal and external data sources
• Used only at the most senior management levels
Type of Input Process Output Users Examples
System
Transaction Transaction, Data Entry, Detailed lower level Sales
Processing Events Listing, reports, managers, Transaction,
System Sorting, lists, supervisors Purchases
(TPS) Merging, summary of transaction,
Updating transactions Claims etc.
Management Output from Routine Summary Middle Level Monthly
Information TPS, high Reports, and Managers Sales
System volume of Simple Exception Reports
(MIS) data model, low reports
level analysis
Decision Output from Analytical Interactive Top-level Investment
Support TPS, and Modeling of queries and managers, Portfolio,
System MIS, Low business responses Professionals Plant
(DSS) level data data Expansion

Executive Aggregate Graphics, Projections, Executive Enterprises-

Information data Interactive responses Directors Wide
Systems (External & to queries performances
(EIS) Internal)
Database Management System

Database Management System or DBMS in short refers to the

technology of storing and retrieving users’ data with utmost
efficiency along with appropriate security measures.

A database management system stores data in such a way that it

becomes easier to retrieve, manipulate, and produce information.

Database is a collection of related data and data is a collection of

facts and figures that can be processed to produce information.
Mostly data represents recordable facts. Data aids in producing
information, which is based on facts. For example, if we have data
about marks obtained by all students, we can then conclude about
toppers and average marks.
Characteristics
Traditionally, data was organized in file formats. DBMS was a new
concept then, and all the research was done to make it overcome the
deficiencies in traditional style of data management. A modern
DBMS has the following characteristics −
Real-world entity − A modern DBMS is more realistic and uses
real-world entities to design its architecture. It uses the behavior and
attributes too. For example, a school database may use students as an
entity and their age as an attribute.
Relation-based tables − DBMS allows entities and relations among
them to form tables. A user can understand the architecture of a
database just by looking at the table names.
Isolation of data and application − A database system is entirely
different than its data. A database is an active entity, whereas data is
said to be passive, on which the database works and organizes.
DBMS also stores metadata, which is data about data, to ease its own
process.
Less redundancy − DBMS follows the rules of normalization, which
splits a relation when any of its attributes is having redundancy in values.
Normalization is a mathematically rich and scientific process that reduces
data redundancy.

Consistency − Consistency is a state where every relation in a database

remains consistent. There exist methods and techniques, which can detect
attempt of leaving database in inconsistent state. A DBMS can provide
greater consistency as compared to earlier forms of data storing
applications like file-processing systems.

Query Language − DBMS is equipped with query language, which

makes it more efficient to retrieve and manipulate data. A user can apply
as many and as different filtering options as required to retrieve a set of
data. Traditionally it was not possible where file-processing system was
used.
Multiuser and Concurrent Access − DBMS supports multi-user environment
and allows them to access and manipulate data in parallel. Though there are
restrictions on transactions when users attempt to handle the same data item,
but users are always unaware of them.

Multiple views − DBMS offers multiple views for different users. A user who
is in the Sales department will have a different view of database than a person
working in the Production department. This feature enables the users to have a
concentrate view of the database according to their requirements.

Security − Features like multiple views offer security to some extent where
users are unable to access data of other users and departments. DBMS offers
methods to impose constraints while entering data into the database and
retrieving the same at a later stage. DBMS offers many different levels of
security features, which enables multiple users to have different views with
different features. For example, a user in the Sales department cannot see the
data that belongs to the Purchase department. Additionally, it can also be
managed how much data of the Sales department should be displayed to the
user. Since a DBMS is not saved on the disk as traditional file systems, it is
very hard for miscreants to break the code.
Database Languages
1. Data Definition Language (DDL) commands - that define a
database, including creating, altering, and dropping tables and
establishing constraints

2. Data Manipulation Language (DML) commands - that

maintain and query a database

3. Data Control Language (DCL) commands - that control a

database, including administering privileges and committing data
DDL, DML, DCL, and the database development process
Files….Records…Fields?
Files

A file is a collection of records.

Data in a database is saved on the computer as a data file and contains data on a
particular topic
e.g. a Students data file.
Records

A record is a collection of fields.

A record is a collection of organised data on a particular person or item.

Fields

A field is a single item of information.

A field is part of a record that holds a particular piece of data e.g. Surname.
Sample Fields
•Relations (tables)
• Attributes (columns)
• Tuples (rows)
• Example query: Salesperson='Mary' AND Price>100.

Here is a sample record from a telephone directory data file made up of different fields:
Field

Title Forename Surname Address Town Postcode Tel. No.

Mr. John Smith 101 Ayr KA7 3KL 01292

High Street 262626

Record
File Management System DBMS

1. Small System Large System

2. The problem of data isolation is found The problem of data isolation is not found

3. Simple Structure Complex Structure

4. Single User Multiple User

5. Data redundancy problem is found Data redundancy problem is not found

6. Few File Many Users

7. Often PC based Mini-Mainframe based

8. Security of data is not good Security of data is good

9. Accessing database is comparatively Access database is easier

difficult
The Importance of Data Models
• Data models

➢Relatively simple representations, usually

graphical, of complex real-world data structures

➢Facilitate interaction among the designer, the

applications programmer, and the end user

➢ End-users have different views and needs for data

➢ Data model organizes data for various users

70
Schema and Instances
The collection of information stored in the database at a particular moment is called an
INSTANCE of the database .

The overall design of the database is called the database SCHEMA.

There are three types of schema.

1. Physical Schema
2. Conceptual Schema
3. External Schema
1. Physical Schema: It specifies additional storage details. It summarizes how
the relations described in the conceptual schema are actually stored on secondary
devices such as disk and tapes.

2. Conceptual Schema: The conceptual schema describes the stored data in

terms of the data model of the DBMS. In a relational DBMS, the con griceptual
schema describes all relations that are stored in the database.

3. External Schema: External schema, which usually are also I terms of the
data model of the DBMS allow data access to be customized at the level of
individual users or groups of users.
Data Model Basic Building Blocks
• Entity - anything about which data are to be collected
and stored
• Attribute - a characteristic of an entity
• Relationship - describes an association among entities
– One-to-many (1:M) relationship
– Many-to-many (M:N or M:M) relationship
– One-to-one (1:1) relationship
• Constraint - a restriction placed on the data

73
Business Rules
• Brief, precise, and clear descriptions of a policies,
procedures, or principles within a specific organization
• Apply to any organization that stores and uses data to
generate information
• Description of operations that help to create and
enforce actions within that organization’s environment

74
Business Rules (continued)
• Must be rendered in writing
• Must be kept up to date
• Sometimes are external to the organization
• Must be easy to understand and widely
disseminated
• Describe characteristics of the data as viewed
by the company

75
Discovering Business Rules
Sources of Business Rules:
• Company managers
• Policy makers
• Department managers
• Written documentation
– Procedures
– Standards
– Operations manuals
• Direct interviews with end users

76
Translating Business Rules into Data
Model Components
• Standardize company’s view of data
• Constitute a communications tool between users and
designers
• Allow designer to understand the nature, role, and
scope of data
• Allow designer to understand business processes
• Allow designer to develop appropriate relationship
participation rules and constraints
• Promote creation of an accurate data model

77
Discovering Business Rules (continued)
• Generally, nouns translate into entities
• Verbs translate into relationships among
entities
• Relationships are bi-directional

78
The Evolution of Data Models
(continued)
• Hierarchical
• Network
• Relational
• Entity relationship

79
The Hierarchical Model

• Developedin the 1960s to manage large amounts

of data for complex manufacturing projects
• Basic logical structure is represented by an
upside-down “tree”
• This model organizes data elements as tabular
rows, one for each instance of an entity.
Consider a company’s organizational structure.

80
The Hierarchical Model (continued)

81
The Hierarchical Model (continued)
• The hierarchical structure contains levels, or
segments
• Depicts a set of one-to-many (1:M)
relationships between a parent and its
children segments
– Each parent can have many children
– each child has only one parent

82
The Hierarchical Model
• Advantages
- Simplicity
- Data Security
- Data Integrity (link between parent segment and child segment)
- Efficiency (to manage easily one to many relationship)
• Disadvantages
– Implementation Complexity
– Database Management Problem
– Lack of Structural Independence
– Implementation Limitation (Many to Many)
– Programming complexity

83
The Network Model
• The Network Model replaces the hierarchical tree
with a graph.

• Difference to handle the many to many

relationship.

• Each entity has a set of relationship.

• Each entity works as a parents and as a child.

84
The Network Model

• Resembles hierarchical model

• Collection of records in 1:M relationships
• Set
– Relationship
– Composed of at least two record types
• Owner
– Equivalent to the hierarchical model’s parent
• Member
– Equivalent to the hierarchical model’s child

85
The Network Model (continued)

86
The Network Model (continued)
• Disadvantages
❑Too bulky
❑The lack of ad hoc query capability put heavy
pressure on programmers
❑Any structural change in the database could
produce disaster in all application programs
that draw data from the database
❑Many database old-timers can recall the
interminable information delays
87
The Relational Model
• Developed by Codd (IBM) in 1970
• Considered ingenious but impractical in 1970
• Conceptually simple
• Computers lacked power to implement the
relational model
• Today, microcomputers can run sophisticated
relational database software

88
The Relational Model (continued)
• Relational Database Management System
(RDBMS)
• Performs same basic functions provided by
hierarchical and network DBMS systems, in
addition to a host of other functions
• Most important advantage of the RDBMS is its
ability to hide the complexities of the
relational model from the user

89
The Relational Model (continued)
• Table (relations)
– Matrix consisting of a series of row/column
intersections
– Related to each other through sharing a common
entity characteristic
• Relational diagram
– Representation of relational database’s entities,
attributes within those entities, and relationships
between those entities

90
The Relational Model (continued)

• Relational Table
– Stores a collection of related entities
• Resembles a file
• Relational table is purely logical structure
❑How data are physically stored in the database
is of no concern to the user or the designer
❑This property became the source of a real
database revolution

91
The Relational Model (continued)

92
The Relational Model (continued)

93
The Relational Model (continued)

• Rise to dominance due in part to its powerful and

flexible query language
• Structured Query Language (SQL) allows the user
to specify what must be done without specifying
how it must be done
• SQL-based relational database application
involves:
o User interface
o A set of tables stored in the database
o SQL engine

94
The Entity Relationship Model
• Widely accepted and adapted graphical tool
for data modeling
• Introduced by Chen in 1976
• Graphical representation of entities and their
relationships in a database structure

95
The Entity Relationship Model
• Entity relationship diagram (ERD)
o Uses graphic representations to model database
components
o Entity is mapped to a relational table
• Entity instance (or occurrence) is row in table
• Entity set is collection of like entities
• Connectivity labels types of relationships
o Diamond connected to related entities through a
relationship line

96
The Entity Relationship Model

97
The Entity Relationship Model

98
Keys
A key is a value which can always be used to uniquely identify an object instance. It
allows us to indentify a set of attributes that suffices to distinguish entities from each
other.

It is basically of three types:-

a) Super Key
b) Candidate Key
c) Primary Key

S-Roll No. S-Name S-Branch S-College S-Year

1001 Manoj IT GLBITM III

1002 Sunil CS GLBITM II

1003 Harish ME GLBITM II

Super Key:

A super key is a set of one or more attributes that, taken collectively, allows us to identify
uniquely an entity in the entity set.

For example:

Super Key= { S-Roll, S-Name, S-Branch, S-College, S-Year}

Candidate Key
A candidate key can be defined as the minimum no. of super key that identifies the
record uniquely. It is possible that several distinct set attributes could serve as a
candidate key

For example:

Candidate Key= { S-Roll, S-Name, S-Branch}

Primary Key:

A primary key can be defined as the minimum no. of candidate key that is chosen by the
database designer as the principal means of identifying entities within an entity set. There
should not be any duplicacy and not null in the record of primary key.

For example:

Primary Key= { S-Roll}

Foreign Key
A foreign key is a column whose values are the same as the primary key of another table.
The relationship is made between two relationship tables by matching the values of the
foreign key in one table with the values of the primary key in another.
Normalization
Normalization is a formal process for deciding which attributes should be grouped
together in a relation. It provides us with a systematic and scientific process.

Normalization is the process of building database structures to store data.

It is basically of three types:-

First Normal Form (1NF)

The multi-valued attributes (Called repeating groups) should be removed .

Second Normal Form (2NF)

The partial functional dependencies have to be removed .

Third Normal Form (3NF)

The transitive dependencies have to be removed i.e. column not dependent on the key.

BCNF (Boyee-Codd Normal Form )

“To be in BCNF, a table must only have candidate keys as determination”
First Normal Form (1NF)

The multi-valued attributes (Called repeating groups) should be removed .

Second Normal Form (2NF)
The partial functional dependencies have to be removed. A relation is in second normal
form if it is in first normal form and every non-key attribute is fully and functionally
dependent on the primary key. A relation in the first normal form will be in the second
normal form if one of the following conditions is satisfied:

1. The primary key consists of only one attribute.

2. All the attributes in the relation are components of the primary key.
3. Every non key attributes is functionally dependent on the full set of primary key
attributes.
Order_Noc Title QTY Unit_Price
1 Computer Networks 1 250
1 Graphics 1 275
1 DBMS 2 295
2 Multimedia 1 300
2 Data Structure 1 190
3 DBMS 1 295
3 Multimedia 2 300
3 Computer Networks 5 250

Table in 1NF
Order_Noc Title QTY
1 Computer Networks 1
1 Graphics 1
1 DBMS 2
2 Multimedia 1
2 Data Structure 1
3 DBMS 1
3 Multimedia 2
3 Computer Networks 5

Title Unit_Price
Issues of 1NF:
Computer Science 250
1. Update the prices Graphics 275
2. Update the titles
DBMS 295
Multimedia 300
Data Structure 190

Conversion 1NF to 2NF

Third Normal Form (3NF)

A relation is in third normal form if it is in second normal form and no transitive

dependencies exist. A transitive dependency in a relation is a functional dependency
between two or more non key attributes. As a result of the transitive dependency, there are
update anomalies as follows:

1. Insertion Anomaly
2. Deletion Anomaly
3. Modification Anomaly

Course_Name Head_Dept Room_No Room_Capacity

B.Tech. (CS) Dr. Anil 102 60
B.Tech. (IT) Dr. Manoj 107 50
B.Tech. (ME) Dr. Shikhar 105 60
B.Tech. (EEE) Dr. Anmol 103 100
MCA Dr. Vandna 111 40
Course_Name Head_Dept Room_No
B.Tech. (CS) Dr. Anil 102
B.Tech. (IT) Dr. Manoj 107
B.Tech. (ME) Dr. Shikhar 105
B.Tech. (EEE) Dr. Anmol 103
MCA Dr. Vandna 111

Room_No Room_Capacity
102 60
107 50
105 60
103 100
111 40

Conversion 2NF to 3NF

Table in 2NF
Emp_No E_Name E_Salary Dept_Name Dept_Location
1001 Vishal 7500 Accounts 102
1002 Amit 5000 Sales 104
1003 Anuj 10000 Accounts 102
1004 Vikas 4500 Sales 104
1005 Sumit 6500 Sales 104

Table in 3NF
Emp_No E_Name E_Salary Dept_Name
1001 Vishal 7500 Accounts
1002 Amit 5000 Sales
1003 Anuj 10000 Accounts
1004 Vikas 4500 Sales
1005 Sumit 6500 Sales

Dept_Name Dept_Location
Accounts 102
Sales 104
Accounts 102
Sales 104
Sales 104
BCNF (Boyee-Codd Normal Form )

“To be in BCNF, a table must only have candidate keys as determination”. BCNF is
primarily useful because it is simpler than 3NF.

Stud_ID S_Name Subject Grade

1001 Vishal Physics A
1001 Vishal Chemistry C
1001 Vishal Maths C
1002 Sumit Physics A
1002 Sumit Chemistry A
1002 Sumit Maths B

Table in 3NF
Stud_ID Subject Grade
1001 Physics A

1001 Chemistry C

1001 Maths C

1002 Physics A

1002 Chemistry A

1002 Maths B

Stud_ID S-Name
1001 Vishal
1002 Sumit
Views
A view is a virtual table based on the result-set of a select statement. A view
contains rows and columns just like real table. To reduce redundant data to the
minimum possible rows and columns.

Database languages allows the creation of an object called a View.

Some view are used only for looking at table data and other views can be used to
insert, update, and delete table data as well as view data.
Like Read Only View and Updateable View

We define a view in SQL by using the create view command.

Example: Create view as <Query Expression>

There are two types of view:

1. Simple View

2. Complex View
Advantages of Views

1. Security
2. Query Simplicity
3. Structural Simplicity
4. Insulation from change
5. Data Integrity

Disadvantages of Views

1. Update restrictions
2. Slow Performance
SQL, the Structured
Query Language
Overview
Introduction
DDL Commands
DML Commands
SQL Statements, Operators, Clauses
Aggregate Functions
Some Facts on SQL
SQL data is case-sensitive, SQL commands are not.

First Version was developed at IBM by Donald D. Chamberlin

and Raymond F. Boyce. [SQL]

Developed using Dr. E.F. Codd's paper, “A Relational Model

of Data for Large Shared Data Banks.”

SQL query includes references to tuples variables and the

attributes of those variables
SQL: DDL Commands
CREATE TABLE: used to create a table.

ALTER TABLE: modifies a table after it was created.

DROP TABLE: removes a table from a database.

SQL: CREATE TABLE Statement
Things to consider before you create your table are:
The type of data
the table name
what column(s) will make up the primary key
the names of the columns

CREATE TABLE statement syntax:

CREATE TABLE <table name>
( field1 datatype ( NOT NULL ),
field2 datatype ( NOT NULL )
);
SQL: ALTER TABLE Statement
To add or drop columns on existing tables.

ALTER TABLE statement syntax:

ALTER TABLE <table name>
ADD attr datatype;
or
DROP COLUMN attr;
Example:
CREATE TABLE FoodCart (
date varchar(10),
food varchar(20), FoodCart
profit float date food profit
);

ALTER TABLE FoodCart ( FoodCart

ADD sold int
date food profit sold
);

ALTER TABLE FoodCart( FoodCart

DROP COLUMN profit date food sold
);

DROP TABLE FoodCart;

SQL: DML Commands
INSERT: adds new rows to a table.

UPDATE: modifies one or more attributes.

DELETE: deletes one or more rows from a table.

SQL: INSERT Statement
To insert a row into a table, it is necessary to have a value
for each attribute, and order matters.
INSERT statement syntax:
INSERT into <table name>
VALUES ('value1', 'value2', NULL);
Example: INSERT into FoodCart
VALUES (’02/26/08', ‘pizza', 70 );

FoodCart
date food sold
date food sold 02/25/08 pizza 350
02/25/08 pizza 350 02/26/08 hotdog 500
02/26/08 hotdog 500 02/26/08 pizza 70
SQL: UPDATE Statement
To update the content of the table:
UPDATE statement syntax:
UPDATE <table name> SET <attr> = <value>
WHERE <selection condition>;
Example: UPDATE FoodCart SET sold = 349
WHERE date = ’02/25/08’ AND food = ‘pizza’;
FoodCart

date food sold date food sold

02/25/08 pizza 350 02/25/08 pizza 349
02/26/08 hotdog 500 02/26/08 hotdog 500
02/26/08 pizza 70 02/26/08 pizza 70
SQL: DELETE Statement
To delete rows from the table:
DELETE statement syntax:
DELETE FROM <table name>
WHERE <condition>;
Example: DELETE FROM FoodCart
WHERE food = ‘hotdog’;

FoodCart
date food sold date food sold
02/25/08 pizza 349 02/25/08 pizza 349
02/26/08 hotdog 500 02/26/08 pizza 70
02/26/08 pizza 70
Note: If the WHERE clause is omitted all rows of data are deleted from the table.
SQL: SELECT Statement
A basic SELECT statement includes 3 clauses

SELECT <attribute name> FROM <tables> WHERE <condition>

SELECT FROM WHERE

Specifies the Specifies the tables Specifies the

attributes that are that serve as the selection condition,
part of the resulting input to the including the join
relation statement condition.

Note: that you don't need to use WHERE

SQL: SELECT Statement (cont.)
Using a “*” in a select statement indicates that every
attribute of the input table is to be selected.
Example: SELECT * FROM … WHERE …;

To get unique rows, type the keyword DISTINCT after

SELECT.
Example: SELECT DISTINCT * FROM …
WHERE …;
Example: 1) SELECT *
Person FROM person
Name Age Weight WHERE age > 30;
Harry 34 80 Name Age Weight
Sally 28 64 Harry 34 80
George 29 70 Helena 54 54
Helena 54 54 Peter 34 80
Peter 34 80

2) SELECT weight 3) SELECT distinct weight

FROM person FROM person
WHERE age > 30; WHERE age > 30;
Weight Weight
80 80
54 54
80
SQL: Join operation
A join can be specified in the FROM clause which
list the two input relations and the WHERE clause
which lists the join condition.
Example:

Emp Dept
ID State ID Division
1000 CA 1001 IT
1001 MA 1002 Sales
1002 TN 1003 Biotech
SQL: Join operation (cont.)
inner join = join
SELECT *
FROM emp join dept or (FROM emp, dept)
on emp.id = dept.id;

Emp.ID Emp.State Dept.ID Dept.Division

1001 MA 1001 IT
1002 TN 1002 Sales
SQL: Join operation (cont.)
left outer join = left join
SELECT *
FROM emp left join dept
on emp.id = dept.id;

Emp.ID Emp.State Dept.ID Dept.Division

1000 CA null null
1001 MA 1001 IT
1002 TN 1002 Sales
SQL: Join operation (cont.)
right outer join = right join
SELECT *
FROM emp right join dept
on emp.id = dept.id;

Emp.ID Emp.State Dept.ID Dept.Division

1001 MA 1001 IT
1002 TN 1002 Sales
null null 1003 Biotech
SQL: Like operation
Pattern matching selection
% (arbitrary string)
SELECT *
FROM emp
WHERE ID like ‘%01’;
finds ID that ends with 01, e.g. 1001, 2001, etc
_ (a single character)
SELECT *
FROM emp
WHERE ID like ‘_01_’;
finds ID that has the second and third character as 01, e.g.
1010, 1011, 1012, 1013, etc
SQL: The ORDER BY Clause
Ordered result selection
desc (descending order)
SELECT *
FROM emp
order by state desc
puts state in descending order, e.g. TN, MA, CA
asc (ascending order)
SELECT *
FROM emp
order by id asc
puts ID in ascending order, e.g. 1001, 1002, 1003
SQL: The GROUP BY Clause
The function to divide the tuples into groups and returns an
aggregate for each group.
Usually, it is an aggregate function’s companion
SELECT food, sum(sold) as totalSold
FROM FoodCart
group by food;
FoodCart

date food sold food totalSold

02/25/08 pizza 349 hotdog 500
02/26/08 hotdog 500 pizza 419
02/26/08 pizza 70
SQL: The HAVING Clause
The substitute of WHERE for aggregate functions
Usually, it is an aggregate function’s companion
SELECT food, sum(sold) as totalSold
FROM FoodCart
group by food
having sum(sold) > 450;
FoodCart

date food sold food totalSold

02/25/08 pizza 349 hotdog 500
02/26/08 hotdog 500
02/26/08 pizza 70
SQL: Aggregate Functions
Are used to provide summarization information for SQL
statements, which return a single value.

COUNT(attr)
SUM(attr)
MAX(attr)
MIN(attr)
AVG(attr)

Note: when using aggregate functions, NULL values are not

considered, except in COUNT(*) .
SQL: Aggregate Functions (cont.)
FoodCart

date food sold

02/25/08 pizza 349
02/26/08 hotdog 500
02/26/08 pizza 70
COUNT(attr) -> return # of rows that are not null
Ex: COUNT(distinct food) from FoodCart; -> 2

SUM(attr) -> return the sum of values in the attr

Ex: SUM(sold) from FoodCart; -> 919

MAX(attr) -> return the highest value from the attr

SQL: Aggregate Functions (cont.)
FoodCart

date food sold

02/25/08 pizza 349
02/26/08 hotdog 500
02/26/08 pizza 70
MIN(attr) -> return the lowest value from the attr
Ex: MIN(sold) from FoodCart; -> 70

AVG(attr) -> return the average value from the attr

Ex: AVG(sold) from FoodCart; -> 306.33
Note: value is rounded to the precision of the datatype