DBMS
Course Objectives
To understand the basic concepts and the applications
of database systems.
To master the basics of SQL and construct queries
using SQL.
To understand the relational database design
principles.
To become familiar with the basic issues of
transaction processing and concurrency control.
To become familiar with database storage structures
and access techniques.
Course Outcomes
Demonstrate the basic elements of a relational database
management system and Ability to identify the data
models for relevant problems.
Ability to design entity relationship model and convert
entity relationship diagrams into RDBMS and formulate
SQL queries on the data.
Apply normalization for the development of application
software.
Syllabus
UNIT – I: Introduction: Database System
Applications, Purpose of Database Systems, View of
Data, Database Languages – DDL, DML, Relational
Databases, Database Design, Database Architecture,
Data Mining and Information Retrieval, Database
Users and Administrators, History of Database
Systems.
Introduction to Data base design: Database Design
and ER diagrams, Entities, Attributes and Entity sets,
Relationships and Relationship sets, Additional
features of ER Model, Conceptual Design with the ER
Model, Conceptual Design for Large enterprises.
UNIT-II
Relational Model: Introduction to the Relational
Model, Integrity Constraints over Relations,
Enforcing Integrity constraints, Querying
relational data, Logical data base Design: ER to
Relational, Introduction to Views, Destroying
/Altering Tables and Views.
Relational Algebra and Calculus:
Preliminaries, Relational Algebra, Relational
calculus – Tuple relational Calculus, Domain
relational calculus.
UNIT-III
SQL: Queries, Constraints, Triggers: Form of Basic
SQL Query, UNION, INTERSECT, and EXCEPT,
Nested Queries, Aggregate Operators, NULL values,
Natural JOINS, Complex Integrity Constraints in
SQL, Triggers and Active Data bases..
Schema Refinement and Normal Forms:
Introduction to Schema Refinement, Functional
Dependencies - Reasoning about FDs, Normal
Forms, Properties of Decompositions,
Normalization, Schema Refinement in Database
Design, Other Kinds of Dependencies.
UNIT-IV
Transaction Management: Transactions, Transaction
Concept, A Simple Transaction Model, Storage
Structure, Transaction Atomicity and Durability,
Transaction Isolation, Serializability.
Concurrency Control: Lock–Based Protocols,
Multiple Granularity, Timestamp-Based Protocols,
Validation-Based Protocols.
Recovery System-Failure Classification, Recovery
and Atomicity, Recovery Algorithm, Buffer
Management, Failure with loss of nonvolatile storage,
Remote Backup systems
UNIT-V
UNIT – V: Storage and Indexing: Overview
of Storage and Indexing: Data on External
Storage, File Organization and Indexing, Index
Data Structures, Comparison of File
Organizations.
Tree-Structured Indexing: Intuition for tree
Indexes, Indexed Sequential Access Method
(ISAM), B+ Trees: A Dynamic Index
Structure, Search, Insert, Delete.
Text Books
Title: Data Base Management systems
Author: Raghuramakrishnan
Title: Data Base System Concepts
Author: A. Silberschatz, Henry. F. Korth, S.
Sudarshan
Importance
DBMS stands for Database Management
System.
Why DBMS ?
What is DBMS?
File Processing System
In earlier times, around 1960's before the
database systems came into existence, let us
understand at how data was stored in
computers. A file processing system is a
collection of programs that store and manage files
in computer hard-disk.
The file processing system is good when there
is only a limited number of files and data is
very less. As the data and files in
the system grow, handling them becomes
difficult.
Drawbacks of File Processing
System
Data Redundancy
Data Inconsistency
Data Isolation
Dependency
Data Security
Atomicity Issues
What is DBMS?
Data
Information
Data Base
Management System
Definition of DBMS
It is a collection of inter-related data and set of
programs to store and access those data in an
easy and efficient manner.
Purpose of Database System
The main purpose of database systems is to
manage the data.
It is a collection of programs that enables the
user to create and maintain a database. In
other words, it is general-purpose software
that provides the users with the processes of
defining, constructing and manipulating the
database for various applications.
Applications:
Banking: all transactions
Airlines: reservations, schedules
Education sector
Online shopping
Computer Horoscope
Sales: customers, products, purchases
Telecom
Human resources: employee records,
salaries, tax deductions
Databases touch all aspects of our lives
Advantages of Dbms
No redundant data
Data consistency and integrity
Data Security
Privacy
Easy access to data
Easy recovery
Flexible
Disadvantages of DBMS
Increased Cost
Complexity
Performance
View of Data
Levels of Abstraction
Physical level describes how data is
actually stored.
Logical level describes what data is stored in
database, and the relationships among the data.
View level describes user interaction with
database system.
Instances and Schemas
Instance : The data stored in database at a particular
moment of time.
Schema: Design of a database is called schema.
Types of Schema
Physical schema: database design at the physical
level.
Logical schema: database design at the logical
level.
View schema: database design at the view level.
Data Independence
1. Physical Data Independence
2. Logical Data Independence
Database languages
Types of database languages
DDL(Data definiton language)
DML(Data Manipulation language)
DCL(Data control language)
TCL(Transaction control language)
Data Definition Language
It is used to define the structure of a database
and for creating tables, schemas etc in data base.
The DDL commands include:
CREATE: Create database instance.
ALTER: Alter structure of data base.
DROP: Drop the database instance.
TRUNCATE: To delete tables in database
instance.
RENAME: To rename database instance.
Data Manipulation Language
The language used to manipulate the
database like inserting data, updating table,
retrieving record from a table, etc.
SELECT: Read records from table.
INSERT: Insert records into table
UPDATE:To update data in table.
DELETE: Delete all records from the table.
Data Control Language
Grant privilege to a user using the GRANT
statement. In the same way, revoke the
privilege using the REVOKE statement. Both
of these statements come under the Data
Control Language (DCL).:
GRANT: Give privilege to access the
database.
REVOKE: Take back the privilege to access
the database.
Transaction Control Language
The changes in the database that we made using DML commands are
either performed/committed or roll backed using TCL.
To persist the changes made by DML commands in database –
COMMIT
To rollback the changes made to the database – ROLLBACK
COMMIT: Save the work permanently.
SAVEPOINT: Set a point in transaction to
rollback later.
ROLLBACK: To roll back the changes
made to the data base.
Relational Databases
A relational database organizes data into tables
which can be linked—or related—based on data
common to each.
Database Architecture
A Database Architecture is a representation of
DBMS design. It helps to design, develop,
implement, and maintain the database management
system. A DBMS architecture allows dividing the
database system into individual components that
can be independently modified, changed, replaced,
and altered.
DBMS architecture depends upon how users are
connected to the database to get their request done.
Types of DBMS Architecture
1-Tier Architecture
1 Tier Architecture in DBMS is the simplest
architecture of Database in which the client, server,
and Database all reside on the same machine.
A simple one tier architecture example would be
anytime you install a Database in your system and
access it to practice SQL queries. But such
architecture is rarely used in production.
2-Tier Architecture
The 2-Tier architecture is same as basic client-server.
In the two-tier architecture, applications on the client
end can directly communicate with the database at the
server side.
The user interfaces and application programs are run
on the client-side.
The server side is responsible to provide the
functionalities like: query processing and transaction
management.
To communicate with the DBMS, client-side
application establishes a connection with the server
side.
3-Tier Architecture
The 3-Tier architecture contains another layer between
the client and server. In this architecture, client can't
directly communicate with the server.
The application on the client-end interacts with an
application server which further communicates with
the database system.
End user has no idea about the existence of the
database beyond the application server. The database
also has no idea about any other user beyond the
application.
The 3-Tier architecture is used in case of large web
application.
Diagrammatic Representation
of 2-tier and 3-tier
Overall System Structure
Database Users
Naive Users
Application Programmers
Sophisticated Users
Specialized Users
Standalone Users
Database Administrators
Schema Definition
Storage structure on access method definition
Schema and physical organization modification
Providing the granting of authorization to access
data.
Database maintenance
Data mining and Information
Retrieval
Information Retrieval: The ability to query a
computer system to return relevant [Link]
most widely used example is the google web
search engine.
Data Mining:The ability to retrieve information
from one or more data sources in order to
combine it,cluster it,visualize it and discover
patterns in the data.
History of Database Systems
1950s and early 1960s: Magnetic tapes were
developed for data storage. Data processing tasks
such as payroll were automated, with data stored on
tapes.
Late 1960s and 1970s:
Widespread use of hard disks in the late 1960s
changed the scenario for data processing greatly,
since hard disks allowed direct access to data.
1980s:
Although academically interesting, the relational
model was not used in practice initially, because
of its perceived performance disadvantages;
relational databases could notmatch the
performance of existing network and hierarchical
databases. That changed with System R, a
groundbreaking project at IBM Research that
developed techniques for the construction of an
efficient relational database system.
Early 1990s:
The SQL language was designed primarily for decision
support applications, which are query-intensive, yet the
mainstay of databases in the1980s was transaction-processing
applications, which are update-intensive.
2000s:
The first half of the 2000s saw the emerging of XML and the
associated query language XQuery as a new database
technology. Although XML is widely used for data exchange,
as well as for storing certain complex datatypes, relational
databases still form the core of a vast majority of large-scale
database applications.
Introduction to database
design
What is database design
A database design is a blue print or a design to
store and manipulate the data.
It is the organization of data according to
database model.
The designer determines what data must be
stored and how the data elements interrelate.
Phases of database design
Requirement analysis
Conceptual database design
Logical database design
Schema refinement
Physical database design
Security design
Phases in Database Design
Data Models
Definition: Data modelling is a mean
of modelling data i.e to give a shape
to the data.
A data model makes it easier to
understand the meaning of data by its
figure.
Types of Data Models
Object based logical model: These models
are used in describing data at logical and view
level.
Record based logical model: Describes
data at logical and view level, and it stores data in
the form of records.
Physical data model: Gives description of
data at lowest level and describes about the
storage of data in low level data structures.
Types of Object based logical
model
Entity Relationship model
Object oriented model
The Semantic data model
Functional data model
Types Record based logical
model
Relational model
Network model
Hierarchical model
Types of Physical data
models
Unifying model
Frame memory model
ER MODEL
ENTITY RELATIONAL (ER) MODEL is a high-
level conceptual data model diagram.
It is a design/blue print of a database.
ER modeling helps you to analyze data
requirements systematically to produce a
well-designed database.
The Entity-Relation model represents real-
world entities and the relationship between
them.
ER DIAGRAM
ENTITY-RELATIONSHIP DIAGRAM
(ERD) displays the relationships of entity set stored in
a database.
In other words, we can say that ER diagrams help you
to explain the logical structure of databases.
ER diagrams are translatable into relational tables
which allows you to build databases quickly.
ER diagrams can be used by database designers as a
blueprint for implementing data in specific software
applications.
Components of the ER Diagram
This model is based on three basic
concepts:
Entities
Attributes
Relationships
WHAT IS AN ENTITY?
A real-world thing/object either living or non-
living thing which can be distinguished from other
objects.
An entity can be place, person, object, event or a
concept, which stores data in the database.
Examples of entities:
Person: Employee, Student, Patient
Place: Store, Building
Object: Machine, product, and Car
Event: Sale, Registration, Renewal
Concept: Account, Course
Attributes
Entities are represented by means of their
properties, called attributes. All attributes
have values. For example, a student entity
may have roll no, class, and age as attributes.
Relationship
A relationship is represented by diamond
shape in ER diagram, it shows the relationship
among entities.
Entity set
An entity set is a group of similar kind of
entities that can have similar attributes.
Types of Entity Set
Entity set can be classified into two categories
as shown below:
Strong Entity Set
Weak Entity Set
Strong Entity Set:
An entity set that has a primary key using which,
entities in the table can be uniquely identified.
This kind of entity set is termed as a strong
entity set.
Weak Entity Sets:
A weak entity set doesn’t have any primary
key which can identify each entity in a set
distinctly.
EXAMPLE
Types of Attributes
Simple attribute − Simple attributes are atomic
values, which cannot be divided further. For
example, a student's phone number is an atomic
value of 10 digits.
Composite attribute − Composite attributes are
made of more than one simple attribute. For
example, a student's complete name may have
first_name and last_name.
Derived attribute −It is one whose value is dynamic
and derived from another attribute. For example,
age can be derived from data_of_birth.
Single-value attribute − Single-value
attributes contain single value. For example −
Social_Security_Number.
Multi-value attribute − Multi-value attributes
may contain more than one values. For
example, a person can have more than one
phone number, email_address, etc.
Key attributes: Key attributes are those
attributes that can identify an entity uniquely
in an entity set.
Simple attribute
Composite attribute
Multi-valued attribute
Derived attribute
Single valued Attribute
Key Attribute
Here are the geometric shapes and their meaning
in an E-R Diagram.
Rectangle: Represents Entity sets.
Ellipses: Attributes.
Diamond: Relationship Set.
Lines: They link attributes to Entity Sets and
Entity sets to Relationship Set.
Double Ellipses: Multivalued Attributes.
Dashed Ellipses: Derived Attributes.
Double Rectangles: Weak Entity Sets.
Double Lines: Total participation of an entity
in a
relationship set.
Relationship
It is an association among two or more entities.
It is represented by diamond symbol.
Relationship set: Set of relationships of same
type.
Like entities, a relation ship can have attributes
and these attributes are called descriptive
attributes. hours
Works
employee for project
Degree of relationship
No of entities participating in a relationship is
called degree of relationship.
1. Unary relationship
2. Binary relationship
3. Ternary relationship
Cardinality constraints/
Mapping cardinalities/Relationship
constraints
Cardinality: Maximum number of relationship
instances that an entity can participate in.
Express the no. of entities to which other entity
can be related via relationship.
Types
One to One
One to many
Many to one
Many to many
One to One Relationship
When a single instance of an entity is associated
with a single instance of another entity then it is
called one to one relationship. For example, a
person has only one passport and a passport is
given to one person.
Example:
1 1
person has passport
One to Many Relationship
When a single instance of an entity is
associated with more than one instances of
another entity then it is called one to many
relationship. For example – a customer can
place many orders but a order cannot be
placed by many customers.
Example:
1 M
HAVE Mobile
Customer
numbers
Many to One Relationship
When more than one instances of an entity is
associated with a single instance of another
entity then it is called many to one relationship.
For example – many students can study in a
single college but a student cannot study in
many colleges at the same time.
Example:
M 1
student study
College
Many to Many Relationship
When more than one instances of an entity is
associated with more than one instances of
another entity then it is called many to many
relationship. For example, a can be assigned to
many projects and a project can be assigned to
many students.
Example:
M M
enroll
student ed courses
Additional features OF ER MODEL
Key Constraints
Participation Constraints
Class Hierarchies
It is a method of classifying entities into
subclasses.
Two ways of representing class hierarchies:
Specialization
Generalization
DBMS Specialization
Aggregation
In aggregation, the relation between two entities
is treated as single entity.
Conceptual design with ER Model
Should a concept be modelled as an entity or
attribute?
Should a concept be modelled as an entity or
relationship?
Should we use binary or ternary relationship?
Should we use aggregation?
Conceptual design for large
enterprises
For a large enterprise, the design may require the
efforts of more than one designer and span data
and application code used by number of user
groups.
An alternative approach is to develop separate
conceptual schemas for different user groups and
then integrate these conceptual schemas.