Database Administration

You are here

Home » Studies » Bachelor's degrees » Bachelor Degree in Informatics Engineering » Curriculum » Syllabus » Database Administration
Credits
6
Types
Specialization complementary (Information Systems)
Requirements
  • Prerequisite: BD
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

  • Carme Martin Escofet ( )

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

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

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

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

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

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

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

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

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

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

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

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

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.