Database architecture, SCHEMAS AND SCHEMA OBJECTS

Database Design

1. M. Ashad Baloch 1
Three Level ANSI-SPARC
Architecture
Themodelprovidesthebasisfor
understandingsomeofthefunctionalityof
aDBMS.
Provideguidelineforvendersofdatabase
managementsystem.
In1975AmericanNationalStandards
Institute(ANSI)StandardsPlanningand
Requirement Committee (SPARC)
recognizedtheneedforthreelevel
architecture.

2. •The design of a DBMS depends on its
architecture. It can be centralized or
decentralized or hierarchical.
•ThearchitectureofaDBMScanbeseenas
eithersingletierormulti-tier.
•Ann-tierarchitecturedividesthewholesystem
intorelatedbutindependentnmodules,which
canbeindependentlymodified,altered,
changed,orreplaced
M. Ashad Baloch 2

3. M. Ashad Baloch 3
Three Level ANSI-SPARC
Architecture-I
Three level architecture
consist of:
1)External Level
2)Conceptual Level
3)Internal Level

4. M. Ashad Baloch 4
External Level
Theusersviewofthedatabase.Thislevel
describesthatpartofthedatabasethatis
relevanttoeachuser.
Theexternallevelconsistsofanumberof
differentexternalviewsofthedatabase.
Differentviewsmayhavedifferent
representationofsamedata.

5. M. Ashad Baloch 5
External Level-II
For example, one user may view dates in
the form (day, month, year), while another
may view dates as (year, month, day).
Some views might include derived or
calculated data. (Example DOB Age)

6. M. Ashad Baloch 6
Conceptual Level
Themiddlelevelinthethreelevel
architectureistheconceptuallevel.
Thisleveldescribeswhatdataisstoredin
thedatabaseandtherelationshipsamong
thedata.
Theconceptuallevelprovidesboththe
mappingandthedesiredindependence
betweentheexternalandinternallevels.

7. M. Ashad Baloch 7
Conceptual Level-I
The conceptual level represents:
1.All entities, their attributes and their
relationships.
2.The constraints on the data.
3.Security and integrity information.

8. M. Ashad Baloch 8
Conceptual Level-II
Theconceptuallevelsupportseachexternal
view,inthatanydataavailabletoauser
mustbecontainedinorderivablefromthe
conceptuallevel.
Howeverthislevelmustnotcontainany
storage-dependentdetails.

9. M. Ashad Baloch 9
Conceptual Level-II
•control how the data must be stored in the
database.
•five Logical structures:
table spaces
segments
extents
data blocks
schema objects

10. M. Ashad Baloch 10
External and conceptual layers

11. M. Ashad Baloch 11
tablespaces
•A database is divided into logical storage units
called Tablespaces.
•Oracle stores data logically in tablespace and
physically in datafiles
•logical construct for arranging different types
of data
•An Oracle database must have at least a
system tablespace.
•It is recommended to have different
tablespaces for user and system data

12. M. Ashad Baloch 12
SCHEMAS AND SCHEMA
OBJECTS
•Collection of database objects
•Tables
•Views
•Sequences
•Indexes
•Procedures
•Functions
•Packages
•Triggers

13. M. Ashad Baloch 13
DATA BLOCKS
•The smallest unit of Input/Output used by Oracle
database.
•Or smallest unit of storage in oracle
•The size of data block for any database is fixed
at the time of creation of the database;
•Some values of the data block size are 2KB,
8KB, 16KB, and 32KB.
•Oracle recommends a size of 8KB

14. M. Ashad Baloch 14
EXTENTS
•The next level of data storage.
•One extent consists of a specific number of
data blocks
•An extents consist of one or more database
blocks
•One or more extents in turn make up a
segment.
•When the existing space in a segment is
completely used, Oracle allocates a new
extent for the segment.

15. M. Ashad Baloch 15
segment
•A segment consists of a set of extents
•Each table’s data is stored in its own single
segment.
•Each index’s data is stored in a single segment.
•More extents are automatically allocated by
Oracle to a segment if its existing extents
become full.
•The different types of segments are the data
segments, index segments, rollback segments,
and temporary segments

16. M. Ashad Baloch 16
Internal Level
Thephysicalrepresentationofthedatabase
onthecomputer.Thisleveldescribeshow
thedataisstoredinthedatabase.
Itcoversthedatastructureandfile
organizationsusedtostoredataonstorage
devices.

17. 17
Internal Level-I
The internal level is concerned:
•Storage space allocation for data and
indexes.
•Record descriptions for storage (with stored
sizes for data items).
•Record placement.
•Data compression and data encryption
techniques.

18. M. Ashad Baloch 18
Representation of data at different levels of data base
Architecture and at the physical level at bottom

19. M. Ashad Baloch 19
Database Schemas
Theoveralldescriptionofthedatabaseis
calledthedatabaseschema.
•Adatabase schemais the skeleton
structure that represents the logical view
of the entiredatabase. It defines how the
data is organized and how the relations
among them are associated. It formulates
all the constraints that are to be applied on
the data.

20. Theschemaisspecifiedduringthe
databasedesignprocessandisnot
expectedtochangefrequently.
Therearethreedifferenttypesofschemain
thedatabase.Thesearedefinedaccording
tothreelevelofarchitecture.
20

21. M. Ashad Baloch 21
External Schema
Atthehighestlevelwehavemultiple
externalschemas(alsocalledsubschemas)
thatcorrespondtodifferentviewsofdata.

22. M. Ashad Baloch 22
Conceptual Schema
At the conceptual level we have conceptual
schema, which describe all the entities,
attributes and relationships together with
integrity constraints.

23. M. Ashad Baloch 23
Internal Schema
Atlowestlevelwehaveinternalschema
whichisthecompletedescriptionofinternal
model.
Containingthedefinitionofstoredrecord,
themethodsofrepresentation,thedata
fieldsandindexes.
Thereisonlyoneconceptualschemaand
oneinternalschemaperdatabase.

24. M. Ashad Baloch 24
Mappings
Mappingistheprocessofconvertingonelevel
toanotherlevel.
Inthisprocessthedataatonelevelisrelatedto
dataatanotherlevel.
TheDBMSisresponsibleformappingbetween
thesethreetypesofschema.
Itmustalsocheckschemasforconsistency.
Eachexternalschemaisderivablefromthe
conceptualschemaanditmustusethe
informationintheconceptualschematomap
betweeneachexternalschemaandtheinternal
schema

25. M. Ashad Baloch 25
Mappings-I
The conceptualschema
relatedtotheinternalschema
throughaconceptual/internal
mapping.
Externalschemaisrelatedto
theconceptualschemabythe
external/conceptualmapping.

26. Naeem Aslam 26

27. Naeem Aslam 27

28. 28
Three Level Architecture Objectives
•Eachusershouldbeabletoaccessthesame
databuthaveadifferentcustomizeviewofthe
data.
•Usershouldnothavetodealdirectlywithphysical
databasestoragedetail.
•TheDBAshouldbeabletochangethedatabase
storagestructurewithoutaffectingtheusersviews.

29. M. Ashad Baloch 29
Three Level Architecture Objectives
•Theinternalstructureofthedatabase
shouldbeunaffectedbychangestothe
physicalaspectsofstorage.
•TheDBAshouldbeabletochangethe
conceptualstructureofthedatabasewithout
affectingallusers.

30. M. Ashad Baloch 30
Database Instance
Thedatainthedatabaseatanyparticularpointin
timeiscalledadatabaseinstance.
Actualdataindatabasemaychangefrequently.
Thereforemanydatabaseinstancescorrespondto
thesamedatabaseschema.
Theschemaissometimescalledtheintensionof
thedatabasewhileaninstanceiscalledand
extensionorstateofthedatabase.

31. M. Ashad Baloch 31
Oracle Database Instance
•Whendatabaseisstartedondatabaseserver
–OracleallocatesamemoryareacalledsystemGlobal
Area(SGA)andstartoneormoreprocesses.
–ThiscombinationoftheSGAandtheORACLE
ProcessesiscalledanOracleinstance.
•EveryrunningOracledatabaseisassociatedwithat
leastoneOracledatabaseinstance.Becausean
instanceexistsinmemoryandadatabaseexistsondisk,
aninstancecanexistwithoutadatabaseandadatabase
canexistwithoutaninstance.

32. M. Ashad Baloch 32
Single instance vs Multiple
Instance
•Asingleinstance(setofprocesses)can
mountatmostonedatabase(setoffiles).
Ifyouneedtoaccessmultipledatabases,
youwillneedmultipleinstances.

33. M. Ashad Baloch 33
Oracle Memory Structures
•Oracle uses memory to store various information:
•program code being executed
•information about a connected session, even if it is not
currently active
•information needed during program execution (for example,
the current state of a query from which rows are being fetched)
•information that is shared and communicated among Oracle
processes (for example, locking information)
•cached data that is also permanently stored on peripheral
memory (for example, data blocks and redo log entries)

34. M. Ashad Baloch 34
Oracle Memory Structures
•The basic memory structures associated with Oracle include:
•Software Code Areas
•System Global Area (SGA)
•the database buffer cache
•the redo log buffer
•the shared pool
•Program Global Areas (PGA):
•the stack areas
•the data areas
•Sort Areas

35. M. Ashad Baloch 35
System Global Area
•Asystemglobalarea(SGA)isagroupofsharedmemory
structuresthatcontaindataandcontrolinformationforoneOracle
databaseinstance.Ifmultipleusersareconcurrentlyconnectedto
thesameinstance,thedataintheinstance'sSGAis"shared"
amongtheusers.Consequently,theSGAissometimesreferredto
asthe"sharedglobalarea“.
•The SGA contains the following data structures:
•the database buffer cache
•the redo log buffer
•the shared pool
•the data dictionary cache
•other miscellaneous information

36. M. Ashad Baloch 36
Database Buffer Cache
•ThedatabasebuffercacheistheportionoftheSGAthat
holdscopiesofdatablocksreadfromdatafiles.

37. M. Ashad Baloch 37
Redo log buffer
•Theredo log bufferis a circular buffer in the SGA that holds
information about changes made to the database. This information
is stored inredo entries. Redo entries contain the information
necessary to reconstruct, or redo, changes made to the database by
INSERT, UPDATE, DELETE, CREATE, ALTER, or DROP
operations. Redo entries are used for database recovery, if
necessary.
•Redo entries are copied by Oracle server processes from the user's
memory space to the redo log buffer in the SGA. The redo entries
take up continuous, sequential space in the buffer. The
background process LGWR(Log writer) writes the redo log buffer
to the active online redo log file (or group of files) on disk.

38. M. Ashad Baloch 38
Shared Pool
•The shared pool portion of the SGA contains three major
areas: library cache, dictionary cache, and control
structures.
•The total size of the shared pool is determined by the
initialization parameter SHARED_POOL_SIZE. The
default value of this parameter is 3,500,000 bytes.
Increasing the value of this parameter increases the
amount of memory reserved for the shared pool, and
therefore increases the space reserved for shared SQL
areas.

39. M. Ashad Baloch 39
Library Cache
•The library cache includes the shared SQL areas, private
SQL areas, PL/SQL procedures and packages, and
control structures such as locks and library cache
handles.
•Dictionary Cache: The data dictionary is a collection of
database tables and views containing reference
information about the database, its structures, and its
users. Oracle accesses the data dictionary frequently
during the parsing of SQL statements.

40. M. Ashad Baloch 40
Program Global Area
•A program global area (PGA) is a memory region containing data
and control information for a single process (server or
background). Consequently, a PGA is sometimes called a
"process global area."
•A PGA is nonshared memory area to which a process can write.
One PGA is allocated for each server process; the PGA is
exclusive to that server process and is read and written only by
Oracle code acting on behalf of that process.
•A PGA is allocated by Oracle when a user connects to an Oracle
database and a session is created, though this varies by operating
system and configuration

41. M. Ashad Baloch 41
Program Global Area
•Stack Space: A PGA always contains astack space,
which is memory allocated to hold a session's variables,
arrays, and other information.
•Sort Areas: Sorting requires space in memory.
Portions of memory in which Oracle sorts data are
calledsort areas. A sort area exists in the memory of an
Oracle user process that requests a sort.

42. M. Ashad Baloch 42
Software Code Areas
•Software code areasare portions of memory used to
store code that is being executed or may be executed.
Oracle code is stored in a software area that is typically
at a different location from users' programs -a more
exclusive or protected location.

43. M. Ashad Baloch
43
Process Structure
•Aprocessis a "thread of control" or a mechanism in an operating
system that can execute a series of steps. Some operating systems
use the termsjobortask. A process normally has its own private
memory area in which it runs. The process structure of Oracle is
important because it defines how multiple activities can occur and
how they are accomplished.
•For example, two goals of a process structure might be to simulate a
private environment for multiple processes to work simultaneously,
as though each process has its own private environment to allow
multiple processes to share computer resources, which each process
needs, but no process needs for long periods of time
•The Oracle's process architecture is designed to maximize
performance.

44. M. Ashad Baloch 44
Data Independence
Amajorobjectiveforthree-levelarchitecture
istoprovidedataindependence.
Whichmeansthatupperlevelsare
unaffectedbychangestolowerlevels.
Twokindsofdataindependence:
1)LogicalDataIndependence
2)PhysicalDataIndependence

45. M. Ashad Baloch 45
Logical Data Independence
Logicaldataindependencereferstotheimmunity
(protection)oftheexternalschemastochangesin
theconceptualschema.
Changestotheconceptualschemasuchasthe
additionorremovalofnewentities,attributesor
relationshipsshouldbepossiblewithouthavingto
changeexistingexternalschemasorhaving
rewritetheapplicationprograms.

46. M. Ashad Baloch 46
Physical Data Independence
Physicaldataindependencereferstothe
immunityoftheconceptualschemato
changesintheinternalschema.
Changestotheinternalschemasuchas
usingdifferentfileorganizationsorstorage
structures,usingdifferentstoragedevices,
modifyingindexesorhashingalgorithms
shouldbepossiblewithouthavingtochange
theconceptualorexternalschema.

47. 47
Database Languages
Adatasublanguageconsistsoftwoparts:
1)DataDefinitionLanguage(DDL)
2)DataManipulationLanguage(DML)
Theselanguagesarecalleddata
sublanguagesbecausetheydonotinclude
constructs(conditionaloriterative
statements)forallcomputingneeds,which
areprovidedbyHLL.

48. M. Ashad Baloch 48
The levels where the Conceptual and Physical data independence are effective

49. M. Ashad Baloch 49
Database Languages-I
ManyDBMShaveafacilityforembedding
thesublanguageinahighlevel
programminglanguagesuchasCOBOL,
Fortran,PASCAL,C,C++,Java,Visual
Basic.
Inthiscasehighlevellanguagerefertoas
thehostlanguage.

50. M. Ashad Baloch 50
The Data Definition Language
(DDL)
A language that allows the DBA or user to
describe and name the:
Entities
Attributes
Relationships
Integrity and Security Constraints.

51. M. Ashad Baloch 51
The Data Manipulation Language
(DML)
Data manipulation operations usually
include the following:
•Insertion of new data into the database
•Modification of data stored in the database
•Retrieval of data contained in the database
•Deletion of data from the database

52. 52
DML Types
Two Types of DML:
1)Procedural DML
2)Non Procedural DML
Procedurallanguagespecifyhowtheoutputof
DMLstatementmustbeobtained.
Whilethenon-proceduralDMLsdescribeonly
whatoutputtobeobtained.
Procedurallanguagetreatrecordsindividually,
whilenon-procedurallanguagesoperateonsetof
records.

53. M. Ashad Baloch 53
Functions of DBMS
1) Data Storage, retrieval and update
ADBMSmustfurnishuserswiththeability
tostore,retrieveandupdatedatainthe
database.
2)Auseraccessiblecatalog
ADBMSmustfurnishacataloginwhich
descriptionsofdataitemsarestoredand
whichisaccessibletousers.

54. M. Ashad Baloch 54
Functions of DBMS-I
3) Transaction Support
ADBMSmustfurnishamechanismwhichwill
ensurethatalltheupdatescorrespondingtoa
giventransactionaremadeorthatnoneofthemis
made.
4)Concurrencycontrolservices
ADBMSmustfurnishamechanismtoensurethat
thedatabaseisupdatecorrectlywhenmultiple
usersareupdatingthedatabaseconcurrently.

55. M. Ashad Baloch 55
Functions of DBMS-II
5) Recovery Services
ADBMSmustfurnishamechanismfor
recoveringthedatabaseintheeventthatthe
databaseisdamagedinanyway.
6) Authorization Services
ADBMSmustfurnishamechanismto
ensurethatonlyauthorizeduserscan
accessthedatabase.

56. M. Ashad Baloch 56
Functions of DBMS-III
7)Supportfordatacommunication
ADBMSmustbecapableofintegratingwith
communicationsoftware.
8)Integrityservices
ADBMSmustfurnishameanstoensure
thatboththedatainthedatabaseand
changestothedatafollowcertainrules.

57. M. Ashad Baloch 57
Functions of DBMS-IV
9) Services to support data independence
A DBMS must include facilities to support
the independence of programs from the
actual structure of the database.

58. M. Ashad Baloch 58
Functions of DBMS-V
10) Utility Services
ADBMSshouldprovideasetofutility
services.
•Import and Export facilities
•Monitoring database facilities
•Examine database performance

59. M. Ashad Baloch 59
DBMS Environments
SingleUserDatabaseEnvironment
Thedatabaseenvironmentwhichsupports
onlyoneuseraccessingthedatabaseata
specifictime.
TheDBMSmighthaveanumberofusers
butatacertaintimeonlyoneusercanlog
intothedatabasesystemanduseit.
ThistypeofDBMSsystemsarealsocalled
DesktopDatabasesystems.

60. M. Ashad Baloch 60
DBMS Environments-I
Multi-UserDatabasesystems
TheDBMSwhichcansupportanumberof
userssimultaneouslyinteractingwiththe
databaseindifferentways.Anumberof
environmentsexistforsuchDBMS.
–Teleprocessing
–FileServers
–Client-Server

61. M. Ashad Baloch 61
Teleprocessing
ThistypeofMultiuserdatabasesystems
processestheuserrequestsatacentral
computer,allrequestsarecarriedtothe
centralcomputerwherethedatabaseis
residing,transactionsarecarriedoutandthe
resultstransportedbacktotheterminals
(literallydumbterminals).Ithasbecome
obsoletenow.

62. M. Ashad Baloch
62
File Servers
•Thistypeofmulti-userdatabaseenvironmentassumesanother
approachforsharingofdatafordifferentusers.
•Afileserverisusedtomaintainaconnectionbetweenthe
usersofthedatabasesystem.
•EachclientofthenetworkrunsitsowncopyoftheDBMSand
thedatabaseresidesonthefileserver.
•Nowwheneverauserneedsdatafromthefileserveritmakes
arequestthewholefilecontainingtherequireddatawas
senttotheclient.
•Atthisstageitisimportanttoseethattheuserhasrequested
oneortworecordsfromthedatabasebuttheserversendsa
completefile,whichmightcontainhundredsofrecords.
•Nowiftheclientaftermakingthedesiredoperationonthe
desireddatawantstowritebackthedataonthedatabasehe
willhavetosendthewholefilebacktotheserver,thus
causingalotofnetworkoverhead.
•TheGoodthingaboutthisapproachisthattheserverdoesnot
havelotsofactionstodo.

63. M. Ashad Baloch 63
Client-Server
Thistypeofmulti-userenvironmentisthebest
implementationofthenetworkandDBMSenvironments.
IthasaDBMSservermachinewhichrunstheDBMSand
tothismachineareconnectedtheclientshaving
applicationprogramsrunningforeachuser.
Onceauserswantstoperformacertainoperationondata
inthedatabaseitsendsitsrequeststotheDBMSthrough
itsmachine’sapplicationsoftware;therequestis
forwardedtotheDBMSserverwhichperformsthe
requiredoperationondatainthedatabasestoredinthe
computerandthenpassesbacktheresulttotheuser
intendingtheresult.
Thisenvironmentisbestsuitedforlargeenterprises
wherebulkofdataisprocessedandrequestsarevery
muchfrequent.