Assignment 1: Database schema
DATE DUE: February 10, 2016
Points possible: 10 points
CS-161: Software Project
Instructor: Rob Bruce
OVERVIEW
For this assignment you will design the database schema you will use in your 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 bounding box, STASM, and eye pupil data for each frame.
The computer vision pipeline will need to store the following data:
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)
Bounding box data
- Cartesian (X, Y) coordinates for upper left corner of bounding box location for whole face (integer)
- Width of of bounding box for whole face (integer)
- Height of of bounding box for whole face (integer)
- Cartesian (X, Y) coordinates for upper left corner of bounding box location for left eye (integer)
- Width of of bounding box for left eye (integer)
- Height of of bounding box for left eye (integer)
- Cartesian (X, Y) coordinates for upper left corner of bounding box location for right eye (integer)
- Width of of bounding box for right eye (integer)
- Height of of bounding box for right eye (integer)
- Cartesian (X, Y) coordinates for upper left corner of bounding box location for nose (integer)
- Width of of bounding box for nose (integer)
- Height of of bounding box for nose (integer)
- Cartesian (X, Y) coordinates for upper left corner of bounding box location for mouth (integer)
- Width of of bounding box for mouth (integer)
- Height of of bounding box for mouth (integer)
Pupil data
- Cartesian (X, Y) coordinates for location of left eye pupil (integer)
- Cartesian (X, Y) coordinates for location of right eye pupil (integer)
STASM data
- Cartesian (X, Y) coordinates for STASM data point 0 (integer)
- Cartesian (X, Y) coordinates for STASM data point 1 (integer)
- Cartesian (X, Y) coordinates for STASM data point 2 (integer)
- Cartesian (X, Y) coordinates for STASM data point 3 (integer)
- Cartesian (X, Y) coordinates for STASM data point 4 (integer)
- Cartesian (X, Y) coordinates for STASM data point 5 (integer)
- Cartesian (X, Y) coordinates for STASM data point 6 (integer)
- Cartesian (X, Y) coordinates for STASM data point 7 (integer)
- Cartesian (X, Y) coordinates for STASM data point 8 (integer)
- Cartesian (X, Y) coordinates for STASM data point 9 (integer)
- Cartesian (X, Y) coordinates for STASM data point 10 (integer)
- Cartesian (X, Y) coordinates for STASM data point 11 (integer)
- Cartesian (X, Y) coordinates for STASM data point 12 (integer)
- Cartesian (X, Y) coordinates for STASM data point 13 (integer)
- Cartesian (X, Y) coordinates for STASM data point 14 (integer)
- Cartesian (X, Y) coordinates for STASM data point 15 (integer)
- Cartesian (X, Y) coordinates for STASM data point 16 (integer)
- Cartesian (X, Y) coordinates for STASM data point 17 (integer)
- Cartesian (X, Y) coordinates for STASM data point 18 (integer)
- Cartesian (X, Y) coordinates for STASM data point 19 (integer)
- Cartesian (X, Y) coordinates for STASM data point 20 (integer)
- Cartesian (X, Y) coordinates for STASM data point 21 (integer)
- Cartesian (X, Y) coordinates for STASM data point 22 (integer)
- Cartesian (X, Y) coordinates for STASM data point 23 (integer)
- Cartesian (X, Y) coordinates for STASM data point 24 (integer)
- Cartesian (X, Y) coordinates for STASM data point 25 (integer)
- Cartesian (X, Y) coordinates for STASM data point 26 (integer)
- Cartesian (X, Y) coordinates for STASM data point 27 (integer)
- Cartesian (X, Y) coordinates for STASM data point 28 (integer)
- Cartesian (X, Y) coordinates for STASM data point 29 (integer)
- Cartesian (X, Y) coordinates for STASM data point 30 (integer)
- Cartesian (X, Y) coordinates for STASM data point 31 (integer)
- Cartesian (X, Y) coordinates for STASM data point 32 (integer)
- Cartesian (X, Y) coordinates for STASM data point 33 (integer)
- Cartesian (X, Y) coordinates for STASM data point 34 (integer)
- Cartesian (X, Y) coordinates for STASM data point 35 (integer)
- Cartesian (X, Y) coordinates for STASM data point 36 (integer)
- Cartesian (X, Y) coordinates for STASM data point 37 (integer)
- Cartesian (X, Y) coordinates for STASM data point 38 (integer)
- Cartesian (X, Y) coordinates for STASM data point 39 (integer)
- Cartesian (X, Y) coordinates for STASM data point 40 (integer)
- Cartesian (X, Y) coordinates for STASM data point 41 (integer)
- Cartesian (X, Y) coordinates for STASM data point 42 (integer)
- Cartesian (X, Y) coordinates for STASM data point 43 (integer)
- Cartesian (X, Y) coordinates for STASM data point 44 (integer)
- Cartesian (X, Y) coordinates for STASM data point 45 (integer)
- Cartesian (X, Y) coordinates for STASM data point 46 (integer)
- Cartesian (X, Y) coordinates for STASM data point 47 (integer)
- Cartesian (X, Y) coordinates for STASM data point 48 (integer)
- Cartesian (X, Y) coordinates for STASM data point 49 (integer)
- Cartesian (X, Y) coordinates for STASM data point 50 (integer)
- Cartesian (X, Y) coordinates for STASM data point 51 (integer)
- Cartesian (X, Y) coordinates for STASM data point 52 (integer)
- Cartesian (X, Y) coordinates for STASM data point 53 (integer)
- Cartesian (X, Y) coordinates for STASM data point 54 (integer)
- Cartesian (X, Y) coordinates for STASM data point 55 (integer)
- Cartesian (X, Y) coordinates for STASM data point 56 (integer)
- Cartesian (X, Y) coordinates for STASM data point 57 (integer)
- Cartesian (X, Y) coordinates for STASM data point 58 (integer)
- Cartesian (X, Y) coordinates for STASM data point 59 (integer)
- Cartesian (X, Y) coordinates for STASM data point 60 (integer)
- Cartesian (X, Y) coordinates for STASM data point 61 (integer)
- Cartesian (X, Y) coordinates for STASM data point 62 (integer)
- Cartesian (X, Y) coordinates for STASM data point 63 (integer)
- Cartesian (X, Y) coordinates for STASM data point 64 (integer)
- Cartesian (X, Y) coordinates for STASM data point 65 (integer)
- Cartesian (X, Y) coordinates for STASM data point 66 (integer)
- Cartesian (X, Y) coordinates for STASM data point 67 (integer)
- Cartesian (X, Y) coordinates for STASM data point 68 (integer)
- Cartesian (X, Y) coordinates for STASM data point 69 (integer)
- Cartesian (X, Y) coordinates for STASM data point 70 (integer)
- Cartesian (X, Y) coordinates for STASM data point 71 (integer)
- Cartesian (X, Y) coordinates for STASM data point 72 (integer)
- Cartesian (X, Y) coordinates for STASM data point 73 (integer)
- Cartesian (X, Y) coordinates for STASM data point 74 (integer)
- Cartesian (X, Y) coordinates for STASM data point 75 (integer)
- Cartesian (X, Y) coordinates for STASM data point 76 (integer)
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 name your assignment with the following naming convention:
Lastname_Firstname_assignment_1.txt (for text files)
Lastname_Firstname_assignment_1.odt (for OpenOffice/LibreOffice format files)
Lastname_Firstname_assignment_1.pdf (for portable document format files)
Lastname_Firstname_assignment_1.doc (for older versons of Microsoft Word)
Lastname_Firstname_assignment_1.docx (for modern versions of Microsoft Word)
LATE ASSIGNMENTS
Assignments submitted after the due date will be worth, at most, 5 points. Additional points will be deducted for programs not meeting the specification as outlined above.
Robert Bruce
Research
Courses
Spring 2016, CS-161:
Lectures:
- Project overview
- Introduction to Postgres RDBMS
- Programming with the Postgres C API
- FFMPEG and systems programming tools
- OpenCV Haar Cascades
- Drawing with OpenCV
- Delaunay Triangles
Assignments:
- Assignment 1: Database schema
- Assignment 2: Extract still images from video
- Assignment 3: Determine bounding boxes
- Assignment 4: Draw bounding boxes
- Assignment 5: Track pupils
- Assignment 6: Draw pupil crosshairs
- Assignment 7: Determine facial landmarks
- Final assignment: Draw face mesh
Programs:
- db_menu
- example_mkdir
- example_fork_exec_pipe
- example_haar_cascades
- example_draw_opencv
- example_draw_delaunay_triangles
Other
