2020/2021

2020/2021

DM270

ING-INF/05 (DATA PROCESSING SYSTEMS)

DEPARTMENT OF MATHEMATICS "FELICE CASORATI"

MATHEMATICS

PERCORSO COMUNE

1°

1st semester (01/10/2020 - 20/01/2021)

6

54 lesson hours

Italian

WRITTEN AND ORAL TEST

FERRETTI MARCO (titolare) - 6 ECTS

Algorithm design basic skills. Notions of high level language programming, basic understanding of computer systems architecture.

The course introduces the current technology of DBMS for the management of huge data volumes of structured information. The student will learn how to use SQL for programming applications that access a data base, and will also learn the design process that maps a high level, informal data specification into a data base schema. The design guidelines will obey the ER methodology. The logical model adopted throughout the course is the relational model. The normalization theory will also be introduced, mainly as a verification tool for functional dependencies. The activation in a cloud environment of an instance of a DBMS in SaaS mode adds to the competences gained through the course

Part I. Introduction to DBMS
Architecture of a DBMS. The layered architecture of data representaion. The notion of metada and schema. Data models: structures, operations, constraints. Language classes: DDL and DML. Transactions and ACID properties.
Part II. The relational model
The relation model: theoretical foundations. Domains and relations. The notion of superkey and primary key. Model constraint and referential integrity. Relational algebra. Set operators. Selection, projection, join. Translating a natural expression into an algebraic formula.

Part III. Data base design
From informal specifications to a logical schema: conceptual and logical design. The ER model: structures and constraints. From ER to a logical schema: data re-organization on the basis of volume information and transaction access plan. Translation of ER into relational schema. Functional dependency: definition and properties. Normal forms. The Boyce codd case and a simplified normalization procedure.

Part IV. SQL
SQL as a standard language for DBMS. Relationship to algebra. Complete syntax of query block SELECT FROM WHERE. Set operators. Simple queries, nested and correlated sub-queries. Grouping. SQL as a DDL. CATALOG. Hosted SQL: conventions SQLCA, cursor. ODBC anD JDBC. The client server model, the web connectivity.Hands-on in lab for SQL query coding and simple java programs.

Lectures (hours/year in lecture theatre): 32
Practical class (hours/year in lecture theatre):22
Practicals / Workshops (hours/year in lecture theatre): 0
Lecture are delivered mainly through slide presentations, with contents perfectly matched to
the main reference class book. The blackboard is also used for detailed examples from past exams tests.
Practical activities cover examples of increasing complexity for each new subject.
SQL is first introduced and then showed in actual DBMS sessions; students are also required
to install on a PC of their own the DBMS and data base environment
used for final test. A cloud environment is also made available for learning how to set-up
a DBMS in SaaS mode. Credentials for cloud access will be distributed at the begin of the
course and must be activated within two months; they will expire in 12 months.

The course includes hands-on lab using a MySQL server and associated Workbench as a SQL programming environment. The daba base used during the hands-on can be downloaded through the course web-site. Students are advised to install on a personal computing facility the same environment used in the lab (instructions available on the course web site). SQL learning material is available both in the course main textbook and on line.

P. Atzeni, S. Ceri, S. Paraboschi, R. Torlone. Basi di dati: Modelli e linguaggi di interrogazione. Mc Graw Hill, 5th edition. The book covers all subject discussed during the course, including SQL.

MySQL. Tutorial availabkle on MySQL official site: http://dev.mysql.com/doc/index.html.

A written test covering all subjects covered during lectures, a test in a lab for SQL with two/three queries, and an optional oral examination. Access to the SQL test requires showing successful activation, in a cloud environment, of MySQL in SaaS mode. Written and SQL tests are compulsory and a pass grade is required in both for successful completion of the course; each weights 27 and 3 out of 30. They can be taken on different exam sessions. For high grades (a grand total beyond 27/30) an oral examination is required; it is otherwise optional.

A written test covering all subjects covered during lectures, a test in a lab for SQL with two/three queries, and an optional oral examination. Access to the SQL test requires showing successful activation, in a cloud environment, of MySQL in SaaS mode. Written and SQL tests are compulsory and a pass grade is required in both for successful completion of the course; each weights 27 and 3 out of 30. They can be taken on different exam sessions. For high grades (a grand total beyond 27/30) an oral examination is required; it is otherwise optional.