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.
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.
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.
Intro, Relational model
SQL (from LEC 5)
Conceptual design (from LEC 10)
Presentation of the course
Information system architecture based on a DBMS
Databases and database managememnt systems
Relational model: schema and data
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
Formulating queries in Relational Algebra
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
Accessing a Postgres DBMS via PgAdmin
Formulating simple queries in SQL
SQL queries with explicit joins
Outer joins in SQL
SQL queries using aggregation operators and grouping
Set operators in SQL
Inline views SQL
Generic integrity constraints
Views in SQL
Access control and privileges
Stored procedures and triggers (in PL/pgSQL)
Formulating nested queries in SQL
Formulating queries using inline views in SQL
Paolo Felli, PhD
Free University of Bozen-Bolzano
Piazza Domenicani 3
39100 Bolzano, Italy