2. Professors
  3. GAETA Matteo
  4. Teaching

Databases

Matteo GAETA Databases

0612700007
DIPARTIMENTO DI INGEGNERIA DELL'INFORMAZIONE ED ELETTRICA E MATEMATICA APPLICATA
EQF6
COMPUTER ENGINEERING
2021/2022



OBBLIGATORIO
YEAR OF COURSE 3
YEAR OF DIDACTIC SYSTEM 2017
SPRING SEMESTER
CFUHOURSACTIVITY
540LESSONS
216EXERCISES
216LAB


MATTEO GAETA T Curriculum
GIUSEPPE D'ANIELLO Curriculum
Objectives
THE COURSE PRESENTS THE MAIN ARCHITECTURES AND DATABASE MODELS, THE METHODOLOGIES FOR THE DESIGN OF DATABASES, THE ARCHITECTURE AND THE CHARACTERISTICS OF TRANSACTIONAL SYSTEMS, AND THE QUERY LANGUAGES FOR THE IMPLEMENTATION AND QUERYING OF RELATIONAL DATABASES AND FOR THE MANAGEMENT OF TRANSACTIONS IN A DBMS.
KNOWLEDGE AND UNDERSTANDING: THE RELATIONAL MODEL. CONCEPTUAL, LOGICA, AND PHYSICAL DESIGN OF A DATABASE. SQL LANGUAGE FOR THE DATA DEFINITION. SQL LANGUAGE FOR DATA MANIPULATION AND DATA EXTRACTION. NORMALIZATION. ACTIVE DATABASES. JAVA DATABASE CONNECTIVITY (JDBC) LIBRARY. ACID TRANSACTIONS.
APPLYING KNOWLEDGE AND UNDERSTANDING: DESIGN, IMPLEMENTATION, AND USE OF A RELATIONAL DATABASE FOR A GIVEN SCENARIO, USING THE DEVELOPMENT AND MANAGEMENT TOOL FOR THE POSTGRES DBMS.
Prerequisites
FOR THE SUCCESSFUL ACHIEVEMENT OF THE OBJECTIVES, A BASIC KNOWLEDGE OF OPERATING SYSTEMS AND OBJECT-ORIENTED PROGRAMMING IS RECOMMENDED. THERE IS A STRICT PREREQUISITE THAT THE STUDENT HAS ALREADY PASSED THE COURSE ALGORITMI E STRUTTURE DATI (ALGORITHMS AND DATA STRUCTURES).
Contents
THE COURSE IS DIVIDED INTO 2 MODULES: 1) MODULE DATABASES; 2) MODULE DATABASE PROJECT
1) MODULE DATABASES (LECTURE-PRACTICE-LABORATORY 44;3;1)
INTRODUCTION TO INFORMATION SYSTEMS AND DBMS (LEC 3; PRA 0; LAB0): INFORMATION SYSTEMS AND COMPUTER SYSTEMS; ARCHITECTURES; CONCEPT OF TRANSACTION; DBMS AND RDBMS ARCHITECTURE.
RELATIONAL MODEL (LEC 4; PRA 0; LAB 0).
INTRODUCTION TO THE RELATIONAL ALGEBRA (LEC 3; PRA 0; LAB 0).
POSTGRESQL (LEC 0; PRA 0; LAB 1): INSTALLATION AND DOCUMENTATION.
THE FUNDAMENTAL CONCEPTS OF SQL DDL (LEC 5; PRA 1; LAB 0): DECLARATIVE NATURE OF SQL; DDL - PRELIMINARY CONCEPTS; DOMAINS, SCHEMAS, AND TABLES DEFINITION; DEFINITION OF INTRA- AND INTER- RELATION CONSTRAINTS; REACTION POLICIES; ALTER AND DROP OF SCHEMAS; EXERCISES IN POSTGRESQL.
BASIC CONCEPTS OF SQL DML (LEC 5; PRA 1; LAB 0): DML - PRELIMINARY CONCEPTS; INTRODUCTION TO QUERIES; INSERT, DELETE, UPDATE OF DATA; SIMPLE QUERIES; INNER AND OUTER JOINS; ORDER BY, AGGREGATION OPERATORS; GROUP BY; EXERCISES IN POSTGRESQL.
METHODOLOGIES AND MODELS FOR THE DESIGN (LEC 4; PRA 0; LAB 0): E-R MODEL, ELEMENTS AND DOCUMENTATION; INTRODUCTION TO THE DATA MODELING IN UML AND CASE TOOLS.
LOGIC DESIGN (LEC 4; PRA 0; LAB 0): PHASES OF LOGIC DESIGN; PERFORMANCE ANALYSIS ON E-R SCHEMAS; E-R SCHEMAS RESTRUCTURATION; TRANSLATION TO THE RELATIONAL MODEL.
PHYSICAL DESIGN: CONSIDERATIONS ON THE PHYSICAL ORGANIZATION AND INDEXES IN RDBMS (LEC 1; PRA 0; LAB 0): INTRODUCTION ON THE PHYSICAL ORGANIZATION.
NORMALIZATION (LEC 4; PRA 0; LAB 0): REDUNDANCIES, ANOMALIES AND FUNCTIONAL DEPENDENCIES; THE THREE NORMAL FORMS AND BCNF; DATABASE DESIGN AND NORMALIZATION; EXAMPLES ON THE EVALUATION OF THE SCHEMA QUALITY AND NORMALIZATION.
COMPLEX CHARACTERISTICS OF SQL LANGUAGE (LEC 4; PRA 0; LAB 0): SET OPERATIONS AND NESTED QUERIES (SIMPLE AND COMPLEX INTERPRETATIONS); NESTED QUERIES IN SELECT AND FROM.
ACCESS CONTROL WITH SQL (LEC 1; PRA 0; LAB 0) RESOURCES AND PRIVILEGES; GRANT AND REVOKE PRIVILEGES; ROLES.
ADVANCE CHARACTERISTICS OF SQL LANGUAGE (LEC 3; PRA 1; LAB 0): GENERIC INTEGRITY CONSTRAINTS AND ASSERTIONS; VIEWS; STORED PROCEDURES AND TRIGGER; TRANSACTIONS (COMMIT AND ROLLBACK) AND ACID PROPERTIES.
APPLICATION DEVELOPMENT FOR DATABASES (LEC 3; PRA 0; LAB 0): INTRODUCTION TO SQL EMBEDDED; ODBC AND JDBC; A GENERIC DESIGN PATTERN AND DEVELOPMENT OF A JAVA APPLICATION USING JDBC.

2) MODULE DATABASE PROJECT (LECTURE-PRACTICE-LABORATORY 4; 11; 9)
THE SOFTWARE LIFECYCLE - PROJECT WORK ORIENTATION (LEC 2; PRA 1; LAB 0): DATA ANALYSIS AND REQUIREMENTS SPECIFICATION (EXAMPLES).
CONCEPTUAL DESIGN - PROJECT WORK ORIENTATION (LEC 2; PRA 4; LAB 0): REQUIREMENT ANALYSIS AND ELICITATION; CONCEPTUAL REPRESENTATION: CRITERIA AND DESIGN PATTERN; DESIGN STRATEGIES; QUALITY OF A CONCEPTUAL SCHEMA; A GENERAL METHODOLOGY; CASE STUDY: REQUIREMENT ANALYSIS AND E-R DIAGRAM.
LOGIC DESIGN - PROJECT WORK ORIENTATION (LEC 0; PRA 5; LAB 0): PHASES OF LOGIC DESIGN; TRANSLATION TO THE RELATIONAL MODEL; EXERCISES AND CASE STUDY - PERFORMANCE ANALYSIS AND E-R SCHEMAS RESTRUCTURATION.
NORMALIZATION - PROJECT WORK ORIENTATION (LEC 0; PRA 1; LAB 0).
COMPLEX CHARACTERISTICS OF SQL LANGUAGE - PROJECT WORK ORIENTATION (LEC 0; PRA 0; LAB 3): EXERCISES - SET OPERATIONS AND NESTED QUERIES.
ADVANCED CHARACTERISTICS OF SQL LANGUAGE - PROJECT WORK ORIENTATION (LEC 0; PRA 0; LAB 3): EXERCISES ON VIEW, TRIGGER, TRANSACTIONS.
DEVELOPMENT OF APPLICATIONS FOR DATABASES - PROJECT WORK ORIENTATION (LEC 2; PRA 1; LAB 0): EXERCISES: A GENERIC DESIGN PATTERN AND DEVELOPMENT OF A JAVA APPLICATION USING JDBC; A CASE STUDY USING JDBC - IMPLEMENTATION OF A JAVA APPLICATION
Teaching Methods
THE COURSE CONSISTS OF LESSONS AND EXERCISES IN CLASS. MOREOVER, TEAMWORK ACTIVITIES WILL BE DONE DURING THE COURSE. DURING THE EXERCISES, THE STUDENTS ARE ASKED TO DESIGN RELATIONAL DATABASES FOR GIVEN SCENARIOS, TO SOLVE QUERIES, TO NORMALIZE RELATIONS, AND TO IMPLEMENT SIMPLE JAVA PROGRAMS THAT INTERACT WITH AN RDBMS. IN ORDER TO PARTICIPATE IN THE FINAL ASSESSMENT AND TO GAIN THE CREDITS (CFU) CORRESPONDING TO THE COURSE, THE STUDENT MUST HAVE ATTENDED AT LEAST 70% OF THE HOURS OF ASSISTED TEACHING ACTIVITIES.
Verification of learning
THE EXAM CONSISTS OF A WRITTEN TEST AND AN ORAL INTERVIEW. IN THE WRITTEN TEST, THE STUDENT, STARTING FROM A SYNTHETIC SPECIFICATION OF A PROBLEM, HAS TO PERFORM ALL THE DESIGN STEPS LEADING TO THE DEFINITION OF THE DATABASE, AND TO IMPLEMENT A SUBSET OF THE QUERIES AND OF THE APPLICATION FUNCTIONALITIES. IN THE ORAL INTERVIEW, THE STUDENT DISCUSSES THE RESULT OF THE WRITTEN TEST AND ALL THE OTHER TOPICS COVERED IN THE COURSE. THE FINAL EVALUATION CONSIDERS BOTH THE WRITTEN AND ORAL EXAMINATIONS. THE ORAL EXAMS COULD ALSO INCLUDE THE EXECUTION OF SOME EXERCISES IN POSTGRESQL.
DURING THE COURSE, THE STUDENTS CAN DEVELOP, ALSO IN TEAMS, A PROJECT WORK CONSISTING OF 1) REQUIREMENT ANALYSIS AND SPECIFICATION; 2) CONCEPTUAL DESIGN; 3) LOGIC DESIGN; 4) DEFINITION AND MANAGEMENT OF DATABASE USING THE SQL LANGUAGE IN POSTGRESQL; 5) REALIZATION OF FEATURES OF AN APPLICATION IN JAVA (OPTIONAL). EACH SINGLE STUDENT IN THE CONTEXT OF THE GROUP WILL BE IN CHARGE OF ONE OF THE SECTIONS OF THE PROJECT WORK, IN THE CONTEXT OF THE OVERALL ACTIVITIES OF THE GROUP. THE DISCUSSION ON THE PROJECT WORK AND IN PARTICULAR THE SECTION ASSIGNED TO THE SINGLE STUDENT WILL BE ONE OF THE EVALUATION ITEMS OF THE STUDENT AND IT SUBSTITUTES THE WRITTEN TEST. THE OVERALL EVALUATION OF THE SINGLE STUDENT CONSIDERS THE EVALUATION OF THE SECTION ASSIGNED TO THE STUDENT AND THE ORAL EXAM, AS WELL AS (NOT PREVALENTLY) THE OVERALL PROJECT WORK. THE PROJECT WORK CAN BE USED FOR THE EXAMS OF THE ENTIRE ACADEMIC YEAR.
Texts
TEXTBOOKS: P. ATZENI, S. CERI, P. FRATERNALI, S. PARABOSCHI, R. TORLONE; BASI DI DATI - QUINTA EDIZIONE; MCGRAW-HILL ITALIA ISBN: 9788838668005.
CONSULTATION TEXTBOOK: A. CHIANESE, V. MOSCATO, A. PICARIELLO, L. SANSONE, BASI DI DATI PER LA GESTIONE DELL'INFORMAZIONE - SECONDA EDIZIONE; MCGRAW-HILL ITALIA, ISBN: 9788838672217
SUPPLEMENTARY TEACHING MATERIAL WILL BE AVAILABLE ON THE UNIVERSITY E-LEARNING PLATFORM ACCESSIBLE TO THE STUDENTS USING THEIR OWN UNIVERSITY CREDENTIALS.
More Information
THE COURSE IS HELD IN ITALIAN.
  BETA VERSION Data source ESSE3 [Ultima Sincronizzazione: 2022-11-21]