Discussion in 'Green Room' started by ming, Nov 8, 2004.
Does anyone know about Normalisation?
no.. i know how to get to the 3rd normal form, but am a bit stuck with one or two of the items that i think i'm supposed to include.
All the info i've been through shows me how to do stuff like getting student ID/name... course code, course title etc etc.. to the 3rd normal form.
Same can be said when using Customer ID... Stock ID...etc etc.
However, not sure where to include extra items.. for example 'Customer Complaints' and 'outcome/resolution of complaint'.
I have no clue what it is, if you have a link to a page and or your assignment, i could think about it, and know what it is, and give some suggestions.
You don't know about normalisation or you don't know what i'm talking about?
damn Database Table Normalisation I remember doing that 3 years ago. If you can wait til after the weekend then I can take a stab at it, but I have code to be getting on with til then.
Basically though you might want to have a table of complaints with complaint ids with customer ids in there as well
or abstract that further into a table of complaints with complaint ids and then a separate sort of mapping table which stores the complaint ids and the customer ids together
Was thinking of using Complaint ID, but one of my tutor said it's not required. Might still use it anyway, but confused as to what to link complaints to... link to customer or purchase...etc
Hopefully I won't need to wait til the weekend. But will have to if can't get any joy in this.
I know a LOT about normalisation - but I am not sure what to give you..... yet....
Let us just say an easy way to remember 3NF is that you must have THE KEY, THE WHOLE KEY, AND NOTHING BUT THE KEY.... IMHO 3NF is the main interesting one - and I do not credit anything beyond 5NF although some talk of 7NF.....
OK - so hit me with your question - plus give me rep points for that easy 3NF mnemonic, huh?
OK. Haven't had time to come on forum coz i had other assignments to do.
I only go up to 3NF on this database thing, nothing further than this.
Mainframe, Geffy. I see the following headings under four tables. I can get to 3NF for the first two, but have not included anything else from the other two. I know I'm missing something but not sure exactly what they are or where they go for that matter. Can you guide me please?
Remember these are only the headings in each table.
Table 1: Customer Details
>Returning Customer? (Yes/No)
Table 2: Purchases
>Product Type Code
Table 3: Complaint Record
Table 4: Compatibility of Products and Outcome
Obviously there are repeating stuff here, but I'm more concerned about getting the 'Complaint' bit included in the Normalisation.
Thanks in advance for any help you can provide me.
Basically the word normalisation as applied to databases is based on Codd’s theories, now many years out of date but still worthy of consideration. He was employed by IBM at the time he developed his theories (which by the way they rejected) and is the father of the modern relational database systems, or at least the logic behind them.
Codd’s initial theories were in fact mathematically 100% correct (and still are today) and his subsequent revisions in later years, to me watered down the logic somewhat as most computer hardware available at reasonable cost and programmes available did not/could not comply totally with his model, and still don’t. He was however a genius in my view. Basically he devised a way of storing data on a computer that had no (or little) redundancy, in other words there was no single item stored in more than one place that was stored somewhere else in the system, except fot "keys" by this I refer to joins between tables or links between them, these could be repeated. Keys are in fact joins between tables where relationships are set hence the name relational databases. Whilst it might seem difficult to get your head round it’s quite simple in context once you throw away all the bull**** and systems speak.
His logic was simple, only store an item once, if you find that you are having to input someone’s name after already inputting it, you database design in incorrect, it’s that easy and this applies to everything but as usual it’s badly explained by most. You will hear things like the first normal form to the sixth and beyond but in reality it’s just a way of thinking that’s logical, nothing more.
Codd completed his PhD at the University of Michigan in 1963, and presented a thesis on the topic of a self-reproducing computer consisting of a large number of simple identical cells, each of which interacts in a uniform manner with its four immediate neighbours. Codd reported this work in a book entitled Cellular Automata published by Academic Press in 1968. He was responsible for the SQL (Structured Query Language) used today by all major databases.
Go here for his twelve rules but I always suggest not doing this as it merely complicates an original brilliant suggestion that has been clouded in academia over the years. Especially the same description of his “normal forms” which if you read or have to learn some of the texts that I have read over the years are almost incomprehensible in both logic and understanding. Codd was a genius make no mistake about it.
why would you not include teh customer number in the product table? I would think that particular relationship is required...
I am about 4 years removed from working on SQL 50hrs a week... lol... still have a little rudimentary idea about whats going on... but not much
You mean table 4?
That table only shows what's a valid outcome for certain products. It's got no direct relationship with the Customer table.
I'm unsure what to link the Complaint part to. Should it be linked directly with the Customer entity at the start (1NF) or linked to the Purchase entity?
Where's Mainframe and Geffy gone?
back now it is rare but I must have had a day without getting to the keyboard!
Anyway - a couple of points, I agree with Ming partially - it is intuitive that the Complaint table needs a purchase key in there too (how can you complain about a product you have not purchased?). However table 2 is misnamed - it is not a purchases table, it is a product table. A purchases table may well be required so you will then have five tables (I am not sure what the application is, we have come into this obliquely).
Secondly your compatibility table cannot be right - it will need two product codes (possibly more) to record compatibility, or the lack thereof (although it would be more logical to only make entries for compatible products and infer they are incompatible if there is no entry).
I hope this input helps - it is no longer really related to normalisation as such - you seem on track there for 3NF OK.
Mainframe the tables above have no relationship in terms of database at the moment. You can say it's just tables that lists stuff associated with that particular entity.
The bit I'm having problems with is still the complaints part.
I may get to looking at that attachment today Ming - no promises now - but just letting you know I have not forgotten and will try to squeeze it in....
Also, as ever, use your own learning/intelligence and do not trust what I say verbatim - I intend to avoid going through actually answering anyway, so it will just be helpful hints/discussion....
Oh yes, and I guess you want me to reply via email? Or shall I post here? Bearing in mind what I just said - I shall not reply nor post until I hear which way anyhow.
Cool. Reply by email please - I check it more often than coming on forum. thanx
a couple of suggestions.
You have a product type code then the product type.
The product type should be on a separate table with the product type code as the key.
You could have a table of standard complaits (like outcomes) and key that with a complaint id, which is you then have in your complaint record.
A customer could have more than one address.
You should have a separate address table keyed on address id and customer id (in case husband and wife had separate accounts)
I'm not required to create a new entity for additional addresses although I have thought about it. My main concern is the Complaint ID - not quite sure whether to link it to the Customer entity or the purchase entity when drawing up the Normalisation thing.
First of all Customer details should contain only unique data regarding a single customer and is recorded by reference to his ID number (which your system generates), that being an individual who is actually purchasing a product, and in this respect allows for multiple customers from the same address as they will be given a different ID, negating the previously posted problem concerning same address details (family members etc). Addresses do change from time to time as we all know but in the word of relational databases it’s the customer ID that identifies the individual, this then poses another problem that being where I change my address, forget to tell anyone, and then a few years later try to purchase another product from your company. As is the case I will give my details over the net or by telephone and no one in your company will be aware that in fact, I have purchased form you before. That is if they don’t ask you or if I don’t tell them for whatever reason. This is why credit information is address based and not individual person based. This was addressed as a problem many years ago and solutions provided that are well known and it broke Cod’s theories.
Your table 1 (customer details) is incorrect as how can they be a returning customer and anyway this is obvious when you join the products table to the customer table this table which only contains unique data about this individual, your table 2 (Purchases) is incorrect, it should be products, your table three (Complaint Record) is customer specific and should be in a one to many join to table one, on the key field customer ID, to both enable many complaints which should really be re-titled remarks with a separate yes/no field to identify complaints form commendations. Your table 3 (Compatibility of Products and Outcome) is entirely incorrect as these are ideas and not data storage issues. Compatibility of products with what? Outcome of what issues? Possibly another ten or twenty tables here.
Hope this helps in steering you in the right direction as I have included some errors here.
You have mail Ming - I was overoptimistic yesterday - but made ten minutes for this today - hope it is not too late!