RELATIONAL DATABASE
MANAGEMENT SYSTEMS
UNIT-1
UNIT-I
1. Introduction to Database Management System
2. Database system Applications
3. Advantages of DBMS over File System
4. Data Models
5. Instances and schema
6. View of Data
7. Database Languages -DDL-DML
8. Database Users and Administrator
9. Database System Structure
UNIT-II
1.Database Design and ER diagrams
2.Attributes and Entity Sets
3.Relationships and Relationship Sets
4.Constraints -Keys
5.Design Issues
6.Entity-Relationship Diagram
7.Weak Entity Sets
8.Extended E-R Features
9.Database Design with ER model
10.Database Design for Banking Enterprise
UNIT –III
1.Introduction to the Relational Model
2.Structure of RDBMS
3.Integrity Constraints over Relations -Enforcing Integrity
Constraints
4.Querying Relational Data -Relational Algebra and Calculus.
5.Introduction to SQL-Data Definition commands
6.Data Manipulation Commands
7.Basic Structure, Set operations , Aggregate Operations
8.Join operations
9.Sub queries and correlated queries
10.SQL functions , views ,Triggers, Embedded SQL.
UNIT –IV
1.Functional Dependencies–Introduction
2.Basic Definitions, Trivial and Non trivial dependencies
3. closure of a set of dependencies, closure of attributes
4.irreducible set of dependencies
5.Schema Refinement in Database-Design-Problems Caused by
Redundancy
6.Decompositions –Problem Related to Decomposition
7.Lossless Join Decomposition
8.Dependency Preserving Decomposition
9.FIRST, SECOND, THIRD Normal Forms
10.BCNF ––Multi valued Dependencies
11.Fourth Normal Form.
UNIT-V
1.Transaction concept
2.Transaction state
3.Implementation of atomicity and Durability
4.Concurrent executions -Serializability, Recoverability
5.File Organization –Organization of records in file
6.Data Dictionary Storage
7.Indexing and Hashing
8.Basic Concepts , Ordered Indices
9.B+Tree Index files, B-tree index files
TEXT BOOKS
1.Database System Concepts, Silberschatz, Korth , Sixth Edition, McGraw hill
( 1,2,3 and 5 Units)
2.Introduction to Database Systems, C.J.Date, Pearson Education (4thUnit)
REFERENCES:
3.Fundamentals of Database Systems, Elmasri Navate Pearson Education
4.Database Management Systems, Raghu Ramakrishnan, Johannes Gehrke, TATA Mc
Graw Hill(1,2,3 and 5 Units)
5.Data base Systems design, Implementation, and Management, Peter Rob and
Carlos Coronel 7th Edition.
6.Data Base Systems using Oracle: A simplified guide to SQL and PL /SQL, Shah, PHI
Database Management Systems, Raghu Ramakrishnan, Johannes
Gehrke, TATA Mc Graw Hill(1,2,3 and 5 Units)
Fundamentals of Database Systems, Elmasri
Navate Pearson Education
Database System Concepts, Silberschatz, Korth , Sixth Edition, McGraw
hill ( 1,2,3 and 5 Units)
1.Introduction to Database Management System
• A database-management system (DBMS) is a collection of
interrelated data and a set of programs to access those data.
• The collection of data referred to as the database which
contains information relevant to an enterprise.
• The primary goal of a DBMS is to provide a way to store and
retrieve database information that is both convenient and
efficient
1.Introduction to Database Management System
• Database Management System (DBMS): A software package/
system to facilitate the creation and maintenance of a
computerized database.
• It defines (data types, structures, constraints), construct (storing
data on some storage medium controlled by DBMS) and
manipulate (querying, update, report generation) databases for
various applications.
.
1.Introduction to Database Management System
• A Database Management System (DBMS) is a software
package designed to store and manage databases:
1. Manages very large amounts of data.
2. Supports efficient access to very large amounts of data.
3. Supports concurrent access to very large amounts of data.
• Example: bank and its ATM machines.
4. Supports secure, atomic access to very large amounts of
data.
1.Introduction to Database Management System
1.Introduction to Database Management System
1.Introduction to Database Management System
• Database systems are designed to manage large
bodies of information.
• Management of data involves both defining
structures for storage of information and
providing mechanisms for the manipulation of
information.
1.Introduction to Database Management System
• The database system must ensure the safety of the information
stored, despite system crashes or attempts at unauthorized access.
• If data are to be shared among several users, the system must
avoid possible anomalous results.
• Because information is so important in most organizations,
computer scientists have developed a large body of concepts and
techniques for managing data.
Advantages of DBMS
• Data independence
• Application programs should not be exposed
to details of data representation and storage,
The DBMS provides an abstract view of the
data that hides such details.
Efficient Data Access
• A DBMS utilizes a variety of sophisticated
techniques to store and retrieve data
efficiently.
Data Integrity and Security
• If data is always accessed through the DBMS, the DBMS can
enforce integrity constraints.
• For example, before inserting salary information for an
employee, the DBMS can check that the department budget
is not exceeded.
• The DBMS can enforce access controls that govern what
data is visible to different classes of users.
Data Administration
• When several users share the data, centralizing the
administration of data can offer retrieval efficient.
• DBA(Data base Administrator) responsible for
organizing the data representation to minimize
redundancy and for fine-tuning the storage of the
data to make retrieval efficient.
Concurrent Access and Crash Recovery
• A DBMS schedules concurrent accesses to the
data in such a manner that users can think of
the data as being accessed by only one user at
a time.
• The DBMS protects users from the effects of
system failures.
Reduced Application Development Time
• The DBMS supports important functions that are
common to many applications accessing data in the
DBMS.
• DBMS applications are also likely to be more robust
than similar stand-alone applications because many
important tasks are handled by the DBMS
2.Database System Applications
• Databases are widely used
• Banking: For customer information, accounts, and
loans, and banking transactions.
• Airlines: For reservations and schedule information.
• Universities: For student information, course
registrations, and grades.
2.Database System Applications
• Credit card transactions: For purchases on credit cards 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 the
communication networks.
• Finance: For storing information about holdings, sales, and
purchases of financial instruments such as stocks and bonds.
2.Database System Applications
• 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 for generation of
paychecks.
Database Systems versus File Systems
• Consider part of a savings-bank enterprise that keeps
information about all customers and savings accounts.
• One way to keep the information on a computer is to
store it in operating system files.
• To allow users to manipulate the information, the
system has a number of application programs that
manipulate the files
Database Systems versus File Systems
• System programmers wrote these application
programs to meet the needs of the bank.
• New application programs are added to the
system as the need arises.
• Thus, as time goes by, the system acquires more
files and more application programs.
Database Systems versus File Systems
• This typical file-processing system is supported by a
conventional operating system.
• The system stores permanent records in various files, and it
needs different application programs to extract records from,
and add records to, the appropriate files.
• Before database management systems (DBMSs) came along,
organizations usually stored information in file systems.
Keeping organizational information in a file-processing system
has a number of major disadvantages
• Data redundancy and inconsistency.
• Since different programmers create the files and
application programs over a long period, the various files
are likely to have different formats and the programs may
be written in several programming languages.
• The same information may be duplicated in several
places (files).
example
• The address and telephone number of a particular customer
may appear in a file that consists of savings-account records
and in a file that consists of checking-account records.
• This redundancy leads to higher storage and access cost.
• It may lead to data inconsistency.
• That is, the various copies of the same data may no longer
agree.
• For example, a changed customer address may be reflected in
Difficulty in accessing data
• Suppose that one of the bank officers needs to
find out the names of all customers who live
within a particular postal-code area.
• The officer asks the data-processing
department to generate such a list.
Difficulty in accessing data
• conventional file-processing environments do
not allow needed data to be retrieved in a
convenient and efficient manner.
• More responsive data-retrieval systems are
required for general use.
Data isolation
• Because data are scattered in various files,
and files may be in different formats, writing
new application programs to retrieve the
appropriate data is difficult.
Integrity problems
• The data values stored in the database must satisfy certain types of
consistency constraints.
• For example, the balance of a bank account may never fall below a
prescribed amount (say, $100).
• Developers enforce these constraints in the system by adding appropriate
code in the various application programs.
• when new constraints are added, it is difficult to change the programs to
enforce them.
• The problem is compounded when constraints involve several data items
from different files.
Atomicity problems
• A computer system, like any other mechanical or electrical device, is subject to
failure.
• In many applications, it is crucial that, if a failure occurs, the data be restored to
the consistent state that existed prior to the failure.
• Consider a program to transfer $50 from account A to account B.
• If a system failure occurs during the execution of the program, it is possible that
the $50 was removed from account A but was not credited to account B, resulting
in an inconsistent database state.
• Clearly, it is essential to database consistency that either both the credit and debit
occur, or that neither occur.
Atomicity problems
• That is, the funds transfer must be atomic—it
must happen in its entirety or not at all.
• It is difficult to ensure atomicity in a
conventional file-processing system.
Concurrent-access anomalies
• For the sake of overall performance of the system and faster
response, many systems allow multiple users to update the data
simultaneously.
• In this environment, interaction of concurrent updates may result in
inconsistent data.
• Consider bank account A, containing $500.
• If two customers withdraw funds (say $50 and $100 respectively)
from account A at about the same time, the result of the concurrent
executions may leave the account in an incorrect (or inconsistent)
state.
Concurrent-access anomalies
• Suppose that the programs executing on behalf of each withdrawal read the old
balance, reduce that value by the amount being withdrawn, and write the result
back.
• If the two programs run concurrently, they may both read the value $500, and
write back $450 and $400, respectively.
• Depending on which one writes the value last, the account may contain either
$450 or $400, rather than the correct value of $350.
• Therefore, the system must maintain some form of supervision.
• In file systems supervision is difficult to provide because data may be accessed by
many different application programs that have not been coordinated previously.
Security problems
• Not every user of the database system should be able to access all
the data.
• For example, in a banking system, payroll personnel need to see
only that part of the database that has information about the
various bank employees.
• They do not need access to information about customer accounts.
• But, since application programs are added to the system in an ad
hoc manner, enforcing such security constraints is difficult.
Data models
• A data model is a collection of conceptual tools for describing data, data
relationships, data semantics, and consistency constraints.
• The entity–relationship (E-R) model is a high-level data model. It is based on
a perception of a real world that consists of a collection of basic objects, called
entities, and of relationships among these objects.
• The relational model is a lower-level model. It uses a collection of tables to
represent both data and the relationships among those data.
• Today a vast majority of database products are based on the relational model.
• Designers often formulate database schema design by first modeling data at a
high level, using the E-R model, and then translating it into the relational
model.
DBMS Database Models
• A Database model defines the logical design and structure of a
database and defines how data will be stored, accessed and updated in
a database management system.
• While the Relational Model is the most widely used database model,
there are other models too:
• Hierarchical Model
• Network Model
• Entity-relationship Model
• Relational Model
Data Models
• The structure of a database is the data model:
a collection of conceptual Tools for describing
data, data relationships, data semantics, and
consistency constraints.
• Data models provide a way to describe the
design of a database at the logical level.
Hierarchical Model
• This database model organizes data into a tree-like-structure, with a single
root, to which all the other data is linked.
• The hierarchy starts from the Root data, and expands like a tree, adding child
nodes to the parent nodes.
• In this model, a child node will only have a single parent node.
• This model efficiently describes many real-world relationships like index of a
book, recipes etc.
• In hierarchical model, data is organized into tree-like structure with one one-
to-many relationship between two different types of data, for example, one
department can have many courses, many professors and of-course many
students.
Hierarchical Model
Network Model
• This is an extension of the Hierarchical model. In this model data is
organized more like a graph, and are allowed to have more than one
parent node.
• In this database model data is more related as more relationships are
established in this database model. Also, as the data is more related,
hence accessing the data is also easier and fast.
• This database model was used to map many-to-many data relationships.
• This was the most widely used database model, before Relational Model
was introduced.
Network Model
The Entity-Relationship Model
• The entity-relationship (E-R) data model is based on a
perception of a real world that consists of a collection of basic
objects, called entities, and of relationships among these
objects.
• An entity is a “thing” or “object” in the real world that is
distinguishable from other objects.
• For example, each person is an entity, and bank accounts can be
considered as entities.
The Entity-Relationship Model
• Entities are described in a database by a set of
attributes.
• For example, the attributes account-number and balance
may describe one particular account in a bank, and they
form attributes of the account entity set.
• Similarly, attributes customer-name, customer-street
address and customer-city may describe a customer
entity.
The Entity-Relationship Model
• A relationship is an association among several
entities.
• For example, a depositor relationship
associates a customer with each account.
Entity-relationship Model
• In this database model, relationships are created by dividing
object of interest into entity and its characteristics into
attributes.
• Different entities are related using relationships.
• E-R Models are defined to represent the relationships into
pictorial form to make it easier for different stakeholders to
understand.
• This model is good to design a database, which can then be
turned into tables in relational model.
Entity-relationship Model
• The overall logical structure (schema) of a database can be
expressed graphically by an E-R diagram, which is built up
from the following components
• Rectangles, which represent entity
• Ellipses, which represent attributes
• Diamonds, which represent relationships among entity sets
• Lines, which link attributes to entity sets and entity sets to
relationships
Entity-relationship Model
• Each component is labeled with the entity or
relationship that it represents
• consider part of a database banking system
consisting of customers and of the accounts
that these customers have
Entity-relationship Model
• The E-R diagram indicates that there are two
entity sets, customer and account, with
attributes.
• The diagram also shows a relationship
depositor between customer and account
E-R DIAGRAM
Relational 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.
Relational Model
• In this model, data is organised in two-dimensional tables and the
relationship is maintained by storing a common field.
• This model was introduced by E.F Codd in 1970, and since then it has
been the most widely used database model, infact, we can say the only
database model used around the world.
• The basic structure of data in the relational model is tables. All the
information related to a particular type is stored in rows of that table.
• Hence, tables are also known as relations in relational model.
Relational Model
• presents a sample relational database comprising three tables:
• One shows details of bank customers, the second shows accounts,
and the third shows which accounts belong to which customers.
• Each table contains records of a particular type.
• Each record type defines a fixed number of fields, or attributes.
• The columns of the table correspond to the attributes of the
record type
Relational Model
• a special character (such as a comma) may be used to
delimit the different attributes of a record, and
another special character (such as a newline
character) may be used to delimit records.
• The relational model hides such low-level
implementation details from database developers and
users.
Relational Model
• The relational model is at a lower level of
abstraction than the E-R model.
• Database designs are often carried out in the
E-R model, and then translated to the
relational model.
Relational Database
View of Data
• A database system is a collection of interrelated files
and a set of programs that allow users to access and
modify these files.
• A major purpose of a database system is to provide
users with an abstract view of the data.
• That is, the system hides certain details of how the data
are stored and maintained.
View of Data
Data Abstraction
• For the system to be usable, it must retrieve data efficiently.
• The need for efficiency has led designers to use complex
data structures to represent data in the database.
• Since many database-systems users are not computer
trained, developers hide the complexity from users through
several levels of abstraction, to simplify users’ interactions
with the system
Physical level
• The lowest level of abstraction describes how
the data are actually stored.
• The physical level describes complex low-level
data structures in detail.
Logical level
• The next-higher level of abstraction describes what data are stored in the
database, and what relationships exist among those data.
• The logical level thus describes the entire database in terms of a small
number of relatively simple structures.
• Although implementation of the simple structures at the logical level
may involve complex physical-level structures, the user of the logical
level does not need to be aware of this complexity.
• Database administrators, who must decide what information to keep in
the database, use the logical level of abstraction.
View level
• The highest level of abstraction describes only part of the entire database.
• Even though the logical level uses simpler structures, complexity remains
because of the variety of information stored in a large database.
• Many users of the database system do not need all this information.
• They need to access only a part of the database.
• The view level of abstraction exists to simplify their interaction with the
system.
• The system may provide many views for the same database.
View level
• account, with fields account-number and
balance
• employee, with fields employee-name and
salary
Example: view of data
• At the physical level, a customer, account, or
employee record can be described as a block
of consecutive storage locations (for example,
words or bytes).
• The language compiler hides this level of
detail from programmers.
Example: view of data
• Similarly, the database system hides many of
the lowest-level storage details from database
programmers.
• Database administrators may be aware of
certain details of the physical organization of
the data.
Example: view of data
• At the logical level, each record is described by
a type definition.
• Programmers using a programming language
work at this level of abstraction.
• Similarly, database administrators usually work
at this level of abstraction
Example: view of data
• at the view level, computer users see a set of application programs that hide details of
the data types.
• Similarly, at the view level, several views of the database are defined, and database
users see these views.
• In addition to hiding details of the logical level of the database, the views also provide
a security mechanism to prevent users from accessing certain parts of the database.
• For example, tellers in a bank see only that part of the database that has information
on customer accounts.
• They cannot access information about salaries of employees.
Instances and Schemas
• Databases change over time as information is inserted
and deleted.
• 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.
• Schemas are changed infrequently.
Instances and Schemas
• A database schema corresponds to the variable
declarations (along with associated type definitions) in
a program.
• Each variable has a particular value at a given instant.
• The values of the variables in a program at a point in
time correspond to an instance of a database schema
Instances and Schemas
• Database systems have several schemas, partitioned according to the
levels of abstraction.
• The physical schema describes the database design at the physical
level.
• the logical schema describes the database design at the logical level.
• A database may also have several schemas at the view level,
sometimes called subschemas, that describe different views of the
database
Instances and Schemas
• programmers construct applications by using the logical
schema.
• The physical schema is hidden beneath the logical schema,
and can usually be changed easily without affecting
application programs.
• Application programs are said to exhibit physical data
independence if they do not depend on the physical schema,
and thus need not be rewritten if the physical schema changes
Data Independence
• Applications insulated from how data is
structured and stored.
• Logical data independence: Protection from
changes in logical structure of data.
• Physical data independence: Protection from
changes in physical structure of data.
86
Levels of Abstraction
• Many views, single View 1 View 2 View 3
conceptual (logical) schema
and physical schema. Conceptual Schema
– Views describe how users see
Physical Schema
the data.
– Conceptual schema defines
logical structure
– Physical schema describes the
files and indexes used.
87
Database Languages
• A database system provides a data definition language
to specify the database schema and a data manipulation
language to express database queries and updates.
• The data definition and data manipulation languages are
not two separate languages.
• instead they simply form parts of a single database
language, such as the widely used SQL language
Data-Definition Language
• Specify a database schema by a set of definitions
expressed by a special language called a data-
definition language (DDL).
• the following statement in the SQL language defines
the account table:
• Create table account(account-number
char(10),balance integer).
DDL
• DDL statement creates the account table.
• it updates a special set of tables called the
data dictionary or data directory.
Database Languages
• A data dictionary contains metadata—that is, data
about data.
• The schema of a table is an example of metadata.
• A database system consults the data dictionary
before reading or modifying actual data.
Database Languages
• specify the storage structure and access methods
used by the database system by a set of statements
in a special type of DDL called a data storage and
definition language.
• These statements define the implementation details
of the database schemas, which are usually hidden
from the users
Database Languages
• The data values stored in the database must
satisfy certain consistency constraints.
• For example, suppose the balance on an
account should not fall below $100.
• The DDL provides facilities to specify such
constraints.
• The database systems check these constraints
every time the database is updated
Data-Manipulation Language
• Data manipulation is
• The retrieval of information stored in the
database
• The insertion of new information into the
database
• The deletion of information from the database
• The modification of information stored in the
database
DML
• A data-manipulation language (DML) is a
language that enables users to access or
manipulate data as organized by the
appropriate data model.
• There are basically two types:
DML
• Procedural DMLs require a user to specify
what data are needed and how to get those
data.
• Declarative DMLs (also referred to as
nonprocedural DMLs) require a user to specify
what data are needed without specifying how
to get those data.
DML..
• Declarative DMLs are usually easier to learn
and use than are procedural DMLs.
• since a user does not have to specify how to
get the data, the database system has to
figure out an efficient means of accessing
data.
• The DML component of the SQL language is
nonprocedural
QUERY
• A query is a statement requesting the retrieval of information.
• The portion of a DML that involves information retrieval is called a
query language
• This query in the SQL language finds the name of the customer
whose customer-id is 192-83-7465:
• select customer.customer-name
• from customer
• where customer.customer-id = 192-83-7465
Database Users and Administrators
• A primary goal of a database system is to
retrieve information from and store new
information in the database.
• People who work with a database can be
categorized as database users or database
administrators
Database Users and User Interfaces
• There are four different types of database-
system users, differentiated by the way they
expect to interact with the system.
• Different types of user interfaces have been
designed for the different types of users.
Naive users
• Naive users are unsophisticated users who interact with the
system by invoking one of the application programs that have
been written previously.
• For example, a bank teller who needs to transfer $50 from
account A to account B invokes a program called transfer.
• This program asks the teller for the amount of money to be
transferred, the account from which the money is to be
transferred, and the account to which the money is to be
transferred.
Naive users
• As another example, consider a user who wishes to find her account
balance over the World Wide Web. Such a user may access a form,
where she enters her account number.
• An application program at the Web server then retrieves the account
balance, using the given account number, and passes this information
back to the user.
• The typical user interface for naive users is a forms interface, where the
user can fill in appropriate fields of the form
Application programmers
• Application programmers are computer professionals who write application programs.
• Application programmers can choose from many tools to develop user interfaces.
• Rapid application development (RAD) tools are tools that enable an application
programmer to construct forms and reports without writing a program.
• There are also special types of programming languages that combine imperative control
structures (for example, for loops, while loops and if-then-else statements) with
statements of the data manipulation language.
• These languages, sometimes called fourth-generation languages, include special features
to facilitate the generation of forms and the display of data on the screen.
• Most major commercial database systems include a fourth generation language.
Sophisticated users
• interact with the system without writing programs.
Instead,they form their requests in a database query language.
• They submit each such query to a query processor, whose
function is to break down DML statements into instructions
that the storage manager understands.
• Analysts who submit queries to explore data in the database
fall in this category.
Sophisticated users
• Online analytical processing (OLAP) tools simplify analysts’ tasks by letting them
view summaries of data in different ways.
• For instance, an analyst can see total sales by region (for example, North, South, East,
and West), or by product, or by a combination of region and product (that is, total
sales of each product in each region).
• The tools also permit the analyst to select specific regions, look at data in more detail
(for example, sales by city within a region) or look at the data in less detail (for
example, aggregate products together by category).
• Another tools for analysts is data mining tools, which help them find certain kinds of
patterns in data
Specialized users
• Specialized users are sophisticated users who write
specialized database applications that do not fit into
the traditional data-processing framework.
• computer-aided design systems, knowledgebase and
expert systems, systems that store data with complex
data types (for example, graphics data and audio
data), and environment-modeling systems.
Database Administrator
• One of the main reasons for using DBMSs is to
have central control of both the data and the
programs that access those data.
• A person who has such central control over the
system is called a database administrator
(DBA).
DBA Responsibilities
• Schema definition. The DBA creates the original database
schema by executing a set of data definition statements in the
DDL.
• Storage structure and access-method definition.
• Schema and physical-organization modification. The DBA
carries out changes to the schema and physical organization to
reflect the changing needs of the organization, or to alter the
physical organization to improve performance.
DBA Responsibilities
• Granting of authorization for data access. By granting
different types of authorization, the database administrator can
regulate which parts of the database various users can access.
• The authorization information is kept in a special system
structure that the database system consults whenever someone
attempts to access the data in the system
DBA Responsibilities
• Routine maintenance. Examples of the database administrator’s
routine maintenance activities are:
• Periodically backing up the database, either onto tapes or onto remote
servers, to prevent loss of data in case of disasters such as flooding.
• Ensuring that enough free disk space is available for normal
operations, and upgrading disk space as required.
• Monitoring jobs running on the database and ensuring that
performance is not degraded by very expensive tasks submitted by
some users.
Database System Structure
• A database system is partitioned into modules
that deal with each of the responsibilities of
the overall system.
• The functional components of a database
system can be broadly divided into the storage
manager and the query processor components.
Storage Manager
• A storage manager is a program module that provides the interface between the
low level data stored in the database and the application programs and queries
submitted to the system.
• The storage manager is responsible for the interaction with the file manager.
• The raw data are stored on the disk using the file system, which is usually provided
by a conventional operating system.
• The storage manager translates the various DML statements into low-level file-
system commands.
• Thus, the storage manager is responsible for storing, retrieving, and updating data
in the database.
The storage manager components include
• Authorization and integrity manager, which tests for the satisfaction of
integrity constraints and checks the authority of users to access data.
• Transaction manager, which ensures that the database remains in a consistent
(correct) state despite system failures, and that concurrent transaction executions
proceed without conflicting.
• File manager, which manages the allocation of space on disk storage and the
data structures used to represent information stored on disk.
• Buffer manager, which is responsible for fetching data from disk storage into
main memory, and deciding what data to cache in main memory.
• The buffer manager is a critical part of the database system, since it enables the
database to handle data sizes that are much larger than the size of main memory
storage manager
• The storage manager implements several data structures as
part of the physical system implementation
• Data files, which store the database itself.
• Data dictionary, which stores metadata about the structure of
the database, in particular the schema of the database.
• Indices, which provide fast access to data items that hold
particular values
The Query Processor
• The query processor components include
• DDL interpreter, which interprets DDL statements and records the definitions in the
data dictionary.
• DML compiler, which translates DML statements in a query language into an
evaluation plan consisting of low-level instructions that the query evaluation engine
understands.
• A query can usually be translated into any of a number of alternative evaluation plans
that all give the same result.
• The DML compiler also performs query optimization, that is, it picks the lowest cost
evaluation plan from among the alternatives.
• Query evaluation engine, which executes low-level instructions generated by the
DML compiler.
