This course gives an overview of what is a database, what is a database model, and what are the different types of users and languages. Specifically we go deep into the relational database model and the database logic components it provides. Students practice the creation and use of these components by means of the SQL language. The course includes an introduction to the design of relational databases, and the study of important issues for the use of databases in the development of software systems such as privileges, transactions, and indexs. The course finishes with an introduction to the new NOSQL database management systems.
Teachers
Person in charge
Carme Quer Bosor (
)
Sergi Nadal Francesch (
)
Others
Antoni Urpi Tubella (
)
Carme Martin Escofet (
)
Francisco Miguel Rodero Blánquez (
)
Jordi Casanovas Muñoz (
)
Marc Molinuevo Garcia (
)
María José Casañ Guerrero (
)
Pau Carbonell Vives (
)
Quim Motger de la Encarnacion (
)
Santiago Rivas Contreras (
)
Vicent Picornell Alandete (
)
Xavier Burgués Illa (
)
Weekly hours
Theory
2
Problems
0
Laboratory
2
Guided learning
0
Autonomous learning
6
Competences
Technical Competences
Common technical competencies
CT2 - To use properly theories, procedures and tools in the professional development of the informatics engineering in all its fields (specification, design, implementation, deployment and products evaluation) demonstrating the comprehension of the adopted compromises in the design decisions.
CT2.2
- To demonstrate knowledge and capacity to apply the characteristics, functionalities and structure of data bases, allowing an adequate use, design, analysis and implementation of applications based on them.
CT2.3
- To design, develop, select and evaluate computer applications, systems and services and, at the same time, ensure its reliability, security and quality in function of ethical principles and the current legislation and normative.
CT2.4
- To demonstrate knowledge and capacity to apply the needed tools for storage, processing and access to the information system, even if they are web-based systems.
CT8 - To plan, conceive, deploy and manage computer projects, services and systems in every field, to lead the start-up, the continuous improvement and to value the economical and social impact.
CT8.6
- To demonstrate the comprehension of the importance of the negotiation, effective working habits, leadership and communication skills in all the software development environments.
CT8.7
- To control project versions and configurations.
Transversal Competences
Appropiate attitude towards work
G8 [Avaluable] - To have motivation to be professional and to face new challenges, have a width vision of the possibilities of the career in the field of informatics engineering. To feel motivated for the quality and the continuous improvement, and behave rigorously in the professional development. Capacity to adapt oneself to organizational or technological changes. Capacity to work in situations with information shortage and/or time and/or resources restrictions.
G8.2
- To be rigorous in the professional development. To be motivated and have a proactive attitude for the quality in the work. Capacity to adapt oneself to organizational or technological changes. Capacity to work in situations with information shortage and/or time and/or resources restrictions.
Objectives
To have a general vision of what a database is, what is a database model, the types of users of databases and which are the categories of databases languages.
Related competences:
CT2.2,
CT2.4,
To know the objectives of a database management system and their architecture.
Related competences:
CT2.2,
CT2.4,
To understand the database relational model, their languages (SQL and relational algebra) and the usual components of a relational database.
Related competences:
CT2.2,
CT2.4,
To be able to define, create and manipulate usual relational database components.
Related competences:
CT2.2,
CT2.4,
CT2.3,
To be able to build programs to manage relational databases.
Related competences:
CT2.2,
CT2.4,
CT2.3,
To be able to apply some defined quality criteria to choose between several SQL statements, database components, or programs, that manage a database and implement the same functionality.
Related competences:
CT2.4,
CT2.3,
To be able to apply some defined quality criteria to choose which types of database components or management programs are more suitable for the implementation of a certain behavior of a software.
Related competences:
CT2.4,
CT2.3,
To have a general vision of how the design of a database should be included in a software development process.
Related competences:
CT2.2,
CT2.3,
To be able to obtain a database relational model starting from a conceptual models in UML.
Related competences:
CT2.2,
CT2.3,
To know the concept of database transaction and its implications.
Related competences:
CT2.2,
CT2.4,
CT2.3,
To know how to identify the different types of interference that can occur between database transactions and their relationship with the isolation levels that defines the SQL Standard.
Related competences:
CT2.2,
CT2.4,
CT2.3,
To know the locking concurrency control technique.
Related competences:
CT2.2,
CT2.4,
CT2.3,
To know the possible physical structures for storing data and its implications for in terms of efficiency.
Related competences:
CT2.4,
CT2.3,
To know the access methods to data and its implications in terms of efficiency.
Related competences:
CT2.4,
CT2.3,
To be able to participate with a proactive atitude in making exercices in teams of 2 or more students, according to the roles assigned to each student that can change during the execution of the exercises.
Related competences:
G8.2,
CT8.6,
To be able to reach to a solution of the exercises that meets the quality criteria defined with limited time and resources.
Related competences:
G8.2,
CT8.6,
CT8.7,
To be able to configure the environment for the implementation and execution of database components and programs that access to databases, taking as input the resources offered to students.
Related competences:
CT8.7,
Understand the main features of NOSQL databases systems, understand how they differ with respect to relational systems, and have seen a classification of types of NOSQL systems that exist today.
Related competences:
CT2.2,
Contents
Introduction
Database Concept. Design models and databases. Types of users. Categories of database languages. Database Management Systems (DBMS). Desirable objectives for databases that DBMS should provide. Architecture of DBMS.
Relational model
Objectives and origin. Data structure with which to construct relational databases. Operations that provides the relational model to manipulate and query data. Integrity rules to be met by the data in a relational database.
Languages: Relational Algebra and SQL
Introduction. Relational Algebra: relational algebra operations; queries. SQL: create tables, insert, delete and update of rows in a table, queries on a database. Considerations and quality criteria about how to write queries.
Logical database components
Concept of a logical database component: data and control components. Introduction to the data components: schemes, tables and domains, assertions and views. Introduction to the control components: stored procedures, triggers and privileges.
Stored Procedures and Triggers
Implementation of stored procedures in PL/pgSQL language. Implementation of triggers in PostgreSQL. Considerations and quality criteria in the design and implementation of procedures and triggers.
SQL Programming
Programming in Java and JDBC. Considerations and quality criteria in the design and implementation of programs that access databases.
Introduction to the design of relational databases
Stages in the design of a database. Introduction to the understanding of simple UML conceptual models. Translation of simple UML conceptual models to relational model databases.
Transactions and concurrency
Concept of transaction. ACID properties of transactions. Interference between transactions. Serialitzability. Recoverability. Concurrency control techniques. Isolation Levels. Locking and isolation levels.
Physical storage structures and access methods
Introduction. Access methods to perform queries and updates in a database. Costs of the different access methods.
NOSQL
Introducción. Diferencias con los SGBD relacionales tradicionales. Objetivos de los SGBD NOSQL. Ejemplos de sistemas que requieren este tipo de SGBD.
Activities
ActivityEvaluation act
T/P. Study of the databases introduction
The contents related with the subject are presented Objectives:12 Contents:
Prepare the environment for deployment, creation and execution of database components from resources provided by the teacher. Preliminary study of the database that will be used in the first laboratory classes and of some basic SQL sentences.
Theory
0h
Problems
0h
Laboratory
2h
Guided learning
0h
Autonomous learning
2h
L. Study of SQL 1
Laboratory teams are created. The way of working in laboratory classes is presented. The teams solve a laboratory questionnaire on the topic in class. An individual paper question is answered on the topic in class. Objectives:346 Contents:
The teams solve a laboratory questionnaire on the topic in class. An individual paper question is answered on the topic in class. Objectives:346151617 Contents:
The teams solve a laboratory questionnaire on the topic in class. An individual paper question is answered on the topic in class. Objectives:346151617 Contents:
The teams solve a laboratory questionnaire on the topic in class. An individual paper question is answered on the topic in class. Objectives:3 Contents:
The teams solve a laboratory questionnaire on the topic in class. An individual paper question is answered on the topic in class. Objectives:346151617 Contents:
Students review laboratory exercises and SQL and relational algebra exams.
Theory
0h
Problems
0h
Laboratory
2h
Guided learning
0h
Autonomous learning
2h
L. Exercises: Stored Procedures basics
The teams solve a laboratory questionnaire on the topic in class. An individual paper question is answered on the topic in class. Objectives:346151617 Contents:
The teams solve a laboratory questionnaire on the topic in class. An individual paper question is answered on the topic in class. Objectives:46151617 Contents:
The teams solve a laboratory questionnaire on the topic in class. An individual paper question is answered on the topic in class. Objectives:46151617 Contents:
Students review laboratory exercises and exams on Stored Procedures and Triggers.
Theory
0h
Problems
0h
Laboratory
2h
Guided learning
0h
Autonomous learning
2h
L. Exercises: Programming with SQL - JDBC basics
The teams solve a laboratory questionnaire on the topic in class. An individual paper question is answered on the topic in class. Objectives:46151617 Contents:
The teams solve a laboratory questionnaire on the topic in class. An individual paper question is answered on the topic in class. Objectives:35151617 Contents:
Students review laboratory exercises and exams on Programming with SQL - JDBC
Theory
0h
Problems
0h
Laboratory
2h
Guided learning
0h
Autonomous learning
2h
L. Partial exam
Students: Solve the exam individually. It is not possible to use any material. Objectives:123468916 Week:
8 (Outside class hours)
Theory
2h
Problems
0h
Laboratory
0h
Guided learning
0h
Autonomous learning
15h
Final exam
The student: Solves the exam individually. It is not possible to use any material. Objectives:123456789101112131415161718 Week:
15 (Outside class hours)
Theory
3h
Problems
0h
Laboratory
0h
Guided learning
0h
Autonomous learning
16h
Teaching methodology
Theory/Problem classes (2.3 hours per week).
Independent learning: To prepare the classes, the students may have to read and understand some materials stated by the teacher. After classes, the student have to review and do exercices about the subject studied in class.
Theory classes. In the theory classes the teacher present a part of the contents of the course. Usually the teachers use slides, which the students should bring to the classes.
Problem classes. In the problem classes the students solve exercises on the content presented during the theory classes.
Laboratory classes (1.7 hours per week).
Independent learning activities: The contents that are the aim of the laboratory classes are studied independently and individually by students at home. Each week before the lab class students have home work to do that ends with the resolution of a questionnaire of moodle / LearnSQL.
Laboratory classes: The work at classes is in teams of 2 students. The students have the opportunity to share concerns with his/her teammate on the home work of the previous weed, and if it is necessary they ask the teacher questions unresolved. Then the students do the activities stated by the teacher and finally they solve a moodle/LearnSQL questionnaire.
Laboratory evaluation: In each class, the students answer a question individually to evaluate the work done at home previously and during the class. The evaluation is also based on the exercices solved during the class.
Resources related to laboratory classes:
All documents, materials and questionnaires related with the course are available to students through the platform moodle/LearnSQL.
Apart from the feedback that teachers give to students during classes, the platform moodle/LearnSQL includes a corrector of database exercises that provides feedback to students about exercises solutions.
Students will be evaluated just in case they assist to the GROUP WHERE ARE ENROLLED, both in classes of theory/problems and in classes of laboratory.
Evaluation methodology
The grade of the course is based on technical competencies:
- NLB: Laboratory grade. It is based on:
. Satisfactory resolution of the questionnaire corresponding to each class.
. Grades of the question answered during each class.
- NEP - Partial exam grade. The partial exam includes the topics: 1, 2, 3, 4 (without stored procedures and triggers), and 7.
- NEF: Final exam grade. The final exam includes the following topics: 4 (only stored procedures and triggers), 5, 6, 8, 9 and 10.
Course grade = 0.40*NEP + 0.50*NEF + 0.10*NL
Students will be evaluated just in case they assist to the GROUP WHERE ARE ENROLLED.
Students who have not taken any of the exams will have a final grade of Not Presented (NP).
Any attempt of fraud during the course will imply the application of the general academic regulations of the UPC
Grades of the generic competence: The possible grades are A, B, C or D (where A corresponds to an excellent level of accomplishment, B corresponds to a desired level of accomplishment, C corresponds to a sufficient level of accomplishment and D corresponds to a level not sufficient). A good evaluation of this competence will be for the students that:
- Act with rigor in the classes (their attitude in class is appropriate according to the guidelines given for different types of class, either theory, problems or laboratory).
- Act with respect towards peers, and in case of teams work with positive interdependence respect to the other team members.
- Collaborate actively in the activities of cooperative learning in teams or pairs that are made. Accept and perform the roles assigned to the team members during these activities.
- Do exercises arriving to solutions (in the laboratory study questionnaires) that pass all the test games (no matter how many attempts they need).
- Do exercises arriving to solutions (in the exams) that meet the quality criteria established in the course for each type of exercise.
- In general, complete the exercises in the time and resources provided.