• Description

Databases are a key element in the development of information systems. More specifically, along this development, database design plays an essential role as it conditions the subsequent data exploitation.

Given that database design is a complex process that allows obtaining a database implementation based on the initial requirements from users of the information system, it will be addressed over different stages. 

In this subject, the different database design stages will be studied and applied to the specific case of relational databases. The physical implementation will be done over a specific database management system and, therefore, it will be necessary to adapt the design to the database in question. Once the database is created, we will proceed with its exploitation and, in particular, we will study query and view processing in order to optimise performance, making data exploitation as efficient as possible. In consequence, in this subject, students will learn how to conduct the database design process and how to improve it by means of the use of views and query processing, assuring the design suitability.

• The subject within the syllabus as a whole

Database Design (DBD) is a compulsory course for students of the Information Technology degree and optional in Telecommunications Technology degree. In both cases, the main goal is learning how to design databases.

Furthermore, this subject belongs to an itinerary formed by subjects in the database knowledge area.

More specifically, in the Information Technology degree, having passed Database Use is a requirement and, in the case of Telecommunications Technology, having passed Databases is recommended. These subjects are equivalent for both degrees. Database Use / Databases. They are compulsory subjects that introduce students to the database world from the point of view of their usage. They include creation and manipulation of the database by using interactive and hosted SQL.

After taking Database Design there is a possibility to increase database knowledge with a more specific subject: Database Architecture. Database Architecture. It is an optional subject in the Information Technology itinerary. This subject covers the aspects related to relational DBMS implementation, as well as the problems introduced by data replication and distribution. It also includes alternatives to relational databases.

Other previous subjects can also be taken in the Telecommunications Technology degree. However, in that case they are optional.

• Professional fields to which it applies

This course allows to develop the necessary competences and skills for the fulfilment of professional exercise closely related to relational database design and usage.

Some of the professional fields in which the knowledge acquired in the subject might be very useful are:

• Information technology software developers (programmers, analysts, project managers).
• Database administrators.
• Trainers and/or database support technicians.
• Entrepreneurs who want to create and maintain their own database.

• Prior knowledge

It is essential to have studied the course Introduction to databases or to have knowledge about relational model and database access standard language.

• Information prior to enrolment

In order to follow a proper learning path, it is essential to abide by the prerequisites indicated in the previous section.

This is a course with a strong practical component, which includes two compulsory practical activities (PR) and an evaluation test (CAT2) also compulsory. There is another evaluation test (CAT1), which is optional but strongly recommended.

PostgreSQL and Diagrams.net UML are the tools used as DBMS and diagram modelling tool, respectively.

There is no final exam in this subject. Therefore, it is compulsory to follow continuous assessment as explained in the corresponding section of this Course Plan.

• Learning objectives and results

The goals of the course are to understand the database designing process and to prepare the student to be able to design quality relational databases, starting from a set of requirements for the information system and ensuring that data exploitation can be achieved correctly and efficiently.

 Therefore, the main competences that students must acquire through the course of this subject are the following ones:

•  Knowing what the database design process consists of, which are its objectives and the stages this process is made of.
• Knowing the fundamentals of database conceptual design and be able to represent data models via UML class diagrams.
• Being able to make a good logical design through the transformation of the conceptual model to the relational model, previously removing eventual design traps and applying the normalisation theory.
• Knowing the physical structure that the database uses to store non-volatile data.
• Starting from the logical design, being able to build the database physical design, adapting it to the features of a specific DBMS.
• Knowing how to define the necessary and convenient indexes over each table to obtain a good performance on accessing the database.
• Knowing the procedures for processing and optimising queries.
• Using views as external design elements to improve the database design.
• Knowing the scope of a database security mechanisms.
• Ability to propose and evaluate different technological alternatives to resolve a specific problem.
• Basic knowledge about the use of computer programming, operating systems, databases and information technology software, applied to engineering.

Additionally, this course as being part of the programme study of the Bachelor’s degree in Techniques for Software Development, the student will acquire the following basic training competence:

• Ability to apply specific data treatment, storage and administration techniques.
• Ability to propose and evaluate different technological alternatives to resolve a specific problem.

• Content

The course contents are distributed across the following modules:

Unit 1. Introduction to database design.

This unit presents a general vision of the database design process, highlighting its most important steps.

Unit 2. Conceptual design for databases.

The unit presents the conceptual design stage as a part of the database design process. Starting from the information system requirements analysis, shows how to obtain a high-level conceptual design, independently from the technology. The UML (Unified Modelling Language) is used for representing this conceptual map.

Unit 3. Logical design for databases.

This unit explains how to obtain the database logical design in the case of relational databases. The conceptual design, described in UML, is reviewed to ensure not falling in specific design traps and translated according to the relational model. Finally, the normalisation theory allows to guarantee the quality of the logical designed obtained.

Unit 4. Physical design of databases.

This unit presents the physical design stage. Chosen a specific DBMS, it explains how to obtain a physical implementation of the database from the corresponding relational logical schema. It also shows how databases are structured and stored in non-volatile physical medium, as well as the different data access methods.

Unit 5. Query and view processing.

This unit presents the different strategies for resolving queries that a DBMS can use to optimise the performance of query processing. In the same way, it shows different security mechanisms to ensure the correct data access by authorised users and their encapsulation, with the objective of providing different database views.

• 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

The assessment process is based on students' own work and the assumption that this work is original and has been carried out by them.

In assessment activities, the following irregular behaviours, among others, may have serious academic and disciplinary consequences: someone else being involved in carrying out the student's assessment test or activity, or the work being not entirely original; copying another's work or committing plagiarism; attempting to cheat to obtain better academic results; collaborating in, covering up or encouraging copying; or using unauthorized material, software or devices during assessment.

If students are caught engaging in any of these irregular behaviours, they may receive a fail mark (D/0) for the assessable activities set out in the course plan (including the final tests) or in the final mark for the course. This could be because they have used unauthorized materials, software or devices (e.g. social networking sites or internet search engines) during the tests, because they have copied text fragments from an external source (internet, notes, books, articles, other student's projects or activities, etc.) without correctly citing the source, or because they have engaged in any other irregular conduct.

In accordance with the UOC's academic regulations , irregular conduct during assessment, besides leading to a failing mark for the course, may be grounds for disciplinary proceedings and, where appropriate, the corresponding punishment, as established in the UOC's coexistence regulations.

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

• Ask the student to provide proof of their identity, as established in the university's academic regulations.
• Request that students provide evidence of the authorship of their work, throughout the assessment process, both in continuous and final assessment, by means of an oral test or by whatever other synchronous or asynchronous means the UOC specifies. These means will check students' knowledge and competencies to verify authorship of their work, and under no circumstances will they constitute a second assessment. If it is not possible to guarantee the student's authorship, they will receive a D grade in the case of continuous assessment or a Fail in the case of final assessment.
  For this purpose, the UOC may require that students use a microphone, webcam or other devices during the assessment process, in which case it will be the student's responsibility to check that such devices are working correctly.

• View the assessment model