Data Base Management Systems

(DBMS) – (1)

Code: INF311

Lecture 2
 In this Lecture

Relational systems and others

Architecture of DBMS

Data independence

Data Models
Relational System

 The Data is perceived by the user as

tables (and nothing but tables)

 The operators available to the user are

operators that drive new tables from
old ones
Relational Database: Definitions

 Relational database: a set of relations.

 Relation: made up of 2 parts:

– Schema : specifies name of relation, plus name
and type of each column.
 E.g. Students(sid: string, name: string, login:
string, age: integer, gpa: real)

– Instance : a table, with rows and columns.

Other Systems

 Hierarchic System
– The data is represented to the user in the
form of trees
– Example, IBM’s IMS

 Network System
Database Management System
(DBMS)

 DBMS is a general purpose software

system that facilitates the process of
definition, construction, manipulating,
and sharing databases among various
users and applications.

 DBMS is the software that controls the

information
Database Management System
(DBMS)

 DBMS contains information about a

particular enterprise
– Collection of interrelated data
– Set of programs to access the data
– An environment that is both convenient
and efficient to use
ANSI/SPARC Architecture
 ANSI - American National  A three-level architecture
Standards Institute – Internal level: For systems
 SPARC - Standards designers
Planning and – Conceptual level: For
database designers and
Requirements Committee administrators
 1975 - proposed a – External level: For
framework for DBs database users
 ANSI/SPARC Architecture
User 1 User 2 User 3

External External
External Schemas
View 1 View 2

External/Conceptual Mappings
Conceptual DBA
Conceptual Schema View

Conceptual/Internal Mapping

Internal Schema Stored

Data
DBMS architecture
External View / Level / Schema
/ User View / Global View
 Application programs hide details of data types.
Views can also hide information (such as an
employee’s salary) for security purposes.

 Highest or Top level of data abstraction ( No

knowledge of DBMS S/W and H/W or physical
storage).

 This level is concerned with the user.

External View / Level / Schema
/ User View / Global View
 Each external schema describes the part of the
database that a particular user is interested in and
hides the rest of the database from user.

 There can be n number of external views for

database where n is the number of users.

 For example, an accounts department may only be

interested in the student fee details. It would not be
expected to have any interest in the personal
information about students.
Conceptual View
Logical level
 Describes data stored in database, and the
relationships among the data.
type instructor = record
ID : string;
name : string;
dept_name : string;
salary : integer;
end;

 This level is in between the user level and physical

storage view.
Conceptual View
Logical level
 There is only one conceptual view for single
database.

 It hides the details of physical storage structures

and concentrates on describing entities, data
types, relationships, user operations, and
constraints.
Internal View / Physical View

 It is the lowest level of data abstraction. (it has the

knowledge about s/w and h/w)

 At this level, it keeps the information about the

actual representation of the entire database i.e. the
actual storage of the data on the disk in the form of
records or blocks.

 It is close to the physical storage method.

Internal View / Physical View

 The internal view is the view that tells us what data

is stored in the database and how. At least the
following aspects are considered at this level:
Storage allocation, Access paths etc.

 The internal view does not deal with the physical

devices directly. Instead it views a physical device
as a collection of physical pages and allocates
space in terms of logical pages.
Three-Schema Examples
Mappings
 Mappings translate  Physical data
information from one independence
level to the next – Changes to internal level
– External/Conceptual shouldn’t affect conceptual
– Conceptual/Internal level

 These mappings provide  Logical data

data independence independence
– Conceptual level changes
shouldn’t affect external
levels
Data independence
 The three level database architecture allows a clear
separation of the information meaning (conceptual
view) from the external data representation and from
the physical data structure layout.

 A database system that is able to separate the three

different views of data is likely to be flexible and
adaptable. This flexibility and adaptability is data
independence.
Data independence

 Types

– LOGICAL DATA INDEPENDENCE

– PHYSICAL DATA INDEPENDENCE

Logical data independence

 Whenever, there is a change or modification at the

conceptual level without affecting the user level or
external level, it is known as logical data
independence.

 E.g. The name field in conceptual view is stored as

first name, middle name and last name whereas in
external view, it remains to be as a single name field.
Physical data independence

 Whenever, the changes are made at the internal

level without affecting the above layers, it is known
as physical data independence.

 E.g. The location of the database, if changed from C

drive to D drive will not affect the conceptual view or
external view as the commands are independent of
the location of the database.
 Logical Data Independence
V.
Physical Data Independence

Logical Data Independence Physical Data Independence

Concerned with the structure of the Concerned with storage of the data.
data or changing the data definition.

Very difficult as the retrieving of data Easy to retrieve.

are heavily dependent on logical
structure of data.

Application program need not be Physical database is concerned with

changed if new fields are added or the change of the storage device.
deleted from the database.

Concerned with the conceptual Concerned with the internal schema.

schema.
3-level Architecture Objectives

 All users should be able to access same data.

 A user’s view is immune to changes made in other views.

 Users should not need to know physical database storage

details.

 DBA should be able to change database storage structures

without affecting the users’ views.

 Internal structure of database should be unaffected by

changes to physical aspects of storage.

 DBA should be able to change conceptual structure of

database without affecting all users.
Data Models
 A collection of tools  Object-based data
for describing models (Object-
– Data oriented and Object-
– Data relationships relational)
– Data semantics
– Data constraints  Semistructured data
model (XML)
 Relational model
 Other older models:
 Entity-Relationship – Network model
data model – Hierarchical model
Relational DBMS Model
 Problems with early  Then, in 1970, E.
databases F. Codd wrote “A
– Navigating the records Relational Model of Data
requires complex programs for Large Shared
– There is minimal data Databanks” and
independence introduced the relational
– No theoretical foundations model
Relational DBMS Model
 Information is stored as  The relational model
tuples or records in covers 3 areas:
relations or tables – Data structure
 There is a sound – Data integrity
mathematical theory of – Data manipulation
relations  More details in the next
 Most modern DBMS are lecture…
based on the relational
model
Relational DBMS Model
 The Entity-Relationship Model
 Models an enterprise as a collection of entities and
relationships
 Entity: a “thing” or “object” in the enterprise that is distinguishable
from other objects
 Relationship: an association among several entities
 Represented diagrammatically by an entity-relationship
diagram: