How RDBMS solves File System Problems
Business / Technical Situation
The business users have complained that the existing file system is
outdated and is unable to adequately address the anticipated
information needs.  As a senior member of the DBA department, you are
to summarize the characteristics (problems) of file systems, and
determine what features in RDBMS solve the problems.  Suggested
format:
Basic File System Problem
     Insufficient field level security - application has access to all
        fields in record
RDBMS feature, and example
     Views - allow access to only the specified columns in table

Here is what I have thus far:(Please not that (1) is the Basic File
System Problem, whereas (2) is the RDBMS feature, and example.  Please
fill in (2) for the example I have come up with, as well as adding
anything that you may e able to.
(1)Insufficeient field level security - application has access to all
fields in record
(2)

(1)Even the simplest data retrieval task requires extensive
programming in 3GL(third-generation language).
(2)

(1)The need to write 3GL programs to produce even the simplest reports
makes ad-hoc queries impossible.
(2)

(1)As the number of files in the system expands, system administration
becomes difficult.  Each file must have its own file management
system, composed of programs that allow the user to do various tasks.
(2)

(1)Structural and Data Dependence
(2)

(1)Data Redundancy - since the file system makes it difficult to pool
data, it is likely that the same data are stored in many different
locations.
(2)

(1)Field Definitions and Naming Conventions
(2)

Hi, bildy-ga:

Thanks for the well-defined format of this question.  I will begin by
responding to the items which you have defined, and then (at bottom)
discuss a few more contrasts between file-based data management and
relational database management.

Here is the sequence of (1) and (2) items, with my examples for (2)
filled in for each:

(1)Insufficeient field level security - application has access to all
fields in record

(2) With VIEWs and rule-based roles & permissions assigned to users,
access to particular fields within records (and even to particular
records within tables) can be limited as necessary (by user and/or by
role).

For a subtle variation on this theme, here's an article discussing (in
the context of Access) a mechanism enforced by the RDMS that ties user
permissions on an underlying table to particular queries:

http://www.databaseadvisors.com/newsletters/0111ViewMetadata.htm
 
(1) Even the simplest data retrieval task requires extensive
programming in 3GL(third-generation language).

(2) With relational database one has an almost universal data
retrieval language in SQL (Structured Query Language).  For more
information see:

http://epoch.cs.berkeley.edu:8000/sequoia/dba/montage/FAQ/SQL.html#SQL

SQL is a "nonprocedural" language, which means that users can focus on
the logic of the data retrieval more than on "how" the data is
retrieved.

(1) The need to write 3GL programs to produce even the simplest
reports
makes ad-hoc queries impossible.

(2) The response is the closely related to the point immediately
above.  With the adoption of SQL as a standard for adhoc queries, many
"client" tools to connect to varied databases (such as Crystal
Reports) have incorporated a SQL interface.  Thus data can be
retrieved in a simple tabular stream and presented in reports and
screens using a variety of WYSIWYG specification tools.

Essentially all RDMS vendors provide some sort of "simple" SQL query
client tool suitable for "off the cuff" adhoc query activities.
 
(1) As the number of files in the system expands, system
administration
becomes difficult.  Each file must have its own file management
system, composed of programs that allow the user to do various tasks.

(2) The theory of RDMS (relational database management systems) is
that the database should be administered through tables that describe
the database, so-called metadata.  This single-minded approach pays
off in providing an all-encompassing mechanism for both database
description and storage.

The situation is not quite so "black and white" as the statements
might suggest.  Many file-based data management schemes in fact rely
on flat files that can be targeted by "reusable" programs.  At the
same time RDMS vendors may permitted user-defined and "BLOB" datatypes
that require application knowledge in order to manipulate/interpret. 
However there is certainly a wide contrast here.  See the discussion
under the next item for more details:

(1) Structural and Data Dependence 
(2) File-based data management systems are often perceived as having
code reuse pitfalls, because there are no universal access mechanisms
to enforce data integrity (so any "field" can be populated or not with
meaningful data).

Any real world RDMS is bound to have a few shortcomings in this
respect, but the large degree of progress made by having the universal
access mechanism (SQL) available to target data integrity issues is
inarguable at this point.

The SQL standard provides a well-thought out scheme of keys and basic
datatypes adequate for structural enforcement of most data dependence
issues.  These may be augmented as necessary by rule-based constraints
and triggers.  So while there is certainly an assortment of data
integrity mechanisms that might be used for a given application, they
are all founded within the RDMS mechanism and do not require constant
reinvention.

Here's an article that gives some background on RDMS table design and
"normalization" (proper use of keys).  Even more importantly you'll
probably want to remember the site itself for future research on RDMS
articles:

http://searchdatabase.techtarget.com/tip/1,289483,sid13_gci804576,00.html

(1) Data Redundancy - since the file system makes it difficult to pool
data, it is likely that the same data are stored in many different
locations.

(2) Normalization of RDMS tables/design is intended to eliminate data
redundancy (except as intentional provided for by the database
architect).

Elimination of data redundancy is critical to data integrity, since
you make thereby make it impossible to alter a piece of data in one
location while preserving an out-of-date copy in the system in another
location.

For an introduction to normalization that touches on this and other
goals:

http://www.sqlmag.com/Articles/Index.cfm?ArticleID=4887&pg=1

(1) Field Definitions and Naming Conventions 

(2) Although one cannot avoid a certain amount of arbitrariness in
assigning names, with an RDMS this assignment is at least
"centralized" so that all users can refer to a common "data
dictionary".  This data dictionary is comprised of certain "system
tables" mentioned earlier that contain descriptions of all the
database objects, from fields in tables to users' permissions and
roles.

For a discussion about how "metadata" including table and column names
are managed (and exposed programmatically) within MS SQL Server, see
this article:

http://www.databasejournal.com/features/mssql/article.php/1460131

Additional contrasts between Filed-based Data Management and RDMS:

- Operating systems provide limited mechanisms for arbitrating between
users both wanting to update particular items within file-based data
management applications.  A file may be opened for write access by
only one user at a time, but this is ordinarily too restrictive for
practical purposes.  An application may provide adhoc solutions to
these common problem, but a uniform approach is provided within RDMS
applications by the concept of "transaction".

A basic introduction the syntactic aspects of transactions (SQL COMMIT
and ROLLBACK) is found here:

http://www.firstsql.com/tutor5.htm

[NB: As a historical note, the early history of UNIX at Bell Labs was
marked by repeated attempts to add operating system mechanisms such as
record locking (and pipes) which address the "database" application
issues raised here.  Eventually it was accepted that an operating
system could not practically address these issues, and the appropriate
mechanisms should be sought in the context of a DBMS instead.]

- Backup strategies differ for File-based data management and RDMS

This is really just a contrast, not a right versus wrong issue.  The
extra structure provided by an RDMS comes a price of tightly coupled
application and system data stored in files that must be exclusively
managed by RDMS software.  Therefore a backup means more, in the case
of an RDMS, than in the case of simple file backups, but it can also
provide more.  Software to backup an RDMS must usually coordinate
schedules with "dumps" and transaction logging events scheduled within
the RDMS framework.

regards, mathtalk-ga

---

Request for Answer Clarification by bildy-ga on 20 Nov 2002 16:02 PST

Please take a look at
https://answers.google.com/answers/main?cmd=threadview&id=110381 and
let me know if you might be able to answer this question.

Question ID = 110381

---

Clarification of Answer by mathtalk-ga on 21 Nov 2002 09:27 PST

Hi, bildy-ga:

I'll be happy to review that question.  I remember looking at it
before and thinking that it was unclear what scale of response would
be acceptable to you.

Having said that, you might want to review Google's pricing
guidelines:

https://answers.google.com/answers/pricing.html

Although I was willing to answer the current question for you at the
listed price because I'm quite familiar with the issues raised by it,
I actually spent the full two-hours provided by Google's "answer"
window preparing my response.

I will make further comments in your other question's thread, which
might be of help to other experts interested in answering your
question as well.

regards, mathtalk-ga

---

Request for Answer Clarification by bildy-ga on 21 Nov 2002 13:45 PST

Ok, take a look at it, and give me any feedback such as what you feel
the question should be valued at, as well as, what needs to be
clarified in the question itself.

---

Clarification of Answer by mathtalk-ga on 21 Nov 2002 14:09 PST

Hi, bildy-ga:

Yes, I just finished posting, as a comment, some analysis of that
other question.  If you wish, you may treat it as a request for
clarification (on that thread, not this one).

I hope that will be helpful to your getting the complete answer you
want.

regards, mathtalk-ga

Went above and beyond what was necessary !  Thanks ! Would love to
work with you again !