[Gambas-user] [slightly OT] No Problem with Gambas or MySql!

[Fernando Martins]

On 10/06/2013 12:05 AM, Caveat wrote:
>>> "duplicate rows are and always were a mistake in SQL", C.J.Date
> The guy has obviously never read William Kent's "Data and Reality". It's
> a book I'd still recommend to anyone working with data and particularly
> databases, even though it was written 35 years ago!
Thanks for the reference. Anyway, you know CJ Date is the guy the who 
mainly created relational databases, while everyone was laughing saying 
it would never be fast?
> <RANT>
> Why *must* every record be uniquely identifiable?  Explain why... don't
> just throw random snippets of relational database theory at me!  If I
> ask you to model the contents of your pockets on a database, are you
> going to attach some artificial key to each individual penny?  Why?
> They each buy the same number of penny chews (showing my age!), and if
> you take 3 pennies away, you've still got 3 pennies less than you had
> before, no matter how carefully you select those 3 pennies!
> </RANT>
It must not. I think most DBMS if not all, allow for it. It just means 
that if you do allow for duplicates, your data model does not follow the 
relational model which is based in set theory. Sets do not have 
duplicates (collections do). The SQL operators follow (in general) the 
set operators. Therefore, when you specify a record in a WHERE criteria 
using all the fields/values of the record, SQL assumes it is unique and 
therefore the DBMS considers all duplicates the same, which might not be 
the intended as in the case of Willy Raets.
> <HINT>
> A tuple is, by definition, unique.  If I have 8 rows on my database
> representing 8 real-world penny objects and I can delete 3 of them...
> are the remaining 5 rows tuples?  How closely does the database model
> reality in this case?
> </HINT>
Tuples are unique when refering to the theoretical model. If you have 
duplicates, then the records are unique (they use different storage 
locations), but they are certainly not unique according to the 
relational model which only cares about the fields themselves. And the 
relational model is all about reducing redundancy of data to minimise 
data inconsistency.

What is important is what you want to model. If you want to model a 
purse and all pennies are the same, then the relational model says you 
have a schema (coin_type, quantity). Spend a penny? Reduce quantity. 
What is the point of having multiple records for the same concept?

However, if you want to model a coin collection, then most likely each 
penny should be registered differently. They could be distinguished by 
year. And if the year is the same, distinguish by date of acquisition, 
etc. Well, if there is no way to distinguish, you would still use quantity.

Regards,
Fernando