Introduction to Databases

Course parts and corresponding teaching material

[LN1] Introduction to databases
[LN2] The relational model
[LN3] The SQL language
[LN4] Database access via software
[LN5] Conceptual database design
[LN6] Logical-physical database design

Lectures 1,2 - 15/10/2018

• Topics [LN1, LN2]
  • Presentation of the course
  • Information system architecture based on a DBMS
  • Databases and database managememnt systems
  • Relational model: schema and data
  • Null values

Lectures 3,4 - 17/10/2018

• Topics [LN2]
  • Structure of a relational schema
  • Integrity constraints in the relational model
  • Intra-relazional constraints (tuple-constraints, keys)
  • Inter-relazional constraints (foreing keys)

Lectures 5,6 - 22/10/2018

• Topics [LN2]
  • Introduction to relational algebra
  • Queries in relational algebra using union, intersection, difference, renaming, selection, projection, and natural join

Lectures 7,8 - 24/10/2018

• Topics [LN2]
  • The theta-join operator of relational algebra
  • Treatment of null values in queries
  • Outer joins
  • Exercises on relational algebra queries

Exercises 1,2 - 24/10/2018

• Topics [LN2]
  • Understanding queries in relational algebra
  • Formulating queries in relational algebra

Lectures 9,10 - 29/10/2018

• Topics [LN3]
  • Introduction to the SQL language
  • Data definition in SQL: creation, deletion, and modification of tables, definition of inter-relational and intra-relational constraints
  • Data manipulation in SQL: insertion, deletion, and update of tuples

Lectures 11,12 - 31/10/2018

• Topics [LN3]
  • Simple queries in SQL, which express selection, projection, renaming, and join
  • NULL values in SQL

Exercises 3,4 - 31/10/2018

• Topics [LN3]
  • Accessing a Postgres DBMS via PgAdmin
  • Formulating simple queries in SQL

Lectures 13,14 - 5/11/2018

• Topics [LN3]
  • SQL queries with explicit joins
  • Outer joins in SQL
  • SQL queries using aggregation operators and grouping
  • Set operators in SQL

Lectures 15,16 - 7/11/2018

• Topics [LN3]
  • Nested queries
  • Inline views SQL

Exercises 5,6 - 7/11/2018

• Topics [LN3]
  • Formulating nested queries in SQL
  • Formulating queries using inline views in SQL

Lectures 17,18 - 12/11/2018

• Topics [LN3]
  • Generic integrity constraints
  • Views in SQL
  • Access control and privileges
  • Transactions
  • Stored procedures and triggers (in PL/pgSQL)

Lectures 19,20 - 14/11/2018

• Topics [LN4]
  • JDBC

Exercises 7,8 - 14/11/2018

• Topics [LN3]
  • Defining relations with constraints
  • Defining transactions to populate a database
  • Defining views

Lectures 21,22 - 19/11/2018

• Topics [LN5]
  • Lifecycle of database applications
  • Introduction to conceptual database design
  • Conceptual models and the Entity-Relationship (ER) model
  • The notions of entity, attribute, and relation

Lectures 23,24 - 21/11/2018

• Topics [LN5]
  • The notion of role
  • Exercises on conceptual modeling

Exercises 9,10 - 21/11/2018

• Topics [LN3, LN4]
  • Defining triggers
  • Interacting with a DBMS via JDBC

Lectures 25,26 - 28/11/2018

• Topics [LN5]
  • Choice between modeling constructs of the ER model
  • ISA relation between entities
  • Generalization between entities
  • Inheritance

Lectures 27,28 - 28/11/2018

• Topics [LN5]
  • Graph of the hierarchies
  • ISA relation and generalization between relationships
  • Cardinality constraints on entities
  • Cardinality constraints on attributes

Lectures 29,30 - 3/12/2018

• Topics [LN5]
  • Identification constraints for entities
  • Identification constraints for relationships

Lectures 31,32 - 5/12/2018

• Topics [LN5]
  • External constraints
  • Documentation accompanying the ER diagram
  • Transformations of an ER diagram

Exercises 11,12 - 5/12/2018

• Topics [LN5]
  • Exercises on conceptual design

Lectures 33,34 - 10/12/2018

• Topics [LN5, LN6]
  • Quality control of the conceptual schema (correctness, minimality, readability).
  • Design methodology for ER diagrams
  • Phases of logical design
  • Restructuring the ER schema: redundancy analysis, elimination of multi-valued attributes, elimination of composite attributes

Lectures 35,36 - 12/12/2018

• Topics [LN6]
  • Restructuring the ER schema: elimination of ISA and of generalizations, choice of primary identifiers, specification of additional constraints
  • Exercises on restructuring the ER schema

Exercises 13,14 - 12/12/2018

• Topics [LN5, LN6]
  • Exercises on conceptual design and on restructuring of the ER schema

Lectures 37,38 - 17/12/2018

• Topics [LN6]
  • Direct translation of entities into relations
  • Direct translation of relationships into relations
  • Merging of relationships into entities
  • Managing of additional contraints during the translation from the ER schema
  • Exercises on direct translation to the relational model

Exercises 15,16 - 19/12/2018

• Topics [LN6] - Held only by Davide Lanti
  • Exercises on direct translation to the relational model

Lectures 39,40 - 8/1/2019

• Topics [LN6]
  • Cost model to evaluate efficiency of operations
  • Restructuring the relational schema to reduce cost: horizontal and vertical partitioning, merging of relations
  • Exercises on restructuring the relational schema

Exercises 17,18 - 9/1/2019

• Topics [LN6]
  • Exercises on restructuring the relational schema

Exercises 19,20 - 15/1/2019

• Topics [LN6]
  • Solving of exam-like exercises

Exercises 21,22 - 16/1/2019

• Topics [LN4]
  • Exercises on the ER model

home page of the course