More Chapter 4

• Designing Read-Only Databases
  • He states that you build read-only databases
    • For BI systems
    • reporting
    • Data-mining
  • Such databases do not need to follow normalization processes
    • They are not updated.
    • So they will not suffer from anomalies
  • He suggests that a nice big join will make a nice useful table.
  • The data you are starting from with homework five is an example.
• Common Design Problems
  • Multivalue Multicolum problem.
    • He gives multiple examples:
      • One person multiple phone numbers, children, cars, ...
      • He suggests that multiple fields such as the following are bad
        • PERSON(pid, name, phone1, phone2, phone3)
        • PERSON(pid, name, child1, child2, child3)
        • PERSON(pid, name, car1, car2, car3)
      • They limit the phones to three, or require a major database change.
      • Queries get ugly
      • Waste space (what about people with no children)
      • Are inconsistent, where do you draw the line.
    • Solution: Make a table
    • Problem, it might be slow.
  • Inconsistent values
    • This is one of the banes of my existence.
      • Corn, large can
      • CORN, can, Large
      • large can corn
      • Korne, kan, lrg
      • ...
    • Mostly handled in referential integrity checks for foreign keys
    • But what about other data

      SELECT SKU_Description, Count(*) AS Number 
          FROM SKU_DATA
          GROUP BY SKU_Description
      
      SELECT InstitutionName , COUNT(*)  AS Number 
           FROM COLLEGE_RAW 
           GROUP BY InstitutionName 
           HAVING Number < 5
           ORDER BY Number DESC;
      	    

    • In the second one, the onesies will be at the bottom
  • Missing Values
    • He discusses NULL
    • It can have three different meanings
      • The value was never provided
      • The value was invalid and changed to NULL
      • There is no value.
    • You can not tell when a field is null which is true.
    • He gives an example of a patient at a clinic, "DateOfLastChildborth"
      • NULL for men - can not have a value
      • NULL for women who have not yet given birth
      • NULL for women who have given birth but were not asked

    • SELECT COUNT(*) 
          FROM ORDER_ITEM
          WHERE Quantity is NULL;
      	    

  • General purpose remarks or notes
    • A field stored as text
    • Just a scratch or note column
    • Snarky remark about spreadsheet users.
    • The columns will be used inconsistently
    • And may be used to store data that should be in a "real" column.