Credits
6
Types
Compulsory
Requirements
This subject has not requirements, but it has got previous capacities
Department
ESSI
The student will have an overview of what a database is, its objectives and main components. It will deepen in the managers based on the relational database model and will practice the creation, management and use of its components through SQL. An introduction to the design of relational databases is included, as well as the study of the different components of a manager.

Teachers

Person in charge

  • Antoni Urpi Tubella ( )

Others

  • Carme Quer Bosor ( )

Weekly hours

Theory
1
Problems
1
Laboratory
2
Guided learning
0.4
Autonomous learning
5.6

Competences

Technical Competences

Technical competencies

  • CE7 - Demonstrate knowledge and ability to apply the necessary tools for the storage, processing and access to data.

Transversal Competences

Transversals

  • CT6 - Autonomous Learning. Detect deficiencies in one's own knowledge and overcome them through critical reflection and the choice of the best action to extend this knowledge.

Basic

  • CB2 - That the students know how to apply their knowledge to their work or vocation in a professional way and possess the skills that are usually demonstrated through the elaboration and defense of arguments and problem solving within their area of ??study.
  • CB3 - That students have the ability to gather and interpret relevant data (usually within their area of ??study) to make judgments that include a reflection on relevant social, scientific or ethical issues.

Generic Technical Competences

Generic

  • CG1 - To design computer systems that integrate data of provenances and very diverse forms, create with them mathematical models, reason on these models and act accordingly, learning from experience.
  • CG2 - Choose and apply the most appropriate methods and techniques to a problem defined by data that represents a challenge for its volume, speed, variety or heterogeneity, including computer, mathematical, statistical and signal processing methods.

Objectives

  1. 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: CE7, CT6,
  2. To know the objectives of a database management system and their architecture.
    Related competences: CE7, CT6,
  3. To understand the database relational model, their languages (SQL and relational algebra) and the usual components of a relational database.
    Related competences: CE7, CT6, CB2, CB3,
  4. To be able to define, create and manipulate usual relational database components.
    Related competences: CE7, CT6, CB2, CB3,
  5. To be able to build programs to manage relational databases.
    Related competences: CE7, CT6, CB2, CB3,
  6. 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: CE7, CT6, CB2, CB3,

  7. To have an overview of data warehouses and multidimensional databases, and to know how to express OLAP statements via SQL.
    Related competences: CE7, CT6, CG1, CG2, CB2, CB3,
  8. 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: CE7, CT6, CB2, CB3,
  9. To have a general vision of how the design of a database should be included in a software development process.
    Related competences: CE7, CT6,
  10. To be able to obtain a database relational model starting from a conceptual models in UML.
    Related competences: CE7, CT6,
  11. To know the concept of database transaction and its implications.
    Related competences: CE7, CT6,
  12. 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: CE7, CT6,
  13. To know the locking concurrency control technique.
    Related competences: CE7, CT6,
  14. To know the possible physical structures for storing data and its implications for in terms of efficiency.
    Related competences: CE7, CT6, CG1, CG2,
  15. To know the access methods to data and its implications in terms of efficiency.
    Related competences: CE7, CT6, CG1, CG2,

Contents

  1. Introduction
    Database concept. Database design and models. Types of users. Categories of languages. Concept of database management system (DBMS). Desirable goals for databases that DBMSs must provide. Architecture of the DBMS.
  2. The relational model
    Objectives and origin. Structure of data with which the relational databases are built. Operations provided by the relational model to manipulate and query the data. Integrity rules to be met by the data in a relational database.
  3. Languages: Relational algebra and SQL
    Introduction. Relational algebra: operations of relational algebra; queries. SQL: table creation; insertion, deletion and modification of rows in a table; queries on a database. Considerations about the implementation of queries.
  4. 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.
  5. Data Warehouses and OLAP
    Introduction to data warehouses and multidimensional databases. SQL Extensions for OLAP
  6. SQL Programming
    Programming in Java and JDBC. Considerations and quality criteria in the design and implementation of programs that access databases.
  7. 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
  8. Transactions and concurrency
    Concept of transaction. ACID properties of transactions. Interference between transactions. Serialitzability. Recoverability. Concurrency control techniques. Isolation Levels. Locking and isolation levels.
  9. Physical storage structures, access methods and optimization
    Introduction. Access methods to perform queries and updates in a database. Costs of the different access methods. Introduction to Ouery Optimaztion

Activities

Activity Evaluation act


Study of the database introduction


Objectives: 2 1
Contents:
Theory
2h
Problems
0h
Laboratory
2h
Guided learning
0h
Autonomous learning
4h

Study of the databases introduction


Objectives: 3
Contents:
Theory
0h
Problems
0h
Laboratory
0h
Guided learning
0h
Autonomous learning
2h

Study of the data logical components


Objectives: 3 6 4
Contents:
Theory
2h
Problems
4h
Laboratory
0h
Guided learning
0h
Autonomous learning
6h

Study of the introduction to design of relational databases


Objectives: 9 10
Contents:
Theory
2h
Problems
2h
Laboratory
0h
Guided learning
0h
Autonomous learning
4h

Study of transactions and concurrency


Objectives: 11 12 13
Contents:
Theory
2h
Problems
4h
Laboratory
0h
Guided learning
0h
Autonomous learning
6h

Study of storage, access methods and optimization


Objectives: 14 15
Contents:
Theory
6h
Problems
4h
Laboratory
0h
Guided learning
0h
Autonomous learning
7h

Study of the Relational Algebra and SQL


Objectives: 3 6 4
Contents:
Theory
0h
Problems
0h
Laboratory
10h
Guided learning
0h
Autonomous learning
12h

Study of data warehouses and in OLAP


Objectives: 6 8 7
Contents:
Theory
2h
Problems
0h
Laboratory
2h
Guided learning
0h
Autonomous learning
4h

Study of stored procedures and triggers


Objectives: 3 6 4
Contents:
Theory
0h
Problems
0h
Laboratory
6h
Guided learning
0h
Autonomous learning
6h

Programming with SQL - JDBC


Objectives: 5 6 4
Contents:
Theory
0h
Problems
0h
Laboratory
4h
Guided learning
0h
Autonomous learning
4h

Laboratory control: Relational Algebra Languages, SQL, OLAP via SQL


Objectives: 6 4 7
Week: 7
Type: lab exam
Theory
0h
Problems
0h
Laboratory
2h
Guided learning
0h
Autonomous learning
2h

Laboratory control: stored procedures and triggers


Objectives: 6 8 4
Week: 11
Type: lab exam
Theory
0h
Problems
0h
Laboratory
2h
Guided learning
0h
Autonomous learning
2h

Laboratory control: Programming with SQL - JDBC


Objectives: 5 6 8
Week: 14
Type: lab exam
Theory
0h
Problems
0h
Laboratory
2h
Guided learning
0h
Autonomous learning
2h

Final Exam


Objectives: 2 3 5 6 8 9 10 11 12 13 14 15 1 4 7
Week: 15 (Outside class hours)
Type: final exam
Theory
0h
Problems
0h
Laboratory
0h
Guided learning
3h
Autonomous learning
23h

Reviews and resolution of doubts about the exams



Theory
0h
Problems
0h
Laboratory
0h
Guided learning
3h
Autonomous learning
0h

Teaching methodology

Theory classes / problems
Autonomous learning: To prepare classes the student may have to read and understand materials and / or notes indicated by the teacher. Afterwards in class, the student needs to review and solve exercises on the topic of study.
Theory classes In lectures the teachers present a part of the contents of the subject. Normally, teachers use transparencies that students would be advised to obtain before classes, in order to do a better follow-up.
Problems classes In problem classes, students solve exercises about content presented during theory classes. These exercises are done in teams of two students according to a cooperative learning technique.
Evaluation. In four of the problem classes, students will solve an exercise that will be collected and evaluated by the teacher.

Laboratory classes
Autonomous learning: The contents that are worked on in the laboratory classes will be studied autonomously by the students. Each week before in the laboratory class students will have a homework assignment that will end with the resolution of a moodle / LearnSQL quiz.
Laboratory classes: Class work will be in teams of 2 students. Students have the opportunity to share doubts with their teammate about the work they have done at home, and if necessary, to ask questions that are not resolved to the teacher. Next the students do the activities that the teacher has indicated and finally solve the class questionnaire.
Assessment: There are three weeks in which laboratory tests are carried out, which count as an evaluation act of the subject.

Evaluation methodology

The grade of the course is based on technical competencies:

- NEF: Final exam grade.

- NPR: Problems grade. It is the average of the grades of the four problems exam.

- NLB: Laboratory grade. It is calculated as the 40% of the grade of the part algebra / SQL, 30% of the grade of the part of procedures / triggers and 30% of the grade of the part of programming with SQL - JDBC.


Final grade = Maximum (
                                   NLB * 0.25 + NEF * 0.60 + NPR * 0.15,
                                   NLB * 0.25 + NEF * 0.75
                                   )

- For students who can concur to the reevaluation, the reevaluation examen grade will replace NEF

Bibliography

Basic:

Complementary:

Previous capacities

To know the data structures in internal memory. To be able to implement programs of medium complexity.

Addendum

Contents

NO HI HA CANVIS RESPECTE LA INFORMACIÓ PUBLICADA A LA GUIA DOCENT

Teaching methodology

Classes de problemes. Els problemes es fan en equips de dos estudiants. Avaluació. L'avaluació es farà en els exàmens parcial i final. Canvis en les classes de laboratori Avaluació: L'avaluació es farà en els exàmens parcial i final, i a més també es tindrà en compte la participació actives en les sessions de laboratori.

Evaluation methodology

NF= 0.45*NEP + 0.45*NEF + 0.1*NL NL - Participació activa en les sessions de laboratori. Es tindrà en compte les classes a les que s'ha participat entregant satisfactòriament els exercicis proposats a classe al LearnSQL. La nota es calcularà de manera proporcional a les classes a les que s'ha participat activament. NEP - Nota examen parcial. En l'examen parcial hi entren els temes: 1, 2, 3, 4 i 7. NEF - Nota examen final. En l'examen final hi entren els temes: 5, 8 i 9.

Contingency plan

Les classes, tant de teoria com de laboratori, es faran amb meet de manera síncrona.