The Databases course is an introduction to the management of large amounts of information using relational databases. Students will acquire the theoretical foundations underlying the relational model and the skills necessary to use pre-existing databases using the standard SQL language. Students will also be able to design database schemas starting from high-level functional specifications, translate them into working implementations, and perform formal correctness checks.
Course Prerequisites
Basic notions from mathematics and computer science: sets, operations on sets, basic first order logic, logic operators, quantifiers, notion of algorithm and basic programming skills.
Teaching Methods
Lectures (hours/year in class): 32 Exercises (hours/year in class): 10 Lab (hours/year in class): 18 Lectures are conducted with the aid of presentations, which are closely related to the content of the reference text; some sections are covered on the blackboard. The laboratory work consists of solving exercises on various topics, of increasing complexity, leading to the solution of exam questions. Laboratory work will focus on the SQL language and the use of relational DBMSs.
Assessment Methods
Assessment consists of a written and oral exam. The written exam is administered on a computer and includes questions on all course topics as well as exercises in writing SQL queries using the tools used in the lab. The oral exam is available after passing the written exam in the same exam session and includes theoretical questions and exercises on the blackboard.
Texts
P. Atzeni, S. Ceri, P. Fraternali, S. Paraboschi, R. Torlone. Basi di dati: Modelli e linguaggi di interrogazione. Mc Graw Hill, 6th edition.
Contents
Part I. The Relational Model. Information systems, information, data. Databases and database management systems (DBMS). Algebraic foundations of the relational model. Domains and relations. Keys and superkeys. Integrity constraints. Relational algebra. Set operators. Projection, selection, and junction. Construction of expressions that translate queries formulated in natural language. Part II. The SQL language. The SQL standard. Definition of table schemas and their population. Comparison with relational algebra. Elementary queries, joins, aggregations, and nested queries. Lab exercises with a relational DBMS. Part III. Database design. From requirements to logical schema: conceptual and logical design. Conceptual design using the Entity, Relationship, and Attribute (ERA) model. Restructuring the ERA schema. Translation into the logical model according to the relational schema. The concept of functional dependency as a tool for verifying the structure of relationships. Boyce-Codd Normal Form and Third Normal Form. Part IV. Database Technologies. Physical organization in databases. Indexes and tree structures. Query management and optimization. Transactions and their management. Control of concurrent transactions.
