09679 - Information Systems

Learning outcomes

At the end of the course students know the basic theoretical notions needed to design, query, and update a database. Students will be able to:

• understand the structure of a relational database and the constraints on the data
• translate a large class of queries into the query language of a relational database management system
• update the database
• manage database authorizations
• analyse the requirements of the application
• translate the requirements into an Entity-Relationship schema
• create the relational tables storing the application's data
• design a basic data warehouse and perform simple data analysis sessions using multidimensional operations and knowledge discovery techniques
  
  
  
  

Course contents

• Introduction: Data bases, data models, data base management systems
• The relational model of data: Attributes, relation schemata and data base schemata, tuples, relations and data bases, keys and superkeys, relational algebra, referential integrity, functional dependencies, dependency theory and normal forms
• The SQL language: Introduction, elements of the language, single table queries, join and subqueries, grouping and column functions, correlated subqueries, outer joins, set operations and ordering, data manipulation, data control and definition
• Entity-Relationship conceptual design: Entities, Relationships, attributes, generalization hierarchies, identifiers, ER schema design from natural language requirements
• Logical design from ER schemata to relational schemata: Selection of primary keys, elimination of generalization hierarchies, elimination of external identifiers, elimination of composite and multivalued attributes, translation of entities and relationships into relation schemata
• Data warehousing and knowledge discovery in databases: The multidimensional model of data, operations in the multidimensional model (outline), Knowledge Discovery in Databases (outline)
  
  

Readings/Bibliography

• P. Atzeni, S. Ceri, S. Paraboschi, R. Torlone: Basi di dati - modelli e linguaggi di interrogazione, McGraw-Hill, Milano, 2009.
• R.F. VAN DER LANS: Introduzione a SQL (2nd ed.), Addison-Wesley, Milano, 2001.

Teaching methods

The theoretical and practical notions for the design of queries,
for the management of a database, and for the design of database
schemata are explained in frontal classes. In laboratory classes,
students are encouraged to design increasingly difficult SQL
queries and to test their correctness on the DBMS at hand. Case
studies in database design are presented and the alternative
solutions are evaluated, encouraging contributions of the students
to class discussions.

Assessment methods

• Examination on SQL queries in laboratory
• Written examination on ER design
• Oral examination

Teaching tools

• PC and overhead projector
• Laboratory with desktop PCs, Microsoft SQL Server, Microsoft Access

Links to further information

http://www-db.deis.unibo.it/~slodi/SI-FAI/2010-2011/si.html

Office hours

See the website of Stefano Lodi