Address B Rejected: Fails Relational; Fails 3NF
Date: Tue, 10 Feb 2015 23:08:17 -0800 (PST)
Message-ID: <6f5ef4b8-d719-4a74-a5a7-2713afb501a1_at_googlegroups.com>
Dear people
> On Saturday, 7 February 2015 20:10:43 UTC+11, Derek Asirvadem wrote:
>
> So it appears that:
> - all of you accept the declaration that the data model is "5NF"
> - a few of you are uncomfortable with it, but not uncomfortable enough to reject it.
> ___ And that discomfort is around the smell of the keys, but nothing specific has been stated
> ___ If nothing is stated, that means you accept it as such
> - nothing has been stated re the 3rd claim, that it is Relational. That means you accept it as such.
>
> I will give it a few more days, perhaps to ponder the keys.
The days are up.
The Address [B] proposition is rejected. I will cut-paste the text that I gave the developer. The main directive was to determine the keys:
>>>>
The issues are the same as the last time. This is a Record Filing System. It fails Relational mandates on two counts, it is non-relational.
1 Definition for keys, from the RM: a Key is made up from the data
___* A surrogate (RecordId) is not made up from the data ___* There are no Keys on the data ___* Yes, you did add Keys, but they are invalid, therefore still no Keys
2 The RM demands unique rows (data)
___* A surrogate does not provide row (data) uniqueness, only Keys supply row uniqueness
Re Normalisation, (if we consider that outside the Relational Model):
3 It breaks Third Normal Form, because in each case, there is no Key for the data to be Functionally Dependent upon (it is dependent, yes, but on a pork sausage, outside the data, not on a Key).
This RFS is nowhere near ready for Normalisation, let alone Relational Normalisation: <<<<
Previous bullet points in annotation in Address [A] have been numbered [a] to [d].
>>>>
e Keys. You did not action my points from your first attempt. I will have to number them for you. Items [c][d] relate to Keys and Identifiers. Read my IDEF1X Intro again.
___* What is the Identifier for a State ? You have StateCode, which is the 2-char ISO code. Really ? You can walk up to the database, ask for a CountyCode, and you will get one row ? Really ? There are thousands of States. Hint: we have the [N]orthern [T]erritory, Canada has the [N]orthwest [T]erritories.
___* What is the Identifier for a County ? When the user walks up to the database, with one County is mind, what does he have in mind, how does he Identify "Lee County" ?
___* County(CountyCode) is unique ? America has 50 States, they have a minimum of 1 County, they start with 001. The moment you attempt to insert the first County for the second State, it will blow you away.
___* Street(StreetName, StreetTypeCode) is unique ? ___ One street of one name+type in the whole Street table ? ___ Did you mean Street(SuburdId, StreetName, StreetTypeCode) is unique ?
___* Do you want two countries named Brazil ? I showed you how to prevent that three years ago, you have forgotten. America has 13 Counties named "Lee County".
___This applies to all seven tables, every one of them has a gross error on the Key. Talk through each point with Ken. If you come back with Keys that are less than 95% complete, if you fail to follow my directions again, I will burn your books. <<<<
None of you theoreticians identified any of that The non-rejection, with or without specifics, is an acceptance
The exercise now, is to determine the correct Keys. Let's see if you can produce them, before the developer can. Use the Address [B] proposal, because there is no change. http://www.softwaregems.com.au/Documents/Article/Normalisation/Relational%20Database%20101%20B.pdf
If you want more detail re what I told the developer, visit this. The pages are in RC order. He has not returned with a proposal [C], so the first page is blank (it will be populated when he does): http://www.softwaregems.com.au/Documents/Article/Normalisation/Relational%20Database%20101%20C.pdf
Now it has been established, for decades, that humans can determine Keys, directly, by working with the data directly, and by using FDs to validate the Keys (the FD Definition is in the 3NF Definition). - That has recently been confirmed yet again, in this thread, when I Relationalised Köhler's DNF data and eliminated his problem, his proposal. - It is further confirmed by Nicola, implicitly, because he is following those directions. the developer will be using that method.
In two iterations you have not been able to do that.
However, it has now been exposed (in this thread) that your teachers have taught you to determine keys via non-FDs; to make puzzles; which is great for RFSs, but not for RDBs; to avoid the direct method that is relevant to RDBs. Further, as evidenced in this thread, you people are obsessed with it. Therefore, I invite you to determine the Keys *using whatever method you like*. This is a great opportunity to demonstrate, to confirm, the non-FD method.
Key points from another post, that are worth keeping in mind:
> On Saturday, 7 February 2015 19:09:53 UTC+11, Derek Asirvadem wrote:
> > On Friday, 6 February 2015 04:07:52 UTC+11, Jan Hidders wrote:
>
> d. Keep in mind that the users and developers mean "Alaska" when they use "AL", they do not want to, and should not have to, use "US-AL". Ie. a StateCode is an Atomic item, it would be preposterous if we had to mess with SUBSTR(StateCode, 3, 2).
>
> e. Likewise for the CountyCode. It is not CHAR(7) ie. containing CountryCode and StateCode. It is CHAR(3) containing CountyCode, only CountyCode, and nothing but CountyCode, so help me Codd. FIPS is US only, numeric; most countries use a string.
Please keep my response of today to James in mind. Please ask me as "customer" and "user" any and all questions. I don't know what you do not know, unless you ask. I thought the data in that cluster was pretty much known to most IT people.
Here is more detail, to cover some of the issues that have been touched, what I think James may be getting at.
- All columns must be Atomic (1NF).
- CountryCode is CHAR(2) ISO-3166-1
- StateCode is CHAR(2) ISO-3166-2
- CountyCode is CHAR(3) FIPS numeric for America; char for the rest of the world
- The above three are generally well-known. Eg. the sales people know their ambit by heart (not the numerics!). We do not mess with clarity. All dialogues (windows, frames, whatever) that are used by users have three separate fields for the above (which may be set next to each other when relevant)
- All Name columns are CHAR(32)
- All FullName columns are CHAR(64)
- All ID columns are 32- or 64-bit Integers, depending on the max rows possible. ___ He is on notice that he can't have them as he has planned because it is an RFS, that he must use the minimum possible surrogates. But I am not pushing it in this iteration, the Keys are thig for this one.
> It has a type
The type is generally not necessary in an exercise at this level, but having had that pointed out as a limit to assumptions, etc, I have given it.
> domain,
Yes. That is for you to determine, to work out.
> and some relationship (perhaps functional dependency) on other
> columns.
Yes. That is for you to determine, to work out. Which is infinitely more possible, and much easier, if you determine the Keys first.
> Any statement about keys *must* be based on stated FDs.
Fine, but it does not apply to this task.
And feel free to ignore my statement above, and go that route. The result we want is, desperately now, to have the Keys determined. If you think your way is better, go for it.
Hugs and kisses
Derek
Received on Wed Feb 11 2015 - 08:08:17 CET