Introduction to Databases

Credits
6
Types
Compulsory
Requirements
This subject has not requirements , but it has got previous capacities
Department
ESSI
Web
https://learnsql2.fib.upc.edu/moodle/course/view.php?id=81
The student 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

Others

Weekly hours

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

Competences

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 [Avaluable] - 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.

    • Technical Competences

    Especifics

  • CE04 - To design and use efficiently the most appropriate data types and structures to solve a problem.
  • CE08 - To detect the characteristics, functionalities and components of data managers, which allow the adequate use of them in information flows, and the design, analysis and implementation of applications based on them.

    • Generic Technical Competences

    Generic

  • CG1 - To ideate, draft, organize, plan and develop projects in the field of artificial intelligence.
  • CG2 - To use the fundamental knowledge and solid work methodologies acquired during the studies to adapt to the new technological scenarios of the future.

    • Objectives

    1. To know the objectives of a database management system and their architecture.
      Related competences: CT6, CE04, CE08,
    2. To understand the database relational model, their languages (SQL and relational algebra) and the usual components of a relational database.
      Related competences: CT4, CT6, CB2, CB3, CE04, CE08,
    3. To be able to define, create and manipulate usual relational database components.
      Related competences: CT4, CT6, CB2, CB3, CE04, CE08,
    4. To be able to build programs to manage relational databases.
      Related competences: CT4, CT6, CB2, CB3, CE04, CE08,
    5. To know the different available formats for semistructured data, and know how to write SQL queries over them.
      Related competences: CG1, CG2, CT4, CT6, CB2, CB3, CE04, CE08,
    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: CT4, CT6, CB2, CB3, CE04, CE08,
    7. 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: CT6, CB2, CB3, CE04, CE08,
    8. To have a general vision of how the design of a database should be included in a software development process.
      Related competences: CT6, CE04, CE08,
    9. To be able to obtain a database relational model starting from a conceptual models in UML.
      Related competences: CT6, CE04, CE08,
    10. To know the concept of database transaction and its implications.
      Related competences: CT6, CE04, CE08,
    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
      Related competences: CT6, CE04, CE08,
    12. To know the locking concurrency control technique.
      Related competences: CT6, CE04, CE08,
    13. To know the possible physical structures for storing data and its implications for in terms of efficiency.
      Related competences: CG1, CG2, CT6, CE04, CE08,
    14. To know the access methods to data and its implications in terms of efficiency.
      Related competences: CG1, CG2, CT6, CE04, CE08,
    15. 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: CT6, CE04,

    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. Semistructured data formats and SQL extensions to query them
      Introduction to the different semi-structured data formats. SQL extensions to query semi-structured data.
    6. SQL Programming
      Programming in Python and DataFrames. Considerations and quality criteria in the design and implementation of programs that access databases.
    7. Transactions and concurrency
      Concept of transaction. ACID properties of transactions. Interference between transactions. Serialitzability. Recoverability. Concurrency control techniques. Isolation Levels. Locking and isolation levels.
    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. 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: 15 1
    Contents:
    Theory
    2h
    Problems
    0h
    Laboratory
    2h
    Guided learning
    0h
    Autonomous learning
    4h

    Study of the databases introduction


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

    Study of the data logical components


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

    Study of the introduction to design of relational databases


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

    Study of transactions and concurrency


    Objectives: 10 11 12
    Contents:
    Theory
    4h
    Problems
    0h
    Laboratory
    0h
    Guided learning
    0h
    Autonomous learning
    6h

    Study of storage, access methods and optimization


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

    Study of the Relational Algebra and SQL


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

    Study of semistructured data models and SQL extensions to query them


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

    Study of stored procedures and triggers


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

    Programming with SQL - Python and DataFrames


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

    Final exam


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

    Reviews and resolution of doubts about the exams



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

    Midterm exam


    Objectives: 15 1 2 3 5 9
    Week: 9
    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.
    Evaluation. In four of the problem classes, students will solve an exercise that will be collected and evaluated by the teacher.

    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.
    Assessment: 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.

    Evaluation methodology

    The grade of the course is based on technical competencies:

    - NPR: Problems grade. It is the average of the grades of the four problems exam.

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

    - NEP - Partial exam grade.

    - NEF: Final exam grade.

    Final grade = Maximum (NLB*0.2+NEP*0.30+NEF*0.35+NPR*0.15,NLB*0,2+NEP*0.35+NEF*0,45)

    - For students who can take the re-assessment, the re-assessment exam mark will replace NEF and NEP. In any case, the maximum reassessment score can only be 7.

    Bibliography

    Basic

    Complementary

    Web links

    Previous capacities

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