Modeling and Databases - Introduction to Databases for Business Informatics

2021/22 - #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).

Teaching material

• Course Slides. The slides will be made available during the course via Teams (Files folder of the Team).
• Exercises done in class. Solutions will be made available typically on the next day on the course page in Teams.
• 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: all the necessary material will be covered by the slides.

News

none

Teaching Register

week	topic	wednesday	friday
28/02	Intro, Relational model	LEC 1	LEC 2 + LEC 3
07/03	SQL (from LEC 4 on)	LAB 1	LEC 4 + LEC 5
14/03	SQL	LAB 2	-
21/03	SQL	LEC 6	LAB 3 + LAB 4
28/03	Conceptual design (except LAB 5)	LEC 7	LEC 8 + LAB 5
04/04	Conceptual design	LEC 9	LEC 10 + LAB 6
11/04	Practice	LAB	-
18/04	Conceptual design	LEC 11	LAB 7
25/04	Conceptual design	LEC 12	LEC 13 + LAB 8
02/05	Conceptual restructuring	LEC 14	LEC 15 + LAB 9
09/05	Direct translation	LEC 16	LAB 10 + LAB 11
16/05	Physical design	LEC 17	LAB 12

LAB 12

Exam preparation
SQL statements for implementing logical schemas

LEC 17

Beyond the relational model
NoSQL and large-scale data

LAB 11

Physical design phase
Decomposition and merging
Exercises

LAB 10

Exercises on the direct translation of conceptual schemas into logical schemas

LEC 16

Direct translation a conceptual schema into a logical schema into logical schemas in the Relational Model
Translation of entities, relationships, entities with merging, constraints. Part 2

LAB 9

Exercises on the conceptual design pahse and on the restructuring of the conceptual diagram

LEC 15

Direct translation a conceptual schema into a logical schema into logical schemas in the Relational Model
Translation of entities, relationships, entities with merging, constraints. Part 1

LEC 14

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 8

Exercises on conceptual design and quality control

LEC 13

Design methodology for ER diagrams
Restructuring the ER schema: volumes and redundancy analysis

LEC 12

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.

LAB 7

Exercises on conceptual design

LEC 11

Identification constraints for relationships
Derived and implicit identifiers

LAB 6

Conceptual modeling 1/2

LEC 10

ISA relation and generalization between relationships
Cardinality constraints on entities
Cardinality constraints on attributes
Identification constraints for entities

LEC 9

Generalization between entities
Inheritance
Graph of the hierarchies

LAB 5

The PL/pgSQL language
Stored procedures and triggers (in PL/pgSQL)

LEC 8

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

LEC 7

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
Transactions

LAB 3

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

LEC 6

Nested queries
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 5

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

LEC 4

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 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