Getting started with Microsoft Access DBMS
1. Create a Database File
● What it implies: This action refers to creating a new Access database file (.accdb) to store
your tables, queries, forms, and other objects.
● Steps:
1. Open Microsoft Access.
2. Click Blank Database.
3. Name your database (e.g., [Link]).
4. Click Create.
2. Create Table(s) in a Database
● Steps:
1. Open your database in Access.
2. Go to the Create tab and click Table.
3. Enter your field names and data types in the table design view.
4. Save the table with a name (e.g., Customers).
Nb: A table in MS Access can be viewed and managed in two distinct views: the Datasheet View and
the Design View. These views serve different purposes; Datasheet view displays the data in the table
in a grid format, similar to an Excel spreadsheet. It allows users to interact with the table contents.
Design View, on the other hand, is used to design and structure the table, focusing on defining fields,
data types, and properties.
3. Change Field Width in a Database
• "Field Width" refers to the visual size of the column displayed in the grid.
• You can achieve this by dragging the column edge in the datasheet view.
4. Add Fields in a Database
● What it implies: Adding a new column to an existing table.
● Steps:
○ Open the table in Design View.
○ Scroll down to the next available row in the grid.
○ Enter a new field name and choose the field type (e.g., phone_number as Text).
○ Save the table to keep the changes.
5. Delete Fields from a Database
● What it implies: Removing an unwanted field from a table.
● Steps:
1. Open the table in Design View.
2. Select the field you want to delete.
3. Right-click the field row and select Delete Rows.
4. Save the table after deletion.
6. Change Field Types in a Database
● What it implies: Changing the data type of a field (e.g., from Text to Number).
● Steps:
1. Open the table in Design View.
2. Select the field whose type you want to change.
3. In the Data Type column, choose the new data type (e.g., change phone_number from
Text to Number).
4. Save the table after making the changes.
7. Create Relationships Between Tables in a Database
● What it implies: Establishing relationships (usually through primary and foreign keys) between
tables to ensure data integrity and establish logical connections.
● Steps:
1. Go to the Database Tools tab and click Relationships.
2. Add the tables you want to relate.
3. Drag the field from the primary table (e.g., customer_id in the Customers table) to the
corresponding foreign key in the related table (e.g., customer_id in the Orders table).
4. In the dialog that appears, define the type of relationship (one-to-many, etc.) and set
referential integrity options (e.g., enforce the relationship, cascade updates).
5. Click Create to establish the relationship.
● For example, creating a relationship between the Customers and Orders tables using
customer_id as the linking field.
What is Referential Integrity
Referential integrity is a feature used to prevent orphaned records (records in a child table that
refer to non-existent records in a parent table) and to maintain the logical connections between data.
1. Cascade Update Related Fields
Automatically updates foreign key values in related tables when the primary key in the parent table is
changed.
Example:
● Parent Table: Students with student_id as the primary key.
● Child Table: Enrollments with student_id as a foreign key.
If student_id in the Students table is updated from 1 to 10, all corresponding records in the
Enrollments table where student_id = 1 will automatically be updated to 10.
Key Points:
● Prevents mismatched or orphaned records.
● Useful when the primary key in the parent table might change (though rare).
2. Cascade Delete Related Records
Purpose:
Automatically deletes related records in the child table when a record in the parent table is deleted.
Example:
● Parent Table: Courses with course_id as the primary key.
● Child Table: Enrollments with course_id as a foreign key.
If a course with course_id = 101 is deleted from the Courses table, all records in the Enrollments table
where course_id = 101 will also be deleted.
Key Points:
● Ensures no orphaned records are left in the child table.
● Prevents inconsistencies in the database.