Working with Multiple Tables Notes
Editing and Deleting Tables
- Editing Tables: To change the structure of a table, such as adding, deleting, or modifying fields:
- Open the table in Design View.
- Modify field names, data types, or add new fields as needed.
- Deleting Tables: If a table is no longer needed, you can delete it:
- Right-click on the table name and choose “Delete” from the menu.
- Be cautious, as deleting a table permanently removes all the data within it.
Relationships Between Tables
- Relationship: A relationship is a link between two tables based on a common field. Relationships help connect related data across multiple tables, making it easy to organize and retrieve information.
Types of Relationships
- One-to-One Relationship: Each record in Table A has only one matching record in Table B, and vice versa. Example: A student table and a library card table where each student has only one library card.
- One-to-Many Relationship: A record in Table A can have multiple matching records in Table B, but each record in Table B has only one match in Table A. Example: A customer table and an orders table where each customer can place many orders, but each order is linked to only one customer.
- Many-to-Many Relationship: Records in Table A can have multiple matching records in Table B, and records in Table B can have multiple matches in Table A. Example: A students table and a courses table, where each student can enroll in multiple courses, and each course can have many students.
Advantages of Relating Tables in a Database
- Data Organization: Relating tables reduces data duplication and helps keep the database organized.
- Easy Data Retrieval: Related tables make it simpler to gather related data through queries.
- Improved Data Accuracy: By connecting tables, databases can prevent redundant or conflicting information.
- Efficient Data Management: Managing related tables improves performance and helps save storage space.
Creating Relationships Between Tables
- To create a relationship:
- Open the Relationships window in LibreOffice Base.
- Drag a field (usually the primary key) from one table to the matching field in another table.
- Set the relationship type (One-to-One, One-to-Many, etc.) and confirm.
Referential Integrity
- Referential Integrity: This rule ensures that relationships between tables remain consistent. If a record in one table is related to records in another table, referential integrity makes sure that changes in one table don’t break the link in another.
- Purpose:
- Prevents accidental deletion of records that are related to others.
- Keeps data accurate and reliable across related tables.
These concepts form the foundation of working with multiple tables in a database, helping students understand how data can be efficiently managed and interconnected for better organization and retrieval.