Oalaba,.

, sY•1em Concepts and Archilec\ure ~

~
!111·1 i 11 11

ollthfc systL•ms, \\'hl•n• till' whole DBMS software package was one lightly inte - Ca tegories of Da~a M~deh
m to the rnndL•rn f)BMS packages that ,ire modular in des ign, with client/server llr.4
1
•n:llltrdur-P. This l'\'OllltHlll mirrors the trends Ul
c_0111pullng, where large centralized ll1ai~;lt11i pata models can be categorized m multiple ways
compuh'rs ,in• 1w 111 g rl'pl,1u•d by hund1 eels or d1s t11buted works tation s and personal coin "lli High Level/Conceptual Data Models - provide conce ts c\o .
1· e concepts such as entities att 'b P se to the way users perceive the
1 nnrH•rtl•d \'i, 1 com11111nic1t1011 networks LO various typ es of server mach111 es such as Web Put~,
data. usOO de\ ' n utes, relationships. Obiect-based models Such as ER
database servers,file servers, application servers, and so 011 . ....,....,.._.._....,.....,....,_ _sei-~er1, model, mo .
con ceptua l data model s use concepts s uch as En n·
1
A .
:!.:! Dntn Model ies, ttnbutes and Relationsh ips:
• Entity - represents a real world obiect or concept.
n,1t,1 Model 1s a method used to defin e and analyze data requirements needed to support the bus·
0 • Attribute . represents property of interest that describes an entity, such as name or
processes of an orga niza tion and by definin g the data s tructures and the relationships between' t
salary.
t.'lcments. d.~
• Relation~hips - among two or more entities, represents an association among two or
A data model is a picture or description which depicts how data is to be arranged to serv more ent1t1es.
s pecific purpose. The model provides the necessary means to achieve the abstraction. e,
2 . LOW Level /Physical Data Models - provide concepts that describe the details of how data is
D.it,1 Abs traction hides details of data storage that are not needed by most database users an stored in the computer. These concepts are generally meant for the specialist, and not the end
.,pplications. user. Physical data models captures aspects of database system implementation and describe
A model is a representation of reality, 'real world' objects , events and associations. It is , how data is stored in files by representing record formats (fixed/variable length) , reconl
abstraction that co ncentrates on the essential and mherent aspects of an organi za tion. A data moct, orderings and access paths (key mdexing) etc.
re presents the organization itself. It should provide th e bas ic conce pts and notati ons that will a1J 0, 3 . Re presentational/Implementation Data Models- provide concepts that may be understood
datab ase designers and end users to communicate their unders tanding of the organizational dat by the end user but not far removed from the way data is organized. Representational data
un ambiguously and accurately. models are used most frequently in commercial DBMSs. They include Relational data models,

=
The main purpose of a data model is to represent data and to make the da ta understandable, and Legacy models such as Network and Hierarchical models.

II Dt>finition: Dntn l\lodt>l

Data Model can be defined as an integrated coUection of conc~pts fo r des~ rib'.ng and ma nipulating
■f-JDifferent Types of Data Models

ldata, relationships between data, and constramts on the data m an orga 111zat10n.
There are five models of DBMS, which are distinguished based on how they represent the data
contained:
A data model comprises of three components: 1. Hierarchical Data Model 2. Network Data Mode\
• Structural Part: co nsisting of a set of rules according to whic h data bases can be constructed 3. Relatio nal Data Model 4. Object-Oriented Data Model
• l\1a nipulative Part: defining th e typ es of operation that are allowed on the data (this includes S. Object -Relational Data Model
tne ,lperations that .ire used for upd ating or retrieving data from the database a nd for changing
tl1t structure of th e data b,1se) .

• Set of Integrity Rules: Which e nsures th at the data is accurate Hierarchi cal Dat abase model is one of the oldest database models, dating from late 1950s. One of the
D.11,1 Modeling techniques are used first hierarchical databases Information Management System (IMS) was developed 1omtly by North

• le 111,111, gl t1Jt.1 .. s a reso urce (migrate, merge, data quality) America n Rockwell Company and IBM.

• LI dt s g 1.ng LClmp11ter da ta bases. The hierarchical data m odel organizes data in a tree structure. There is a hierarchy of parent and
child data segments. This model is like a structure of a tre e with the records forming rhe node
m.ikt cf mges rnto t 11e da ta model. that will automatically induce changes in the database
and fi elds fo rming the b ran ch es of th e tree. This structure implies that a record can have repeanng
l p og ,l111S. 1
information, ge nerally in the child data segments. Data in a series of records, wh ich have a set f
- ---.---1-.
~ lues atta
b Otd
~--~----.z•--- -~
ched to it. It collects all the ins tan ces O
I a specific i-ecord rogeth_er as a record ty
_
1
mode l and w ith th e inct "1d ll~- 1
1
types are the equivalent of tables in th e re la t; o na record ~ p es, th e hi e ra rchica1lta\ t ~t1
~

-
Da1a1,0 ••S
elng the equivalent of row: To create links be twe_e n tb1 e:een record types. The hie rarc:oe1~1•,• Y• l•rn Conceph and Arch,leclurw ~
Parent Child Relationships. ~hesc are a 1 : N m a pptn7 i~stance of entity. teal < Ad vantages o f Hi ernrchi cnl Mod e l
org . one fo r eac 1 . ct
an,zes data elements as tabul a 1- rows, . ct w ith oth er superio r recor s o n Which
In the hierarchical data model, records_are ltnl~: pende n t on them . A tree stru cture n-tay
dependent and also on the records, whi ch a re
~h~~
~\%1
• Simplic ity _: Data n_aturally have hierarchical relati{)nshi , --
The re~ore, 1t 1s easter to view data arranged in tree stru~t~:;::~~,e~~•· r11 dLl1t,1I 1tuattun
• Secunty : These database system can enforce va in
one-to-many relationship. s resent in the hierarc h ical tree stru ~ture hav1t I>
rd til e system. ry g degree 01 Sl!cunty te aturc 1>nhke flat
The different elements (exa mpl e: reco ) p y chi ldren e lements but a Chtld e lernen ijt,
• Data b ase Integrity : Because of its inherent parent-ch
Child relationship. A Pare n t e le m en t .can - have
h" a lman
mode l cannot represen t m a ny t o many re\atit c•1
have many p a re nt ele m e n ts. That is, ht e i arc tc n1h hi ghly promoted 111 these systems. i1d structure, <.latabasl' 1ntcgnty
0
• Efficiency: The hierarchical database mode\ ts a ve e{ _ 1
- !l!!!!!!!!!!!!!!!!!!!'!!!!~"!'!""'!!'!l!'!!!!~~~~~~~~~~~~~~~~~!l!!!!!!J
among r e cor: d~s~
contains a large number of l - N relationships (one-t;ma:cient one when the <.lat.iba e
u sers require large number of transactions using d h Y re\auunsh_ips) anu when the
Disadvantages or Hierarchical Model ' ata w ose relationships are
- hxeu -

• Implementation Complexity: Although it is simple and d . .--

to implem e nt. easy to estgn, tt 1s quite complex

• Data b ase Management Problem: If we make any changes m the database structure then~
we n eed to make changes in the entire application program that access the database. '
• Lack of Structural Independence: There is a lack of structural independence because
w h en we change the structure then 1t becomes compulsory to change the apphcat11Jn too.
Operational Anomalies: H1erarch1ca\ mode\ suffers from the insert, delete ,md upt\ate
a n omalies, also retneva\ operation 1s difficult.
- ~ = ---'---------"-----__:..:_________ - - -
0 f f-tM Network Data Model
, . ,, , . , , The Netwock mode I ceplam the hiecacchical tm With a graph thusa\\owing mo,e gen,rn\ connections
[~;::::;:
Employee !5 among the nodes. The main diffecence of the netwo,k model fmm the hie<acch,ca\ model." its ability
cm,>o,o,, to handle many to many celations. ln othec wocds, It allows a cecocd to have morn than one pacen,.

E l
~____,;~;...,;;;.~===.;=======:.....-----==:::aa:a:a.. ._____,._
Example- of· Hieracch,cal
I t
. Model
t re At the top we have a General Mana,
DBMS Is said
to-many. The to
Network model is a collection data in which records are physica\\y \inked through \inked hsts. A
b• a N •twoamong
ce\ationshlp ck DBMS if the <e\ationships among data in the database ace of type many•
many-to-many
. appeacs in the _
focm of a netwock.
1 xam >le·· Conslde,·
· a company's o,·gan.,at>0na s
1M cue u · (DGMs) Each DGM looks aftecac~• Thus the stcuctuce
. . .of a netwock database " extremely comphcated . because of th, many-to-many.
f,1 MI \J nded,1
111 1 m we h avesevecal Deputy Geneca a nagecs · > celatlons h, ps m wh,ch one cecocd can be used asa key of theent,ce database. A netwock database,,
i, -t cits and each department will have a manager and many employees. W h e n represented structured in the form of a graph that is also a data structure.
l t c pc1i • t· the GM each DGM each departme-
h" , '"Jm ,d model. thm w;!\ be sepacate mws fonep ,esen mg
t
. ' .
th
'
01h
,, , h "' n,,ge,· and e ,,ch employee. The rnw position im pH es a celatwnsh,p to
t nd th
~
Suppose a n employee woe ks foe two depactments. The stcict hiecacchical anangement ,snot poss,b\e
" cows. A In th iscase a nd the tcee becomes a moce geneca\iz,d graph - a netwock
,,,ployee be\ ongsto the d epa,·tm entthatis cl oseS above it in e liS a e depact".'ent beloni, The netwo , k model was evolved to specifically handle non-hiemchical celationshlps. A nen,o,k
th
,n rnase,· !hat ,s lnnned;ate ly above it in the list and so on as shown m e a hove hguce. stcuctuceth us allows 1'l ( one,oneJ, 1,N ( one,many], N,N ( many,manyJ celalionshipsamong eonn,
6
n
l,....l'.'."
n_
""ion, a;,,.,,i.;,,al Da<a Mode\
In netwock database tecminology, a celationship is a set Each set is made up of at least two ~p
ofcecocds, a n ownec ceco,d (equivalent to pacent in the hiemchical model) and a memh
h dh1 dt.3
r rchiL I data model organizes data in a tree structure. There is a hierarchy of parent a]~
segments. _ (similar to th e child record in the h1erarchica\ model) .
 llun1 n Rt sou re ~ 1 owner to 11II :-1 r cord
I I on IV cc !Hin 111 cmhcrs t"'"rk Mod,·I

: All the recr,rds hav!! Lo maintain using pointers thu~ the database
omc more c.ompl, x
1Anomalif'~: Large number of pointers 1~ required and hence in~ert1on, deletion
Ing record arc more complex.
or Strnctu, al lnd"p~ndence: There 1s lark of structural independrmce because
ha nge the structurl' then l~~s~~~o change the application too.
Relational Data Model
Human
Hesources al model stores da ta m the form of tables Thtsconct:pt purposed by Dr. E.F. Codd, a researcher
M n the ) ear 1960s The relational model consists of three maJor components:
The set of relations and set of domains thdt defines the way data can be represented (data
str11cture)
ntegnty rules that define the procedure to protect the data (data integrity).
The operatio ns that can be performed on data (data manipulation).

R auonJI model 1s def med Js a database that allows to group its data items into one or more
Example of Network Model ble
ep ndent tables that can be related to one another by using common fields related to each

E lt on: \t't" ork Data Mod.-1 - -- - - -- - - - -ii-~;;._R·e--la-t1-·o-n_a_l _M_o-:del (RDBMS, Relat,ona/ database management system): The data is stored m
di mensional tables (rows and columns) The data is manipulated based on the relational theory
The nen,ork ~ode! orga" zes data usmg nvo fundamen tal constructs called records and sets.
Recon1s contain fields, Sets define one-to-many relatio nships between records.
Relation Variable Attribute (Column)
{unordered}
Ad,a.ntaies of l'ict" ork llodel (T/Name~\

J
R A,
, Conceptual Simplicity: Just like hiera chical model it also simple and easy to implement ... A, /ad}ing
Value
• Capability to Handle more Relationship Types: The network model can handle one to
one(l-1) and many to r.1.my :N: NJ •elat onsh1p. Relation
• Ease to Access Data: The ctata access 1s easier than the hierarchical model. Body (Tabl,]
• Data Integrity: Since it is based on the parent child relationship, there is always a link l

l•
between the parent segment and the child segment under it.
Data Independence: The network model is better than hierarchical model in case of data
independence. Tuple (Row) {unordered}

Example of Relational Model

• The whole data is conceptually represented as an orderly arrangement of data into rows and
columns, called a relation or table.
 Database System Concepts and Architecture •
•tion in the relation there is
w/co\umn pos1 on~~
at is, atanY given ro . . -,
d suit is an entire relation, a Disadvantages of Relational Model
entire relation an re con,
rformed on an
. database model based on first M . tenance Problem: The maintenance of the relational database becom es difficult over
pe
re. management is a ·or~ . amdue to the increase in the data. Developers and programmers have to spend a lot of time
• time
tional Model for database in maintaining the database. \
C st: The relational database system is costly to set up and maintain. The initial cost of the
• siftware alone can be quite high for smaller businesses .
• Physical Storage : A relational database is con:iprised of rows and columns, which requires
lot of physical memory because each operation performed depends on separate storage
~he requirements of physical memory may increase along with the increase of data .
• Lack of Scalability :While using the relational database over mul tiple servers, its structure \
Performance Analysis changes and becomes difficult to handle, especially when the quantity of the data is large. \
Std_Name Cours Due to this, the data is not scalable on different physical storage servers. Ultimately, its \
performance is affected i.e. lack of availability of data and load time etc.
Kavitha BCA
Shwetha Complexity in Structure : Relational databases can only store data in tabular form which
makes it difficult to repr~sentcomplex relationships between objects. This is an issue because
5003 Shruthi
First class many applications require more than one table to store all the necessary data required by
BBM ------ ~
their application logic.
First class
5003 BCA
ff fii ubject-Oriented Data Model

"th "key" in relational model. To represent the complex real world problems there was a need for a data model that 1s closely related
The related records are linked together w1 a to real world. Object Oriented Data Model represents the real world problems easily.
In Object-Oriented Programming. everything is an object, and many objects are quite complex,
Advantages o fRel ational Model (Relational Databases)
having different properties and methods. An object-oriented database management system works
• Simplicity: We have seen tIiat both the hierarchical and network models are conceptual in concert with an obj ect-oriented programming language to facilitate the storage and retrieval of
object-oriented data.
sm1ple, but relational model is simpler than both of tho~e tw_o. .
• Ease of Use: Users can eas1·1Y access/retrieve the required information from the datab~ In Object Oriented Data Model, data and their relationships are contained in a single structure which
· DBMS 1·s easy as tables consisting of rows and columns are qu:
· I modeI 111
The Relatlona is referred as obj ect. In this, real world problems are rep resented as objects with different attributes .
All objects have multiple relationships between them.
•1atur.il and simple to understand
• Data Integrity: Relational databases are also widely used for data int~grity as they pro~ Object-oriented databases directly deal w ith data as complete objects. All the information comes m
one instantly available object package instead of multiple tables
consistency across all tables. The data integrity ensures the features like accuracy and e~
ofi..se A object-oriented data model consists of the follo wing basic object-oriented concepts:
• Data independence: The Structure of Relational database can be changed without havi 1. Object: The real world ent ities and situations are represented as objects in the Object oriented
database model.
to chdnge any application. 2
• Structural Independence: In the Relational model, changes in the structure do not aff, · At~ribute~ and Methods: Every object has certain characteristics. These are represented
Attributes. The behaviour of the objects is represented using Methods.
the data access. 3 using
· Class: Similar attributes and methods are grouped together using a class. An obJect can be
• Design Implementation: The relational model achieves both data independence : called as an instance of the class ..
structural independence 4
· lnh~ritan ce : A new class can be derived from the original class. The denvell class <. nt
• Security :_Data is secure as Relational Database Management System allows only autbort attributes and methods of the original class as well as its own.
sers to directly access the data. No unauthorized user can access the information. These chara t . t· k b.
a . c en s 1cs ma e o Ject databases suitable for projects with complex d.it.1 \ h!Ch q
• Collaboration: Multiple users can access the database to retrieve information at tbe ,;;· n ob1ect-oriented approach to programming. An object-oriented management s st m P
t111e id even if data is being updated.
su~ported functionality catered to object-oriented programming where complex ohiects .ire
This approach unifies attributes and behaviors of data mto one entity.
 Database System Concepts and Architecture •

he form of
rl pefioitioo: Object Relational Data Model

d a class is a co lational database (ORD) is a database management system (DB MS) that's composed
les of object-oriecti°' An object;::ational database (RDBMS) and an object-oriented database (OODB MS). ORD supports
ency, transactio n~ ofborh_a ponents of any object-oriented database model in its schemas and the query language
basic com
the s obJ.ects classes and inheritance.
ed sue Ila ,
us ' . lational database may also be known as an object relational database management
n obJect-re
:ystems (ORDBMS).
easily.
ved qu1c Advantages of Object Relational Data Model
mming languages. . .
.th bJ·ect-oriented progra . etwork or relatwnal), the obiectorie
or
ks well w1 °
. . 1d tabases (such as
hierarch1ca 1, n • tu n~
f data, for examp 1e, pie res, Voice v·d
heritance: The Object Relational data model allows its users to inherit objects, tables etc \
• In that they can extend their functionality. Inherited objects contains new attributes as well
like trad1nona a f toring different types o I eo,
are capable o s so the attributes that were inherited. \
as ..
atabase mbers and so on. . . es The ability to factor out co Complex Data Types : Complex data types can be formed using existing data ~pes. ~his IS
including text, nu types to be built from ex1st1_ngt ~ su.per-class that can be share:rn~; useful in Object relational data model as complex data types allow better mampulatlon of \
new data d ~ m them m o d d
It a11 ow_
IVJ~
f eral classes an or .th . system is regar e as one of the rn . the data.
ropernes o sev tl reduce redundancy w1 m a~
ub-classes can grea Y_ . . . , Extensibility : The functionality of the system can be extended in Object relational d_ata
dvantages of object orientation. arks that have suggested that ?bJect Ormeted Datab¾ model. This can be achieved using complex data types as well as advanced concepts of obiect
h been a number ofbenchm . ovements over relatwnal DBMSs. oriented mod el such as inheritance.
e ave e significant performanc:e:1:m~p:r::==::::==::=::=:::::::::::::::::_-
~ Disadvantages of Object Relational Data Model

f Object Oriented Data Model

Disadvan tages o

, There is no universa IIYagre

,
ed data mo deI ,ora
n OODBMS and mos t models lack a theoreljr;
' "
• The ORDBMSs approach has the obvious disadvantage of complexity and associated
increased costs.
• There are proponents of the relational approach that believe the essential simplicity and
I
foundation. . database systems the use of object oriented database is sti! purity of th e relation al model are lost with these types of extension.
Jn comparison to relationa l d ot yet have the level of experi ence that we hilll
relatively limited. This means that we o n
with traditional systems.
M!►ti:i!:i¾mmJllmfm,@
• There is no standard object-oriented query language. i!IM Schema

llll('object Relational Data Model . A database is a place to store information. It can sto re the simplest data, such as a list of people as well
. . 1 d 1 • combination of a Object oriented database model and a Relatio as th e most complex data. The database stores th e information in a well-structured format In any
An ObJect relatrona mo e is a . . • 0 · d dI . data mod el, it is importa nt to di sti ngu ish between the description of the database and the database
database model. So, 1·t suppo1 .ts o b.iects, classes , inheritance etc. iust like Obiect nente mo es ~
itself. Schema is the overall description of th e database. The basic structure of how the data will be
has support for da ta types, tabular structures etc. like Relational data model. . . stored in th e database is called sch ema.
One of the major goa ls ofObject relational data model is to close the gap between relational databa,
and the object oriented practises fre quently used in many programming languages such as C++,0 Defi nition: Database Sch em a
Java etc.
Object Relatinal Data model ca n be thought of as an attempt to extend relational da tabase systi·
A database schema is the logical representation of a databast, which shows how the data is stored I
logically in the th e database. It con tains list of attributes and instruction that infom1s the databJse
with the functionality necessary to su ppor t a broader class of application domains, provide a brie engine that how th e data is orga nized and how the elements are related to each oth_e_r:_ _ _ __
between the relational and object-oriented paradigms. This approach attempts to get the bestofb::
The schema does no t phys ically contain the data itself; instead, it gives information about the ~h P
of data and how it can be related to other tables or models.
II
DatabaH Syatem Conceph •

- S c h e , n n Di11gn1m
- - J·Databa<estate d ·agram is a diagram which conl,1ins cnt1t 1es and th atlnbut
"fllllffllllt c:on1tents of the database (actual data . .
A scliem~ s~he~1 a diagram only shows us the dat,1b.1se design It du n t
.Mucaai11pfrequently nt in time is called a database state or snapshot. I schem:atabase. Schema can be a single table or it can h,1vc mon th III on
••k•,._• In database at a particular mome. . the database. In a given database t~ th
of e a represents the relations hip between tliese t.1hll• I: ch obj t n
th urrent set of occurrences (or) instances in stq~ I
The sc1em
e . current set of instances. Schellla Construct.
lllllb•:bellll8 c,onstruct has its own . d at a particular moment. The instances ca Exarnple: Imagine a case where we want to store lat:ls ..ibout employ
th collection of information store . n~ Id include their name, address, <late of birth, and s.,1,iry. In ,1 d 1t b
~ e t10ns as like addition, deletion of data. ..
rtaln opera . ain the set of individual student ent1t1es lrecords) as cou ees would be held in a single storage "cont,11nc1·", c,1lled r blc T
cmP Ioy . .
th Student con st ruct will con~. the instance of the database has SO records for i~ like a spreadsheet page, with different employees as the row • md th I t
uppose the table ha< 50 recorhs s;O records so tomorrow the insta nce have total no\ columns ... Let's call this tabl e EMP and it could look something hkc
rrow we are going to .idd anot er 10(,
s c lied an inst.1111:e Name Address Datt• of UlrU,

'""11P"1ID•,ldcle: Datab••" lnstam·•• (or) l)ntnbnst• Stnte

n the d.1t;ib.1se at ,1 particular moment in time 1s called the Database state (or) Snapshot From this information the schema would dchnc th 1t EMP
"NAME","ADDRESS","DOB","SALARY"
n d t b" is defined, the schema 1s specified to the DBMS. The database s t a t ~ 1. NAME is a string, and needs to hold at least 12 ch,11.1ct 1
5 nth empty state, with no data.
2. ADDRESS is a string, and needs to hold at least 12 chm 1cll•1
initial state of the dat,1base is when the database 1s first populated or loaded With tlit
3. DOB is a date. It should be in dd/mm/yyyy form.it
E ry ume dat,1 is added/removed/updated there 1s a new database instance.
4 . SALARY is a number. It must be greater than zero.
spons1ble for ensuring every state IS a ialid state, a state that satisfies !ht
on tr.itnts spenhcd in the schema Example: Let us suppose we have three tables Fmploycc-, D p
md ~chcnHs ,ire 1el.1ted to Jnd impact ach other through the DBMS. Dll rep rese nt the schema of these three tables usmg the c;clll m ch r
d.1t.1h 1,e inst,11Ke cnmpllc s wtth the wnstramts imposed by the database diagram, Employee and Departm,mt are rcl.ited and the EmJlloy l
I 1t 1b 1se sdwm 1
nd onstralnt-., i.:.lllcd thl' meta-
Employee
N.1me
tat rn f.xtct1sio 11 of tht lob r1tlc
l'hu1w no
S.11:iry
Dept 1d
P101cct id

P oj ct
 ► Databa se Syste m Concepts and Architecture •

The diagram below shows the logical architecture for a typical DBMS:

ex sophisticated softWare applications that Pro"i External or View Level

Database Management Systems are very comp! , ' derstand general database concepts i,
f data To better un Of . a~,
re\\ab\e management oflarge amounts O · mine the architectu re a typical datab '
the structure and capabilities of a DBMS, it is useful to exa as,
management system. the architecture ofa DBMS: The logic~/ DBMS_Architeclllr
There are two different ways to look at . / DBMS architecture. The logical architecture d 1e
, d The phys1ca . . ea1 Exte rn al / Conceptual Mapping
{Three schema Architecture,,1 an hile the physical architecture 1s concerned .
with the way data is stored and presented to users, w Will i Database
the software components that make
f\ administrator
. up a DBMS.
Th Level or Schema DBMS Architecture)
1,ogical DBMS Architect ure ( ree . . Conceptual/ Intern al Mapping ~ - - ' - - -
. d cribes how data in the database is perceived by users. It is not concerneq
The logical architecture es d b the DBMS but only with how it looks. User
WI "th how th e data is handled
d . and t processe Y
d on the underlying file' system, and can ma nipulate
. the sdare
-
sh"1elded from .the way ata h ts s·t ore
. located or how it is actually stored. Tl1is resu Its in the datab at;
without worrying about w ere 1 1s ase

..
having different levels of abstraction.

16111 FfP@WMIII■
The objective of the three level architecture is to separate each user s view of the_database from Logical DBMS Architecture
the way the database is physically represented. There are several reasons why this separation ~

desirable:
1. Each user should be able to access the same data, but have a different customized view of the 1. The External (or) View Level:
data. Each user should be able to change the way he or she views the data, and this change ~ It is the users' view of the database~his level describes that part of the database that is
relevant to each usef.""Extern al level is the one which is closest to..tha end user~is level
should not affect other users. deals with the way in which individual users view the datt.'h~dual users' are given different
2. Users should not have to deal directly with physical data base storage details, such as indexing
or hashing. In other words a user's interaction with the database, should be independent of views according to the user's requirement. \

A view involves only th ose portions of a database which are of concern to a user. Therefore
storage considerations.
3. The Database Administrator (DBA) should be able to change the database storage structures same database can have differen t views for different users. The external view insulates users
from the details of the internal and con ceptual levels. External level is also known as the view
without affecting the user's views.
level. In addition different views may have differen t representations of the same data. For
4. The internal structure of the database should be unaffected by changes to the phys ical aspects
example, one user may view dates in the fo rm (day, month, year), while another may view
of storage, such as the upgrading to a new storage device.
dates as (year, month, day).
5. The DBA should be able to change the conceptual structure of the database without affecting
all users.
2. The Conceptual Level :
It is the commun ity view of the databas~ This level describes what data is stored in the da tabase
MiM@MMi4H,ifn,l§HMMMl'■ and the relatio nships among the data. The middle level in the th ree level architecture is the
(a) External Level conceptual level. This level contains the logi cal structure of the entire database as seen by the
OBA. It is a complete view of th e data requirements of the organiza tion that is independent of
(b) Conceptual Level
any storage considerations. The conceptual level represents:
(c) Internal Level
 Database Syste m Concepts and Architecture .
~ ms

• A\\ entities, their attributes, and their relationships;

. d · the da tabase. For example · 5 ~ a pping . ..
10 DBMS must transform a re.q uest spec1f1ed on an external schema into a request a a inst the conce tu al
An Entity is an object whose information. is store
• haracten.st1c
. of m ' in tuclen1
. terest a b out
is an entity in student database. An attnb~te is a cf entity studen t. an enti~ schema and then into th e internal schema. g p
O
Ro\\ No, Name, Class, Address etc. are attributes ·
DeJinition: Mapping
Field Name The processes of transformin g requests and results between different levels of schema is called mappings
Add ress
J. Name
Class
Roll No, Jaya nagar
Naven BCA
101 Hanumanthanagar
Nandas BSc External Level External Level
102
acorn r. P.N View 1 (Library User) View 2 (Account Office User)
103 Vinaya N R Colony Requi red Values
BBA Requi red Values
104 Nandish Ro ll Nu mber Roll Number
t Nam e Name
Field of Column Address Fee
or attribute Book Num ber
Date of Issue
Date of Re tu rn
• The constraints on the data;
• Semantic information about th e data;
~/
Conceptual Leve l
• Securi ty and integrity info rmation. Field Name
Data Type
The conceptual level supports each external view, in that any data ava ilable to a user m Roll Number
Number (6)
Character (20)
be contained in, or derivable from, the concep tual lev~l. ~ owever, this. level must not cont~~ Name Character ( 40)
Address
any storage dependent details. For instance, th e descri ption of a n en t1~ should contain only Book Number
Number (SJ
Date
data types of attribu tes (for example, integer, real. character) a nd th e ir length (such as the Date of Issue
Date
Date of Return
maximum number of digits or characters), but not any storage cons id erations, such as the Number (5 + 2)
Fee
number of bytes occupied. Conceptual level is also known as the, logical level.
1
3. The Internal (or) Physical Level : Inte rna l Level
It is the physical representation of th e database on the computer. Th is level des cribes how the Stored Item Length
Ty pe = Byte(B)
data is stored in the database. The internal level is the one that concerns the way the data are Roll Number
Type = Byte (20)
physically stored on the hardwa re. The internal level covers th e p hysical\ implementation of Name
Ty pe = By te ( 40)
Add ress
the database to achieve optimal run ti me perform ance and storage s p ace utiliza tion. It coven Type = Byte (BJ
Book Number
Type = By te (BJ
the data structures and file organizations used to store data on storage devices. It interfaces Date of Issue
Ty pe = Byte (BJ
Date of Return
with the operating system access methods to place the da ta on the storage devices, build the Type = Byte (BJ
Fee

indexes, retrieve the data, and so· on. Three Level Architecture of Collage Management System
The internal level is concerned with s uch things as:
• Storage space allocation for data and indexes· Mappings between Levels
• Record descriptions for storage (with stored ~izes for da ta items) ; The DBMS is res ponsible fo r mapping b etween these three types of schema. Two mappings are
• Record placement; required in a database system w ith th ree di ffe rent views. th
• Data compression and data encryption techniques. Extern al/Conceptual Mapping: Each extern al schema is r elated to the conceptual schema by th e
1 th
There will . .be, only. one conceptua l view,
· cons1·stmg
· of the abstract represe ntation of th externa l/con ceptua l m a pping. A m apping between the external and co nce ptual views gives e
correspondence a m ong the r ecords a nd th e relatio nships of the external and conceptu al v,e,.,, e
d atat btase
th . ·.Similarly there wi.11 b eon 1Y one internal or physical view, represennn,
1 dm 1tbs ent1rely .,
e o a ata ase, as it is physically stored.
 .ch in its turn is a n a b~tra~tion of the in~
. tual view, wh1 th user or apphcation progra111 "
external view is an abstraction of the conc:pse as perceived by ; corresponding to the extern ~f\ •wrrl\ww11e:Hi- I hemas
vlew. lt describes the contents of th e data ; manipulates a recor al view to one ( or more) con a \Iii\ ,w:::c;I .
. .-••- cha n e th e conceptual schema without haVJng to change th e ex~erna sc
view. Tbeuserofthe external view sees a_n l record in the extern cep~ • The abi!ityti~on ro;a ms• When data 1s add ed or removed, only the vie w de fimt~ a nd th e
There is a mapping from a P\w
rticular Jogica
. related to th e inte rnal schema b /#- or ap~h ca .\
mappings r•ee
/to be cha nged in the DBM S that s upport logical data m de2e ndence.
·
-'•
ams tha t v-,r '
record(s) in the conceptual vi · C eptual schema is t· d the actual record or cornbinatj~ U. tual sche ma und ergdes a logical reorganization, a pphca t1on progr
Conceptual/lntema
I Mapping· one
. Th . enables th e
DBMS to m h M
d . conceptual sc ema. apping be ... q
on ~ • If th e concepexternal schem a constructs must work as before. ,
0

1/' •we'l -flrt--~." ~ e independence ind icates that the conceptual sc h e m a ca n b e c hanged without
.Jt. Log1ca-·1·-d a·'ta ...--,....___
t nal mappmg. is l ·cal recor m h tu I • ~

conceptua in ~r e that constitute a ogi ethod of derivi ng t e co ncep a record &

records in physical storag J I Is specifies the m o~ f~ f1 g the existi ng external-Schemas.
the conceptual and the interna e_v_e=="""""""""""""""""""""""""""""""""""""""'"""'='=='="=='=='=';;;,;;,;....., 0 .n d endence ,s~antageous in the database env1ronme nt s ince it allows for changes
the physical database.
Data Jndcpcndcncc
f-!}P'-a '7 ef
of the data base without affecting otl;ier jevels. Logical data independence is rpo ~e
es of DBMS. The three-schema architecture PtoVid at
di fficu to ac h1·eve than Phvsical
o_ne l teve 'J _ _
data jJJdependence. Since application programs are heavi y
. one of the main advantag r-levels are unaffected by changes to lo ~ dependent on the logical ~tructure of the data they access.
Data indepen1:°t~ei~sdependence, which mea~s tha_t u~~:chieve true data independence.
the concept o a h ·tecture makK1e:s~1t~e:_:a:s1:er:_:_:_ _ _ _ _ _ _ _ _ __ __
levels. The three schemas arc-_1- - - - B
IV~
~ Definition: Logical D ata Ind ependence . .
1
I
- >acityty tt.Q_l:ho ange the conceptual schema without having to change in external schema or a pphca tlon
Definition: Data Independence . . . It is the cap~ •1
• .
The ability to modify a schema de~nitl,£!1 m one Ieve
level is called •v~ ence ·
ere are two kinds of data independence
1without affecting schema defi nition m the next higher schema.

Mi•M&+MSNMW94◄
A DBMS supports a variety of users a nd mus t provide a ppropr ia te la n guages and interfaces for each
.
-~ =='

1. Physical Data independence category of users. .

z. Logical Data independence
_
1. DDL (Data Definition Language): used (by th e OBA a n d/or database designers) to s p ecify the
conceptual schema.
IMPMHdiffiMllr■ ► Example: CREATE, DROP, TRUNCATE a nd ALTER statements.
• The ability to change the internal schema without haVJng to change the co n c e p ~ i
• SDL (Storage Definition Language): used for specifying the inte rnal sche m a
.,. called Physical data indep ~ . By extension, the external schema snowd not change i
well. • VDL (View Definition Language): u sed for specifying the external schemas (i.e.,
user views)
• Physical file reorga nizatio n to improve performance (such as creating access~structures)
results in a change to the internal schema. If the sam)! data as before remains in the databast
z. DML (Data Manipulation Language): used for p erforming operations s uch as retrieval and
upd a te upon the populated da ta b ase.
the conceptual schema sho uld not chang:e.
► Example: SELECT, DELETE, INSE RT and UPDATE statements.
• The physical data independence allows changes in the physical storage devices or organizatior
of the files to be made without requiring changes in the conceptual view ( or) any of the extern. 3. TCL (Transaction Con trol Language): It is used to manage different transactions occurring
wi thin a database.
views and hence in the application programs using the database. Thus the files may migrau
from one ty~e of physical media to another of the file structure may change without any nee/ ► Example: COMMIT RO LLBACK and SAVEPOINT statements.
for changes m the application programs. 4. DCL (Data Control Language): It is used to create roles, permissions, and referential mtegrity
as w ell it is us ed to contro l access to database by securing it.
Example, providing an access path to improve retrieval speed of section records by seme 5I~
and year should not require a q b h . . ► Example: GRANT and REVO KE statements.
· the access path.
bYu al·1zmg uery to e c anged, although it should become more efficier.
The above description represents so me kind of ideal. In real-life, at least so far, the de fac to stand 1rd
Definition. Ph)sical Data independence
DBMS language is SQL (Sta ndard Query Language), which has constructs to s uppo rt th efun;r/;;;-5
needed by DDL, VDL, and DML languages. (Early versions of SQL had fea tu r es in suppon °
~ th:.> capacity •o change the Internal Schema With functions, but no mo re.)
out having to change conceptual schema.
 • htabaM Manaa-ment SystNt,
Databa ,e Sy1tem Conceph and Architecture ~

lbhihMiSblihiriNfii@ii■
\ hem I storu1 In a D.at ~ [ >1oie;
'[)\)\. define the logical <.d1cmJ {rel.1t10ns , IC\\ s ,•tr) Ju d <tor ig, ( d lntcm,,I fr.w•I th
ti ronrcplu 11 111 • t [J d.ilJ ~lorcd in a d,llab.15<!, D(Lst.iti,ments executed based on the type of action
In n\3MS where a clear ~epa1a\10111, 111J111t a 111t•d t,, t,, '11 H The 1)01 1s uscd Lo
, 111 f s1 lw111a 011 · 111 tr Grant lo allrm pc•e1hcd user~ lo perform specified tasks and revoke to cancel
ne(mition Language {DOI..) 1s u,l·d to specif)' th, ninrcf cf , ,nrn,11 ,Iv he used hy dat b n
Tl sc ron1111dn s 11 I111 a
and destro~ database-, and datal.JJ, c ohiccts 11' d 1 isr 11 rojtet Another language
administrator< durmi; the s tup and rcmm a
I 11h ,s,•< of J J 1 i i.
h ,clit a 'Inc mappmgs hnwecn
;;,;1wa
d 10 spec1f1 t t 1n1e, 1111 • 111
storage definition languai;c (SOL), 1' u~e gps Foi 111.1c thrcc-schcma architect nsaction Control (TCLJ stater mt Jre used to manage the changes made by DML statements.
two schema, ma} be spec1h-d e,t c
h r onr ofthrse Ia!lgu,1 u -rra ws statements to be grouped together rnto logical transactions. A transaction is a logical unit
111

\\e would need a third lani;ua!? 1 e

h I1 e11 definition I.ingJag
r (VDL) to specify user views an d ,L
·
DBMS the ODL is used Lo def me both conceptuaf a
"' 1 I
:rk
a All changes made to the database can be referred to as a transaction. Transaction changes
mappings to the concepti;al ~chema. but 111 mo,t of be made permanent to the database only if they are committed a transaction begins with an
ca:UtableSQL statement and ends explicitly with either role back or commit statement. SQL provides
~chemas -~-----~-- --- ----...._
:mmands hke COMMIT, SAVEPOINT, ROLLBACK, SET TRANCTION (discussed detail in Chapter6).
l'hal 1< DDL! ::---...
the DBA and bl database designer to define schemas. The DBMS will have a DDL comp,1e What i-TCL?
ncnon < 10 process OOL statements m order 10 identify descriptions or the schema constructs and
b
Statements \,hich are used to manage the changes made by DML statements are called Transaction Control
schema dem• don "the DBMS catalog-Je.
Language It 1s used to manage transactions In a database. It allows statements to be grouped together into

i 111 1infftSiMM' ;.;;m■

I g,cal transactions.

ML Jre other set of commands in databases that lets you work with the real data items_ that Ye
are concerned with. SQL commands such as SELECT. UPDATE, INSERT are used to retrieve dat;
&n=%iffi@H+W
Interfaces are the programs which convert system language to user understandable language and
from daubase. modify data items (ordo some correction or update) and insert new records insidt user language to system understandable language. So its work likes a trans lator which provides an

=
da tabase respectively. By this J-OU can see DML are related with the data and not with structure Of environmen• in which a user can easily operate the system.

ii!4MifiMf MS·ii·i·P@i#l5nm#rtll
u \\'hat i, O\fL!

OML stmds for Data ,\fanipulation Language. Once database is created or defined, and the database is
1. Menu-based interfaces for web clients or browsing
2. Forms-based interfaces
populated with data, user must ha\e some means to manipulate the database by using DML. (OR) 3. GUl's
DML 1s a language that enable users to access or manipulate data, as organized by the appropriate data model 4. Natural Language Interfaces
Data model involves retrieval of data from the database, Insertion of new data into the database, deletion or
modification of existing data. 5. Interfaces for parametric users
6. Interfaces for the DBA
M@ii•MR·ili&idi·i¾fl 1. Menu-Based Interfaces for Web Clients ( or) Browsing
Database stores huge amount of data and data are most important and sensitive to any organization. • These interfaces present the user with lists of options (called menus) that lead the user
Nobody likes to grant whole access to everything inside database. For example an employee in Admin through the formation of a request.
department has nothing to do with records related to Finance of the organization. So, it would be • The main advantage of using menus is that they removes the tension of remembering
st
wise to re rict th e employee to use only the required tables and data. For this pu r pose database specific commands and syntax of any qu ery language.
provides a set of command th t b '
.. s a can e used to create new user accounts for the database, assign • The query is basically composed step by step by collecting or picking options from a
;;:;~o::~::d:~y1v1blegesd. Eachhuser i~ i_dentified through his/her user ID and is allowed to perform menu that is shown by the system. Pull-down menus are a very popular techniq ue in Web
ase on w at privileges are g t d h based interfaces.
GRANT, REVOKE co d . ran e to t at user. SQL provides commands like . to look through the
roman s to assign and remove user rights to access database. • They are also often used in browsing interface which allow a user
co ntents of a data base in an exploratory and unstructured manner.
 ► Databaae Sy , 1•m Conceph and Archlteclure •

2. Forms-Based Interfaces
• A lorms-hawd interl,u c displ,1ys ,1 torm to each user Users c,m fill outhall ol lhe Iii .. , terrn database system refers to an organization of co m
entnl's to lns,•rt new 11.lt.1, or they c,111 lill oul only cert,iin entries, in win c case the llR~tri Illilectioll, storage, m~nagement, and use of data within a dat i on~ nls that def me and regulate ti •
. a Jase env ironment In a ht 1 1 1view
owing f'1ve maJor ie
will rede1•m same type of d,1ta for other r,.• maintng l!ntn cs. S the ca ta, base system 1s co mposed of the foll
co component" • 1-\ > eve
1
• Tlu•se types of for ms .m.> usually dcslgn1•d or created and progra_n'.med for the Users Iha 11ardware 2. Software s.
1
ha\ l' no expertise in operating system. Many DBMSs have Cot ms specihc,1t1on langua 1l people 4. Procedures s. Data
3
which are special languages that help specify such form s. &~ uard wa rc Components
. in a Databasesystem Environment·
.
Example SQL• Forms Is ,l form based language that specifi es queries using a for Hardware 1dent1fles all the system's ph sical ·
peripherals, ne two rk components etc. y devtces. ll includes rnmi>ulPI s, rn111pulc1
designed in 1 onjunction wuh the relattonal database schema. 'Ii

3. Graphical User Interfaces (GUI'S)

• A GU I wptc.ill} di splays a schema to the user 111 d1agr.m1matic fo rm.The user then
spt·, if\ a query by mant pulaung thr d1agr,rn1. In many cases, GU ls uti lize both menus:~:

tunus.
lt Procedures
wrl lc~ ,md ~
~s 11'
-
ll.ll,1b,1sc
Atlnunls\1 ,1lur
~·upctvt,~•s t Sy tcm
J
. •l pomtin"" device such as mouse, to pick a certatn part
. Gllls. use
, Most of the dls pl,"Y~d
r------- """•••1 Mmm, """'
sd11•111.1d1.1gran1.
4. NnturJl L.mguage Interfaces
, rhe" ml<' rt,mis :1cn•pt reques t written in English or some other language ,md atteni
w untlers1.111d 1hcm. i\ Narnr.11 langu,lgt' intert.1ce has its own schema, whtch Is sunna/ 10
t Programmers litD,\l.1b.1sc
'[ Des1gnc1
-~gr
jli1c,1_g11s ILL =:.i

the d.1tab 1s,• .onteptu.il schem,1 as well ,1s a 1hctionar) of Important words.
DBMS utthttcs
• rh,• ndtUr,11 l.mguag,· 111wrf,1n' n•ter, to the words III its sclwma ,1s well as to the set
,t.md,ml 110111s 111 1d1ct1unarv 10 1n1,•rprt'I till' request.If the 1nrerpre1a11on ls success[:
tlH 1nte1 t.m gener,11e, ,1 hlgh•lt•vcl q1wn rorri'spondmg to the natur,11 language al\
~MS J l_
s1 hriuts 1t t,, tht• DUMS fur pro,essmg. 1nhenv1s1• ,1 dialogue is st.11·tl'd with the user t

• 1
' ml\ 11 pro1·1lied rnmhtlon 01 1,•qurst
m III dis llh 111t.1g, 1111h thi s 1' that tht· c 1pah11iti1•s ol this typt' uf lnterl,1ces ,lie 1101
,llTL'SS
u
DATA

t It Ill h 111\ llll'l'.

lntrrf,irr, for rar.111wtric Uw1 s
'l. . Soft wan• Cn11111011cnts h1 '·1 I)·,, t·,,b·,,sc Sysll•n r I
1 t11, u, , hJ11 sm.111 ,t't or op,•1 1t1ons ther p,•1fo1111. work Ill opt•rly, t hn·t• typ1•s ol s It : . ·. ' mv ronmcnt: lo mJk tht d It ,h
I ~ts ,,, I~ gramme~ d1 ,lgn 1111I unpl1•n11•11t ,, sp1•n,1I mt,•rf.111• lor e.trh d,tss 0 .ippltt.111011 prog1<1111s. o w,11e ,nt• nccclctl , opc1,1tmr, y t m, DBM

t (a) Opt•rating System: It m,1 11 ,1gcs ,11 111 mlw tt

1111 mds IIH lutlt d to m111im1z,, till' numlwr ol kt•Y\lt ok1 s 1Pquuid nm on lhc rnm1rntPrs F I . , c component Jilli !low
• l' .111d ctr · · · .x tmp I'S 01 opc,.nmg Y tun iftw1r

(h) DUM~ Solt ware: It m,111,1gt•~ tl11• d,1t.ih,1sc

11•s pm llegrtl romm,1111ls th.it ,,111 Ill' usL•cl only by th c) lll!MS \oltw,11 L' i11< i11dt• Or,Kil' , Att l•s , I"'1
'cor1111 mds fo, rn.,1t111g acrounts, settmg ~ystrm p,11c1mrte ( Appllcatwn l'rogr,1ms: Tlws1 .-it e u l ti
tt111' ch 111 srhrma rcorg.uuzmg till' stor,tgc stl urtuff' to lll<lll.lgl' t ht• COlllJ)lltt I l'IIVI ll
Appl1t,1t 1011 pi O 11 ,llll\ 11 rm< 1
ul tlw • pplic.ilton 1iro •1 un I
 ilill Database System Conceph and Architecture . .
onent includes all users ass
t· People comp "d ·ry oc1,1
• People In a Database System Environmen ·. ·ob function we ~a~ I ent1 five lyp ~~ . , n· Centolized DBMS Arch itectu re
~ p efin1t10 •
with the database system. On the basis o~p_nmaTY J Database Administrators, Data M es llf
Administrators, oc1,, :J . d 0B_M_S_1_
·n_w_h_ic_h_a_ll_th_e_D_B_M_S_f_un-c-ti-on_a_li_ty_,a-p-p-lic_a_
ti-on- pr-o-gr_a_
m_e-xe_c_u_tio_n_a_n_d_u_se_r_in~t-
er_fa_c_
e------,
users in a database system: System d End Users. I, rrahze . 1 •
A cen . were carried out on a smg e machine.
System Analysts and Programmers an • Data Modellers processing
• System Administrators • System Analysts and Programmers
II
-

• Database Administrators
• End Users th database system's general operations.
Terminals
I Display
Monitor
Display
Monitor
I I Display I
Monitor
L I I
( a) Sustem Administrators oversees e h nceptual design from the requirement
'J ) repare t e co ' ' ei I Network
M dellers (Architect P . f OLTP application. ~ Mainframe
(b) Data o tu al design o an SOFTWARE
model represent the concep . II ·mplements the database according t (Application Programs, DBMS, Text Ed itors,
(D BA) phys1ca Y I . d , 0 tn
b Administrator h . implementation an mamtenanc t Compilers etc.)
(c) Data ase . he DBA performs the p ys1ca1 e or i
logical design. T HARDWARE
database system. . and implement the application Proo,. (CPU, Controller, Memory, Disk, 10 Devices)
mmers design . "' <Ill\
(d} System Analysts and progra rts and procedures through which end users <Ice s ;

They crea te the input screens, repo • es1

Centralized DBMS Architecture
. I t the database. I .
and manipu a e th lication. For examp e m case of a bank
ople who use e app . fa Tty 101
(e} End Users are th e pe . ATMor online banking c1 I are end users.
- Client Server Architecture
IOyees customer using .
system, the emp ' • • p edures are the instructions and rules th
b e EnV1ronment roe .. a•
The client/ server architecture was developed to deal with computi ng environments in which a brge
4, Procedures in a .Data dasuse of the data base system . Procedures are a critical th
cornpon ·
e,,
supervise the design an . t role in a company because ey enforce H. number of PCs, workstations, file servers, printers, database servers, Web servers, e-mail server ,
p dures play an importan "~ and other software and equipment are connected via a network. The idea is to defme specialized
of the system. roce . ducted in an organization.
d b hich business is con d b I · h servers with specific fu nctionalities. For example, it is possible to connect a number of PCs or small
st,mdar s Yw . ta t basic entity in a ata ase. t 1st e collecti
b . Data are the very 1mpor n . . . ot workstations as clients to a file server that maintains the files of the client machines. Another
s. Data in the Data ase. . h material from which mformation 1s generateo
machine can be designated as a printer server by being connected to various pri nters; all pnrt
of facts stored m the database. Data is t e raw
no database can exist without database. requests by the clients are forwarded to this machine. Web servers or e-mail servers also fall Into the
specialized server category. The resources provided by specialized servers can be accessed by many
C7'ZfIDWJWW MHiif1MMMN&1MiM client machines. The client machines provide the user with the appropriate interfaces to utilize thes
servers, as well as with local processing power to run local applications.
_ . -Ccrtralized DB~IS Architecture
• The concept of client/server architecture assumes an underlying framtwork that 1:ons1s of
--.:
I C II d Architectue the mam . f
rame computers are used for processing all system f function many PCs and workstations connected via LANs and other types of computer netwo ks
n t'ntra e ' d U er Interface Programs as well as DBMS unctionalitit
1 d U,er ,pl canon Progr.1ms an s . • Client : A client in this framework is typically a user machine that provides user interface
h• 'u,,mi, · in earlier
because · da} s. most users accessed such systems via computer . terminals, whicl capabilities and local processing. When a client requires access to additional funcuonahty h
l dis•ilav cipabilitv. Therefore the Processing used
l n t prJcess an d t he1 ha1e on } ' . ' . . I d to h takeplace. as database access that does not exist at that machine, it connects to a server that prov1d th
se [Jmputer c,1 ~ter,~
. . an,:I th~' displJ,, info rmation ' is sent to display term ma s an t ese l termrnlli · needed functionality.
e lor. . cted to mainframe computers via I arious kinds of Networks. Th~re fore, al processm • Server: Aserver is a system containing both hard-ware and software that can pro 1d
s erto ~d re:,iotel) on the computer system, and only display informatton and controls we1 to the client machines, such as file access, pri nting, archiving. or database acce . It 1
he compute· to the d1spla1 terminals, which were connected to the central computeri common that client and server software usually run on separate machines
~ p ~ .:it com!"' mcat,ons net:11 orks Two main types of basic DBMSarch itectures were created on this underlymgchent
rd1 are cteclmed comr ,er~ ha1 e come 11 ith Processing Power and in turn led to tr Two Tier & Three Tier.
er '.'Chit ctu e
 I ,1;,' ~I ' - • •~ I . I • • •

Database Sy119m eo__,.. -, Atcli-.-.. .

Tier client-server architecture of
, In 2 e management system, one server
aliSarwr! . abilities and local processing. dalllbaS d with many clients.
•d ser interface cap . onnecte
• Cient - a user machine that proVJ es u . h·nes such as file access, printing, ,s c . architecture means that Database system
Senler- machine that provides services to chent mac J a~iJ
, rwo-oer e has two layers which are Client
database access hitectur
arc d Database layer.
layer an . . .
Client Application (Chent Tier)
b
n o u1 . .
Server 1
lluklessOient ~ : Database (Database Tier)
2
Client layer we have several Client
• In chines which can have the access to the
ma b se server. The client layer contains
dalll a d 1· .
er interface programs an app ,cation

1Bj us ms When DBMS access is required,

progra . bl. h .
th rogram estah ·1s es tha connection'd to)
8 Bl th
p
e DBMS (whic 1s on e server s1 e ;
· 1·s crea ted, th e cI"1ent
e the connection
once Two Tier Client/Server Architecture
program can communicate with the DBMS.
S1telj~~
~ I , The client sends a request to the server, where it then processes the request and nd ba k

---- the data. Meaning the client handles both the Presentation layer (application mterfac ) nd
Application layer (logical operations), while the server system handles the databa y ~
• Astandard called Open Database Connectivity (ODBC) provides an apphcat1on programm n
interface (API), which allows client-side programs to call the DBMS, as long a both ch nt
Definition: Client Sen·er Architecture and server machines have the necessary software installed. A related standard for th :dv
. · a computing model in which the server hosts, delivers and manages programming language, called ]DBC, has also been defined. This allows Java client pro ram to
Client server arc h1tecture 1s
access one or more DBMSs through a standard interface.
· to be consumed by the client. This type .of architecture has one
most of the resources an d services
0 more client computers connected to a central server over a network. Chen~ computers provide
• The query will be sent by the application in the client tier and It w,ll be proce ed by tte
Database server and in turn the queried information will be sent to the client Mach n
n terface to allow a computer user to request services of the server and to display the results the
• Two tier architecture provides added security to the DBMS as 111s not exposed to th end u r
directly. It also provides direct and faster communication.
- \hati IT1e
Advantages or 2:rit'r DBMS Architecture
I T er Arch, ecture in DB'-1S is the simplest architecture of Database in which the chcnt, server,
md Database Jll res1C1. on the sJme machine. A simple one tier architecture example is installing • Easy to maintain and modification is bit easy
D taha in our I ptop or desktop to practice SQL queries. But such architecture is I a1 cly used in
prodJct10
Di ~udvantnges or 2-Ticr DBMS Arrhitecluri
8111:rwo i r I entf, ner '\rrhilel'turc for DBMS
• In two tier architecture application performance will be· dcgradt> up
re, de loped by using Client-Server architecture. All work or communicatirx • Cost-ineffective
cl t dnd the en,er There 1s no intermediary layer between client and serve
---
1
Three-Tier Client ScrHr Arch,ttetur for J)H f
d1r ly don b tw en c 1cnt and server. Because of tight coupling a 2 tieril
Three Tier (3:ner) datahasc architecture de 1gn
3-tier architecture m D8\1S 1s the most popul
 Databa .. Syalem Conceph •ncl Archltectvre •

. esses, logic, data access, data storage, an

development and maintenance of functwnal proc fe r architecture has the following d
Interface is done independently as separate modules. A 3· 1 I
a.l'eri ' ' he user applications and physical database
eparate t
1. Presentation Layer (Client Tier) • fo5 DBM S characteristics
2. Application Layer (Business Tier) ~0 support
• .data independence
3. Database Layer (Data Tier) •• supporting
progra ~ multiple views
_ ___ __ of_ the
_data.
_ _ _ _ __ __ _ _ _ _ _ _ _ __ _ _ __,
1. Presentation Layer {Client
Tier): Client Ad va ntages of a-Tier DBMS Architectu re
The Presentation Layer
H. h performance, lightweight persistent objects
is the topmost layer of an
application. It provides a • ig b·t·ty _ Each tier can scale horizontally
Scala 11 .
graphical user interface • fo rmance - Because the presentation layer can cache requests, network utilization is
Application Server (OR) Web Server
for the end user to in teract • pe.r . 1•zed and the load is reduced on the application and data tiers.
rn1 n1m ,
with the DBMS. Its main • High degree of fl exibility in deployment platform and configuration \
fu nction is to communicate Improve Data Integrity
with the application layer. Database Server Im proved Security - Client has no direct access to database.
The presentation layer will \
Easy to maintain and modification is bit easy, won't affect other modules
contact Application L a y e r ' - - - - - - - - - - - - - - - - - - . _ _ .
• ln th ree tier architecture application performance is good.
which in tum processes Three Tier Client/Server Archite c ture
application programs and . . ~ Disadvantages of 3-Tier DBMS Architecture
fetches the required data from database layer and then sends this Information back to th,
client machine in th e suitable format only. · • It is complex to build and maintain.
2. Application Layer (Business Tier or Middle Tier) :
• Increased complexity of implementation and communication. It becomes difficult for this
In three-tier, the application logic or process lives in the middl e-tier, it is separated from th sort of interaction to take place due to the presence of middle layers.
data and the user interface. The Application Layer is also kn ow n as the Business logic layer
Application logic layer is the layer where the Application Programs are processed. Here !Ji.
application programs are processed in the application layer itself, w hich makes it differen
rui·@MMMI mlfJM
A distributed database is basical1y a database that is not limited to one system, it is spread over
from Two-tier Architecture where queries are processed in the data base layer. It contain different sites, i.e, on multiple computers or over a network of computers. A distributed database
all the logic and functionality that processes data received from the pres enta tion layer an system is located on various sites that don't share physical components. This may b e required when a
data base layer. lt acts as an intermediary between the presentation and da ta base layer. particular database needs to be a ccessed by various users global1y. It needs to b e managed such that
This intermediate layer or middle tier 1s also called the application server or th e Web servei for the users it looks like one single database.
dependmg on the application. Th is server plays an intermediary ro le by r un-ning applicatior
programs and storing business rules (procedures or constraints) that are used to acce• Definition: Distributed DBMS

::~e~~t~:;/::r::;::e 1
'.t
s;_iver. can also improve database security by checking a client'.
ar mg a I equest to the data-base server.
A distributed database is a database in which all storage devices are n ot attach ed to a common CPU. I
2. Database Layer (Data Tier) : Data may be stored in multiple sites separate from each other.
The distr ibuted DBMS permits the m anagement of the distributed d atab ase so that it appears as one
J'lw Database Layer ts the layer that stores dat . .
n11•thods made hy the 'lPJJlicati a wir/i th e retneval storage and executio single database to the users. Each d a tabase in the distributed system has its DBMS software.
' on Iaye1: lt conta ins meth 0d l
pe, forms the req ui red ,1Clions need Tl . s t lat connect to th e da ta base an, Distributed DBMS con sists of a single logical datab ase that is split into multiple fragments. Each
ec.I iese are Insert d
, up ate or delete. Just to name a few fragme n t is stored in one or m ore computers w h ich are un der th e control of a separate DBMS a n d
connected by a network. W h en user re quests for any data then query will be executed on multiple
local d ata bases and the results are merged proving the illusion of a single database .
 nrJ DI 11dvontugr. ■

tru:tur
, I autnni,my
1mprov, d rcliab1hty
• Ec1Jnorn1cs

g rs the p1otedure ol ,ins

ls) 111 a drstnhuted i·nviron • Cost
eve 411ery (e.g., forrnulat,,d 111 • lntegnty control more difficult
cl query 01n•r,1tors irr some v;i • Lack of experience
n 'flillr
C1_H1--'1p'-l_ex~---
• OJt,ib.isc design morl?__
I, t s whcr e cf,lla rs slon•cl ar: ha_rdwar e-indt•pr•n - u nctions of a Distrihutcd DBMS
d, n
9C..!l amL1ctlx-twe1m l:h~;e distributed databases which 1s accornpli h 1II ,d O(f.t:
• (:.xtcnded commumcat10n services. • Extended Data Dictionary.
management: D stnbuted database prov1clrs a • (Jrsmbuted query processing. • Extended concurrency control.
I d 'b d consrstcn
P 1stn ute recovery methods, and distrib t • (:xtcndcd recovery services.
e of many transaction failures. Ut~d
2.11 Classification of DBMS
The DBMS can be classified into different categories on the basis of several criteria such as the data
[RVCR C/11· model they are using, number of users they support, number of sites over which the database is
SfR
distributed and the purpose they serve.
1. Classification based on Data Model
• Relational data model
• Hierarchical data model
• Network data model
• Example: Star • Object-Oriented data model
. their own DBMS.
• Can have many s·t, cs, eac h with • Object -Relational data model
fJB
2. Classification based on Number ofUsers
~-/ • Local data stored at each site.
• Single User Systems· single-user system the database resides on one computer and is
• Sites connected by communications network.
/_/~ Q • To a user (appl',ca rron program) all sites together
only accessed by one user at a time.
• Multi User Systems - multiple users can access the database simultaneously. ln multi-
user DBMS, the data is both integrated and shared. For example, the Online Book database
~
appear as one big DB.
is a multi-user database system in which the data of books, authors, and publishers are
• Needs simple st ructures -I relational DBs
stored centrally and can be accessed by many users.
 Databa" System Concepts and Architectvre •
3. Classlftcation based on Number of Sites
, Centralized _ data is stored at si ngle site. dover many sites connected by an object-oriented data mo~el. A_ object - oriented database is a collection of ob1ects whose
MS software store 11 et,,, behavior, state and the relationships are called object-oriented data model.
, Distributes - database and DB multiple sites. ~~
BMS software at d . 4-, Explain Object Relational Data Model?
, Homogeneous - use same D d for data entry an retrieva l. Jt is
(OLTP) - use f; t . llstd An object-relational database (ORD) is a database management system (DBMS) that's
0 n11ne Transaction Processing . .
b king airlines,
supermarkets, manu ac unng, etc. ,
,4ns. composed of both a relational database (RDBMS) and an object-oriented database (OODBMSJ.
various industries, such as an '
ORD supports the basic components of any object-oriented database model in its schemas and
4. Classification based on Cost • High-end systems, the query language used, such as objects, classes and inheritance.
• Low-end systems S. What is schema and database instance?
S. Classification based on Purpose • Special purpose ,\JIS- Schema: A database schema is the logical representation of a database, which shows how the
• General purpose It can be designed for specific purposes su h data is stored logically in the the database. It contains list of attributes and instruction that
DBMS is a general purpose so
Ftware system. h r .
cannot be used for ot er app 1cat1ons With
c ., informs the database engine that how the data is organized and how the elements are related
. n
airline or railway reservaoo · Such systems o, to each other.
major changes. -.......__, Database Instance: The data in the database at a particular moment in time is called the
Database state (or) Snapshot (or) instance.
@ifoi4¥M·M·~- 6 _ What is mapping and data independence?

MMW\Mii&/H·41:l·BHH■ Ans. Mapping: The processes of transforming requests and results between different levels is
called mappings.
. Data Model? And name different type of data models.
1. What is d fl d an integrated collection of concepts for describing ah,! Data independence: The ability to modify a schema definition in one level without affecting
Ans. Data Model can be ~ neh _as b tween data and constraints on the data in an organizati . schema definition in the next higher level is called "Data Independence".
manipulating data, relaoons 1ps e ' . . . . 01
Types of data mo des.I . Th ere. are five models of DBMS, which are d1stmgu1shed based on ho,, 7 _ What is DDL and DML?
they represent the data con tamed: Ans. DDL: DDL is used by the DBA and by database designer to define both schemas. The DBMS
, Relational data model will have a DDL compiler, whose function is to process DDL statements in order to identify
, Hierarchical data model descriptions of the schema constructs and to store the schema description in the DBMS
• Network data model catalogue.
, Object-Oriented data model DML: DML stands for Data Manipulation Language. Once database is created or defined, and
• Object -Relational data model the database is populated with data, user must have some means to manipulate the database
by using DML.
2. What is Relational and Network Data Model?
(OR)
Ans. Relational Data Model: A Relational model data base is defin ed as a database that allows to
grfJup its data irem: intfJ fJne or more independent ta bles that can be related to one another~ DML is a language that enable users to access or manipulate data, as organized by the
u~ing u,mmon field•, mlated to each table. appropriate data model. Data model involves retrieval of data from the database, Insertion of
Network Data Model: Netwrirr. model organizes data using two fundamental constructs new data into the database, deletion of modification of existing data.
ullr•d rr:r.r,rd•, and •,r:t•,. J<w,rd\ contain field•,, Sets define on e-to-many relationships between 8 Define DCL and TCL.
rr•cr,rd\.
Ans. DCL: DCL is used to control access to data stored in a database. DCL statements executed based
.1. Define Hierarchical and Object Oriented Data Model?
on the type of action to be gra nted (or) revoked. Grant to allow specified users to perform
An~. ffleranhic~I fJata Mod1:I: '/ h<; h1r:rarchital data model orga nizes data in a tree structure specified tasks and revoke to cancel previously granted permission .
Thut ,, a h11,rnrchy r,f pan,nt and thild data se:gments.
TCL: Statements which are used to manage the changes made by DML statements are called
Object Oril:nt1:d Data Model· A J· t· d l. .
(• ',J a+rnr, Transaction Control Language. Used to manage transactions in a database. It allows statements
l.!nt1t11- J, rtm•,tmint•. ,m thr·rn· itnd t" r: h.is a logical orga nization of the real world object.I
· rr. ;i ir,n•, 1P among objects. A data model that captures~ to be grouped together into logical transactions.
 . distri"b ted and centalized DBMS,
9 • What 1S b se in which all storage device
u is a data a s are
Distributed DBMS: A distributed database din multiple sites separate _rrom _each Othe
Ans. D ta may be store th DBMS functionality, r.
attached to a common CPU. a 10
which alI e . ' applica .
C traliz d DBMS: A centralized DBMS
en e .
program execution and us
er interface processin
S ArcWtecture.
g were carried out on a smgle machine.
Data Modeling
10. Mention the three layers of DBM
Ans. (a) External Level
(b) Conceptual Level
Using Entity-
(c) Internal Level

ll'EIE·&fo■of data models with an example. Relationship Model

1. Describe any two types d disadvantages?
2. Explain relational model with advantages an
3. Describe object oriented data model?
4 What object oriented relational model? .
· . f DBMS with a neat di agram. CONTENTS
5. Describe the three schema architecture o
6. What mapping? Explain two types of mapping. Cl8. Introduction
7. What is data independence? Explain the two types of data independence
C.Q. High Level Conceptual Data Models for Database Design
8. Differentiate between logical data independence and physical data independ e nce.
Cl8. Entity And Attributes
9. Give the classification ofDBMS.
C.Q. Entity Type, Entity Sets, Key Attributes, Value Sets

ilii#ltlfiiil§oh&,U,■ Q1. Relationship

1. Describe the different data models with advantages and disadvantages. Exp lai n one example Cl8. ER Diagram Notation
for each. Cl8. Relationship Types of Degree Higher than Tw o
Write a short note on database languages. Q1 Abstraction
3. Explain the different DBMS interfaces. Q1 Aggregation
4. Write a short note on distributed and centralized DBMS architecture. Cl8. Review Question