Logo UAB
2020/2021

Database Management and Administration

Code: 102741 ECTS Credits: 6
Degree Type Year Semester
2502441 Computer Engineering OB 3 1
2502441 Computer Engineering OT 4 1
The proposed teaching and assessment methodology that appear in the guide may be subject to changes as a result of the restrictions to face-to-face class attendance imposed by the health authorities.

Contact

Name:
Oriol Ramos Terrades
Email:
Oriol.Ramos@uab.cat

Use of Languages

Principal working language:
catalan (cat)
Some groups entirely in English:
No
Some groups entirely in Catalan:
Yes
Some groups entirely in Spanish:
No

External teachers

Carles Sánchez Ramos

Prerequisites

It is recommended that the student have the knowledge and skills of:

  • Characteristics, functionality and structure of Database systems.
  • Relational model of Databases and SQL language at query level.
  • To design a Database with the E/R model and build the associated relational model.

These concepts correspond to contents of the Database course.

Objectives and Contextualisation

In this course we introduce the advanced concepts of Databases (DB) necessary both at the designer level of BD and user.

KNOWLEDGE: At the end of the course the student must be able to:

  • To know, to understand and to know how to use the main DB management tools to be able to parameterize the BD in the most optimum way according to the needs of users and processes.
  • To know, to understand and to know how to use the main DB optimization tools.
  • To understand and to know how to configure distributed DBs.
  • To understand and to know how to use non-relational DB.

SKILLS: It is intended that students acquire the following abilities:

  • To set up a DB system in the most optimal way based on some needs.
  • To optimize the execution of transactions that maximizes the response time and the use of resources available to the DB.
  • To use the SQL language immersed to design, program and verify DB applications based on programming languages and SQL.
  • To use and configure a non-relational DB based on a DB design I/O.

To work with the previous skills with relational DBMS, such as ORACLE, which is widely used in the professional field, both at user level and administrator level and non-relational DBMS, such as MongoDB.

Competences

    Computer Engineering
  • Ability to develop, maintain and evaluate software services and systems that meet all user requirements and behave reliably and efficiently, are affordable to develop and maintain and meet quality standards, applying theories, principles, methods and practices of the Software engineering.
  • Acquire thinking habits.
  • Have the capacity to conceive, develop and maintain computer systems, services and applications employing the methods of software engineering as an instrument to ensure quality.

Learning Outcomes

  1. Apply different management tasks of DB in practic cases.
  2. Apply query scheduling for resource optimization.
  3. Develop a capacity for analysis, synthesis and prospection.
  4. Develop and maintain data models that serve as a basis for software systems.
  5. Know the limitations of different error recovery systems and understand the process involved in Rollback.
  6. Know the mechanisms of consultation and synchronization of nodes in distributed systems.
  7. Know the methods for optimising databases and the mechanisms of administration and parametrisation of the same.
  8. Know the bases of the paradigm of DDBB oriented to objects.

Content

  1. Object oriented and non-relational BD (10 hours)
    • Basic object-orientation concepts.
    • Identity and structure of an object.
    • Encapsulation of operations, methods and persistence.
    • Basic concepts of non-relational databases: MongoDB.
  2. Access and security control (10 hours)
    • Discretionary access control (DAC).
    • Mandatory access control (MAC).
    • Access control through roles (RBAC).
  3. Processing and processing of transactions (10 hours)
    • Concepts and registration of the system.
    • ACID properties.
    • Classification of transactions based on recoverability.
  4. Recovery of BD (20 hours)
    • Recovery concepts.
    • Recovery techniques based on delayed and immediate update.
    • Shadow paging.
    • ARIES Algorithm.
    • Database backup and recovery against catastrophic failures.
  5. Concurrency Control (30 hours)
    • Transaction planning.
    • Classification of transactions based on serialization.
    • Administration of SQL transactions.
    • Deadlock and starvation
    • Locking techniques: Granularity
    • Techniques based on temporary brands
    • Multiverse techniques
  6. Queries processing (20 hours)
    • Architecture.
    • Translation of SQL queries
    • Implementation of relational operators
    • Process in sequence
    • Heuristics of optimization
  7. Physical design (20 hours)
    • Internal representation of data.
    • Oracle: tablespaces and datafiles.
    • MongoDB: WiredTiger.
    • Indexing.
    • Hashing.
  8. Distributed BD (30 hours)
    • Basics
    • Techniques of fragmentation, replication and allocation of data
    • Types of distributed BD systems
    • Concurrence control
    • DDB in Oracle and MongoDB

* The hours in parentheses are estimates of the hours that the students will dedicate to each subject, counting the classroom hours in class and the hours outside the classroom.

Methodology

In this course we will follow a flipped class methodology. In this methodology, the study of the theoretical contents must be carried out before face-to-face sessions, as autonomous activities of the students, based on the material and documentation that students will have access through the virtual campus. In class, lectures become practice sessions for solving exercises and carrying out the project, aimed at solving all the doubts and problems that have been encountered throughout the week.

There will be two types of activities: theory and project activities. The theory activities will be aimed at consolidating the most theoretical aspects of the course. A part of them will be individual while others will be done in work groups. The project will be an essentially practical group activity that will be carried out throughout the course and will help to consolidate the theoretical aspects worked in class.

The work groups will be groups of 4 students, will be formed on the first day of class and will remain stable throughout the course. They will be the same for group theory activities and the project.

Not counting the hours that have to be devoted to prepare the partial exams. An average load of 9 hours per week and student distributed in the following activities has been calculated:

  • Previous work: an average of 2 hours per week is estimated to be devoted to reading or visualizing the material that will be used in face-to-face sessions.
  • Project work: it is estimated an average of 3 hours per week/member of the group to do the project in addition to the hours dedicated in class to problems solving and project.
  • Problem solving and project: weeks with less project workload will be devoted to solve more exercises and problems of the topics worked in the face-to-face sessions.

Cross-disciplinarycompetences: In this course the competence T01.02 must be worked out - Develop the capacity for analysis, synthesis and prospecting. This will be done throughout the course in all the activities that must be done but with more intensity in the resolution of problems and project.

Activities

Title Hours ECTS Learning Outcomes
Type: Directed      
Inclass sessions 26 1.04 7, 3
Type: Supervised      
Problem solving and project 24 0.96 2, 1, 7, 3
Type: Autonomous      
Previuos work 26 1.04 2, 1, 6, 7, 8, 5, 4, 3
Study and preparation of partial tests 20 0.8 6, 7, 8, 5, 3
project developement 39 1.56 2, 1, 7, 4, 3

Assessment

The evaluation will be carried out continuously. There will be two individual theoretical-practical tests in writing, with a weight of 50% each on the final grade. The first test (Par1) will be done approximately in the middle of the semester and will evaluate the theoretical concepts and management skills and administration of databases treated in the 1st part of the course. The second test (Par2) will be done at the end of the semester and will evaluate the theoretical concepts and management skills and database administration discussed in the 2nd part of the course.

Second chance exam: In case that the mean of both theoretical-practical exams does not reach the minimal mark to pass, students will have a second-chance exam on all the contents explained during the course.

Most of the weeks there will be the possibility to submit activities done during the week. The submission is optional and can be done up to fixed deadline set in advance in the Virtual Campus. With 50% of the maximum possible score students will be able to achieve the maximum score (1 point) of this activity (NPrb). The score will be obtained from cross-correction activities between students.

Cross-correction problems (CorPr) are also optional and will be made by students who already have submitted the exercises. Students will be able to obtain a maximum of one point in this activity that will be added to the theory score whenever the minimum score has been obtained in all the partial ones.

The score of the project will be obtained from the average of the obtained score in each part. There will be 3 parts: Prj1, Prj2 and Prj3. The score of each part will be computed from a group score and an individual score. Each project part must be passed separately. In the case of failing any of the them, the student will have a second-chance to pass that part. In that case, a 5 will be the maximum possible score for the failed part. 

QUALIFICATION INDICATORS:

The final grade ofthe subject is calculated as follows:

  • Partial Score 1 = Par1
  • Partial Score 2 = Par2
  • Theory Score = 0'5 * Par1 + 0'5 * Par2 + NPrb + CorPr
  • Project Score = 1/3 * Prj1 + 1/3 * Prj2 + 1/3 * Prj3
  • FINAL GRADE = 0'5 * Theory Score + 0'5 * Project Score

EVALUATION CRITERIA

  • In order to take into account the problems score (NPrb) a minimum of 4 is required from the mean of the partial exams: = 0'5 * Par1 + 0'5 * Par2> = 4.
  • In order to take into account the project grades (Prj) a score higher or equal to 5 must be obtained in all the project parts.
  • In the case of failing any project part, the maximum possible score for the failed part in the second-chance submission is 5.
  • In order to pass either of the two parts (theory and project) a minimum of 5 must be obtained in each part.
  • The course will be passed if the FINAL GRADE is greater than or equal to 5.
  • In case of not reaching the minimum required in any of the assessment activities, the numerical score of the course will be the lowest value between 4.5 and the weighted average of scores.
  • NON-APPRAISING: If you do not deliver any evaluation activity.
  • REPEATING STUDENT: No passed part (theory, project) is kept from one academic year to another.
  • IMPORTANT FOR ALL STUDENTS: It is important to enroll in the virtual campus of the course at Caronte (http://caronte.uab.cat), because the materials of the course, the activities and the final grades of the course are published there.
  • Granting an honorific matriculation qualification is a decision of the faculty responsible for the course. The regulations of the UAB indicate that MH can only be awarded to students who have obtained a final grade of 9.00 or more. It can be granted up to 5% of MH of the total number of students enrolled.

EVALUATION CALENDAR:

  • Partial Exams: schedule announced at the beginning of the semester.
  • Second-chance Exam: according to the academic calendar of the Engineering School.
  • Deliveries of the activities: date and time fixed in advance to the Caronte.
  • Deliveries of the project: date and time fixed in advance to the Caronte.

The delivery dates will be published in Caronte (http://caronte.uab.cat) and may change due to be adapted to any unexpected incidents. Any change will also be announced through Caronte since it is the usual communication channel between teachers and students.

For each assessment activity, a place, date and time of revision will be indicated in which the student will be able to review the activity with the teacher. In this context, claims can be made about the activity score, which will be evaluated by the teachers responsible for the subject. If the student does not submit to this review, this activity will not be reviewed later.

Note about plagiarism:

Without prejudice to other disciplinary measures deemed appropriate, and in accordance with the current academic regulations, irregularities committed by a student who may lead to a variation of the qualification in an assessable activity will be graded with zero (0). Assessment activities qualified in this way and by this procedure will not be recoverable. If it is necessary to pass any of these assessment activities to pass the subject, this subject will be suspended directly, without opportunity to recover it in the same course. These irregularities include, among others:

  • the total or partial copy of a lab exercise, report, or any other evaluation activity;
  • let another student to copy;
  • present a group work not done entirely by the members of the group (applied to all members and not only to those who have not worked);
  • present as own materials prepared by a third party, even if they are translations or adaptations, and generally works with non-original and exclusive elements of the student;
  • have communication devices (such as mobile phones, smart watches, pens with camera, etc.) accessible during theoretical-practical assessment tests (individual exams);
  • talk with classmates during the individual theoretical-practice tests (exams);
  • copy or attempt to copy from other students during the theoretical-practical assessment tests (exams);
  • use or attempt to use written material related to the subject during the theoretical-practical evaluation tests (exams), when these have not been explicitly allowed. 

the numerical official grade will be the lowest value between 3.5 and the average weighted grades in case the student has committed irregularities in an evaluation act (and therefore the subject cannot be passed by compensation). In future editions of this subject, the student who has committed irregularities in an evaluation act will not be validated any of the assessment activities carried out.

In summary: copy, let copy or plagiarize (or attempt) in any of the assessment activities will lead to a FAIL, not compensable and without validations of parts of the subject in subsequent courses.

Assessment Activities

Title Weighting Hours ECTS Learning Outcomes
1st Theory exam 0.25 2 0.08 6, 7, 8, 5, 3
2nd Theory exam 0.25 2 0.08 2, 1, 7, 8, 3
Problem delivery (NPrb) 0.1 2 0.08 2, 1, 6, 7, 8, 5, 4, 3
Project delivery 0.4 6 0.24 2, 1, 7, 4, 3
peer correction exercises (CorPr) 0.05 3 0.12 2, 1, 8, 3

Bibliography

COURS MATERIAL: http://caronte.uab.cat.

BASIC BIBLIOGRAFY:

  • Avi Silberschatz, Henry F. Korth, S. Sudarshan, Database System Concepts Sixth Edition, McGraw-Hill.
  • Elmasri/Navathe, Fundamentos de Sistemas de Bases de Datos, Addison-Wesley, 5a edición, 2007.
  • Ramakrishmnan, Gehrke, Sistemas de Gestión de bases de Datos, 3a edición, McGraw-Hill, 2006.

COMPLEMENTARY BIBLIOGRAFY:

  • A. Silberschatz, H.F. Korth, S. Sudarshan, Fundamentos de Bases de Datos, 5a edición, McGraw-Hill, 2006.
  • T.M. Connolly, C.E. Begg, Sistemas de Bases de Datos, 4a edición, Pearson-Addison-Wesley, 2005.
  • P.Rob, C. Coronel, Sistemas de Bases de datos. Diseño, implementación y administración, Thomson-Paraninfo, 2004.
  • J. Hernández, M. José Ramírez, C. Ferri, Introducción a la Minería de Datos, Pearson-prentice Hall, 2005.
  • M. Celma, J.C. Casamayor, L. Mota, Bases de Datos Relacionales, Pearson-Prentice Hall, 2003.
  • D.M. Kroenke, Procesamiento de Bases de Datos, 8ª edición, Pearson-Prentice Hall, 2003.
  • M. Marqués, J.I. Aliaga, S. García, G. Quintana, SQL y desarrollo de aplicaciones en ORACLE 8, Col.lecció; "Treball d'Informàtica i Tecnologia, 9, Universitat Jaume I, 2001.
  • G.W. Hansen, J.V. Hansen, Diseño y administración de Bases de Datos, 2a edición, Prentice Hall, 1997.
  • C.J. Date, H. Darwen, A Guide to the SQL standart, 3rd edition, Addison-Wesley, 1994.
  • C.J. Date, Introducción a los sistemas de Bases de Datos, Vol.1, 7a edición, Prentice Hall, 2001.

WEB LINKS:

RELATIONAL & NO RELATIONAL DATABASES: