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
2022/2023



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


ANTONIO D'ACIERNO T Curriculum
MATTEO GAETA 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, LOGIC, 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 FOR A TOTAL OF HOURS LECTURE – PRACTICE - LABORATORY 48; 14; 10.
1) MODULE DATABASES (LECTURE-PRACTICE-LABORATORY HOURS 44;3;1)
Didactic Unit 1: INFORMATION SYSTEMS, DBMS AND RELATIONAL MODEL.
(LECTURE/PRACTICE/LABORATORY HOURS 7/0/1).
- 1 (3 HOURS Lecture): INFORMATION SYSTEMS AND COMPUTER SYSTEMS; ARCHITECTURES; CONCEPT OF TRANSACTION;
- 2 (1 HOUR LECTURE AND 1 HOUR LABORATORY): DBMS AND RDBMS ARCHITECTURE AND INSTALLATION OF A RDBMS (POSTGRES).
- 3) (3 HOURS LECTURE): RELATIONAL MODEL.
KNOWLEDGE AND UNDERSTANDING: UNDERSTANDING THE CONCEPT OF INFORMATION SYSTEM, THE IMPORTANCE OF DBMS AND RDBMS; KNOWLEDGE OF THE RELATIONAL MODEL.
APPLYING KNOWLEDGE AND UNDERSTANDING: KNOW HOW TO REPRESENT THE ARCHIVES OF INTEREST FOR AN APPLICATION USING THE RELATIONAL MODEL.
DIDACTIC UNIT 2: THE FUNDAMENTAL CONCEPTS OF SQL
(LECTURE/PRACTICE/LABORATORY HOURS 13/1/0).
- 4 (3 HOURS LECTURE): INTRODUCTION TO THE RELATIONAL ALGEBRA.
- 5 (3 HOURS LECTURE): DECLARATIVE NATURE OF SQL; DDL - PRELIMINARY CONCEPTS; DOMAINS, SCHEMAS, AND TABLES DEFINITION;
- 6 (2 HOURS LECTURE): DEFINITION OF INTRA- AND INTER- RELATION CONSTRAINTS; REACTION POLICIES; ALTER AND DROP OF SCHEMAS; EXAMPLES IN POSTGRESQL.
- 7 (3 HOURS LECTURE): PRELIMINARY CONCEPTS DML; INTRODUCTION TO QUERIES; INSERT, DELETE, UPDATE OF DATA; SIMPLE QUERIES; INNER AND OUTER JOINS.
- 8 (2 HOURS LECTURE AND 1 HOUR PRACTICE): ORDER BY, AGGREGATION OPERATORS; GROUP BY; EXERCISES IN POSTGRESQL.
KNOWLEDGE AND UNDERSTANDING: KNOWLEDGE OF THE BASIC CONCEPTS, THE MAIN CONSTRUCTS AND COMMANDS OF SQL.
APPLYING KNOWLEDGE AND UNDERSTANDING: KNOW HOW TO CREATE, MODIFY, AND DELETE DOMAINS, TABLES, ETC. AND KNOW HOW TO CREATE A SIMPLE QUERY AND USE THE JOIN.
DIDACTIC UNIT 3: METHODOLOGIES AND MODELS FOR THE DESIGN.
(LECTURE/PRACTICE/LABORATORY HOURS 13/0/0).
- 9 (3 HOURS LECTURE): E-R MODEL, ELEMENTS OF THE RELATIONAL MODEL.
- 10 (2 HOURS LECTURE): THE DOCUMENTATION IN THE E-R MODEL; INTRODUCTION TO THE DATA MODELING IN UML AND CASE TOOLS; PHASES OF LOGIC DESIGN; PERFORMANCE ANALYSIS ON E-R SCHEMAS.
- 11 (3 HOURS LECTURE): LOGIC DESIGN; E-R SCHEMAS RESTRUCTURATION.
- 12 (2 HOURS LECTURE): TRANSLATION TO THE RELATIONAL MODEL; INTRODUCTION ON THE PHYSICAL ORGANIZATION.
- 13 (3 HOURS LECTURE): NORMALIZATION; 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.
KNOWLEDGE AND UNDERSTANDING: UNDERSTANDING THE E-R CONCEPTUAL MODEL; UNDERSTANDING THE PRINCIPLES OF LOGIC DESIGN; ACQUIRE THE CONCEPT OF LOGIC DESIGN PATTERN; UNDERSTANDING THE CONCEPT OF INDEX; UNDERSTANDING THE CONCEPTS AND PRINCIPLES OF NORMAL FORMS.
APPLYING KNOWLEDGE AND UNDERSTANDING: KNOW HOW TO CREATE AN E-R SCEHMA; KNOW HOW TO RESTRUCTURE A RELATIONAL SCHEME; KNOW HOW TO TRANSLATE A RESTORED RELATIONAL SCHEME BY APPLYING THE LOGIC DESIGN PATTERN; KNOW HOW TO NORMALIZE AT LEAST A RELATIONAL SCHEME TO THE THIRD NORMAL FORM.
DIDACTIC UNIT 4: COMPLEX AND EVOLVED SQL FEATURES.
(LECTURE/PRACTICE/LABORATORY HOURS 8/2/0).
- 14 (3 HOURS LECTURE): SET OPERATIONS AND NESTED QUERIES (SIMPLE AND COMPLEX INTERPRETATIONS); NESTED QUERIES IN SELECT AND FROM.
- 15 (1 HOUR LECTURE AND 1 HOUR PRACTICE): EXERCISES ON SET AND NESTED QUERY; ACCESS CONTROL WITH SQL; RESOURCES AND PRIVILEGES; GRANT AND REVOKE PRIVILEGES; ROLES.
- 16 (3 HOURS LECTURE): ADVANCE CHARACTERISTICS OF SQL LANGUAGE; GENERIC INTEGRITY CONSTRAINTS AND ASSERTIONS; VIEWS; STORED PROCEDURES AND TRIGGER;
- 17 (1 HOUR LECTURE AND 1 HOUR PRACTICE): EXERCISES ON TRIGGER; TRANSACTIONS (COMMIT AND ROLLBACK) AND ACID PROPERTIES.
KNOWLEDGE AND UNDERSTANDING: UNDERSTANDING HOW DEFINE SET AND NESTED QUERIES; UNDERSTANDING THE CONCEPTS OF VIEW, TRIGGER AND TRANSACTION.
APPLYING KNOWLEDGE AND UNDERSTANDING: KNOW HOW TO CREATE SET AND NIDIFIED QUERIES; KNOW HOW TO CREATE A VIEW; KNOW HOW TO IMPLEMENT A TRIGGER AND A TRANSACTION.
DIDACTIC UNIT 5: APPLICATION DEVELOPMENT FOR DATABASES
(LECTURE/PRACTICE/LABORATORY HOURS 3/0/0).
- 18 (3 HOURS LECTURE): INTRODUCTION TO SQL EMBEDDED; ODBC AND JDBC; A GENERIC DESIGN PATTERN AND DEVELOPMENT OF A JAVA APPLICATION USING JDBC.
KNOWLEDGE AND UNDERSTANDING: UNDERSTANDING THE CONCEPTS OF ODBC AND JDBC; UNDERSTANDING PATTERN APPLICATION DESIGN AND DEVELOPMENT WITH JDBC.
APPLYING KNOWLEDGE AND UNDERSTANDING: KNOW HOW TO IMPLEMENT A JAVA APPLICATION USING JDBC.
2) MODULE DATABASE PROJECT (LECTURE-PRACTICE-LABORATORY 4; 11; 9)
DIDACTIC UNIT 1: THE DESIGN OF A RELATIONAL DATABASE - ORIENTATION TO PROJECT WORK.
(HOURS LECTURE/PRACTICE/LABORATORY 4/11/0).
- 1 (2 HOURS LECTURE): THE SOFTWARE LIFECYCLE - PROJECT WORK ORIENTATION; DATA ANALYSIS AND REQUIREMENTS SPECIFICATION (EXAMPLES).
- 2 (2 HOURS LECTURE AND 1 HOUR PRACTICE) CONCEPTUAL DESIGN - PROJECT WORK ORIENTATION; REQUIREMENT ANALYSIS AND ELICITATION; CONCEPTUAL REPRESENTATION: CRITERIA AND DESIGN PATTERN; DESIGN STRATEGIES; QUALITY OF A CONCEPTUAL SCHEMA; A GENERAL METHODOLOGY; PROJECT PATTERN EXAMPLES AND EXERCISES.
- 3 (3 HOURS PRACTICE) CASE STUDY: REQUIREMENT ANALYSIS AND E-R DIAGRAM.
- 4 (2 HOURS PRACTICE): LOGIC DESIGN - PROJECT WORK ORIENTATION; PHASES OF LOGIC DESIGN.
- 5 (3 HOURS PRACTICE): PERFORMANCE ANALYSIS AND E-R SCHEMAS RESTRUCTURATION.
- 6 (2 HOURS PRACTICE): TRANSLATION TO THE RELATIONAL MODEL; PATTERN OF LOGIC DESIGN AND NORMALIZATION.
KNOWLEDGE AND UNDERSTANDING: UNDERSTANDING THE CONCEPTUAL DESIGN AND LOGIC PATTERN; UNDERSTAND THE CONCEPTS AND PRINCIPLES OF NORMAL FORMS FOR VERIFYING SCHEMES. APPLY THE KNOWLEDGE ACQUIRED TO THE PROJECT.
APPLYING KNOWLEDGE AND UNDERSTANDING: KNOW HOW TO APPLY THE CONCEPTUAL DESIGN PATTERN AND TO BE ABLE TO CREATE AN E-R SCEHMA; KNOW HOW TO RESTRUCTURE A RELATIONAL SCHEME; TO KNOW HOW TO TRANSLATE A RESTORED RELATIONAL SCHEME BY APPLYING THE LOGIC DESIGN PATTERN; KNOW HOW TO NORMALIZE AT LEAST A RELATIONAL SCHEME TO THE THIRD NORMAL FORM. APPLY THE KNOWLEDGE ACQUIRED TO THE PROJECT.
DIDACTIC UNIT 2: COMPLEX AND EVOLVED SQL FEATURES - PROJECT WORK ORIENTATION
(LECTURE/PRACTICE/LABORATORY HOURS 0/0/6).
- 7 (3 HOURS LABORATORY): EXERCISES - SET OPERATIONS AND NESTED QUERIES.
- 8 (3 HOURS LABORATORY): EXERCISES ON VIEW, TRIGGER, TRANSACTIONS.
KNOWLEDGE AND UNDERSTANDING: UNDERSTANDING HOW TO DEFINE SET AND NESTED FOR THE PROJECT; UNDERSTANDING HOW TO USE THE CONCEPTS OF VIEW, TRIGGER AND TRANSACTION TO PROJECT.
APPLYING KNOWLEDGE AND UNDERSTANDING: KNOW HOW TO CREATE SET QUERIES, NESTED FOR THE PROJECT; KNOW HOW TO CREATE A VIEW; KNOW HOW TO IMPLEMENT TRIGGERS AND TRANSACTIONS FOR THE PROJECT.
DIDACTIC UNIT 3: DEVELOPMENT OF APPLICATIONS FOR DATABASES - PROJECT WORK ORIENTATION.
(LECTURE/PRACTICE/LABORATORY HOURS 0/0/3).
- 9 (3 HOURS LABORATORY): A GENERIC DESIGN PATTERN AND DEVELOPMENT OF A JAVA APPLICATION USING JDBC; A CASE STUDY USING JDBC - IMPLEMENTATION OF A JAVA APPLICATION.
KNOWLEDGE AND UNDERSTANDING: UNDERSTANDING THE CONCEPTS OF ODBC AND JDBC; UNDERSTANDING PATTERN DESIGN FOR DEVELOPING A SIMPLE APPLICATION WITH JDBC.
APPLYING KNOWLEDGE AND UNDERSTANDING: KNOW HOW TO IMPLEMENT A SIMPLE JAVA APPLICATION USING JDBC FOR THE PROJECT.
TOTAL HOURS OF LECTURE - PRACTICE - LABORATORY 48; 14; 10
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 LEVEL OF ACHIEVEMENT OF THE TEACHING OBJECTIVES IS CERTIFIED BY PASSING AN EXAM WITH THE ASSESSMENT IN THIRTIETHS. THE EXAM CONSISTS OF A WRITTEN TEST AN AN ORAL INTERVIEW. IN THE WRITTEN TEST, THE STUDENTS, STARTING FROM A SYNTHETIC SPECIFICATION OF A PROBLEM, HAVE TO PERFORM ALL THE DESIGN STEPS LEADING TO THE DEFINITION OF THE DATABASE AND IMPLEMENT A SUBSET OF THE QUERIES AND OF THE APPLICATION FUNCTIONALITIES. IN THE ORAL INTERVIEW, THE STUDENT DISCUSSES THE RESULTS 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 AN 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.
THE PROJECT WILL BE ASSESSED IN THIRTIETHS ON THE BASIS OF CORRECTNESS, COMPLETENESS AND QUALITY OF THE PROPOSED SOLUTIONS.
THE FINAL VOTE IS OBTAINED TAKING INTO ACCOUNT THE RESULTS OF THE ORAL INTERVIEW AND THE VOTE AWARDED TO THE PROJECT WHICH HAS A WEIGHT OF 70%. HONORS MAY BE ATTRIBUTED TO STUDENTS WHO PROVE THAT THEY HAVE EXCELLENT MASTERY OF THE COURSE CONTENT TOGETHER WITH THE ABILITY TO APPLY THE KNOWLEDGE ACQUIRED FOR THE RESOLUTION OF PROBLEMS NOT ADDRESSED DURING THE PROJECT.
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: 2024-08-21]