Desenvolvemento de Aplicacións: Normalización

Video Tutorial 5: Databases

• The tutorial is for CFGS Web Application Development and Multiplatform Application Development students
• The tutorial covers database normalization
• The tutorial includes a complete exercise

Normalization

• The lesson will include a full E-R model
• A Relational Model practice exercise
• Key Concepts of Normalization
• A General Summary of the concepts

Exercise 1

• Students need to create an Entity Relationship diagram and convert it to a relational model.
• The model should meet the first normal form.
• The exercise details involve handling multiple PACs (assignments) for a student and calculating the maximum possible grade.
• Students need to determine the attributes for a student (ID, name, age, and languages).
• Student assignments include the ID, and number of questions, and the date of opening, closing, days accessible, and the maximum points for the assignment. Each question has a value of 0.2 points.

Exercise 1 Diagram

• A diagram illustrating the relationship between 'Alumno' (Student) and 'PAC' (Assignment) data points.
• The diagram shows a many-to-many relationship between the two entities.
• The 'hacer' entity represents the concept of students completing assignments.
• Attributes like 'Id alu' (student ID), 'edad' (age), 'Nombre' (name), 'nota' (grade), 'tiempo' (time).
• Other attributes for PAC data like 'Id pac', 'fecha_aper', 'dias_apertura', 'fecha_cierre', n'numero_preg', 'puntuación_max'

Exercise 1 Summary

• The solution, which includes an E-R and a Normalized model for the database example presented.
• The diagram illustrates attributes and relationships between different tables (e.g. 'ALUMNO','idiomas', 'HACER', 'PACS').

Exercise 2

• Normalizing the "Alquiler" (Rentals) table to 3rd normal form (3NF).
• Students must consider restrictions on the table like multiple rentals of the same book but on different dates.
• The table needs a primary key composed of columns such as 'FechaDeAlquiler', 'CódigoLibroAlquilado', 'CódigoUsuario'.
• Addressing issues like updating user phone numbers, and updating editorial locations.
• Students need to review the restrictions and other important considerations relating to rentals.

Exercise Solution (Normalisation)

• The exercises show how to go from one table to 11.
• Detailed information for "Alquileres" (rental) tables to include attributes such as "FechaDeAlquiler", "CódigoLibroAlquilado","CódigoUsuario","Título Libro", "CódigoEditorial" and "NombreEditorial".
• The process to convert from one table to six related tables in order to normalize the data to 3NF.

Normalization to 1NF

• Eliminating multi-valued fields (e.g., multiple authors, phone numbers) is part of reaching first normal form.
• Creating separate tables for phone numbers and authors are examples here

Normalisation to 2NF

• Ensuring that attributes depend on the entire primary key, not just a portion, is the core of 2NF.
• Removing attributes that are only predicated on partial components of primary keys.

Normalization to 3NF

• Eliminating transitive dependencies.
• Breaking down attributes relying on other non-key attributes.
• Creating a new table, "EDITORIALES", to store editorial information, separate from the "LIBROS" table.

Exercise Summary (Overall)

• The overall solution provided involves normalizing a database to the 3NF.
• Separate tables for rentals, users, books, authors, and editorial information are created.
• Relationships between these tables are shown using foreign keys.

Questions for Review

• The normalization exercise itself.
• Converting E-R diagrams to models.
• The number of strong entities involved.
• The different relationships and their number.
• Why "FechaAlquiler" is a key field in the "Alquileres" table.
• Whether "Autores" is an entity or an attribute.

Contextualization

• Conceptual, Logical, and physical stages of database development.
• Explaining which diagram helps visualize database structure.
• How to use a diagram to display database structure for ease of development.
• Explaining how a model is converted into a usable database.

Este cuestionario está dirixido a alumnos de CFGS en Desenvolvemento de Aplicacións Web e Aplicacións Multiplataforma. A lección céntrase na normalización de bases de datos, incluíndo un modelo E-R completo e exercicios prácticos relacionados. Os estudantes deberán crear un diagrama de relación de entidades e conversalo a un modelo relacional que cumpran coa primeira forma normal.