Chapter 3, Customize, Analyze and Summarize Query Data

• The last chapter was about designing databases.
  • That is a fairly daunting topic, which probably requires more training than you have been given.
  • On the other hand, it is likely that you could have a database person set up a database, and you maintain it.
• This chapter focus more on using an existing database
  • More specifically, the database has been designed and contains data
  • You wish to use the data in the database to produce information
• When you start with a database someone else has defined, you should have some sort of definition document.
  • It should somehow justify the tables
  • It should define what data is stored in each table and the restrictions on that data.
  • It should show relationships between the tables.
  • It might not exist, if so, and you are in long term control of the database, you should create one.
• For our project today, no such document exists, so we should check out the database.
  • I like to start with the relation diagram access produces.
    • There are other terms for this
      • ER-Diagrams
      • Crow's Foot Diagram.
  • Whatever you call it, and whatever form it is in, it provides a quick overview of the database.
  • These can become quite complex
  • 
  • But in our case it is not too bad.
  • 
• For today
  • Based on the story on the first page of the chapter and the diagram
    • There is a table of real estate agents (Agents)
      • Primary Key - AgentID, most likely a surrogate key in another table.
      • Other information is just text.
      • 
    • There is a table of subdivisions
      • Again there is a ID field
      • This seems to identify amenities in a given neighborhood.
      • Everything but the ID is text.
      • 
    • There is a table called Lists
      • It seems to be where the listing for the property was found.
      • This probably needs some better documentation.
    • There is the big table of properties.
      • Again a unique id for key.
      • Relations with all other tables.
      • Many different types of fields here.
  • Notice, you can add a description to most table fields.
  • We can probably work from here, however.
    • But this step is fairly important, understand what is in the database before you try to do anything with it.
• Hands on exercise one shows us how to do computations on fields in a query.
  • This is fairly straight forward
• Hands on exercise two discusses the expression builder
  • This adds a large range of functions to our computations.
• Hands on three introduces aggregate functions.
  • This allows us to aggregate (or group) by any columns marked group by.
  • Computations such as sum, average, max, min, or user defined can then be don on all data in the group.
  • Explore a grouping by Sales Person, number of rooms, count of listings and average price.