Logo UAB
2021/2022

Databases

Code: 102744 ECTS Credits: 6
Degree Type Year Semester
2502441 Computer Engineering OB 2 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:
Debora Gil Resina
Email:
Debora.Gil@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

Teachers

Oriol Ramos Terrades
Carles Sanchez Ramos

Prerequisites

It is recommended that students have knowledge and skills:
• Programming in third-generation languages (C, Pascal, Basic, etc.).
• Basic data structures.
These concepts correspond to the contents of the subjects:
• Fundamentals of informatics
• Programming Methodology

Objectives and Contextualisation

This course introduces the basic concepts of databases (BD) necessary so much to level of design of BD and of user.


KNOWLEDGE: At the end of the course, students must be able to:

  • Knowledge and understanding of the important technological leap that represent the systems of databases regarding computer information processing, as well as in the design and maintenance of information processing applications.
  • To know the architecture of the database Systems (BD), the functions of each module and the staff working on these systems (users, programmers and DB administrators). The architecture is studied from a local or remote point of view.
  • To know the entity/Relationship model (E/R), widely used in database design.
  • Study the properties of the BD relational model, widespread in most BD engines.
  • To know the SQL language standard in relational BD.
  • Understanding the BD design methodology, including standardization techniques of a relational BD.
  • Know the main data structures that are used in relational BD, such as indexing and hashing functions.


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

  • Use the rules of integrity of the BD relational model, as well as be able to formulate any query to a BD through relational algebra.
  • Perform simple queries and of a certain complexity to a BD through SQL.
  • Designing a BD in the E-R Model based on real-world specifications.
  • Convert the BD into the E-R Model to a set of relationships and attributes of a relational BD, applying standardisation techniques.
  • Working with an example of BD engine as is ORACLE, widely used in the professional field, both user and administrator.

Competences

  • Acquire personal work habits.
  • Capacity to design, develop, evaluate and ensure the accessibility, ergonomics, usability and security of computer systems, services and applications, as well as of the information that they manage.
  • Know and apply the characteristics, functionalities and structure of Databases for their suitable use and the design, analysis and implementation of applications based on these.
  • Know and apply the functional and structural characteristics of distributed systems and computer and Internet networks, and design and implement applications based on these.
  • Know and apply the necessary tools for the storage of, processing of and access to information, including web based tools.

Learning Outcomes

  1. Critically evaluate the work done.
  2. Know and apply the client server model and service oriented architectures.
  3. Know and understand the important role of Database systems in terms of the treatment of the information in a computer, as well as the design and maintenance of applications for treating this information.
  4. Know and understand the important role representing systems databases regarding the processing of information in a computer , as well as the design and application maintenance treatment information.
  5. Know the architecture of Database systems, the functions of each module and the staff that work on these systems (DB users, programmers and administrators) from a local and remote point of view.
  6. Know the entity–relationship model and the properties of the DB relational model.
  7. Know the main data structures used in relational DB.
  8. Make one’s own decisions.
  9. Manage time and resources available. Work in an organized manner .
  10. Understand and use the SQL language.
  11. Understand data standards and their persistence.
  12. Understand the entity–relationship model and the properties of the DB relational model.

Content

BLOCK 1. DATA BASES PARADIGMS


1. Introduction. Basics

  • Introduction and Definitions
  • Components of a database system
  • Historical evolution
  • Advantages and disadvantages of a database system


2. Architecture

  • ANSI-SPARC Architecture
  • DBA and DBMS
  • Architecture back-end/front-end


BLOCK 2. RELATIONAL DATABASES


3. Relational data Model

  • Introduction
  • Data structure
  • Rules of Integrity
  • Data manipulation


BLOCK 3. MODEL ENTITY-RELATIONSHIP


4. The Entity-relationship model

  • Design of a database
  • Model Entity-Relationship
  • E-R Extended Model
  • Design criteria of an E-R schema
  • Designing an E-R schema


BLOCK 4. RELATIONAL DATABASE DESIGN


5. Design of a database

  • BD Design Phases
  • Recruitment and analysis of requirements
  • Conceptual design of the BD
  • Logical design
  • Physical design


6. Normalization

  • Normalization theory
  • Functional dependencies
  • Normal ways of Codd (1NF, 2NF, 3NF)
  • Normal form of Boyce-Codd (BCNF)
  • Standardisation process


BLOCK 5. FILE ACCESS


7. Internal level

  • Access to the physical database. Files
  • Storage structures
  • Indexing
  • Hashing (dispersion)
  • Compression techniques

Methodology

The final objective of the course is that students are able to design and manipulate relational databases in the context of current computer applications. For this reason, the classroom sessions will be highly practical and will focus on the students to consolidate the knowledge that is objective of learning this subject.

The general methodology of the course can be divided into three activities:


PREVIOUS PREPARATION. The aim is that the Alumnado can learn the concepts that will work in the next session through diverse activities proposed by the Profesorado like it can be the viewing of videos, the reading of texts, etc. All the necessary material (enunciated, BD scripts, problem results) will be available in the documentary Manager Caronte (http://caronte.uab.cat).
CLASSROOM. The objective is to consolidate the concepts seen and put them into value within the context of the subject. The teacher will ensure that students delve into these concepts through exercises (more or less) guided during the session. For this reason the classroom sessions will take place in 2 weekly 2-hour meetings each in classrooms with of oridnadors and connection to the servant. The distribution will be based on problem groups. The attendance to the classroom is not COMPULSORY, but RECOMMENDABLE.

The management of the classes that due to the pandemic are made on-line will be through teams https://teams.microsoft.com/, any change throughout the course will be notified Caronte.


AUTONOMOUS LEARNING. Two activities are proposed: case use in the design of a real database including requirements analysis, design and self-learning of typical SQL queries.

  • In the first activity will be presented to the student a real case of design of BD for the student to perform all phases of design. At the end of the course students will have to submit a technical report that will be evaluated.  Throughout the course we will keep track of the various stages of the technical report to some problem sessions and tutoring hours. In addition, a resource will be enabled in Caronte to be able to send partial deliveries and obtain the teacher's feed-back. This activity will be carried out in groups of 5 people who must register via Caronte. In order to monitor the problem sessions, all students in a group will have to Debelong to the same group of problems.
  • The second activity will consist of an autoaprendizaje of SQL queries using a module of Autoevaluación available in Caronte. The student will upload the consultations to Caronte in a specific format to be evaluated his result.

The practices and problems statements are available on the Web page in PF and Caronte (http://caronte.uab.cat).
Deliveries of autonomous learning work will be done via Caronte.


Transversal competences:
The competencies T 02.03-manage the time and resources available. Work in an organised way, T 02.05-make own decisions and T 02.08-critically evaluate the work carried out to are, both, worked in the activity of the design of a BD in which the student will work in a group of 5 people to make a technical report on the development of a database from scratch 

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      
Classroom explanations (on-line due to COVID) 50 2 11, 12, 6, 2, 3, 4, 10, 5, 7
Type: Supervised      
Use Case Technical Report 34 1.36 1, 12, 6, 10, 9, 8
Type: Autonomous      
Previous Work 35 1.4 12, 6, 10, 7
SQL On-line Queries 17 0.68 10, 7, 9

Assessment

The course will be evaluated in 3 aspects: Theory and problems, practices and self-learning activities.  The mark of theory/problems will be obtained by means of an individual examination and the one of practices by means of the individual delivery of some exercises in the last session of practices. The mark of the autonomous work will obtain delivering a series of exercises along the course and will consist of 2 activities: Technical report (20%) and SQL queries (10%).


The final mark will be the weighted average of the grade obtained in each activity:


FINAL MARK= 0 ' 4 * Theory exam Mark + 0 ' 3 * Practical exercises + 0 ' 2 * Technical report Mark + 0 ' 1 * autonomous work Mark

In order to make the weighted average, it is necessary to pass the theory exam and practice exercises with a 5.  The remaining activities (technical report and autonomous work) do not require minimum mark to do average. In case of not reaching the minimum required in any of the evaluation activities the final grade in the student record will be  the minimum between the final mark (obtained from the weighted average) and  4 ' 5. The subject is considered passed if the final grade is equal to or exceeded 5.

EVALUATION CRITERIA

Theory exam. There will be two individual written examinations, weighing 40% each on the final grade. The first test will be held approximately in the middle of the semester and the second Test will be held at the end of the semester during the exams period.
The final grade of the theory will be the average of the 2 partial notes, if the note of each partial exceeds 4. Otherwise, the theory part will be considered suspended and the student will have to take a recuperation of the part suspended.  In the case of not reaching the minimum for one of the exams, the theory grade will be the minimum between the averageand a 4'5.

Practice exercises. The practices assessment is done by delivering a test of SQL queries carried out in the laboratory integrated during the examination period, and where the student will have access to his notes. The format will be the same as the autonomous learning module.

SQL queries module.  Autonomous learning activities are available throughout the course. Consist of several modules of SQL queries available in Caronte that are open certain periods of time specified in the own module. Each query correctly resolved Púntua 1 (sencilles) or 2 (subqueries) points. The point will be obtained at the time the student has answered consultations worth 75% of the maximum score to be achieved if all the modules had been answered correctly.

Technical report of a use case. The technical report consists of 4 parts corresponding to the 4 phases of the design of a BD: requirements, Diagram ER, table logic Model and implementation/testing game. The document must be properly formatted because the design ER and table logical Model diagrams can be read correctly. If a diagram can not be read with clarity for reasons of resolution or quality, this part of the report will be suspended, as well as those that depend on it not being evaluated. The report has 2 deliveries. To suspend the first, you can get back to 5 in the second installment.

Continuous Assessment: The continuous assessment process includes all these activities: technical report (recoverable supervised group activity), SQL queries module (supervised indivdual activity) and theory and Problems exam (synthesis test Recoverable

Theory revision exam: In case the note of some individual theory exam is less than 4, or the overall average does not reach 5, students will have to undertake a revision exam to pass the part of theory and problems they have failed.

Practice exercicies revision: In case that in thepractice exercises is not reached the 5, the student will have a new opportunity with the same format and norms.

IMPORTANT FOR ALL STUDENTS: It is important to register in Caronte (http://caronte.uab.cat) at the beginning of the course, as the materials of the subject are published, the practical deliveries are made and the final grades of the course are published. To register in Careonte in the subject of databases 1, you must give your personal data and a passport photo in JPG format.

NON-ASSESSABLE: A student is considered non-assessable (NA) only if he has not done any assessment activity. Recall that a non-assessable (NA) also consumes registration.

There is no special treatment for students who repeat the subject.

Pass with Honor: Students who have more than a 9.5 in the final grade will have a distinction of honour (MH) until reaching the limit of 5% of the enrolled holders. In case of having more than 5% of the students over the 9.5 will have MH those that have the highest notes.

EVALUATION CALENDAR:
• Theory and problem Examinations: Theory hours announced in advance.
• Recovery exam: Depending on the academic calendar of the school of Engineering.
• Practice exercises: During the period of the partial seconds, in laboratory practicals and announced in advance.
• Rehabilitation of practices: During the period of theory recovery exams, in laboratory practices and announced in advance.
• Autonomous work: the theory and/or problems will be communicated in advance either via Caronte or in lectures.

The dates of continuous assessment and delivery of work will be published in Caronte and may be subject to programming changes for reasons of adaptation to possible incidents. Caronte will always be informed about these changes as it is understood that this is the usual platform for exchanging information between teachers and students.
For each evaluation activity, a place, date and time of revision will be indicated in which the student will be able to revise the activity with the teacher. In this context, it will be possible to make claims about the activity mark, which will be evaluated by the teachers responsible for the course. If the student is not present for this revision, this activity will not be reviewed later.


ACADEMIC IRREGULARITIES. Notwithstanding other disciplinary measures deemed appropriate, and in accordance with the academic
regulations in force, assessment activities will receive a zero whenever a student commits academic
irregularities that may alter such assessment. Assessment activities graded in this way and by this procedure
will not be re-assessable. If passing the assessment activity or activities in question is required to pass the
subject, the awarding of a zero for disciplinary measures will also entail a direct fail for the subject, with no
opportunity to re-assess this in the same academic year. Irregularities contemplated in this procedure include,
among others:


• allowing others to copy;
• presenting group work that has not been done entirely by the members of the group;
• presenting any materials prepared by a third party as one’s own work, even if these materials are
translations or adaptations, including work that is not original or exclusively that of the student;
• having communication devices (such as mobile phones, smart watches, etc.) accessible during
theoretical-practical assessment tests (individual exams).

In case of not passing the subject because one of the evaluation activities does not reach the minimum note, the numerical grade of the file will be the lowest value between 4.5 and the weighted average of the notes. A "non-assessable" grade cannot be assigned
to students who have participated in any of the individual partial tests or the final exam.

In short: copying, leaving copying orplagiating in any of the evaluation activities equals a FAIL with a note less than 3.0.

Assessment Activities

Title Weighting Hours ECTS Learning Outcomes
Individual pratical exercices 30% 2 0.08 6, 10, 7
Individual final exams of theory and exercicies 40% 4 0.16 11, 12, 6, 2, 3, 4, 10, 5, 7
Individual partial exams of theory and exercicies 40% 4 0.16 11, 12, 6, 2, 3, 4, 10, 5, 7
SQL On-line Queries 10% 1.3 0.05 10, 7, 9
Use Case Technical Report 20% 2.7 0.11 1, 12, 6, 10, 9, 8

Bibliography

COURSE MATERIAL: Caronte http://caronte.uab.cat

BASIC REFERENCES:

  • A. Silberschatz, H.F. Korth, S. Sudarshan, Fundamentos de Bases de Datos, 5a edición, McGraw-Hill, 2006.
  • C.J. Date, Introducción a los sistemas de Bases de Datos, Vol.1, 7a edición, Prentice Hall, 2001.

EXTRA REFERENCES:

  • 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.
  • 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.
  • Elmasri/Navathe, Sistemas de Bases de Datos, Addison-Wesley, 3a edición, 2000.
  • A. Silberschatz, H.F. Korth, S. Sudarshan, Fundamentos de Bases de Datos, 3a edición, McGraw-Hill, 1998.
  • A. de Miguel, M. Piattini, Diseño y uso de Bases de Datos Relacionales, Ra-Ma, 1997.
  • 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.

WEB SITES:

MULTIUSER DATA BASES:

Software

SQL- developer, Data Modeller, Oracle (for local BD)