Logo UAB
2020/2021

Data Bases

Code: 104535 ECTS Credits: 6
Degree Type Year Semester
2503743 Management of Smart and Sustainable Cities 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:
Carlos Alejandro Parraga
Email:
CarlosAlejandro.Parraga@uab.cat

Use of Languages

Principal working language:
spanish (spa)
Some groups entirely in English:
No
Some groups entirely in Catalan:
No
Some groups entirely in Spanish:
No

Other comments on languages

Although lessons will be imparted in Spanish, students will be able to make questions in Catalan or English

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)

 

 

Competences

  • Critically analyse work carried out and demonstrate a desire to improve.
  • Identify and use different sources, models and data bases of information generated by urban activity, as well as their principles of operation, access policies and standards.
  • Solve urban management problems using knowledge, methodology and procedures for the design and implementation of computer applications for different types of environment (web, mobile, cloud) and different paradigms.
  • Students must have and understand knowledge of an area of study built on the basis of general secondary education, and while it relies on some advanced textbooks it also includes some aspects coming from the forefront of its field of study.

Learning Outcomes

  1. Apply database management technologies.
  2. Critically analyse work carried out and demonstrate a desire to improve.
  3. Describe database technologies for storing results from urban activity.
  4. Students must have and understand knowledge of an area of study built on the basis of general secondary education, and while it relies on some advanced textbooks it also includes some aspects coming from the forefront of its field of study.
  5. Write the technical reports pertaining to a database.

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

Methodology

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.

Please note that:

a. Teaching will be offered on campus or in an on-campus and remote hybrid format depending on the number of students per group and the size of the rooms at 50% capacity.

b. The proposed teaching methodology may undergo some modifications according to the restrictions imposed by the health authorities on on-campus courses.

Activities

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

Assessment

The evaluation of the course consists of 5 parts: theory exam, E-R design problems’ evaluation, drafting of a technical report, evaluation of practical work and SQL query resolution. The weight of each of these exams can be seen in the table of evaluation activities. The final grade (Nota_Final) will be the weighted average of the notes in each part provided that the minimum mark for each evaluation is exceeded. The minimum grade for the theory exam is 4. For the other evaluations there is no minimum mark. A grade must be drawn higher, or equal, to 5 to approve the course. A student is considered "not evaluable" only if he has not done any assessment activity.

The final grade will be calculated as follows:

  • For students who reach a mark larger or equal than 4 in the theory exam:

Nota_Final = 0,2* Nota_Pract + 0,2*Nota_InfTèc + 0,1*Nota_AutoAval + 0,2*Nota_Probl + 0,3*Nota_Teoria

  • For students who do not reach a minimum of 4 in the theory exam:

Nota_Final = 0,2* Nota_Pract + 0,2*Nota_InfTèc + 0,1*Nota_AutoAval + 0,2*Nota_Probl

 

EVALUATION CRITERIA

  • PROBLEMS’ EXAM and THEORY EXAM (Nota_Probl and Nota_Teoria). There will be two individual written examinations, weighing 20% and 30% on the final grade. The first test will be done approximately in the middle of the semester and will evaluate the basic concepts and acquired skills 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 and the skills in solving SQL queries.
  • PRACTICAL SQL EXAM (Nota_Pract). The evaluation of the practical work (20%) will be done by solving test of SQL queries, which will be carried out in the last practical session and where the student will have access to his/her notes. The format will be the same as in theautonomouslearning module.
  • AUTONOMOUS WORK. It will be divided into two blocks: technical report (20%) and resolution of online SQL queries (10%). The technical report consists of 4 parts corresponding to the 4 design phases of a BD: requirements, E-R diagram, logical tables model and implementation/testing. The document must be formatted appropriately so that the E-R design and logical table diagrams can be read correctly. If a diagram cannot be read clearly for reasons of resolution or quality, this part of the report will be suspended, as well as those that depend on it being not evaluated. The technical report will be delivered in the middle of the semester and a second (and last) installment at the end thereof. The final grade of the technical report will be calculated from both notes (Nota1 and NOTA2) as follows:


Nota_IT_ = max [(nota1 + nota2) / 2; 0,7 * nota2]

The resolution of autonomous SQL queries (self-assessed partical work) will be done outside the class schedules through an online autonomous learning module. This learning module will be open after each practical session and will be available until the day before the next session. The mark will be proportional to the number of questions answered throughout the course and its difficulty.

In the case of not fulfilling the conditions to approve the course, the final grade will be the minimum between the mark obtained from the weighted average of the sit-in exams and a 4.5. (The course is considered approved if the final grade exceeds 5).

RE-EVALUATION CRITERIA

Students who are in one of these three situations can apply for re-evaluation:
(A) students with a grade in the theory exam equal to or greater than 3.5 and who obtain a final grade from the course between 4 and 5. (Nota_Teoria > = 3.5 but with 4 < = Nota_Final < 5)
(B) students with a final grade to pass the course (a 5) but who have a low grade (less than 4) in the final exam. (Nota_Teoria <4 but with Nota_Final > = 5)
(C) students with a final grade of the course equal or greater than 4 and with an average of sit-in exams (problems, practices and theory) equal to or greater than 3.5. (Nota_Final > = 4 but with ((0.3 * Nota_Teoria + 0.2 * Nota_Probl + 0.2 * Nota_Pract)/0.7) > = 3.5)

In this test of re-evaluation, which will be written and held in the last week of the semester, the student will be evaluated again in the same content as inthe theory exam. You must obtain a grade higher, or equal, to 5 to approve the re-evaluation. If approved, the final grade of the course will be "approved" (a 5). In case of suspending, the final grade of the course will be the maximum between the grade obtained in the exam of re-evaluation and the average of the grades obtained in sit-in examinations.

Students in the following situation will pass the course with a final grade equal to the minimum mark (a 5):
(D) students who obtain a total mark of the course between 3.5 and 5, but have passed the theory exam (3.5 < = Nota_Final < 5 but with Nota_Teoria > = 5)

 

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.5 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.5, MH will be awarded to those 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 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. In the case of the Technical Report, all the groups involved will be punished with zero (0), regardless of the authorship of the work.

 Calendar of evaluation activities

The dates of the evaluation activities (midterm exams, 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 who, in accordance with the previous paragraph need to change an evaluation activity date must 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 the date 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 participatein the retake process, it is required for students to have been previously been 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).

 

Assessment Activities

Title Weighting Hours ECTS Learning Outcomes
E-R problems evaluation 20 1.5 0.06 1, 2, 3, 4
Practical work 20 2 0.08 1, 2, 4
SQL queries test 10 2 0.08 1, 2, 4
Technical report 20 0.5 0.02 1, 2, 3, 4, 5
Theory exam 30 2 0.08 1, 2, 3, 4

Bibliography

Basic Bibliography

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

Complementary bibliography

  • A. Fowler, NOSQL for Dummies, For Dummies; 1 edition, 2015
  • Gaurav Vaish, Getting Started with NoSQL, Packt Publishing, 2013
  • C.A. Coronel & S.A. Morris, Database systems : design, implementation, and management (13 ed.): Course Technology, 2018
  • T.M. Connolly & C.E. Begg, Database systems : a practical approach to design, implementation, and management (6th ed. ed.). Boston, MA: Pearson Education, 2014.
  • 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.
  • 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 links