Summary
Highlights
This video continues the Access database tutorial, focusing on creating relationships between tables. The process begins by navigating to 'Database Tools' and then 'Relationships' to add the desired tables (e.g., 'Estudiantes', 'Asignaturas', 'Profesores') to the graphical view.
Table relationships involve connecting a primary key (e.g., 'ID Profesor' in 'Profesores') to a foreign key in another table (e.g., 'ID Profesor' in 'Asignaturas'). The foreign key must be of the same data type as the primary key. If a table, like 'Asignaturas', needs to relate to 'Profesores', an 'ID Profesor' field must be added to 'Asignaturas' with a 'Number' data type, as the primary 'ID Profesor' in 'Profesores' would be an autonumber type that equates to a number.
To create a relationship, drag the primary key ('ID Profesor' from 'Profesores') to the foreign key ('ID Profesor' in 'Asignaturas'). It's crucial to check 'Enforce Referential Integrity' to ensure that only existing professor IDs can be used in the 'Asignaturas' table. This prevents data inconsistencies. Also, 'Cascade Update Related Fields' and 'Cascade Delete Related Records' are selected to automatically update or delete related data across tables.
The newly created relationship can be tested by entering data. For instance, if an 'ID Profesor' of '2' is entered in 'Asignaturas' but doesn't exist in 'Profesores', Access will prevent the entry, enforcing referential integrity. Only valid, existing professor IDs can be assigned to subjects.
Relationships can be modified or deleted by right-clicking on the relationship line. Modifying allows changing parameters, while deleting removes the connection between tables. The video then proceeds to create a new table.
A new table named 'Notas' is created. It includes fields for 'ID Nota' (Autonumber, Primary Key), 'Calificacion' (Number, Decimal for grades with decimals), and 'Fecha del Examen' (Date/Time with short date format). Importantly, it also includes foreign key fields 'ID Estudiante' (Number) and 'ID Asignatura' (Number) to link to the 'Estudiantes' and 'Asignaturas' tables, respectively.
The 'Notas' table is added to the relationships window. To avoid errors, all other open tables must be closed. The 'ID Estudiantes' from the 'Estudiantes' table is dragged to 'ID Estudiante' in 'Notas', and similarly, 'ID Asignatura' from 'Asignaturas' is dragged to 'ID Asignatura' in 'Notas'. Referential integrity and cascade options are selected for both. This creates 'one-to-many' relationships, indicating that one student can have many grades and one subject can have many grades.
After establishing all relationships, the 'ID Alumno' field in 'Notas' is corrected to 'ID Estudiante' for consistency. The entire relationship layout is then saved by right-clicking and selecting 'Save'. Reopening the relationships window confirms that all established connections are present, ensuring a well-structured and logical database.