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  


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 - conc. design LEC 10 + LEC 11 -
5/04 ER - conc. design LEC 12 + LAB 6 LEC 13
12/04 ER - conc. design LEC 14 + LAB 7 LEC 15
19/04 Logical design LEC 16 + LAB 8 LEC 17/LAB 9
26/04 Logical design LEC 18 + LAB 10 LEC 19
03/05 Exercises LEC 20 + LAB 11 -


LAB 11

Exam preparation

LEC 20

ER understanding: checking emptiness of ER models. Discussion of the project.

LEC 19

Exercises on the restructuring of the logical schema. More examples.

LAB 10

Exercises on the restructuring of the logical schema

LEC 18

Cost model to evaluate efficiency of operations
Restructuring the relational model (logical schema) to reduce cost: horizontal and vertical decomposition, merging of relations
Exercises on restructuring the relational schema

LEC 17 - LAB 9

Exercises on the direct translation

LAB 8

Exercises on conceptual design and on the restructuring of the ER schema

LEC 16

Exercises on restructuring the ER schema
Direct translation of entities into relations

LEC 15

Restructuring the ER schema: redundancy analysis, elimination of multi-valued attributes, elimination of composite attributes, elimination of ISA and of generalizations, choice of primary identifiers, specification of additional constraints

LAB 7

Design methodology for ER diagrams
Interacting with a DBMS via JDBC

LEC 14

Quality control of the conceptual schema (correctness, minimality, readability).
Design methodology for ER diagrams
Phases of logical design
Approaches for representing events and historicized entities
Equivalence-preserving transformations: composite attributes, attributes to entities, relationships to entities.

LEC 13

Exercises on conceptual design

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)

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

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