Databases

You are here

Home » Studies » Bachelor's degrees » Bachelor Degree in Data Science and Engineering » Curriculum » Syllabus » Databases
Credits
6
Types
Compulsory
Requirements
This subject has not requirements, but it has got previous capacities
Department
ESSI
Web
https://learnsql.fib.upc.es/
Students will have an overview of what a database is, its objectives and main components. It will deepen in the managers based on the relational database model and will practice the creation, management and use of its components through SQL. An introduction to the design of relational databases is included, as well as the study of the different components of a manager.

Teachers

Person in charge

  • Carme Quer Bosor ( )

Others

  • Antoni Urpi Tubella ( )
  • Francisco Miguel Rodero Blánquez ( )
  • Quim Motger de la Encarnacion ( )
  • Sergi Nadal Francesch ( )

Weekly hours

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

Competences

Technical Competences

Technical competencies

  • CE7 - Demonstrate knowledge and ability to apply the necessary tools for the storage, processing and access to data.

Transversal Competences

Transversals

  • CT4 [Avaluable] - Teamwork. Be able to work as a member of an interdisciplinary team, either as a member or conducting management tasks, with the aim of contributing to develop projects with pragmatism and a sense of responsibility, taking commitments taking into account available resources.
  • CT6 - Autonomous Learning. Detect deficiencies in one's own knowledge and overcome them through critical reflection and the choice of the best action to extend this knowledge.

Basic

  • CB2 - That the students know how to apply their knowledge to their work or vocation in a professional way and possess the skills that are usually demonstrated through the elaboration and defense of arguments and problem solving within their area of ??study.
  • CB3 - That students have the ability to gather and interpret relevant data (usually within their area of ??study) to make judgments that include a reflection on relevant social, scientific or ethical issues.

Generic Technical Competences

Generic

  • CG1 - To design computer systems that integrate data of provenances and very diverse forms, create with them mathematical models, reason on these models and act accordingly, learning from experience.
  • CG2 - Choose and apply the most appropriate methods and techniques to a problem defined by data that represents a challenge for its volume, speed, variety or heterogeneity, including computer, mathematical, statistical and signal processing methods.

Objectives

  1. To have a general vision of what a database is and what is a database model.
    Related competences: CE7, CT6,
  2. To know the objectives of a database management system and their architecture.
    Related competences: CE7, CT6,
  3. To understand the database relational model, their languages (SQL and relational algebra) and the usual components of a relational database.
    Related competences: CE7, CT4, CT6, CB2, CB3,
  4. To be able to define, create and manipulate usual relational database components.
    Related competences: CE7, CT4, CT6, CB2, CB3,
  5. To be able to build programs to manage relational databases.
    Related competences: CE7, CT4, CT6, CB2, CB3,
  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: CE7, CT4, CT6, CB2, CB3,
  7. To have an overview of data warehouses and multidimensional databases, and to know how to express OLAP statements via SQL.
    Related competences: CE7, CT4, CT6, CG1, CG2, CB2, CB3,
  8. Know the different semi-structured data formats, and know how to express SQL queries on some of these formats.
    Related competences: CE7, CT4, CT6, CG1, CB2, CB3,
  9. To have a general vision of how to design a database
    Related competences: CE7, CT6,
  10. To be able to obtain a database relational model starting from a conceptual models in UML.
    Related competences: CE7, CT6,
  11. To know the concept of database transaction and its implications.
    Related competences: CE7, CT6,
  12. 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: CE7, CT6,
  13. To know the locking concurrency control technique.
    Related competences: CE7, CT6,
  14. To know the possible physical structures for storing data and its implications for in terms of efficiency.
    Related competences: CE7, CT6, CG1, CG2,
  15. To know the access methods to data and its implications in terms of efficiency.
    Related competences: CE7, CT6, CG1, CG2,
  16. 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: CE7, CT4, CT6, CB2, CB3,
  17. Have a global view of the role of SQL and other types of components studied in data life cycle tasks
    Related competences: CE7, CG1, CG2,

Contents

  1. Introduction
    Database concept. Database design and models. Types of users. Categories of languages. Concept of database management system (DBMS). Desirable goals for databases that DBMSs must provide. Architecture of the DBMS.
  2. The relational model
    Objectives and origin. Structure of data with which the relational databases are built. Operations provided by the relational model to manipulate and query the data. Integrity rules to be met by the data in a relational database.
  3. Languages: Relational algebra and SQL
    Introduction. Relational algebra: operations of relational algebra; queries. SQL: table creation; insertion, deletion and modification of rows in a table; queries on a database. Considerations about the implementation of 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. Data Warehouses and OLAP
    Introduction to data warehouses and multidimensional databases. SQL Extensions for OLAP
  6. Semi-structured data and SQL extensions for querying it
    Introduction to the different semi-structured data formats. SQL extensions to query semi-structured data in JSON format.
  7. SQL - Python Programming
    Programming in Python and DataFrames. Considerations and quality criteria in the design and implementation of programs that access databases.
  8. 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
  9. Transactions and concurrency
    Concept of transaction. ACID properties of transactions. Interference between transactions. Serialitzability. Recoverability. Concurrency control techniques. Isolation Levels. Locking and isolation levels.
  10. Physical storage structures, access methods and optimization
    Introduction. Access methods to perform queries and updates in a database. Costs of the different access methods. Introduction to Ouery Optimaztion
  11. SQL in the data life cycle
    The life cycle of the data is presented, that is to say the stages through which the data pass from its acquisition, preparation and analysis, together with the different tasks that are carried out in each stage, as well as the role of SQL and other components studied in these tasks

Activities

Activity Evaluation act


Study of the database introduction


Objectives: 2 1
Contents:
Theory
2h
Problems
0h
Laboratory
0h
Guided learning
0h
Autonomous learning
2h

Study of the databases introduction


Objectives: 3
Contents:
Theory
2h
Problems
0h
Laboratory
0h
Guided learning
0h
Autonomous learning
2h

Study of the data logical components


Objectives: 3 6 4
Contents:
Theory
4h
Problems
0h
Laboratory
0h
Guided learning
0h
Autonomous learning
4h

Study of the introduction to design of relational databases


Objectives: 9 10
Contents:
Theory
4h
Problems
0h
Laboratory
0h
Guided learning
0h
Autonomous learning
4h

Study of transactions and concurrency


Objectives: 11 12 13
Contents:
Theory
5h
Problems
0h
Laboratory
0h
Guided learning
0h
Autonomous learning
7h

Study of storage, access methods and optimization


Objectives: 14 15
Contents:
Theory
7h
Problems
0h
Laboratory
0h
Guided learning
0h
Autonomous learning
10h

Study of the use of SQL in the data life cycle


Objectives: 17
Contents:
Theory
1h
Problems
0h
Laboratory
0h
Guided learning
0h
Autonomous learning
1h

Study of the Relational Algebra and SQL


Objectives: 3 6 4
Contents:
Theory
0h
Problems
0h
Laboratory
12h
Guided learning
0h
Autonomous learning
13h

Study of data warehouses and in OLAP


Objectives: 6 16 7
Contents:
Theory
0h
Problems
0h
Laboratory
2h
Guided learning
0h
Autonomous learning
4h

Study of semi-structured data and SQL extensions to query them


Objectives: 8
Contents:
Theory
0h
Problems
0h
Laboratory
2h
Guided learning
0h
Autonomous learning
4h

Study of stored procedures and triggers


Objectives: 3 6 4
Contents:
Theory
0h
Problems
0h
Laboratory
8h
Guided learning
0h
Autonomous learning
4h

Programming with SQL - Python - DataFrames


Objectives: 5 6 4
Contents:
Theory
0h
Problems
0h
Laboratory
6h
Guided learning
0h
Autonomous learning
4h

Partial exam


Objectives: 2 3 6 9 10 1 4 7
Week: 8 (Outside class hours)
Theory
2h
Problems
0h
Laboratory
0h
Guided learning
0h
Autonomous learning
16h

Final Exam


Objectives: 2 3 5 6 16 9 10 11 12 13 14 15 1 4 7
Week: 15 (Outside class hours)
Theory
3h
Problems
0h
Laboratory
0h
Guided learning
0h
Autonomous learning
15h

Reviews and resolution of doubts about the exams



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

Teaching methodology

Theory classes / problems
Autonomous learning: To prepare classes the student may have to read and understand materials and / or notes indicated by the teacher. Afterwards in class, the student needs to review and solve exercises on the topic of study.
Theory classes In lectures the teachers present a part of the contents of the subject. Normally, teachers use transparencies that are accessible to students.
Problems classes In problem classes, students solve exercises about content presented during theory classes.

Laboratory classes
Autonomous learning: The contents that are worked on in the laboratory classes will be studied autonomously by the students. Each week before in the laboratory class students will have a homework assignment that will end with the resolution of a moodle / LearnSQL quiz.
Laboratory classes: Class work will be in teams of 2 students. Students have the opportunity to share doubts with their teammate about the work they have done at home, and if necessary, to ask questions that are not resolved to the teacher. Next the students do the activities that the teacher has indicated and finally solve the class questionnaire.

Evaluation methodology

The qualification of the technical competences is based on:

- NL - Active participation in laboratory sessions. The classes in which students have participated will be taken into account in case of successfully submision of the exercises proposed in the class through LearnSQL. The grade will be calculated in proportion to the classes in which the students have actively participated.

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

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

- NF = 0.45 * NEP + 0.45 * NEF + 0.1 * NL

- For students who can take the re-assessment, the re-assessment exam mark will replace NEF and NEP. In any case, the final mark will be the maximum between the ordinary mark and the re-evaluation mark.

Bibliography

Basic:

Complementary:

Web links

Previous capacities

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