• Description

A key element in the development of information systems is databases. Specifically, within this development, the design of the database plays a fundamental role, as it conditions the future exploitation of data.

Additionally, the design of databases is a complex process that will be addressed in different stages or phases, starting from the requirements of the end-users to obtaining an implementation of the database.

In this course, we will study the different stages of database design, focusing on the specific case of relational databases. Its physical implementation will take place on a specific database management system, requiring an adaptation of this design to the database software. After creating the database, we will proceed with its exploitation, specifically examining query processing and views to optimize this process and ensure the most efficient data exploitation. Therefore, in this course, students will learn to complete the entire database design process and enhance it by considering query processing and views, ensuring its suitability.

• The subject within the syllabus as a whole

• Professional fields to which it applies

• Prior knowledge

It is essential to have completed the Database Usage course or have knowledge of the relational model and the standard language for database access. Proficiency in English is also necessary to understand technical manuals.

• Information prior to enrolment

For proper course progression, it is crucial to meet the prerequisite knowledge requirements indicated in the previous section. The course has a significant practical component, including two mandatory practical exercises and a cumulative assessment test (CAT2), also mandatory. CAT1 is optional but highly recommended. The database management system (DBMS) used is PostgreSQL, and the tool for creating UML diagrams is Diagrams.net.

The course does not have a final exam, so continuous assessment is essential, as indicated in the corresponding section of this course outline.

• Learning objectives and results

The objective of this course is to understand the process of database design and prepare students to be capable of designing quality relational databases, starting from a set of information system requirements and ensuring that data exploitation can be carried out correctly and efficiently.

The main competencies that students should acquire through this course are outlined below:

• Understand the process of database design, including its objectives and stages.
• Know the fundamentals of conceptual database design and be able to represent data models using UML class diagrams.
• Be capable of creating a good logical design by transforming the conceptual model into the relational model, eliminating potential design pitfalls, and applying normalization theory.
• Understand the physical structure used by the database to store data in a non-volatile manner.
• Perform the physical design of the database based on the logical design, adapting it to the characteristics of a specific Database Management System (DBMS).
• Define necessary and appropriate indexes in each table to achieve optimal performance in database access.
• Understand query processing and optimization mechanisms.
• Use views as external design elements to enhance the database design.
• Understand the scope of database security mechanisms.
• Ability to propose and evaluate different technological alternatives when solving a specific problem.

For the Telecommunication Technologies Bachelor’s Degree, the competencies of the course are related to the following basic training competency:

Basic knowledge of computer programming, operating systems, databases, and software applications applied to engineering.

In the case of the Computer Engineering Bachelor’s Degree, these competencies are related to the following:

• Ability to apply specific techniques for data processing, storage, and management.
• Ability to propose and evaluate different technological alternatives to solve a specific problem.

• Content

The course consists of the following instructional modules:

Module 1: Introduction to Database Design

This module provides an overview of the database design process, highlighting its key stages.

Module 2: Conceptual Database Design

This module introduces the conceptual design stage as part of the overall database design process. Starting with the analysis of information system requirements, it demonstrates how to obtain a high-level conceptual design independent of technology. The Unified Modeling Language (UML) is used for representing this conceptual map.

Module 3: Logical Database Design

This module explains how to obtain the logical design of the database for relational databases. It reviews the conceptual schema of the database described in UML to avoid specific design pitfalls and translates it according to the relational model. Finally, normalization theory ensures the quality of the obtained logical design.

Module 4: Physical Database Design

This module covers the physical design stage. After selecting a specific Database Management System (DBMS), it explains how to obtain a physical implementation of the database from the corresponding relational logical schema. It also explores the structuring and storage of databases on non-volatile physical media, along with different data access methods.

Module 5: Query Processing and Views

This module presents various query resolution strategies that the DBMS can use to optimize query processing performance. It also examines views as external design mechanisms that enable data updating and encapsulation while enhancing database performance and the readability of complex queries.

The instructional modules are available in multi-format in the classroom. For self-assessment, students have Moodle quizzes. Before accessing them, it is recommended to refer to the Continuous Assessment and Final Evaluation section.

 The software used for the practical part of the course is the PostgreSQL database management system, and the recommended diagramming tool is Diagrams.net. However, any other tool may be used as long as it allows for designs following the nomenclature and style used in the course.

• View the UOC learning resources used in the subject

• Additional information on support tools and learning resources

The main learning resources of the course are the previously described teaching units and the software to carry out the practical activities.

The teaching units are available in multiple formats in the theory classroom, 22.622 – Database design. Moodle quizzes are also available in the classroom for your own self-assessment. Before accessing them, please review the “Continuous assessment” and “Final assessment” sections.

The software to achieve the competences related to the database design practical activities is PostgreSQL Database Management System, as well as the recommended tool Diagrams.net. In the classroom there is a “Laboratory” forum available, in which teachers will provide guidelines and support for the software installation in a Windows environment. In case of other environments, such support will not be guaranteed, although it will also be provided to the extent possible. You can use any other diagram modelling tool as long as it allows to make designs following the terminology and style used along the course.

• Guidelines on assessment at the UOC

Assessment at the UOC is, in general, online, structured around the continuous assessment activities, the final assessment tests and exams, and the programme's final project.

Assessment activities and tests can be written texts and/or video recordings, use random questions, and synchronous or asynchronous oral tests, etc., as decided by each teaching team. The final project marks the end of the learning process and consists of an original and tutored piece of work to demonstrate that students have acquired the competencies worked on during the programme.

To verify students' identity and authorship in the assessment tests, the UOC reserves the right to use identity recognition and plagiarism detection systems. For these purposes, the UOC may make video recordings or use supervision methods or techniques while students carry out any of their academic activities.

The UOC may also require students to use electronic devices (microphones, webcams or other tools) or specific software during assessments. It is the student's responsibility to ensure that these devices work properly.

The assessment process is based on students' individual efforts, and the assumption that the student is the author of the work submitted for academic activities and that this work is original. The UOC's website on academic integrity and plagiarism has more information on this.

Submitting work that is not one's own or not original for assessment tests; copying or plagiarism; impersonation; accepting or obtaining any assignments, whether for compensation or otherwise; collaboration, cover-up or encouragement to copy; and using materials, software or devices not authorized in the course plan or instructions for the activity, including artificial intelligence and machine translation, among others, are examples of misconduct in assessments that may have serious academic and disciplinary consequences.

If students are found to be engaging in any such misconduct, they may receive a Fail (D/0) for the graded activities in the course plan (including final tests) or for the final grade for the course. This could be because they have used unauthorized materials, software or devices (such as artificial intelligence when it is not permitted, social media or internet search engines) during the tests; copied fragments of text from an external source (the internet, notes, books, articles, other students' work or tests, etc.) without the corresponding citation; purchased or sold assignments, or undertaken any other form of misconduct.

Likewise and in accordance with the UOC's academic regulations, misconduct during assessment may also be grounds for disciplinary proceedings and, where appropriate, the corresponding disciplinary measures, as established in the regulations governing the UOC community (Normativa de convivència).

In its assessment process, the UOC reserves the right to:

• Ask students to provide proof of their identity as established in the UOC's academic regulations.
• Ask students to prove the authorship of their work throughout the assessment process, in both continuous and final assessments, through a synchronous oral interview, of which a video recording or any other type of recording established by the UOC may be made. These methods seek to ensure verification of the student's identity, and their knowledge and competencies. If it is not possible to ensure the student's authorship, they may receive a D grade in the case of continuous assessment or a Fail grade in the case of the final assessment.

Artificial intelligence in assessments

The UOC understands the value and potential of artificial intelligence (AI) in education, but it also understands the risks involved if it is not used ethically, critically and responsibly. So, in each assessment activity, students will be told which AI tools and resources can be used and under what conditions. In turn, students must agree to follow the guidelines set by the UOC when it comes to completing the assessment activities and citing the tools used. Specifically, they must identify any texts or images generated by AI systems and they must not present them as their own work.

In terms of using AI, or not, to complete an activity, the instructions for assessment activities indicate the restrictions on the use of these tools. Bear in mind that using them inappropriately, such as using them in activities where they are not allowed or not citing them in activities where they are, may be considered misconduct. If in doubt, we recommend getting in touch with the course instructor and asking them before you submit your work.

• View the assessment model