Instructor: Shirley Cohen
Email: scohen at cs dot utexas dot edu
Office hours: After class or by appointment in GDC 4.314
TA: Eric Nguyen
Email: eric dot nguyen at utexas dot edu
Office hours: Fridays 12:00pm - 2:00pm in GDC 2.212
Proctor: Daniel Dao
Email: danielvudao at utexas dot edu
Office hours: Tuesdays 1:30pm - 3:00pm in GDC 3.302
Class Times: Mondays and Wednesdays 6:30pm - 8pm
Class Location: CLA 0.126
Course Description:
This course is a practical introduction to SQL and how to use a relational database management system to develop a database application. It is not a course about database administration or database system design.
The contents of the course will be organized into three parts: 1) developing SQL programs for populating and querying databases using database definition and database manipulation constructs; 2) designing logical data models and converting them to suitable physical models; and 3) building a database-driven application that utilizes the database for storage and retrieval. In addition, the course will touch on a few database security topics such as SQL injection attacks and other common vulnerabilities.
Throughout the term we will use a relational database management system (RDBMS) to gain hands-on experience with SQL programming. I have chosen MySQL because it is simple yet expressive and is also also the RDBMS that is used by our textbooks. That said, we will learn the standardized language features of SQL and try to steer away from any vendor-unique constructs. Our study of logical and physical database design will center around the relational model and we will experiment with various schemas. Some alternative technologies, such as NoSQL systems, will be introduced to compare strengths and weaknesses and appropriate uses for data management. Once we have learned the foundations of querying and modeling structured data, we will see how to connect and interract with MySQL from a Python application. The Python application will extract, transform, and load data into a schema in a MySQL database.
The course will be taught using active learning techniques. We will spend the majority of class time working through problems instead of listening to me lecture. Therefore, it is critical that you prepare for class by doing the assigned readings prior to class. In fact, you will get points for completing the readings on time and for participating in class activities. The goal is to use our precious class time to review difficult concepts, work through problems, and learn from your peers.
Once we have built a conceptual understanding and worked through a number of hands-on exercises, we will apply the same techniques in a broader context: students will work in small groups on a substantial project which will consist of an end-to-end database application. The project will have several important milestones and each group will produce a final presentation, report, and video to convey the results of their work.
Below is a list of the main topics that we will cover during the course organized by section.
Part 1: SQL
-select-from-where queries
-joins
-inserts, updates, deletes
-aggregates and group-bys
-subqueries
-views
Part 2: Database Design
-logical database design
-inheritance
-normalization forms (with focus on 3NF)
-physical database design
-optimization techniques
Part 3: Application Development
-data extraction
-data transformation
-data integration
-data ingestion
-database security (by guest lecturer Prof. Bill Young)
Textbooks: There are three required texts for the course and one optional text. If you are new to Python, I highly recommend that you obtain all four texts.
Required:
Alan Beaulieu's Learning SQL, 2nd edition, 2009.
Clare Churcher's Beginning Database Design: From Novice to Professional, 2nd edition, 2012.
Ryan Mitchell's Web Scraping with Python: Collecting Data from the Modern Web, 1st edition, 2015.
Optional:
Mark Lutz's Learning Python, 5th edition, 2013.
Clickers:
We will be using clickers for class participation. You will need to have your own clicker and bring it to class each time. You can purchase a clicker from the University's bookstore if you don't have one already. The clicker we will be using is called i>clicker 2 (ISBN 1498601634).
Grading Rubric:
The basic grading rubric is comprised of four components listed below.
Week-by-Week Schedule:
Below is a week-by-week schedule that includes the important milestones and assigned readings. Please note that the readings listed for a given day must be done prior to the start of class on that day.