Database Administration

You are here

Credits
6
Types
Specialization complementary (Information Systems)
Requirements
  • Prerequisite: BD
  • This subject can not be enroled if you passed: DBD
Department
ESSI
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.4
Autonomous learning
5.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 - 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. To know the tasks, the available documentation, and tools and principles 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.
    Related competences: CT2.2, CSI4.2,
  3. Be able to design and obtain information from multidimensional databases.
    Related competences: CT2.2, CSI2.6, CSI4.3, CSI4.2,
    Subcompetences:
    • Being able to get the query corresponding to a multidimensional statistical tables using the SQL-99 specific buildings.
    • Being able to move a UML diagram in a multidimensional relational schema star-join.
    • Being able to convert a sequence of algebraic operations on multidimensional SQL query as simple as possible.
  4. Being able to analyze and choose the best mechanism to collect integrity constraints in the database.
    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. Being able to decide the rates to be defined in terms of expected operations.
    Related competences: CT2.2, CSI2.6, CSI4.3, CT7.2, CSI4.2,
    Subcompetences:
    • Being able to reproduce the execution of each operation on each index.
    • List the types of indexes that can be defined and the cost of each operation (insertion, deletion, query single query more than one) in each index.
  7. Being able to list the existing options regarding policies, methods and time optimization along with the advantages and disadvantages of each application conditions.
    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:
    • Being able to estimate the size of intermediate and final results of a query and the cost of resolving each step.
    • Being able to optimize a syntax tree.
  9. Being able to reproduce the implementation of the algorithms involved in a process tree and estimate their cost.
    Related competences: CT2.2, CSI4.3, CSI4.2,
  10. Being able to list the main parameters and options that affect concurrency and justify the effect of modifying the value.
    Related competences: CT2.2, CSI4.3, CSI4.2,
    Subcompetences:
    • Being able to play the concurrent execution of transactions depending on the level of aïlament.
  11. Being able to list the main options and parameters that affect the recovery and justify the effect of modifying the value.
    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.
    • Detecting satisfiability problems, vitality, and redundancy and abastabilitat recognize containment queries.
    • Being able to decide on the possibility of aggregation of data.
  14. Being able to detect and solve data integration problems.
    Related competences: CT2.2, CSI4.2,
  15. To be able to participate with a proactive atitude in making exercices in teams of 2 or more students, according to the roles assigned to each student that can change during the execution of the exercises.
    Related competences: G8.3,
  16. Through presentations of experts, understand the importance of a good professional realization.
    Related competences: G8.3,

Contents

  1. Introduction
    Concepts related to the administration of databases. Administrator tasks and principles that should guide their work.
  2. Database Reverse Engineering
    Conceptual schema generation from logical schema.
  3. Design orrectness
    Normalization. Validation. Conditions for aggregation.
  4. Data integration
    Problem of heterogeneous data. Models of information integration.
  5. Data Warehouses
    Multidimensional databases and OLAP tools.
  6. Physical design
    Materialized views. Integrity constraints.
  7. Indexes
    B+. Cluster. Multiattribute. Bitmaps.
  8. Algorithms
    Selection. Projection. Ordering. Join.
  9. Optimization
    Stages of optimization. Estimated volumes of data. Optimization based on costs. Access plan.
  10. Concurrency control
    Transaccions and Isolation levels.
  11. Files and system parameters
    File and parameter types.
  12. Security
    Access control.
  13. Recovery
    Recovery types. Backup types.
  14. Beyond the relational model
    NoSQL databases.

Activities

Activity Evaluation act


Presentation

Students must prepare your computer by any of the proposed alternatives in order to perform activities that are expected of him during the course. Basically, you must have access to Moodle and Oracle.

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

Study of introductory concepts

Study of the contents explained and proposed questions for the exam using Moodle glossary.
Objectives: 1 15
Contents:
Theory
2h
Problems
0h
Laboratory
0h
Guided learning
0h
Autonomous learning
2h

Session 1: Basic SQL Queries

The students, in pairs, must answer a Moodle questionnaire that is instantly corrected through LEARN-SQL. Whenever students send the answer a question receive a rating. Students may decide to send new answers to try to improve the above. Each shipment is a new penalty. Students will also have to answer some questions in writing. The teacher corrected written questions and review the grade obtained by LEARN-SQL. During the week before the test, students will have a Moodle questionnaire resolved remotely the same features with the same partner.
Objectives: 1
Week: 2
Type: lab exam
Theory
0h
Problems
0h
Laboratory
2h
Guided learning
0h
Autonomous learning
2h

Study of concepts related to database reverse engineering

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

Study of concepts relating to the correctness of the design

Study of the contents explained and proposed questions for the exam using Moodle glossary.
Objectives: 4 13 15
Contents:
Theory
2h
Problems
0h
Laboratory
0h
Guided learning
0h
Autonomous learning
5h

Study of concepts related to data warehouses

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

Study of concepts related to data integration

Study of the contents explained and proposed questions for the exam using Moodle glossary.
Objectives: 14 15
Contents:
Theory
2h
Problems
0h
Laboratory
0h
Guided learning
0h
Autonomous learning
5h

Session 2: Advanced SQL queries

The students, in pairs, must answer a Moodle questionnaire that is instantly corrected through LEARN-SQL. Whenever students send the answer a question receive a rating. Students may decide to send new answers to try to improve the above. Each shipment is a new penalty. Students will also have to answer some questions in writing. The teacher corrected written questions and review the grade obtained by LEARN-SQL. During the week before the test, students will have a Moodle questionnaire resolved remotely the same features with the same partner.
Objectives: 2 1
Week: 3
Type: lab exam
Theory
0h
Problems
0h
Laboratory
2h
Guided learning
0h
Autonomous learning
2h

Study of the concepts of physical design

Study of the contents explained and proposed questions for the exam using Moodle glossary.
Objectives: 5 13 15
Contents:
Theory
2h
Problems
0h
Laboratory
0h
Guided learning
0h
Autonomous learning
5h

Session 3: Reverse engineering and database normalization.

The students, in pairs, have to solve a problem. Students will also have to answer some written questions. The teacher will correct all the tests.
Objectives: 2 15
Week: 4
Type: lab exam
Theory
0h
Problems
0h
Laboratory
2h
Guided learning
0h
Autonomous learning
2h

Study of concepts related to indexes

Study of the contents explained and proposed questions for the exam using Moodle glossary.
Objectives: 6 15
Contents:
Theory
2h
Problems
0h
Laboratory
0h
Guided learning
0h
Autonomous learning
2h

Study of concepts related to optimization

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

Session 4: Design correctness

The students, in pairs, have to solve a problem. Students will also have to answer some written questions. The teacher will correct all the tests.
Objectives: 13
Week: 5
Type: lab exam
Theory
0h
Problems
0h
Laboratory
2h
Guided learning
0h
Autonomous learning
2h

Study of the concepts of algorithms

Study of the contents explained and proposed questions for the exam using Moodle glossary.
Objectives: 9
Contents:
Theory
2h
Problems
0h
Laboratory
0h
Guided learning
0h
Autonomous learning
5h

Session 5: OLAP

The students, in pairs, have to solve a problem. Students will also have to answer some written questions. The teacher will correct all the tests.
Objectives: 3
Week: 6
Type: lab exam
Theory
0h
Problems
0h
Laboratory
2h
Guided learning
0h
Autonomous learning
2h

Session 6: Materialized views

The students, in pairs, have to solve a problem. Students will also have to answer some written questions. The teacher will correct all the tests.
Objectives: 5
Week: 8
Type: lab exam
Theory
0h
Problems
0h
Laboratory
2h
Guided learning
0h
Autonomous learning
2h

Session 7: Indexes

The students, in pairs, have to solve a problem. Students will also have to answer some written questions. The teacher will correct all the tests.
Objectives: 6
Week: 9
Type: lab exam
Theory
0h
Problems
0h
Laboratory
2h
Guided learning
0h
Autonomous learning
2h

Session 8: Costs of selections

The students, in pairs, have to solve a problem. Students will also have to answer some written questions. The teacher will correct all the tests.
Objectives: 9
Week: 10
Type: lab exam
Theory
0h
Problems
0h
Laboratory
2h
Guided learning
0h
Autonomous learning
2h

Study of concepts relating to transactions

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

Session 9: Costs of queries with joins

The students, in pairs, have to solve a problem. Students will also have to answer some written questions. The teacher will correct all the tests.
Objectives: 8 9
Week: 11
Type: lab exam
Theory
0h
Problems
0h
Laboratory
2h
Guided learning
0h
Autonomous learning
2h

Session 10: Optimization according to workload

The students, in pairs, have to solve a problem. Students will also have to answer some written questions. The teacher will correct all the tests.
Objectives: 6 8 9
Week: 12
Type: lab exam
Theory
0h
Problems
0h
Laboratory
2h
Guided learning
0h
Autonomous learning
2h

Exam

The exam will consist of two sets of questions (a set proposed by the teacher and another set proposed by students during the course). Students involved with the intensity required in the proposed questions can choose questions from both sets. The other students can only answer the set of questions proposed by the teacher.
Objectives: 3 2 1 6 7 8 10 11 12
Week: 15
Type: problems exam
Theory
0h
Problems
2h
Laboratory
0h
Guided learning
0h
Autonomous learning
2h

Review of laboratory tests

Students who need, can ask explanations or present assessment claims following established guidelines.

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

Exam review

Students who need, can ask explanations or present assessment claims following established guidelines.

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

Study of concepts relating to NoSQL databases

Study of the contents explained and self-study materials. Resolution of exercises and proposed questions for the exam using Moodle glossary.
Objectives: 1 15
Theory
2h
Problems
0h
Laboratory
0h
Guided learning
0h
Autonomous learning
5h

Session 11: Concurrency

The students, in pairs, have to solve a problem. Students will also have to answer some written questions. The teacher will correct all the tests.
Objectives: 10
Week: 13
Type: lab exam
Theory
0h
Problems
0h
Laboratory
2h
Guided learning
0h
Autonomous learning
0h

Session 12: Files and Settings

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

Session 13: NoSQL Databases

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

DBA presentation

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

Teaching methodology

In theory hours, the teacher explains the concepts relating to any of the content. The student must work these concepts and optionally suggests possible exam questions through the campus. Some concepts of some contents are not exposed by the professor but students must work materials that the teacher be published in the virtual campus.

In the lab hours, the teacher opens a questionnaire to campus or proposed to solve a problem that students must solve pairs.

Evaluation methodology

The final grade = 40% L+ 40% E+ 20% P

L = average of 11 laboratory tests
E = final exam
C = peer evaluation
P = average of 11 written problems

Exam: students will have (or not) the right to choose that answer questions based on their participation in the activity during the semester to propose exam questions.

The grade of the competency will be: (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

Being able to list the steps that make the process of software engineering.
Being able to understand UML conceptual schemas.
Being able to create, access and manipulate databases with SQL and relational algebra.