Due: Friday, March 3, 2023
Reading: There are links on the class home page (in the section on Database Graphical Tools) that describe crow's foot notation for entity-relationship diagrams. Information about normalization can be found in Section 6.2 of the text.
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 10 minutes). Those measurements are forwarded over the sensor 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.
Consider the following requirements:
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.
Each deployed node in the sensor 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 (the network does not use IP). 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 from the sensor ID because, for example, if a sensor is replaced, the replacement (may) use the same address as the sensor previously at that location. Furthermore, only deployed sensors have node addresses. Sensors in storage do not have node addresses.
Information about sensor vendors should be stored (vendor name, mailing address, website URL, support phone number). Which vendor each sensor comes from should also be recorded. Sensors also have a make and a model number that should be recorded (as strings since they might contain letters). It is not necessary to store information about the manufacturers of the sensors, only the vendors who sell the sensors. However, it is understood that in some cases the manufacturer might also be a vendor.
Information about deployment locations should be stored separately. Location information should include a building name (a string), a room number (also a string since it might contain letters), 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.
Information about deployments should be kept "forever" since even if a sensor is currently in storage, or thrown away, 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 (precise deployment times are not required). Be sure it is easy to look up which sensors are currently deployed.
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; 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 would be typical) so enough temporal resolution is needed to capture that. However, sensors would never take multiple readings per second.
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.
Proceed as follows:
Start by creating an entity-relationship (ER) diagram using crow's foot notation. You can use a diagramming tool if you like, or alternatively create the diagram on paper and take a picture of it for submitting with your lab report.
Focus at first on the entities involved and the relationships between them (the conceptual model). Once you are satisfied with the conceptual model, add attributes to the entities as appropriate (the logical model). Be sure to indicate which attribute(s) form the primary key. You do not need to include any more information in your diagram. In particular, you don't need to include any of the data types for the attributes (although you can if you would like).
It is desirable for all the tables in your database to be in third normal form. After completing the logical model, review each table to verify that it is normalized. If there is even one table that is not normalized, you should either a) revise your design until all tables are normalized, or b) come up with a justification as to why you think it is okay for one or more tables to not be normalized.
Next create an SQL script with appropriate CREATE TABLE statements that will construct the necessary tables implementing your design. Here is where you decide on specific data types for the attributes and constraint checks (such as CHECK constraints and non-NULL constraints). Also be sure to include appropriate foreign key constraints.
Include as comments in your script any information you want to add about normalization. In particular, if a table is not in 3NF, why is that justified?
Submit (possibly a photo of) your diagram and your SQL script in a zip archive to Canvas. (20 points).
Last Revised: 2023-02-13
© Copyright 2023 by Peter C. Chapin <pchapin@vtc.edu>
