The second homework assignment asks you to build a database of sensors. I’ll be showing an example in class of building a Database that is aimed at recording measurements of any type in rooms of buildings, using sensors from a list that you’ve created. It doesn’t exactly meet the requirements of the assignment, but should give you a good headstart on how to complete the assignment. In some ways it goes well beyond the assignment requirements, though it certainly does not illustrate all of the capabilities of MS Access.

Videos of making the database
There are five videos with a total length of 45 minutes that show you the steps.

The specific steps of the process are as follows – with links for each:
  • Note that these videos are for the 2007 version of Access so there may be some menu changes, but the basic steps are the same
  • Note too that these videos cover more than the requirements of the assignment.

Description & Link

Length

9:38
5:44
11:01
11:12
7:48

The purpose of the database

It’s always important to have a clear goal for a database, particularly a relational database. Professionals have a well-developed method for defining the elements of database that relies on a mathematically rigorous theory, but that isn’t necessary for our fairly simple one.
In our case I define the goal as being able to record the following about a measurement:
  • The measurement reading – a number
  • The date and time it was taken
  • The room and building in which it was taken
  • The specific type of sensor used including: model & manufacturer (for your assignment you’ll want to extend what you record about the sensor greatly)

How the Database is Set Up

What is minimally necessary for a reasonable database to address this goal are four tables that have "relations" between them. You could easily increase the number of tables (e.g. for "categories" of sensors and/or buildings and rooms) but this should be sufficient. Those tables are:
  • Building
  • Room
  • Sensor
  • Measurement
The relation between these tables (you can see the fields too) is:
20100208%20IB%20Access%20Relations[1]
When you’re creating a database design it’s useful to think of what your goals are (in this case a record of measurements) and then work out from there to think about what is necessary to record for each measurement and how can I be sure that I enter the basic information only once.
Note: A classic problem with Database design is to have "circular" relations (e.g if you put a "room" "key" in the Sensor table and connected directly from the Room Table to the Sensor Table). There are techniques for dealing with this (usually adding another, intermediate, table).

Jim Mitchell