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
Petar Jovanovic (
)
Others
Anna Queralt Calafat (
)
Gerard Pons Recasens (
)
Sergi Nadal Francesch (
)
Weekly hours
Theory
2
Problems
0
Laboratory
2
Guided learning
0
Autonomous learning
6
Competences
Transversal Competences
Transversals
CT4 [Avaluable] - Teamwork. Be able to work as a member of an interdisciplinary team, either as a member or conducting management tasks, with the aim of contributing to develop projects with pragmatism and a sense of responsibility, taking commitments taking into account available resources.
CT6 [Avaluable] - 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.
Technical Competences
Especifics
CE04 - To design and use efficiently the most appropriate data types and structures to solve a problem.
CE08 - To detect the characteristics, functionalities and components of data managers, which allow the adequate use of them in information flows, and the design, analysis and implementation of applications based on them.
Generic Technical Competences
Generic
CG1 - To ideate, draft, organize, plan and develop projects in the field of artificial intelligence.
CG2 - To use the fundamental knowledge and solid work methodologies acquired during the studies to adapt to the new technological scenarios of the future.
Objectives
To know the objectives of a database management system and their architecture.
Related competences:
CT6,
CE04,
CE08,
To understand the database relational model, their languages (SQL and relational algebra) and the usual components of a relational database.
Related competences:
CT4,
CT6,
CB2,
CB3,
CE04,
CE08,
To be able to define, create and manipulate usual relational database components.
Related competences:
CT4,
CT6,
CB2,
CB3,
CE04,
CE08,
To be able to build programs to manage relational databases.
Related competences:
CT4,
CT6,
CB2,
CB3,
CE04,
CE08,
To know the different available formats for semistructured data, and know how to write SQL queries over them.
Related competences:
CG1,
CG2,
CT4,
CT6,
CB2,
CB3,
CE04,
CE08,
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:
CT4,
CT6,
CB2,
CB3,
CE04,
CE08,
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:
CT6,
CB2,
CB3,
CE04,
CE08,
To have a general vision of how the design of a database should be included in a software development process.
Related competences:
CT6,
CE04,
CE08,
To be able to obtain a database relational model starting from a conceptual models in UML.
Related competences:
CT6,
CE04,
CE08,
To know the concept of database transaction and its implications.
Related competences:
CT6,
CE04,
CE08,
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
Related competences:
CT6,
CE04,
CE08,
To know the locking concurrency control technique.
Related competences:
CT6,
CE04,
CE08,
To know the possible physical structures for storing data and its implications for in terms of efficiency.
Related competences:
CG1,
CG2,
CT6,
CE04,
CE08,
To know the access methods to data and its implications in terms of efficiency.
Related competences:
CG1,
CG2,
CT6,
CE04,
CE08,
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:
CT6,
CE04,
Contents
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.
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.
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.
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.
Semistructured data formats and SQL extensions to query them
Introduction to the different semi-structured data formats. SQL extensions to query semi-structured data.
SQL Programming
Programming in Python and DataFrames. Considerations and quality criteria in the design and implementation of programs that access 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.
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
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
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: 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.
Evaluation methodology
The grade of the course is based on technical competencies:
- NPR: Problems grade. It is the average of the grades of the four problems exam.
- NLB: Active participation in laboratory sessions. The classes in which students have participated will be taken into account in case of successfully submission of the exercises proposed in the class. The grade will be calculated in proportion to the classes in which the students have actively participated.
- NEP - Partial exam grade.
- NEF: Final exam grade.
Final grade = Maximum (NLB*0.2+NEP*0.30+NEF*0.35+NPR*0.15,NLB*0,2+NEP*0.35+NEF*0,45)
- For students who can take the re-assessment, the re-assessment exam mark will replace NEF and NEP. In any case, the maximum reassessment score can only be 7.