Databases and WEB - Teoria: basi di dati (2008/2009)

Learning outcomes

The module “Databases Theory” ("Teoria: basi di dati" in italian) of the course “Databases and WEB” has the aim to provide the student with the necessary concepts and methods for the design of a database and its applications. In particular, this module 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.

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

The final examination of the course "Database and Web" is organized in three tests. The first covers the module "Teoria: basi di dati" and consists of some exercises on the conceptual and logical design of a database, SQL and relational algebra queries and questions about the theory. The second test covers the module "Laboratorio" and consists of some exercises on the design of a web application based on the MVC-2 approach using the JSP, Java data beans and Servlet technologies. The third one covers the module "Teoria: siti web centrati sui dati" and consists of some exercises on the logical design of a data-intensive web application .
The final grade is obtained by the weighted average of the grades collected on the three modules of the course: the module "Teoria: basi di dati" (weight 3/5), the module "Teoria: siti web centrati sui dati" (weight 1/5) and the module "Laboratorio" (weight 1/5).