Logo UAB

Database Analysis and Design

Code: 102186 ECTS Credits: 6
2024/2025
Degree Type Year
2501232 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 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
Theory and problem-solving lessons 36 1.44 CM13, SM09
Type: Supervised      
Tutorials 15 0.6 CM13
Type: Autonomous      
Creation of a database and its corresponding report 25 1 CM13, SM09
Exercise preparation and problem resolution 15 0.6 CM13, SM09
Practical session preparation 5 0.2 CM13
Study 36 1.44 CM13, SM09

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 series of 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. Practical work will be assessed and evaluated after every practical session.

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 teach at the time agreed.

Practical coursework material, timetables for presentations and evaluations will be publicised in Caronte (http://caronte.uab.es). We will make all possible efforts to allow the students to complete the practical work at home, although in some cases this might not be possible, and these should be completed using university computers.

AUTONOMOUS FORMATIVE ACTIVITIES

Creation of a database with its corresponding technical report:
Alongside theory lessons, problems and practical lessons, all students should participate in the creation of a database. This process will be complete by submitting a technical report, the scripts necessary for the task, and a test set for database validation. This will be accomplished in groups of 4 or 5 students whose work will be mostly autonomous under the supervision of the teacher in tutorial time.

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 (preferently by 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
E-R design problems evaluation 20% 1.5 0.06 CM13, SM09
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
Theory evaluation 30% 2 0.08 CM13, SM09

EVALUATION CRITERIA

The evaluation of the course consists of 5 parts: theory evaluation, evaluation of E-R design problems, writing a technical report, evaluation of practices and autonomous resolution of SQL queries.

  • E-R DESIGN PROBLEMS EVALUATION and THEORY EVALUATION (Nota_Probl and Nota_Teoria). There will be two written individual exams, with a weight of 20% and 30% in the final grade respectively. The first will be conducted approximately in the middle of the semester and will evaluate the basic concepts and skills acquired for the design of a database (E-R model). The second test will be held at the end of the semester and will evaluate the theoretical concepts of the entire course.

  • PRACTICAL WORK EVALUATION (Nota_Pract). The evaluation of practical work (with a 20% weight) will be carried out by means of a series of SQL queries in the last practical session where the student will be able to check his mark at the end of the test. The format will be the same as in the autonomous learning module.

  • WRITTEN INDIVIDUAL TESTS (Nota_PEI): The above three grades will be averaged to calculate the written individual tests mark:

    Nota_PEI = (0.3 * Nota_Teoria + 0.2 * Nota_Probl + 0.2 * Nota_Pract) / 0.7

  • AUTONOMOUS WORK (Nota_InfTec and Nota_AutoAval). It will be divided into two blocks: technical report (20%) and autonomous SQL query resolution (10%). To score in the first one, the students need to write a technical report consisting of 4 parts (corresponding to the 4 phases of DB design: requirements, E-R diagram, logical table model and implementation/test dataset. The document must be delivered in the previously defined format and that must allow a correct reading and interpretation of the diagrams. If a diagram cannot be read clearly for reasons of resolution or quality, this part of the report will be failed, as well as those that depend on it since they cannot be evaluated. The technical report will be delivered in two parts, in the middle and end of the semester. The final grade of the technical report will be calculated from both notes (note1 and note2) as follows:

    Nota_IT = max [(note1 + note2) / 2 ; 0.7 * note2].

    The autonomous SQL queries resolution (auxiliary practical work) will be carried out outside the regular lessons’ timetable through an autonomous online learning module. This module will be open after each practice and will be available until the next practice. The grade will be proportional to the number of questions answered throughout the course and their difficulty.

The final grade (Nota_Final) will be the weighted average of the grades in each part (see table of evaluation activities) and will be calculated as follows:

Nota_Final = 0.2 * Nota_Pract + 0.2 * Nota_InfTec + 0.1 * Nota_AutoAval + 0.2 * Nota_Probl + 0.3 * Nota_Teoria

A necessary condition for this calculation is that each of the components should have a mark greater than zero, and the average mark obtained in the written individual tests (Nota_PEI) should be equal or greater than 4.5.

You must have a Final Grade equal or greater than 5 to pass the course. A student is considered "Non-evaluated" only if he or she has not done any of the assessment activities.

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 have not passed the course after applying the above assessment criteria will be eligible for the re-evaluation only if they fall in one of the following categories:

(A) Meetthe conditions for the calculation of the final grade, but the total score obtained is equal to or greater than 3.5 and less than 5; (Nota_PEI ≥ 4,5 but with 3.5 ≥ Nota_Final < 5)

(B) Have obtained a score of less than 4.5 in the written individual tests, but if the final grade was calculated as described above, it would be equal or higher than 5; (Nota_PEI < 4,5 but with Nota_Final ≥ 5)

In this re-evaluation exam, which will be written and will be carried out the last week of the semester, students will be evaluated again in all the content of the course (Nota_reeval). The final grade of the course will be obtained by averaging the mark of the reevaluation (Nota_Reeval) and the written individual tests (Nota_PEI). In case this average is greater than or equal to 5, the final grade (Nota_Final) of the course will be "pass" (a 5).

In the case of not being eligible for reevaluation, the final grade will be the minimum between the written individual tests (Nota_PEI) and 4.5.

 

COMPREHENSIVE EVALUATION

This subject/module does not offer the option for comprehensive evaluation.

 

 

OTHER IMPORTANT INFORMATION

  • REGISTRATION IN CARONTE: Students should register in Caronte (http://caronte.uab.cat) at the beginning of the course, since coursework materials, practical assignments and final marks are published there. To register in Caronte it is necessary to enter personal data and a photo card in JPG format. This information will remain strictly private and will be destroyed once the course has finished.
  • CONVALIDATIONS: There will be no special treatment for students who repeat the course.
  • HONOR REGISTRATIONS: Students who have more than 9.0 in the final mark will have an honor matriculation (MH) up to the limit of 5%of those enrolled, according to UAB regulations. In the case of having more than 5% of the students over 9.0, MH will be awarded tothose who have the highest marks.
  • PLAGIARISM: Without prejudice to other disciplinary measures that may be considered appropriate, the irregularities committed by any student that can lead to a variation in the markings of an evaluation act will lead to a grade of zero. Therefore, copying, plagiarizing, cheating, copying, etc. in any of the assessment activities it will imply a suspension and result in a zero mark. Assessment activities qualified in this way and by this procedurewill not be recoverable. If it is necessary topass any of these assessment activities to pass the subject, this subject will be suspended directly, without opportunity to recover it in the same course. In the case of the Technical Report, all the groups involved will be punished with zero (0), regardless of the authorship of the work.
  • COMPREHENSIVE EVALUATION: This subject/module does not offer the option for comprehensive evaluation.

 Calendar of evaluation activities

The dates of the evaluation activities (exercises in the classroom, assignments, ...) will be announced well in advance during the semester.

The date of the final exam is scheduled in the assessment calendar of the Faculty.

"The dates of evaluation activities cannot be modified, unless there is an exceptional and duly justified reason why an evaluation activity cannot be carried out. In this case, the degree coordinator will contact both the teaching staff and the affected student, and a new date will be scheduled within the same academic period to make up for the missed evaluation activity." Section 1 of Article 115. Calendar of evaluation activities (Academic Regulations UAB). 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 revision process

After all grading activities have ended, students will be informed of thedate and way in which the course grades will be published. Students will be also be informed of the procedure,place, date and time of grade revision following University regulations.

 Retake Process

"To be eligible to participate in the retake process, it isrequired for students to have been previouslybeen evaluated for at least two thirds of the total evaluation activities of the subject." Section 3 of Article 112 ter. The recovery (UAB Academic Regulations). Additionally, it is required that the student to have achieved an average grade of the subject between 3.5 and 4.9.

The date of the retake exam will be posted in the calendar of evaluation activities of the Faculty. Students who take this exam and pass, will get a grade of 5 for the subject. If the student does not pass the retake, the grade will remain unchanged, and hence, student will fail the course.

 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


Language list

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