Assignment 2: From One Room Kit to a Campus Sensing System

12-778 — Sensors, Data, and Models in Engineering Systems

Author

Mario Bergés and Codex

Published

September 22, 2026

Why we are doing this

Assignment 1 produced a working room-sensing kit and a folder of measurements. A facilities manager needs considerably more than that. Before measurements from hundreds of kits can guide lighting, maintenance, or room-service decisions, we must know what each number means, whether the measurement is credible, where and when it was collected, and how to retrieve it without opening hundreds of CSV files by hand.

In this assignment, you will take the next step from a prototype toward a sensing system. First, you will return to your own Assignment 1 light measurements and attach an uncertainty to a result that was previously only nominal. You will then reprogram the kit to use changes in lamp intensity as a virtual measurement of grid frequency. Finally, you will combine your measurements with a synthetic campus deployment, design and implement a relational database, answer facilities questions with SQL, and decide how the resulting data should be stored.

The assignment follows one question across the SENSORS and DATA portions of the course: What has to happen between a sensor producing a number and an engineer being able to trust and use it?

Learning objectives

By completing this assignment, you should be able to:

  • Design and implement a finite-rate acquisition suitable for a stated frequency measurement.
  • Apply the FFT to estimate a physical frequency and explain how sampling, record duration, leakage, windowing, and sensor dynamics limit the estimate.
  • Quantify and propagate Type A and Type B uncertainty in measurements from your room-sensing kit.
  • Clean, align, aggregate, and label time-series records without erasing gaps or overstating timestamp accuracy.
  • Translate requirements for a fleet of sensing kits into an ER diagram and a normalized relational schema.
  • Implement and populate that schema in SQLite and answer facilities-management questions with SQL.
  • Compare storage representations and compression results, then recommend a defensible format for a campus deployment.

What you receive

  • the hardware and Assignment 1 measurements from your room-sensing kit;
  • assignment02_logger.ino, an ESP32 acquisition shell with focused sections for you to complete;
  • synthetic_signal.csv, a known-frequency record for checking your FFT analysis;
  • campus source exports describing rooms, kits, deployments, slow observations, and optical-frequency attempts;
  • a data dictionary for those exports;
  • uncertainty_inputs.pdf, identifying the specifications and teaching assumptions for Task 1;
  • Python scaffolding for reading the files, working with SQLite, and measuring file sizes; and
  • an editable Word submission template and a PDF copy.

The campus data are synthetic. They represent a sample from a hypothetical deployment and contain deliberately planted gaps, duplicates, invalid values, and deployment changes. They are not measurements from CMU rooms. Your Assignment 1 log and new optical captures are real measurements, and your database must preserve that distinction.

Download the starter archive from Canvas. Begin with README.pdf; data_dictionary.pdf defines the time and coverage conventions used throughout the assignment. These guides also come as editable Word and Markdown files. Open submission-template.docx in Word or another compatible editor and export your completed report to PDF. No Quarto installation is required. Save the downloaded assignment PDF beside the extracted starter files to use its local handout links.

The Python utilities handle file reading, plot layout, and storage measurement. You complete the acquisition, analysis, data-quality decisions, schema, importer, and SQL queries. You can begin Tasks 1–3 immediately and complete Tasks 4–6 as the database and compression lectures are covered.

What you submit

Submit one archive named andrewID-assignment02.zip containing:

  1. A single PDF containing every requested calculation, diagram, table, figure, query result, and interpretation.
  2. Your completed assignment02_logger.ino.
  3. Your untouched Assignment 1 log and untouched new serial captures, plus the processed files you imported.
  4. Your analysis and import code.
  5. Your ER diagram as a PDF or legible image.
  6. schema.sql, queries.sql, and the populated room_sensing.sqlite database.
  7. A short README.md identifying the files and the command or steps needed to rebuild the database.

Use the supplied submission template to keep the requested evidence in a predictable order. Each assessed subtask ends with an Evidence to submit line: put the requested explanation and selected results in the report, and include the named code/data files in your archive. Label excerpts and include full machine-readable results when a table is too long for the report.

State whom you discussed the assignment with and acknowledge any use of generative AI, following the course syllabus. You may troubleshoot with classmates, the TA, and the lab assistants, but you must modify your own firmware, collect and analyze your own measurements, design your own schema, and write your own submission.

WarningProtect yourself and the hardware

The experiment is entirely optical. Never open a lamp, alter its wiring, or connect the ESP32 to mains electricity. Disconnect USB power before changing your sensing circuit. Use a normal, intact lamp and keep hot bulbs and fixtures away from skin, wires, paper, and the photoresistor.

Task 1: Revisit the commissioned kit (15%)

Assignment 1 asked you to report nominal photoresistor resistances. Those values looked precise because they had several digits, not because the measurement chain justified those digits. Here you will attach an uncertainty to one result and use it to decide what your kit can actually distinguish.

1.1 Define the measurand and the evidence (3%)

Choose one Assignment 1 illumination condition with three repeated trials. State the measurand precisely: the photoresistor resistance inferred from the mean measured voltage under your documented condition. Use the equally weighted mean of the three trial means; this is not the same estimator as averaging sample-by-sample resistance estimates through the nonlinear inverse model. Identify:

  • the number of samples in each trial;
  • the mean ADC code within each trial;
  • the within-trial standard deviation; and
  • the mean and standard deviation of the three trial means.

Explain which scatter describes repeated ADC readings under nominally fixed conditions and which describes your ability to reproduce the condition. Do not treat every sample in one trial as an independent replication of the lamp placement and room conditions.

Evidence to submit: your measurand and estimator, a three-row trial-statistics table, the mean and standard deviation of the trial means, and a short interpretation of the two kinds of scatter. Identify the source log and trial labels.

1.2 Construct and propagate an uncertainty budget (8%)

An uncertainty budget is an accounting table showing where uncertainty enters a measurement and how much each input contributes to uncertainty in the final result. It connects the source evidence (a repeated-reading statistic or an assumed bound), the input’s standard uncertainty, and the measurement model’s sensitivity to that input. This lets you see both the total uncertainty and which part of the kit is worth improving.

Use the Assignment 1 model

\[ V=gQ\,\bar c, \qquad Q=\frac{3.3\ \mathrm{V}}{4096}, \]

and

\[ R_L=R_f\left(\frac{V_s}{V}-1\right), \]

where \(\bar c\) is mean ADC code, \(R_f\) is the fixed resistance, \(V_s\) is the divider excitation, and \(g\) is an uncertain voltage-conversion multiplier with nominal value 1.

Use the relevant guidance and component assumptions in uncertainty_inputs.pdf, included in the Canvas starter archive, together with Lecture 7.

The sheet defines the input bounds and distinguishes specifications from teaching assumptions. Keep \(Q\) fixed when propagating uncertainty in the physical supply \(V_s\). Your budget must contain:

  • a Type A contribution equal to the standard deviation of the trial means divided by the square root of the number of trials; do not add within-trial scatter a second time;
  • the fixed-resistor tolerance read from your resistor or supplied by the instructional team;
  • uncertainty associated with converting raw ESP32 ADC code to voltage;
  • uncertainty associated with the divider excitation; and
  • a clear decision about quantization: include it separately only if it is not already represented by the observed code scatter.

For each component, identify whether it is Type A or Type B, state the original bound or statistic, state the assumed distribution, convert it to a standard uncertainty, and compute its contribution after propagation. State any independence assumption. Report:

  1. the nominal \(R_L\);
  2. the combined standard uncertainty \(u_c(R_L)\);
  3. the expanded uncertainty \(U=2u_c\); and
  4. the result as \(R_L \pm U\), rounded consistently and labeled with the coverage factor \(k=2\).

State the distribution and degrees-of-freedom assumptions behind that coverage statement. With only three replicated trials, do not describe \(k=2\) as an exact 95% interval.

You may perform the propagation in Python. Submit a table showing the budget rather than only a final number. If your chosen operating point is clipped or so close to a rail that the inverse model becomes unstable, explain the problem and choose another Assignment 1 condition.

Evidence to submit: the budget table, sensitivity expressions or calculations, the four reported results above, coverage/independence assumptions, and your quantization decision. Include the calculation code or worked calculations.

1.3 Make a design judgment (4%)

Here, distinguish between two conditions means resolve a difference between their inferred photoresistor resistances despite measurement uncertainty. Choose a second Assignment 1 illumination condition and summarize its three trial means. Use the shared-input difference model in uncertainty_inputs.pdf to report \(D=R_{L,a}-R_{L,b}\) and \(D\pm2u(D)\). The same resistor, supply, and ADC multiplier affect both conditions; count each shared uncertainty once in the difference model.

If this interval excludes zero, the comparison supports a nonzero resistance difference under the stated model and coverage assumptions. If it includes zero, the comparison does not resolve a difference at that uncertainty level; it does not prove the conditions are identical. Overlap of the two individual resistance intervals is not by itself a test of distinguishability, and this comparison is not a calibration in lux.

Identify the largest contribution to your budget and propose one concrete design change that attacks it. Estimate how the combined uncertainty would change if that contribution were removed or reduced as you propose. More digits and more samples are not acceptable improvements unless they address the dominant term.

Evidence to submit: the second condition’s trial means, your shared-input difference calculation and interpretation, and a before/after uncertainty comparison for one proposed design change.

Task 2: Build an optical grid-frequency measurement (25%)

The Assignment 1 logger sampled at 4 samples/s, which was adequate for slow room changes but incapable of resolving the variation produced by the electrical grid. You will turn the same photoresistor channel into a virtual frequency sensor by changing both the acquisition and the analysis.

2.1 Design the acquisition (5%)

For an ideal resistive light source driven by

\[ v(t)=V_0\sin(2\pi f_g t), \]

the instantaneous power is

\[ p(t)=\frac{v(t)^2}{R} =\frac{V_0^2}{2R}\left[1-\cos(4\pi f_g t)\right]. \]

The power, and therefore the strongest ideal flicker component, varies at \(2f_g\). Real lamps and photoresistors have dynamics, so this relationship identifies a component to investigate; it does not guarantee that every lamp will expose it clearly.

Choose a target sampling frequency and record duration for an optical component near 120 Hz. Your design must:

  • put the target below the Nyquist frequency with a margin you justify;
  • provide a nominal grid-frequency bin spacing no larger than 0.10 Hz after dividing the optical frequency by two; and
  • fit within the limits documented in the starter sketch.

State the number of samples, sampling interval, Nyquist frequency, record duration, optical-frequency bin spacing, and grid-frequency bin spacing. Explain separately what changes when you increase the sampling rate and when you increase the record duration.

Use \(T=N/f_s\) for the DFT record duration and \(\Delta f=f_s/N\) for bin spacing. The elapsed time between the first and last regularly spaced samples is \((N-1)/f_s\), one interval shorter. The starter permits up to 12,000 samples and integer sampling intervals from 250 to 10,000 microseconds; your chosen settings must also satisfy the frequency and duration requirements above.

Evidence to submit: a table of your acquisition settings and derived quantities, with calculations and a justification of the Nyquist margin and record duration.

2.2 Complete and check the logger (5%)

Complete the marked sections of assignment02_logger.ino. The acquisition must collect a finite record on a controlled schedule, store sample indices and ADC codes, and transmit the completed record after sampling. Serial printing must not determine the interval between ADC reads.

Upload the sketch using the same board profile and photoresistor circuit as Assignment 1. For one capture, report the first-to-last sample span and achieved rate \((N-1)/\text{span}\), identify any missing or repeated indices, and summarize the read-start lateness and ADC-call duration reported by the sketch. These software timestamps approximate acquisition time; they do not measure the absolute accuracy of the ESP32 clock. Inspect the ADC codes for clipping before continuing.

The supplied logger schedules individual analogRead() calls in software rather than using the ESP32’s continuous (DMA) ADC mode, so equal spacing must be checked, not assumed. Report the minimum, median, and maximum difference between consecutive read-start timestamps and compare them with your requested interval. An average rate alone can hide irregular spacing. If reads overrun the interval, reduce the target rate while preserving your justified Nyquist margin and required duration, then repeat the timing check.

Evidence to submit: the completed sketch, one untouched capture, a timing/index/clipping diagnostic table, and a short explanation of whether the timing supports your FFT analysis.

2.3 Check the FFT analysis on a known signal (4%)

Analyze synthetic_signal.csv before treating the FFT as a measuring instrument. From its timestamps, determine \(N\), \(f_s\), duration, and bin spacing. Compute a one-sided spectrum using numpy.fft.rfft and its corresponding frequency axis using numpy.fft.rfftfreq.

Submit a small table containing the DC bin, the largest non-DC bin, and the bins immediately adjacent to it. Include bin index, frequency, real part, imaginary part, magnitude, and correctly scaled one-sided amplitude. State the known signal frequency and amplitude from the data dictionary and quantify your recovery error.

Evidence to submit: the sampling properties, four-row FFT table, normalization, frequency/amplitude recovery errors, and analysis code.

2.4 Measure the optical component (8%)

Use an intact mains-powered incandescent or tungsten-filament lamp if one is safely available. Other lamps may contain electronic drivers with different modulation. Fix the lamp, sensor position, and surrounding illumination, allow the lamp to stabilize, and document the lamp type and geometry.

Collect at least:

  • three lamp captures without moving the setup; and
  • one control capture with the sensor covered or the lamp off while other conditions remain as similar as practical.

For each capture, subtract the mean and compute an unwindowed spectrum and a Hann-windowed spectrum. State the amplitude normalization, including the window’s coherent-gain correction when comparing amplitude spectra. Produce:

  1. a representative time-series plot;
  2. a full one-sided spectrum up to the Nyquist frequency; and
  3. a close view of the region in which you search for the grid-related component.

Use the largest spectral bin in a stated search region as the required frequency estimator. Identify the peak you regard as the optical component, compare it with neighboring peaks and the control, and infer \(\hat f_g=\hat f_{opt}/2\). Explain what the Hann window changes and what it does not change. Do not interpret zero-padding as additional measured information. Sub-bin interpolation is optional and must be labeled separately from the required estimate.

If your available lamp does not produce a defensible component, submit the same diagnostic evidence, classify the estimate as unavailable, and explain why. Do not replace a failed measurement with 60 Hz. Access to a suitable lamp through the instructional team may be possible by appointment but is not guaranteed.

Use your diagnostics to investigate possible causes: a lamp driver may produce little modulation near 120 Hz or modulation elsewhere; lamp thermal inertia and the photoresistor’s response can attenuate flicker; ambient light and sensor placement can bury it in background variation; clipping can distort the waveform; and irregular sampling or an unsuitable analysis can obscure or misidentify a peak. Compare the control, timing diagnostics, code range, and spectra before deciding which explanation your evidence supports. You are not required to identify a single cause conclusively.

Use accepted when the evidence supports a grid-related component, rejected when acquisition or analysis faults make the record unsuitable, and unavailable when a usable capture contains no identifiable grid-related component. Record a reason for every decision and a missing frequency for rejected/unavailable attempts. A documented failed detection can earn full credit: you still complete the known-signal analysis, acquisition checks, spectra, control comparison, and discussion.

Identify each capture as a lamp measurement or a control. Preserve the control’s metadata and raw file, but do not count it as an attempted grid-frequency measurement in Task 5.

Evidence to submit: a setup description or annotated photograph, three lamp captures and one control with untouched raw files, the three requested plot views with an unwindowed/Hann comparison, and a per-capture decision table (lamp/control, status, frequency when accepted, reason, raw filename). Use evidence from the control and diagnostics to explain any failed detection.

2.5 State the uncertainty and limitations (3%)

Report the number of accepted lamp captures. Complete the numerical analysis below only if Task 2.4 produces at least one accepted estimate. With two or more, report their mean and sample standard deviation; with one, report the estimate and mark repeatability as unavailable. Propagate the factor of two between optical and grid frequency. Compare repeatability, when available, with the grid-frequency bin spacing. Bin spacing is the step of the required peak-bin estimator, not a complete statistical uncertainty; identical selected bins do not establish zero uncertainty.

For an accepted result, discuss at least three effects not fully captured by that calculation, including the ESP32 timebase, spectral leakage or peak-selection method, and lamp/photoresistor dynamics. Your result may demonstrate a component consistent with nominal grid frequency; without an independent reference, it does not validate small departures from 60 Hz.

If no estimates are accepted, skip the numerical uncertainty calculation. Instead, explain why no grid-frequency result can be supported, using your Task 2.4 timing checks, spectra, and control. Discuss the relevant acquisition, lamp, and sensor limitations, distinguish observed problems from possible causes, and propose one useful next diagnostic or improvement. This evidence-based explanation can earn all 3% assigned to Task 2.5.

Evidence to submit: accepted count and either (a) the numerical summary, factor-of-two propagation, bin-spacing comparison, and limitations, or (b) the failed-detection explanation and proposed next step. Mark undefined quantities as unavailable.

Task 3: Prepare the time-series records (10%)

Imagine that the university’s Facilities Management team finds your room-sensing kit useful enough to deploy across campus. They want to screen for bright rooms with no detected motion, find sensors that need servicing, and compare the availability of optical-frequency estimates across buildings. These are prompts for investigation: an uncalibrated light code is not illuminance, and a quiet PIR sensor does not establish that a room is empty.

Managing the fleet adds questions that one kit never raised. Which building contains room 101? Which room held a particular kit when a measurement was taken? Did a room have no observations because no kit was assigned, because a kit stopped reporting, or because records failed quality checks? Kits may move, some registered rooms have no kit, and every reading must retain its location at acquisition time. The system also needs to distinguish real student measurements from synthetic pilot data, retain unsuccessful optical attempts, and preserve the raw evidence behind derived results. These needs will guide your database design in Task 4 and the facilities queries in Task 5.

A database makes bad records easier to retrieve; it does not make them good. Before designing that database, you will clean and document the available records. So far, your physical kit provides the Assignment 1 light/PIR log and Task 2 optical captures and frequency attempts. The larger pilot supplies the variety needed to explore fleet management.

The starter archive therefore includes five synthetic source exports from a hypothetical pilot involving many rooms and kits:

  • campus_rooms.csv: the rooms registered for the pilot;
  • campus_kits.csv: the sensing kits owned by the facilities group;
  • campus_deployments.csv: the time intervals during which particular kits were assigned to particular rooms, including each deployment’s light-screening threshold;
  • campus_observations.csv: slow timestamped light-code and PIR-state observations; and
  • campus_frequency_attempts.csv: metadata and results from high-rate optical captures, including accepted, rejected, and unavailable estimates.

The accompanying data dictionary defines every source field, unit, domain, intended sampling interval, and candidate identifier. These files are not a proposed database schema: they are exports from separate parts of a hypothetical sensing workflow, and you will decide how to represent their facts relationally in Task 4. They deliberately include a small number of duplicates, invalid values, gaps, and deployment-boundary cases. Your job in this task is to identify those cases, make documented decisions about them, and prepare both the synthetic records and your own measurements for import without hiding their timing, missingness, quality, or provenance.

3.1 Audit the supplied exports (4%)

Use the data dictionary to check all five campus exports. Apply each check where it is meaningful: a room registry does not have a sampling interval, while an observation stream does. Report:

  • row counts and candidate keys;
  • duplicate keys or rows;
  • values outside documented domains;
  • timestamps that fail to parse or fall outside a deployment interval; and
  • gaps relative to the intended sampling interval.

Create a compact issue log giving the file, row or key, problem, and action. Preserve the untouched inputs. A correction must either be justified from other supplied evidence or recorded as a quality flag; do not silently invent a replacement value. Your cleaned output should remain traceable to its source row.

Evidence to submit: an audit summary for all five exports, a representative issue-log excerpt, and your complete issue log, cleaned files, and cleaning code. Explain exclusions, corrections, and retained quality flags.

3.2 Prepare your own records (3%)

Clean and document your Assignment 1 log, Task 2 optical captures, and Task 2 frequency attempts in a consistent intermediate format, such as CSV observations and a CSV or JSON capture-metadata file. You have not designed the database yet: these files need clear fields, units, identifiers, quality decisions, and source traceability, but need not match its eventual tables. In Task 4, your importer will map these prepared records into your chosen schema. Preserve the distinction among:

  • device elapsed time or sample index within a capture;
  • an approximate wall-clock session anchor; and
  • the time at which a derived frequency estimate was produced.

Label your records as student-measured. The five supplied campus exports are synthetic; any instructor capture is course-measured. A reset begins a new session; host receipt time is not the ADC sampling time. Assign your physical kit a new identifier that cannot collide with identifiers in the synthetic exports, and document the room or test location to which you assign it.

Evidence to submit: your prepared observation and capture/attempt metadata files, a short field dictionary, example rows, and a description of identifiers, sessions, time anchors, quality decisions, and links back to untouched raw files. Include the preparation code.

3.3 Construct interval summaries (3%)

From campus_observations.csv, construct 15-minute summaries containing building and room identifiers, deployment and kit identifiers, interval start, mean light code, PIR-high fraction, valid observation count, expected count, and coverage fraction. Follow the UTC, half-open interval, and valid paired-observation conventions in data_dictionary.pdf. Split bins at deployment changes and calculate expected counts only for the overlapping deployment period. Construct the expected intervals even when no observations exist; empty intervals have zero coverage and missing channel summaries. These summaries will be the input to the facilities queries in Task 5. Keep the cleaned individual observations as well: the summaries do not replace their evidence.

Do not fill a missing interval with zero. Do not interpolate PIR transitions, and do not carry a frequency estimate forward into intervals for which no capture exists. Your short Assignment 1 trials are organized by experimental condition rather than 15-minute campus intervals; retain that structure instead of forcing them into this aggregation.

Use a sample-based PIR-high fraction for the supplied regular-grid campus observations, and explain how gaps limit its interpretation. If you additionally report a time-weighted result, state the assumptions about unobserved states; do not extend a known state across an outage without justification.

Evidence to submit: the complete interval-summary file and aggregation code; show full-coverage, partial, empty, and deployment-split examples in the report, including counts and coverage calculations. Explain what the PIR fraction can and cannot establish.

Task 4: Design and implement the campus database (25%)

The facilities manager should not need to know which CSV file contains a room or which kit happened to be there last month. Your database must preserve those relationships and prevent common invalid states.

4.1 Application requirements

Your system must represent the following facts:

  1. Buildings contain rooms. A room label is unique within its building, not necessarily across campus. A registered room may have no observations.
  2. Kits exist independently of rooms. A kit may be deployed to different rooms over time, and a room may receive different kits. A session and its measurements retain the deployment that applied when they were collected.
  3. A kit has light and PIR channels. Observations retain time, value, unit or representation, and quality status. Raw light values are ADC codes, not lux; PIR values are detected-motion states, not occupancy counts.
  4. A high-rate optical capture retains its sampling settings and provenance. Its waveform may remain in a submitted raw file whose relative path is stored in the database; you need not insert every high-rate sample into SQLite. Synthetic campus captures have metadata only, so their raw-file path is missing. A derived frequency attempt refers to its capture and may be accepted, rejected, or unavailable; zero is not a substitute for missing.
  5. Student-measured, course-measured, and synthetic records remain distinguishable.
  6. A per-deployment light threshold may support screening queries, but thresholds from uncalibrated kits are not automatically interchangeable.

4.2 Conceptual and relational design (10%)

Submit an ER diagram showing entities, attributes, keys, relationships, cardinalities, and participation constraints. Translate it into a relational schema listing every relation, attribute, domain, primary key, foreign key, and nullable attribute.

List the important functional dependencies for at least two relations. Identify one insertion, update, or deletion anomaly that a plausible single-table design would create, and explain how your decomposition avoids it. State the highest normal form you can justify for the principal relations; do not claim BCNF merely because the tables look tidy.

Evidence to submit: a legible ER diagram, the relation/attribute/key/domain listing, functional dependencies for two relations, and your anomaly and normalization argument. Include the ER diagram as a separate PDF or image.

4.3 Implement the schema (8%)

Implement your design in schema.sql using SQLite. Include meaningful NOT NULL, UNIQUE, CHECK, primary-key, and foreign-key constraints. Enable foreign-key enforcement on every connection.

Demonstrate, inside a transaction that you roll back, that the database rejects:

  • an observation referring to a nonexistent parent; and
  • one other invalid record of your choice, such as an invalid PIR state or duplicate sample key.

Create one index intended to accelerate a room/time or kit/time query. Explain which query it supports and the cost of maintaining it.

Evidence to submit: schema.sql, the two attempted invalid inserts and their actual error messages, evidence that the transaction was rolled back, and an explanation of foreign-key enforcement and your index.

4.4 Populate reproducibly (7%)

Write an importer using Python’s sqlite3 module, parameterized statements, and an explicit transaction. It must load the cleaned supplied records and your own records into a new empty database. The database must not depend on hand edits made in a graphical SQLite browser.

Report the number of rows loaded into every table, the number rejected or quality-flagged from every source, and the checks you use to prevent measurements from being attached to the wrong deployment. Explain one integrity rule enforced by your importer rather than SQLite and why.

Evidence to submit: your importer, populated room_sensing.sqlite, per-table and per-source count tables, the deployment/integrity checks, and a rebuild command in your README. Keep linked raw files at the submitted relative paths.

Task 5: Answer facilities questions with SQL (15%)

The schema earns its keep when it answers questions without reconstructing the deployment in your head. Put the following queries in queries.sql. For each, include the SQL, a result table, and a short interpretation that respects the sensing limitations.

5.1 Possible lighting-service candidates (4%)

For each room and 15-minute interval with at least 80% coverage, return intervals whose mean light code exceeds the applicable deployment threshold and whose PIR-high fraction is zero. Include building, room, interval start, kit, mean code, threshold, PIR-high fraction, and coverage.

Explain why the result is a list for investigation, not proof that an empty room’s lights were left on.

Evidence to submit: the SQL, result table, and a short screening interpretation with the sensing limitations stated.

5.2 Data-quality and maintenance candidates (3%)

Return kits or deployments with missing coverage, invalid/flagged observations, or suspiciously frequent rail values. Define your criterion in SQL and rank the results so the facilities team knows what to inspect first.

Evidence to submit: the SQL, ranked result table, your selection/ranking rule, and an explanation of which kit or deployment should be checked first and why.

5.3 Frequency-attempt summary (3%)

By building, report the number of attempted, accepted, rejected, and unavailable frequency estimates, together with the mean and range of accepted grid-frequency estimates. Do not allow rejected or missing results to become zero-valued estimates.

Evidence to submit: the SQL and building-summary table, with an explanation of how you exclude controls and handle buildings with no accepted estimates.

5.4 Registered rooms without observations (3%)

Return every registered room with no observation in a requested time period. Use an outer join or a logically equivalent construction. Explain why absence from an inner-join result cannot distinguish “no room” from “room with no data.”

Evidence to submit: the requested half-open time period, SQL, room-result table, and your explanation of how registered rooms remain visible without measurements.

5.5 One question of your own (2%)

Write one additional query that would help a facilities manager evaluate service delivery or data quality. It must use either a subquery/common table expression or a join not already needed by your previous queries. State the decision the result could inform and one claim it cannot support.

Evidence to submit: your facilities question, SQL, result table, the decision it supports, and one limitation.

Task 6: Plan storage at campus scale (10%)

A format that is convenient for ten minutes of one kit may become expensive or awkward at campus scale. Compare representations using both a transparent calculation and the files you created.

6.1 Estimate the raw payload (4%)

Suppose 500 kits each produce one slow observation every 10 seconds for 365 days. A compact binary observation uses 8 bytes for a timestamp, 4 bytes for a kit identifier, 2 bytes for a light code, 1 byte for PIR state, and 1 byte for a quality field.

Compute the number of observations and raw payload size in decimal GB. This is a lower bound: explain at least three sources of storage overhead or additional data omitted from the 16-byte record.

Evidence to submit: the observation-count and byte calculations with units, decimal-GB result, and at least three omitted storage costs.

6.2 Measure three representations (3%)

Use the supplied storage utility, or equivalent code, to measure the same cleaned campus-observation rows and fields as:

  1. the cleaned observation data as CSV;
  2. a SQLite snapshot containing only those observations and an index on deployment/time; and
  3. a gzip-compressed copy of the CSV, created without changing its contents.

Report each size and the size ratio \(\text{CSV bytes}/\text{representation bytes}\). These three files must preserve the same logical records. Separately report the size of your complete populated database, keeping its indexes in place, and identify the additional contents it stores. Its total size is a system-storage measurement, not a compression comparison with observation-only CSV. Include the total size of linked optical files separately; external files still consume storage.

Evidence to submit: a representation/row-count/byte-size/ratio table, evidence that the three representations preserve the same rows and fields, separate complete-database and optical-file totals, and the measurement command or code. Include the generated comparison files.

6.3 Recommend a storage approach (3%)

Explain why the measured sizes differ, referring to repeated text, types, indexes, and statistical redundancy. Discuss how delta or delta-of-delta timestamp encoding and run-length behavior in PIR states could reduce storage even though you did not implement a new codec.

Recommend a primary representation and an archival/export representation for the facilities manager. Consider queryability, interoperability, constraints, provenance, and recovery as well as size. State why lossless storage is the default for these primary measurement records and what evidence would be required before discarding information.

Evidence to submit: a short recommendation tied to your measured results, explanations of timestamp and PIR compression opportunities, and the storage/recovery tradeoffs supporting your choices.

Grading summary

Task Weight
1. Revisit the commissioned kit 15%
2. Build an optical grid-frequency measurement 25%
3. Prepare the time-series records 10%
4. Design and implement the campus database 25%
5. Answer facilities questions with SQL 15%
6. Plan storage at campus scale 10%
Total 100%

Credit depends on the reasoning and evidence supporting an answer, not merely the final number or whether inexpensive hardware behaves perfectly. Report unexpected behavior honestly and investigate it. A carefully diagnosed unavailable frequency estimate is better engineering than a 60 Hz answer unsupported by the record.

Resources

References