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 ------------------------------------------------------------------------------ October Webinars: Code for Performance Free Intel webinars can help you accelerate application performance. Explore tips for MPI, OpenMP, advanced profiling, and more. Get the most from the latest Intel processors and coprocessors. See abstracts and register > http://pubads.g.doubleclick.net/gampad/clk?id=60134791&iu=/4140/ostg.clktrk _______________________________________________ Gambas-user mailing list Gambas-user@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/gambas-user