Database Management Systems

1
Database Management Systems

• Chapter-1

By Dinesha L Lecturer, Dept. Of CSE SSIT, Tumkur
2
Objectives

• An Overview of Database Management
• Database
• DBMS
• Database Systems
• Why Use Database
• Database Architecture
• An Example of the Three Levels
• Schema
• Data Independence
• Types Of Database Models
• Database Design Phases

3

• Definitions
• Data Known facts that can be recorded and that
  have implicit meaning
• Database Collection of related data
• Ex. the names, telephone numbers and addresses of
  all the people you know
• Database Management System A computerized
  record-keeping system

4

• Goals of a Database Management System
• To provide an efficient as well as a convenient
  environment for accessing data in a database
• Enforce information security database security,
  concurrency control, crash recovery
• It is a general purpose facility for
• Defining database
• Constructing database
• Manipulating database

5
History of DBMS

• 1960 First DBMS designed by Charles Bachman at
  GE. IBMs Information Management System (IMS)
• 1970 Codd introduced the RDBMS
• 1980 Relational model became popular and
  accepted as the main database paradigm. SQL, ANSI
  SQL, etc.
• 1980 to 1990 New data models, powerful query
  languages, etc. Popular vendors are Oracle, SQL
  Server, IBMs DB2, Informix, etc.

6

• Various types of data Images, Text, complex
  queries, Data Mining, etc.
• Enterprise Resource Planning (ERP)
• Management Resource Planning (MRP)
• Database in Web technologies
• Current Database trends
• Multimedia databases
• Interactive video
• Streaming data
• Digital Libraries

7
DBMS Functions

• Data Definition
• Data Manipulation
• Data Security and Integrity
• Data Recovery and Concurrency
• Data Dictionary
• Performance

8
Early File Systems Vs DBMS

• Catalog in DBMS. Data definition in file systems
  is part of application programs.
• Program-Data independence
• Views
• Sharing and Transaction processing

9
File System approach
Database
DBMS approach
10
Benefits of database approach

• Redundancy can be reduced
• Inconsistency can be avoided
• Data can be shared
• Standards can be enforced
• Security restrictions can be applied
• Integrity can be maintained
• Data independence can be provided
• Backup and Recovery

11
Describing and Storing Data in DBMS

• Data Model
• A data model is a collection of high-level data
  description constructs that hide many low-level
  storage details.
• Relational Data Model
• Semantic Data Model ER Model

12
Relational Model

• A Relation is a set of records and attributes.
  Also known as tuples and columns.
• A Schema is the description of data in terms of a
  data model.
• Eg. Student(RegNo String, Name String, Sem
  Integer, Branch String)

13
Student Relation

• RegNo Name Sem Branch
• 0016 N. Deepak 3 CSE
• 0674 K. Gopal 5 MECH
• 2901 Kanchana 2 CSE

14
Other Data Models

• Relational Model DB2, Oracle, Informix, Sybase,
  MS-Access, Foxbase, Paradox, etc.
• Hierarchical Model IMS DBMS
• Network Model IDS IDMS
• Object-Oriented Model ObjectStore Versant
• Object-Relational Model Products from IBM,
  Oracle, ObjectStore, Versant.

15
Three-Layer Abstraction
External Schema - 2
External Schema - 1
External Schema - 3
Conceptual Schema
Physical Schema
Disk
16
Conceptual Schema

• Describes the stored data in terms of the data
  model of the DBMS. This leads to conceptual
  database design.
• Example
• Student(RegNoInteger, NameString,
• SemInteger, BranchString)
• Faculty(FidInteger, FNameString, SalaryFloat)
• Course(CourseNoInteger, CNameString,
• CreditIntegere, DeptString)
• Section(SecIdInteger, CourseNoInteger,
• SemInteger, YearInteger, InstructorString)
• GradeReport(RegNoInteger, SecIdInteger,
• GradeChar)

17
Physical Schema

• Describes the actual storage details of the
  relations described in conceptual schema.
• Primary indexing, sequential, binary, secondary
  indexing, etc.
• This leads to the physical database design.

18
External Schema

• Describes several views of the database based on
  the database model.
• Several external schemas are possible for a
  single database.
• Each view is based upon the user requirements.
• Example
• StdGrade(RegNoInteger, NameString,
  SemInteger, GradeChar)

19
Program-Data Independence

• The data independence is the ability to change
  the schema at one level of a database system with
  out changing the schema at a higher level.
• Logical data Independence
• It is the ability to change the schema at one
  level of a database system without changing the
  external schema or application programs, is
  called as the logical data independence. With out
  changing the application programs, one can change
  the logical schema.

20

• Example
• Suppose the Faculty relation is modified as
• Faculty_Public(FidInteger, FNameString,
  OfficeInteger)
• Faculty_Private(FidInteger, SalaryFloat)
• Any view designed before this modification can
  still retrieve the data with little modification
  (relation name) and obtain the same answer.

21

• Physical data independence
• There are occasions for changing the internal
  structures for improved performance of the
  retrieval of data.
• Any change introduced to the internal schema or
  physical schema will not affect the other
  schemas.

22
Database Languages

• DDL Data Manipulation Language
• SDL Storage Definition Language
• VDL View Definition Language
• DML Data Manipulation Language
• (For data manipulations like
• insertion, deletion, update,
  retrieval, etc.)

23
Transaction Management

• Atomic operation Handling concurrent execution
  of transactions from several users.
• Example Reservation systems, Banking systems.
• Transaction failures and recovery.
• Locking protocols.
• Log (WAL Write Ahead Log)

24
DBMS Architecture
25
Web Forms
Front-End
SQL I/f
SQL Commands
Plan Executor
Parser
SQL Engine
Optimizer
Operator Evaluator
Recovery Manager
Tx Manager
Files Access
Buffer Manager
Lock Manager
DBMS
Disk Space Manager
Database
Catalog
Data File
26
People Who Work with Databases

• Database Implementers
• End Users
• Application Programmers
• DBA

27
End Users

• Casual users
• These are people who use the database
  occasionally.
• Naive users
• These are users who constantly querying and
  updating the database.
• Eg. Reservation Clerks of Airline, Railway,
  Hotel, etc.
• Clerks at receiving station of Courier service,
  Insurance agencies, etc.
• Sophisticated Users
• People who use for their complex requirements.
• Eg. Engineers, Scientists, Business analysts
• Standalone Users
• Who maintain database for personal use.

28
DBA

• Managing resources
• Creation of user accounts
• Providing security and authorization
• Managing poor system response time
• System Recovery
• Tuning the Database

29

• End of Chapter - 1