DATABASE M - Z
Academic Year 2023/2024 - Teacher: SALVATORE ALAIMOExpected Learning Outcomes
The course aims to give the knowledge and basic skills for database representation, organization and querying. The course mainly refers to the relational data model widely adopted in commercial and open-source systems. The course thoroughly introduces relational algebra, the SQL query language, the Entity-Relation model, the normalization of databases: Boyce-Codd and 3NF, transactions and recovery of databases, XML (along with xpath and xquery), and the basis of NoSQL data. MySQL in connection to the host language PHP.
General teaching training objectives in terms of expected learning outcomes:
- Knowledge and understanding: The course aims to give the knowledge and basic skills for database representation, organization, and query.
- Applying knowledge and understanding: the student will acquire the skills needed to design conceptually and logically a database. You can implement a relational database using SQL and interface with suitable host languages. In addition, acquired concepts to achieve non-relational databases such as XML and NoSQL. In this respect, a part of the course will consist of practical lessons which will be presented in MySQL, CouchDB, MongoDB, the PHP language, JavaScript, and Ajax.
- Making judgments: Through concrete examples and case studies, the student can independently develop solutions to specific problems related to databases.
- Communication skills: the student will acquire the necessary communication skills and expressive appropriateness in using technical language in the general area of databases.
- Learning skills: The course aims, as the goal, to provide students with the necessary theoretical and practical methods to deal independently and solve new problems that may arise during a work activity. For this purpose, we will cover several topics involving students searching for possible solutions to real issues, such as query optimization.
Course Structure
Lectures.
Should teaching be carried out in mixed mode or remotely, it may be necessary to introduce changes with respect to previous statements, in line with the programme planned and outlined in the syllabus.
Required Prerequisites
Course year: Second - first semester
Scientific Disciplinary Area: INF/01
CFU: 9 hours: 72
Propaedeuticities: Programming 2
Attendance of Lessons
The main resources made available to the student are face-to-face lectures, whose attendance is strongly recommended.
To better follow the lectures, the slides used for the course are shared with the students. However, the slides do not constitute a means of study: they provide details on the topics covered in class.
Detailed Course Content
- Introduction to databases: data models, general information about DBMS.
- The data model: The relational model. Relationships, attributes, instances of relationship, tuples. Integrity constraints, the key concept. relational algebra: basic and derived operators.
- The SQL (Structured Query Language). The data definition language: definition of tables, domains, indexes. Specification of simple integrity constraints. The query language: the join-selection-projection operators, aggregates operators, grouping operator. Nested queries and related, set operators. Recursive queries. The data manipulation language: insertion, deletion and modification of tuples. Definition of views, generic integrity constraints. Controlling access to a data base. Active databases.
- Design of databases: Conceptual design; logic design; physical design
- Normalization of data bases: Abnormalities. Functional dependencies. Decompositions of diagrams: which store records that preserve the functional dependencies. Normal forms: Boyce-Codd and 3NF.
- Basis of XML data
- Introduction to NoSQL database.
- Systems: MySQL (php / javaScript / Ajax interfaces). CouchDB, MongoDB.
Textbook Information
Textbooks:
- Atzeni,Ceri,Paraboschi, Fraternali, Torlone, Basi di Dati, McGraw-Hill.
- Albano-Ghelli-Orsini, Fondamenti di basi di dati, Zanichelli.
Other suggested books:
- Garcia-Molina, Ullman, Widow Database Systems: The Complete Book, Prentice Hall.
Course Planning
| Subjects | Text References | |
|---|---|---|
| 1 | Modello Relazionale | Capitolo 2, Atzeni et al. e materiale didattico integrativo |
| 2 | Algebra relazionale | Capitolo 3, Atzeni et al. e materiale didattico integrativo |
| 3 | SQL: Interrogazioni | Capitolo 4, (paragrafo 4.3) Atzeni et al. e Capitolo 6, Albano et al. e materiale didattico integrativo |
| 4 | SQL: Definizione dati | Capitolo 4, (paragrafo 4.2) Atzeni et al. e materiale didattico integrativo |
| 5 | SQL: Modifica dati | Capitolo 4, (paragrafo 4.4) Atzeni et al. e materiale didattico integrativo |
| 6 | Viste e Stored Procedure | Capitolo 5, Atzeni et al. e materiale didattico integrativo |
| 7 | Trigger | Capitolo 5, Atzeni et al. e materiale didattico integrativo |
| 8 | Progettazione di una base di dati | Capitolo 6, Atzeni et al. e materiale didattico integrativo |
| 9 | Progettazione concettuale | Capitolo 7, Atzeni et al. e materiale didattico integrativo |
| 10 | Progettazione logica | Capitolo 8, Atzeni et al. e materiale didattico integrativo |
| 11 | Normalizzazione | Capitolo 5, Albano et al e materiale didattico integrativo |
| 12 | Sviluppo Applicazioni e linguaggi host | http://www.w3schools.com e materiale didattico integrativo |
| 13 | Organizzazione fisica e gestione delle interrogazioni | Capitolo 11, Atzeni et Al. e materiale didattico integrativo |
| 14 | Transazioni, controllo affidabilità e controllo concorrenza | Capitolo 12, Atzeni et al e materiale didattico integrativo |
| 15 | XML, XML schema, Xquery Xpath | Capitolo 14, Atzeni et al. e materiale didattico integrativo |
| 16 | Cenni sui NoSQL database | materiale didattico integrativo |
Learning Assessment
Learning Assessment Procedures
The final examination consists of a written test and an oral interview in which a project is discussed.
The written test consists of exercises and theory questions.
Those who fail the written test cannot take the oral one. The written test may be viewed before the oral tests.
Unless otherwise announced, the written examination is held at 9:00 a.m.
Notes:
- Using any hardware tools (calculators, tablets, smartphones, cell phones, BT headsets, etc.), books, or personal papers during the (written) exams is prohibited.
- To take the exams, it is mandatory to make a reservation using the appropriate form on the student portal (therefore, in the absence of the prerequisites, it will not be possible to take the exam).
- Late bookings via email are not allowed. In the absence of reservation, the exam cannot be verbalized.
- Verification of learning may also be conducted electronically, should conditions require it.