Logo UAB
2022/2023

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

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

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.       Access and security control (5 hours)

  • Discretionary access control (DAC).
  • Mandatory access control (MAC).
  • Role-based ace control (RBAC).

2.       Internal level in Oracle and PL/SQL (10 hours)

  • Internal representation of data: tablespaces and datafiles.
  • Concepts and system logs.
  • System logs in Oracle: ARCHIVELOG and NOARCHIVELOG
  • PL/SQL: basic structures, procedures, functions and triggers.

3.       DB Recovery (10 hours)

  • Recovery concepts.
  • Recovery techniques based on delayed and immediate update.
  • Shadow paging.
  • ARIES Algorithm.
  • Database backup and recovery against catastrophic failures.

4.       Transaction management and process  (5 hours)

  • ACID properties.
  • Transaction planning.
  • Classification of transactions based on serialization.

5.       Concurrency control protocols (25 hours)

  • Administration of SQL transactions.
  • Deadlock and starvation
  • Locking techniques: Granularity
  • Techniques based on temporary brands
  • Multiverse techniques

6.       Queries optimization (25 hours)

  • Indexation and hashing
  • Architecture.
  • Translation of SQL queries
  • Implementation of relational operators
  • Process in sequence
  • Heuristics of optimization

7.       No relational DBs: MongoDB  (10 hours)

  • Introduction to no relational databases
  • Introduction  to MongoDB: colections and documents
  • Basic concepts of MongoDB queries
  • Security in MongoDB: users and roles
  • Internal level in MongoDB: WiredTiger
  • Execution plan in MongoDB.

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 classroom methodology. In this methodology, the study of the theoretical contents must be carried out before the face-to-face sessions, as an autonomous activity of the students, and from material and documentation that students will have access through the virtual campus of the course. The face-to-face classes become practical sessions of problem solving and practices, aimed at solving all the doubts and problems that may have been encountered throughout the week. 

Two types of activities will be carried out: problem solving and practices. Problem solving is aimed at consolidating the most theoretical aspects of the course. A part of them will be individual while others will be carried out in groups. The practices will be essentially practical group activities that will be carried out throughout the course. 

The workinggroups 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 the group theory activities and the project. Not counting the hours that have to be dedicated to preparing the partial exams and the hours in the face-to-face classes. An average load of 6 autonomous works hours per week and student  has been calculated distributed in the following activities: 

  • Previous work: it is estimated an average of 2 hours per week that must be dedicated to reading or visualizing the material that will be worked on in the face-to-face sessions. 

  • Realization of the practices: it is estimated an average of 2 to 3 hours per week and studentwho must dedicate himself to doing the practices in addition to the hours that are dedicated punctually to the sessions dedicated to the resolution of problems. 

  • Problem solving: the weeks with less workload dedicated to the practices will be dedicated from 1 to 2 hours to make more resolutions of exercises and problems of the topics worked on in the face-to-face sessions.

Cross-disciplinary competences: 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.

 

Annotation: Within the schedule set by the centre or degree programme, 15 minutes of one class will be reserved for students to evaluate their lecturers and their courses or modules through questionnaires.

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 and administration skills of databases treated in the 1st part of the course. The second test (Par2) will be carried out at the end of the semester and will evaluate the theoretical concepts and management and administration skills of databases treated in the 2nd part of the course. 

Second chance exam: If the Theory Grade does not reach the pass, students will be able to take a second chance exam on all the contents covered in theory class. 

Most weeks there will be the possibility of submitting activities done during the week. The submission is optional and can be made up to hours set in advance at the Virtual Campus. With 80% of the highest possible grade you can achieve the highest grade (1 point) of this activity (NPrb). The grade will be obtained from cross-correction activities between students that are enabled for each submission.  

Cross-correction problems (CorPr) are also optional and can be done by students who have delivered the exercises. Students will be able to obtain a maximum of one point of this activity that will be added to the theory note provided that the minimum grade has been reached in all partial ones. The Theory Grade may under no circumstances exceed 10. 

The grade of practices will be the result of averaging the grades obtained in each practice submission. There will be 3 submissions: Prac1, Prac2 and Prac3. The maximum grade of the second chance practice submission will be 5. 

QUALIFICATION INDICATORS: 

The final grade of the subject is calculated as follows: 

  • Nota Parcial 1 = Par1  

  • Nota Parcial 2 = Par2  

  • Nota Teoria = 0'5*Par1 +0'5*Par2 + NPrb + CorPr 

  • Nota Pràctiques = 1/3*Prac1 + 1/3*Prac2 + 1/3*Prac3 

  • FINAL GRADE SUBJECT = 0'5 * Nota Teoria + 0'5 * Nota Pràctiques

EVALUATION CRITERIA 

  • To count the problem grades (NPrb) you have to obtain a minimum of 4 in the mean of the partials: = 0.5*Par1 +0'5*Par2 >= 4. 

  • In case of failing some of the practice submissions the maximum grade that can be obtained in the second chance submission is 5.   

  • To consider either of the two parts (theory and practice) passed, a minimum of 5 must be obtained. 

  • The course will be passed if the FINAL GRADE SUBJECT is greater than or equal to 5. 

  • In the case of not reaching the minimum required in any of the evaluation activities, the numerical grade will be the lowest value between 4.5 and the weighted average of the grades. 

  • NOT EVALUABLE: If you do not present any evaluation activity. 

  • REPEATERS: No passed part  (theory, practices) is kept from one academic year to another. 

  • IMPORTANT FOR ALL STUDENTS: It is important to register in the virtual campus of the course in the Caronte (http://caronte.uab.cat), because the materials, the activities and the final grades of the course are published there. 

  • Granting an honors grade is the decision of the faculty responsible for the course. The UAB regulations indicate that the MH can only be granted to students who have obtained a final grade equal to or greater than 9.00. Up to 5% MH of the total number of students enrolled can be awarded. 

EVALUATION CALENDAR:  

  • Partial Exams: schedule announced at the beginning of the semester. 

  • Second-chance Exam: according to the academic calendar of the Engineering School. 

  • Activities submissions: date and time fixed in advance to the Caronte. 

  • Practice submissions: 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 5, 4, 3
2nd Theory exam 0.25 2 0.08 2, 7, 8, 3
Problem delivery (NPrb) 0.1 2 0.08 2, 1, 6, 7, 8, 5, 4, 3
Project delivery 0.5 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:

Software

To do the project the following software is needed

  • Python ID:  Pycharm
  • ssh client
  • Use of code repository  (bitbucket)  and project management system  (Jira)