Databases

You are here

Home » Studies » Bachelor's degrees » Bachelor Degree in Informatics Engineering » Curriculum » Syllabus » Databases
Credits
6
Types
Compulsory
Requirements
Department
ESSI
Web
https://learnsql.fib.upc.edu/
This course gives an overview of what is a database, what is a database model, and what are the different types of users and languages. Specifically we go deep into the relational database model and the database logic components it provides. Students practice the creation and use of these components by means of the SQL language. The course includes an introduction to the design of relational databases, and the study of important issues for the use of databases in the development of software systems such as privileges, transactions, and indexs. The course finishes with an introduction to the new NOSQL database management systems.

Teachers

Person in charge

  • Carme Quer Bosor ( )
  • Sergi Nadal Francesch ( )

Others

  • Antoni Urpi Tubella ( )
  • Carme Martin Escofet ( )
  • Francisco Miguel Rodero Blánquez ( )
  • Jordi Casanovas Muñoz ( )
  • Marc Molinuevo Garcia ( )
  • Marc Oriol Hilari ( )
  • María José Casañ Guerrero ( )
  • Pau Carbonell Vives ( )
  • Quim Motger de la Encarnacion ( )
  • Santiago Rivas Contreras ( )
  • Vicent Picornell Alandete ( )
  • Xavier Burgués Illa ( )

Weekly hours

Theory
2
Problems
0
Laboratory
2
Guided learning
0
Autonomous learning
6

Competences

Technical Competences

Common technical competencies

  • CT2 - To use properly theories, procedures and tools in the professional development of the informatics engineering in all its fields (specification, design, implementation, deployment and products evaluation) demonstrating the comprehension of the adopted compromises in the design decisions.
    • CT2.2 - To demonstrate knowledge and capacity to apply the characteristics, functionalities and structure of data bases, allowing an adequate use, design, analysis and implementation of applications based on them.
    • CT2.3 - To design, develop, select and evaluate computer applications, systems and services and, at the same time, ensure its reliability, security and quality in function of ethical principles and the current legislation and normative.
    • CT2.4 - To demonstrate knowledge and capacity to apply the needed tools for storage, processing and access to the information system, even if they are web-based systems.
  • CT8 - To plan, conceive, deploy and manage computer projects, services and systems in every field, to lead the start-up, the continuous improvement and to value the economical and social impact.
    • CT8.6 - To demonstrate the comprehension of the importance of the negotiation, effective working habits, leadership and communication skills in all the software development environments.
    • CT8.7 - To control project versions and configurations.

Transversal Competences

Appropiate attitude towards work

  • G8 [Avaluable] - To have motivation to be professional and to face new challenges, have a width vision of the possibilities of the career in the field of informatics engineering. To feel motivated for the quality and the continuous improvement, and behave rigorously in the professional development. Capacity to adapt oneself to organizational or technological changes. Capacity to work in situations with information shortage and/or time and/or resources restrictions.
    • G8.2 - To be rigorous in the professional development. To be motivated and have a proactive attitude for the quality in the work. Capacity to adapt oneself to organizational or technological changes. Capacity to work in situations with information shortage and/or time and/or resources restrictions.

Objectives

  1. To have a general vision of what a database is, what is a database model, the types of users of databases and which are the categories of databases languages.
    Related competences: CT2.2, CT2.4,
  2. To know the objectives of a database management system and their architecture.
    Related competences: CT2.2, CT2.4,
  3. To understand the database relational model, their languages (SQL and relational algebra) and the usual components of a relational database.
    Related competences: CT2.2, CT2.4,
  4. To be able to define, create and manipulate usual relational database components.
    Related competences: CT2.2, CT2.4, CT2.3,
  5. To be able to build programs to manage relational databases.
    Related competences: CT2.2, CT2.4, CT2.3,
  6. To be able to apply some defined quality criteria to choose between several SQL statements, database components, or programs, that manage a database and implement the same functionality.
    Related competences: CT2.4, CT2.3,
  7. To be able to apply some defined quality criteria to choose which types of database components or management programs are more suitable for the implementation of a certain behavior of a software.
    Related competences: CT2.4, CT2.3,
  8. To have a general vision of how the design of a database should be included in a software development process.
    Related competences: CT2.2, CT2.3,
  9. To be able to obtain a database relational model starting from a conceptual models in UML.
    Related competences: CT2.2, CT2.3,
  10. To know the concept of database transaction and its implications.
    Related competences: CT2.2, CT2.4, CT2.3,
  11. To know how to identify the different types of interference that can occur between database transactions and their relationship with the isolation levels that defines the SQL Standard.
    Related competences: CT2.2, CT2.4, CT2.3,
  12. To know the locking concurrency control technique.
    Related competences: CT2.2, CT2.4, CT2.3,
  13. To know the possible physical structures for storing data and its implications for in terms of efficiency.
    Related competences: CT2.4, CT2.3,
  14. To know the access methods to data and its implications in terms of efficiency.
    Related competences: CT2.4, CT2.3,
  15. To be able to participate with a proactive atitude in making exercices in teams of 2 or more students, according to the roles assigned to each student that can change during the execution of the exercises.
    Related competences: G8.2, CT8.6,
  16. To be able to reach to a solution of the exercises that meets the quality criteria defined with limited time and resources.
    Related competences: G8.2, CT8.6, CT8.7,
  17. To be able to configure the environment for the implementation and execution of database components and programs that access to databases, taking as input the resources offered to students.
    Related competences: CT8.7,
  18. Understand the main features of NOSQL databases systems, understand how they differ with respect to relational systems, and have seen a classification of types of NOSQL systems that exist today.
    Related competences: CT2.2,

Contents

  1. Introduction
    Database Concept. Design models and databases. Types of users. Categories of database languages. Database Management Systems (DBMS). Desirable objectives for databases that DBMS should provide. Architecture of DBMS.
  2. Relational model
    Objectives and origin. Data structure with which to construct relational databases. Operations that provides the relational model to manipulate and query data. Integrity rules to be met by the data in a relational database.
  3. Languages: Relational Algebra and SQL
    Introduction. Relational Algebra: relational algebra operations; queries. SQL: create tables, insert, delete and update of rows in a table, queries on a database. Considerations and quality criteria about how to write queries.
  4. Logical database components
    Concept of a logical database component: data and control components. Introduction to the data components: schemes, tables and domains, assertions and views. Introduction to the control components: stored procedures, triggers and privileges.
  5. Stored Procedures and Triggers
    Implementation of stored procedures in PL/pgSQL language. Implementation of triggers in PostgreSQL. Considerations and quality criteria in the design and implementation of procedures and triggers.
  6. SQL Programming
    Programming in Java and JDBC. Considerations and quality criteria in the design and implementation of programs that access databases.
  7. Introduction to the design of relational databases
    Stages in the design of a database. Introduction to the understanding of simple UML conceptual models. Translation of simple UML conceptual models to relational model databases.
  8. Transactions and concurrency
    Concept of transaction. ACID properties of transactions. Interference between transactions. Serialitzability. Recoverability. Concurrency control techniques. Isolation Levels. Locking and isolation levels.
  9. Physical storage structures and access methods
    Introduction. Access methods to perform queries and updates in a database. Costs of the different access methods.
  10. NOSQL
    Introducción. Diferencias con los SGBD relacionales tradicionales. Objetivos de los SGBD NOSQL. Ejemplos de sistemas que requieren este tipo de SGBD.

Activities

Activity Evaluation act


T/P. Study of the databases introduction

The contents related with the subject are presented
Objectives: 1 2
Contents:
Theory
2h
Problems
0h
Laboratory
0h
Guided learning
0h
Autonomous learning
2h

T/P. Study of the databases relational model

The contents related with the subject are presented. The exercises proposed by the teachers are done.
Objectives: 3
Contents:
Theory
2h
Problems
0h
Laboratory
0h
Guided learning
0h
Autonomous learning
2h

T/P. Study of the data logical components

The contents related with the subject are presented. The exercises proposed by the teachers are done.
Objectives: 3 4
Contents:
Theory
2h
Problems
0h
Laboratory
0h
Guided learning
0h
Autonomous learning
2h

T/P. Study of control logical components

The contents related with the subject are presented. The exercises proposed by the teachers are done.
Objectives: 3 4 7
Contents:
Theory
1h
Problems
0h
Laboratory
0h
Guided learning
0h
Autonomous learning
2h

T/P. Exercises: Privileges, views and assertions

Exercises are solved in class with the help of the teacher.
Objectives: 3 4 7
Contents:
Theory
3h
Problems
0h
Laboratory
0h
Guided learning
0h
Autonomous learning
2h

T/P. Study of the introduction to design of relational databases

The contents related with the subject are presented. The exercises proposed by the teachers are done.
Objectives: 8 9
Contents:
Theory
2h
Problems
0h
Laboratory
0h
Guided learning
0h
Autonomous learning
2h

T/P. Exercises: Translation from UML to relational model

Exercises are solved in class with the help of the teacher.
Objectives: 8 9
Contents:
Theory
2h
Problems
0h
Laboratory
0h
Guided learning
0h
Autonomous learning
2h

T/P. Study of transactions and concurrency

The contents related with the subject are presented. The exercises proposed by the teachers are done.
Objectives: 10 11 12
Contents:
Theory
2h
Problems
0h
Laboratory
0h
Guided learning
0h
Autonomous learning
2h

T/P. Exercises: Transactions and Concurrency

Exercises are solved in class with the help of the teacher.
Objectives: 10 11 12
Contents:
Theory
3h
Problems
0h
Laboratory
0h
Guided learning
0h
Autonomous learning
4h

T/P. Study of storage and access methods

The contents related with the subject are presented. The exercises proposed by the teachers are done.
Objectives: 13 14
Contents:
Theory
3h
Problems
0h
Laboratory
0h
Guided learning
0h
Autonomous learning
4h

T/P. Exercises: Storage and access methods

Exercises are solved in class with the help of the teacher.
Objectives: 13 14
Contents:
Theory
2h
Problems
0h
Laboratory
0h
Guided learning
0h
Autonomous learning
3h

T/P NOSQL

The contents related with the subject are presented.
Objectives: 1 18
Contents:
Theory
1h
Problems
0h
Laboratory
0h
Guided learning
0h
Autonomous learning
1h

T/P. Review

Questions are answered on the topics of the subject
Objectives: 3 4 5 6
Contents:
Theory
0h
Problems
0h
Laboratory
2h
Guided learning
0h
Autonomous learning
5h

L: Environment preparation and preliminary study

Prepare the environment for deployment, creation and execution of database components from resources provided by the teacher. Preliminary study of the database that will be used in the first laboratory classes and of some basic SQL sentences.

Theory
0h
Problems
0h
Laboratory
2h
Guided learning
0h
Autonomous learning
2h

L. Study of SQL 1

Laboratory teams are created. The way of working in laboratory classes is presented. The teams solve a laboratory questionnaire on the topic in class. An individual paper question is answered on the topic in class.
Objectives: 3 4 6
Contents:
Theory
0h
Problems
0h
Laboratory
2h
Guided learning
0h
Autonomous learning
0h

L. Study of SQL 2

The teams solve a laboratory questionnaire on the topic in class. An individual paper question is answered on the topic in class.
Objectives: 3 4 6 15 16 17
Contents:
Theory
0h
Problems
0h
Laboratory
2h
Guided learning
0h
Autonomous learning
2h

L. Exercises: SQL

The teams solve a laboratory questionnaire on the topic in class. An individual paper question is answered on the topic in class.
Objectives: 3 4 6 15 16 17
Contents:
Theory
0h
Problems
0h
Laboratory
2h
Guided learning
0h
Autonomous learning
2h

L. Study of relational algebra

The teams solve a laboratory questionnaire on the topic in class. An individual paper question is answered on the topic in class.
Objectives: 3
Contents:
Theory
0h
Problems
0h
Laboratory
2h
Guided learning
0h
Autonomous learning
2h

L. Study of SQL and relational algebra

The teams solve a laboratory questionnaire on the topic in class. An individual paper question is answered on the topic in class.
Objectives: 3 4 6 15 16 17
Contents:
Theory
0h
Problems
0h
Laboratory
2h
Guided learning
0h
Autonomous learning
2h

L. Study: Algebra and SQL

Students review laboratory exercises and SQL and relational algebra exams.

Theory
0h
Problems
0h
Laboratory
2h
Guided learning
0h
Autonomous learning
2h

L. Exercises: Stored Procedures basics

The teams solve a laboratory questionnaire on the topic in class. An individual paper question is answered on the topic in class.
Objectives: 3 4 6 15 16 17
Contents:
Theory
0h
Problems
0h
Laboratory
2h
Guided learning
0h
Autonomous learning
2h

L. Exercises: Triggers basics

The teams solve a laboratory questionnaire on the topic in class. An individual paper question is answered on the topic in class.
Objectives: 4 6 15 16 17
Contents:
Theory
0h
Problems
0h
Laboratory
2h
Guided learning
0h
Autonomous learning
2h

L. Exercises: Stored Procedures / Triggers.

The teams solve a laboratory questionnaire on the topic in class. An individual paper question is answered on the topic in class.
Objectives: 4 6 15 16 17
Contents:
Theory
0h
Problems
0h
Laboratory
2h
Guided learning
0h
Autonomous learning
2h

L: Lab study on Stored Procedures and Triggers.

Students review laboratory exercises and exams on Stored Procedures and Triggers.

Theory
0h
Problems
0h
Laboratory
2h
Guided learning
0h
Autonomous learning
2h

L. Exercises: Programming with SQL - JDBC basics

The teams solve a laboratory questionnaire on the topic in class. An individual paper question is answered on the topic in class.
Objectives: 4 6 15 16 17
Contents:
Theory
0h
Problems
0h
Laboratory
2h
Guided learning
0h
Autonomous learning
2h

L. Exercises: Programming with SQL - JDBC

The teams solve a laboratory questionnaire on the topic in class. An individual paper question is answered on the topic in class.
Objectives: 3 5 15 16 17
Contents:
Theory
0h
Problems
0h
Laboratory
2h
Guided learning
0h
Autonomous learning
2h

L. Lab study on Programming with SQL - JDBC

Students review laboratory exercises and exams on Programming with SQL - JDBC

Theory
0h
Problems
0h
Laboratory
2h
Guided learning
0h
Autonomous learning
2h

L. Partial exam

Students: Solve the exam individually. It is not possible to use any material.
Objectives: 1 2 3 4 6 8 9 16
Week: 8 (Outside class hours)
Theory
2h
Problems
0h
Laboratory
0h
Guided learning
0h
Autonomous learning
15h

Final exam

The student: Solves the exam individually. It is not possible to use any material.
Objectives: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
Week: 15 (Outside class hours)
Theory
3h
Problems
0h
Laboratory
0h
Guided learning
0h
Autonomous learning
16h

Teaching methodology

Theory/Problem classes (2.3 hours per week).
Independent learning: To prepare the classes, the students may have to read and understand some materials stated by the teacher. After classes, the student have to review and do exercices about the subject studied in class.
Theory classes. In the theory classes the teacher present a part of the contents of the course. Usually the teachers use slides, which the students should bring to the classes.
Problem classes. In the problem classes the students solve exercises on the content presented during the theory classes.

Laboratory classes (1.7 hours per week).
Independent learning activities: The contents that are the aim of the laboratory classes are studied independently and individually by students at home. Each week before the lab class students have home work to do that ends with the resolution of a questionnaire of moodle / LearnSQL.
Laboratory classes: The work at classes is in teams of 2 students. The students have the opportunity to share concerns with his/her teammate on the home work of the previous weed, and if it is necessary they ask the teacher questions unresolved. Then the students do the activities stated by the teacher and finally they solve a moodle/LearnSQL questionnaire.
Laboratory evaluation: In each class, the students answer a question individually to evaluate the work done at home previously and during the class. The evaluation is also based on the exercices solved during the class.

Resources related to laboratory classes:
All documents, materials and questionnaires related with the course are available to students through the platform moodle/LearnSQL.
Apart from the feedback that teachers give to students during classes, the platform moodle/LearnSQL includes a corrector of database exercises that provides feedback to students about exercises solutions.

Students will be evaluated just in case they assist to the GROUP WHERE ARE ENROLLED, both in classes of theory/problems and in classes of laboratory.

Evaluation methodology

The grade of the course is based on technical competencies:

- NLB: Laboratory grade. It is based on:
. Satisfactory resolution of the questionnaire corresponding to each class.
. Grades of the question answered during each class.

- NEP - Partial exam grade. The partial exam includes the topics: 1, 2, 3, 4 (without stored procedures and triggers), and 7.

- NEF: Final exam grade. The final exam includes the following topics: 4 (only stored procedures and triggers), 5, 6, 8, 9 and 10.

Course grade = 0.40*NEP + 0.50*NEF + 0.10*NL

Students will be evaluated just in case they assist to the GROUP WHERE ARE ENROLLED.

Students who have not taken any of the exams will have a final grade of Not Presented (NP).

Any attempt of fraud during the course will imply the application of the general academic regulations of the UPC

Grades of the generic competence: The possible grades are A, B, C or D (where A corresponds to an excellent level of accomplishment, B corresponds to a desired level of accomplishment, C corresponds to a sufficient level of accomplishment and D corresponds to a level not sufficient). A good evaluation of this competence will be for the students that:
- Act with rigor in the classes (their attitude in class is appropriate according to the guidelines given for different types of class, either theory, problems or laboratory).
- Act with respect towards peers, and in case of teams work with positive interdependence respect to the other team members.
- Collaborate actively in the activities of cooperative learning in teams or pairs that are made. Accept and perform the roles assigned to the team members during these activities.
- Do exercises arriving to solutions (in the laboratory study questionnaires) that pass all the test games (no matter how many attempts they need).
- Do exercises arriving to solutions (in the exams) that meet the quality criteria established in the course for each type of exercise.
- In general, complete the exercises in the time and resources provided.

Bibliography

Basic:

Complementary:

Web links

Previous capacities

To know the data structures in internal memory. To be able to implement programs of medium complexity.