hi,
somebody asked me this question but I haven't succeeded to give a good answer...
The question was...
Access 2000 is a DBMS and Sql server is an RDBMS,so what makes sql server a RDBMS in context of Codd's rule?What are the properties that Access doesn't have to be a RDBMS?
May be this is not so important issue,if anybody wants to answer plz help,gurus are requested not to waste their valuable time...If any of the gurus come I would be delighted...:DAccording to some (Fabian Pascal), neither are RDBMS according to the strict interpretation of Codd's definition.
For all practical purposes, both are RDBMS. The differences are:
1) SQL Server is a SERVER, which is scalable to Enterprise Applications, while Access process data locally and therefor has limited scalability.
2) SQL Server is a SERVER ONLY, concentrating on the storage and processing of data, while Access strives to be a complete development platform bundling interface and reporting tools as well.
Both are equally "RDBMS".|||One thing that I think is important to note is that based on the original (http://www.acm.org/classics/nov95/toc.html) paper Codd posted in the CACM, no commercial database meets all of the requirements to be considered an RDBMS. There are a number of non-commercial packages that do meet the requirements, but none of them are commercially successful, and I don't know of any that are even commercially offered.
At least in my opinion, the primary point of getting to an RDBMS is removing the "geek" side of the definition of a schema. In other words, if you don't need to know what language/tools will be used to implement the schema in order to understand and discuss that schema, then in my frame of reference you've met that spirit of the RDBMS if not the letter of the formal definition. By that standard, both Jet (the database engine that underlies MS-Access) and MS-SQL are able to meet that definition.
Nearly every database engine has its own "geek toys". Some lean toward "punch card" geeks like Z Series DB2 and Oracle. Others lean toward OOP geeks like GemStone and GBase. The problem is that any problem defintion that falls prey to relying on any geek toy is moving away from the RDBMS idea, back toward the day when you needed both file defintions and application code in order to understand what a schema meant, and in my view that is a bad thing.
-PatP|||no commercial database meets all of the requirements to be considered an RDBMS.
-PatP
Then Oracle also doesn't meet all the codd's 12 rules...but microsoft and oracle both claims their product to be a Real RDBMS.
The 12th rule i.e. no subversion rule is also confusing.Anyway
Thanks Batman,thanks Pat ,thank you both for those valuable comments. :)
Showing posts with label dbms. Show all posts
Showing posts with label dbms. Show all posts
Tuesday, March 20, 2012
Saturday, February 11, 2012
About Design Requirements for Replication across different DBMS's...
you can do snapshot and transactional replication to mysql through an odbc
datasource.
SQLCE only supports merge replication.
Are you trying to replicate from SQLCE to MySQL using merge? This isn't
supported by Microsoft.
Not only do you have connection problems but other problems with database
consistency. You replicate to SQLCE using an isapi extension. Your going to
have to write something in Visual C++ to connect the two database.
Addressing your sync statement.
You can use identity columns instead of GUIDs, MySQL does support identity.
There are a lot of problems with you approach. The main problem is how do
you handle conflicts? You are only replicating new records, but what about
updqated records or inserted records?
Suppose you delete a record on the CE device, then you read records on
MySQL, the deleted record is still on MySQL so its sent back to the SQL CE
device.
I think you are trying to roll your own merge replication. Good luck.
"Sky" <forums@.xact-solutions.removethis.com> wrote in message
news:ehtSzCKGEHA.2876@.TK2MSFTNGP09.phx.gbl...
> Hello:
> I've been musing about the requirements of making a CE port of an
> application I wrote so that the data (a classic tasks/project set of
tables)
> can be carried on a CE.
> My questions are... I know that CE and MSDE have a replication feature,
but
> what about replicating to other database systems (MySQL in this case).
> I've been trying to think of what needs to be modified in the schema
design
> to allow this to work correctly.
> My first obversations are that
> a) Autoincrement ID columns are probably a dead-end as they will get out
of
> sequence if they are generated on both the CE and the desktop. Therefore a
> GUID Primary Key column seems to be more in order.
> b) The tables all need some form of LastEdited datetime column
> c) I believe that in NET you can connect to two different providers and
the
> following would work (real rough proxycode -- I never can write INNER JOIN
> statements on the fly without trial and error, but here goes):
> Step 1: Get New records on Server:
> SELECT ServerConnection.MyTable.GUID
> FROM ServerConn.MyTable,
> ServerConn.MyTable.GUID
> INNER JOIN
> ON CEConn.MyTable.GUID
> WHERE CEConn.MyTable.GUID=NULL
> These we write to the CE client.
> Step 2: Reverse Step 1 to write any NEW records on CE to Server
> Step 3: Then get any records on server that were edited later than the
CE's:
> SELECT ServerConnection.MyTable.GUID
> FROM ServerConn.MyTable, CEConn.MyTable
> WHERE ServerConn.MyTable.GUID=CEConn.MyTable.GUID
> AND (ServerConn.MyTable.LastEdited > CEConn.MyTable.LastEdited)
> Step 4: Reverse the process to write any new records from the CE to the
> server.
>
> I think that about covers it...Any changes made on the CE done at a
earlier
> date than changes also done on the server are crushed by the Server who
has
> higher priority...
>
> What I would like to know is whether anybody has dealt with this kind of
> mess before and can give pointers on how to do it.
> For example -- does anybody have a fair idea of how Palm Pilot sychronizes
> their records?
> Is there an ODBC schema field that exists per row, cross-platform, that is
> the equivalent of DateLastModified so that I don't have to add it to each
> table?
> As you can see -- I'm at the preliminary stages of this, and any advice,
> tips, articles, code samples that you can point me to would be greatly
> appreciated.
> Thank you!
>
>
Thanks for the heads up about the Deleted records. Hum.
Obviously due to your statement
<<<Are you trying to replicate from SQLCE to MySQL using merge? This isn't
supported by Microsoft.>>
I am going to have to roll my own...which I agree is not going to be easy.
But must. I can't stand it anymore not being able to synch between different
DBMS's and want to find out where I can find a good article/code example as
to how someone goes about doing it.
It has been done before -- eg: Palm, Outlook CE, etc.
So it's not impossible. Just need help to get started.
For example -- once again, stabbing in the dark -- maybe a sys table
needs to be created called Deleted
GUID : TableName : ForeignGUID : DateDeleted
So that when a Delete FROM 'task' WHERE ID='...' one has to log it
in the Deleted table...
although this works only when deleting records one at a time (would get out
of whack if the statement were simply DELETE FROM task where Subject='xxx'.)
Or all delete statements in the program have to be modified to delete the
fields in three steps (get the ID of what you want to delete first, write
those to the deleted table, then delete the records...)
Not easy. At all.
;-(
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:OI%236$fLGEHA.2980@.TK2MSFTNGP09.phx.gbl...
> you can do snapshot and transactional replication to mysql through an odbc
> datasource.
> SQLCE only supports merge replication.
> Are you trying to replicate from SQLCE to MySQL using merge? This isn't
> supported by Microsoft.
> Not only do you have connection problems but other problems with database
> consistency. You replicate to SQLCE using an isapi extension. Your going
to
> have to write something in Visual C++ to connect the two database.
> Addressing your sync statement.
> You can use identity columns instead of GUIDs, MySQL does support
identity.
> There are a lot of problems with you approach. The main problem is how do
> you handle conflicts? You are only replicating new records, but what about
> updqated records or inserted records?
> Suppose you delete a record on the CE device, then you read records on
> MySQL, the deleted record is still on MySQL so its sent back to the SQL CE
> device.
> I think you are trying to roll your own merge replication. Good luck.
>
> "Sky" <forums@.xact-solutions.removethis.com> wrote in message
> news:ehtSzCKGEHA.2876@.TK2MSFTNGP09.phx.gbl...
> tables)
> but
> design
> of
a
> the
JOIN
> CE's:
> earlier
> has
sychronizes
is
each
>
|||Hi Hilary: addendum...
Just came across some info at:
http://www.xpinneret.com/cwu09-01.html
which was about the Palm pilot, but it did clear up some issues.
I still would need an identity column, a DateLastModified, but I have to add
a byte column.
The palm pilots was simply 4 bytes:
dirty (ie: modified)
secret
locked
deleted
In other words, one simply modifies the SQL statements to "SELECT * from
table where id=... AND deleted=false (I havn't yet figured out how to write
the bit checking part in cross platform sql, but you get the point).
And instead of using a traditional DELETE statement, making it an UPDATE
statement that modifies the bit. Quite easy for that step.
Then we are back to synchronization (the article didn't cover that ;-( ) but
I think I am starting to get a less hazy view of what we're talking about...
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:OI%236$fLGEHA.2980@.TK2MSFTNGP09.phx.gbl...
> you can do snapshot and transactional replication to mysql through an odbc
> datasource.
> SQLCE only supports merge replication.
> Are you trying to replicate from SQLCE to MySQL using merge? This isn't
> supported by Microsoft.
> Not only do you have connection problems but other problems with database
> consistency. You replicate to SQLCE using an isapi extension. Your going
to
> have to write something in Visual C++ to connect the two database.
> Addressing your sync statement.
> You can use identity columns instead of GUIDs, MySQL does support
identity.
> There are a lot of problems with you approach. The main problem is how do
> you handle conflicts? You are only replicating new records, but what about
> updqated records or inserted records?
> Suppose you delete a record on the CE device, then you read records on
> MySQL, the deleted record is still on MySQL so its sent back to the SQL CE
> device.
> I think you are trying to roll your own merge replication. Good luck.
>
> "Sky" <forums@.xact-solutions.removethis.com> wrote in message
> news:ehtSzCKGEHA.2876@.TK2MSFTNGP09.phx.gbl...
> tables)
> but
> design
> of
a
> the
JOIN
> CE's:
> earlier
> has
sychronizes
is
each
>
datasource.
SQLCE only supports merge replication.
Are you trying to replicate from SQLCE to MySQL using merge? This isn't
supported by Microsoft.
Not only do you have connection problems but other problems with database
consistency. You replicate to SQLCE using an isapi extension. Your going to
have to write something in Visual C++ to connect the two database.
Addressing your sync statement.
You can use identity columns instead of GUIDs, MySQL does support identity.
There are a lot of problems with you approach. The main problem is how do
you handle conflicts? You are only replicating new records, but what about
updqated records or inserted records?
Suppose you delete a record on the CE device, then you read records on
MySQL, the deleted record is still on MySQL so its sent back to the SQL CE
device.
I think you are trying to roll your own merge replication. Good luck.
"Sky" <forums@.xact-solutions.removethis.com> wrote in message
news:ehtSzCKGEHA.2876@.TK2MSFTNGP09.phx.gbl...
> Hello:
> I've been musing about the requirements of making a CE port of an
> application I wrote so that the data (a classic tasks/project set of
tables)
> can be carried on a CE.
> My questions are... I know that CE and MSDE have a replication feature,
but
> what about replicating to other database systems (MySQL in this case).
> I've been trying to think of what needs to be modified in the schema
design
> to allow this to work correctly.
> My first obversations are that
> a) Autoincrement ID columns are probably a dead-end as they will get out
of
> sequence if they are generated on both the CE and the desktop. Therefore a
> GUID Primary Key column seems to be more in order.
> b) The tables all need some form of LastEdited datetime column
> c) I believe that in NET you can connect to two different providers and
the
> following would work (real rough proxycode -- I never can write INNER JOIN
> statements on the fly without trial and error, but here goes):
> Step 1: Get New records on Server:
> SELECT ServerConnection.MyTable.GUID
> FROM ServerConn.MyTable,
> ServerConn.MyTable.GUID
> INNER JOIN
> ON CEConn.MyTable.GUID
> WHERE CEConn.MyTable.GUID=NULL
> These we write to the CE client.
> Step 2: Reverse Step 1 to write any NEW records on CE to Server
> Step 3: Then get any records on server that were edited later than the
CE's:
> SELECT ServerConnection.MyTable.GUID
> FROM ServerConn.MyTable, CEConn.MyTable
> WHERE ServerConn.MyTable.GUID=CEConn.MyTable.GUID
> AND (ServerConn.MyTable.LastEdited > CEConn.MyTable.LastEdited)
> Step 4: Reverse the process to write any new records from the CE to the
> server.
>
> I think that about covers it...Any changes made on the CE done at a
earlier
> date than changes also done on the server are crushed by the Server who
has
> higher priority...
>
> What I would like to know is whether anybody has dealt with this kind of
> mess before and can give pointers on how to do it.
> For example -- does anybody have a fair idea of how Palm Pilot sychronizes
> their records?
> Is there an ODBC schema field that exists per row, cross-platform, that is
> the equivalent of DateLastModified so that I don't have to add it to each
> table?
> As you can see -- I'm at the preliminary stages of this, and any advice,
> tips, articles, code samples that you can point me to would be greatly
> appreciated.
> Thank you!
>
>
Thanks for the heads up about the Deleted records. Hum.
Obviously due to your statement
<<<Are you trying to replicate from SQLCE to MySQL using merge? This isn't
supported by Microsoft.>>
I am going to have to roll my own...which I agree is not going to be easy.
But must. I can't stand it anymore not being able to synch between different
DBMS's and want to find out where I can find a good article/code example as
to how someone goes about doing it.
It has been done before -- eg: Palm, Outlook CE, etc.
So it's not impossible. Just need help to get started.
For example -- once again, stabbing in the dark -- maybe a sys table
needs to be created called Deleted
GUID : TableName : ForeignGUID : DateDeleted
So that when a Delete FROM 'task' WHERE ID='...' one has to log it
in the Deleted table...
although this works only when deleting records one at a time (would get out
of whack if the statement were simply DELETE FROM task where Subject='xxx'.)
Or all delete statements in the program have to be modified to delete the
fields in three steps (get the ID of what you want to delete first, write
those to the deleted table, then delete the records...)
Not easy. At all.
;-(
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:OI%236$fLGEHA.2980@.TK2MSFTNGP09.phx.gbl...
> you can do snapshot and transactional replication to mysql through an odbc
> datasource.
> SQLCE only supports merge replication.
> Are you trying to replicate from SQLCE to MySQL using merge? This isn't
> supported by Microsoft.
> Not only do you have connection problems but other problems with database
> consistency. You replicate to SQLCE using an isapi extension. Your going
to
> have to write something in Visual C++ to connect the two database.
> Addressing your sync statement.
> You can use identity columns instead of GUIDs, MySQL does support
identity.
> There are a lot of problems with you approach. The main problem is how do
> you handle conflicts? You are only replicating new records, but what about
> updqated records or inserted records?
> Suppose you delete a record on the CE device, then you read records on
> MySQL, the deleted record is still on MySQL so its sent back to the SQL CE
> device.
> I think you are trying to roll your own merge replication. Good luck.
>
> "Sky" <forums@.xact-solutions.removethis.com> wrote in message
> news:ehtSzCKGEHA.2876@.TK2MSFTNGP09.phx.gbl...
> tables)
> but
> design
> of
a
> the
JOIN
> CE's:
> earlier
> has
sychronizes
is
each
>
|||Hi Hilary: addendum...
Just came across some info at:
http://www.xpinneret.com/cwu09-01.html
which was about the Palm pilot, but it did clear up some issues.
I still would need an identity column, a DateLastModified, but I have to add
a byte column.
The palm pilots was simply 4 bytes:
dirty (ie: modified)
secret
locked
deleted
In other words, one simply modifies the SQL statements to "SELECT * from
table where id=... AND deleted=false (I havn't yet figured out how to write
the bit checking part in cross platform sql, but you get the point).
And instead of using a traditional DELETE statement, making it an UPDATE
statement that modifies the bit. Quite easy for that step.
Then we are back to synchronization (the article didn't cover that ;-( ) but
I think I am starting to get a less hazy view of what we're talking about...
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:OI%236$fLGEHA.2980@.TK2MSFTNGP09.phx.gbl...
> you can do snapshot and transactional replication to mysql through an odbc
> datasource.
> SQLCE only supports merge replication.
> Are you trying to replicate from SQLCE to MySQL using merge? This isn't
> supported by Microsoft.
> Not only do you have connection problems but other problems with database
> consistency. You replicate to SQLCE using an isapi extension. Your going
to
> have to write something in Visual C++ to connect the two database.
> Addressing your sync statement.
> You can use identity columns instead of GUIDs, MySQL does support
identity.
> There are a lot of problems with you approach. The main problem is how do
> you handle conflicts? You are only replicating new records, but what about
> updqated records or inserted records?
> Suppose you delete a record on the CE device, then you read records on
> MySQL, the deleted record is still on MySQL so its sent back to the SQL CE
> device.
> I think you are trying to roll your own merge replication. Good luck.
>
> "Sky" <forums@.xact-solutions.removethis.com> wrote in message
> news:ehtSzCKGEHA.2876@.TK2MSFTNGP09.phx.gbl...
> tables)
> but
> design
> of
a
> the
JOIN
> CE's:
> earlier
> has
sychronizes
is
each
>
Subscribe to:
Comments (Atom)