If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
About MS-Access RDBMS
Can we say MS-Access as True RDBMS?, What are the key differences between the
RDBMS (Sql-Server) and MS-Access |
#2
|
|||
|
|||
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? -- Good luck Jeff Boyce Access MVP "Srinivasan" wrote in message ... Can we say MS-Access as True RDBMS?, What are the key differences between the RDBMS (Sql-Server) and MS-Access |
#3
|
|||
|
|||
"=?Utf-8?B?U3Jpbml2YXNhbg==?="
wrote in : 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 |
#4
|
|||
|
|||
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) "Srinivasan" wrote in message ... Can we say MS-Access as True RDBMS?, What are the key differences between the RDBMS (Sql-Server) and MS-Access |
#5
|
|||
|
|||
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" wrote: "=?Utf-8?B?U3Jpbml2YXNhbg==?=" wrote in : 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 |
#6
|
|||
|
|||
Tim Ferguson wrote: Could you clarify some of your points, please...? 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. 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? Access can define ... validation rules that cover one table and rules across multiple tables. ... this rule is a pass 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? Access does not support views as an alternative to direct base table access -- queries are partly updateable 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'? Jamie. -- |
#7
|
|||
|
|||
"=?Utf-8?B?U3Jpbml2YXM=?=" wrote in
: 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 |
#8
|
|||
|
|||
"Jamie Collins" wrote in
oups.com: it does not use the same langauge for security features or the system catalog or queries, so this is a fail. 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. Access can define ... validation rules that cover one table and rules across multiple tables. ... this rule is a pass 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 |
#9
|
|||
|
|||
Tim Ferguson wrote: Tim, Thank you. One more point: A Jet CHECK constraint cannot refer to rows in the same table in which it is defined. 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 ) Your CHECK references the current row with rows in *another* table. When I said 'cannot refer to rows in the same table' I meant something more like this: ALTER TABLE MyTable ADD CONSTRAINT NoDups CHECK (0 = (SELECT COUNT(*) FROM MyTable AS T1 WHERE MyIDNumber = T1.MyIDNumber)); 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. Actually, I think it *was* me g. Jamie. -- |
#10
|
|||
|
|||
"Jamie Collins" wrote in
ps.com: 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. Actually, I think it *was* me g. In that case I'll shut up and take your word for it. Best wishes Tim F |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How to get data from Analysis Services cubes into Access | General Discussion | 2 | December 4th, 2004 02:21 PM | |
Access 2000 -vs- Access 2003? | Mark | General Discussion | 5 | November 30th, 2004 06:36 AM |
is Access 2003 any better than XP? | Gorb | General Discussion | 4 | November 11th, 2004 09:44 PM |
MICROSOFT INVESTING HEAVILY IN ACCESS | Mike Painter | General Discussion | 39 | October 15th, 2004 03:56 PM |
Access user profile | Michael Breitsameter | General Discussion | 7 | July 23rd, 2004 07:47 AM |