Case study in access

Mukesh Singh

Case study in access

Sure, I can provide a sample case study for creating a database in Microsoft Access.

Case Study: Inventory Management System

A company that sells electronics and computer peripherals needs a system to manage their inventory. They want a database to track their products, suppliers, sales, and stock levels. The company has several branches and each branch has its own stock levels.

Here are the requirements for the system:

  1. Products:
    • The database should keep track of each product’s name, description, model number, price, and stock level.
    • Each product can have multiple suppliers.
    • Each supplier has a unique supplier ID, name, address, and phone number.
  2. Sales:
    • The database should keep track of each sale made, including the date, the customer’s name, and the product sold.
    • The company offers discounts for bulk purchases. The discount percentage varies depending on the quantity purchased.
    • Each sale can have multiple items.
  3. Stock Levels:
    • The database should track the stock levels for each branch.
    • Each branch has a unique branch ID, name, and address.
    • Each branch can have multiple products in stock.

To create this database in Access, we would need to create the following tables:

  1. Products:
    • ProductID (Primary Key)
    • ProductName
    • Description
    • ModelNumber
    • Price
    • StockLevel
  2. Suppliers:
    • SupplierID (Primary Key)
    • SupplierName
    • Address
    • Phone
  3. Products_ Suppliers:
    • ProductID (Foreign Key to Products table)
    • SupplierID (Foreign Key to Suppliers table)
  4. Sales:
    • SaleID (Primary Key)
    • SaleDate
    • CustomerName
  5. Sale_Items:
    • SaleID (Foreign Key to Sales table)
    • ProductID (Foreign Key to Products table)
    • Quantity
    • DiscountPercentage
    • TotalPrice
  6. Branches:
    • BranchID (Primary Key)
    • BranchName
    • Address
  7. Branch_Inventory:
    • BranchID (Foreign Key to Branches table)
    • ProductID (Foreign Key to Products table)
    • StockLevel

Once the tables are created, we can create forms to enter data and run queries to retrieve data from the database. For example, we could create a form to add new products and suppliers to the database, and a query to retrieve the stock levels for a specific product at each branch.

This inventory management system will help the company keep track of their products, suppliers, sales, and stock levels, and allow them to make informed decisions about purchasing and restocking inventory.