Databases

A.Y. 2018/2019
12
Max ECTS
120
Overall hours
SSD
INF/01
Language
Italian
Learning objectives
Il corso si propone di fornire i concetti fondamentali relativamente alle basi di dati e ai sistemi per la loro gestione, con particolare riguardo ai sistemi di basi di dati relazionali. Il corso prevede una parte di teoria dedicata a modelli, linguaggi, metodologie di progettazione e agli aspetti di sicurezza e transazioni, e una parte di laboratorio dedicata all'acquisizione e uso di strumenti di gestione e progettazione di basi di dati relazionali e alle principali tecnologie di basi di dati e Web.
Expected learning outcomes
Undefined
Course syllabus and organization

Milan

Responsible
Lesson period
First semester
ATTENDING STUDENTS
Course syllabus
Database module
Introduction to databases. Information systems, information, data. Database and Database Management System (DBMS). Data models. Database schema and instance. Abstraction levels in DBMSs. Data independence. Database languages and users.
Relational data model. Relations and tables. Relational schema and instance. Properties of relations. Null values. Definitions and properties of keys. Integrity constraints. Relational algebra: definitions, operations. Equivalence of relational algebra expressions and transformation rules.
SQL. SQL Data Definition Language. Definition of schemas, tables, domains. Specifying constraint in SQL. Schema change statements in SQL. SQL Query Language: basic SQL queries, complex SQL queries. SQL Data Manipulation Language: insert, delete, and update statements. Specifying constraints as assertions. Triggers in SQL: specification and use. Views in SQL: specification and use. View update problems.
Entity-Relationship model. Basic elements: entity, relationship, attribute. Generalization hierarchies. Identifiers. Integrity constraints. ER schema documentation.
Database design. Database design methodology. Requirements collection and analysis. Conceptual database design strategies: top-down, bottom-up, inside-out, mixed. Quality of ER schemas. Logical design. Schema restructuring. Relational database logical design by ER-to-relational mapping. Physical design. Introduction to indexes and general guidelines for relational database physical design.
Normalization. Definition of functional dependency. Relation Normal Forms: 1NF, 2NF, 3NF, Boyce-Codd NF. Relation decomposition. Dependency preservation property and lossless join property of decompositions.
Database security. Introduction to data security. Authentication, access control, auditing. Security policies. Discretionary and mandatory access control policies. The Trojan horse problem. The System R authorization model. Gran and revoke of authorizations. SQL GRANT and REVOKE statements. Cascade revoke.
Transactions. Introduction to transactions in DBMS. ACID transaction properties.

Laboratory module
DBMS. DBMS classification and client/server architecture of DBMS. Comparison of the main existing DBMS. PostgreSQL: installation and configuration, architecture, main commands, database and user creation, table creation, primary key and foreign key constraints, referential integrity constraints. CASE tools for database design support. Main features and commands of the PowerDesigner CASE tool. Reverse database engineering.
Embedded SQL and PL/SQL. Application development in a DBMS environment with the SQL language. Introduction to embedded SQL. The notion of cursor. Introduction to the Oracle PL/SQL. Loops and flow control: if, loop, while, for statements. Use of cursors in PL/SQL. Introduction to PLpgSQL. Procedures, functions, and triggers in PLpgSQL.
Web and databases. Introduction to the Web architecture. The HTTP protocol. Stateless property of the Web. Web Server: general functionalities; configuration of the Apache Web server. Web architecture with server-side script. PHP: installation and general features. Parameter exchange between pages. GET and POST methods in HTTP. Control structures, data types, superglobal variables. Interaction with DBMS. Persistency in client/server communications: cookie and sessions.
NON-ATTENDING STUDENTS
Course syllabus
Database module
Introduction to databases. Information systems, information, data. Database and Database Management System (DBMS). Data models. Database schema and instance. Abstraction levels in DBMSs. Data independence. Database languages and users.
Relational data model. Relations and tables. Relational schema and instance. Properties of relations. Null values. Definitions and properties of keys. Integrity constraints. Relational algebra: definitions, operations. Equivalence of relational algebra expressions and transformation rules.
SQL. SQL Data Definition Language. Definition of schemas, tables, domains. Specifying constraint in SQL. Schema change statements in SQL. SQL Query Language: basic SQL queries, complex SQL queries. SQL Data Manipulation Language: insert, delete, and update statements. Specifying constraints as assertions. Triggers in SQL: specification and use. Views in SQL: specification and use. View update problems.
Entity-Relationship model. Basic elements: entity, relationship, attribute. Generalization hierarchies. Identifiers. Integrity constraints. ER schema documentation.
Database design. Database design methodology. Requirements collection and analysis. Conceptual database design strategies: top-down, bottom-up, inside-out, mixed. Quality of ER schemas. Logical design. Schema restructuring. Relational database logical design by ER-to-relational mapping. Physical design. Introduction to indexes and general guidelines for relational database physical design.
Normalization. Definition of functional dependency. Relation Normal Forms: 1NF, 2NF, 3NF, Boyce-Codd NF. Relation decomposition. Dependency preservation property and lossless join property of decompositions.
Database security. Introduction to data security. Authentication, access control, auditing. Security policies. Discretionary and mandatory access control policies. The Trojan horse problem. The System R authorization model. Gran and revoke of authorizations. SQL GRANT and REVOKE statements. Cascade revoke.
Transactions. Introduction to transactions in DBMS. ACID transaction properties.

Laboratory module
DBMS. DBMS classification and client/server architecture of DBMS. Comparison of the main existing DBMS. PostgreSQL: installation and configuration, architecture, main commands, database and user creation, table creation, primary key and foreign key constraints, referential integrity constraints. CASE tools for database design support. Main features and commands of the PowerDesigner CASE tool. Reverse database engineering.
Embedded SQL and PL/SQL. Application development in a DBMS environment with the SQL language. Introduction to embedded SQL. The notion of cursor. Introduction to the Oracle PL/SQL. Loops and flow control: if, loop, while, for statements. Use of cursors in PL/SQL. Introduction to PLpgSQL. Procedures, functions, and triggers in PLpgSQL.
Web and databases. Introduction to the Web architecture. The HTTP protocol. Stateless property of the Web. Web Server: general functionalities; configuration of the Apache Web server. Web architecture with server-side script. PHP: installation and general features. Parameter exchange between pages. GET and POST methods in HTTP. Control structures, data types, superglobal variables. Interaction with DBMS. Persistency in client/server communications: cookie and sessions.
INF/01 - INFORMATICS - University credits: 12
Laboratories: 48 hours
Lessons: 72 hours
Shifts:
Professor: Ferrara Alfio
Turno A
Professor: Montanelli Stefano
Turno B
Professor: Frasca Marco
Professor(s)
Reception:
Friday, 15.00- 16.00
Department of Computer Science, via Celoria 18 Milano, Room 7012 (7 floor)
Reception:
Thursday from 11AM to 12PM (check news at the specified webpage)
Room 7015, Dipartimento di Informatica "Giovanni degli Antoni", Via Celoria 18 - 20133 Milano