How to work with data stored in relational database systems or in formats utilizing markup languages. Storage structures and schemas, data relationships, and ways to query and aggregate such data.
By the end of the course, students are expected to be able to:
- Use an Entity-Relationship (ER) Diagram to determine the entities, relationships, attributes, data types, primary keys, foreign keys, etc., for a given database application; translate an ER Diagram to a relational database schema.
- Write SQL statements to define, query, and update tables in a database.
- Write programs containing embedded SQL statements to communicate with and query a database, thereby generating reports requiring more complicated logic and calculations than what is possible via stand-alone SQL.
- Write well-formed XML; use XPath and XQuery to write simple queries over an XML repository (i.e., XML database).
- Explain basic concepts of the Map Reduce paradigm.
Labs
| Lab topic | Due Date | |
|---|---|---|
| 1* | Intro to SQL. Embedded SQL programming in R | 2018-11-17 |
| 2 | ER diagrams. Embedded SQL programming in Python. | 2018-11-24 |
| 3* | Advanced SQL. | 2018-12-01 |
| 4 | Write well-formed XML; extraction from XML; MapReduce | 2018-12-08 |
Quizzes
| Time | Date | Location | |
|---|---|---|---|
| 1 | 15:00 - 15:30 | 2018-11-27 | In your lab section |
| 2 | 11:00 - 11:30 | 2018-12-13 | DPM 301 |
| # | Date | Day | Topic | Slides |
|---|---|---|---|---|
| 1 | 2018-11-13 | Tue | Introduction to the relational model; Syntax/concepts for creating tables; Introduction to constraints | Lecture1.pdf; live coding examples |
| 2 | 2018-11-15 | Thur | Basic querying using SQL: Selection, Projection, Join | Lecture2.pdf; sailors.db; Lecture2_with_answers.pdf; live coding examples |
| 3 | 2018-11-20 | Tue | Conceptual database design - Part 1: ER model, constraints | Lecture3.pdf |
| 4 | 2018-11-22 | Thur | Conceptual database design - Part 2: Translating ER to relational DB | Lecture4.pdf; Lecture4_with_answers.pdf; live coding examples |
| 5 | 2018-11-27 | Tue | Advanced SQL queries: set operations, nested queries | Lecture5.pdf; Lecture5_with_answers.pdf; live coding examples; Selfjoin_to_nestedquery_example |
| 6 | 2018-11-29 | Thur | Advanced SQL queries: aggregation | Lecture6.pdf |
| 7 | 2018-12-04 | Tue | Database application development; Introduction to semi-structured data and internet applications | Lecture7.pdf |
| 8 | 2018-12-06 | Thu | Semantic web; Big data; Introduction to MapReduce | Lecture8.pdf; cd_catalog.xml; cd_catalog.ipynb |
- Ramakrishnan, Raghu and Gehrke, Johannes. Database Management Systems, 3rd Edition, McGraw-Hill, 2002. (http://pages.cs.wisc.edu/~dbbook/)
- SQL Tutorials on W3Schools