12-778 Fall 2023: Assignment #4

Author

Mario Bergés

1 Instructions

This assignment will be slightly different than the other ones we have had so far in the course. Nonetheless, previous instructions advising that you consider the issues before answering, and that you justify your answers with as much context and detail as possible, remain relevant.

2 Managing Sensor Data

Let’s begin with some Time-Series Data

2.1 Pre-processing Time-Series Data (10%)

After having so little rain in the last couple of weeks, I thought it would be appropriate to analyze some rainfall time-series data.

Visit to learn about historical rainfall data that you can obtain (either programmatically, or via their web interface) for the city of Pittsburgh. Pick a specific rainfall gauge that you are intersted in, and download data for the last 2 years on an hourly interval.

  • Plot the full time-series, ensuring that the axes of your plot have meaningful labels and units.
  • Using the techniques described in Lectures 18 and 20, describe how you would proces that dataset (i.e., find and remove outliers, model trends in the data, etc.) in a manner that you find fitting. (There is no single correct answer here but, clearly, there are wrong answers)

Now let’s transition to data that is not of the time-series kind.

2.2 Designing and Implementing your First Database (10%)

Suppose that you are required to develop a simple database for a construction design firm, using the following information as a starting point for your design:

  • The database can be used by multiple projects, and each of these projects is composed of design items.
  • The organization you are working under has several departments, namely: Architectural Department, Structural Department, Environmental Department, Land Development Department, etc.
  • One or more departments may participate in any single project. Any department can participate in several projects at once.
  • A department may employ many employees but each employee is employed by only one department
  • Each department is managed by only one of its employees.
    • The organization has a training program in which an employee may manage (or train) many other employees, and each employee is managed (or trained) by one employee.
    • During the implementation of a project, project documents (reports or drawings) will be generated.
    • One document is produced by one or many employees and an employee can produce several documents.
    • A design item consists of multiple documents.
    • The developed database can provide the user with the name of a project, its start date, its end date, contract price, design fee for each design item, the involved departments, basic information about the employees involved in the project, the number of employees in the organization, the number of Licensed Professional Engineers, etc.

2.3 Entity Relationship Diagram (10%)

Develop an Entity-Relationship diagram for the problem described above. Your E-R diagram should have several weak entity sets, one relationship with roles, many types of relationships (one-to-many, many-to-many, one-to-one) and one entity set that contains subclasses in an ``isa’’ relationship. Please specify any additional assumptions you make while building this E-R diagram. You can use any software you wish to develop the diagram, including Microsoft (Powerpoint, Visio, Publisher), or resort to a neat hand-drawn figure that you later digitize and include in your submission.

2.4 From E-R to Relations (10%)

Based on the previous E-R diagram, and without resorting to SQL, generate a list of all the relations and their schema. Please use consistent notation here. For example, if one of your relations is named “Organization” and has attributes “Name” (the key), “Address” and “Phone Number”, then you could write down:

Organization(Name, Address, Phone Number)

Make sure to generate relations and schemas for all entity sets and relationships in your E-R diagram.

2.5 Relational Algebra (10%)

Assume you were to write queries for the database you created in the first part of this assignment. Write down the relational algebra expression for the following queries:

  • Find the name and licensing status (i.e., whether he/she is a licensed professional engineer) of the epmployees working on projects since 2012
  • Find the project names for those projects that have fewer than 10 employees associated with them

3 Moving onto the final project

Answer the questions below that correspond to the course number you are registered for and the specific final project proposal you are a part of. For example, if you are in Team 1 for the final project, and registered for 12-741, then you do not need to fill out any of the questions that are for 12-778 students.

Though you will be using your specific final project proposal as the basis for this assignment, and it will be the same system as your final project teammates, the answers you present here should be entirely your own.

3.1 12-778: Characterizing your physical stimulus (15%)

Using as many concepts as applicable from what we learned in class, and in as much detail as you can, describe the physical stimulus of interest for your measurement project. For example, if your physical stimulus is an artificial light source, describe how the electromagnetic radiation is produced from said artificial light source and what static and dynamic properties you expect to see in that signal (an electromagnetic waveform). Similarly, if your project is about identifying a specific orientation for a moving animal, describe the animals motion and what physical stimuli are produced (actively or passivley) by the specific orientation you are interested in. Ideally, your description would result in a mathematical model.

3.2 12-778: Describing your measurement system (15%):

Create a diagram of the full measurement system that you are planning to build for your project. In particular, starting from the physical stimulus, create a block diagram (like the one we’ve been using to describe generic measurement systems since the beginning of class) with all of the stages of your measurement system leading up to the display. At this moment, do not worry too much about trying to elaborate on the static/dynamic characteristics of each of the intermediary stages. However, please describe the (expected) final output signal in as much detail as possible.

3.3 12-778: Characterizing the chosen sensor(s) (10%):

Given the model for the physical stimulus that you developed in Section 3.1, provide a rationale for choosing the specific sensor(s) you ended up choosing to measure this stimulus. Are you loosing some information in this process? If so, is it information that is required for the measurement you want to make? Why or why not?

3.4 12-778: Interface circuit (10%):

To allow your sensor’s output to be compatible with and useful to your data acquisition system (especially the analog-to-digital converter), you may need to use some interface circuits. This may already be provided as part of the sensor package by the manufacturer, or it may be a circuit that you have to design/implement. In either case, here you are asked to describe in as much detail as possible (including a circuit diagram) this interface circuit and its dynamic/static characteristics.

3.5 12-778: Analog-to-digital conversion (10%):

Justify the selection of the following ADC parameters for your measurement system:

  • Resolution
  • Sampling rate

3.6 12-741: Design stage uncertainty (15%):

Using manufacturer’s specifications, and the parameters your team has chosen for your overall measurement system (which teammates would have described in Section 3.2), please compute the design-stage uncertainty for your system in the units of the physical stimulus.

3.7 12-741: User Interface Design (10%):

Describe, in simple language, the way you envision a user interacting with your system. What do you assume they would use your sensing system for (what is their objective)? What steps would they have to follow in order to achieve that objective using your system?

List up to three queries that the user may pose to the system through its user interface (e.g., plot the average measurement over the last 5 days in hourly intervals).

3.8 12-741: E-R Diagram (10%)

Create an E-R diagram to model the information that your system will need to store in order to serve the queries described in the previous section.

3.9 12-741: Implementation Strategy (15%)

Describe, in as much detail as you can at this point, how you will translate the E-R diagram into a database design and, crucially, what kind of database management system you will be using to implement it and interface it with the sensing system generating the time-series data.