HSM University
Explore
Engineering
Architecture
Business
Health Sciences
Science
Arts & Social Sciences
Film & Photography
Law
Skilled Trades
Safety Training
Construction Management
Professional Certifications
Test Prep
Professional Development
Browse all courses

Introduction to Databases

Relational databases from the ground up: what a DBMS provides and the three-schema architecture, relations, keys and relational algebra, SQL for defining and changing data with constraints and referential actions, queries with joins, NULL logic and aggregation, subqueries, views, window functions and recursive queries, entity–relationship modelling and mapping diagrams to tables, functional dependencies and normalization to BCNF and 3NF, storage, B+-tree indexes, join algorithms and query plans, transactions, isolation levels, MVCC and log-based recovery, safe database programming against SQL injection with access control and Canadian privacy law, and a capstone clinic database with a survey of NoSQL, all with real SQLite examples.

CMPUT-291 Year 2 3 credits 12 lessons · 6 h 19 min

Offered by HSM University · Computing Science

This is the Canadian version of this course. See the American version.

What you'll learn

  • Explain what a DBMS provides beyond file storage, including data independence, declarative querying, integrity, concurrency control and recovery.
  • Define relations, keys and integrity constraints, and write relational algebra expressions using selection, projection, set operations, products and joins.
  • Create tables with appropriate types and constraints, and insert, update and delete data safely in SQL, including referential actions.
  • Write SQL queries with joins of every kind, aggregation, grouping and set operations, reasoning correctly about NULL and three-valued logic.
  • Write advanced SQL with subqueries, EXISTS, relational division, common table expressions, views, CASE, window functions and recursive queries.
  • Build entity–relationship models from requirements, with cardinalities, participation, weak entities and specialization.
  • Map ER diagrams to relational schemas, choosing foreign key placement, junction tables and hierarchy mappings.
  • Use functional dependencies, attribute closure and candidate keys to test normal forms, and produce lossless BCNF or 3NF decompositions.
  • Explain how pages, buffer pools, B+-tree indexes, join algorithms and query optimization determine performance, and choose indexes by reading query plans.
  • Explain transactions and the ACID properties, recognize concurrency anomalies, test serializability, and describe locking, MVCC, isolation levels and log-based recovery.
  • Access databases safely from programs with parameterized queries, apply least-privilege access control and encryption, and design for Canadian privacy law such as PIPEDA and Quebec's Law 25.
  • Carry a database project from requirements to a tested, indexed schema, and judge when a NoSQL data model is a better fit.

Course content

12 lessons · 6 h 19 min total · final exam

  1. 1 What databases are for 32 min · quiz
  2. 2 The relational model and relational algebra 32 min · quiz
  3. 3 SQL I: defining and modifying data 31 min · quiz
  4. 4 SQL II: queries, joins and aggregation 31 min · quiz
  5. 5 SQL III: subqueries, views, window functions and recursive queries 33 min · quiz
  6. 6 Entity–relationship modelling 31 min · quiz
  7. 7 From ER diagrams to tables 31 min · quiz
  8. 8 Functional dependencies and normalization 31 min · quiz
  9. 9 Storage, indexing and query processing basics 31 min · quiz
  10. 10 Transactions, concurrency and recovery 33 min · quiz
  11. 11 Database applications and security 32 min · quiz
  12. 12 Capstone: designing and implementing a database, plus NoSQL and next steps 31 min · quiz
  13. E Final exam 20 questions

Requirements

Recommended background. You can start any course at any time.

Description

Relational databases from the ground up: what a DBMS provides and the three-schema architecture, relations, keys and relational algebra, SQL for defining and changing data with constraints and referential actions, queries with joins, NULL logic and aggregation, subqueries, views, window functions and recursive queries, entity–relationship modelling and mapping diagrams to tables, functional dependencies and normalization to BCNF and 3NF, storage, B+-tree indexes, join algorithms and query plans, transactions, isolation levels, MVCC and log-based recovery, safe database programming against SQL injection with access control and Canadian privacy law, and a capstone clinic database with a survey of NoSQL, all with real SQLite examples.

Part of the Science stream, typically taken in year 2.