DATABASE MANAGEMENTSYSTEM 22CS43

MODULE 1
INTRODUCTION TO DATABASE
Introduction
Importance:Databasesystemshavebecomeanessentialcomponentoflifeinmodernsociety,in
thatmanyfrequentlyoccurringeventstriggertheaccessingofatleastonedatabase:bibliographic library
searches, bank transactions, hotel/airline reservations, grocery store purchases, online (Web)
purchases, etc., etc.

[Link] databases:

Theapplicationsmentionedaboveareall"traditional"onesforwhichtheuseofrigidly-structured textual
and numeric data suffices. Recent advances have led to the application of database technologyto
a wider class of data. Examples include multimedia databases (involvingpictures, video clips,
and sound messages) and geographic databases (involving maps, satellite images).

Also,databasesearchtechniques areappliedbysomeWWW search engines.

Definitions

Thetermdatabaseisoftenused,ratherloosely,torefertojustaboutanycollectionofrelateddata.
E&Nsaythat,inadditiontobeingacollectionofrelateddata,adatabasemusthavethefollowing
properties:

 It represents some aspect of the real (or animagined) world,calledthe miniworld or universe of
discourse. Changes to the miniworld are reflected in the database. Imagine, for example, a
UNIVERSITY miniworld concerned with students, courses, course sections, grades, and course
prerequisites.
 Itisalogicallycoherentcollectionofdata,towhichsomemeaningcanbeattached.(Logical
coherency requires, in part, that the database not be self-contradictory.)
 Ithasapurpose:thereisanintendedgroupofusersandsomepreconceivedapplicationsthatthe users are
interested in employing.

Tosummarize:adatabasehassomesource(i.e.,theminiworld)fromwhichdataarederived,some degree
of interaction with events in the represented miniworld (at least insofar as the data is updated
when the state of the miniworld changes), and an audience that is interested in using it.

An Aside: data vs. information vs. knowledge: Data is the representation of "facts" or
"observations" whereas information refers to the meaning thereof (according to some
interpretation). Knowledge, on the other hand, refers to the ability to use information to achieve
intended ends.

DEPTOF CSE Page4

 DATABASE MANAGEMENTSYSTEM 22CS43

Computerized vs. manual: Not surprisingly (this being a CS course), our concern will be with
computerizeddatabasesystems,asopposedtomanualones,suchasthecardcatalog-basedsystems that
were used in libraries in ancient times (i.e., before the year 2000). (Some authors wouldn't even
recognize a non-computerized collection of data as a database, but E&N do.)

Size/Complexity: Databases run the range from being small/simple (e.g., one person's recipe
database) to being huge/complex (e.g., Amazon's database that keeps track of all its products,
customers, and suppliers).

Definition:Adatabasemanagementsystem(DBMS)isacollectionofprogramsenablingusers to
create and maintain a database.

Morespecifically,aDBMSisageneralpurposesoftwaresystemfacilitatingeachofthefollowing (with
respect to a database):

 definition:specifyingdatatypes(andotherconstraintstowhichthedatamustconform)and data
organization
 construction:theprocessofstoringthedataonsomemedium(e.g.,magneticdisk)thatis
controlled by the DBMS
 manipulation:querying,updating,reportgeneration
 sharing:allowingmultipleusersandprogramstoaccessthedatabase"simultaneously"
 systemprotection:preventingdatabasefrombecomingcorruptedwhenhardwareorsoftware
failures occur
 securityprotection:preventingunauthorizedormaliciousaccesstodatabase.

Given all its responsibilities, it is not surprising that a typical DBMS is a complex piece of
software.

AdatabasetogetherwiththeDBMSsoftwareisreferredtoasadatabasesystem.(SeeFigure1.1, page 7.)

:AnExample:
[Link]!

Amongthemainideasillustratedinthisexampleisthateachfile/relation/tablehasasetofnamed
fields/attributes/columns,eachofwhichisspecifiedtobeofsomedatatype.(Inadditiontoadata type, we
might put further restrictions upon a field, e.g., GRADE_REPORT must have a value from the
set {'A', 'B', ..., 'F'}.)

The idea is that, of course, each table will be populated with data in the form of
records/tuples/rows,eachofwhichrepresentssomeentity(intheminiworld)orsomerelationship
between entities.

DEPTOF CSE Page5

 DATABASE MANAGEMENTSYSTEM 22CS43

Forexample,eachrecordintheSTUDENTtablerepresentsa—surprise!—[Link] the
COURSE and SECTION tables.

Ontheotherhand,eachrecordinGRADE_REPORTrepresentsarelationshipbetweenastudent
[Link] two
courses.

Databasemanipulationinvolvesqueryingandupdating.

Examples of (informal) queries:

 Retrievethetranscript(s)ofstudent(s)named'Smith'.
 Listthenamesofstudentswhowereenrolledinasectionofthe'Database'courseinSpring 2006, as
well as their grades in that course section.
 Listallprerequisitesofthe'Database'course.

Examplesof(informal) updates:

 ChangetheCLASSvalueof'Smith'tosophomore(i.e., 2).
 Insertarecordforasectionof'FileProcessing'forthissemester.
 Removefromtheprerequisitesofcourse'CMPS340'thecourse'CMPS 144'.

Ofcourse,aquery/updatemustbeconveyedtotheDBMSinapreciseway(viathequerylanguage of the
DBMS) in order to be processed.

As with software in general, developing a new database (or a new application for an existing
database) proceeds in phases, including requirements analysis and various levels of design
(conceptual (e.g., Entity-Relationship Modeling), logical (e.g., relational), and physical (file
structures)).

:CharacteristicsoftheDatabaseApproach:
Database approach vs. File Processing approach: Consider an organization/enterprise that is
organized as a collection of departments/offices. Each department has certain data processing
"needs",[Link],eachdepartmentwould control
a collection of relevant data files and software applications to manipulate that data.

For example, a university's Registrar's Office would maintain data (and programs) relevant to
student grades and course enrollments. The Bursar's Office would maintain data (and programs)
pertaining to fees owed by students for tuition, room and board, etc. (Most likely, the people in
these offices would not be in direct possession of their data and programs, but rather the
university'sInformationTechnologyDepartmentwouldberesponsibleforprovidingservicessuch as
data storage, report generation, and programming.)

DEPTOF CSE Page6

 DATABASE MANAGEMENTSYSTEM 22CS43

One result of this approach is, typically, data redundancy, which not only wastes storage space
but also makes it more difficult to keep changing data items consistent with one another, as a
change to one copy of a data item must be made to all of them (called duplication-of-effort).
Inconsistency results when one (or more) copies of a datum are changed but not others. (E.g., If
youchangeyouraddress,informingtheRegistrar'sOfficeshouldsufficetoensurethatyourgrades are
sent to the right place, but does not guarantee that your next bill will be, as the copy of your
address "owned" by the Bursar's Office might not have been changed.)

In the database approach, a single repository of data is maintained that is used by all the
departments in the organization. (Note that "single repository" is used in the logical sense. In
physical terms, the data may be distributed among various sites, and possibly mirrored.)

Main Characteristicsof databaseapproach:

1. Self-Description: A database systemincludes —in addition to the data stored that is of relevance
tothe organization—acomplete definition/description of thedatabase's structureandconstraints.
This meta-data (i.e., data about data) is stored in the so-called system catalog, which contains a
description of the structure of each file, the type and storage format of each field, and the various
constraints on the data (i.e., conditions that the data must satisfy).

SeeFigures1.1and1.3.

The system catalog is used not onlyby users (e.g., who need to know the names of tables
and attributes, and sometimes data type information and other things), but also by the
DBMS software, which certainlyneeds to "know" how the data is structured/organized in
order to interpret it in a manner consistent with that structure. Recall that a DBMS is
generalpurpose, as opposedtobeinga specificdatabase [Link],the structure of
the data cannot be "hard-coded" in its programs (such as is the case in typical file
processing approaches), but rather must be treated as a "parameter" in some sense.

2. InsulationbetweenProgramsandData;DataAbstraction:

Program-Data Independence: In traditional file processing, the structure of the data

filesaccessedbyanapplicationis"hard-coded"initssourcecode.(E.g.,Considerafile
descriptor in a COBOL program: it gives a detailed description of the layout of the
records in a file by describing, for each field, how many bytes it occupies.)

If,forsomereason,wedecidetochangethestructureofthedata(e.g.,byaddingthefirst two digits

to the YEAR field, in order to make the program Y2K compliant!), every application in
which a description of that file's structure is hard-coded must be changed!

In contrast, DBMS access programs, in most cases, do not require such changes, becausethe
structure of the data is described (in the system catalog) separatelyfrom the programs that access
it and those programs consult the catalog in order to ascertain the structure of the data (i.e.,
providing a means by which to determine boundaries betweenrecordsbetween fields within
records) so that they interpret that data properly See Figure 1.4.

DEPTOF CSE Page7

 DATABASE MANAGEMENTSYSTEM 22CS43

In other words, the DBMS provides a conceptual or logical view of the data to application
programs, so that the underlying implementation may be changed without the programs being
modified. (This is referred to as program-data independence.)
Also, which access paths (e.g., indexes) exist are listed in the catalog, helping the DBMS to
determine the most efficient way to search for items in response to a q uery.

Data Abstraction:

Adatamodelisusedtohidestoragedetailsandpresenttheuserswithaconceptual view of the
database.

Programsrefertothedatamodelconstructsratherthandatastoragedetails

Note:InfairnesstoCOBOL,itshouldbepointedoutthatithasaCOPY featurethatallows
differentapplicationprogramstomakeuseofthesamefiledescriptorstoredina"library". This
provides some degree of program-data independence, but not nearly as much as a good
DBMS does. End of note.

Example by which to illustrate this concept: Suppose that you are given the task of
[Link],each record
should be displayed as follows:

Record #i:
value of first field
valueofsecondfield
...
...
valueoflastfield

To keep things very simple, suppose that the file in question has fixed-length records of 57 bytes
with six fixed-length fields of lengths 12, 4, 17, 2, 15, and 7 bytes, respectively, all of which are
ASCII strings. Developing such a program would not be difficult. However, the obvious solution
wouldbetailoredspecificallyforafilehavingtheparticularstructuredescribedhereandwouldbe of no
use for a file with a different structure.

Now suppose that the problem is generalized to say that the program you are to develop
must be able to display any file having fixed-length records with fixed-length fields that
are ASCII strings. Impossible, you say? Well, yes, unless the program has the ability to
access a description of the file's structure (i.e., lengths of its records and the fields

DEPTOF CSE Page8

 DATABASE MANAGEMENTSYSTEM 22CS43

therein),[Link],i.e., data
describing other data.

3. Multiple Views of Data: Different users (e.g., in different departments of an organization) have
different"views"[Link],fromthepointofviewofaBursar's
Officeemployee,studentdatadoesnotincludeanythingaboutwhichcoursesweretakenorwhich grades
were earned. (This is an example of a subset view.)

As another example, a Registrar's Office employee might think that GPAis a field of data
ineachstudent'[Link],theunderlyingdatabasemightcalculatethatvalueeach time it
is needed. This is called virtual (or derived) data.

Aviewdesignedforanacademicadvisormightgivetheappearancethatthedatais structured to
point out the prerequisites of each course.

(SeeFigure1.5,page14.)

A good DBMS has facilities for defining multiple views. This is not only convenient for
users, but also addresses securityissues of data access. (E.g., The Registrar's Office view
should not provide any means to access financial data.)

4. DataSharingandMulti-userTransactionProcessing: Asyoulearnedabout(orwill)inthe OS
course, the simultaneous access of computer resources by multiple users/processes is a major
source of complexity. The same is true for multi-user DBMS's.

Arising from this is the need for concurrency control, which is supposed to ensure that
several users trying to update the same data do so in a "controlled" manner so that the
results of the updates are as though they were done in some sequential order (rather than
interleaved, which could result in data being incorrect).

Thisgivesrisetotheconceptofa transaction,whichisaprocessthatmakesoneormore
accessestoadatabase andwhichmust havethe appearanceofexecutingin isolation from
allothertransactions(evenonesthataccessthesamedataatthe"sametime")andofbeing atomic
(in the sense that, if the system crashes in the middle of its execution, the database
contents must be as though it did not execute at all).

Applicationssuchasairlinereservationsystemsareknownasonlinetransaction processing
applications.

:ActorsontheScene
Theseapplyto"large"databases,not"personal"databasesthataredefined,constructed,and used by a
single person via, say, Microsoft Access.

DEPTOF CSE Page9

 DATABASE MANAGEMENTSYSTEM 22CS43


Usersmaybedivided into

Thosewhoactuallyuseandcontrolthedatabasecontent,andthosewhodesign,developand maintain
database applications (called ―Actors on the Scene ), and

ThosewhodesignanddeveloptheDBMSsoftwareandrelatedtools,andthecomputer systems
operators (called ―Workers Behind the Scene ).

1. Database Administrator (DBA): This is the chief administrator, who oversees and
managesthedatabasesystem(includingthedataandsoftware).Dutiesincludeauthorizing
userstoaccessthedatabase,coordinating/monitoringitsuse,acquiringhardware/software for
upgrades, etc. In large organizations, the DBA might have a support staff.
2. Database Designers: They are responsible for identifying the data to be stored and for
[Link] of
users. The final design must be able to support the requirements of all the user sub-
groups.
3. EndUsers:Thesearepersons whoaccessthedatabaseforquerying,updating, and
[Link] fordatabase's existence!
o Casual end users:use database occasionally, needing different information each
time;usequerylanguagetospecifytheirrequests;typicallymiddle-orhigh-level
managers.
o Naive/Parametric end users: Typicallythe biggest group of users; frequently
query/updatethedatabaseusingstandardcannedtransactionsthathavebeen
carefully programmed and tested in advance. Examples:
 banktellerscheckaccountbalances,postwithdrawals/deposits
 reservationclerksforairlines,hotels,etc.,checkavailabilityof
seats/rooms and make reservations.
 shippingclerks(e.g.,atUPS)whousebuttons,barcodescanners,etc.,to update
status of in-transit packages.
o Sophisticatedendusers:engineers,scientists,businessanalystswhoimplement
their own applications to meet their complex needs.
o Stand-alone users: Use "personal" databases, possibly employing a special-
purpose (e.g., financial) software package. Mostly maintain personal databases
using ready-to-use packaged applications.
o Anexampleis ataxprogramuserthatcreates itsowninternal database.
o Anotherexampleismaintaininganaddressbook
4. SystemAnalysts,ApplicationProgrammers,Software Engineers:
o SystemAnalysts:determineneedsofendusers,especiallynaiveandparametric
users, and develop specifications for canned transactions that meet these needs.
o ApplicationProgrammers:Implement,test,document,andmaintainprograms that
satisfy the specifications mentioned above.

DEPTOF CSE Page10

 DATABASE MANAGEMENTSYSTEM 22CS43

:WorkersBehindtheScene
DBMSsystemdesigners/implementors:providetheDBMSsoftwarethatisatthe
foundation of all this!
 tool developers: design and implement software tools facilitating database system
design,performancemonitoring,creationofgraphicaluserinterfaces,prototyping,ets.
 operatorsandmaintenancepersonnel:responsiblefortheday-to-dayoperationofthe
system.

:Capabilities/AdvantagesofDBMS's

1. ControllingRedundancy:Dataredundancy(suchastendstooccurinthe"fileprocessing"
approach) leads to wasted storage space, duplication of effort (when multiple copies of
a datum need to be updated), and a higher liklihood of the introduction of inconsistency.

On the other hand, redundancy can be used to improve performance of queries. Indexes,
for example, are entirely redundant, but help the DBMS in processing queries more
quickly.

Another example of using redundancyto improve performance is to store an "extra" field

inordertoavoidtheneedtoaccessothertables(aswhendoingaJOIN,forexample).See Figure
1.6 (page 18): the StudentName and CourseNumber fields need not be there.

ADBMSshouldprovide thecapabilityto automaticallyenforcetherulethatno

inconsistenciesareintroducedwhendataisupdated.(Figure1.6again,inwhich
Student_name does not match Student_number.)

2. Restricting Unauthorized Access: A DBMS should provide a security and

authorization subsystem, which is used for specifying restrictions on user accounts.
Common kinds of restrictions are to allow read-only access (no updating), or access only
toasubsetofthedata(e.g.,recalltheBursar'sandRegistrar'sofficeexamplesfromabove).
3. Providing Persistent Storage for Program Objects: Object-oriented database systems
make it easier for complex runtime objects (e.g., lists, trees) to be saved in secondary
storage so as to survive beyond program termination and to be retrievable at a later time.
4. Providing Storage Structures for Efficient Query Processing: The DBMS maintains
indexes (typically in the form of trees and/or hash tables) that are utilized to improve the
executiontimeofqueriesandupdates.(Thechoiceofwhichindexestocreateandmaintain
ispartofphysicaldatabasedesignandtuning(seeChapter16)andistheresponsibilityof the
DBA.

Thequeryprocessingandoptimization moduleisresponsibleforchoosinganefficient
query execution plan for each query submitted to the system. (See Chapter 15.)

5. ProvidingBackupandRecovery:Thesubsystemhavingthisresponsibilityensuresthat
recovery is possible in the case of a system crash during execution of one or more
transactions.

Providing Multiple User Interfaces: For example, query languages for casual users,
programming language interfaces for application programmers, forms and/or command
codes for parametric users, menu-driven interfaces for stand-alone users.

DEPTOF CSE Page11

 DATABASE MANAGEMENTSYSTEM 22CS43

6. RepresentingComplexRelationshipsAmongData:ADBMSshouldhavethe
capability to represent such relationships and to retrieve related data quickly.
7. EnforcingIntegrityConstraints: Mostdatabase applications aresuchthatthesemantics
(i.e.,meaning)ofthedatarequirethatitsatisfycertainrestrictionsinorder tomakesense.
Perhapsthemostfundamentalconstraintonadataitemisitsdatatype,whichspecifiesthe
universeofvaluesfromwhichitsvaluemaybedrawn.(E.g.,aGradefieldcouldbedefined
tobeoftypeGrade_Type,which,say,wehavedefinedasincludingpreciselythevaluesin the set
{ "A", "A-", "B+", ..., "F" }.

Another kind of constraint is referentialintegrity,which says that if the database includes

anentitythatreferstoanotherone,[Link],
if(R56547,CIL102)isatupleintheEnrolled_Inrelation,indicatingthatastudentwithID R56547
is taking a course with ID CIL102, there must be a tuple in the Student relation
corresponding to a student with that ID.

8. PermittingInferencingandActionsViaRules:Inadeductivedatabasesystem,onemay
specifydeclarative rules that allow the database to infer new data! E.g., Figure out which
students are on academic probation. Such capabilities would take the place of application
programs that would be used to ascertain such information otherwise.

Activedatabasesystemsgoonestepfurtherbyallowing"activerules"thatcanbeusedto initiate
actions automatically.

:ABriefHistoryofDatabaseApplications

EarlyDatabaseApplications:

TheHierarchicalandNetworkModelswereintroducedinmid1960sanddominatedduring the
seventies.

Abulkoftheworldwidedatabaseprocessingstilloccursusingthesemodels.

RelationalModelbased Systems:

Relationalmodelwasoriginallyintroducedin1970,washeavilyresearchedand experimented
with in IBM Research and several universities.
 
Object-orientedandemergingapplications:

Object-OrientedDatabaseManagementSystems(OODBMSs)wereintroducedinlate1980sand early
1990s to cater to the need of complex data processing in CAD and other applications.

Theirusehas not taken off much.

ManyrelationalDBMSshaveincorporatedobjectdatabaseconcepts,leadingtoanewcategory called
object-relational DBMSs (ORDBMSs)

DEPTOF CSE Page12

 DATABASE MANAGEMENTSYSTEM 22CS43

Extended relational systems add further capabilities (e.g. for multimedia data, XML, and other
data types)


RelationalDBMSProductsemergedinthe

1980s
DataontheWeband E-commerce Applications:

WebcontainsdatainHTML(Hypertextmarkuplanguage)withlinksamongpages.

ThishasgivenrisetoanewsetofapplicationsandE-commerceisusingnewstandards like
XML (eXtended Markup Language).

ScriptprogramminglanguagessuchasPHPandJavaScriptallowgenerationofdynamic Web
pages that are partially generated from a database
 
NewfunctionalityisbeingaddedtoDBMSsinthefollowingareas:
 
ScientificApplications
 
XML(eXtensibleMarkupLanguage)
 
ImageStorageandManagement
 
AudioandVideodata management
 
DataWarehousingand DataMining
 
Spatialdata management
 
TimeSeriesandHistoricalDataManagement

Theabovegivesrisetonewresearchanddevelopmentinincorporatingnew data
types, complex data structures, new operations and storage and indexing
schemes in database systems.
 
AlsoallowdatabaseupdatesthroughWebpages

:WhenNottoUseaDBMS

Maininhibitors(costs)of usingaDBMS:
 Highinitialinvestmentandpossibleneedforadditionalhardware.

 Overheadforprovidinggenerality,security,concurrencycontrol,recovery,and
integrity functions.

 When aDBMS maybe unnecessary:

 Ifthedatabaseandapplicationsaresimple,welldefined,andnotexpectedto
change.

 Iftherearestringentreal-timerequirementsthatmaynotbemetbecauseof
DBMS overhead.

 If access to data bymultiple users is not required.

 WhennoDBMS maysuffice:

DEPTOF CSE Page13

 DATABASE MANAGEMENTSYSTEM 22CS43

 Ifthedatabasesystemisnotabletohandlethecomplexityofdatabecauseof
modeling limitations

 Ifthedatabaseusersneed specialoperations notsupported bythe DBMS.

DEPTOF CSE Page14