General information


Subject type: Mandatory

Coordinator:

Trimester: First term

Credits: 4

Teaching staff: 

Alfonso Palacios Gonzalez

Skills


Basic skills
  • B2_That students know how to apply their knowledge to their job or vocation in a professional way and have the skills they demonstrate by developing and defending arguments and solving problems within their area of ​​study

  • B4_That students can convey information, ideas, problems and solutions to both specialized and non-specialized audiences

  • B5_That students have developed those learning skills necessary to undertake further studies with a high degree of autonomy

Specific skills
  • EFB4_Basic knowledge of the use and programming of computers, operating systems, databases and computer programs with application in engineering

  • EIS1_Ability to develop, maintain and evaluate software services and systems that meet all user requirements and that behave reliably and efficiently, are affordable to develop and maintain and comply with quality standards, applying theories, principles, methods and software engineering practices

  • EIS2_Ability to assess customer needs and specify software requirements to meet those needs, reconciling conflicting goals, by seeking acceptable compromises, within the limitations of cost, time, the existence of already developed systems and of the organizations themselves

  • EIS4_Ability to identify and analyze problems and design, develop, implement, verify and document software solutions based on adequate knowledge of current theories, models and techniques

  • EIS5_Ability to identify, assess and manage potential associated risks that may arise

  • EIS6_Ability to design appropriate solutions in one or more application domains, using software engineering methods that integrate ethical, social, legal and economic aspects

  • ESI3_Ability to actively participate in the specification, design, implementation and maintenance of information and communication systems

Transversal competences
  • T1_That students know a third language, which will be preferably English, with an adequate level of oral and written form, according to the needs of the graduates in each degree

  • T2_That students have the ability to work as members of an interdisciplinary team either as one more member, or performing management tasks in order to contribute to developing projects with pragmatism and a sense of responsibility, making commitments taking into account the available resources

Description


The general objective of the subject is the deepening in the knowledge of the databases and of the systems managers of databases, discovering the procedural extension of the language SQL for the implementation of the external designs of the databases, knowing the architecture of DBMSs to be able to manage data access methods, optimize SQL queries, define physical design and manage concurrency.  

This subject has methodological and digital resources to make possible its continuity in non-contact mode in the case of being necessary for reasons related to the Covid-19. In this way, the achievement of the same knowledge and skills that are specified in this teaching plan will be ensured.

Learning outcomes


In general, this subject contributes to the following learning outcomes specified for the subject of Software Engineering to which it belongs:

  • (1) Appropriately use theories, procedures and tools in the professional development of computer engineering in all its areas (specification, design, implementation, deployment -implantation- and product evaluation) so as to demonstrate understanding of the commitments made to design decisions.
  • (2) Take initiatives that generate opportunities, new objects or new solutions, with a vision of process and market implementation, and that involve others in projects to be developed (ability to act autonomously).
  • (3) Specify, design, implement, manage and maintain complex and / or critical software systems and services.
  • (4) Evaluate and select hardware and software production platforms for the execution of computer applications and services.
  • (5) Evaluate hardware / software systems based on a given quality criterion.
  • (6) Determine the factors that negatively affect the security and reliability of a hardware / software system, and minimize its effects.
  • (7) Identify current and emerging technologies and assess whether they are applicable, and to what extent, to meet user needs.
  • (8) Design solutions that integrate hardware, software, and communications technologies (and the ability to develop system-specific software solutions) for distributed systems and ubiquitous computing devices.
  • (9) Understand and use effectively manuals, product specifications and any other technical information written in English.

At a more specific level, at the end of the course the student must be able to:

  • LO1: Schedule in advance a procedural language of definition of databases (in the course 2019/2020 with PL / SQL of Oracle). (Contribute to 1,2,3,4,5,6,9)
  • LO1.1: To develop functions, procedures and packages with PL / SQL. (Contribute to 1,2,3,4,5,6,9)
  • LO1.2: Administrator exceptions with PL / SQL. (Contribute to 1,2,3,4,5,6,9)
  • LO1.3: Define database triggers with PL / SQL. (Contribute to 1,2,3,4,5,6,9)
  • LO2: Implement an external design with views, materialized views, procedures, functions and packages. (Contribute to 1,2,3,5,6,8,9)
  • LO3: Implement an internal or physical design from the logical design. (Contribute to 1,2,3,4,5,6,7,8,9)
  • LO4: Define distributed databases. (Contribute to 1,2,3,4,5,6,7,8,9)
  • LO5: Identify the parts and functions of each component of the architecture of a database management system. (Contribute to 1,2,4,5,6,7,9)
  • LO6: Optimize the execution of SQL statements deciding the best execution plan and the best access methods in each case: B-Tree and Bitmap indexes, Hash functions, clusters. (Contribute to 1,2,3,4,5,6,7,9)
  • LO7: Define and manage concurrency in databases. (Collaborate in 1,2,3,4,5,6,7,8,9)
  • LO8: Administrator concurrent execution of SQL statements. (Contribute to 1,2,3,4,5,6,8,9)

 

Working methodology


Guided learning hours include:

Theoretical classes in large group in which:

 > the invited teachers or professionals introduce the contents of the subject.

 > small teaching activities are carried out that seek to make the student an active protagonist in the acquisition of their knowledge.

> workshops or practical seminars are held where the contents are worked on and developed

Internships in small teams of two people that have a part that is performed in the laboratory led or supervised by the teacher and a part in which the team must operate independently.

Within the hours of autonomous learning are considered the hours to prepare the theoretical classes, the hours to study and consolidate the acquired knowledge, the hours to prepare the work of the practices and, finally, the hours that the student can dedicate to increase and complement their knowledge on the subject.

The confirmed tools that will be used in the 2020/21 academic year are: Oracle Academy, PL / SQL, Oracle APEX, Oracle Database and Oracle SQL Developer

This course, due to the situation generated by COVID, some of the large group sessions can be done -if the health situation requires it- in hybrid format: face-to-face and online (via streaming). This will allow students to rotate to face-to-face classes, respecting the maximum number of students per classroom imposed by the distance measures. When they are not in contact, they will be able to follow the class online from home.

With regard to internship sessions in smaller spaces (such as laboratories, studios or sets), where appropriate, work will be carried out simultaneously in several spaces in order to ensure that the conditions established by the safety protocols are met.

Contents


Topic 1

Content title 1 External database design

Dedication: GG: 15h GP: 6h AA: 30h

 

 

 

Description

Introduction to database procedural languages, which allow to enrich the logical design and definition of advanced database business rules on the one hand and, on the other hand, to implement external design and distributed design.  

Subject matter

1.1 Transactional or procedural languages ​​of DBMS

1.2 Procedures and functions

1.3 Abstract Data Types: Packages and Types.

1.4 Triggers

1.5 Synonyms, views, materialized views

1.6 Implementation of external schemes

1.7 Implementation of distributed databases

Topic 2

Content title 2:  Architecture, structure, access methods and DBMS optimization

Dedication: GG: 10h GP: 2h AA: 20h

 

 

 

Description

Deepening knowledge of databases and database management systems, the architecture of DBMSs to be able to manage data access methods, optimize the execution of SQL queries and define the physical design.

Subject matter

2.1 DBMS architecture

2.2 Internal database structure

2.3 Methods of access

2.4 Query processing and execution plans

2.5 Optimization of SQL statements

Topic 3

Content title 3:  Concurrency control

Dedication: GG: 5h GP: 2h AA: 10h

 

 

 

Description

Introduction to concurrency control in databases, learning the tools to manage transactions, concurrency and ACID systems. Management of consistency in reading and updating.

Subject matter

3.1 Transaction management and concurrency control

2.2 ACID systems

2.3 Consistency in reading

2.4 Management of seriability, locks and deadlocks.

Learning activities


Activity 1:

Laboratory of programming practices with procedural languages ​​for defining databases
(MECES 2 descriptors a) b) c) d) and f))

Description:

Block programming practices, exceptions, procedures, functions, packages and triggers with PL / SQL.

Three practice sessions (small group), 1, 2 and 3 where the Oracle PL / SQL language is worked

Material: Oracle12c, PL / SQL, SQL Developer, Oracle APEX, Oracle Academy
Deliverable:

Individual assessment: exercises and questions solved individually

Learning outcomes LO1 (RA 1.1, RA 1.2 and RA 1.3)
Skills in which he collaborates

B2 (apply PL / SQL knowledge to solve real data usage problems)
B4 (write technical comments in the PL / SQL script code)
B5 (discover advanced applications for advanced definition of databases with procedural languages ​​that can be extended to professional life or complement their training with specialized courses)
T1 (read technical documentation in English and use PL / SQL commands and statements with English keywords)
T2 (activity carried out in a team of two)
EFB4 (Programming with PL / SQL to consult, update and define data)
CIN1 (Develop databases that are reliable, secure and quality)
CIN8_ (Ability to build and maintain databases robustly, securely and efficiently)
CIN12_ (Knowledge and application of the characteristics, functionalities and structure of the databases)
CIN13_ (Knowledge and application of the necessary tools for the storage, processing and access to information systems)
EIS1_ (Develop, maintain and evaluate databases that meet all user requirements and behave reliably and efficiently)
EIS2_ (Assess customer needs and specify database requirements to meet these needs)
EIS4_ (Identify and analyze problems and design, develop, implement, verify and document software solutions in the field of databases)
ESI3_ (Participate actively in the specification, design, implementation and maintenance of databases needed by information and communication systems)

Evaluation: 10% of the grade of the subject

 

Activity 2:

External and internal design project of a database 
(MECES 2 descriptors a) b) c) d) e) and f)

Description:

Course project for the definition of a database following the stages of conceptual, logical, physical design to end with an external design proposal.

Seminars 1,2 and 3 
Support Material: Oracle12c, PL / SQL, SQL Developer, Oracle APEX, Oracle Academy, database modeling and generation tool
Deliverables:

Individual evaluation: participation in seminars, exercises and individually resolved questions

Learning outcomes  RA1 (RA 1.1, RA 1.2, RA 1.3), RA2 
Skills in which he collaborates

B2 (apply knowledge of external database design to solve real data usage problems)
B4 (write and generate project documentation)
B5 (discover advanced techniques of external design of databases that can extend to the professional life or complement his training with specialized courses)
T1 (read technical documentation in English and use PL / SQL commands and statements with English keywords)
T2 (activity carried out in a team of two)
CIN1 (Design and develop databases that are reliable, secure and of quality)
CIN2 (Plan and deploy a database design and development project)
CIN3 (Understand the importance of communication skills in writing technical reports) 
CIN4 (Elaborate the technical conditions of an external design of databases)
CIN5_ (Using an external design to improve the administration and maintenance of a database)
CIN8_ (Ability to analyze, design, build and maintain databases robustly, securely and efficiently)
CIN12_ (Knowledge and application of the characteristics, functionalities and structure of the databases)
CIN13_ (Knowledge and application of the necessary tools for the storage, processing and access to the information systems) CIN14_ (Knowledge and application of the fundamental principles and basic techniques of the programming of the concurrence to databases and of the distribution of data)
EIS1_ (Develop, maintain and evaluate databases that meet all user requirements and behave reliably and efficiently)
EIS2_ (Assess customer needs and specify database requirements to meet these needs)
EIS4_ (Identify and analyze problems and design, develop, implement, verify and document software solutions in the field of databases)
EIS5_ (Identify, assess, and manage potential risks associated with database access without an external design layer)
EIS6_ (Design solutions to isolate and manage access to sensitive data such as personal data through an external design)
ESI2_ (Determine external design requirements to develop the information and communication systems of an organization bearing in mind aspects of security and compliance with current regulations and legislation)
ESI3_ (Participate actively in the specification, design, implementation and maintenance of databases needed by information and communication systems)

Assessment 10% of the grade of the subject

 

Activity 3:

Laboratory of SQL statement optimization and concurrency control practices
(MECES 2 descriptors a) b) c) d) and f))

Description:

Consultation optimization techniques and use of the EXPLAIN PLAN
Concurrency control: seriousness management, read consistency, locks and inter-locks
Two practice sessions (small group), the 4 and 5 and three seminars (3, 4 and 5).

Material: Oracle12c, SQL Developer, Oracle APEX
Deliverable:

Individual evaluation: participation in seminars, exercises and individually resolved questions

Learning outcomes (RE3, RE4, RE5, RE6, RE7, RE8)
Skills in which he collaborates

B2 (apply optimization and concurrency knowledge to solve real data usage problems)
B4 (write comments and arguments appropriate to practice reports)
B5 (discover advanced applications of optimization and concurrency management that can extend to professional life or complement their training with specialized courses)
T1 (read technical documentation in English and use SQL commands and statements with English keywords)
T2 (activity carried out in a team of two)
EFB4 (Programming with SQL to consult the optimization)
CIN1 (Develop databases that are reliable, secure and quality)
CIN5 (Administration and maintenance of databases)
CIN8_ (Ability to build and maintain databases robustly, securely and efficiently)
CIN12_ (Knowledge and application of the characteristics, functionalities and structure of the databases)
CIN13_ (Knowledge and application of the necessary tools for the storage, processing and access to information systems)
CIN14_ (Knowledge and application of the fundamental principles and basic techniques of the programming of the concurrence to databases and of the distribution of data)  
EIS1_ (Develop, maintain and evaluate databases that meet all user requirements and behave reliably and efficiently)
EIS2_ (Assess customer needs and specify database requirements to meet these needs)
EIS4_ (Identify and analyze problems and design, develop, implement, verify and document software solutions in the field of databases)
EIS5_ (Identify, evaluate and manage the potential risks of poor optimization of data access and poor control of concurrency)
ESI3_ (Participate actively in the specification, design, implementation and maintenance of databases needed by information and communication systems)

Evaluation: 10% of the grade of the subject.

 

Activity 4:

Examination of external design and programming with procedural languages ​​for defining databases (Topic 1):

(MECES 2 descriptors a) b) c) and d)

Description: Individual test to assess the knowledge acquired on external database design and programming with database procedural languages
Deliverables:
  • Procedural language questions and exercises
  • External database design questions and exercises
Learning outcomes LO1 (LO1.1, LO1.2, LO1.3) LO2
Skills in which he collaborates

B2 (apply knowledge of PL / SQL and external database design to solve real data usage problems)
EFB4 (Programming with PL / SQL to consult, update and define data)
CIN1 (Design and develop databases that are reliable, secure and of quality)
CIN2 (Plan and deploy a database design and development project)
CIN4 (Elaborate the technical conditions of an external design of databases)
CIN5_ (Using an external design to improve the administration and maintenance of a database)
CIN8_ (Ability to analyze, design, build and maintain databases robustly, securely and efficiently)
CIN12_ (Knowledge and application of the characteristics, functionalities and structure of the databases)
CIN13_ (Knowledge and application of the necessary tools for the storage, processing and access to information systems)
CIN14_ (Knowledge and application of the fundamental principles and basic techniques of the programming of the concurrence to databases and of the distribution of data)
EIS1_ (Develop, maintain and evaluate databases that meet all user requirements and behave reliably and efficiently)
EIS2_ (Assess customer needs and specify database requirements to meet these needs)
EIS4_ (Identify and analyze problems and design, develop, implement, verify and document software solutions in the field of databases)
ESI2_ (Determine external design requirements to develop the information and communication systems of an organization bearing in mind aspects of security and compliance with current regulations and legislation)
ESI3_ (Participate actively in the specification, design, implementation and maintenance of databases needed by information and communication systems)

Evaluation: 35% of the grade of the subject

 

Activity 5:

Examination of DBMS architecture, optimization and concurrency control (Topics 2 and 3)
(MECES 2 descriptors a) b) c) and d))

Description: Individual assessment test to measure the level of expertise in SQL statement optimization and in the use of SQL to manage transactions and control concurrency
Deliverables:
  • Troubleshooting SQL statement optimization
  • Evaluation of architectures, physical structures and methods of access to DBMS
  • Troubleshooting transaction management and concurrency control
Learning outcomes LO3, LO4, LO5, LO6, LO7, LO8
Skills in which he collaborates

B2 (apply optimization and concurrency knowledge to solve real data usage problems)
EFB4 (Programming with SQL to query the option and manage concurrency)
CIN1 (Design and develop databases that are reliable, secure and of quality)
CIN4 (Elaborate conditions and technical requirements of an internal design of databases)
CIN5 (Administration and maintenance of databases)
CIN8_ (Ability to analyze, design, build and maintain databases robustly, securely and efficiently)
CIN12_ (Knowledge and application of the characteristics, functionalities and structure of the databases)
CIN13_ (Knowledge and application of the necessary tools for the storage, processing and access to information systems)
CIN14_ (Knowledge and application of the fundamental principles and basic techniques of the programming of the concurrence to databases and of the distribution of data)
EIS1_ (Develop, maintain and evaluate databases that meet all user requirements and behave reliably and efficiently)
EIS2_ (Assess customer needs and specify database requirements to meet these needs)
EIS4_ (Identify and analyze problems and design, develop, implement, verify and document software solutions in the field of databases)
EIS5_ (Identify, evaluate and manage the potential risks of poor optimization of data access and poor control of concurrency)
ESI3_ (Participate actively in the specification, design, implementation and maintenance of databases needed by information and communication systems)

Evaluation: 35% of the final grade of the subject

Evaluation system


Qualification system (evaluation)

 

External and distributed design internship laboratory: 10% (practice sessions 1, 2 and 3). (Due to its continuous assessment component, this grade is not recoverable)

External and internal design project: 10% (Work throughout the course) (seminars 1,2, 3 and XNUMX). (Due to its continuous assessment component, this grade is not recoverable)

Laboratory of practices of optimization of sentences SQL and control of concurrence: 10% (practice sessions 4 and 5) (seminars 3,4, 5 and XNUMX). (Due to its continuous assessment component, this grade is not recoverable)

PL / SQL exam, external design and distributed design (Topic 1): 35%

Examination of DBMS architecture, optimization and concurrency control (Topics 2 and 3): 35%

 

All grades are required. A grade of more than 3,5 is required in each activity to pass the subject and be able to take the average.

 

Following the general regulations, the copy in the evaluation activities leads to the evaluation with a SUSPENSED note (0) of the whole subject.

 

Rules for carrying out the activities

All activities are required to attend to be evaluated.

Activities are mandatory. Activities not performed - or those not attended - are rated as 0.

 

REFERENCES


Basic

Elmasri, R .; Navathe, SB "Fundamentals of Database Systems, Global Edition". Seventh Edition. Harlow, Essex: Pearson, 2017. ISBN 978-1-292-09761-9.

Silberschatz, Abraham; Korth, Henry; Sudarshan, S. "Fundamentals of Databases". Sixth edition. Madrid-McGraw-Hill-Education, 2014. ISBN 978-84-481-9033-0.

Complementary

McLaughlin, Michael. “Oracle Database 12c PL / SQL Programming”. First edition. Oracle Press-McGraw-Hill Education, 2014. ISBN-13: 9780071812436. ISBN-10: 0071812431.

Date, CJ “Introduction to Database Systems”. Seventh edition. Mexico-Pearson Education, 2001. ISBN 9684444192

Loney, Kevin; Bryla, Bob. “Oracle Database 12c. The Complete Reference ”. First edition. Oracle Press-McGraw-Hill Education, 2013. ISBN-13: 9780071801751. ISBN-10: 0071801758.