Database Systems
- Overview
- Assessment methods
- Learning objectives
- Contents
- Bibliography
- Teaching methods
- Contacts/Info
Being able to code in Java is a requirement. Therefore, having passed the exam of the Programming course is a prerequisite.
Students learning extent is assessed through a written exam which lasts approx. 2.5 hours. Students are not allowed to consult any text book or other learning material during the test. The test is divided into two parts. The first part consists of 6 open questions on the conceptual aspects of the course, whereas the second part consists of three exercises: two exercises on SQL (one on the DDL part and the other on the query language) and one on database design. The grade of each part is given in thirtieths. The overall grade is determined by the following formula: 1/3 * grade of the first part + 2/3 * grade of the second part. The exam is passed if the student obtains a grade greater than or equal to 18 in both the parts.
The course has the main goal of illustrating the tools, models and languages for the use, design and development of relational databases and applications for these databases. More specifically, the objectives of the course are the following:
1. Understand the features and the main services offered by a database management system.
2. Know and understand the relational data model.
3. Know and use the SQL standard, both for the creation of a database schema and the specification of correctness constraints, as well as for the manipulation and querying of its instances.
4. Know the main solutions for the development of an application that interfaces a database; know the concept of transaction and the services made available by a DBMS for their concurrent execution.
5. Know and use JDBC for the implementation of object-oriented programs that interface a relational data base.
6. Know the main methodologies for the design of a relational database.
7. Know and be able to use the Entity-Relationship model for the conceptual design of a database.
8. Know how to deal with all the phases of the design of a relational database, with particular reference to conceptual and logical design.
The knowledge provided by the course will facilitate individual deepening of student knowledge and development of new skills. For example, it should not be difficult for a student who has successfully followed the course to independently learn the basic concepts of a new non-relational data model or the programming of applications that interface to a data base with a language other than the Java language seen in class.
Lessons will cover the following topics:
Introduction to databases and database management systems (4h, objective 1)
- introductory concepts
- from operating systems to DBMSs
- objectives and services of a DBMS
- data models and their evolutions
- levels in data representation
- languages and users of a DBMS
The relational data model (6 h, objective 2)
- relations
- null values
- keys and foreign keys
The SQL language - DDL commands (4 h, objective 3)
- data types
- tables creation
- tables drop and update
SQL queries (12 h, objective 3)
- basic format of the SELECT command
- operators and functions
- sorting of the result of a query
- join operation
- group functions and grouping
- null values
- sub-queries
- set operations
SQL update operations (4 h, objective 3)
- insertion of tuples
- deletion of tuples
- modification of tuples
Additional SQL commands (4 h, objective 3)
- CHECK constraints and assertions
- check of integrity constraints
- views
SQL and programming languages. (5 h, objectives 4/5)
- SQL procedural extensions
- API (JDBC)
- embedded SQL
The concept of transaction (2 h, objective 4)
Database design (2 h, objective)
- database design phases
Entity-relationship model (4 h, objective 7)
- basic constructs
- integrity constraints
- generalization hierarchies
Design methodologies (6 h, objective 6)
- collection and analysis of the requirements
- restructuring of the specifications
- ER scheme generation
- quality of ER schemes
Logical design - restructuring phase (5 h, training objective 8)
- removal of composite and multi-value attributes
- removal of generalization hierarchies
Logical design - translation phase (6 h, objective 8)
- entity translation
- translation of associations
- final scheme and optimizations
B. Catania, E. Ferrari, e G. Guerrini.Sistemi di Gestione Dati: Concetti e Architetture, Città Studi Edizioni, 2006.
B. Carminati, A. Maddalena, M. Mesiti. Sistemi di Gestione Dati (Esercizi),Città Studi Edizioni, 2008.
Slides (in pdf) and additional material is available on the university e-learning website, as well as the text and solutions of previous exams.
The course consists of lectures (64 ore) and exercises (16 ore).
The lessons illustrate the basic concepts of data management systems, the relational model, the languages for its implementation and manipulation (both in stand-alone mode and through an application program) and the techniques and tools for the design of a database.
Some of the topics covered in class are the subject of exercises involving the active participation of students. Exercises cover the use of SQL on a commercial DBMS, the programming of Java applications that interface a relational database using JDBC and the exemplification of the techniques for database design by presenting and discussing case studies.
Students need to contact the instructor by email to set up an appointment. The instructor will only answer to emails sent by the studenti.uninsubria.it domain.