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

Others

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
    0h
    Problems
    0h
    Laboratory
    0h
    Guided learning
    0h
    Autonomous learning
    0h

    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
    0h
    Problems
    0h
    Laboratory
    0h
    Guided learning
    0h
    Autonomous learning
    0h

    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: 3, 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.