Databases (2012/2013)

  1. Home
  2. Teaching
  3. Bachelor's degrees
  4. Bachelor's degree in Computer Science
  5. Insegnamenti
Course code
4S00037
Credits
12
Coordinator
Alberto Belussi
Academic sector
INF/01 - INFORMATICS
Language of instruction
Italian
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

I semestre
Activity Day Time Type Place Note
Teoria Monday 11:30 AM - 1:30 PM lesson Lecture Hall C  
Teoria Tuesday 11:30 AM - 1:30 PM lesson Lecture Hall A  
II semestre
Activity Day Time Type Place Note
Teoria Monday 11:30 AM - 1:30 PM lesson Lecture Hall C  
Teoria Tuesday 11:30 AM - 1:30 PM lesson Lecture Hall A  
Laboratorio Wednesday 10:30 AM - 1:30 PM laboratorio Laboratory Delta  

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.

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

Syllabus

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

LAB
-------
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), PHP and other languages for web applications.
11. JSP and servlet: a software architecture based 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 McGraw-Hill 2003 88-386-603
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: ottimizzazione  pdfpdf (it, 293 KB, 10/12/12)
Applicazioni Web: Data Beans  pdfpdf (it, 460 KB, 15/04/13)
Applicazioni Web: esempio di servlet & JDBC  pdfpdf (it, 147 KB, 09/04/13)
Applicazioni Web: introduzione e protocollo HTTP  pdfpdf (it, 357 KB, 26/03/13)
Applicazioni Web: JDBC  pdfpdf (it, 490 KB, 08/04/13)
Applicazioni Web: JSP  pdfpdf (it, 426 KB, 16/04/13)
Applicazioni Web: metodologia (aggiornato)  pdfpdf (it, 316 KB, 15/04/13)
Applicazioni Web: MVC-2  pdfpdf (it, 450 KB, 29/04/13)
Applicazioni Web: PHP cenni  pdfpdf (it, 364 KB, 30/04/13)
Applicazioni Web: servlet  pdfpdf (it, 261 KB, 08/04/13)
Concorrenza I  pdfpdf (it, 664 KB, 05/03/13)
Concorrenza II  pdfpdf (it, 588 KB, 11/03/13)
Dati multimediali: codifica di Huffman & LZW  pdfpdf (it, 431 KB, 27/05/13)
Dati multimediali: introduzione  pdfpdf (it, 413 KB, 13/05/13)
Dati multimediali: k-d-tree  pdfpdf (it, 358 KB, 27/05/13)
Esercitazione sulla seconda prova intermedia  pdfpdf (it, 334 KB, 29/05/13)
Introduzione  pdfpdf (it, 758 KB, 01/10/12)
Progettazione logica  pdfpdf (it, 1127 KB, 13/11/12)
SQL-1  pdfpdf (it, 238 KB, 11/12/12)
SQL-2  pdfpdf (it, 241 KB, 17/12/12)
SQL-3  pdfpdf (it, 216 KB, 18/12/12)
Strategie per la progettazione concettuale  pdfpdf (it, 142 KB, 22/10/12)
Strutture di accesso ai dati I  pdfpdf (it, 560 KB, 19/03/13)
Strutture di accesso ai dati II  pdfpdf (it, 1801 KB, 19/03/13)
Transazioni  pdfpdf (it, 258 KB, 05/03/13)
XML  pdfpdf (it, 1128 KB, 30/04/13)
XML: DTD  pdfpdf (it, 284 KB, 13/05/13)
XML: esercizio  pdfpdf (it, 141 KB, 08/05/13)
XMLSchema  pdfpdf (it, 983 KB, 06/05/13)
Esercitazione 10: lucidi  pdfpdf (it, 506 KB, 29/05/13)
Esercitazione 10: php.zip  zipzip (it, 1 KB, 29/05/13)
Esercitazione 11: cos.jar  octet-streamoctet-stream (it, 55 KB, 05/06/13)
Esercitazione 11: esercizio  pdfpdf (it, 65 KB, 05/06/13)
Esercitazione 11: lucidi  pdfpdf (it, 208 KB, 05/06/13)
Esercitazione 11: photos.src  x-gzipx-gzip (it, 4 KB, 05/06/13)
Esercitazione 11: photos.webapp  x-gzipx-gzip (it, 77 KB, 05/06/13)
Esercitazione 1: esercizio  pdfpdf (it, 42 KB, 06/03/13)
Esercitazione 1: lucidi  pdfpdf (it, 637 KB, 05/03/13)
Esercitazione 2: esercizio  pdfpdf (it, 123 KB, 20/03/13)
Esercitazione 2: lucidi  pdfpdf (it, 982 KB, 19/03/13)
Esercitazione 3: esercizio  pdfpdf (it, 81 KB, 26/03/13)
Esercitazione 3: lucidi  pdfpdf (it, 610 KB, 26/03/13)
Esercitazione 4: esercizio  pdfpdf (it, 69 KB, 27/03/13)
Esercitazione 4: lucidi  pdfpdf (it, 397 KB, 27/03/13)
Esercitazione 5: esercizio  pdfpdf (it, 52 KB, 03/04/13)
Esercitazione 5: lucidi  pdfpdf (it, 2032 KB, 03/04/13)
Esercitazione 6: esercizio  pdfpdf (it, 40 KB, 10/04/13)
Esercitazione 6: lucidi  pdfpdf (it, 1074 KB, 10/04/13)
Esercitazione 6: tomcat  zipzip (it, 41 KB, 10/04/13)
Esercitazione 7: esercizio  pdfpdf (it, 44 KB, 17/04/13)
Esercitazione 7: lucidi  pdfpdf (it, 657 KB, 17/04/13)
Esercitazione 7: servlet con bean  zipzip (it, 4 KB, 17/04/13)
Esercitazione 7: servlet senza bean  zipzip (it, 2 KB, 17/04/13)
Esercitazione 8: corsi.jsp  zipzip (it, 0 KB, 08/05/13)
Esercitazione 8: esempiJSP  zipzip (it, 1 KB, 08/05/13)
Esercitazione 8: esercizio  pdfpdf (it, 85 KB, 08/05/13)
Esercitazione 8: lucidi  pdfpdf (it, 766 KB, 08/05/13)
Esercitazione 9: daFare.jsp  octet-streamoctet-stream (it, 0 KB, 22/05/13)
Esercitazione 9: esercizio  pdfpdf (it, 88 KB, 22/05/13)
Esercitazione 9: lucidi  pdfpdf (it, 520 KB, 21/05/13)
Esercitazione 9: main.java  octet-streamoctet-stream (it, 3 KB, 22/05/13)