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

  • Antoni Urpi Tubella ( )

Others

  • Carme Quer Bosor ( )
  • Quim Motger de la Encarnacion ( )

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, what is a database model, the types of users of databases and which are the categories of databases languages.
    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. 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,
  9. To have a general vision of how the design of a database should be included in a software development process.
    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,

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

Activities

Activity Evaluation act


Study of the database introduction


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

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

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
6h

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 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 8 7
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 - JDBC


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

Partial exam


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

Final Exam


Objectives: 2 3 5 6 8 9 10 11 12 13 14 15 1 4 7
Week: 15 (Outside class hours)
Type: theory exam
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 students would be advised to obtain before classes, in order to do a better follow-up.
Problems classes In problem classes, students solve exercises about content presented during theory classes. These exercises are done in teams of two students according to a cooperative learning technique.

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:

Previous capacities

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