Need help with my database assignment

Discussion in 'Green Room' started by ming, Nov 8, 2004.

  1. ming

    ming OSNN Advanced

    Messages:
    4,252
    Location:
    UK
    Does anyone know about Normalisation?
     
  2. X-Istence

    X-Istence * Political User

    Messages:
    6,498
    Location:
    USA
    Tried google?
     
  3. ming

    ming OSNN Advanced

    Messages:
    4,252
    Location:
    UK
    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'.
     
  4. X-Istence

    X-Istence * Political User

    Messages:
    6,498
    Location:
    USA
    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.
     
  5. ming

    ming OSNN Advanced

    Messages:
    4,252
    Location:
    UK
    You don't know about normalisation or you don't know what i'm talking about? :p
     
  6. Geffy

    Geffy Moderator Folding Team

    Messages:
    7,805
    Location:
    United Kingdom
    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
     
  7. ming

    ming OSNN Advanced

    Messages:
    4,252
    Location:
    UK
    ahh Geffy. :)
    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.
     
  8. Mainframeguy

    Mainframeguy Debiant by way of Ubuntu Folding Team

    Messages:
    3,763
    Location:
    London, UK
    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? :cool:
     
  9. ming

    ming OSNN Advanced

    Messages:
    4,252
    Location:
    UK
    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
    >Customer Number
    >Customer Name
    >Address
    >Customer Telephone
    >Returning Customer? (Yes/No)
    >Notes

    Table 2: Purchases
    >Product Code
    >Product Name
    >Product Type Code
    >Product Type

    Table 3: Complaint Record
    >Customer Number
    >Product Code
    >Complaint
    >Outcome

    Table 4: Compatibility of Products and Outcome
    >Product type
    >Valid Outcomes.

    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.
     
  10. dave holbon

    dave holbon Moderator

    Messages:
    1,014
    Location:
    London England
    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.


    http://www.itworld.com/nl/db_mgr/09022002/pf_index.html

    :) :) :) :) :) :)
     
  11. Sazar

    Sazar F@H - Is it in you? Staff Member Political User Folding Team

    Messages:
    14,905
    Location:
    Between Austin and Tampa
    why would you not include teh customer number in the product table? I would think that particular relationship is required...

    :confused:

    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 :eek:
     
  12. ming

    ming OSNN Advanced

    Messages:
    4,252
    Location:
    UK
    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? :p
     
  13. Mainframeguy

    Mainframeguy Debiant by way of Ubuntu Folding Team

    Messages:
    3,763
    Location:
    London, UK
    back now :) it is rare but I must have had a day without getting to the keyboard! :eek:

    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.
     
  14. ming

    ming OSNN Advanced

    Messages:
    4,252
    Location:
    UK
    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. :eek:
     
  15. Mainframeguy

    Mainframeguy Debiant by way of Ubuntu Folding Team

    Messages:
    3,763
    Location:
    London, UK
    I may get to looking at that attachment today Ming - no promises now :p - 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.
     
  16. ming

    ming OSNN Advanced

    Messages:
    4,252
    Location:
    UK
    Cool. Reply by email please - I check it more often than coming on forum. thanx
     
  17. Graham Ball

    Graham Ball Womble

    Messages:
    3
    Ming,
    a couple of suggestions.
    Purchases.
    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.

    Complaint Record.
    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.

    Customer details
    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)

    HTH
     
  18. ming

    ming OSNN Advanced

    Messages:
    4,252
    Location:
    UK
    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.
     
  19. dave holbon

    dave holbon Moderator

    Messages:
    1,014
    Location:
    London England
    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.


    :) :) :)
     
  20. Mainframeguy

    Mainframeguy Debiant by way of Ubuntu Folding Team

    Messages:
    3,763
    Location:
    London, UK
    You have mail Ming - I was overoptimistic yesterday - but made ten minutes for this today - hope it is not too late!