Data Management

Overview

Databases are essential to applications in a wide variety of domains, including finance, health care, commerce, and telecomunications. In fact, most applications that people use on a day-to-day bases are backed by databases. This course provides a practical introduction to database technology. By the end of this course, students will understand the fundamental concepts about database management systems, become familiar with commercial tools for the design and development of database applications, and be exposed to recent trends in database-like storage systems. Topics covered include modeling enterprise data with ER diagrams, the relational model, SQL, XML, logical design with normalization, physical design, query execution, transaction processing, recovery, concurrency, online analytical processing, and NoSQL systems.

Details

Textbooks

We rely on one textbook:

• Database Systems: The Complete Book (DSCB), 2nd edition, by Hector Garcia-Molina, Jeffrey D. Ullman, Jennifer Widom.

Moodle

Please send class-related questions to the Discussions Forum on Moodle (unless, of course, they concern private rather than technical or organizational issues).

Grading Policy

Approximately: 5% notes; 25% for homework; 20% for quiz 1; 20% for quiz 2; 30% for final exams.

Academic Integrity

I encourage you to collaborate on homework assignments. But you must write up and turn in your own answers. Also, you must clearly indicate who you collaborated with. If I detect any incidents of cheating, I will report them immediately to the department, and the assignment will be given a grade of 0.

Syllabus

Please be sure to regularly check this page for updates.

Feb 17

No class

Feb 21

Introduction

• Read DSCB 1.1 – 1.2.

Feb 24

Modeling Enterprise Data with ER Diagrams

• Read DSCB 4.0 – 4.3.
• Notes from Matteo Morisoli and Simone Raimondi.
• Zvi Kedem's lecture notes.

Feb 28

Modeling Enterprise Data with ER Diagrams

• Read DSCB 4.4 – 4.6.
• Notes from Lokesh Menghani and Edoardo Piazza.
• Homework 1 on Moodle. Due Mar 14.

Mar 03

Relational Model And ER Diagrams

• Read DSCB 2.0 – 2.2.
• Notes from Filippo Ferrario and Christian Vuerich.
• Zvi Kedem's lecture notes.

Mar 07

Relational Model And ER Diagrams

• Read DSCB 2.0 – 2.2.
• Notes from Jesper Findahl and Talal El Afchal.

Mar 10

Relational Algebra with SQL Equivalents

• Read DSCB 2.4.
• Notes from Hanieh Soleimani and Michele Chersich.
• Some quick notes on using sqlite.
• The person.sql example from class.

Mar 14

Relational Algebra with SQL Equivalents

• Notes from Sebastian Poliak and Vitor Carmo Vannuchi.
• Zvi Kedem's lecture notes.
• Homework 2 on Moodle. Due Mar 21.

Mar 17

SQL as Data Manipulation Language

• Notes from Talal El Afchal and Edoardo Piazza.
• Read DSCB 6.0 – 6.2.

Mar 21

SQL as Data Manipulation Language

• Read DSCB 6.2 – 6.4.
• Notes on relational division.
• SQLite queries for division example.
• Notes from Lisa Maria Schiestel and Vitor Carmo Vannuchi.
• Zvi Kedem's lecture notes.

Mar 24

SQL as Data Definition and Control Language

• Read DSCB 7.0 – 7.3.
• Notes from Lokesh Menghani and Hanieh Soleimani.

Mar 28

SQL as Data Definition and Control Language

• Read DSCB 7.4 – 6.5.
• Note: Lecture rescheduled for 1:30-3:15pm, room SI-006.
• Notes from Sebastian Poliak and Jesper Findahl.
• Zvi Kedem's lecture notes.

Mar 31

Quiz 1

Apr 04

Logical Design with Normalization

• Read DSCB 3.0 – 3.3.
• Notes from Lisa Maria Schiestel, Talal El Afchal, and Vitor Carmo Vannuchi.

Apr 07

Logical Design with Normalization

• Read DSCB 3.4 – 3.6.
• Notes from Filippo Ferrario and Hanieh Soleimani.

Apr 11

Logical Design with Normalization

• Notes on finding a minimal cover.
• Example JDBC code.
• Zvi Kedem's lecture notes.

Apr 14

Physical Design and Query Execution Concepts

• Read DSCB 8.3 – 8.4, 13.1 – 13.2, 14.1 – 14.3.
• Zvi Kedem's lecture notes.
• Notes on B-trees.

Apr 18

Easter holiday

Apr 21

Easter holiday

Apr 25

Easter holiday

Apr 28

Physical Design and Query Execution Concepts

May 02

Quiz 2

May 05

Transaction Processing Recovery

• Read DSCB 17.0 – 17.4.
• Zvi Kedem's lecture notes.

May 09

Transaction Processing Recovery

May 12

Transaction Processing Concurrency

• Zvi Kedem's lecture notes.

May 16

Transaction Processing Concurrency

May 19

Online Analytical Processing and NoSQL Concepts I

• Zvi Kedem's lecture notes (OLAP).
• Zvi Kedem's lecture notes (NoSQL).

May 23

NoSQL Concepts II

May 26

Semi-Unstructured Data and XML

• Read DSCB 11.0 – 11.6.
• XML notes.

May 30

XPath, XQuery, and XSLT

• Read DSCB 12.0 – 12.4.
• W3C tutorials on: XPath, XQuery, and XSLT.
• BaseX for command-line XQuery.
• xsltproc for command-line XSLT.
• XML language notes.

June 20

Final Exam at 13:30 in Room SI-008

Tools and Resources

Use this template for submitting your lecture notes.

Microsoft Visio is available to USI students free of charge with the Microsoft Developer Network Academic Alliance.

If you are running OSX or Linux, please use a virtual machine monitor such as VMWare to run Windows 8.1, which is also available through the MSDNAA.

Acknowledgements