Modeling and Databases - Introduction to Databases for Business Informatics

2020/21 - #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 modeling 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 Teams or other days/time if required (all by email appointment).

Syllabus: Course Syllabus  OLE page

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.
• Exercises done in class. Solutions will be made available typically on the next day 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

none

Teaching Register

week	topic	wednesday	friday
22/02	Intro, Relational model	LEC 1 + LEC 2	LEC 3
01/03	SQL (from LEC 5)	LEC 4 + LAB 1	LEC 5
08/03	SQL	LEC 6 + LAB 2	LEC 7
15/03	SQL	LAB 3 + LAB 4	-
22/03	SQL + ER	LEC 8 + LAB 5	LEC 9
29/03	ER	LEC 10 + LEC 11	-
6/04	ER	LEC 12 + LAB 6	LEC 13

LAB 6

Exercises on conceptual design

LEC 12

Identification constraints for relationships

LEC 11

Identification constraints for entities

LEC 10

Graph of the hierarchies
ISA relation and generalization between relationships
Cardinality constraints on entities
Cardinality constraints on attributes

LEC 9

The notion of role
Exercises on conceptual modeling
Choice between modeling constructs of the ER model
ISA relation between entities
Generalization between entities
Inheritance

LAB 5

Stored procedures and triggers (in PL/pgSQL)
JDBC

LEC 8

Life cycle of database applications
Introduction to conceptual database design
Conceptual models and the Entity-Relationship (ER) model
The notions of entity, attribute, and relation

LAB 4

Defining relations with constraints
Defining transactions to populate a database
Reaction to updates and deletes
Deferred constraints
Defining views

LAB 3

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

LEC 7

Inline views SQL
Generic integrity constraints
Views in SQL
Access control and privileges
Transactions

LAB 2

Accessing a Postgres DBMS via PgAdmin
Formulating simple queries in SQL

LEC 6

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

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
Simple queries in SQL, which express selection, projection, renaming, and join
NULL values in SQL

LAB 1

Relational algebra

LEC 4

Relational algebra: exercises

LEC 3

Relational Algebra: operators
Exercises

LEC 2

Schemata and instances
Incomplete information
Integrity constraints
Relational Algebra: operators

LEC 1

Introduction
The Relational Model
Mathematical relations
Relations in the RM