Sensors and Databases

Weeks 10 – 13: OPTION 2

SENSORS (ARDUINOS) AND DATABASES
 (pdf version)

1. LEARNING MATERIALS

Arduino
Arduino is an open-source electronics prototyping platform based on flexible, easy-to-use hardware and software. It’s intended for … anyone interested in creating interactive objects or environments. (www.makeuseof.com/tag/arduino-technology-explained/). If you want to control objects within the Internet of Things (IOT) learning through Arduino environments is a good place to start.

You are supplied with SparkFun Inventor’s Kit – V3.2

You should download the guide for use of this kit at: SIK Guide for Arduino – V3.2 https://cdn.sparkfun.com/assets/learn_tutorials/3/1/0/RedBoard_SIK_3.2.pdf (or you may use the smaller guide provided with your kit )

Follow the hardware set-up instructions in Section 1 of your SIK Guide. Note: For a tutorial video of the setup, see Getting Started with Arduinos:  https://www.youtube.com/watch?v=6XbFbcX1GTY

Try a couple of the example circuits! To complete your first project assignment (due in two weeks) you will need to complete at a minimum the following circuits as detailed in the SIK Guide.
o    Circuit 1: Blinking an LED. Note: For a tutorial, see https://www.youtube.com/watch?v=BIHmmL9iEvc
o    Circuit 3: Red/Green/Blue (RGB) LED
o    Circuit 6: Photo Resistor. Note: For a tutorial, see https://www.youtube.com/watch?v=l21avEtZJEo
o    Circuit 7: Temperature Sensor
o    Circuit 12: Spinning a Motor. Note: For a tutorial, see https://www.youtube.com/watch?v=t5EcENW_d80&t=256s
Feel free to do all of the circuits in the SIK Guide as time permits.

• Arduino Software Download Page:  https://www.arduino.cc/en/Main/Software

• SIK Guide Code Example Download Page:   https://www.sparkfun.com/sikcode
Download and unzip the folder for the codes and open them in the Arduino Software

• Arduino Built-In Example Tutorials: https://www.arduino.cc/en/Tutorial/BuiltInExamples

Python
Python is a high-level general-purpose programming language with simple, easy to learn syntax and which uses modules and packages to encourage code reuse.

1. Download Python 3.4.3 https://www.python.org/downloads/release/python-343/
2. Download PyCharm (Community Version)
https://www.jetbrains.com/pycharm/download/#section=windows
3. Install pip https://youtu.be/jnpC_Ib_lbc (this makes it easier to install the software in 4)
4. Installing PySerial:
(i) Set-up Environment (you will be setting your Path to Python34):
o Windows:  https://www.youtube.com/watch?v=2HF3xlwVfzE
o Mac: Should be automatic
(ii) Open Command Line
o Windows: Click on start menu, go to search, type in “CMD” click on Command Prompt
o Mac: Go to Spotlight (magnifying glass), type in “Terminal” and click on it
(iii) Type in “python -m pip install pyserial” and press enter

Use SoloLearn App (if you have a smart phone this App is available at your App store for Android and iPhone) to
Complete the following sections of the Python 3 Course:
o Basic Concepts
o Control Structures
OR (if you do NOT have a smart phone)
Learn Python through Codecademy (https://www.codecademy.com/learn/python click on “Syllabus”)
Complete the following sections:
o Section 2: Strings
o Section 3: Conditionals
o Section 5: Lists
o Section 8: Loops

SQL
SQL stands for Structured Query Language and is used to communicate with a database.

Learn SQL at Codecademy (https://codecademy.com/learn/learn-sql click on “Syllabus”)
Complete the following sections:
o Section 1: Manipulation
o Section 2: Queries

ALSO
Using the SoloLearn App (if you have a smart phone this App is available at your App store for Android or is titled Learn SQL by SoloLearn in the iPhone App store)
Complete the following sections:
o Basic Concepts
o Filtering, Functions, and Subqueries
OR (if you do NOT have a smart phone)
Complete W3School Tutorial (http://www.w3schools.com/sql/default.asp)

2. SCHEDULE & BLOG ASSIGNMENTS

Week 10 Tuesday4/4/17
Week 10 Thursday4/6/17
  • Continue work with your team partner on Circuits 1, 3, 6, 7 and 12.
  • Discuss with your partner ideas for Sensor and Databases Project 1. In addition to the circuit exercises, you will need to complete at a minimum Sparkfun Code Examples 1, 6, and 7 from the SIK Guide to prepare you to complete Sensor Project 1. (See next week.)
  • Blog Assignment: (1) Go to https://www.ted.com/playlists/17/how_to_live_with_robots Select a minimum of two videos to watch. Name the videos you watched and provide your reflections on those videos. (2) Comment on what worked well this week, what didn’t, and what you learned. (3) Download the Team Project Peer Assessments handout, read it, click the url at the end of the handout, and rate a minimum of two of your past project partners. We encourage you to rate all four. (Due Sunday evening by 8:00 pm)
Week 11 Tuesday4/11/17 Complete the following in class with CLA and instructor support and on your own outside of class as needed:

  • Using Python for Arduinos (See previous Learning Materials section for web links for the following)
    1. Download Python 3.4.3
    2. Download PyCharm (Community Version)
    3. Install pip
    4. Download and Install PySerial
  • Accomplish one of the following:(See Learning Materials section)
    – In SoloLearn App (complete the Basic Concepts and Control Structures Sections of the Python 3 Course) OR
    – Complete the Codecademy online Python tutorial (Sections 2, 3, 5, and 8)
  • Run the PySerial example program
  • Create your own python program that opens a document, mp3 file, or application (See video: https://www.youtube.com/watch?v=IJm9m8kv7gU)
  • Complete Sparkfun Code Examples 1, 6, and 7 from the SIK Guide
Week 11 Thursday4/13/17
Week 12 Tuesday4/18/17 Introduction to SQL

  • Complete the Codecademy online Tutorial on SQL (See Learning Materials section) Do Sections 1 and 2.
  • Accomplish one of the following:(See Learning Materials section)
    – Learn SQL App (complete the section on Basic Concepts and the section on Filtering, Functions, and Subqueries) OR
    – Complete the W3School Tutorial on SQL
  • Create a database (https://www.youtube.com/watch?v=KOKTF1tlWZM at 6:40 in video)
  • Create a table (https://www.youtube.com/watch?v=ITmOVaZ0-ko&t=4s)
Week 12 Thursday4/20/17 Python and SQL
Week 13 Tuesday4/25/17 Connecting Arduinos to Databases
Week 13 Thursday4/27/17

3. TWO-WEEK PROJECT ASSIGNMENTS

Sensor Team Project Assignment 1 (due at end of Week 11) – Event Processing

Prerequisites:
• Installed and setup Arduino Software and drivers
• Completed Sparkfun Code Examples 1, 6, and 7 from the SIK Guide
• Completed Sparkfun Code Examples 9, 10, 11 or 15 (only if you choose to use a Flex Sensor, Soft Potentiometer, Buzzer, or LCD)

Assignment:
In a team of two (or as a team of two supporting the independent work of each other), build a circuit of your choice using the Sparkfun Inventor’s Kit materials that effectively uses at least one sensor that has two or more states (i.e. high or low, hot or cold, etc.) where each state performs a separate action. The actions must be different depending on the state and can be opening an application, opening a picture, or controlling different components (a buzzer, LED light, LCD, etc.). The program must run in a loop and the sensor must write to the serial port and display the states on the serial monitor. You may display more than just the states (i.e. different values, print statements saying if something will occur or not, etc.). Try displaying the state/sensor values on the LCD instead of on the serial monitor if you want to try something different.

Make certain you understand each component of your circuit and how the components will interact with each other and with the board. You will write the code that will run the circuit you’ve created using the coding knowledge you’ve learned so far and the example codes from the Sparkfun Inventor’s Kit.

Deliverables:
You will write a very short report describing what your circuit does, a picture of your circuit (while it is running), anything not working in your circuit that should be, what the output is, and a screenshot of the output (or picture of the LCD if you choose to do the extra credit).

You will also submit your code which will have a comment at the top stating your name(s) and what the code is for/what it does. Make sure your code is commented throughout, telling the reader what each part is doing.

You should zip your deliverables into one zip file and submit it.

Assistance:
You will work on this project outside of class, but can ask for help from the professor or the computer learning assistant in class if you get stuck. Use what you’ve learned so far and any resources that have been provided to you so far. You may use parts of the example codes from the Sparkfun Inventor’s Kit, but make sure you write notes in your code and in your report saying which parts were not written by you. Use whatever resources you can find online as long as you understand and cite what you have used.

 Project Ideas

  • Create a circuit that reads a temperature from the temperature sensor and depending on the temperature either turns on an LED light or plays a tune on the piezo buzzer
  • Create a circuit that reads a value from the photosensor and if it is bright then read the temperature and if it’s dark turn on an LED light
  • Make a nightlight with the photosensor
  • Create a circuit that uses a sensor that is connected to an object and have it write something to the serial monitor if something touches the object or play a tune if not touching it

Grading: Late project submissions result in lowering of full grade per week.

Rubric

Grade Performance
A
  • Created a circuit that has separate actions for different states and writes to serial monitor continuously.
  • Code completely works, contains a comment at the top providing your names and describing the purpose and functions of the circuit, and the code is commented throughout describing the functions of the primary sections of the code.
  • Report is submitted containing a description of what your project does, a picture of your circuit (while it is running), and screenshots of your output.
B
  • Created a circuit that has separate actions for different states and writes to serial monitor continuously.
  • Code mostly works, contains a comment at the top providing your names and describing the purpose and functions of the circuit, and the code is commented throughout describing the functions of the primary sections of the code.
  • Report is submitted containing a description of what your project does, a picture of your circuit (while it is running), screenshots of your output and a description of anything not working.
C
  • Created a circuit that does NOT have separate actions for different states and/or does NOT write to serial monitor continuously.
  • Code partially works, contains a comment at the top providing your names and describing the purpose and functions of the circuit, and the code is commented throughout describing the functions of the primary sections of the code.
  • Report is submitted containing a description of what your project does, a picture of your circuit (while it is running), screenshots of your output and a description of anything not working.
D Same criteria as C but the documentation in the code or the report falls short of the standard established for a C.
F No project submitted meeting any of the above levels of performance (A through D) within two weeks of the deadline.
Extra Credit Using LCD instead of a serial monitor to display output.

Sensor Team Project Assignment 2 (due at end of Week 13) – Event Logging with Arduinos and Databases

Prerequisites:
• Installed and setup MAMP, Python 3.4, PySerial, and PyCharm
• Installed pymysql python package using pip (see video for installation)
• Downloaded python scripts “db-test-insert.py” and “db-query-test.py”
• Watch “COS120: Using MAMP and Python” video (to insert data into database)
• Download python scripts “03_Arduino_Grab_from_SerialPort”
• Watch “COS120: Arduino Data Logging with MAMP and Python” video

Assignment:
In a team of two (or as a team of two supporting the independent work of each other), go to your “phpMyAdmin” page, via MAMP, create a database and then create a table. Then create a circuit using the Arduino with one or more sensors (you may use your circuit from assignment 1).

Write an Arduino program that will “catch events” from the sensor(s) (for example, an event would be a light value over 800 from a photosensor). For this assignment you may edit previously written code (i.e. from assignment 1 or from the SIK Guide). The code should read the data from the Arduino and check to see if the data meets a specific condition. If it does meet the condition, then it should write the event to the Serial Port. The event should be the sensed value (i.e. the light value over 800, etc.).

You will then use the provided python code (“03_Arduino_Grab_from_SerialPort”) to insert the data into your database. See video “COS120: Arduino Data Logging with MAMP and Python” for an example.

Once you’ve inserted at least 10 data entries, query the data in your table (using MAMP) to show all of your newly inserted sensed values. Then create a display chart to display your sensed data. See video “COS120: Arduino Data Logging with MAMP and Python” for an example.

Bonus: Query your event data in the database to find either the maximum, minimum, or average value of your data set. Submit a screenshot of your query result.

Deliverables:
Submit your code which should have a comment at the top stating your names and what the code is for or what it does. Make sure your code is commented throughout, telling the reader what each part is doing.

Also, write a report that describes the project that you implemented, a picture of your circuit, describe anything not working in your software that should work, as well as screenshots of your PyCharm console of your data being inserted, a screenshot of your table after the data has been inserted, and a screenshot of the display chart displaying your queried data.

You should zip your deliverables into one zip file and submit it.

Assistance:
Work on this outside of class, but ask for help from the professor or the computer learning assistants in class if you get stuck. Use what you’ve learned so far and any resources that have been provided to you so far. Please refer to the videos named “COS120: Using MAMP and Python” and “COS120: Arduino Data Logging with MAMP and Python” if you have not already and are stuck. Use whatever resources you can find online as long as you understand and cite what you have used.

 Project Ideas

  • Create a database and table via MAMP
  • Create a circuit that reads a value from a sensor in your circuit. If the sensed value meets some condition, send to Serial Port
  • Insert data sent to serial port into your table
  • Display the queried sensor values in your table

Grading: Late project submissions result in lowering of full grade per week.

Rubric

Grade Performance
A
  • Created a circuit that has at least one sensor that senses and sends events to the computer that is inserted in the database system.
  • Code completely works, contains a comment at the top providing your names and describing the purpose and functions of the circuit, and the code is commented throughout describing the functions of the primary sections of the code.
  • Report is submitted containing a description of what your project does, a picture of your circuit, and screenshots of your output.
B
  • Created a circuit that has at least one sensor that senses and sends events to the computer that is inserted in the database system.
  • Code mostly works, contains a comment at the top providing your names and describing the purpose and functions of the circuit, and the code is commented throughout describing the functions of the primary sections of the code.
  • Report is submitted containing a description of what your project does, a picture of your circuit, screenshots of your output and a description of anything not working.
C
  • Created a circuit where several individual components work, but not all together (i.e. Arduino circuit works but data is not inserted into database, etc.).
  • Code partially works, contains a comment at the top providing your names and describing the purpose and functions of the circuit, and the code is commented throughout describing the functions of the primary sections of the code.
  • Report is submitted containing a description of what your project does, a picture of your circuit, screenshots of your output and a description of anything not working.
D
  • Only small sub-component of the code works, contains a comment at the top providing your names and describing the purpose and functions of the circuit, and the code is commented throughout describing the functions of the primary sections of the code.
  • Report is submitted containing a description of what your project does, a picture of your circuit, screenshots of your output and a description of anything not working.
F
  • No project submitted meeting any of the above levels of performance (A through D) within two weeks of the deadline.

As with all university courses, you should expect to spend 2 to 3 hours outside of class for every hour you spend in class.

Creative Commons License
COS 120 Introduction to Programming by School of Computing and Information Science is licensed under a Creative Commons Attribution 4.0 International License.