Programming Project 1: Database schema
DATE DUE: Monday, May 15, 2017
Points possible: 5 points
CS-160: Software Engineering
Instructor: Robert Bruce
OVERVIEW
For this assignment you will design the database schema you will use in your web-based, computer vision pipeline. Your schema design should be modular and scalable. I suggest viewing the other programming assignments for this class. Each assignment will be storing or retrieving data from the database. Knowing what the other assignments entail may help you to design an efficient database schema.
SPECIFICATIONS
In your computer vision pipeline, you will be extracting still images of each frame from your input video. For example, a video that is 10 seconds long encoded at 30 frames per second would yield 300 frames. You will need to do image analyis on each of those three-hundred frames (i.e. frame 1, frame 2, frame 3, etc.). Your database will thus need to hold OpenFace and Fabian Timm's eye pupil tracking data for each frame.
The computer vision pipeline will need to store the following data:
User profile information
- Username for logging into the web-based portal (char)
- User's password (char)
- First name of the user (char or varchar)
- Last name of the user (char or varchar)
- Last login date/time (timestamp)
- User's IP address (location) from their last login (char)
Input video metadata
- Number of frames in the input video (integer)
- X (width) resolution of each frame of input video (integer)
- Y (height) resolution of each frame of input video (integer)
- Frames per second the input video was encoded at (real number)
Head (skull) position
- Yaw (degrees or radians) from OpenFace (real number)
- Pitch (degrees or radians) from OpenFace (real number)
- Roll (degrees or radians) from OpenFace (real number)
Pupil data
- Cartesian (X, Y) coordinates from OpenFace for location of left eye pupil (point)
- Cartesian (X, Y) coordinates from OpenFace for location of right eye pupil (point)
- Cartesian (X, Y) coordinates from Fabian Timm's pupil tracker for location of left eye pupil (point)
- Cartesian (X, Y) coordinates from Fabian Timm's pupil tracker for location of right eye pupil (point)
OpenFace data
- Cartesian (X, Y) coordinates for OpenFace data point 1 (point)
- Cartesian (X, Y) coordinates for OpenFace data point 2 (point)
- Cartesian (X, Y) coordinates for OpenFace data point 3 (point)
- Cartesian (X, Y) coordinates for OpenFace data point 4 (point)
- Cartesian (X, Y) coordinates for OpenFace data point 5 (point)
- Cartesian (X, Y) coordinates for OpenFace data point 6 (point)
- Cartesian (X, Y) coordinates for OpenFace data point 7 (point)
- Cartesian (X, Y) coordinates for OpenFace data point 8 (point)
- Cartesian (X, Y) coordinates for OpenFace data point 9 (point)
- Cartesian (X, Y) coordinates for OpenFace data point 10 (point)
- Cartesian (X, Y) coordinates for OpenFace data point 11 (point)
- Cartesian (X, Y) coordinates for OpenFace data point 12 (point)
- Cartesian (X, Y) coordinates for OpenFace data point 13 (point)
- Cartesian (X, Y) coordinates for OpenFace data point 14 (point)
- Cartesian (X, Y) coordinates for OpenFace data point 15 (point)
- Cartesian (X, Y) coordinates for OpenFace data point 16 (point)
- Cartesian (X, Y) coordinates for OpenFace data point 17 (point)
- Cartesian (X, Y) coordinates for OpenFace data point 18 (point)
- Cartesian (X, Y) coordinates for OpenFace data point 19 (point)
- Cartesian (X, Y) coordinates for OpenFace data point 20 (point)
- Cartesian (X, Y) coordinates for OpenFace data point 21 (point)
- Cartesian (X, Y) coordinates for OpenFace data point 22 (point)
- Cartesian (X, Y) coordinates for OpenFace data point 23 (point)
- Cartesian (X, Y) coordinates for OpenFace data point 24 (point)
- Cartesian (X, Y) coordinates for OpenFace data point 25 (point)
- Cartesian (X, Y) coordinates for OpenFace data point 26 (point)
- Cartesian (X, Y) coordinates for OpenFace data point 27 (point)
- Cartesian (X, Y) coordinates for OpenFace data point 28 (point)
- Cartesian (X, Y) coordinates for OpenFace data point 29 (point)
- Cartesian (X, Y) coordinates for OpenFace data point 30 (point)
- Cartesian (X, Y) coordinates for OpenFace data point 31 (point)
- Cartesian (X, Y) coordinates for OpenFace data point 32 (point)
- Cartesian (X, Y) coordinates for OpenFace data point 33 (point)
- Cartesian (X, Y) coordinates for OpenFace data point 34 (point)
- Cartesian (X, Y) coordinates for OpenFace data point 35 (point)
- Cartesian (X, Y) coordinates for OpenFace data point 36 (point)
- Cartesian (X, Y) coordinates for OpenFace data point 37 (point)
- Cartesian (X, Y) coordinates for OpenFace data point 38 (point)
- Cartesian (X, Y) coordinates for OpenFace data point 39 (point)
- Cartesian (X, Y) coordinates for OpenFace data point 40 (point)
- Cartesian (X, Y) coordinates for OpenFace data point 41 (point)
- Cartesian (X, Y) coordinates for OpenFace data point 42 (point)
- Cartesian (X, Y) coordinates for OpenFace data point 43 (point)
- Cartesian (X, Y) coordinates for OpenFace data point 44 (point)
- Cartesian (X, Y) coordinates for OpenFace data point 45 (point)
- Cartesian (X, Y) coordinates for OpenFace data point 46 (point)
- Cartesian (X, Y) coordinates for OpenFace data point 47 (point)
- Cartesian (X, Y) coordinates for OpenFace data point 48 (point)
- Cartesian (X, Y) coordinates for OpenFace data point 49 (point)
- Cartesian (X, Y) coordinates for OpenFace data point 50 (point)
- Cartesian (X, Y) coordinates for OpenFace data point 51 (point)
- Cartesian (X, Y) coordinates for OpenFace data point 52 (point)
- Cartesian (X, Y) coordinates for OpenFace data point 53 (point)
- Cartesian (X, Y) coordinates for OpenFace data point 54 (point)
- Cartesian (X, Y) coordinates for OpenFace data point 55 (point)
- Cartesian (X, Y) coordinates for OpenFace data point 56 (point)
- Cartesian (X, Y) coordinates for OpenFace data point 57 (point)
- Cartesian (X, Y) coordinates for OpenFace data point 58 (point)
- Cartesian (X, Y) coordinates for OpenFace data point 59 (point)
- Cartesian (X, Y) coordinates for OpenFace data point 60 (point)
- Cartesian (X, Y) coordinates for OpenFace data point 61 (point)
- Cartesian (X, Y) coordinates for OpenFace data point 62 (point)
- Cartesian (X, Y) coordinates for OpenFace data point 63 (point)
- Cartesian (X, Y) coordinates for OpenFace data point 64 (point)
- Cartesian (X, Y) coordinates for OpenFace data point 65 (point)
- Cartesian (X, Y) coordinates for OpenFace data point 66 (point)
- Cartesian (X, Y) coordinates for OpenFace data point 67 (point)
- Cartesian (X, Y) coordinates for OpenFace data point 68 (point)
ASSIGNMENT: WHAT TO INCLUDE
When submitting your assignment, give me a description of the actual tables you created in Postgres.
I expect you to list the names and datatypes for all the fields which comprise each table you will be using.
Example: Lets say I want to create a simple database for storing student grades in a university, I might create the following three tables:
Table name: Students
| field name | data type |
|---|---|
| student_id | bigserial not null primary key |
| first_name | varchar(100) not null |
| last_name | varchar(100) not null |
Table name: Grades
| field name | data type |
|---|---|
| student_id | bigint not null |
| course_id | bigint not null |
| grade | char(2) not null |
NOTE: For Grades table, the primary key is the student_id and course_id together.
Table name: Courses
| field name | data type |
|---|---|
| course_id | bigserial not null primary key |
| course_code | char(4) not null |
| course_number | integer not null |
| course_description | varchar(255) not null |
If you are unsure if you have a correct description, contact me or visit me in office hours or after class. I can show you more examples. Really, I want to see you are thinking about the structure of your database tables
SUBMITTING YOUR ASSIGNMENT
1. Please submit your program as text, Portable Document format (Acrobat), OpenOffice/LibreOffice format, or Microsoft Word document format file labeled "Assignment 1" to the Canvas dropbox.
2. Please include the full names of all members of your team at the top of this assignment.
3. Please name your assignment with the following naming convention:
assignment_2.txt (for text files)
assignment_2.odt (for OpenOffice/LibreOffice format files)
assignment_2.pdf (for portable document format files)
assignment_2.doc (for Microsoft Word)
assignment_2.docx (for Microsoft Word)
LATE ASSIGNMENTS
Assignments submitted after the due date will be worth, at most, 2.5 points. Additional points will be deducted for programs not meeting the specification as outlined above.
Robert Bruce
Research
Courses
Spring 2017, CS-160-03:
Lectures:
- What is software engineering?
- Postgres RDBMS (Relational Database Management System)
- Document management
Assignments:
- Biographical Writeup
- Programming Project 1
- Programming Project 2
- Programming Project 3
- Programming Project 4
- Programming Project 5
- Programming Project 6
- Final Project Oral Presentation
- Final Project Report
Handouts:
- Creating a bootable Linux Mint flash drive
- How to update software on Linux Mint
- How to install and configure the Apache web server on Linux Mint
- How to install and configure the Postgres database server on Linux Mint
- How to build and install Openface on Linux Mint
Programs:
- db_menu
- example_mkdir
- example_fork_exec_pipe
- example_draw_opencv
- example_draw_delaunay_triangles
- example_process_get_form_data
- example_process_post_form_data
Other
