343 Introduction to Databases

Concepts, approaches, and techniques in database management systems (DBMS): relational databases, querying and updating a database, query language SQL, database constraints and database design, elements of database technology.

[Up]

Prerequisistes

CSC263H1/265H1/(228H1,238H1)/378H1
CGPA 3.0

[Up]

Follow-On Courses

CS 443

[Up]

Learning Objectives

Understand relational database model, declarative and procedural query languages (relational algebra and calculus)
Learn how to use SQL to query and update relational databases, and how to use SQL together with a programming language
Understand the concept of database design, and Learn how to design good databases
Understand how a database management system operates, and how it processes queries and transactions

[Up]

Topics (not necessarily in this order)

Introduction
- concept of DBMS, general DBMS architecture
- relational databases
- querying and updating a database
- declarative vs procedural paradigms
Query languages
- relational algebra operators
- relational calculus syntax
- basic SQL queries
- equivalence of algebra and calculus
SQL
- DDL
- basic SQL queries
- views
- nested queries
- aggregation
- updates
- recursive queries
- embedded SQL
- dynamic SQL
- incomplete information and nulls
Integrity constraints and database design
- notion of integrity constraints
- functional and inclusion dependencies
- keys and foreign keys
- normal forms
- decomposition algorithms
- SQL DDL revisited (adding constraints)
- SQL assertions and/or triggers
Elements of Database Technology
- Query Processing and Optimization
  - basic stages of executing a query
  - query optimization: rewriting, join ordering, semantic optimization
  - implementing relational algebra operators, join processing
- Transaction Processing
  - notion of transaction, ACID properties
  - serializability, two-phase locking
  - notions of deadlock, deadlock prevention, crash recovery

[Up]

Textbooks

Database Managament Systems by R. Ramakrishnan, and J. Gehrke McGraw Hill, 2003.

Database Systems: The Complete Book by Hector Garcia-Molina, Jeffrey D. Ullman, Jennifer D. Widom Prentice Hall, 2002

Database Systems Concepts by A. Silberschatz, H. F. Korth, and S. Sudarshan McGraw Hill, 2006

[Up]