General information


Subject type: Mandatory

Coordinator: Alfonso Palacios González

Trimester: Second term

Credits: 4

Teaching staff: 

Alfonso Palacios González

Teaching languages


The teaching of this subject is mainly in Catalan. However, some sessions, the bibliography, part of the contents and the tools can be in Spanish or English.

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

  • 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 to deepen the knowledge of databases and database management systems (DBMS. The course goes through all the stages of database design, -conceptual, logical, physical, external-, and studies the advanced features of SQL.  

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 is 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) Define and manage the requirements of a software system.
  • (5) 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: Design the conceptual schema of a database based on system requirements. (Collaborate on 1,2,3,4 and 5)
  • LO2: Transform the conceptual schema into a logical schema and obtain a logical design from the database. (Collaborate on 1,2,3 and 5)
  • LO3: Form advanced queries in SQL. (Collaborate on 1,2,3 and 5)
  • LO4: Use in an advanced way the SQL DDL and DML languages. (Collaborate on 1,3 and 5)
  • LO5: Generate a database with DDL from logical design. (Collaborate on 1,2,3,4 and 5)

 

Working methodology


Teaching methodology

Guided learning hours include:

Large group classes in which:

 > the invited teachers or professionals introduce the contents of the subject in master classes, lectures, presentations and videos.

 > small teaching activities are carried out such as problem solving i simulations i case studies which pursue the student to be an active protagonist in the acquisition of their knowledge.

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 work individually on the e-learning platforms of the subject, prepare the theoretical classes, the hours to study and consolidate the acquired knowledge, the hours to prepare the internship work, the hours to carry out the course project and, finally, the hours that the student can dedicate to increase and complement their knowledge on the subject.

The tools that will be used in the 2020/21 academic year are: Oracle Academy, Oracle Database, Oracle APEX, Data Modeler and 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


Content 1: The database design process

Dedication:

  • large group activities: 10 hours
  • seminars: 6 hours
  • small group activities: 4 hours
  • autonomous learning: 30 hours

Description: 

Introduction to the process of designing and implementing a database from the capture of requirements to the generation of the database, through the stages of conceptual design, logical design and physical design.

Conceptual data modeling and standardization tools and techniques are used for the conceptual design stage and relational data modeling for logical and physical designs. 

Topics:

1.1 Introduction to database design

1.2 Conceptual design: from the requirements to the conceptual data scheme

1.3 Logical design: from the conceptual scheme to the logical scheme

1.4 Physical design: database generation

1.5 Normalization

 

Content 2: Development and use of databases with advanced SQL

Dedication:

  • large group activities: 10 hours
  • seminars: 4 hours
  • small group activities: 6 hours
  • autonomous learning: 30 hours

Description: Deepening the knowledge of SQL to implement the logical and physical design of a relational database

Topics: 

2.1 Data Recovery with Advanced SQL: Functions, Operators, Table Combinations, Set Operations, and Results Sorting

2.2 Time management in advanced SQL

2.3 Advanced retrieval of grouped data

2.4 Subconsultations

2.5 Hierarchical recoveries

2.6 DML statements for large data sets

2.7 DDL statements for managing database schema objects

2.8 Access control

2.9 Views, materialized views, synonyms

2.10 Creation of access methods: indexes, clusters

 

 

 

 

Learning activities


Activity Title 1:

Database troubleshooting with advanced SQL
(MECES 2 descriptors a) b) c) d) and f))

Description: Three internship sessions (small group), 1, 4 and 5 and seminars 4 and 5 where the Oracle SQL language is worked
Material: Oracle12c, SQL Developer, Oracle APEX, Oracle Academy
Deliverable:

Individual assessment, exercises and individually solved questions

Learning outcomes RA3, RA4 and RA5
Skills in which he collaborates

B2 (apply SQL knowledge to solve real data usage problems)
B4 (write technical comments in the SQL script code)
B5 (discover advanced SQL applications that can extend your professional life or complement your 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 query, update and define data)
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)
EIS1_ (Develop, maintain and evaluate databases that meet all user requirements and behave reliably and efficiently)
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: 15% of the grade of the subject. 5% for each session.

 

Activity Title 2:

Project for the design and generation of a database based on a requirements analysis
(MECES 2 descriptors a) b) c) d) e) and f)

Description:

Design of a step-by-step database in practice sessions 2 and 3 and as directed work:

  • Requirements analysis
  • Conceptual design
  • Logical design
  • Generation of the database creation script
  • Creation of the database
Support Material: Oracle Academy, modeling and database generation tool
Deliverables:
  • Conceptual scheme
  • Logical scheme
  • Database script
  • Database
  • Individual assessment, exercises and individually solved questions
Learning outcomes  RA1, RA2 and RA5
Skills in which he collaborates

B2 (apply database design knowledge to solve real data usage problems)
B4 (write and generate project documentation)
B5 (discover advanced techniques of design and generation of databases that can extend to the professional life or complement his 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)
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 conceptual data modeling as a communication and discussion tool in database design)
CIN4 (Develop designs, diagrams and conceptual models of data as a way to define requirements and specifications of technical conditions in the deployment 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)
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)

Assessment 15% of the grade of the subject

 

Activity Title 3:

Database Design Exam

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

Description: Individual test to assess the knowledge acquired about database design
Deliverables:
  • Conceptual data modeling exercise
  • Database design questions and exercises
Learning outcomes RA1, RA2 and RA5
Skills in which he collaborates

B2 (apply database design knowledge to solve real data usage problems)
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 conceptual data modeling as a communication and discussion tool in database design)
CIN4 (Develop designs, diagrams and conceptual models of data as a way to define requirements and specifications of technical conditions in the deployment 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)
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: 35% of the grade of the subject

 

Activity Title 4:

Advanced SQL exam
(MECES 2 descriptors a) b) c) and d))

Description: Individual assessment test to measure the level of expertise in SQL
Deliverables:
  • Troubleshooting SQL database queries
  • Solving database maintenance needs with SQL DML
  • Solving database definition needs with SQL DDL
  • Resolution of access definitions and transaction control
Learning outcomes RA3, RA4 and RA5
Skills in which he collaborates

B2 (apply database design knowledge to solve real data usage problems)
CIN1 (Design and develop databases that are reliable, secure and of quality)
CIN4 (Develop designs, diagrams and conceptual models of data as a way to define requirements and specifications of technical conditions in the deployment 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)
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: 35% of the final grade of the subject

Evaluation system


Qualification system (evaluation)

Activities 1 and 2 can be worked on in pairs of two, however the evaluation of all activities is always individual.

 

Activity 1, Database troubleshooting with advanced SQL: 15% (practice sessions 1, 3 and 5 and seminars 4 and 5) IT IS NOT RECOVERABLE

Activity 2, Project of design and generation of a database from an analysis of requirements: 15% (practice sessions 2 and 3 and seminars 1,2 and 3) IT IS NOT RECOVERABLE

Activity 3, Database Design Exam (Content 1): 35% 

Activity 4, SQL Exam (Content 2): 35% 

 

All grades are required. A grade higher than 3,5 is required in each activity to pass the subject and be able to 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

 

In all activities it is compulsory attendance to be evaluated. (exams, seminars and internship sessions)

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

REFERENCES


Basic

Silberschatz, Abraham; Korth, Henry; Sudarshan, S. Database Systems Concepts. Seventh Edition. McGraw-Hill-Education, 2019. ISBN 978-1260084504.

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

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

Complementary

Date, CJ “An Introduction to Database Systems”. Seventh edition. Addison Wesley Longman, 2001. ISBN 978-0201787221 / Eighth edition. Person, 2003 ISBN 978-0321197849

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

Price, Jason. “Oracle Database 12c SQL”. First edition. Oracle Press-McGraw-Hill Education, 2013. ISBN-13: 9780071799355. ISBN-10: 0071799354.