Databases (21601)
Degree/study: Bachelor's degree in Computer Sciences, Bachelor's Degree in Telematics Engineering, Bachelor's degree in Audiovisual Systems Engineering
Year: 1st
Term: 3rd
Number of ECTS credits: 4 credits
Hours of studi dedication: 100 hours
Teaching language or languages: catalan and spanish
Teaching Staff: Jesús Bisbal, Nadjet Bouayad-Agha, Horacio Saggion
1. Presentation of the subject
The Databases subject is part of a group of programming subjects that aim at students on the first year of the Engineering degrees. It is a complement for the programming subjects taught during the first year of studies.
Its aim is that students are able to analyse problems and to give valid solutions related to database management systems by the end of the term. More specifically, the Databases subject is about conceptual modelling, relational algebra and the use of structured query language (SQL) amongst others.
The subject comprises three main activities: lectures, seminars and laboratory sessions. In the lectures, the basic concepts on database theory are introduced together with some application examples. In the seminars, students will have to solve problems, which will have been given before the session. Each program corresponds to one of the concepts explained in the lectures. In the laboratory sessions, students will have the opportunity to put into practice the knowledge from the lectures and the seminars with some practical problems to be solved.
2. Prerequisites to follow the subject
To understand the contents of this subject, the students need to have a deep knowledge on the topics covered in Programming Fundamentals and Linear Algebra and Discrete Mathematics. More specifically, it is important that students have a high command on the mathematical concepts studied in the Linear Algebra and Discrete Mathematics.
3. Competences to be attained
The main objective of this subject is students become acquainted with the basic aspects on database management systems and that they are able to analyse database-related problems, which include designing and implementing a database.
The competences to be obtained at the end of the subject are listed underneath. General competences are not directly related to the subject's main topic (that is databases), they are abilities with have to do with the engineering profession. Specific competences are directly related to the topics that the subject deals with.
|
General competences |
Specific competences |
Instrumental1. Cognitive abilities Interpersonal3. Communication competence Systemic4. Information management Other6. Using technologic devices |
1. Capacity to design a database using the Entity-Relationship model from some real-world specifications. 2. Deep knowledge on structured query language (SQL) to perform easy and more complex consult operations on a database. 3. Capacity to solve consult issues on a database by using relational algebra operations.
|
This is an introductory course on the basic database aspects, which are needed for both relational database designers and users.
The final aim of the subject is for the students to be able to solve middle size problems, which involve relational databases design and implementation phases.
4. Assessment
4.1. General assessment criteria
Each of the three types of activities that are part of this course (lectures, laboratory sessions and seminars) is assessed.
T: lectures' grade through a final exam
L: laboratory sessions are assessed by grading the exercises that the students need to hand in
S: seminars' grade
The final mark is to be calculated as follows:
Final Grade = 0,5 x T + 0,35 x L + 0,15 x S
At the end of the course the students will write a multiple-choice test (in which incorrect answers are worth -1/4 of their value) corresponding to the lectures. It is an individual and written exam which assesses the competences to be acquired during the course. This assessment is compulsory and needs to be qualified with at least a 40% so that the student passes the course.
During the laboratory sessions, the students do some practical exercises that put into practice their ability to work with a relational database, both consulting it and programming on it. Each practice is done on a continuous basis.
These practical exercises are done in pairs so that the students have to cooperate and be able to communicate in order to solve the potential problems that may arise. This assessment is compulsory too and needs to be qualified with at least a 40% so that the student passes the course.
The students are supposed to have prepared the multiple-choice questions that they will find in the instructions for the seminars before each session. They will be able to ask questions on these multiple-choice questions during the in-class sessions even if they are not devoted to working on them. The aim of these sessions is for the students to work together in some development problems (i.e. SQL consults). At the end of every session the student will have to solve an exercise set out by the professor individually and to hand it in via Moodle so that it is corrected. The solutions which are handed in (in a completely individual way) are a part of the continuous assessment of this subject. There are not compulsory but are worth 15% of the final mark and will be weighted equally taking into account the total number of seminar sessions.
4.2. Competences' specification
|
Competences to be acquired |
Acquisition index |
Assessment procedure |
Temporalization |
General competences1. Cognitive abilities 2. Common sense 3. Communication competence 4. Information management 5. Capacity to generate ideas 6. Using technologic devices |
1. Capacity to find solutions to problems 2. Capacity to find solutions to problems 3. Coherent and well written problems in practice exercises 4. Solutions that equal to previous problems 5. Clever solutions to previous problems 6. Correct use of the SQL language |
1. Assessment of practice exercises, seminars and final exam 2. Assessment of practice exercises, seminars 3. Assessment of practice exercises 4. Assessment of practice exercises, seminars and final exam 5. Assessment of practice exercises, seminars and final exam 6. Assessment of practice exercises |
Throughout the quarter
|
Specific competences1. Capacity to implement a database using the Entity-relationship model and some specifications from the reality. 2. Solid knowledge on the SQL language and ability to perform easy and more complex database consult operations 3. Capacity to solve database consult operations with relational algebra operations. |
1. Correct syntax in seminars 2. Correct syntax in seminars 3. Correct solution and notation in seminars
|
1. Assessment of seminars 2. Assessment of practice exercises, seminars and final exam 3. Assessment of seminars and final exam
|
Throughout the quarter |
5. Contents
5.1. Content blocks
- Content block 1. Relational model and relational algebra. The Entity-relationship model
- Content block 2. The Entity-relationship model
- Content block 3. SQL language
- Content block 4. Indexation
5.2. Contents' organization and specification
Content block 1. Relational model and relational algebra.
|
Concepts |
Procedure |
Attitudes |
|
1. Data structure of the relational model 2. Integrity rules 3. Data manipulation 4. Relational algebra |
1. Understanding the components of the relational model 2. Applying the relational model integrity rules to a database 3. Knowing how to apply the relational algebra operators explained in class to problems and specific consult operations |
1. Willingness to understand concepts that may seem confusing and difficult at first. 2. Willingness to have a solid base knowledge. 3. Willingness to spend as much time as necessary in order to understand each concept. |
Content block 2. The Entity-relationship model
|
Concepts |
Procedure |
Attitudes |
|
1. The Entity-relationship model principles 2. Design criteria of an E-R diagram 3. M-N relationships |
1. Understanding the components of the entity-relationship model 2. Understanding and modelling the data requirements of an organisation 3. Transforming M-N relationships into the relational model |
1. Willingness to understand concepts that may seem confusing and difficult at first. 2. Willingness to have a solid base knowledge. 3. Willingness to spend as much time as necessary in order to understand each concept. |
Content block 3. SQL language
|
Concepts |
Procedure |
Attitudes |
|
1. Data Manipulation Language (DML) 2. Data Definition Language (DDL) 3. Basic concepts on transactions 4. Basic concepts on indexation
|
1. Knowing how to solve SQL consult operations 2. Being able to create a relational database from a relational diagram 3. Understanding the different isolation models between transactions and defining the best one for each context 4. Understanding the indexation mechanisms and deciding when to index an attribute in a table |
1. Willingness to have a solid base knowledge. 2. Understanding the need to write SQL consult operations (declarative language) in a clear way.
|
Content block 4. Indexation
|
Concepts |
Procedure |
Attitudes |
|
1. Consult operations 2. Efficiency 3. Indexation
|
1. Analyzing the natural assessment process of a consult operation 2. Intuitive calculation of the execution efficiency of a consult operation 3. Identifying the right indexes to improve efficiency in a consult operation 4.Index definition in a relational database |
1. Willingness to have a solid base knowledge. 2. Understanding the need to improve efficiency in consult operations.
|
6. Metodology
6.1. Subject's metodology
The usual learning process begins with a lecture in which some theory and practice concepts are presented. The students have to complete this task by carefully reading their notes and the additional material that the professor recommends. A two-hour lecture, for instance, will need an additional hour of work outside after the session.
After that, there will be some seminar sessions in which the students will put into practice the concepts and techniques that they have learnt in the lecture by solving problems. The students will get the solution for the first exercises but not for the following ones. The aim of the activity is to consolidate the basis of the subject so that they are later able to solve more complicated problems. This activity is done in groups of 15 to 20 students.
The next step in the learning process is the practice sessions, in which bigger problems that require a conceptual design previous to the implementation are presented. The final practice exercise puts together all specific competences that the student is supposed to have acquired throughout the subject. This final activity is done in pairs, in groups of 30 people. Students need to work in a continuous way outside class to perform this task.
7. Bibliography and didactic resources
7.1. Didactic ressources. Basic readings (paper and electronic)
R. Ramakrishnan and J. Gehrke, Database Management Systems, 3rd Edition, McGraw-Hill 2003
J. Sistac. Bases de Dades, EdiUOC, 2000 (second edition 2005)
C.J. Date. An introduction to Data Base systems, Vol.1, 6th edition, Addison-Wesley, 1995.
7.2. Didactic ressources. Further readings (paper and electronic)
R. Elmasri, S. B. Navathe. Sistemas de Bases de Datos, Addison-Wesley, 1997.
J. Sistac. Disseny de bases de dades, EdiUOC, 2002.
A. Silberschatz, H.F. Korth, S. Sudarshan. Fundamentos de Bases de Datos , 3rd edition, McGraw-Hill, 1998.
J.D. Ullman, J. Widom. First Course in Database Systems, Prentice Hall, 1997.
H. García-Molina, J.D. Ullman, J. Widom. Database System Implementation, Prentice Hall, 2000.
C. Türker and M. Gertz. Semantic integrity support in SQL:1999 and commercial (object-)relational database management systems, The VLDB Journal, 10(4):241-269, December 2001.
7.3. Didactic resources. Teaching material for the course
- Aula Moodle
- Powerpoint
- Instructions for the seminar sessions and solutions to the multiple-choice questions
- Instructions for the laboratory sessions
7.4. Didactic resources. Additional materials.
J. Sánchez. Manual rápido de MySQL. 2004.
http://www.jorgesanchez.net/bd/mysql.pdf
MySQL 5.0. Reference Manual.
http://dev.mysql.com/doc/refman/5.0/en/index.html