Introduction to Database Management System.pdf

Database Management System
Biswajit Nayak
Assistant Professor

Chapter:1
Introduction To

Database
• Data - Data is meaningful known raw facts that can be
processed and stored as information.
• Database - Database is a collection of interrelated and
organized data. In general, it is a collection of files (tables).
Biswajit Nayak

• Important Terms to Remember
– Data-Data can be defined as raw, unorganized facts that need to be
processed.
– Information- Processed form of data which conveys some meaning is
known as information..
Biswajit Nayak
– Ex-
• Data- 45 47
• Information- Mark of two students
is 45,47

• Database Management System (DBMS) is a collection of
interrelated data (called database) and a set of programs to access,
update and manage those data.
OR
• A database management system (DBMS) is a collection of programs
that enables users to create and maintain a database. The DBMS is
a general-purpose software system that facilitates the processes of
defining, constructing, manipulating, and sharing databases among
various users and applications.
OR
• It is a software package to facilitate creation and maintenance of
computerized database.
Biswajit Nayak

• It is general purpose software that facilitates the following:
• Defining: Specifying data types and structures, and
constraints for data to be stored.
• Constructing: Storing data in a storage medium.
• Manipulating: Involves querying, updating and
generating reports.
• Sharing: Allowing multiple users and programs to
access data simultaneously.
• Eg. Of DBMS- Access, dBase, FileMaker Pro, and FoxBASE, ORACLE etc
Biswajit Nayak

Purpose of Database System
• In the early days, database applications were built on top of file
systems :
– Drawbacks of using file systems to store data.
• 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
• Data isolation — multiple files and formats
• Integrity problems
– Integrity constraints (e.g. account balance > 0) become part of
program code
– Hard to add new constraints or change existing ones
Biswajit Nayak

• Atomicity of updates
– Failures may leave database 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
• 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
• Database systems offer solutions to all the above problems
Biswajit Nayak

Advantages of DBMS:
• Data independence: provides an abstract view of the data that hides the
details data representation and storage.
• Efficient Data Access: This is the advantage where we use variety of
techniques to store and retrieve data.
• Data integrity and security: we can ensure data integrity if the data is
always enforced through integrity constraint.
• Data administration: data administration deals with the modelling of the
data and treats data as an organizational resource, while "database"
administration deals with the implementation of the types of databases
that are in use.
• Concurrent Access and crash recovery: It ensures concurrent access of the
data in such a way that the data is being accessed by only one user a time.
Also protects the system from crashes.
• Reduced Application Development time: It supports all the important
functions that are common to many applications.
Biswajit Nayak

Disadvantages of a DBMS:
• Setup of the database system requires more knowledge,
money, skills, and time.
• The complexity of the database may result in poor
performance.
Biswajit Nayak

Applications of DBMS
• Banking – For customer information, accounts, loans, and
banking transactions.
• Airlines – For reservation and schedule information.
• Universities – For student information, course registrations, and
grades.
• Credit Card Transactions – For purchases on credit card and
generation of monthly statements.
• Telecommunication – For keeping records of calls made,
generating monthly bills, maintaining balances on prepaid
calling cards, and storing information about communication
networks.
• Finance – For storing information about holdings, sales, and
purchases of financial instruments such as stocks and bonds.
Biswajit Nayak

• Sales – For customer, product, and purchase information.
• Manufacturing – For management of supply chain and for
tracking production of items in factories, inventories of items
in warehouses/stores, and orders for items.
• Human Resources – For information about employees,
salaries, payroll taxes and benefits, and generation of pay
checks.
Biswajit Nayak

Database System Environment
• The Database System Environment may consists of several
components like:
• Users
• Database Systems
• DBMS Software
• Stored Database
Biswajit Nayak

Actors
• We identify the people whose jobs involve the day-to-day use
of a large database; we call them the actors on the scene.
• Database Administrator
• Database Designer
• End User
• Casual
• Naive
• Sophisticated
• Stand-alone
Biswajit Nayak

Database Administrator (DBA)
• Administering different resources of database is the
responsibility of the database administrator (DBA). The DBA
is responsible for authorizing access to the database,
coordinating and monitoring its use, and acquiring software
and hardware resources as needed.
• The DBA is also accountable for problems such as security
breaches and poor system response time. In large
organizations, the DBA is assisted by a staff that carries out
these functions.
Biswajit Nayak

Role and Responsibility of DBA
• Schema definition: the creation of the original database
schema. This involves writing a set of definitions in a DDL
(data storage and definition language), compiled by the DDL
compiler into a set of tables stored in the data dictionary.
• Storage structure and access method definition: writing a set
of definitions translated by the data storage and definition
language compiler.
• Schema and physical organization modification: writing a set
of definitions used by the DDL compiler to generate
modifications to appropriate internal system tables (e.g. data
dictionary). This is done rarely, but sometimes the database
schema or physical organization must be modified.
Biswajit Nayak

• Granting user authority to access the database: granting
different types of authorization for data access to various
users
• Specifying integrity constraints: generating integrity
constraints. These are consulted by the database manager
module whenever updates occur.
• Routine Maintenance: It includes the following-
• Acting as liaison with users.
• Monitoring performance and responding to changes in
requirements.
• Periodically backing up the database
Biswajit Nayak

Database Designers
• Database designers are responsible for identifying the data to
be stored in the database and for choosing appropriate
structures to represent and store this data.
• It is the responsibility of database designers to communicate
with all prospective database users in order to understand
their requirements and to create a design that meets these
requirements. These tasks are mostly undertaken before the
database is actually implemented and populated with data.
Biswajit Nayak

End Users
• Casual end users occasionally access the database, but they
may need different information each time.
• Naive or parametric end users make up a sizable portion of
database end users.
Their main job function revolves around constantly
querying and updating the database, using standard types of
queries and updates—called canned transactions—that have
been carefully programmed and tested.
Ex-
• Bank tellers check account balances and post withdrawals and deposits.
• Reservation agents for airlines, hotels, and car rental companies check
availability for a given request and make reservations
Biswajit Nayak

• Sophisticated end users –They may be engineers, scientists,
business analysts, and others who thoroughly familiarize
themselves with the facilities of the DBMS in order to
implement their own applications to meet their complex
requirements.
• Standalone users – These are the users who maintains
personal databases by using ready-made program packages
that provide easy-to-use menu-based or graphics-based
interfaces.
Ex- Tax package that stores a variety of personal financial
data for tax purposes.
Biswajit Nayak

Data abstraction
• Data abstraction generally refers to the suppression of
details of organization data and storage, and the highlighting
of the essential features for an improved understanding of
data.
OR
• Data abstraction is the process of specifying essential
features and hiding details of data organization and storage to
reduce the complexity and improve the user interaction.
Biswajit Nayak

• Data abstraction: It can be summed up as follows.
• When the DBMS hides certain details of how data is
stored and maintained, it provides what is called as the
abstract view of data.
• This is to simplify user-interaction with the system.
• Complexity (of data and data structure) is hidden from
users through several levels of abstraction.
• purposes:
• To provide abstract view of data.
• To hide complexity from user.
• To simplify user interaction with DBMS
Biswajit Nayak

Level of Abstraction
Biswajit Nayak
• There are three levels of data abstraction
• Physical level
• Logical level
• View level

• Physical Level: It describes how a record is stored. (It
describes how data is actually stored in database.)
• Features:
• Lowest level of abstraction.
• It describes how data are actually stored.
• It describes low-level complex data structures in detail.
• At this level, efficient algorithms to access data are
defined.
Biswajit Nayak

• Logical level: It describes what data stored in database, and
the relationships among the data.(Records can be described as
fields and attributes along with their data types, their relationship among
each other can be logically implemented)
• Features:
• It is next-higher level of abstraction. Here whole
Database is divided into small simple structures.
• Users at this level need not be aware of the physical-
level complexity used to implement the simple
structures.
• Here the aim is ease of use.
• Generally, database administrators (DBAs) work at
logical level of abstraction. Biswajit Nayak

• View level: Application programs hide details of data types.
Views can also hide information (e.g., salary) for security
purposes.(User just interact with system with the help of GUI and enter
the details at the screen).
• Features:
• It is the highest level of abstraction.
• It describes only a part of the whole Database for
particular group of users.
• This view hides all complexity.
• It exists only to simplify user interaction with system.
• The system may provide many views for the whole
system
Biswajit Nayak

Introduction To
Business Rules

An Overview of Business Rule
• Companies which are not well-organized have employees
making and executing decisions on the fly. Clear rules and
guidance is required within the enterprise regarding Business
actions which are permitted and which are not. In such a
scenario, all decisions made in the company would be without
the structure offered by consulting company
policies/guidelines. This would result in general disorder and
chaos within the enterprise. Business Rules are the
constraints and conditions that define how the enterprise
functions and should be analysed with the help of Business
needs.
Biswajit Nayak

Business Rule?
• A business rule is statement that imposes some form of
constraint on a specific aspect of the business.
• Constraints that cannot be directly expressed in the schemas
of the data model, and hence must be expressed and
enforced by the application programs. We call these
application-based or semantic constraints or business rules.
• The rules in the mini world that determine the constraints are
sometimes called the business rules, since they are
determined by the business or organization that will utilize
the database.
Biswajit Nayak

• A business rule is a brief, precise, and unambiguous
description of a policy, procedure, or principle within a
specific organization’s environment.
• Business Rules are a combination of guidelines and inferences
from those guidelines which in turn direct how we conduct
Business .
• Business Rules are framed to help all stakeholders understand
processes in the Business or the Business itself.
• A business rule is a rule that is defined and owned by business
people, but is coded and implemented by system
development people.
Biswajit Nayak

• The rule expresses a policy about some aspect of how the
organization carries out its tasks. In any case, the business rule
represents information about the real world, and that information
makes sense from a business point of view. That is, people are
interested in “the set of rules that determine how a business
operates”.
• A statement that defines or constrains some aspect of the business.
It is intended to assert business structure or to control or influence
the behavior of the business.
• A student may register for a section of a course only if he or she
has successfully completed the prerequisites for that course
• A preferred customer qualifies for a 10 percent discount, unless
he has an overdue account balance
Biswajit Nayak

• In the database world, it has been more common to use the
related term integrity constraint when referring to such rules.
• referring to maintaining valid data values and relationships
in the database.
• The Database designer has to discover these rules, select the
ones relevant to the desired database application, and
formulate them as “brief, precise, and unambiguous
description of a policy, procedure, or principle”.
Biswajit Nayak

Examples
• Business Rule may includes :
• A decision-making approval structure for invoice
processing where only certain managers can sign off on
invoices totaling a specific amount
• Calculations in which a formula may be used to calculate
revenue or expenses
• Policies where an organization requires its employees to
work with a preferred list of vendors
Biswajit Nayak

Characteristics of Good Business Rule
• Declarative: A Business rule describes wat a process validates.
Not how policy is enforced or conducted. The process does
not describe a process of implementation.
• Precise: With the related organization, the rule must have
only one interpretation among all interested people ad its
meaning must be clear.
• Atomic: A Business rule makes one statement, not several. No
part of rule can stand on its own as a rule.
• Consistent: A Business rule must be internally consistent and
must be consistent wit other group.
Biswajit Nayak

• Expressible: A Business rule must be able to be stated in
natural language but it will be stated in structural natural
language so that there is no misinterpretation.
• Distinct: Business rules are not redundant but a business rules
a refer to other rules.
Biswajit Nayak

Types Of Business Rule
• Business Rule can be categorized into three different
categories:
Biswajit Nayak

• A Derivation — a statement of knowledge that is derived from
other knowledge in the business.
• A Structural Assertion — a defined concept or a statement of
a fact that expresses some aspect of the structure of an
enterprise. This encompasses both terms and the facts
assembled from these terms.
• An Action Assertion — a statement of a constraint or
condition that limits or controls the actions of the enterprise
Biswajit Nayak

Important Concepts to Remember
• Active rules can also be used to enforce integrity constraints
by specifying the types of events that may cause the
constraints to be violated and then evaluating appropriate
conditions that check whether the constraints are actually
violated by the event or not. Hence, complex application
constraints, often known as business rules, may be enforced
that way.
• Example: In the UNIVERSITY database application, one rule
may monitor the GPA of students whenever a new grade is
entered, and it may alert the advisor if the GPA of a student
falls below a certain threshold; another rule may check that
course prerequisites are satisfied before allowing a student to
enroll in a course; and so on.
Biswajit Nayak

Benefits of Business Rules
• Efficiency: Domain experts can centrally define and implement
changes to policy logic themselves. Updates can be immediately
applied to relevant processes without waiting for a technical
resource to become available.
• Increased productivity: With employees spending less time on
tedious updates, they can focus more on value-add activities.
• Consistency: If a business rule is updated, all processes referencing
that rule will be changed accordingly.
• Improved compliance: Organizations can effectively show how
certain outcomes were reached when business rules are explicitly
defined and decisions tracked.
• Reduced Complexity: Business rules are represented in simplified
formats that do not require coding skills, such as tables and
diagrams, and can be re-used as necessary on appropriate
processes.
Biswajit Nayak

Sources of Business Rules
• There are several Sources of Business Rules, such as:
• Company managers
• Policy makers
• Department managers
• Written documentation
• Procedures
• Standards
• Operations manuals
• Direct interviews with end users
Biswajit Nayak

Conclusion
• Business Rules can be easily analysed when they are
documented and managed independent of the processes
enforcing them. Occasionally, when not planned properly,
Business Rules may contradict one another when combined.
Business Rules should be progressively maintained to see that
they stay relevant to the enterprise.
Biswajit Nayak

Chapter:2
Database System Concepts and
Architecture

Data Models
• A collection of concepts that can be used to describe the
structure of a database—provides the necessary means to
achieve this abstraction.
Structure of a database means the data types,
relationships, and constraints that apply to the data.
OR
• A data model is a collection of high-level data description
constructs that hide many low-level storage details.
A DBMS allows a user to define the data to be stored in
terms of a data model.
Biswajit Nayak

Categories of Data Models
• According to the types of concepts they use to describe the
database structure , data models can be categorized into
several categories such as:-
• Conceptual (high-level, semantic)
• Implementation (representational)
• Physical (low-level, internal)
Biswajit Nayak

Conceptual Data Model
• Provide concepts that are close to the way many users
perceive data. (Also called entity-based or object-based data
models.)
• Conceptual data models use concepts such as
• Entities
• Attributes
• Relationships
Biswajit Nayak

• An entity represents a real-world object or concept, such as
an employee or a project from the mini-world that is
described in the database.
• An attribute represents some property of interest that further
describes an entity, such as the employee’s name or salary.
• A relationship among two or more entities represents an
association among the entities,
Ex- A works-on relationship between an employee and a
project.
• Entity-Relationship model—a popular high-level conceptual
data model
Biswajit Nayak

• Relationship - describes an association among entities
– One-to-many (1:M) relationship
• A PAINTER can paint many PAINTINGS;
• Each PAINTING is painted by one PAINTER
– Many-to-many (M:N or M:M) relationship
• An EMPLOYEE can learn many SKILLS;
• Each SKILL can be learnt by many EMPLOYEES
– One-to-one (1:1) relationship
• An EMPLOYEE manages one STORE;
• Each store is managed by one EMPLOYEE
Biswajit Nayak

Representational/Implementation
data models
• Implementation (representational) data models: Provide
concepts that fall between the balancing user views with
some computer storage details. (Also called Record based
data model).
• It includes different data models such as:-
• Relational models
• Network models
• Hierarchical models
Biswajit Nayak

Relational data model
• The relational model uses a collection of tables to represent
both data and the relationships among those data.
• Each table has multiple columns, and each column has a
unique name as follows
Biswajit Nayak

• ADVANTAGES:
– Structural independence – changes in the relational data
structure do not affect the DBMS’s data access in any way
– Easier database design, implementation, management,
and use
– Powerful database management system
• DISADVANTAGES:
– Substantial hardware and system software overhead
– Can facilitate poor design and implementation
– May promote “islands of information” problems
Biswajit Nayak

Network data model
• Data in the network model is represented by collection of
records, and relationship among data is represented by links,
which can be viewed as pointers. The records in the database
are organized as collections of arbitrary graphs. Such type of
database is shown below:-
Biswajit Nayak

• ADVANTAGES:
• Network Model is able to model complex relationships and
represents semantics of add/delete on the relationships.
• Can handle most situations for modeling using record
types and relationship types.
• DISADVANTAGES:
• Navigational and procedural nature of processing.
• Database contains a complex array of pointers that thread
through a set of records.
Biswajit Nayak

Hierarchical data models
• The hierarchical model is similar to the network model in the
sense that data and relationships among data are represented
by records and links, respectively. It differs from the network
model in that records are organized as collection of trees
rather than arbitrary graphs.
Biswajit Nayak

• ADVANTAGES:
• Hierarchical Model is simple to construct and operate on
• Corresponds to a number of natural hierarchically
organized domains - e.g., assemblies in manufacturing,
personnel organization in companies
• DISADVANTAGES:
• Navigational and procedural nature of processing
• Database is visualized as a linear arrangement of records
• Little scope for "query optimization
Biswajit Nayak

Physical Data Model
• Physical data models describe how data is stored as files in
the computer by representing information such as record
formats, record orderings, and access paths.
• An access path is a structure that makes the search for
particular database records efficient.
An index is an example of an access path that allows
direct access to data using an index term or a keyword. It is
similar to the index at the end of this book
Biswajit Nayak

Evolution of Data Model
Biswajit Nayak

Schema
• The overall design of a database is called database schema.
E.g., the database consists of information about a set of
customers and accounts and the relationship between them.
It is analogous to variable along with its type information in a
program.
• There are different types of Schema, such as:-
• Physical schema
• Logical schema
• Subschema
Biswajit Nayak

• Physical schema: It is database design at the physical level. It
is hidden below logical schema, and can be changed easily
without affecting application programs.
• Logical schema: It is database design at the logical level.
Programmers construct applications using logical schema. It is
by far the most important schema, in terms of its effect on
application programs.
• Subschema: It is schema at view level.
Biswajit Nayak

Database Schema
• The description of a database is called the database schema,
which is specified during database design and is not
expected to change frequently.
• A displayed schema is called a schema diagram.
Biswajit Nayak

Instance
• It is the actual content of the database at a particular point in
time. It is analogous to the value of a variable.
Biswajit Nayak

Architecture for database systems
• Architecture for database systems, called the three-schema
architecture, that was proposed to help achieve and visualize
characteristics.
• The goal of the three-schema architecture, is to separate the
user applications from the physical database.
• There are three level of schemas, such as:-
• Internal level has an internal schema
• Conceptual level has a conceptual schema
• External or view level / external schemas or user view
Biswajit Nayak

• The internal level has an internal schema, which describes the
physical storage structure of the database. The internal schema
uses a physical data model and describes the complete details of
data storage and access paths for the database.
• The conceptual level has a conceptual schema, which describes the
structure of the whole database for a community of users. The
conceptual schema hides the details of physical storage structures
and concentrates on describing entities, data types, relationships,
user operations, and constraints. Usually, a representational data
model is used to describe the conceptual schema when a database
system is implemented. This implementation conceptual schema is
often based on a conceptual schema design in a high-level data
model.
Biswajit Nayak

• The external or view level includes a number of external
schemas or user views. Each external schema describes the
part of the database that a particular user group is interested
in and hides the rest of the database from that user group. As
in the previous level, each external schema is typically
implemented using a representational data model, possibly
based on an external schema design in a high-level data
model.
Biswajit Nayak

• The three-schema architecture is a convenient tool with
which the user can visualize the schema levels in a database
system. Most DBMSs do not separate the three levels
completely and explicitly, but support the three-schema
architecture to some extent.
Biswajit Nayak

Data Independence
• The three-schema architecture can be used to further explain
the concept of data independence, which can be defined as
the capacity to change the schema at one level of a database
system without having to change the schema at the next
higher level.
• We can define two types of data independence:
• Logical data independence
• Physical data independence
Biswajit Nayak

Logical data independence
• Logical data independence is the capacity to change the
conceptual schema without having to change external
schemas or application programs. We may change the
conceptual schema to expand the database, to change
constraints, or to reduce the database.
• After the conceptual schema undergoes a logical
reorganization, application programs that reference the
external schema constructs must work as before Changes to
constraints can be applied to the conceptual schema without
affecting the external schemas or application programs
Biswajit Nayak

Physical data independence
• Physical data independence is the capacity to change the
internal schema without having to change the conceptual
schema.
• Hence, the external schemas need not be changed as well.
• Changes to the internal schema may be needed because some
physical files were reorganized:
– for example, by creating additional access structures to
improve the performance of retrieval or update.
• If the same data as before remains in the database, we should
not have to change the conceptual schema.
Biswajit Nayak

• The three-schema architecture can make it easier to achieve
true data independence, both physical and logical. However,
the two levels of mappings create an overhead during
compilation or execution of a query or program, leading to
inefficiencies in the DBMS
Biswajit Nayak

Introduction to Database Management System.pdf

Recommended

More Related Content

What's hot (20)

Similar to Introduction to Database Management System.pdf (20)

Recently uploaded (20)

Introduction to Database Management System.pdf