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.

A2b–Required Sensor Information

Question:

I am looking up sensor information to put into my database and, as you said in class, there is a lot of information that is not available on the manufacturer's website. Do we need to find sensors and manufacturers that give us ALL the information to fill in our fields in our database? or can we indicate in some way that this information was not available in some cases?

 

Response:

You do NOT need to find sensors that have all the information – that’s quite likely impossible.  Leave blank the info you cannot find or put in something like “unavailable”.  What these gaps will illustrate is how difficult it is to find the “right” information.

A2b–Sensor Types

Question:

For the sensor entries, are we entering specific sensors? For example, the manufacturer name and model number of the sensor. Or are we just entering genres of sensors, i.e thermocouples or infrared laser?

Response:

Specific sensors with as much information as possible about manufacturer, characteristics, etc.

A2b - Building Systems Question

Question
I was looking over the rubric and noticed a section regarding the sensors addressing the building systems. I could find no further explanation of this in the assignment details. Is this referring to sensor fields that address the building systems being monitored by the sensing device?


Response
My intention is that the types of sensors you choose address the needs of the different building systems:  Structures, HVAC, Electrical, Plumbing etc.

P3, P4 Paper Formatting

Question

I was wondering if our paper should be formatted as a formal research paper or is this more user defined.

Answer

What I care about is that you’re clear, organized and thorough.  That includes citing references and being careful to put in quotes any words taken from another source, including something you’ve written for another purpose (e.g. Senior Design document).  A bibliography is appropriate.

I guess that adds up to a research paper, but the specific format is not critical.

A2a & A2b Clarification on Requirements

I discovered today that I was inconsistent in what I asked for A2a.  The grading form expected a word document describing what you'd done and problems you have, but that was not made explicit in the assignment itself or in what I said yesterday in a blog post.  I'll stick with the lesser requirement this year.  You do not need to provide a Word document for A2a,  everyone who turns in the assignment will get those points automatically.

Today I updated the assignment to reflect this clarification.  I also made it explicit that I do expect a word document for A2b. I also updated the grading form for A2b to reflect this expectation.  The total points are not changed.

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.

A1–Submission Requirements

Question:

If we are doing the project for advanced users we also need to create the model of a house?

Response:

Yes, you need to create a simple “project”, not necessarily with a roof or lots of partitions, etc.  The goal is to show that you can load the family you created and then use it in a project, including creating a schedule (furniture) that shows you the number of instances of the different types you’ve put in the project.  To do that I ask that you create a worksheet and a PDF of it as you did in the beginner’s tutorial.

Revit Assignment Question

Question:
Can you please give me some more information on what "Your continuous wall is = 10 x the number of letters of your last name (e.g. Mitchell -> 8x10 = 80') - a dimension and a note showing the computation indicate this on the "sheet"" corresponds too?  I was working without sound on the video so I am not sure if there is a part where we are told to make the wall with a length related to our name.

Response:
The goal here is to make certain that you do indeed make a wall (It doesn't absolutely have to be the longest wall) a length that varies from student to student.  I'm asking you to put a dimension on that wall in the "sheet" and a note on how you calculated it.

My tutorial wasn't created specifically for this class (and indeed goes further than the assignment requirements) so it probably doesn't address this question specifically.

Term Project Questions

Questions

1. I was unclear about what is due next week for the term project.
2. 1. Do I choose a broad topic or is it more specific?
3. 2. Is this supposed to be a four page research paper with an 8 minute PPT?

Response #1

• A very short submission by each person to VBB containing
• Teammates – if any
• Topic – a sentence or two

Response #2

• The more well defined (narrower) the topic the better.  You’ll need to narrow down a general topic to get anything meaningful at the end, but I’m not insisting on super-specific at this stage.

Response #3

• You are welcome to make it a research paper OR a project of some sort with a document that comments on the project.  The project could be of many types:
• Extension of your BIM capabilities – e.g. Macros
• Building something using 3d Printing
• A video on a related topic
• A website addressing some topic

Question About Reading Assignments

Question: 
 I was going over the the assigned readings and I just wanted to clarify the requirements of the reading assignments.We don't have to submit the assigned reading to the reading submission, only the article that we find on our own. But we have to mention the assigned reading and the article we found in our blog post each week? Let me know at your earliest convenience.

Response:
You are correct.