• Description

A key element in the development of information systems are databases. 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 to obtain 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 processing of queries and views in order to optimise this process, making this 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. Its main goal is learning how to design databases. Before enrolling to this course, it is recommended to have passed Introduction to databases.

Introduction to Databases is another compulsory subject that introduces students to the database world from the point of view of their usage. It includes the creation and manipulation of the database by using interactive and hosted SQL.

Therefore, having passed such a previous course or having similar knowledge about database field is a good starting point to take advantage of Database design.

Other courses in the programming studies like Advanced Web Programming uses the contents of Database design.

• 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 mandatory practical activities (PRA). In addition, it is advisable to carry out two continuous assessment tests (CAT). The database management system (DBMS)      chosen for practical activities is Oracle and the Draw.io tool will be used for building UML diagrams.

 The subject includes a final exam. Delivering the two practical activities and having a minimum score on them is required to apply for the final exam. Also, it is required to achieve a minimum mark in the final exam to pass the subject.

• 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 make the practical activities.

 The teaching units are available in multiple formats in the theory classroom, 22.622 – Database design. They are accessible in PDF format from the classroom calendar. Moodle quizzes are also available in the classroom for your own self-assessment. Before accessing them, please review the “Continuous assessment” section.

 The software to achieve the competences related to the database design practical activities can be found in the Resources section of the classroom. Oracle 11gXEr2 will be used as our DBMS, SQLDeveloper 3.1 as the development environment and, as a modelling tool, Draw.io is recommended. The related documentation is available through the classroom calendar, together with the activities that require use of this software.

 The use of a modelling tool for the development of the proposed exercises is compulsory, as well as ensuring, in case of not using Draw.io, that the chosen tool allows making the designs following the naming and style conventions used across the subject.

• Guidelines on assessment at the UOC

The assessment process is based on the student's personal work and presupposes authenticity of authorship and originality of the exercises completed.

Lack of authenticity of authorship or originality of assessment tests, copying or plagiarism, the fraudulent attempt to obtain a better academic result, collusion to copy or concealing or abetting copying, use of unauthorized material or devices during assessment, inter alia, are offences that may lead to serious academic or other sanctions.

Firstly, you will fail the course (D/0) if you commit any of these offences when completing activities defined as assessable in the course plan, including the final tests. Offences considered to be misconduct include, among others, the use of unauthorized material or devices during the tests, such as social media or internet search engines, or the copying of text from external sources (internet, class notes, books, articles, other students' essays or tests, etc.) without including the corresponding reference.

And secondly, the UOC's academic regulations state that any misconduct during assessment, in addition to leading to the student failing the course, may also lead to disciplinary procedures and sanctions.

The UOC reserves the right to request that students identify themselves and/or provide evidence of the authorship of their work, throughout the assessment process, and by the means the UOC specifies (synchronous or asynchronous). For this purpose, the UOC may require students to use a microphone, webcam or other devices during the assessment process, and to make sure that they are working correctly.

The checking of students' knowledge to verify authorship of their work will under no circumstances constitute a second assessment.

• View the assessment model