Lecturer: Marco Ferretti  

Course name: Data Base
Course code: 502479
Degree course: Ingegneria Elettronica e Informatica
Disciplinary field of science: ING-INF/05
L'insegnamento è caratterizzante per: Ingegneria Elettronica e Informatica
University credits: ECTS 6
Course website: n.d.

Specific course objectives

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.

Course programme

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. The client server model, the web connectivity.

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. Hands-on in lab for SQL query coding and simple java programs.

Course entry requirements

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

Course structure and teaching

Lectures (hours/year in lecture theatre): 45
Practical class (hours/year in lecture theatre): 0
Practicals / Workshops (hours/year in lecture theatre): 0

Suggested reading materials

The course includes hands-on lab (room B2) using a MySQL server and associated Workbench as a SQL programming environment. The daba base used during the hands-on can be downloaded from the course web-site. Students are advised to install on a personal computing facilty the same environment used in the lab (instryuctions 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, 3za edizione. Testo completo su tutti gli argomenti del corso, anche per SQL..

MySQL. Tutorial disponibile sul sito di MySQL: http://dev.mysql.com/doc/index.html. Utile per chi installa sul proprio PC MySQL. Verificare a lezione la versione da installare..

Testing and exams

A written test covering all subject covered during lectures, but for SQL. An SQL test in the lab, consisting of two/three queries. Both tests are compulsory and a pass grade is required in both for successful completion of the course, but they can be taken on different exam sessions.