Database Systems
Being able to code in Java is a requirement. Therefore, having passed the exam of the Programming course is a prerequisite.
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