Logo UAB

Database Analysis and Design

Code: 102186 ECTS Credits: 6
2025/2026
Degree Type Year
Business and Information Technology OB 2

Contact

Name:
Carlos Alejandro Parraga
Email:
carlosalejandro.parraga@uab.cat

Teaching groups languages

You can view this information at the end of this document.


Prerequisites

There are no previous requirements for this subject


Objectives and Contextualisation

In this course we will introduce basic concepts of relational databases (DB), which are necessary both for users and designers.

Knowledge:

At the end of the course the student will be capable of:

  • Knowing and understanding the important technological jump represented by database systems regarding the treatment of information as well as the design and maintenance of data manipulation applications.
  • Knowing and understanding the architecture of database systems, the functions of each module and those of the personnel involved in its creation and day-to-day running (users, programmers, administrators, etc.)
  • Comprehending the design methodology of a database
  • Understanding the entity-relationship model (E-R model)
  • Knowing and understanding the properties of the relational model, as implemented in most database engines.
  • Knowing and understanding the SQL language, the relational database standard language

Abilities:

At the end of the course the student will develop the following abilities:

  • Ability to design the E-R model of a database from a series of real-world specifications
  • Ability to convert the E-R model into the set of attributes and relationships that compose a real relational database
  • Ability to use the integrity rules of the relational model to be able to fill the database with data and guarantee its consistency and general robustness.
  • Ability to make simple and relatively complex queries to the database using SQL language.
  • Ability to work with a typical DB engine such as Oracle (whose use is very extended in the professional world)

 

 


Learning Outcomes

  1. CM13 (Competence) Propose relational databases that collect the characteristics, functionalities and structure appropriate to the organisation.
  2. SM09 (Skill) Design databases adapted to the operational needs of organisations.

Content

. Introduction and basic concepts

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

2. Architecture

  • ANSI-SPARC architecture
  • DBA and SGBD
  • Back-end/front-end architecture

3. Design. The Entity-Relationship (E-R) model

  • Design of a database
  • Entity-Relationship model
  • Extended Entity-Relationship model
  • The design criteria of an E-R schema
  • E-R schematic design

4. The relational model of databases

  • Introduction
  • Database structure
  • Integrity rules
  • Database manipulation: relational algebra and SQL queries

5. The design of a database

  • Design stages of a database
  • Requirements’ capture and analysis
  • Conceptual design of a database
  • Logical design
  • Physical design
  • Normalization

6. Internal level

  • Physical database access
  • Storage structures: Indexing, Hashing (dispersion)
  • Compression techniques

Activities and Methodology

Title Hours ECTS Learning Outcomes
Type: Directed      
Practical work 10 0.4 CM13, SM09, CM13
Theory and problem-solving lessons 36 1.44 CM13, SM09, CM13
Type: Supervised      
Tutorials 15 0.6 CM13, SM09, CM13
Type: Autonomous      
Creation of a database and its corresponding report 25 1 CM13, SM09, CM13
Exercise preparation and problem resolution 15 0.6 CM13, SM09, CM13
Practical session preparation 5 0.2 CM13, SM09, CM13
Study 36 1.44 CM13, SM09, CM13

The learning process will be based in three kinds of activities: (a) theory lessons and problems, (b) practical sessions, and (c) the creation of a database with its corresponding technical report. All the documentation and the necessary materials for following the course will be available from the course online platform (http://caronte.uab.es)

FORMATIVE ACTIVITIES LED BY THE TEACHER

  • Theory and Problems:

Theory classes will be imparted on the screen using electronic support and whiteboard. We will introduce all the necessary theoretical concepts to be able to solve the problems that will appear along the course. During the problem time we will solve some exercises in class and encourage students to do the rest autonomously, using the database and exemplary solutions. Problem lessons will be divided in two blocks: (a) relational database design and (b) database query.

(a) In the first problem group, we will practice the E-R design following some specific practical requirements.

(b) In the second problem group we will explain the basic concepts of Relational Algebra (RA) and Structured Query Language (SQL) over an exemplary database. We will query this database in both RA and SQL with increasing levels of complexity. The idea is to use the concepts of RA to understand the logic behind SQL querying with emphasizing that RA could be of much use in complex queries. We will provide the results of the queries for the students to verify their results.

  • Lab practice:

In lab practical sessions, we will introduce the student to the practical side of Structured Query Language (SQL) language, which is the standard language to query relational databases.

The course will include 5 practical sessions in a closed regime, i.e. each session will consist of a seriesof exercises on a database with the objective of comprehending the concepts explained in previous theory lessons. The general idea is also to familiarize the student withthe practical side ofdesigning, creating and manipulatingrelational databases as users and programmers. 

The assistance to practical sessions IS COMPULSORY. Students should prepare the work explained in the practical coursework. This preparation must occur before the corresponding practical session. The work ends by assisting to the practical sessions tutorised by the teacher.

Practical coursework material and corresponding materials can be found in the corresponding documents on Caronte.

AUTONOMOUS FORMATIVE ACTIVITIES

  • Creation of a database with its corresponding technical report:

In parallel with the theory, problem-solving, and practical sessions, all students will be required to develop a database. The project will involve writing a technical report and creating the necessary scripts for building the database, as well as a test suite for its validation. The work will be carried out in groups of 4 or 5 students and will be largely autonomous, under the supervision of the instructor during office hours.

  • Self-assessed practical SQL work:

Alongside theory lessons, problems and practical sessions, all students should participate in self-assessed partical work consisting in the resolution of SQL problems online. This work will be done individually by students at the time they consider most convinient within the dynamic of the course. The statements of these SQL problems, alongside theinstructions on how to complete them and evaluate them will be available through the document platfom Caronte well in advance. 

SUPERVISED FORMATIVE ACTIVITIES

  • Tutorials:

These can be group-based or one-to-one based depending on the subject to discuss. The objective of these tutorials is to solve doubts and consolidate the knowledge acquired during the week. The tutorial timetable will be availabla at the begining of the course, but the student should tell the teacher (preferentlyby e-mail) his/her intentions to assist to a tutorial well in advance.

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.


Assessment

Continous Assessment Activities

Title Weighting Hours ECTS Learning Outcomes
Practical work evaluation 20% 2 0.08 CM13, SM09
SQL queries test 10% 2 0.08 CM13, SM09
Technical report 20% 0.5 0.02 CM13, SM09
1st Partial Exam 20% 1.5 0.06 CM13, SM09
2nd Partial Exam 30% 2 0.08 CM13, SM09

EVALUATION CRITERIA

The course evaluation consists of five parts: Partial Evaluation 1, Partial Evaluation 2, a technical report, practical assessment, and independent resolution of SQL queries.

  1. THEORY AND PROBLEM ASSESSMENTS (Nota_Parcial1 and Nota_Parcial2). Two individual written tests will be conducted, contributing 20% and 30% respectively to the final grade.

    • First partial test (Nota_Parcial1): a mid-semester test assessing basic concepts and skills for database design (E-R model).
    • Second partial test (Nota_Parcial2): an end-of-semester test covering all course content, with emphasis on topics not included in the first test.

  2. PRACTICAL ASSESSMENT (Nota_Pract)Worth 20%, this will be conducted through an SQL query test during the final practical session. Students can view their grade after the test. The format will be similar to the autonomous learning module.

  3. INDIVIDUAL WRITTEN TESTS (Nota_PEI). The three previous grades are combined to calculate the score for individual written tests:

    Nota_PEI = (0.2 * Nota_Parcial1 + 0.3 * Nota_Parcial2 + 0.2 * Nota_Pract) / 0.7

  4. AUTONOMOUS WORK (NotaInfTec and NotaAutoAval)

    • Technical Report (Nota_InfTec): Worth 20%, includes four phases of DB design (requirements, ER diagram, logical table model, and implementation/testing). Must be submitted in the required format and be legible. If any diagram is unreadable, that part will be failed. Submitted in two phases: mid-semester and end of semester. Final grade:

      Nota_IT_ = màx [(nota1 + nota2) / 2; 0,7 * nota2].

    • Independent SQL Query Resolution (Nota_AutoAval): Worth 10%, done outside class hours via an online module. Grade depends on the number and difficulty of queries solved.

FINAL GRADE (Nota_Final). The final grade is the weighted mean of all grades.

Nota_Final =  0,2 * Nota_Parcial1 + 0,3 * Nota_Parcial2 + 0,2 * Nota_Pract + 0,2 * Nota_InfTec + 0,1 * Nota_AutoAval

To calculate this final grade, each component must be greater than zero, and the score of the written tests (Nota_PEImust be ≥ 4.5.

To pass the course, the final grade (Nota_Final) must be ≥ 5. Students who do not complete any evaluation activity will be marked as “not assessable.”

ATTENTION: Due to the update of Article 266, point 4, of the Academic Regulations in July 2023, if a student has a Final Grade of 4.9, this grade is automatically converted to a 5 according to the regulations.

 

RE-EVALUATION CRITERIA

Students who did not pass may be eligible for re-evaluation if:

  • (A) They meet the conditions to calculate the final grade, with a total score between 3.5 and 5, and Nota_PEI ≥ 4.5.
  • (B) They scored below 4.5 in Nota_PEI, but the weighted final grade would be ≥ 5.

The re-evaluation test (Nota_reeval), written and held in the final week, covers the entire course. The new final grade is the average of Nota_reeval and Nota_PEI. If this average is ≥ 5, the student passes.

The re-evaluation date is setinthe Faculty’s exam calendar.

To participate in re-evaluation, students must have completed at least two-thirds of the total course assessments.

If not eligible, the final grade will be the lower of Nota_PEI or 4.5.

SINGLE ASSESSMENT MODALITY

This subject/module does not offer the option for single assessment modality (comprehensive evaluation).

 

OTHER IMPORTANT INFORMATION

  • CARONTE REGISTRATION: Mandatory at the start of the course via http://caronte.uab.cat. This platform hosts course materials, practice submissions, and final grades. Students must upload personal data and a passport-style photo (JPG). This data is confidential and deleted after the course ends.
  • REPEATING STUDENTS: No special treatment is given.
  • HONORS: Students with a final grade above 9 may receive an honors distinction (MH), limited to 5% of enrolled students. If more than 5% qualify, MH is awarded to the highest scorers.
  • PLAGIARISM: Any dishonest behavior (copying, plagiarism, cheating, allowing others to copy etc.) will result in a zero for the activity, which cannot be recovered. If the activity is essential to pass the course, the student will fail the course outright. In the case of the technical report, all involved groups will receive a zero, regardless of authorship.
  • AI USAGE: The use of AI technologies is strictly prohibited in all phases of the course. Any work containing AI-generated content will be penalized and may result in a zero or more severe sanctions. 
  • SINGLE EVALUATION: This subject/module does not provide for the single assessment modality.

Evaluation Calendar

Dates for assessments (exams, exercises, submissions, etc.) will be announced in advance during the semester. The final exam date is set in the Faculty’s exam calendar.

Assessmentdates cannot be changed unless there is a justified exceptional reason. In such cases, a new date will be proposed in consultationwith faculty and affected students.

Students of the Faculty of Economics and Business, who in accordance with the previous paragraph need to change an evaluation activity datemust process the request by filling out an Application for exams' reschedule
https://eformularis.uab.cat/group/deganat_feie/application-for-exams-reschedule

Grade Review Procedure

The date and method for publishing final grades will be announced with the final exam. Information on how to request a review, including time and place, will also be provided according to university regulations.

Irregularities in evaluation activities

In spite of other disciplinary measures deemed appropriate, and in accordance with current academic regulations, "in the case that the student makes any irregularity that could lead to a significant variation in the grade of an evaluation activity, it will be graded with a 0, regardless of the disciplinary process that can be instructed. In case of various irregularities occur in the evaluation of the same subject, the final grade of this subject will be 0". Section 10 of Article 116. Results of the evaluation. (UAB Academic Regulations).


Bibliography

Basic Bibliography

  • A. Silberschatz, H.F. Korth, S. Sudarshan, Fundamentos de Bases de Datos, 5a edición, McGraw-Hill, 2006. Permanent link
  • A. Silberschatz, H.F. Korth & S. Sudarshan, Database system concepts (7th ed., International ed. ed.). New York, N.Y.: McGraw-Hill, 2018. Permanent link
  • C.J. Date, Introducción a los sistemas de Bases de Datos, Vol.1, 7a edición, Prentice Hall, 2001. Permanent link
  • C.J. Date, An introduction to database systems (8th ed.). Boston, Mass.; London: Pearson/Addison-Wesley, 2004. Permanent link

Complementary Bibliography

  • A. Fowler, NOSQL for Dummies, For Dummies; 1 edition, 2015. Permanent link
  • Gaurav Vaish, Getting Started with NoSQL, Packt Publishing, 2013. Permanent link
  • C.A. Coronel & S.A. Morris, Database systems: design, implementation, and management (13 ed.): Course Technology, 2018. Permanent link
  • T.M. Connolly & C.E. Begg, Database systems: a practical approach to design, implementation, and management (6th ed. ed.). Boston, MA: Pearson Education, 2014. Permanent link
  • P. Rob, C. Coronel, Sistemas de Bases de datos. Diseño, implementación y administración, Thomson-Paraninfo, 2004. Permanent link
  • M. Celma, J.C. Casamayor, L. Mota, Bases de Datos Relacionales, Pearson-Prentice Hall, 2003. Permanent link
  • D.M. Kroenke, Procesamiento de Bases de Datos, 8ª edición, Pearson-Prentice Hall, 2003. Permanent link
  • 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. . Permanent link
  • C.J. Date, H. Darwen, A Guide to the SQL standart, 3rd edition, Addison-Wesley, 1994.

Web links


Software


Groups and Languages

Please note that this information is provisional until 30 November 2025. You can check it through this link. To consult the language you will need to enter the CODE of the subject.

Name Group Language Semester Turn
(PAUL) Classroom practices 201 Catalan/Spanish first semester morning-mixed
(PAUL) Classroom practices 202 Catalan/Spanish first semester morning-mixed
(PLAB) Practical laboratories 201 Catalan/Spanish first semester morning-mixed
(PLAB) Practical laboratories 202 Catalan/Spanish first semester morning-mixed
(PLAB) Practical laboratories 203 Catalan/Spanish first semester morning-mixed
(PLAB) Practical laboratories 204 Catalan/Spanish first semester morning-mixed
(TE) Theory 20 Catalan/Spanish first semester morning-mixed