DBMS Data Base Management Systems

1
DBMS Data Base Management Systems

• Module CS6003
• Lecturer Roberto Rossi
• Feb - 2008

2
DBMS features

• There are 3 basic features for DBMSs
• A DBMS is a software system able to manage large
  amount of data
• Handling large amount of data means facing
  efficiency issues
• Information persistence and sharing are
  guaranteed respectively by means of fault
  tolerance strategies, access control strategies
  and concurrency control strategies
• Several other features are available in terms of
  effectiveness of data handling, in order to
  simplify data descriptions, application
  development, DB administration etc.

3
Why not a simple drive and an operating system?

• In order to manage large amount of data in a
  persistent and shared way it could be possible in
  principle to use a file system (that is a normal
  disk) but this has several drawbacks
• There are no additional services available
• Sharing procedures are limited
• Many users cannot modify at the same time
  different parts of the same file. If the file is
  large this constitutes a bottleneck
• Access to shared files requires a description of
  these and this may lead to inconsistencies and
  errors
• On the other hand managing data by means of a
  file system may bring efficiency since handling a
  file system requires much less computational
  effort than handling a DBMS

4
Why sharing data?

• Different departments of a big company and/or
  different parts of a complex application
  tipically share many data. Therefore sharing data
  is a good way to avoid repetitions, i.e. waste of
  resources (storage devices)
• Furthermore redundancy can produce inconsistency
  in the different copies. For this reason a
  propagation of the changes is needed every time
  one of the many copies is modified. This is a
  source of waste in terms of CPU time and Network
  bandwith
• Example the department A that handles order in a
  large company stores its data in a file that is
  not shared with other depts. Every time an order
  is issued the data are transmitted to the
  department B that handles the respective
  shipment. When the shipment is completed the
  respective data are sent back to the dept. A.

new order
Orders
Shipments
shipment completed
5
Data model

• From users viewpoint a DB is a collection of
  data that models a certain portion of the reality
• The logic abstraction used to present data to the
  user defines a data model, more precisely
• A data model is a collection of concepts used to
  describe data, their associations and the
  constraints that these data have to satisfy
• Very important are then the possible strategies
  that can be used to structure data
• Ex. There are data models where data are
  described as trees (Hierarchical model),
  graphs (Network model) and complex objects
  (Object model)
• http//en.wikipedia.org/wiki/List_of_database_mode
  ls

6
Example

• Hierarchical model

• Network model

Todt
Ferrari
Ferrari
Todt
Shumacher
Shumacher
Barrichello
Barrichello
7
Example

• Relational model

Tables are a convenient way of representing
relations
8
Schemas and Instances

• There are two components in every DB
• SCHEMA describes the data structure
• INSTANCE describes the actual data
• A SCHEMA is needed to interpret data in the
  instance
• Typically an instance changes frequently while a
  schema is usually not modified once defined. In
  order to change a schema we may need to
  restructure the whole DB

SCHEMA
9
Logic and Phisic independence

• Two main objecting of DBMS are to provide
• Physical data independence
• The organization of phisical data on the system
  depends upon several considerations linked to the
  specific hardware and operating system in use.
  Reorganizing data on the disk should not affect
  applications that use the DBMS
• Logical data independence
• Although a logic scheme is defined for the data
  in the DB, this does not mean that all the user
  should have the same view on the data

10
Three-tier architecture of a DBMS
user
user
user
user
Extern schema
Extern schema
Logical schema
Physical schema
DB
11
Physical Level

• The Physical DB consists in a set of files,
  stored in a mass memory (disks, tapes etc.),
  containing data, indexes and so forth
• The Physical Schema describes how the Logical
  Schema is represented in the Physical DB
• i.e. in which file a particular relation is
  stored
• Managing the Physical DB is a task accomplished
  by the DB Administrator. Users can therefore
  operate at a higher level of abstraction in their
  applications

Ferrari
Shumacher
Todt
Barrichello
File c//apps/DB/data/teams
12
External Level (View Level)

• The External Level is built on the top of the
  Logic Schema by defining ad-hoc views. These
  describe part of the Logic Schema according to
  users needs.
• For instance we can define a view combining data
  of more relationships
• In many cases the distinction between external
  level and logical level is transparent to users.
  For instance users only see a set of tables in
  a RDBMS.

13
Why do we have Views?

• Views offer a personalized view of the DB
• They are also important since a modification of
  the logical schema may be sometimes masked by
  employing views
• It is possible to perform access control by
  masking reserved data
• i.e. some users are only allowed to browse
  specific views
• Views can be used to dynamically compute new data
  from those stored in the DB. Therefore they
  prevent redundancy.

28/9/2001
14
DBMS Languages

• A DBMS offers several languages to interact with
  the DB. The abstraction level of these languages
  depends on the data model we refer to
• A typical distinction (also used in SQL)
  classifies languages in terms of their function
• DDL (Data Definition Language)
• Defines schemas (logical, external, physical)
• DML (Data Manipulation Language)
• Performs queries and modifies instances within
  the DB
• DCL (Data Control Language)
• Several commands for access control, DBMS
  administration, etc
• SQL incorporates instructions from all these
  three kinds of language.
• Therefore we have SQL-DDL, SQL-DML and SQL-DCL

15
Sharing access control

• DB users are partitioned in several cathegories.
  They also have different authorization levels
• A student can read his own data, but not those of
  other students. He cannot modify the list of the
  exams he has already passed.
• A professor can read only the data of the
  students attending his courses, he cannot modify
  the list of the exams a student already passed
  but he can register exams for his own course
• The registrar office can read all the data and
  register new students
• Managing authorizations can be a complex task,
  therefore there are DB Administrators that give
  to users the right authorization levels.
• By using SQL-DCL it is easy to grant
  authorizations. The administrator can give
  authorizations to users and he can also allow
  some users to pass authorizations to other
  users.

16
Handling concurrent accesses

• A DBMS must guarantee that accesses from
  different users to a particular data do not
  interfer. In other words it has to control
  concurrency
• Ex. Consider two requests T1 and T2 trying to
  withdraw money from the same account having a
  balance of 100
• A DBMS must guarantee that these kind of
  situations do not arise. For instance by blocking
  T1 till the updated balance has been written

account
17
Persistency fault tolerance

• When an application operates several updates it
  is possible that only some of the updates are
  actually performed
• The DBMS must guarantee data integrity and it
  must therefore undo all those operations
  performed
• Ex. In a balance transfer the following fault may
  arise

100
50
Account 2
read
write
FAULT!
100
50
30
withdraw 50
T1
read
30
Account 1
18
DBMS Modules

• It is now possible to identify some foudamental
  modules of a DBMS
• QM analyzes, authorizes, optimizes and executes
  requests.
• TM coordinates requests
• LRM protects from faults
• CM handles concurrency

admin
user
Transaction Manager
DDL Compiler
Query Manager
Concurrency Manager
Logging Recovery Manager
19
Summary

• A DBMS is a software system (complex) that
  handles large amount of data. These data are
  persistent and shared. A DBMS guarantess
  integrity and security by coordinating requests,
  by protecting from faults and by realizing an
  access policy
• A data model is a collection of concepts used to
  describe data, their relationships and the
  constraints that data have to satisfy
• A database has a Schema, that describes the
  logical structure, and an instance, that is the
  actual data stored in the DB
• By means of a three-tier architecture a DBMS
  offers different levels of physical and logical
  independence of data