DBMS

Database Management System (DBMS)

  • Definition: A Database Management System (DBMS) is a software system that enables the creation, management, and manipulation of databases. It allows users to define, create, maintain, and control access to the database for various applications and users.

Advantages of Using MS Access

  • User-Friendly Interface: MS Access provides a graphical user interface that simplifies database management compared to code-based systems.

  • Rapid Application Development: Users can quickly create database applications without extensive programming knowledge.

  • Integration with Other Microsoft Products: MS Access easily integrates with Word, Excel, and other Microsoft Office products, facilitating data sharing and reporting.

  • Cost-Effective: It is a cost-effective solution for small to medium-sized businesses that need database functionality without expensive licensing fees.

  • Robust Query Capabilities: MS Access allows users to perform complex queries using SQL (Structured Query Language) and its own query design tools.

  • Supports Multiple Users: MS Access allows simultaneous data access for multiple users in a networked environment.

  • Data Consistency and Integrity: Built-in features help maintain consistency and integrity of data.

MS Access Overview

  • Definition: MS Access is a desktop relational database management system developed by Microsoft. It combines the relational Microsoft Jet Database Engine with a graphical user interface and software-development tools.

  • Features:

    • Database Creation: Users can easily create new databases.

    • Table Design: Users can design tables that store data in a structured way.

    • Query Design: Users can create queries to retrieve specific data from the database.

    • Form Creation: Accessible forms can be created for data entry or presentation.

    • Report Generation: Generate printed reports based on data stored in the database.

    • Macros: Automate repetitive tasks within the database.

    • Data Import/Export: Easily import and export data from various formats, such as Excel, CSV, etc.

Steps to Create a Database in MS Access

  1. Open MS Access: Launch the MS Access application on your computer.

  2. Select New Database: In the start menu, choose to create a new database.

  3. Name the Database: Specify a name for your database in the specified field.

  4. Choose a Location: Select the folder where you want to store the database file.

  5. Create Database: Click on the 'Create' button to generate the new database file.

Steps to Create a Table in MS Access

Method 1: Using the Table Design View

  1. Open Database: Open your existing database in MS Access.

  2. Go to Create Tab: Click on the 'Create' tab on the ribbon.

  3. Select Table Design: Click on the 'Table Design' button to open the design view.

  4. Define Fields: Enter field names, data types, and properties as needed.

  5. Save Table: Click on the 'Save' button and provide a name for your table.

Method 2: Using Datasheet View

  1. Open Database: Open your existing database in MS Access.

  2. Go to Create Tab: Click on the 'Create' tab on the ribbon.

  3. Select Table: Click on 'Table' to create a new table in datasheet view.

  4. Add Fields: Add fields directly into the datasheet.

  5. Save Table: Click on the 'Save' button and provide a name for your table.

Steps to Save a Database in MS Access

  1. Open the Database: Ensure that your database is open in MS Access.

  2. Click on File Tab: Navigate to the 'File' tab in the ribbon.

  3. Select Save: Choose the 'Save' or 'Save As' option.

  4. Provide Name (if needed): If saving as a new file, enter the database name.

  5. Choose Location: Select the folder where you would like to save the file.

  6. Confirm Save: Click the 'Save' button to finalize the process.

Function of a Primary Key

  • Definition: A Primary Key is a specific choice of a minimal set of attributes (columns) that uniquely identifies a tuple (row) in a relational database table.

  • Purpose: It helps in enforcing entity integrity by ensuring that no two rows can have the same primary key value.

  • Characteristics:

    • Must hold a unique value for each record in a table

    • Cannot contain NULL values

    • Typically defined within the table's design settings in MS Access.

Steps to Add a Field in a Table

  1. Open Table in Design View: Open the relevant table in Design View.

  2. Insert Row: Click on a blank row at the bottom where a new field can be added.

  3. Define Field: Enter the field name in the first column and set the datatype in the second column.

  4. Save Changes: Click the ‘Save’ button to maintain changes.

Steps to Delete a Field in a Table

  1. Open Table in Design View: Open the table that contains the field you want to delete.

  2. Select Field: Click to select the field you wish to remove.

  3. Delete Field: Right-click on the field and select ‘Delete Rows’ from the context menu.

  4. Confirm Deletion: Save changes to finalize the deletion of the field.

Steps to Insert a Field in an Existing Database

  1. Open Table in Design View: Access the table where you wish to insert a new field.

  2. Click on Field Location: Position the cursor in the appropriate area to insert.

  3. Insert Field: Select 'Insert Rows' where applicable and define the new field name and attributes.

  4. Save Changes: Click the ‘Save’ button to apply the changes.

Definition of Sorting

  • Definition: Sorting in a database context refers to the process of ordering the records in a table based on one or more specified fields, either in ascending or descending order.

Steps to Sort a Set of Records in MS Access

  1. Open Table or Query: Access the table or query that contains the records you wish to sort.

  2. Select Column Header: Click on the header of the field you want to sort by.

  3. Sort Options: Use the Sort options in the toolbar to choose between ascending or descending order.

  4. Execute Sort: Click the 'Sort' button to apply the sort to the record set.

Explanation of Queries in MS Access

  • Definition: A Query in MS Access is a request for data from one or more tables that defines the specific information to be displayed based on conditions or calculations.

  • Types of Queries: Queries can serve various purposes, such as selecting data, updating records, or even executing actions.

Steps to Create a Query Using Query Design

  1. Open Database: Open your MS Access database.

  2. Create Tab: Navigate to the 'Create' tab on the ribbon.

  3. Query Design: Click on 'Query Design' to open the query design window.

  4. Add Tables: Add the tables or queries you want to use in the query by selecting them from the list.

  5. Select Fields: Drag and drop the fields you need to display in the query results.

  6. Define Criteria: Set criteria in the criteria row to filter records as needed.

  7. Run Query: Click on the 'Run' button to execute the query and view the results.

Definition of a Form in MS Access

  • Definition: A Form in MS Access is a user-friendly interface that allows users to enter, modify, and view records in the database without directly interacting with the table.

Steps to Create a Form in MS Access

  1. Open Database: Launch your MS Access database.

  2. Create Tab: Click the 'Create' tab in the ribbon.

  3. Select Form Wizard or Blank Form: Choose between using the Form Wizard to create a form step-by-step or a Blank Form for full customization.

  4. Add Fields: Drag fields from the field list into the form design area.

  5. Customize Layout: Rearrange and format fields as necessary.

  6. Save Form: Click the 'Save' button to store the form in the database.