Referential Integrity in LibreOffice Base

Keeping Database Links Correct

1 / 30
volgende
Slide 1: Slide
newEditor

In deze les zitten 30 slides, met interactieve quizzen en tekstslides.

Dit is niet oké

Onderdelen in deze les

Keeping Database Links Correct

Open from students' prior knowledge of tables, records and fields. The lesson thread is that related tables are only useful if their links stay valid over time.

Learning goal

I can explain and apply referential integrity in a relational database, and show how LibreOffice Base maintains it.

Deze slide heeft geen instructies

What words come to mind when table links go wrong?

Use this to surface prior ideas such as missing data, wrong links, errors, duplicates, broken records or reports. Connect their words to the lesson focus on keeping links valid.

Primary keys and foreign keys

  • A primary key uniquely identifies each record in a table

  • A foreign key stores a value that links to the primary key in another table

  • Together, they connect related tables in a relational database

  • Example: `StudentID` in Students links to `StudentID` in Results

Keep the example simple and consistent across later slides, such as Students and Results or Customers and Orders.

How relationships work

  • One record in a main table can be linked to many records in another table

  • The foreign key repeats the matching primary key value

  • The link is based on matching values, not on record position

Stress that relationships depend on field values and field types matching correctly. Students often think tables are linked visually rather than by stored key values.

What referential integrity means

Referential integrity means every foreign key value must refer to a valid existing primary key value in the related table. This rule prevents broken links between records.

This is the core definition for the lesson. Keep returning to 'valid existing primary key value' as the test for whether a link is correct.

Why this matters

  • Keeps data consistent across tables

  • Improves accuracy by stopping invalid links

  • Makes queries and reports more reliable

  • Reduces duplication and confusion in large databases

Link this to practical outcomes: if links are wrong, reports can show missing, duplicated or misleading information.

Enforcing valid links between tables

  • A foreign key cannot contain a value that does not exist as a primary key in the related table

  • The linked fields should use compatible data types and meanings

  • Relationship rules are used to stop invalid records being saved

You can point out that correct field design matters before integrity rules can work properly: matching field type, size and purpose.

What does a foreign key do in a relational database?

A

Stores repeated data

B

Links to another table

C

Sorts the records

D

Names the database

Checks the core idea from slides 2 to 4. Distractors reflect common confusion between keys, data storage and table features.

Complete the database summary

A primary key uniquely identifies each record in a table. A foreign key stores a value that links to another table. Referential integrity means each foreign key matches a valid existing primary key value.

primary key
foreign key
integrity
primary key
relationship
record
query
field

Echoes the exact lesson language so students secure the load-bearing terms before moving on.

Consistency and accuracy

  • Consistency means related tables agree with each other

  • Accuracy means each link points to the correct record

  • Referential integrity protects both at the same time

Distinguish the two terms clearly: consistent data can still be wrong if entered incorrectly, but referential integrity specifically guards the correctness of links.

Insert operations

  • When adding a record with a foreign key, the matching primary key must already exist

  • If the related main record is missing, the new linked record should not be accepted

  • This prevents invalid links from entering the database

Example: a Results record for StudentID 205 cannot be added unless StudentID 205 already exists in Students.

Update operations

  • Changing a primary key can affect every related foreign key

  • If key values stop matching, links become invalid

  • Integrity rules help prevent or control changes that would break relationships

Students may not expect primary keys to change, so explain that updates are risky because other tables depend on those values.

Delete operations

  • Deleting a record from the main table can leave related records without a valid link

  • These broken records are a major integrity problem

  • Database rules can block the deletion or handle linked records in a controlled way

Use an example such as deleting a student while their results records still exist.

What can go wrong

Insert with missing parent key → invalid linked record

Update key without matching changes → mismatched keys

Delete main record without control → orphan records

This slide summarises the cause-and-effect pattern across insert, update and delete. Read each line as a practical database risk.

Orphan records

An orphan record is a record with a foreign key value that no longer has a matching primary key in the related table. It still exists, but its link is broken.

This is one of the most important error types for examination language. Make sure students can define it precisely.

Mismatched keys

  • A foreign key value does not match any primary key in the related table

  • This may happen through incorrect entry or unsafe updates

  • Queries, forms and reports may then show incomplete or misleading data

Differentiate mismatched keys from orphan records: both are invalid links, but orphan records often arise after deletion, while mismatches can also come from entry or update errors.

Spotting integrity problems

  • Missing related records in forms or reports

  • Key values that should match but do not

  • Records in a linked table that have no valid parent record

  • Errors when trying to save inconsistent data

Keep this practical: students should recognise that integrity problems show up in outputs, not only in table design.

Which action can create an orphan record?

A

Deleting a main record

B

Adding a valid record

C

Sorting a linked table

D

Renaming a field label

Targets the cause-and-effect summary in slides 8 to 12. Students often confuse general edits with integrity-breaking operations.

Complete the operations summary

During an insert, the matching parent key must already exist. During an update, changing a key can break existing links. During a delete, related records may become orphan records if the main record is removed.

insert
update
delete
orphan
report
query
relationship
duplicate

Reinforces the different effects of insert, update and delete using the same vocabulary as the content slides.

LibreOffice Base and relationships

  • LibreOffice Base can define relationships between tables

  • These relationships connect a primary key to a foreign key

  • Once set correctly, Base helps maintain valid links between related records

Keep the focus on Base as the practical tool named in the syllabus, not on relational theory alone.

Defining table relationships

  • Choose the related tables

  • Select the matching key fields

  • Create the relationship between primary key and foreign key

You can mention the Relationships view in LibreOffice Base, but avoid overloading the slide with interface detail.

Maintaining integrity in Base

  • Base uses relationship settings to protect linked data

  • Invalid foreign key entries can be prevented

  • Changes and deletions in related tables can be controlled to avoid broken links

Tie this back to insert, update and delete operations from earlier slides so students see the software enforcing the database rule.

Applying the idea in practice

When a database is designed in LibreOffice Base, referential integrity means linked tables stay trustworthy over time. Correct relationships stop invalid entries, reduce errors and keep reports dependable.

This lands the lesson: referential integrity is not just a definition, but a design rule with practical effects on real database use.

How does Base help keep links valid between tables?

Expected elements: LibreOffice Base lets users choose related tables, match the primary key to the foreign key, and use relationship settings to stop invalid entries or control changes and deletions.

In LibreOffice Base, what is matched to create a relationship?

A

Two record colours

B

Two table names

C

Matching key fields

D

Matching report titles

Checks practical understanding from slides 15 to 18: relationships are created by matching a primary key and a foreign key.

What is one idea from this lesson you will remember?

Discussion prompt only. Encourage students to mention one clear idea such as valid links, orphan records, insert/update/delete effects, or how Base enforces relationships.

What helped you understand referential integrity best?

Discussion prompt only. Look for comments about examples, the Students and Results model, the insert-update-delete sequence, or the Base relationship steps.

Glossary

relational database - a database that stores data in related tables

primary key - a field that uniquely identifies each record in a table

foreign key - a field in one table that links to a primary key in another table

relationship - a defined link between tables using matching key fields

referential integrity - the rule that every foreign key must match a valid primary key

consistency - the state where related data agrees across tables

accuracy - the state where data and links are correct

Deze slide heeft geen instructies

Glossary

insert - adding a new record to a table

update - changing data in an existing record

delete - removing a record from a table

orphan record - a record whose foreign key has no matching primary key

mismatched keys - key values that should match across tables but do not

LibreOffice Base - a database application used to create tables and relationships

Deze slide heeft geen instructies