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 DETAILS AND INSTRUCTIONS CAN BE FOUND ON OLE.

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.

The document detailing the project specification IS POSTED ON OLE.


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 LEC 8 LAB 3 LAB 4
06/04 SQL LEC 9 LAB 5 -
13/04 Conceptual design - LEC 10 LEC 11
20/04 Conceptual design LEC 12 LEC 13 LAB 6
27/04 Conceptual design LEC 14 LEC 15 -
04/05 Logical design LEC 16 LEC 17 LAB 7
11/05 Logical design LEC 18 LEC 19 LAB 8
18/05 NoSQL + exercises LEC 20 LAB 9 -
25/05 exercises LAB 10 - -


LAB 10

Exam-like exercises

LAB 9

Exercises on restructuring the relational schema

LEC 20

NoSQL

LAB 8

Exercises on direct translation to the relational model

LEC 19

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

LEC 18

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

LAB 7

Exercises on conceptual design and on restructuring of the ER schema

LEC 17

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

LEC 16

Restructuring the ER schema 2/2: elimination of ISA and of generalizations, choice of primary identifiers, specification of additional constraints

LEC 15

Quality control of the conceptual schema (correctness, minimality, readability).
Design methodology for ER diagrams
Phases of logical design
Restructuring the ER schema 1/2: redundancy analysis, elimination of multi-valued attributes, elimination of composite attributes

LEC 14

External constraints
Documentation accompanying the ER diagram
Transformations of an ER diagram

LAB 6

Exercises on conceptual design

LEC 13

Identification constraints for entities
Identification constraints for relationships

LEC 12

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

LEC 11

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

LEC 10

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

LAB 5

Defining triggers
Interacting with a DBMS via JDBC

LEC 9

Stored procedures and triggers (in PL/pgSQL)
JDBC

LAB 4

Defining relations with constraints
Defining transactions to populate a database
Defining views

LAB 3

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

LEC 8

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

LEC 7

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

LAB 2

Accessing a Postgres DBMS via PgAdmin
Formulating simple queries in SQL

LEC 6

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

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

LAB 1

Formulating queries in Relational Algebra

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
Paolo Felli, PhD
RTDA
Free University of Bozen-Bolzano
Piazza Domenicani 3
room 2.03
39100 Bolzano, Italy
name.surname@unibz.it