Modeling and Databases - Introduction to Databases for Business Informatics

2019/20 - #76404B

This is the Module 2 of the course Modeling and Databases (76404):
Students attending this course will study and put into practice languages, methodologies, and techniques for modelling data, business processes and decisions that are instrumental to the creation of information systems supporting contemporary organizations in their operations management. In addition, they will be able to translate a data model into a corresponding database, and learn how to make use of the basic functionalities (definition, update, and querying) of database management systems in the context of development and deployment of information systems. The course focuses specifically on relational databases, the SQL language, and software programs accessing them, but the taught methods and principles are of a more general nature, and can be applied also in those contexts where data models and database systems different from relational ones are adopted.

Office hours: Every Tuesday 10:00AM via Skype or other days/time if required (all by email appointment).

Exam format: The final mark will be based on (1) an individual/group project and (2) a final written exam. The final exam will have more impact on the final mark than the project. More info will be posted later.

Syllabus: Course Syllabus  


Teaching material

  • Course Slides. The slides will be made available during the course and can be downloaded from the course page in the OLE system.
  • Esercises solved in class. The exercises will be assigned for the exercise hours, and the solutions will be made available in the following week on the course page in the OLE system.
  • Textbook. Database Management Systems (3rd edition). Raghu Ramakrishnan, Johannes Gehrke. McGraw-Hill, 2005. Available at University Library Bozen: 13-Textbook Collection (ST 271). The textbook is suggested, but not strictly necessary.

News

The course should be visible in OLE. For any issue you can contact me.
The course will start on the 26th. On the 28th there will be a LEC, while all future Fridays will have a LAB.
The start date of the course has been postponed to the following week, in accordance to ministerial guidelines.
The lecture of Friday 6th is canceled: I am arranging for the TEAMS platform. It should all be set up for the next monday.


Teaching Register


week topic monday wednesday friday
02/03 Intro, Relational model - LEC 1 -
09/03 Relational Algebra LEC 2 LEC 3 -
16/03 SQL (from LEC 5) LEC 4 LAB 1 LEC 5
23/03 SQL LEC 6 LAB 2 LEC 7
30/03 SQL (programming) LEC 8 LAB 3 LEC 9
06/04 Conceptual design (from LEC 10) LEC 10 ... ...

LEC 1-2-3-4

Presentation of the course
Information system architecture based on a DBMS
Databases and database managememnt systems
Relational model: schema and data
Null values
Structure of a relational schema
Integrity constraints in the relational model
Intra-relazional constraints (tuple-constraints, keys)
Inter-relazional constraints (foreing keys)

Introduction to relational algebra
Queries in relational algebra using union, intersection, difference, renaming, selection, projection, natural join, and theta-join
Exercises on relational algebra queries combining different operators
Treatment of null values in queries
Outer joins

LAB 1

Formulating queries in Relational Algebra


LEC 5

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

LEC 6

Simple queries in SQL, which express selection, projection, renaming, and join
NULL values in SQL

LAB 2

Accessing a Postgres DBMS via PgAdmin
Formulating simple queries in SQL

LEC 7

SQL queries with explicit joins
Outer joins in SQL
SQL queries using aggregation operators and grouping
Set operators in SQL
Nested queries

LEC 8

Inline views SQL
Generic integrity constraints
Views in SQL
Access control and privileges
Transactions
Stored procedures and triggers (in PL/pgSQL)

LAB 3

Formulating nested queries in SQL
Formulating queries using inline views in SQL

LEC 9

JDBC

LEC 10


Paolo Felli, PhD
RTDA
Free University of Bozen-Bolzano
Piazza Domenicani 3
room 2.03
39100 Bolzano, Italy
name.surname@unibz.it