Transcript Introduction updated File

What is Database Administration ?
Database administration' is the
process of establishing computerized
databases,
and
insuring
their
recoverability,
integrity,
security,
availability, reliability, and performance.
Introduction to Databases
A Database is a collection of a logically
related data. And description of this data
A database Management System (DBMS)
is the software that manages and controls
access to the database.
Ex: Oracle 10g, MS Access, MySql,
SQLServer 2007, SqlLight, DB2
Examples of database systems
•
Supermarket products
Purchases, checkout bar code reader, a program accesses the database to obtain
item description and price, then updates stock balances
•
Credit card details
CC reader accesses a database to check the credit present purchases and credit
limit. Another program updates balances
•
Holiday and flight details for travel agents
Booking arrangements, Travel agent invoicing database.
•
Library database
Books’ details, index, borrowers and borrowing details
•
•
Insurance database
University admin and registration database
File Based Approach
. are ideal for small amounts
The flat-file style of database
of data that needs to be human readable or edited by
hand. Essentially all they are made up of is a set of
strings in one or more files that can be parsed to get the
information they store; great for storing simple lists and
data values, but can get complicated when you try to
replicate more complex data structures.
Limitations of the File Based Approach
• Separation and isolation of data
An example is a list of course that matches a potential major, since two files
have to be accessed: Courses and Major. A program has to be written to
create a temporary file that contains the required details
• Duplication of data
- Duplication is wasteful: time, money, and storage space
- Duplication leads to loss of data integrity and consistency
• Data dependence.
• Incompatibility of files
• Proliferation (‫ توالد‬،‫ )تكاثر‬of application programs
which keeps programmers very busy, and reduce the quality of programs
Database Approach
• A database is a shared collection of
logically related data (and a description of
this data) designed to meet the info needs
of an org. It is a large repository of data
that is a corporate shared resource.
• A database is also defined as a selfdescribing collection of integrated records;
The Database Management
System (DBMS)
• A DBMS is a software system that enables users to define, create,
and maintain the database and provides controlled access to it.
• A DBMS provides the following facilities:
1) Allow users to define a database through DDL
( Data Definition Language )
Ex:
2) Allow user to insert, update, delete, and retrieve data – DML
( Data Manipulation Language)
Ex:
3) Control access to the database, such as security, integrity,
concurrency control, recovery control, and user-accessible catalog.
concurrency control: Managing Simultaneous Operation On DB
without having Interleaved.
Ex: Locking
Components of the DBMS
Environment
•
•
•
•
•
Hardware
Software
Data
Procedures
People (Users) (Roles)
Roles in the Database
Environment
DB USERS
•
•
•
•
Database Administrators (DBA)
Database Designers
Application Programmers
End-Users( Naïve , Sophisticated)
1) Database Administrators (DBA)
Responsibilities:
1) Manage Users( Create User , Grant and Revoke Privileges)
2) Defining Security & Integrity Checks
3) Monitoring Performance
4) Defining Backup / Recovery Procedures
5) Setting System Parameters
6) Acquiring Hardware and Software
7) Define DB Procedures , Triggers , …….
2) Database Designers
Responsibilities:
1) Define DB Schema (Structure , Types , Constraints)
2) Communicate with end user and understand their
needs
3) Application Programmers
4) End-Users
They are the clients for the database to serve their information
needs.
Naïve users
They do not need to know about the database;
They access the database through simple application programs
such as data entry.
Sophisticated users
They are familiar with the structure of the database and the functionality
of the DBMS.
They use high-level query language, such as SQL.
DBA Responsibilities
Defining the Schema
The DBA defines the schema which contains the structure of
the data in the application. The DBA determines what
data needs to be present in the system ad how this data
has to be represented and organized.
Liaising with Users
The DBA needs to interact continuously with the users to
understand the data in the system and its use.
Defining Security & Integrity Checks
The DBA finds about the access restrictions to be defined
and defines security checks accordingly. Data Integrity
checks are also defined by the DBA.
DBA Responsibilities (count.)
Defining Backup / Recovery Procedures
The DBA also defines procedures for backup and recovery.
Defining backup procedures includes specifying what data is
to backed up, the periodicity of taking backups and also the
medium and storage place for the backup data.
Monitoring Performance
The DBA has to continuously monitor the performance of the
queries and take measures to optimize all the queries in the
application.
DBA Challenges
- Ever Changing Technologies
- Never ending requests
- Urgency of a client’s demands for data
- Need to maintain a secure, yet
available database environment
The History of DBMS
Hierarchical Model
Network Model
first Generation DBMS
relational model
The extended relational model
second Generation DBMS
Object Oriented DBMS and Object-Relational DBMS
These are called third Generation DBMS
Advantages of DBMSs
•
•
•
•
•
•
•
•
•
•
Control of Data Redundancy
Data Consistency
Economy of scale
Sharing of data
Improved data integrity
Improved security
Improved Backup and Recovery Services
Increased Productivity
Increased Concurrency
Improved Data Accessibility and Responsiveness
Disadvantages
•
•
•
Complexity
Size
Cost
Functions of a DBMS
Data storage, retrieval, and update
A DBMS must provide users with the ability to store, retrieve, and
update data in the database. It should hide the internal physical
implementation details from users
A user-accessible catalog
A DBMS must provide a catalog in which descriptions of data items are
stored and which is accessible to users.
Functions of a DBMS
Transaction Support
A DBMS must furnish a mechanism that will ensure that either all
updates corresponding to a given transaction are made or that none
of them is made. This is to prevent the database being in an
inconsistent state. A transaction is a series of actions, carried out by
a single user, or a program that access or changes the contents of
the database.
Concurrency Control Services
A DBMS must furnish a mechanism to ensure that the database is updated
correctly when multiple users are updating the database concurrently
Functions of a DBMS
Recovery services
A DBMS must furnish a mechanism for recovering the database in the
event that the database is damaged in any way.
Security services
A DBMS must furnish a mechanism to ensure that only authorized
users can access the database.
Support for data communication
A DBMS must be capable of integrating with communication software.
Functions of a DBMS
Integrity services
A DBMS must furnish a means to ensure that both the data in the
database and changes to the data follow a certain rules.
Services to promote data independence
A DBMS must include facilities to support the independence of programs
from the actual structure of the database.
Utility services
A DBMS should provide a set of utility services. These are tools to
help the DBA to administer the database effectively, such as import
facilities, monitor facilities, statistical analysis programs, index
reorganization facilities, and many others.
The system Catalog
It is called sometime DATA DICTIONARY
The catalog stores data about the data (metadata)
Views and their advantages
A view is a subset of the database, presented to one user
or set of users.
The benefits of views are:
•
They reduce complexity by letting users see the data
in the way they want
•
They provide security by excluding data that some
users should not see
•
They customize the appearance of the database,
such as changing attribute names
•
They hide changes in the database and provide
consistent view of the database to users. The also
provide the program-data independence mentioned
earlier