Database Administration

Credits
6
Types
Specialization complementary (Information Systems)
Requirements
Department
ESSI
Web
https://learnsql2.fib.upc.edu/moodle/
The Database Administrator (ABD) is a professional who not only has a high technical knowledge of design and maintenance of databases, but also adds business value to the information system. Is responsible for the conceptual and physical design, security and authorization, availability and data recovery and adequate performance of the database. This course aims to train not only technically future database administrator but also convey the importance of this figure for the continuity of business of the organizations, as responsible of the efficient and complete data availability.

Teachers

Person in charge

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.
  • CT7 - To evaluate and select hardware and software production platforms for executing applications and computer services.
    • CT7.2 - To evaluate hardware/software systems in function of a determined criteria of quality.

    • Technical Competences of each Specialization

    Information systems specialization

  • CSI2 - To integrate solutions of Information and Communication Technologies, and business processes to satisfy the information needs of the organizations, allowing them to achieve their objectives effectively.
    • CSI2.6 - To demonstrate knowledge and capacity to apply decision support and business intelligence systems.
  • CSI4 - To participate actively in the specification, design, implementation and maintenance of the information and communication systems.
    • CSI4.3 - To administrate databases (CES1.6).
    • CSI4.2 - To participate actively in the design, implementation and maintenance of the information and communication systems.

    • 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.3 - To be motivated for the professional development, to face new challenges and the continuous improvement. To have capacity to work in situations with a lack of information.

    • Objectives

    1. Be able to understand the tasks, available documentation, tools and principles of action of the database administrator.
      Related competences: CSI4.3, CSI4.2,
    2. Being able to get the conceptual schema of a database of an information system from the physical schema, theoretically and in practice with reverse engineering tools.
      Related competences: CT2.2, CSI4.2,
    3. Be able to understand the operation of a corporate information factory and obtain information from multidimensional databases.
      Related competences: CT2.2, CSI2.6, CSI4.3, CSI4.2,
      Subcompetences
      • Be able to use an ETL and generate reports.
      • Be able to get metadata from an organization.
      • Be able to design and use a multidimensional UML schema.
    4. Be able to prepare documentation for a physical design.
      Related competences: CT2.2, CSI4.3, CT7.2, CSI4.2,
    5. Being able to decide which materialized views must be defined according to the expected operations.
      Related competences: CT2.2, CSI2.6, CSI4.3, CT7.2, CSI4.2,
    6. Be able to decide the most appropriate indexes for each situation.
      Related competences: CT2.2, CSI4.3, CT7.2, CSI4.2,
      Subcompetences
      • Know all types of indexes.
      • Be able to calculate the cost of each index.
    7. Be able to perform semantic, syntactic and physical optimization on a theoretical level. On a practical level, be able to use specialized tuning tools.
      Related competences: CT2.2, CSI4.3, CT7.2, CSI4.2,
    8. Be able to get access plan for a query according to criteria of optimization.
      Related competences: CT2.2, CSI4.3, CSI4.2,
      Subcompetences
      • Be able to interpret the cost of an operation and decide how to improve it.
    9. Be able to understand the algorithms involved in an access plan.
      Related competences: CT2.2, CSI4.3, CSI4.2,
    10. Be able to reproduce the concurrent execution of transactions depending on the level of isolation.
      Related competences: CT2.2, CSI4.3, CSI4.2,
    11. Being able to list the main options and parameters that affect the recovery.
      Related competences: CT2.2, CSI4.3, CSI4.2,
    12. Being able to manage the security of the database, and specifically access control.
      Related competences: CSI4.3, CT7.2,
    13. Being able to detect and correct faults in a logic design.
      Related competences: CT2.2, CSI4.3, CT7.2, CSI4.2,
      Subcompetences
      • Being able to establish what is the logical schema normal form of an information system operational and normalize it (or undo normalization) to the level required.
    14. Being able to detect and solve data integration problems.
      Related competences: CT2.2, CSI4.2,
    15. Be able to choose the parameter values needed for the most appropriate database administration for each situation.
      Related competences: CT2.2, CSI4.3, CT7.2, CSI4.2,
    16. Be able to know the main types of NOSQL databases.
      Related competences: CT2.2, CSI4.3, CT7.2,
    17. Through presentations of experts, understand the importance of a good professional realization.
      Related competences: G8.3,
    18. Be able to participate with a proactive attitude in carrying out exercises in teams of 2 or more, following the assigned roles, which change in different exercises.
      Related competences: G8.3,

    Contents

    1. The importance of data in the information system
      The importance of databases in the information system. Main tasks of the database administrator and documentation necessary for administration. The DAMA guide to data management.
    2. Data in an information system: Correctness, normalization and improvement through data reengineering
      Presentation of the main design pitfalls to be validated to guarantee the correctness of the design. Explanation of the 5 normal forms and the BCNF, as an additional validation tool. Generation of the conceptual scheme from the logical scheme. Types of foreign key patterns. Use of a specific tool of a DBMS to carry out reverse engineering.
    3. Distributed databases
      Characteristics, classification and main architectures of DBMSs. Problem of data heterogeneity. Information integration models.
    4. The corporate information factory and its strategic factor
      Data warehouses within the corporate information factory. The integration and transformation component: ETL. Metadata. Multidimensional model and operations. Data Warehouse Connections with Business Intelligence
    5. Analysis and improvement of data performance
      Characteristics of physical design. Adjustments and improvements. Performance management. Physical design documentation. File types and settings required for database administration. Examples to choose the most appropriate values for each situation.
    6. Key factors to choose the best access plan
      The B index and its insertion and deletion algorithms. Static and dynamic hashing. Cluster index. Multi-attribute index. Bitmap. Criteria for choosing the appropriate index for each case. Main sorting and combination algorithms. The access plan for any SQL statement. Definition and purpose of materialized views.
    7. Administration and 'Tuning' of databases
      Introduction to query processing. Semantic, syntactic and physical optimization. Database tuning with a specific DBMS administration tool.
    8. Concurrency in databases
      The transaction manager, the concurrency manager, and the data manager. Transactions, Interferences. Insulation levels. Basic and advanced incompatibilities of the reserve-based concurrency control technique. Use of multiple levels of granularity.
    9. Security and recovery in databases
      Definition of security. Consequences of loss of security and basic security mechanisms. Recovery techniques. Modalities of the recovery manager and examples of the different modalities.
    10. Administration of post-relational databases
      Main types of post-relational databases. Knowing more about a NOSQL database of a particular type.

    Activities

    Activity Evaluation act


    Presentation and SQL review

    Students prepare the connection to the Oracle database that will be used throughout the semester. Become familiar with LEARSQL. Solve some basic queries using the SQL language.
    Objectives: 1 18
    Contents:
    Theory
    0h
    Problems
    0h
    Laboratory
    2h
    Guided learning
    0h
    Autonomous learning
    0h

    The experience of an administrator

    Study of the contents. Answer a questionnaire.
    Objectives: 1 17
    Contents:
    Theory
    0h
    Problems
    0h
    Laboratory
    0h
    Guided learning
    0h
    Autonomous learning
    2h

    Study of the importance of data in the information system

    Understand the importance of data for the information system. Know the main tasks of the database administrator and the necessary documentation for administration. Perform the transformation from a conceptual design to logical design.
    Objectives: 1 18
    Contents:
    Theory
    2h
    Problems
    0h
    Laboratory
    0h
    Guided learning
    0h
    Autonomous learning
    4h

    Session 1: SQL and Relational Algebra Queries

    Students, in pairs, must answer a SQL and Relational Algebra Query Moodle Quiz that is instantly corrected via LEARNSQL. Each time students submit the answer to a question they receive a grade. Students may decide to submit new answers to try to improve on the previous ones. Each new submission is a penalty, but you get the best grade.
    Objectives: 1 18
    Week: 2
    Theory
    0h
    Problems
    0h
    Laboratory
    0h
    Guided learning
    0h
    Autonomous learning
    0h

    Study of data in an information system: Database reengineering as a validation and improvement tool

    Study of the contents explained: generation of the conceptual scheme from the logical scheme and types of foreign key patterns. and self-study materials. Perform database reengineering to move from a logical model to a conceptual model.
    Objectives: 2 18
    Contents:
    Theory
    2h
    Problems
    0h
    Laboratory
    0h
    Guided learning
    0h
    Autonomous learning
    4h

    Session 2: Advanced SQL queries

    The students, in pairs, must answer a Moodle quiz of SQL queries and Real Algebra that is instantly corrected through LEARNSQL. Each time the student body submits an answer to a question, they receive a grade. Students can decide to send new answers to try to improve the previous ones. Each new submission incurs a penalty, but eventually you get the best grade.
    Objectives: 1 18
    Week: 3
    Theory
    0h
    Problems
    0h
    Laboratory
    0h
    Guided learning
    0h
    Autonomous learning
    0h

    Study of data in a information system: Correctness and standardization

    Study of the main design traps to be validated to guarantee the correctness of the design and of the 5 normal forms and the BCNF, as an additional validation tool. Realization of correctness and normalization exercises.
    Objectives: 13 18
    Contents:
    Theory
    2h
    Problems
    0h
    Laboratory
    0h
    Guided learning
    0h
    Autonomous learning
    4h

    Session 3: Reverse engineering

    Students, in pairs, have to solve a data reengineering problem using the tools of a DBMS.
    Objectives: 2 18
    Week: 4
    Theory
    0h
    Problems
    0h
    Laboratory
    0h
    Guided learning
    0h
    Autonomous learning
    0h

    Study of concepts of distributed databases

    Study of the characteristics, classification and main architectures of DBMSs. Problem of data heterogeneity. Information integration models. Performing distributed database exercises.
    Objectives: 14 18
    Contents:
    Theory
    2h
    Problems
    0h
    Laboratory
    0h
    Guided learning
    0h
    Autonomous learning
    4h

    Session 4: Design correctness

    Els estudiants, per parelles, han de resoldre un problema. Els estudiants també hauran de contestar algunes preguntes per escrit. El professor corregirà totes les proves.
    Objectives: 13 18
    Week: 5
    Theory
    0h
    Problems
    0h
    Laboratory
    0h
    Guided learning
    0h
    Autonomous learning
    0h

    The strategic factor of the corporate information factory

    Study of data warehouses within the corporate information factory. The integration and transformation component: ETL. Metadata. Multidimensional design and operations. Connections of data warehouses with business intelligence. Completion of exercises on this topic.
    Objectives: 3 18
    Contents:
    Theory
    2h
    Problems
    0h
    Laboratory
    0h
    Guided learning
    0h
    Autonomous learning
    4h

    Session 5: Multidimensional queries

    Students, in pairs, must solve a problem of multidimensional queries, using the tools of a DBMS.
    Objectives: 3 18
    Week: 6
    Theory
    0h
    Problems
    0h
    Laboratory
    0h
    Guided learning
    0h
    Autonomous learning
    0h

    Study of the concepts of physical design

    Study of adjustments and improvements. Characteristics of physical design. Performance management. Physical design documentation. Completion of exercises on this topic.
    Objectives: 4 18
    Contents:
    Theory
    2h
    Problems
    0h
    Laboratory
    0h
    Guided learning
    0h
    Autonomous learning
    4h

    Session 6: Algorithms

    Students, in pairs, have to solve an algorithm problem using the tools of a DBMS.
    Objectives: 9 18
    Week: 7
    Theory
    0h
    Problems
    0h
    Laboratory
    0h
    Guided learning
    0h
    Autonomous learning
    0h

    DBA presentation

    Preparation of questions and writing a report.
    Objectives: 1 17
    Contents:
    Theory
    2h
    Problems
    0h
    Laboratory
    0h
    Guided learning
    0h
    Autonomous learning
    2h

    Session 7: Materialized views

    Students, in pairs, must solve a problem of materialized views using the tools of a DBMS.
    Objectives: 5 18
    Week: 8
    Theory
    0h
    Problems
    0h
    Laboratory
    0h
    Guided learning
    0h
    Autonomous learning
    0h

    Study of concepts related to indexes

    Study of B+ trees and their insertion and deletion algorithms. Static and dynamic hash. Cluster index. Multi-attribute index. Bitmap. Criteria for choosing the appropriate index for each case. Performing access method exercises.
    Objectives: 6 18
    Contents:
    Theory
    2h
    Problems
    0h
    Laboratory
    0h
    Guided learning
    0h
    Autonomous learning
    4h

    Session 8: Indexes

    Students, in pairs, must solve an index problem using the tools of a DBMS.
    Objectives: 6 18
    Week: 9
    Theory
    0h
    Problems
    0h
    Laboratory
    0h
    Guided learning
    0h
    Autonomous learning
    0h

    Study of the concepts of security and recovery

    Study of the contents explained and self-study materials. Resolution of exercises and proposed questions for the exam using Moodle glossary.
    Objectives: 11 12 18
    Contents:
    Theory
    2h
    Problems
    0h
    Laboratory
    0h
    Guided learning
    0h
    Autonomous learning
    4h

    Session 9: System parameters

    Study of the contents. Answer a questionnaire.
    Objectives: 15
    Week: 10
    Theory
    0h
    Problems
    0h
    Laboratory
    0h
    Guided learning
    0h
    Autonomous learning
    0h

    Study of concepts related to optimization

    SStudy of query processing. Semantic, syntactic and physical optimization.
    Objectives: 7 8 18
    Contents:
    Theory
    2h
    Problems
    0h
    Laboratory
    0h
    Guided learning
    0h
    Autonomous learning
    4h

    Session 10: Optimization according to workload

    Tuning databases with a specific administration tool for a DBMS.
    Objectives: 7 8 18
    Week: 11
    Theory
    0h
    Problems
    0h
    Laboratory
    0h
    Guided learning
    0h
    Autonomous learning
    0h

    Study of concepts relating to transactions

    Study of the transaction manager, the concurrency manager and the data manager. Transactions, Interferences. Isolation levels. Basic and advanced incompatibilities of the reserve-based concurrency control technique. Use of multiple levels of granularity. Completion of exercises on this topic.
    Objectives: 10 18
    Contents:
    Theory
    2h
    Problems
    0h
    Laboratory
    0h
    Guided learning
    0h
    Autonomous learning
    4h

    Session 11: Concurrency

    Students, in pairs, must solve a concurrency problem using the tools of a DBMS.
    Objectives: 10 18
    Week: 12
    Theory
    0h
    Problems
    0h
    Laboratory
    0h
    Guided learning
    0h
    Autonomous learning
    0h

    Study of concepts relating to NoSQL databases

    Study of the main types of NOSQL databases. Completion of exercises on this topic.
    Objectives: 18 16
    Contents:
    Theory
    2h
    Problems
    0h
    Laboratory
    0h
    Guided learning
    0h
    Autonomous learning
    4h

    Session 12: NOSQL Databases

    Students, in pairs, must solve a problem using a NOSQL DBMS.
    Objectives: 18 16
    Week: 13
    Theory
    0h
    Problems
    0h
    Laboratory
    0h
    Guided learning
    0h
    Autonomous learning
    0h

    Important concepts of data use and processing in the information system

    Study of the contents. Answer a questionnaire.
    Objectives: 3 1 17
    Contents:
    Theory
    2h
    Problems
    0h
    Laboratory
    0h
    Guided learning
    0h
    Autonomous learning
    2h

    Agile software and database development

    Study of the contents. Answer a questionnaire.
    Objectives: 1 17
    Contents:
    Theory
    0h
    Problems
    0h
    Laboratory
    2h
    Guided learning
    0h
    Autonomous learning
    2h

    Advanced Oracle Concepts

    Study of the contents. Answer a questionnaire.
    Objectives: 1 17
    Contents:
    Theory
    2h
    Problems
    0h
    Laboratory
    0h
    Guided learning
    0h
    Autonomous learning
    2h

    Advanced database security issues

    Study of the contents. Answer a questionnaire.
    Objectives: 12 17
    Contents:
    Theory
    0h
    Problems
    0h
    Laboratory
    2h
    Guided learning
    0h
    Autonomous learning
    2h

    Exam

    The exam will consist of approximately 10 questions of all the contents of the subject.
    Objectives: 3 2 1 4 5 6 7 8 9 10 11 12 13 14 15 16
    Week: 15 (Outside class hours)
    Theory
    0h
    Problems
    0h
    Laboratory
    0h
    Guided learning
    0h
    Autonomous learning
    0h

    Teaching methodology

    During theory hours, the teacher explains the concepts corresponding to some of the content. Students must work on these concepts and solve, using cooperative learning, a problem that arises. Some concepts of some contents are not presented by the teacher, but the students must work on materials that the teacher will have published on the virtual campus.

    During laboratory hours, the teacher proposes a problem to the students to solve in pairs.

    Evaluation methodology

    The final grade = 30% L+ 50% E+ 20% P (to opt for this option attendance is required) or 100% E

    L = Average of the top11 laboratory tests
    E = Final exam grade
    P = Average of the top 11 problem deliveries

    The grade of the competency will be: A (competence passed with excellence), B (competition surpassed the desired level), C (competition surpassed a level sufficient) or D (competition unbeaten).

    The grade of the generic competency "Appropriate attitude towards work " will be decided according to the notes of the cooperative learning activities carried out and the peer evaluation.

    Peer evaluation: students will have multiple partners during the semester and evaluate them. Based on these assessments, the teacher assigned the note.

    Bibliography

    Basic

    Complementary

    Web links

    Previous capacities

    Be able to understand conceptual schemes in UML and transform into a Relational model.
    Be able to create, consult and manipulate databases with SQL and Relational Algebra.