Course name:
Basi di dati e Laboratorio di basi di dati (Edizione 1)
Code: F49012
Credits: 12
Category:
Fondamentale
Year of course: 3
Study course: C.so di Laurea in Informatica, Informatica
per Telecomunicazioni e C.so di Diploma in Informatica
vecchio ordinamento
Teacher: prof.ssa Silvana Castano
# lesson: 76
# excercise: 20
Semester: 1
Exam procedure: written test (part A with questions on theory and part B with exercises)
+ oral test (when required) + laboratory test concerning the development of a web-based
database application.
Goal of the course
Objectives of the course are to provide fundamentals of databases and database management systems,
with focus on relational database systems. The course is organized into a database module, devoted to
theory aspects, and a laboratory module devoted to practical issues, The database module is devoted to
models, languages, design methodologies, security and basic transaction concepts. The lab module is
devoted to acquisition and use of tools for relational database design, implementation, and management
and of technologies for Web database programming.
Programme of the course
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. Client-side programming,
server-side programming, CGI technology. Web architecture with server-side script. PHP: installation and
general features. Parameter exchange between pages. GET and POST methods in HTTP. Control stuctures,
data types, superglobal variables. Interaction with DBMS, the ODBC driver, the JDBC driver, the
PostgreSQL library, the PEAR libraries. Persistency in client/server communications: cookie and sessions.
Interaction with the file system: object upload/download. Email transmission in PHP. XML language and use
of XML in PHP.
Reference material
R. Elmasri, S.B. Navathe, Sistemi di basi di dati – Fondamenti (5 ed.), edizione italiana a cura di S.
Castano, Pearson-Addison Wesley, 2007
Chap. 1 (complete); Chap. 2 (complete); Chap. 3 (complete except for 3.8); Chap. 4 (4.1, 4.2(all the
subparagraphs), 4.3 (all the subparagraphs except for 4.3.2), 4.5, 4.7); Chap.5 (complete); Chap. 6
(complete until 6.3.4 included, 6.4.4, 6.5); Chap. 7 (complete); Chap. 8 (complete); Chap. 9
(complete); Chap. 10 (10.1(all the subparagraphs), 10.2 (10.2.1), 10.3 (all the subparagraphs), 10.5);
Chap. 12 (12.1 (all the subparagraphs), 12.2 (all the subparagraphs), 12.5); Chap. 14 (additional
read to the material provided by the teacher); Chap. 15 (complete); Chap. 16 (complete).
S.Castano. M. Fugini, G. Martella, P. Samarati, Database security, Addison Wesley, 1995.
Chap. 1 (complete); Chap. 4 (4.2.2). On this topic, specific material of the teacher is available in the
photocopy shop.
PostgreSQL. http://www.postgresql.org/
PHP. www.php.net
Additional teaching material is available on the course web site.
Suggested material (for the laboratory module)
K. Douglas, S. Douglas, PostgreSQL (2 ed.), Sams, 2005
D. Sklar, PHP 5 - Elementi di programmazione, McGraw-Hill, 2005.
A. Gutmans, S. Bakken, D. Rethans, PHP 5 Guida Completa, Apogeo, 2005.
Prerequisites
Sistemi operativi, Linguaggi di programmazione
Course web-site: http://islab.dico.unimi.it/bdlab1/
