About MS-Access RDBMS

[Srinivasan]

Can we say MS-Access as True RDBMS?, What are the key differences between the
RDBMS (Sql-Server) and MS-Access

 

[Jeff Boyce]

It really depends on how you define "True".

Both rely on well-normalized data structures to make the most of their
functions/features.

SQL-Server provides more in the way of logging, triggers, security,
capacity.

Access provides a front-end development tool.

Which is more "true" for you?

 

[Tim Ferguson]

Can we say MS-Access as True RDBMS?,

I have a feeling that this is a troll, but in any case I'm sucker enough
to bite... Codd describe the twelve rules of relational fidelity, and
Pascal added rule 0.

0) Foundation rule: a relational dbms must manage databases entirely
through relational capabilities.

The mdb file is a monolith, and it's not possible to pretend that tables
in different file can be related, and there are no tools to manipulate
the data in mdb files directly; so this is a pass.

1) Information rule: all information must be held in R-tables.

Access allows tables to be created without primary keys and therefore
fails rule 1.

2) Guaranteed logical access rule: every value must be accessible via
table name, PK value and column name.

Since Access fails rule 1, this also fails by definition.

7) Set level operation rule: select, insert, update and delete operations
must be available via set operations rather than requiring row-by-row
manipulation

Within the limitations of the language, all these operations are called
by SQL and so this is a pass.

3) Missing information rule: missing information should be handled by the
database not by applications' own exception processing features.

Access provides NULL handling on all data types except booleans. Note
that Codd himself had big problems with proper handling of null data, so
Access is not significantly worse than any other product. I think MVDBMSs
are meant to be better, but I don't understand enough to make a valid
judgement.

8) Physical independence rule: applications and interactive operations
should remain independent of internal storage mechanisms.

Microsoft only provides access to (logical) data via the Jet engine, and
the internal (physical) structure of mdb files is a deep mystery which
may or may not change from one version to another. This rule is therefore
a pass.

9) Logical independence rule: applications and interactive operations
should remain independent of the structure of the base tables.

Access does not support views as an alternative to direct base table
access -- queries are partly updateable and cannot always be substituted
for base tables. This is at best a partial pass, and strictly it's a
fail.

6) View update rule: the rdbms should identify at definition time whether
or to what extent a view can be updateable.

I have a feeling that Access should do this, but updateability seems so
unreliable and paradoxical that I can't use the capability. In my hands,
Access fails this rule, but it may be that in others' more experienced,
it's at least a partial pass.

10) Integrity independence rule: operations and applications should be
independent of internal integrity declarations.

Access can define unique key and foreign key integrity rules, validation
rules that cover one table and rules across multiple tables.
Unfortunately, the user interface makes these hard and often pretends
that they are optional, so although this rule is a pass, Microsoft loses
credit for trying to obfuscate it.

11) Distribution independence rule: operations and applications should be
independent of distribution and re-distribution of the database

I don't have a lot of experience with Jet replication, but I gather that
the applications run just as happily against a replica as with a real
database, so this is a pass. Just whether replication counts as true
distribution is another argument and it's well over my head!

4) Dynamic online relational catalog rule: The database description
should be held just like ordinary data i.e in R-tables

Access nearly gets a pass on this. Actually, the entire schema is held in
R-tables, but then agan Microsoft has officially not documented these and
the only reliable access is via proprietary dao or adox libraries. In
practical terms, therefore, it's a fail.

5) Comprehensive data language rule: there should be one language to
support all the relational features of the database - data definition,
data manipulation, integrity rules, authorisation etc.

Access has a pretty complete implementation of SQL DDL and DML; but it
does not use the same langauge for security features or the system
catalog or queries, so this is a fail.

12) Non subversion rule: operations in a native language should not be
able to subvert data integrity rules declared in a higher-level language.

Access does not allow the DAO library (for example) to break integrity
rules, so this is a pass.

Summary: Access passes seven out of thirteen rules for relational
fidelity. It's up to you whether this makes it a True RDBMS, or even
whether you think that a product can be half-relational. Are you looking
for a platform to learn about relational theory and design? Are you
looking for a standardised environment to develop a mission-critical
database? Are you comparing with other desktop products, or with
industry-strength software systems costing thousands of dollars?

All the best


Tim F

 

[david epsom dot com dot au]

RDBMS is the type of DBMS that Access is. Roughly, this means
that you can define views in Access, and treat them like tables.
This is the thing that was 'different' between an RDBMS and
other kinds of DB systems.

Moreover, a 'True' RDBMS is a theoretical construct. There is
no such thing as a RDBMS that is both True and Real. MS SQL
Server is not a 'True' RDBMS.

However, there was a reason for each of the design rules for
a True RDBMS, and to the extent that Access has a weak, missing
or faulty implementation of each of the design rules, it is,
whatever it's strengths, not a very true implementation of
a True RDBMS.

(david)

 

[Srinivas]

Thank you very much Tim, this what I expected.

I'm a developer on VB looking to build prototype applications on MS-Access.
Working in Malaysia, Could you tell me how about the VBA and Access market
now.? would you advise me switch to these techies from VB.

Thanks
-Srinivas

 

[Tim Ferguson]

I'm a developer on VB looking to build prototype applications on
MS-Access. Working in Malaysia, Could you tell me how about the VBA
and Access market now.? would you advise me switch to these techies
from VB.

No I wouldn't advise you: I don't earn my living doing these things. Maybe
some of the people around here who do... It's not really a TablesDBDesign
NG question, I don't think.

All the best


Tim F

 

[Tim Ferguson]

Jet has DML (SELECT, UPDATE, DELETE etc), DDL (CREATE TABLE, CREATE
VIEW, DROP PROCEDURE etc), DCL (CREATE GROUP, DROP USER, ROLLBACK
TRASACTION etc), which can be considered sublanguages of the SQL
language. So why do you think this is a fail?

I take the point about the DCL, but I've never used those commands and
wasn't aware that they were there. It's still a fail because the system
catalog is not _formally_ available to SQL, and Codd keeps repeating that
the system tables should be accessible in exactly the same way as the
normal data tables. There is nothing to stop MS changing the way the
catalog tables are built in a future version of Access (and God knows
they have taken worse decisions in the past) that will simply break loads
of existing applications that do SELECTs FROM _msysObjects and whatever.

A Jet CHECK constraint cannot refer to rows in the same table in which
it is defined. I assume the same applies to Validation Rules in the MS
Access UI. A fail, then?

No: using ADO and Jet 4, this sort of thing works:

ALTER TABLE MyTable
ADD CONSTRAINT NoCopy CHECK 0 =
( SELECT COUNT(*) FROM MyOtherTable
WHERE MyOtherTable.IDNumber = MyIDNumber
)

Stupid example I know, but you get the picture. It can be useful, for
example, in subtyping, where you want to make sure that a master record
can be echoed in only one of the subtypes at a time.

It's not available to the Access UI out of the box, which appears to use
DAO and Jet 3.6, but it can be done. This was pointed out quite recently
by someone else (sorry if it was you!!!) in these NGs. I didn't know
about it before either and was delighted when I tested it and it worked.

Jet supports a VIEW syntax, of course. Your comment reminded my how
Jet's syntax for performing an UPDATE with a JOIN is 'non-relational'
and that doing things the 'proper' way i.e. with an EXISTS (<subquery>)
clause results in the baffling "updateable query" error. Perhaps 7)
should be a fail then due to a fundamental design flaw... sorry,
feature, rather than being a 'limitations of the language'?

I confess to being baffled by SQL and views, and in particular how to get
Access to update what I want it to. Rule 7 is meant to make sure that all
four commands are available to SQL (compare with other dbs that will
SELECT but everything else has to be row-by-row). Full View independence
is probably one of those areas that is genuinely a "theoretical
construct" and not able to be physically implemented. This is where I
start to get out of my depth, though.

And by then I was starting to get fed up with typing what turned out to
be a much longer response than I started with!!

All the best


Tim F

 

[Tim Ferguson]

Actually, I think it *was* me <g>.

In that case I'll shut up and take your word for it.

Best wishes


Tim F

 

[david epsom dot com dot au]

I think your description misses the point out of rule 1:

Rule two says that tables have primary keys.

Rule one says that not just 'data', but foreign key
index definitions, stored procedures, permissions,
constraints, etc etc are stored in tables.

When properly formulated, the rules should be orthogonal:
ie you shouldn't be able to say 'fails rule 2 because it
fails rule 1'

(david)