A2b - Databases - Grading Comments

The grading is complete for all A2b submissions except those who received an extension.  Overall most people did well, though some comments are worth making.

• Links
• Those of you who just followed the tutorial had no problems with links.  Those who struck off on their own (admirable) had more problems.  The subject of the different kinds of links and how to make them is not intuitive for most of people, myself included.  It takes work and mistakes, and does eventually come clear.
• Dropdown Boxes
• I didn't want to make dropdown boxes required because it was one more layer of complexity.  Nonetheless they are almost essential in practice for data entry into forms.  One creates them using a "Combo Box".  There are reasonable explanations in the help files.  Without them one has to keep referring to other views (tables or queries) to determine what is the appropriate ID #.
• Extending Access
• As my comments about links and dropdown boxes indicate, to become a proficient database users takes considerable work.  
• Planning initially is extraordinarily important.
• Learning to import data is very helpful - one person did so.
• There are multiple types of queries, many of which are very helpful.
• To get really proficient you need to learn to use VBA - the programming language (:
• Sensor Information
• As I said in my comments to many of you, one of the purposes of this assignment was to give you a gut sense that picking the right sensor isn't as easy a process as one might think.  Manufacturers very often don't provide the information you know is important.  Tracking that down can be a pain and sometimes impossible.  In a work situation, a call to the representative is often the best solution.
• A related issue is how you set up your sensor information table.  Most of you had only a few fields in it.  In fact there's no problem in having many fields, with only a few fields being appropriate to each sensor type.  You can generate good reports by using a query that displays on the relevant fields.  You can also write queries that concatenate info from different fields or leave it blank if there's nothing in a field.
• Sensor Characteristics by Building System
• No one really tackled the criteria addressing whether there were differences between sensors by building system (structure, HVAC) etc.  The kinds of things that might have been addressed here are comments on:
• Size
• Ease of installation
• Appropriate frequency of measurement
• Other Databases
• There are many databases available - I used Access because you all have it with Office (though in future that won't be true thanks to Microsoft's business relationship with Drexel).
• My own favorite (Mac and PC) is FileMaker.  Unfortunately it's expensive, an expense I've found worthwhile because I do so many things with DB.
• Open Office (I prefer the LibreOffice version) has a free DB modeled on Access, but it's been buggy for years and doesn't operate the same way.  The latest LO version seemed pretty good to me when I tested a week ago, though it has some different interface design approaches.

A2b–Detailed Access/Query Question

Question:

In class last Tuesday, we created a list of many, many sensor "fields", and then broke them down based on major characteristics, including Physical properties, Measurements, Communication, etc... I know you said that we would not need to include all of these, and at this point, it is not my intention to. However; I have a question regarding how I might go about including a significant number of them. Hopefully I can explain it clearly enough that you have some context...
My intention is to have one 'Main' sensor table, with Primary Key, Manufacturer, Model, Cost, Parameter Measured, Output Units, Sensor Comments, Date of Installation and Specifications (a link to URL). In addition, I would like to have other tables showing: physical characteristics, additional measurement characteristics, communication, time, power, of each sensor. Each separate table would be comprised of 'fields' relating to those specific characteristics.
Now my question is: what is the best/appropriate way to do this? I have identified two ways that this might be done, but I am not sure which or if either would work:
1. 1 Query -> Multiple Tables
If I create these multiple tables, and then one query (and subsequent form) where I enter the data for every field of each sensor as I enter it into the database, will this in turn populate the tables with the information that each requires (so long as the necessary relationships exist)? I tried to do a simplified case of this, using players for a sports roster, broken down into the positions that they played. I entered all the players, such that each had a Primary Key, and also attached to them a Position Key, both of which were linked to the separate tables for position. However, when I entered all the players, it didn't populate any of the position tables. So this didn't seem to work, though I am not 100% certain that I did it correctly, so I don't want to rule it out completely.
2. 1 Table Filled By Multiple Queries
This was what my initial thinking was. It seems to make sense based on my somewhat still limited understanding of what the query actually does. Logically, if I have one table with all the information for each sensor, then I can have a individual sub-queries that provide specific packets of information, corresponding to the distinct categories we identified (physical, power, time, etc...). However, my experience with Access tells me that the table/tables are populated by information that you manually enter through a form, which is often linked to a query. In this case, for each characteristic query (physical, measurement, power, etc...) to populate column values in the Sensor Table, it will need to be related to that table by a "Key Sensor - Sensor ID" relationship. But wont this create issue if I call out Sensor ID in potential 3, 4 or 5 different queries? Or is database smart enough to take the information from each query and attach it to the correct column places in the Sensor Table.
My biggest confusion I think is my lack of strong grasp of what the query does. It seems to me that this is the mechanism that populates the tables in the database. But logic suggest that they would be how you can request specific groups of information, like Measurement, Sensor, Room, Building or Building, Date, Sensor Type, all corresponding measurements. If the later is true, then that suggests the 2nd option above would be the best, since I am creating queries based on what groupings of information I would like to gather from my database. On the other hand, though, if I am using these queries to populate the database, wont I run into issues of overlap and frustration (overlap wont actually occur since referential integrity would restrict it)?

 

Response:

I’m answering rapidly without having read every word of you question, in order to get something off early.

A query is PRIMARILY a method of drawing together from a carefully thought-through set of tables. Yes, it can do important other things such as updating records, deleting and appending them, but you always start with the tables.

What I think would address your approach is the following, which is what I showed in class using the FreeMind diagram of table relationships.

To be thorough you need two tables for sensors (minimum)

#1 has the information for all sensors of that type – model number, electrical power…. – Primary Key IDSensorType

#2 has the information for a specific instance of a sensor – e.g. the one in building #3, room 342, northwest wall – Primary Key IDSensorInstance + Foreign Key IDSensorType

#2 is linked to number #1 via the ForeignKey as a many-to one-relationship

You CAN have multiple table linked through 1:1 relationships that break a big table into multiple smaller ones, but for our purposes there really isn’t any need to do so.

If you set things up this way and get your links working properly, then a query can display information from ALL the linked tables simultaneously. Putting information into that query in each field will put information into the appropriate fields automatically.

A2a - Questions on Database Deliverables

Question 1

What deliverables are required for the rough draft?

Answer - Just the working database, as stated on the assignment.

 

Question 2

Can the file be in a OpenOffice format?

Answer  -Yes, though I warn you it’s less stable than Access.

 

Question 3

What is needed for the word document, if necessary? 

Answer – No word document is required – though it will be welcome to read what you’ve learned.

 

Question 4

Do we need queries and, if so, how many?

Answer – Queries are not required, but certainly welcome.  A great deal of the power in databases comes from using queries

Fields in MS Access

Question:

I am starting to work on the database assignment and am confused by what you mean by the "fields" to describe the sensor, as well as the "records" for each sensor. I have watched the videos you posted to learn how to use MS Access, just am confused by the actual terms of the assignment and what to put in to our database.

Response:

Every table has at least one, usually multiple, “fields”.  They are the equivalent of columns in an Excel spreadsheet, and will be created that way if you import an Excel spreadsheet.

Similarly, the rows of an excel spreadsheet correspond to the individual records of a table, and will be imported that way.

MS Access Tutorials

I’ve looked for tutorials that will help you learn about creating a database.  Here are the results of looking at what a Google Search turns up, plus what you can find from Microsoft.

Tutorial	Comment	Time to Use
Quackit.com Access Intro	Very basic step-by-step.  Does not deal with relationships, but does walk you through creating a table, form and quary.	30 min
M. Brydon Tutorials	This looks like a pretty good textbook-like set of 15 tutorials with supplied sample database and other support files.  It deals with concepts and planning, not just step-by- step.  It’s based on an older version of Access so the menu references will be somewhat different, but the principles are the same. Note that you’ll want to use the support file univ0_v7.mdb, not the earlier version.  You’ll have to OK upgrading it to your version of Access, which may give an error, but not a serious one.	Hours
Microsoft’s Access Support Site	There are multiple tutorials on this site, most of which are pretty good.  It’s where I’d start.	Hours

Resources Current Through 2/8/2012

I’ve created updated printouts (PDF) of all the resources that you’ve entered so far as part of the weekly readings, organized by Topic, Content type, Source Type and title.  They are:

• Full Version – 98 pp
• Brief Version – 9pp

These reports are generated from an MS Access Database that I created.  Its updated by downloading the information from the Google Docs into a “Table” and then using “related Tables” and a “Report” to make it all organized.   We’ll discuss these in class in Week-6.

Resources Printout–Through 1/26/2012–From MS Access

All your entries so far for the resources that you’ve submitted are now in this 55pp PDF.  I’ll update it as we move forward

• Here’s a short version that can serve as an index
• It should be useful for those of you working on BIM projects
• It’s an example of how one can manipulate information in a Database
  • I downloaded the information from GDocs into Excel
  • I then put it into MS Access and converted it to a relational database using the “analyze” tool in Access.
  • I produced a “report” to print it in this form