AUDITING DATABASE

SYSTEM
Chapter 4-Hall
 DAT
DATA MANAGE
MANAGEMEN
MENT
T
APPROACHES
Flat fle
 DAT
DATA MANAGE
MANAGEMEN
MENT
T
APPROACHES
Flat fle
 Flat fle approach
approach
• Data redundanc that contr!"ute# to
three #!$n!fcant pro"le%# !n the &at-
fle en'!ron%ent(
 – data storage,
 – data updating, and
 – currency of information
 – Tas data dependency the u#er)#


!na"!l!t to o"ta!n add!t!onal !n*or%at!on

a# h!# or her need# chan$e
 Data"a#e approach
• DBMS  a #pec!al #o*t+are ##te%


that !# pro$ra%%ed to ,no+ +h!ch

data ele%ent# each u#er !#
author!ed to acce##
• El!%!nat!on o* three .plu# one/ &at
fle)# pro"le%
 !E" E#EMENTS O$ THE
DATA%ASE EN&'RONMENT
• See f$ 401
• DBMS
• U#er#
• DBA
• 2h#!cal data"a#e
• DBMS %odel
 DBMS
• DBMS pro'!de# a controlled
en'!ron%ent to a##!#t .or pre'ent/
acce## to the data"a#e and to
e3c!entl %ana$e the data re#ource
• #o%e tp!cal *eature# !nclude(
 – 2ro$ra% de'elop%ent
 – Bac,up and reco'er
 – Data"a#e u#a$e report!n$
 – Data"a#e acce##
 DD .Data Defn!t!on
an$ua$e/
• a pro$ra%%!n$ lan$ua$e u#ed to defne
the data"a#e to the DBMS0
•  The DD !dent!fe# the na%e# and the
relat!on#h!p o* all data ele%ent#5 record#5
and fle# that con#t!tute the data"a#e0
•  Th!# defn!t!on ha# three le'el#5 called
views:
 – the ph#!cal !nternal '!e+5
 – the conceptual '!e+ .#che%a/5 and
 – the u#er '!e+ .#u"#che%a/
 Data"a#e '!e+#
Internal6ph#!cal
 u#er
• For%al acce## ( appl!cat!on !nter*ace
 – acce## !# po##!"le " the *or%al
appl!cat!on !nter*ace#
 – U#er pro$ra%#5 prepared " ##te%#
pro*e##!onal#5 #end data acce## re7ue#t#
.call#/ to the DBMS5 +h!ch 'al!date# the
re7ue#t# and retr!e'e# the data *or
proce##!n$
• In*or%al acce##   7uer lan$ua$e
Data %an!pulat!on lan$ua$e .DM/

• the propr!etar pro$ra%%!n$

lan$ua$e that a part!cular DBMS u#e#
to retr!e'e5 proce##5 and #tore data
• In#ert!n$ DM co%%and# ena"le#
#tandard pro$ra%#5 +h!ch +ere
or!$!nall +r!tten *or the &at-fle
en'!ron%ent5 to "e ea#!l con'erted
to +or, !n a data"a#e en'!ron%ent
 DBMS operat!on
80 A u#er pro$ra% #end# a re7ue#t *or data
to the DBMS0 The re7ue#t# are +r!tten !n a
#pec!al data %an!pulat!on lan$ua$e that !#
e%"edded !n the u#er pro$ra%0
90 The DBMS anale# the re7ue#t "
%atch!n$ the called data ele%ent# a$a!n#t
the u#er '!e+ and the conceptual '!e+0 I*
the data re7ue#t %atche#5 !t !# author!ed5
and proce##!n$ proceed# to Step 10 I* !t
doe# not %atch the '!e+#5 acce## !# den!ed
10 The DBMS deter%!ne# the data #tructure
para%eter# *ro% the !nternal '!e+ and
pa##e# the% to the operat!n$ ##te%5
+h!ch per*or%# the actual data retr!e'al
40 U#!n$ the appropr!ate acce## %ethod
.an operat!n$ ##te% ut!l!t pro$ra%/5
the operat!n$ ##te% !nteract# +!th the
d!#, #tora$e de'!ce to retr!e'e the data
*ro% the ph#!cal data"a#e
:0 The operat!n$ ##te% then #tore# the
data !n a %a!n %e%or "u;er area
%ana$ed " the DBMS
<0 The DBMS tran#*er# the data to the u#er)#
+or, locat!on !n %a!n %e%or0 At th!#
po!nt5 the u#er)# pro$ra% !# *ree to acce##
and %an!pulate the data
=0 >hen proce##!n$ !# co%plete5 Step# 45 :5
and < are re'er#ed to re#tore the
proce##ed data to the data"a#e
 ?uer lan$ua$e
• A query is an ad hoc access
methodology for extracting
information from a database.
• U#er# can acce## data '!a d!rect
7uer5 +h!ch re7u!re# no *or%al u#er
pro$ra%# u#!n$ the DBMS)# "u!lt-!n
7uer *ac!l!t
• S? the #tandard 7uer lan$ua$e


*or "oth %a!n*ra%e and

%!croco%puter DBMS#
 Data"a#e Ad%!n!#trator
.DBA/
• !# re#pon#!"le *or %ana$!n$ the
data"a#e re#ource0
•  The #har!n$ o* a co%%on data"a#e
" %ult!ple u#er# re7u!re#
or$an!at!on5 coord!nat!on5 rule#5 and
$u!del!ne# to protect the !nte$r!t o*
the data"a#e
  The dut!e# o* DBA
• data"a#e plann!n$@
• data"a#e de#!$n@
• Data"a#e !%ple%entat!on5 operat!on5
and %a!ntenance@ and
• data"a#e $ro+th and chan$e
 Data #tructure#
• the "r!c,# and %ortar o* the
data"a#e0
•  The data #tructure allo+# record# to
"e located5 #tored5 and retr!e'ed5
and ena"le# %o'e%ent *ro% one
record to another0
• Data #tructure# ha'e t+o
*unda%ental co%ponent#(
or$an!at!on and acce## %ethod0
 Data r$an!at!on
• the +a record# are ph#!call
arran$ed on the #econdar #tora$e
de'!ce0 Th!# %a "e e!ther sequential
or random
 Data Acce## Method#
• the techn!7ue u#ed to locate record# and to
na'!$ate throu$h the data"a#e
•  The cr!ter!a that !n&uence the #elect!on o* the data
#tructure !nclude
() Rapid *+e access and data retriea+
-) E.cient use of dis storage space
/) Hig0 t0roug0put for transaction
processing
1) Protection from data +oss
2) Ease of recoery from system fai+ure
3) Accommodation of *+e gro4t0
 DBMS Model
• A data %odel !# an a"#tract
repre#entat!on o* the data a"out
ent!t!e#5 !nclud!n$ re#ource# .a##et#/5
e'ent# .tran#act!on#/5 and a$ent#
.per#onnel or cu#to%er#5 etc0/ and
the!r relat!on#h!p# !n an or$an!at!on0
•  The purpo#e o* a data %odel !# to
repre#ent ent!t attr!"ute# !n a +a
that !# under#tanda"le to u#er#
•  Three co%%on %odel# are
 – the h!erarch!cal5
 – the net+or,5 and
 – the relat!onal %odel#
 Data"a#e Ter%!nolo$
• A data attri5ute 6or *e+d7 !# a #!n$le
!te% o* data5 #uch a# cu#to%er)# na%e5
account "alance5 or addre##
• An entity !# a data"a#e repre#entat!on o*
an !nd!'!dual re#ource5 e'ent5 or a$ent
a"out +h!ch +e choo#e to collect data
• >hen +e $roup to$ether the data
attr!"ute# that lo$!call defne an ent!t5
the *or% a record type
• A data5ase !# the #et o* record
tpe# that an or$an!at!on need# to
#upport !t# "u#!ne## proce##e#
•  Associations. Record types that
constitute a database exist in
relation to other record tpe#
 – 8(8
 – 8(M
 – M(M
 H!erarch!cal %odel
•  The h!erarch!cal %odel !# con#tructed o*
#et# that de#cr!"e the relat!on#h!p "et+een
t+o l!n,ed fle#0 Each #et conta!n# a parent
and a child
• F!le# at the #a%e le'el +!th the #a%e
parent are called siblings. This structure is
also called a tree structure.
•  The h!$he#t le'el !n the tree !# the root
segment, and the lowest le in a particular
branch !# called a leaf.
• !%!tat!on o* h!erarch!cal %odel (
 – A parent record %a ha'e one or %ore
ch!ld record#
 – No ch!ld record can ha'e %ore than one
parent
 Net+or, %odel
• !,e h!erarch!cal5 the net+or, %odel
!# a na'!$at!onal data"a#e +!th
epl!c!t l!n,a$e# "et+een record# and
fle#0
•  The d!#t!nct!on !# that the net+or,
%odel per%!t# a ch!ld record to ha'e
%ult!ple parent#0
 elat!onal %odel
•  The relat!onal %odel portra# data !n the
*or% o* t+o-d!%en#!onal ta"le#
• See f$ 4081
• Acro## the top o* the ta"le are attri5utes
6data *e+ds7 *or%!n$ colu%n#0
Inter#ect!n$ the colu%n# to *or% ro+# !n
the ta"le are tup+es)
• A tup+e !# a nor%al!ed arra o* data that
!# #!%!lar5 "ut not prec!#el e7u!'alent5 to a
record !n a &at-fle ##te%
• *our character!#t!c#(
80 All occurrence# at the !nter#ect!on o* a ro+ and
a colu%n are a #!n$le 'alue0 No %ult!ple 'alue#
.repeat!n$ $roup#/ are allo+ed0
90 The attr!"ute 'alue# !n an colu%n %u#t all "e
o* the #a%e cla##0
10 Each colu%n !n a $!'en ta"le %u#t "e un!7uel
na%ed0 Ho+e'er5 d!;erent ta"le# %a conta!n
colu%n# +!th the #a%e na%e0
40 Each ro+ !n the ta"le %u#t "e un!7ue !n at lea#t
one attr!"ute0 Th!# attr!"ute !# the pr!%ar ,e0
 DATA%ASES 'N A D'STR'%8TED
EN&'RONMENT
• 2art!t!oned
 – #pl!t# the central data"a#e !nto
#e$%ent# or part!t!on# that are
d!#tr!"uted to the!r pr!%ar u#er#
 – See f$ 408<
• epl!cated
 – there e!#t# a h!$h de$ree o* data
#har!n$ "ut no pr!%ar u#er
 – See f$ 408
 CONTRO##'NG AND A8D'T'NG
DATA MANAGEMENT S"STEMS
• t+o $eneral cate$or!e#(
 – acce## control#  to pre'ent


unauthor!ed !nd!'!dual# *ro% '!e+!n$5

retr!e'!n$5 corrupt!n$5 or de#tro!n$ the
ent!t)# data
 – "ac,up control#  en#ure that !n the


e'ent o* data lo## due to unauthor!ed

acce##5 e7u!p%ent *a!lure5 or ph#!cal
d!#a#ter the or$an!at!on can reco'er !t#
data"a#e0
 Acce## control
• Data"a#e author!at!on ta"le
• U#er-defned procedure
• Data encrpt!on
• B!o%etr!c de'!ce
• In*erence control
• Aud!t "ect!'e elat!n$ to Data"a#e
Acce##
 er!* that data"a#e acce##
author!t and pr!'!le$e# are $ranted
to u#er# !n accordance +!th the!r
le$!t!%ate need#0
 Aud!t 2rocedure# *or Te#t!n$
Data"a#e Acce## Control#
• Responsibility for Authority
Tables and Subschemas.
•  Appropriate Access Authority.
• Biometric Controls
• Inference Controls
• Encryption Controls
 Bac,up Control# !n the Flat-F!le
En'!ron%ent
• GPC Backup Techniue
• !irect Access "ile Backup
• #$%Site Stora&e