Welcome to EECS3421 (M Section)
Introduction to Database Management Systems



About

Course Description

This course provides an introduction to the fundamental concepts of database management, including aspects of data models, database languages, and database design. At the end of this course, a student will be able to understand and apply the fundamental concepts required for the use and design of database management systems.

Topics
  • Relational Data Model
  • Relational Algebra
  • SQL Query Language
  • SQL in a Server Environment
  • The Entity/Relationship Model
  • Design Theory and Normalization
  • The Semi-structured Data Model (XML)
  • Programming Languages for XML (XPath/XQuery)
  • Advanced Topics (Transactions, Concurrency Control, and Recovery)
Lectures & Office Hours

Lectures: Wed 19:00-21:00PM at LSB 105 (Life Sciences Building)

Tutorial: Wed 21:00-22:00PM at LSB 105 (Life Sciences Building)

Office Hours: Wed 13:00PM at LAS 3050 (Lassonde Building, third floor)

Team

Manos Papagelis (papaggel@gmail.com)

Hoorieh Marefat

Md Tahmid Rahman Laskar

Tilemachos Pechlivanoglou

Textbooks

The course will rely mainly on the following textbooks.

Syllabus

Download the syllabus (v1.0)

Download the remarking request form

Handouts

Lecture 1. Introduction, Course Administrivia, Overview of a DBMS [Slides]

Introduction, course administrivia, early database management systems, relational database systems, overview of a DBMS, outline of database system studies.

Readings:

  • Chapter 1
Lecture 2. The Relational Model [Slides]

Overview of data models, basics of the relational model (relation, schema, attributes, tuples), value-based references, the null value, integrity constraints, keys, primary keys, foreign keys, referential constraints.

Readings:

  • Sections 2.1-2.3
Lecture 3. Relational Algebra [Slides]

Algebraic query language, what is an algebra, overview of relational algebra (set operations on relations, projection, selection, cartesian product, natural joins, theta-joins, combining operations to form queries, naming and renaming, relationships among operations), constraints on relations (referential itegrity constraints, key constraints, additional constraints), relational operations on bags (union, intersection, and difference of bags, projection, selection, product, joins), extended operators of relational algebra (duplicate elimination, aggregation operators, grouping, the grouping operator, extending the projection operator, the sorting operator, outerjoins).

Readings:

  • Sections 2.4, 2.5 and 5.1, 5.2
Lecture 4. Structured Query Language (SQL) [Slides]

Simple queries in SQL (projection, selection, comparison of strings, pattern matching, dates and times, NULL values and comparisons involving NULL, ordering output), queries involving more than one relation (products, joins, disambiguating attributes, tuple variables, interpreting multirelation queries, union, intersection, difference), subqueries (subqueries that produce scalar values, conditions involving relations and tuples, correlated subqueries, subqueries in FROM clauses, join expressions, natural joins, outerjoins), full-relation operations (eliminating duplicates, duplicates in unions/intersections/differences, grouping and aggregation, aggregation operators, grouping and aggregation with NULLs, HAVING clause).

Readings:

  • Sections 6.1-6.4
Lecture 5. Data Definition Lang. (DDL), Data Manipulation Language (DML); Views; Indexes [Slides]

Database modifications (insert, delete, update), keys and foreign keys, constraints on attributes and tuples, modification of constraints, assertions, triggers, virtual views, modifying views, materialized views, indexes, database tuning.

Readings:

  • Section 6.5, Chapter 7 (except 7.5), Chapter 8 (except "trigger" example)
Lecture 6. Non-interactive SQL [Slides]

The SQL environment, the SQL/host-language interface, stored procedures, Using a call-level interface, JDBC.

Readings:

  • Sections 9.1-9.6
Lecture 7. SQL Security [Slides]

SQL injection, first-order SQL injection, second-order SQL injection, SQL injection solutions, mitigating the impact of SQL injection attacks.

Readings:

Lecture 8. The Entity/Relationship (ER) Model & DB Design [Slides]

The Entity/Relationship (ER) model, design principles, constraints in the E/R model, weak entity sets, from E/R diagrams to relational designs.

Readings:

  • Sections 4.1 to 4.5 (except subclasses: 4.1.11)
Lecture 9. Design Theory for Relational Databases [Slides]

Functional dependencies, schema decomposition, normal forms.

Readings:

  • Sections 3.1 to 3.5
Lecture 10. Database Transactions [Slides]

Transactions, ACID properties, COMMIT, ROLLBACK, isolation levels.

Readings:

  • Sections 6.6
Lecture 11. NoSQL [Slides]

Structured, unstructured, semi-structured data, What is NOSQL, NOSQL taxonomy.

Readings:

  • to be filled.
Lecture 12. Wrap-Up [Slides]

Wrap-up of the class.

Assignments

Tutorials

Resources

Online resources related to Relational Algebra
  • RelaX Relational Algebra Calculator
Online resources related to Database Management Systems
Interactive SQL: A Sample Database to Practice SQL
  • Data base dump PostgreSQL port of the "World" database.
  • Questions Questions to motivate interactive exploration (querying)of the data set
  • Answers to the questions above about interactive exploration (querying)of the data set
  • Setup Setup information to get you running
Embedded SQL: Sample Code for Running SQL commands within a Program
The Entity/Relationship (ER) Model & DB Design