 |
CEE 412/599 (Winter 2012) Transportation Data Management and Analysis |
Meeting Times
10:30am - 12:20pm every Tuesday and Thursday at More Hall 230.
Office Hours
12:30pm - 2:00pm every Thursday
or by appointment (stop by is also welcome).
Instructor
Office: 133B, More Hall
Tel: (206) 616-2696
Fax: (206) 543-1543
Email: yinhai@uw.edu
Teaching Assistant
Cathy Liu
Office: 135, More Hall
Tel: (206) 685-6817
Fax: (206) 543-1543
Email: liuxy@uw.edu
Office Hour: 3:30 - 5:00pm every Tuesday and 12:30 - 2:00pm every Friday
or by appointment (stop by is also welcome).
Course Description and
Objectives:
In recent years, traffic detectors have been intensively deployed in major highway systems across the country. These sensors generate tremendous traffic data that are extremely valuable for traffic management, forecast, and control. How to manage the data efficiently and produce the most useful information out of them have been crucial challenges faced by traffic professionals.
The objective of this class is to introduce modern concepts, algorithms, and tools for transportation data management and analysis. With the instructions, assignments, and projects in this course, students are expected to learn database design theories; analytical methods for capacity, safety, and time series analyses; skills on popular software tools for transportation data management and analysis, and available resources for further developments along these directions.
Major topics of this course include: (1) database design and management; (2) data management and analysis tools (Microsoft SQL 2008, Excel 2010, etc.); (3) analytical methods for transportation data analysis; (4) data exchange format (XML); and (5) online data sharing technologies.
Prerequisite:
None. Data analysis experience is helpful, but not necessary.
Required Textbook:
CEE 412 /599 Course Pak (Reader), available at the Professional Copy 'N' Print , 4200 University Way NE. Tel: (206) 634-2689. The price is about $29.
Optional Textbook:
Garcia-Molina, H., J.D. Ullman, and J. Widom. Database Systems: The Complete Book. Second Edition. Prentice-Hall, Inc. Upper Saddle River, New Jersey, 2009.
Class Assignments and Grading
Written assignments and projects.
There are two in-class midterm exams and no final exam.
Grading: Midterms: 50% | Assignments: 20% | Projects: 30%
More information about course arrangement, expectations, and policy is available in pdf format at here.
Day-by-day topics and required readings (Course files are accessible to only registered students):
Day Date Tu Jan. 3 Introduction and Course Overview Course Survey Th Jan. 5 Guest speach: Playing Data with Microsoft Excel, by Jon Corey Reader 1 A#1 out Tu Jan. 10 Advanced Excel Applications Readers 2&3 Th Jan. 12 Database Design Fundamentals Reader 4 A#1 due; P#1 out Tu Jan. 17 E/R Diagram and Relational Data Model Readers 5&6 Th Jan. 19 Structured Query Language (SQL) I Reader 7 P#1 due; A#2 out Tu Jan. 24 Excel practice at the CEE Computer Lab (not instructed because of the TRB meeting in DC) Th Jan. 26 Reduce Redundancy in Schema Design and SQL II Reader 8 A#2 due; P#2 out Tu Jan. 31 Structured Query Language III Th Feb. 2 Structured Query Language IV Reader 9 Tu Feb. 7 Introduction to Advanced SQL / Midterm 1 M1Solution Th Feb. 9 Road Capacity Analysis Reader 15 P#2 due; A#3 out Tu Feb. 14 Accident Analysis and R Readers 16&17 Th Feb. 16 Access Fundamentals Readers 10-12 A#3 due; P#3 out; Tu Feb. 21 Using Access for Data Management Readers 13&14 Th Feb. 23 Sharing Data Online and Expression Web Reader 18 P#3 due; P#4 out Tu Feb. 28 Midterm 2 M2Solution Th Mar. 1 Guest lecture: TBD Tu Mar. 6 Time Series Analysis Reader 19 Th Mar. 8 XML and Course Wrap up Reader 20 P#4 draft report due 11
M Mar. 12 Final Project Presentation (10:30-12:20) P#4 final report and code due A#n - Assignment Number n;
P#m - Project Number m.
Lectures (Files are accessible to only registered students):
Lecture 1: Introduction and Course Overview (ppsx, pdf), Jan. 3, 2012.
Lecture 2: Playing Data with Microsoft Excel (ppsx, pdf), Jan. 5, 2012.
Lecture 3: Advanced Excel Applications (ppsx, pdf), Jan. 10, 2012. The in-class exercise instruction package: Exercises 1 and 2 (ppt); Data file for in-class Exercise 2 is here.
Lecture 4: Database Design Fundamentals (ppsx, pdf), Jan. 12, 2012.
Lecture 5: E/R Diagram and Relational Data Model (ppsx, pdf), Jan. 17, 2012.
Lecture 6: Structured Query Language (SQL) I (ppsx, pdf), Jan. 19, 2012. The in-class exercise instruction package: Exercise 3 (ppt).
Lecture 7: Non-instructed practice session at the CEE Computer Lab, Jan. 24, 2012. The instruction file for in-class exercise 4 is available here. Excel file needed for this exercise is here. The instruction file for in-class exercise 5-7 can be found here (ppt). Data files can also be downloaded for in-class exercise 5 (Exercises5.xls) and exercise 6 (Exercise6.xls).
Lecture 8: Reduce Redundancy in Schema Design and SQL II (ppsx, pdf), Jan. 26, 2012.
Lecture 9: Structured Query Language III (ppsx, pdf), Jan. 31, 2012. The in-class exercise instruction package: Exercise 8 (ppt).
Lecture 10: Structured Query Language IV (ppsx, pdf), Feb. 2, 2012. The in-class exercise instruction package: Exercise 9 (ppt).
Lecture 11: Introduction to Advanced SQL / Midterm 1 (ppsx, pdf), Feb. 7, 2012.
Lecture 12: Road Capacity Analysis (ppsx, pdf), Feb. 9, 2012.
Lecture 13: Accident Analysis and R (ppsx, pdf), Feb. 14, 2012. The in-class exercise instruction package: Exercise 10 (ppt).
Lecture 14: Access Fundamentals (ppsx, pdf), Feb. 16, 2012. The in-class exercise instruction package: Exercise 11 (ppt). Database skeleton file for Exercises 11: Exercise11.mdb.
Lecture 15: Using Access for Data Management (ppsx, pdf), Feb. 21, 2012.
Lecture 16: Sharing Data Online and Expression Web (ppsx, pdf), Feb. 23, 2012. The in-class exercise instruction package: Exercise 12 (ppt). The zip file with the static page and the dynamic page is here: Exercise12.zip. An example solution to Exercise 12 is here.
Lecture 17: Midterm 2, Feb. 28, 2012.
Lecture 18: Guest lecture: Using Data for Performance Management: Communicating the Good, the Bad, and the Ugly (ppsx, pdf) by Ms. Daniela Bremmer and Mr. Sreenath Gangula of WSDOT, Mar. 1, 2012. The required extra reading is the WSDOT 2011 Congestion Report.
Lecture 19: Time Series Analysis (ppsx, pdf), Mar. 6, 2012. The instruction file for in-class exercise 13 is available here (ppt). The SQL script file for creating the AccidentByMonthByRouteView is here: Exercise13.sql. The instruction file for in-class exercise 14 is available here (ppt).
Lecture 20: XML and Course Wrap up (ppsx, pdf), Mar. 8, 2012.
Assignments and Projects (Files are accessible to only registered students):
Assignment #1 due at the class on Jan. 12, 2012. The data file for Problem 1 is available here. Solution to Assignment #1 is here.
Project #1 due Jan. 19, 2012. Download data sets: single.txt (single loop) and dual.txt (dual loop). An electronic version of the median speed paper by Coifman et al (2003) is available here. If you are interested to know more about the segmentation error with loop detector data, please read this article by three STAR Lab authors. A sample solution to Project #1 is here.
Assignment #2 due at the class on Jan. 26, 2012. Solution to Assignment #2 is here.
Project #2 due Feb. 9, 2012. Download data sets: P2wa02acc.xls (accident data file), P2wa02road.xls (road data file) and P2wa02veh.xls (vehicle data file). Data dictionary (P2Dictionary.doc, P2Dictionary.pdf). A sample solution package: summary, variable definitions, Accidents relation (Excel format), Roads relation (Excel format), and Vehicles relation (Excel format).
Assignment #3 due at the class on Feb. 16, 2012. The data file for Problem 6 (R data file) is available here. Solution to Assignment #3 is here.
Project #3 due Feb. 23, 2012. Download the skeleton mdb file for this project: LDMS.mdb. A sample solution to Project #3 is here.
Project #4 report is due in class on Mar. 8, 2012 and website files are due at 10:00am on Mar. 12, 2012. A sample implementation of Project#4 is here.
The following E-books are available on-line at the Net Library. Click here for information on how to use the Net Library.
1. Shapiro, J. R. SQL Server 2000: The Complete Reference. McGraw-Hill Professional, Berkeley, 2001. ISBN: 0072224681.
2. Ivens, K. and C. G. Carlberg. The Complete Reference. McGraw-Hill Professional, New York, 2001. ISBN : 0-596-00315-3.
3. Cassel, P. and P. Palmer. Sams Teach Yourself Microsoft Access 2000 in 21 Days. Sams Publishing, Indianapolis, 1999. ISBN: 0672312921.
1. Excel Tutorial at Florida Gulf Coast University
2. SQL Tutorial at SQLCourse.com
3. Access Tutorial at Florida Gulf Coast University
4. R Tutorials at R-Tutor.com
5. Expression Web Tutorial at Microsoft
6. HTML Tutorial at EchoEcho.com
7. Microsoft DreamSpark program with many free software applications for students