CIS-3010 Lab Assignment #4: Temperature Database

Due: Friday, March 8, 2019

Reading:

In this lab assignment you will design a new database and develop an Entity-Relationship (ER) diagram for it using some suitable tool.

Imagine a wireless sensor network distributed over the campus with small, battery powered nodes in each room. The nodes contain a temperature sensor and make periodic measurements of the temperature (for example, every 15 minutes). Those measurements are forwarded over the network to a base station where a program gathers the data and inserts it into a database for future analysis. The goal of this lab is to create that database.

Data Requirements

Consider the following requirements:

  1. Each sensor should have a unique ID. If a sensor is replaced (because it fails) the new sensor should have a different ID. Sensors might not all be deployed at the same time; some sensors might be in storage. Information about a sensor should be kept "forever" since, even if a sensor is thrown out there might still be readings made with that sensor in the database.

  2. Each deployed node in the network has a 16 bit node address (range from 0 to 65,535). These addresses are used by the network protocol that connects the sensors together. Incoming data packets are stamped with the sender's node address and software will need to look up which sensor is currently deployed at that address. A sensor's node address is different than the sensor ID because, for example, if a sensor is replaced it (may) use the same address as the sensor previously at that location. Furthermore only deployed sensors have node addresses.

  3. Information about sensor vendors should be stored (for example: name, mailing address, web site URL, support phone number). Which vendor each sensor comes from should also be recorded. Sensors also have a model number that should be recorded.

  4. Information about deployment locations should be stored separately. Location information should include room number and a short description of the room. For sensors that are deployed, their current location should be recorded. Because the locations that are in use may vary over time, information about old locations used should be retained even if no sensor is currently deployed to such a location.

  5. Information about deployments should be kept "forever" since even if a sensor is currently in storage or even trashed users might want to track which sensors were ever deployed to any particular location. Deployment information should also include a start date and end date for the deployment. Be sure it is easy to look up which sensors are currently deployed.

  6. The actual data should store the sensor used to make the reading, the location where the reading was taken, the temperature, and the date and time of the reading. Note that some of this information might be available in other tables; try to avoid duplicating information unnecessarily. For example, the (sensor, location) information might also be in a "deployment" table. Sensors might take readings fairly frequently (every few minutes) so enough temporal resolution is needed to capture that. Keep in mind that sensors might be moved or taken out of service and then later redeployed. It is important to be able to track which data was taken with what sensor at what location. For example, if a sensor is later discovered to be defective, you might want to purge its data from the database regardless of where it was ever located.

Start by creating an ER diagram (for example using SSMS, but another tool would be fine) to model the data. Focus at first on the entities involved and the relationships between them (the conceptual model). Once you are satified with the conceptual model, add attributes to the entities as appropriate (the logical model). Finally, decide on specific data types and constraint checks (especially non-NULL constraints) as appropriate for the DBMS we are using. The result is the physical model. You may find it convenient to implement the physical model as an SQL script using CREATE TABLE statements, but if your diagramming tool is powerful enough you might be able to implemented it directly in the tool. You may also find that it is easiest to blend the logical and physical model into a single step (SSMS will encourage that).

Although we will talk about database normalization soon, for now focus on using your intuition to reduce the duplication of information in your design. If some data needs updating, for example to make a correction, there should be only one place in the database where the change is necessary. Don't worry if you end up with some needless duplication of information; after we discuss normalization you'll be able to transform your database into one that is (largely) free of any update anomolies using a "cookbook" normalization procedure.

Submit your ER diagram and, if you created one, your SQL script. (20 points).


Last Revised: 2019-02-14
© Copyright 2019 by Peter C. Chapin <pchapin@vtc.edu>