DATA BASE
Stampa
Enrollment year
2017/2018
Academic year
2018/2019
Regulations
DM270
Academic discipline
ING-INF/05 (DATA PROCESSING SYSTEMS)
Department
DEPARTMENT OF MATHEMATICS "FELICE CASORATI"
Course
MATHEMATICS
Curriculum
PERCORSO COMUNE
Year of study
Period
1st semester (01/10/2018 - 18/01/2019)
ECTS
6
Lesson hours
54 lesson hours
Language
Italian
Activity type
WRITTEN AND ORAL TEST
Teacher
FERRETTI MARCO (titolare) - 6 ECTS
Prerequisites
Algorithm design basic skills. Notions of high level language programming, basic understanding of computer systems architecture.
Learning outcomes
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 contents
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.
Teaching methods
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 checkboard 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 given
the possibility to install on a PC of their own the DBMS and data base environment
used for final test.
Reccomended or required readings
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 through the course web-site. Students are advised to install on a personal computing facilty 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, 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..
Assessment methods
A written test covering all subject covered during lectures, but for SQL, and an optional oral examination. The SQL test in the lab consists of two/three queries. WRitten and SQL 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. For high grades (beyond 27/30) an oral examination is required; it is otherwise optional.
Written and SQL tests can be taken in differente exam sessions.
Further information
A written test covering all subject covered during lectures, but for SQL, and an optional oral examination. The SQL test in the lab consists of two/three queries. WRitten and SQL 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. For high grades (beyond 27/30) an oral examination is required; it is otherwise optional.
Written and SQL tests can be taken in differente exam sessions.
Sustainable development goals - Agenda 2030