1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.
  2. Hi Guest, welcome to the TES Community!

    Connect with like-minded education professionals and have your say on the issues that matter to you.

    Don't forget to look at the how to guide.

    Dismiss Notice

normalisation

Discussion in 'Computing and ICT' started by msa969, Jan 15, 2012.

  1. msa969

    msa969 New commenter

    I
    have downloaded the datasets from the Edexcel website for GCE Applied ICT. Here
    is the table structure:</font>


    large
    table (order number, cd number, order date, cd title, artist, new customer
    title, first name, last name, address line 1, town, country, postcode, card
    type, card number, expiry date)</font>



    <font size="3" face="Times New Roman">

    </font>I
    have interpreted this and did a normalisation. </font>


    Order table (order number, order date,
    cd number)</font>


    Cd
    (cd number, cd title, artist) </font>


    Customer (customer #, new customer
    title, first name, last name, address line 1, town, country, postcode, cd
    number, card type, card number, expiry date) </font>


    Will this be a good interpretation or
    shall I normalise it further for 4 data dictionary for example card type and is
    this necessary. </font>
     
  2. msa969

    msa969 New commenter

    I
    have downloaded the datasets from the Edexcel website for GCE Applied ICT. Here
    is the table structure:</font>


    large
    table (order number, cd number, order date, cd title, artist, new customer
    title, first name, last name, address line 1, town, country, postcode, card
    type, card number, expiry date)</font>



    <font size="3" face="Times New Roman">

    </font>I
    have interpreted this and did a normalisation. </font>


    Order table (order number, order date,
    cd number)</font>


    Cd
    (cd number, cd title, artist) </font>


    Customer (customer #, new customer
    title, first name, last name, address line 1, town, country, postcode, cd
    number, card type, card number, expiry date) </font>


    Will this be a good interpretation or
    shall I normalise it further for 4 data dictionary for example card type and is
    this necessary. </font>
     
  3. Haven't seen the Edexdel datasets (I don't teach it) but for customers to be able to order multiple CDs but who only ever use one Credit Card, wouldn't it be more like:
    Cust(Cust ID, Title, 1st Name, 2nd Name, Address
    Line 1, Town, Country, Postcode, Card Type, Card Number, Expiry Date)
    Order(Order Number, Order Date, Cust ID)
    Order Detail(Order Number, CD No)
    CD(CD No, CD Title, Artist)
    Apologies if this is bull%%%,; like I say, I haven't read the Edexcel thing and am on my 4th Pink Gin.
    Later
     
  4. msa969

    msa969 New commenter




    Here is the books orders
    &ndash; data dictionary</font>


    Large table (order id, order date, number ordered, isbn,
    author first name, authors last name, book
    title, price, title, type, first name, last name, address line 1, town,
    country, post code, card type, card number, expiry date)</font>


    Here is three tables I have created after normalisation, note
    I have added costumer # during normalisation to the data dictionary :</font>


    Order table (order id, order date, number ordered, customer
    #)</font>


    Product _book (isbn, author first name, author last name,
    book title, price, title, type)</font>


    Customer (customer #, title, first name, last name, address
    line 1, town country, post code, card type, card number expiry date, isbn) </font>



     
  5. DEmsley

    DEmsley New commenter

    @msa969 Your systems above would only allow a single CD/Book per order.

    Why do you have the CD Number in the Customer table?


     
  6. djphillips1408

    djphillips1408 New commenter

    And an isbn in the customer table of second example. Mr Browns assertions for structure above are pretty much spot on but personally I would take the card details out of customer table to allow for storing more than one card per customer.......
     
  7. I think maybe the Order Line should have an "order line id" unique key. It is probably not practical to use an ISBN as a key (a) because it's non numeric and (b) it would make extending the system to something that is sold that isn't a book complicated ; effectively you would have to fake an ISBN.
     
  8. djphillips1408

    djphillips1408 New commenter

    But it IS an ordering system for books. Why add fields I'm when they are not required and the compound key works just fine?
     
  9. djphillips1408

    djphillips1408 New commenter

    But it IS an ordering system for books. Why add fields In when they are not required and the compound key works just fine?
     
  10. djphillips1408

    djphillips1408 New commenter

    And by having a compound you instantly get the validation that you can't order the same book twice on the same order. If you run with order line id then you are going to have to set up an index as well
     
  11. gedlad

    gedlad New commenter

    The Link Entity OrderLines( <u>OrderNo, ProductCode</u>, qty) would be standard. A fag packet sketch of the ERD would justify djp's design. It refines a relation Product to Order, into two functions Product to OrderLine and Order to OrderLines. That's what Link Entities do.
     
  12. colwynexile

    colwynexile Occasional commenter

    Are these the data sets for Unit 2? If they are,then there's no quantity field to them and is therefore a three table RDB; Customer - 1:M - Order - M:1 - Books (assumed you only buy 1 book at a time)
    But to get full functionality you would need to have a link entity such as gedlad produced. By using this compound key, you can order a number of different books of one order, with the compounding of the order and the ISBN of the book being unique to form a primary key.
     
  13. msa969

    msa969 New commenter

    Are these the data sets for Unit 2?
    Yes these are the data sets for Unit 2.
    So should I stick with a 3 table RDB ?

    Thank you in advance.
     
  14. I would work with with just the data and headings in the data sets given. Not great or technically correct but get the pupils to evaluate the issues of only having 3 tables and using compromised primary fields. In Unit 7 they are not expected to add new fields that don't utilise the data sets provided. The important aspect of this unit is documenting carefully the validation and the queries - if my moderation feedback is to be believed.
    Regards
     
  15. Errr.... well because its a string type and a compound index and will be less efficient for both reasons, and because you are relying on a specific format (ISBN) as a key.
    As an example, suppose in the 60s or whenever car registration AAA111A came out, you would have issues when they were reversed to be A111AAA and potential chaos when it became AA111AAA.
    Also the third, it locks your ordering system into a specific product and means you couldn't change it to some other system which is dim from a sales POV.
    Also the fourth, your target company may expand and start selling something that doesn't have an ISBN (magazines ? ebooks - differentiating ?) , what do you say, sorry, you've got to make one up ?
    I am looking at it from a real world POV. No-one normalises from first in the real world, of course !

     
  16. No you don't if the ISBN has a checksum which I'm pretty sure it does, so you'll have to validate every ISBN entry anyway .... and you are likely to do a primary key look up on book far more often than you validate it.
     
  17. djphillips1408

    djphillips1408 New commenter

    You are ceating a scenario to justify your undoubted talents, the poster does not ask for this. What I (and a number of others on this thread) have displayed is the smart way to do this until with students.

     
  18. djphillips1408

    djphillips1408 New commenter

    You are missing the point, by using a compund the same book can not appear twice in the same order. I don't care about the checksum and a simple combo box on the order form for the isbn (and any other fields form the book table,along with an unbound textbox or two to verify the chosen book) would be a far more appropriate solution for this KS5 task
    If you want I will do you a video [​IMG]

     
  19. Interesting point. Not sure complex primary keys are a good idea in any scenario :)
     
  20. How do you know the same book doesn't appear twice in the same order ? If a large organisation has several subsections that are ordering books that are ordered in one large order there may be 2 lines with 1 copy of each book rather than 1 line of 2 copies of the book (for example)
    I do agree with the general point. Having said this, if education is more about mechanically processing children through exams (and yes, this is debatable !) these things perhaps should be up for discussion if not implementation.
    Mind you, given my experience with providing a sample answer (got bombarded by emails from pupils asking how it worked and how do I memorise it) it's a farce anyway.
    You should. Same thing. In a sane world this would be an extra for advanced students.
    Well, if it was marked sanely (which they apparently aren't) then this should be a negative mark :)
    .

     

Share This Page