IN/0031 - DATABASES
Academic Year 2021/2022
Free text for the University
GIORGIO GIACINTO (Tit.)
- Teaching style
- Lingua Insegnamento
|[70/89] ELECTRICAL, ELECTRONIC AND COMPUTER ENGINEERING||[89/30 - Ord. 2016] INFORMATICA||6||60|
|[70/89] ELECTRICAL, ELECTRONIC AND COMPUTER ENGINEERING||[89/66 - Ord. 2016] INFORMATICA ON LINE E IN PRESENZA (BLENDED)||6||60|
The teaching activity on Databases is aimed at providing the knowledge, understanding, and capabilities of applying the acquired knowledge, to design, build, and manage a database. The main part of the course will cover the relational model and the SQL language, while a few hours will be devoted to acquire the basic knowledge on the emerging 'NoSQL' data models.
The learning outcomes of this teaching activity, expressed in terms of the Dublin Descriptors, are the following:
Knowledge and understanding.
This teaching activity is aimed to provide the student with the knowledge and understanding of the relational data model. In particular, the techniques and methodologies to design, build, manage and maintain a relational database are described. The conceptual database design is carried out through the ER model, while the design of queries is carried out through the SQL language. In addition to the relational model, the course also presents the basic characteristics of the data models falling within the so-called 'NoSQL' movement.
Applying knowledge and understanding
For each topic of the course, a number of examples and practical exercises will be presented in class, so that the students gain the basic skills both for designing a database, and for understanding an existing model written in the SQL language. Part of the exercises will be carried out using open-source software packages employed in professional contexts. The students will be also able to compute the reduction in the computational cost of query execution when indexes are used, and the corresponding memory use.
The students will evaluate the different alternatives when designing a database during the different phases, i.e., conceptual design, query specification, choice of the indexes. All the alternatives should be in agreement with the requirements, and should allow the maintenance and evolution of the database, as well as exhibiting the minimum performance required by the application during the operation. The basic knowledge of the models falling in the 'NoSQL' movement will allow the student to assess in which cases one of these models can be preferred to the relational model.
The design of the conceptual model of a database for a given application requires the capability of translating the requirements expressed by the user in natural language, into the formalism of the relational model. The students will learn the basic communication capabilities needed to interact with non technical users in order to clearly define the application requirements.
Lifelong learning skills
Throughout the course the student will be exposed to the sources of the technical and professional literature on database systems that enable lifelong learning capabilities. In particular, the section of the course on 'NoSQL' database is based on the most recent development of data models, whose material is mainly available in technical websites.
The student must have the knowledge of data representation in computers, computer architecture, basic algorithms for sorting and searching, high-level programming languages.
Applying the knowledge
The student must be able to specify an algorithm fo low complexity into a program in a high-level language.
The student must be able to understand the language of logic used in the math courses of the first year of any engineering course.
For students enrolled in the degree course in Electrical, Electronic and Computer Engineering, it is advised they have passed the exam of the course on Foundations of Computer Science.
Database Architecture (4hrs theory)
The relational model, table definition and query formulation in SQL and relational algebra (10hrs theory - 6hrs exercises)
Database conceptual design using the ER model, and ER-to-Relational mapping (7hrs theory - 3hrs exercises)
Normalization (4hrs theory - 2hrs exercises)
SQL and programming languages (4hrs theory - 2hrs exercises)
File organizations and index structures for files (6hrs theory - 2hrs exercises)
Query processing and physical database design (2hrs theory)
Database security and privacy (2hrs theory)
Basic concepts on "NoSQL" databases (4hrs theory - 2 hrs exercises)
- lectures with slides
- exercises on the PostgreSQL DBMA and on a "NoSQL" database
- seminar or site visit.
The teaching material is available at the following web address: http://people.unica.it/giorgiogiacinto/didattica/insegnamenti/materiale-didattico/basi-di-dati/.
The teacher is available to answer questione either by email, or during the contact hours, or directly in class, during the lecture or during the break between consecutive teaching hours.
Teaching modalities might be modified as a consequence of the epidemiological situation. It will be possible that lectures will be given through streaming platforms, and exercises will be carried out through interactive platforms.
Verification of learning
The final test is carried out in writing, and consists of questions and exercises on the following topics:
- query specification in SQL, and use of SQL in programs written in high level languages
- query specification in relational algebra
- conceptual design of a database using the ER model, and translation into a relational schema
- candidate keys, normal forms and normalization of a given relation with a set of functional dependencies
- indexes for query speedup, file access and query optimization
- security and privacy in databases
- NoSQL models
For each exercise/question in the test, a maximum score is assigned. The answer provided for each exercise/question is evaluated with a score from 0 to the max assigned score. The maximum score is assigned in the case of a correct answer, while a smaller score is assigned according to the severity of the errors. In particular, conceptual errors, and errors caused by lack of knowledge have a larger weight than errors due to misunderstandings or inaccuracies.
The final score is given by the sum of the scores assigned to each exercise/question of the test. The max total score will be equal to 32. Students attaining the final score equal to 32 will be assigned the '30 cum laude' mark.
The semester is subdivided into two parts, and students can take an exam related to the topics of the first half of the course during the short break. Then, a second test will be given after the end of the course, that will cover the topics of the second half of the course.
It is possible to increase the final mark with a short oral test.
The exam could be carried out remotely if the epidemiological situation will prevent the possibility of having written texts. In this case, each student will be given a short written text with three exercises: design of a relational database through the ER model, the formulation of a SQL query and a query using the relational algebra. Then, each student will have an oral exam with a discussion on the proposed solution to the three exercises, and question on the other topics of the exam. The evaluation will be carried out according to the above criteria.
The following textbooks are available in the Main Library of the Faculty of Engineering and Archtecture and contain all the topics addressed in this teaching activity.
Ramez Elmasri, Shamkant B. Navathe
Fundamentals of Database Systems, 7/E
Pramod J. Sadalage, Martin Fowler
NoSQL Distilled: A Brief Guide to the Emerging World of Polyglot Persistence
Addison-Wesley Professional 2012
Copies of the slides as well as all the material using for the exercises will be available at the following link