An Overview of Database Management

1
An Overview of Database Management
2
Outline

• Introduction
• What is a Database System?
• Why Database?
• Relational Systems, and Others

3
Introduction
4
Database System

• Computerized record-keeping system
• A repository or container for a collection of
  computerized data files
• A system that stores information and allows users
  to retrieve and update that information on demand
• Supports operations
• Add or delete files to/from the database
• Insert, retrieve, remove, or change data (record)
  in a file/database
• Components
• Data, hardware, software, users

5
The Wine Cellar Database (file CELLAR)
6
Retrieval Example
7
Insert, Delete and Change Examples
8
Notes

• All the SELECT, INSERT, DELETE, and UPDATE
  requests (statements, commands, or operators) are
  all expressed in SQL (Originally stood for
  Structured Query Language)
• UPDATE means change
• Computerized files such as CELLAR are called
  tables (relations)
• Rows records (tuples)
• Columns fields (attributes)

9
Notes (Cont.)

• Columns can contain data of arbitrary complexity
• Photograph, text, map, audio recording
• Column BIN constitutes the primary key for table
  CELLAR
• No two CELLAR rows contain the same BIN value
• Use double underlining to indicate primary key
  columns

10
What is a Database System?
11
Simplified Picture of a Database System
Data, Hardware, Software, and Users
12
Data

• May support single or many users
• The data in the database will be both integrated
  and shared
• Integrated the database can be thought of a
  unification of several otherwise distinct files,
  with any redundancy among those files partly or
  wholly eliminated
• Shared different users can have access to the
  same data, possibly even at the same time
• Different users will require different views

No need to store the department of the staff
attending a course, because
13
Hardware

• The secondary storage volumes (typically disk)
  are used to store data
• Direct access to subset portions
• Rapid I/O
• The hardware processor(s) and associated main
  memory are used to support the execution of the
  database system software

14
Software

• All requests for access to the database are
  handled by
• Database management system (DBMS)
• Database manager
• Database server
• Shielding of database users from hardware-level
  details
• DBMS provides users with a perception of the
  database that is elevated somewhat above the
  hardware level, and it supports user operations
  that are expressed in terms of that higher-level
  perception

15
Database Management System

• DBMS handles all access to the database
• The overall purpose of DBMS is to provide the
  user interface to the database system
• Scenario
• A user issues an access request, using some
  particular DSL (typically SQL)
• The DBMS accepts that requests and analyzes it
• The DBMS executes the necessary operations on the
  stored DB

16
Functions of DBMS

• Data definition DDL processor / compiler
• Data manipulation DML processor / compiler
• Handle planned (scheduled) and unplanned (ad hoc)
  queries
• DBA will probably have tuned the physical DB
  design in such a way as to guarantee good
  performance for planned requests
• Optimization and execution
• Optimizer determine an efficient way of
  implementing the request
• The optimized requests are then executed under
  the control of the run-time manager
• Data security and integrity
• Performance

17
Functions of DBMS (Cont.)

• Data recovery and concurrency
• Transaction manager or TP monitor must enforce
  certain recovery and concurrency controls
• Data dictionary or catalog
• Contains definitions of other objects in the
  system
• All of the various schemas and mappings and all
  of the various security and integrity constraints
  will be kept in the dictionary
• In addition, which program use which parts of the
  DB, which users require which reports, and so on
• Data dictionary can be regarded as a DB in its
  own right
• It is possible to query the dictionary like any
  other DB

18
Major DBMS Functions and Components
19
Utilities

• Programs designed to help the DBA with various
  administration tasks
• Utility examples
• Load create the initial database from regular
  data files
• Unload/reload (or dump/restore) unload the
  database or portions to backup storage and to
  reload data from such backup copies
• Reorganization rearrange the data in the stored
  database for various reasons (usually having to
  do with performance)
• Statistical compute various performance
  statistics such as file sizes, value
  distributions, I/O counts, and so on
• Analysis analyze the statistic just mentioned

20
Users

• Application programmers
• End users
• Database Administrators

21
Data Administrator (DA)

• The one who has the central responsibility for
  the data
• The one who understand the enterprise data and
  the needs of the enterprise with respect to that
  data, at a senior management level
• Jobs
• Decide what data should be stored in the DB in
  the first place
• Establish policies for maintaining and dealing
  with that data once it has been stored
• Example who can perform what operations on what
  data in what circumstances
• DA is a manager, not a technician

22
Database Administrator (DBA)

• The technical person responsible for implementing
  the DAs decisions
• Jobs
• Create the actual database
• Put in place the technical controls needed to
  enforce the various policy decisions made by the
  DA
• Ensure that the system operates with adequate
  performance
• Provide a variety of other technical services
• Liaising with users
• Defining security and integrity constraints
• Defining dump/restore schemas unload/reload
  utilities
• Monitoring performance and responding to changing
  requirements

23
Why Database?
24
Advantages over Traditional, Paper-based Methods
of Record-Keeping

• Compactness
• Speed
• Less drudgery
• Currency
• Protection
• In a multi-user environment, the database system
  provides the enterprise with centralized control
  of its data

25
Benefits of Database Approach

• Shared data
• Reduced redundancy (in a controlled manner)
• Reduced inconsistent data (to some extent)
• Transaction support
• A transaction is a logical unit of work typically
  involving several database operations in
  particular, several update operations
• Guaranteed to be atomic (all or nothing), even if
  the system fails in the middle of the transaction
  in question

26
Benefits of Database Approach (Cont.)

• Support for data integrity by integrity
  constraints
• Ensuring (as far as possible) that the data in
  the database is correct
• Inconsistency is an example of lack of integrity
• An employee might be shown as having worked 400
  hours in a week
• Security enforcement by security constraints
• Conflicting requirements can be met
• Provide an overall service that is best for the
  enterprise
• Support for standards
• Data coding, data naming, documentation
  standards
• Good for data interchange
• Data independence

27
Relational Systems and Others
28
Relational Systems

• Most important innovation in database history
• Based on logic and mathematics
• Relation is basically just a mathematical term
  for a table
• A loose definition of relational system
• Data is perceived as tables, and nothing but
  tables
• Operators derive new tables from existing tables

29
Data Structure and Operators in a Relational
System (Example)
Derived from existing tables
30
Not Relational Systems

• Hierarchic
• Network
• Inverted List
• Object
• Object/Relational
• Multi-dimensional

Users can see other data structures (such as
pointer), except (or in addition to) tables
31
Database Technology Trends