Databases (2010/2011)

Course code
Alberto Belussi
Other available courses
Other available courses
Other available courses
    Academic sector
    Language of instruction
    Teaching is organised as follows:
    Activity Credits Period Academic staff Timetable
    Teoria 9 II semestre, I semestre Alberto Belussi
    Laboratorio 3 II semestre Alberto Belussi

    Lesson timetable

    Learning outcomes

    Theory I
    The first part of the course, called “Databases Theory”, has the aim to provide the student with the necessary concepts and methods for the design of a database and its applications. In particular, it will be focused on the methodologies for the conceptual and logical design of a database and for the successive database implementation on database systems. Moreover, some general techniques for the implementation of database systems will be presented and the fundamental characteristics of the query language SQL and of the relational algebra will be illustrated.

    Theory II
    The second part of the course, called “Web and multimedia Applications”, has the aim to provide the student with the necessary concepts and methods for the design of data-intensive web applications. In particular, this module will be focused on a methodology for the logical design of a web application that interacts with a database system (DBMS).
    Moreover, the approach Model-View-Controller (MVC-2 servlet centric) will be illustrated and applied to some examples. Finally, the main issues concerning the treatment of multimedia datasets in a DBMS will be presented.

    The “Lab” ("Laboratorio" in italian) of the course “Databases and WEB” has the aim to provide the student with the necessary concepts and methods for the implementation of a database and its applications. In particular, this module will be focused on the usage of a specific DBMS for the creation, management and manipulation of a database by means of SQL commands. Moreover, some general techniques for the implementation of web applications will be presented. In particular a MVC-2 architecture based on the Servlet and the Java Server Pages (JSP) technologies will be illustrated and used to show the implementation of some practical examples.


    Theory I
    * Introduction to database managemente systems (DBMS): architectures and functionalities of a DBMS. Phisical and logical data independence. Data models. Concepts of model, schema and instance of a database. Languages for database systems. DBMS vs. file system.
    * Conceptual database design: conceptual data models. The Entity-Relationship model (ER). Elements of the ER model: entities, attributes, relationships, ISA hierarchies and cardinality constraints.
    * Logical database design: logical data models, the relational data model. Elements of the relational data models: relations and integrity constraints. Mapping between conceptual schemas in ER model and logical schema in the relational model. Languages for data definition: SQL as DDL. Table creation and integrity constraints definition in SQL.
    * Interacting with a database system: languages for the definition, querying and update of a database. The relational algebra. SQL: select-from-where statement, join in SQL, the GROUP BY and ORDER BY clauses, using subqueries. SQL for update: INSERT, DELETE and UPDATE statements. Views.
    * The internal architecture of a DBMS. Transactions. Transactions properties. The concurrency control: schedules, view and conflict equivalence, the two-phase locking. Access methods (indexes): primary and secondary indexes, B-+tree, hashing based access methods.

    Theory II
    The module is organized in 16 hours of lessons and exercises carried out during the second semester. The module will focuses on the logical design of a data-intensive web application and its implementation using: Postgresql, Servlet and Java Server Pages (JSP) technologies. Moreover, the issues concerning the treatment of multimedia datasets in a DBMS will be presented.
    In particolar, the following arguments will be illustrated:
    * Web Server: basic internet, basic HTML (form), basic HTTP.
    * Information systems on the Web: techniques for the interaction between a DBMS and a HTTP server (CGI, Servlet, JSP); JDBC library and Java data beans; the architecture proposed by the MVC-2 approach.
    * a methodology for the logical design of a data-intesive web application.
    * XML
    * features of multimedia data: compression techniques and indexing

    1. Introduction to DBMS PostgreSQL: connection to a database, table creation, insert, update and alter table.
    2. PostgreSQL: integrity constraints, reactions to contraints violations.
    3. PostgreSQL: simple SQL queries.
    4. PostgreSQL: complex SQL queries.
    5. HTML: structure of an HTML document, main tags, tables.
    6. HTML: form.
    7. Servlet Engine on an application server. Examples of simple servlets.
    8. Servlet and JDBC. Connection to a DBMS PostgreSQL. Simple servlet of the interaction with a DBMS.
    9. Java bean e servlet.
    10. Java Server Pages (JSP)
    11. JSP and servlet: a software architecture base on the Model View Controller approach.
    12. Servlet/JSP and multimedia datasets.

    Assessment methods and criteria

    Written tests.

    Reference books
    Activity Author Title Publisher Year ISBN Note
    Teoria P. Atzeni, S. Ceri, P. Fraternali, S. Paraboschi, R. Torlone Basi di dati: architetture e linee di evoluzione (Edizione 2) McGraw-Hill 2007 978-88-386-6370-3
    Teoria P. ATZENI, S. CERI, S. PARABOSCHI, R. TORLONE Basi di dati- Modelli e linguaggi di interrogazione (Edizione 3) McGraw-Hill 2009 9788838666001
    Teoria E. Baralis, A. Belussi, G. Psaila Basi di dati - Temi d'esame svolti (Edizione 1) Progetto Leonardo Società Editrice Esculapio Bologna 1999 B135655713
    Teaching aids
    Title Format (Language, Size, Publication date)
    Algebra relazionale e Ottimizzazione  pdfpdf (it, 306 KB, 16/01/11)
    Applicazioni Web: dispensa  pdfpdf (it, 283 KB, 05/04/11)
    Applicazioni Web: esempio servlet e JDBC  pdfpdf (it, 147 KB, 04/04/11)
    Applicazioni Web: JavaBeans  pdfpdf (it, 351 KB, 23/05/11)
    Applicazioni Web: JDBC  pdfpdf (it, 320 KB, 29/03/11)
    Applicazioni Web: JSP  pdfpdf (it, 363 KB, 11/04/11)
    Applicazioni Web: metodologia di progettazione di siti web centrati sui dati  pdfpdf (it, 272 KB, 05/04/11)
    Applicazioni Web: MVC-2  pdfpdf (it, 339 KB, 23/05/11)
    Applicazioni Web: protocollo HTTP  pdfpdf (it, 272 KB, 28/03/11)
    Applicazioni Web: servlet  pdfpdf (it, 248 KB, 29/03/11)
    Dati multimediali: Codifica Huffman e LZW  pdfpdf (it, 327 KB, 09/05/11)
    Dati multimediali: codifica JPEG  pdfpdf (it, 688 KB, 09/05/11)
    Dati multimediali: introduzione e concetti di base  pdfpdf (it, 281 KB, 09/05/11)
    Dati multimediali: K-d-tree  pdfpdf (it, 294 KB, 23/05/11)
    DTD  pdfpdf (it, 267 KB, 02/05/11)
    Esercizi sul modello ER  pdfpdf (it, 72 KB, 11/10/10)
    Introduzione  pdfpdf (it, 2717 KB, 04/10/10)
    Lucidi MM su una pagina  zipzip (it, 1665 KB, 23/05/11)
    Lucidi Teoria su una pagina  zipzip (it, 3485 KB, 29/03/11)
    Lucidi Web su una pagina  zipzip (it, 2079 KB, 23/05/11)
    Lucidi XML su una pagina  zipzip (it, 1768 KB, 19/04/11)
    Progettazione logica  pdfpdf (it, 3616 KB, 30/11/10)
    Seconda prova intermedia: tema d'esame risolto  pdfpdf (it, 291 KB, 09/06/11)
    SQL - prima parte  pdfpdf (it, 234 KB, 17/01/11)
    SQL - seconda parte  pdfpdf (it, 193 KB, 18/01/11)
    SQL - terza parte  pdfpdf (it, 229 KB, 30/01/11)
    Strutture fisiche: prima parte (file sequenziale, indici primari e secondari)  pdfpdf (it, 431 KB, 22/03/11)
    Strutture fisiche: seconda parte (B+-tree e Hashing)  pdfpdf (it, 805 KB, 22/03/11)
    Temi d'esame  zipzip (it, 1047 KB, 09/05/11)
    Transazioni  pdfpdf (it, 216 KB, 02/03/11)
    Transazioni: gestione della concorrenza - prima parte  pdfpdf (it, 355 KB, 14/03/11)
    Transazioni: gestione della concorrenza - seconda parte  pdfpdf (it, 384 KB, 21/03/11)
    XML  pdfpdf (it, 745 KB, 19/04/11)
    XML: esercizio  pdfpdf (it, 141 KB, 02/05/11)
    XML: esercizio soluzione  pdfpdf (it, 124 KB, 23/05/11)
    XML-Schema  pdfpdf (it, 800 KB, 02/05/11)
    Esercitazione 01: esercizio  pdfpdf (it, 44 KB, 01/03/11)
    Esercitazione 01: lucidi  pdfpdf (it, 588 KB, 01/03/11)
    Esercitazione 02: esercizio  pdfpdf (it, 132 KB, 08/03/11)
    Esercitazione 02: lucidi  pdfpdf (it, 1100 KB, 08/03/11)
    Esercitazione 03: esercizio  pdfpdf (it, 74 KB, 15/03/11)
    Esercitazione 03: lucidi  pdfpdf (it, 544 KB, 15/03/11)
    Esercitazione 04: esercizio  pdfpdf (it, 128 KB, 22/03/11)
    Esercitazione 04: lucidi  pdfpdf (it, 401 KB, 22/03/11)
    Esercitazione 05: esercizio  pdfpdf (it, 50 KB, 29/03/11)
    Esercitazione 05: lucidi  pdfpdf (it, 1654 KB, 29/03/11)
    Esercitazione 06: esercizio  pdfpdf (it, 40 KB, 05/04/11)
    Esercitazione 06: lucidi  pdfpdf (it, 994 KB, 05/04/11)
    Esercitazione 06: tomcat  zipzip (it, 34 KB, 05/04/11)
    Esercitazione 07: esercizio  pdfpdf (it, 77 KB, 12/04/11)
    Esercitazione 07: lucidi  pdfpdf (it, 750 KB, 12/04/11)
    Esercitazione 07: servlet con bean  zipzip (it, 4 KB, 12/04/11)
    Esercitazione 07: servlet senza bean  zipzip (it, 2 KB, 12/04/11)
    Esercitazione 08: Corsi.jsp  octet-streamoctet-stream (it, 1 KB, 19/04/11)
    Esercitazione 08: EsempiJSP  zipzip (it, 1 KB, 19/04/11)
    Esercitazione 08: esercizio  pdfpdf (it, 42 KB, 19/04/11)
    Esercitazione 08: lucidi  pdfpdf (it, 747 KB, 19/04/11)
    Esercitazione 09: daFare.jsp  octet-streamoctet-stream (it, 0 KB, 03/05/11)
    Esercitazione 09: ElencoCorsiStudio.jsp  octet-streamoctet-stream (it, 0 KB, 03/05/11)
    Esercitazione 09: esercizio  pdfpdf (it, 47 KB, 03/05/11)
    Esercitazione 09: lucidi  pdfpdf (it, 360 KB, 03/05/11)
    Esercitazione 09:  octet-streamoctet-stream (it, 3 KB, 03/05/11)
    Esercitazione 10: esercizio  pdfpdf (it, 65 KB, 17/05/11)
    Esercitazione 10: libreria cos  octet-streamoctet-stream (it, 55 KB, 17/05/11)
    Esercitazione 10: lucidi  pdfpdf (it, 544 KB, 17/05/11)
    Esercitazione 10: photos.JPS  x-gzipx-gzip (it, 0 KB, 17/05/11)
    Esercitazione 10: photos.src  x-gzipx-gzip (it, 4 KB, 17/05/11)
    Esercitazione 10: photos.webapp  x-gzipx-gzip (it, 77 KB, 17/05/11)
    Esercitazione 11: classi COMMAND, comando EXPLAIN, cenni a portle e AJAX  pdfpdf (it, 1924 KB, 24/05/11)
    Laboratorio di basi di dati e MM: Tema d'esame  pdfpdf (it, 217 KB, 10/05/11)
    Laboratorio di basi di dati e Web: Tema d'esame  pdfpdf (it, 216 KB, 10/05/11)