Chris Bell Chris Bell 'A business that makes nothing but money is a poor business.'
- Henry Ford

About Me >>   Bachelor's Degree >>   IT-330 Database Design and Management

SNHU - IT-330 Database Design and Management
Written by: Chris Bell - June, 2013

Database Design Order of Activities

The correct order of activities is as follows:

  1. Obtain a general description of the company operations
  2. Interview the shop manager
  3. Interview the mechanics
  4. Create the file (table) structures
  5. Draw a data flow diagram and system flowcharts
  6. Create a conceptual model using ER diagrams
  7. Normalize the conceptual model
  8. Load the database
  9. Create a description of each system process
  10. Create the application programs
  11. Test the system

Some various modules the system should include should be geared towards their multiple locations because there needs to be reports that compare sales and inventory in each location. This will help determine the amount of products to keep in stock based on the history. One location may require more than another depending on the demand of each office. Obviously, 1 manager can handle at least 6 mechanics and a receptionist, but what's the maximum number of employees that can be managed by a single manager? That can be implemented in the database to be sure the corporate office doesn't lack in certain areas due to over extending the management of a particular office.

Data Dictionary

The data dictionary will help develop the system, especially when multiple programmers access it, because it helps all of the designers and programmers understand the flow of the particular company. The company might have a maximum of 10 employees per location, only one manager per location or a maximum of three branches which can all be found in the data dictionary.

Integrated System

It is ABSOLUTELY necessary to have an integrated system! An integrated system will allow the company to track all inventory in each location separately and allow for each location to share inventory when needed. For instance, if Branch A ran out of a particular tire that Branch B has in stock, then Branch A would be able to immediately see it in the integrated system and request it. Comparing sales and usage of inventory will be helpful for management to make decisions for the future of the company.

Top Down Method of Design

I like the top down method of conceptual database design because that's the standard way of creating a flowchart or a website. I wouldn't want to create the bottom pages of a website only to work my way back to the homepage. Nor would I like to start an employee flowchart at the bottom level of a company. It seems to make more sense to start from the top and work down.

Here are 4 reports that a company should have:

  1. Total YTD sales for each location: It's difficult to stay completely updated with technology as your company is in the growth process. Therefore, if the company already had a DBMS before acquiring another company they may not have updated their system to provide separate sales figures for each location. Simply seeing the sales figures of each location can create questions for management such as: Why does Branch C have twice the sales of Branch A? Is it the location, management or service creating this difference?

  2. Inventory usage for each location: The usage of inventory will help the purchasing department buy certain products for each of the locations. For instance, New England customers may have more winter related repairs that require different materials than that of a Florida branch. Once a few months of history is developed more accurate decisions can be made.

  3. Time to complete each service: The average of the total time it takes to repair or replace brakes will allow management to get a better idea of the amount to charge for each service they offer. Once they have the average time they can compare it to competition and come up with a solid and attractive price to charge its customers.

  4. Total employee and branch hours: Pulling a report for the total hours of Branch A in order to compare it to the total hours of Branch B determine which branch is more productive. It could also mean that a certain branch has better access to supplies, better education or better management allowing them to get work done faster without sacrificing quality.

Four questions for a food manufacturing plant:

  1. What are the current departments? Each department will have a manager, employees and tasks to complete which is necessary information for the database.

  2. Do you want to track inventory for each "total product" or each completed package? Since there are different sizes of each snack food it's assumed that the inventory should state how many of each package are in the warehouse, or refrigerator, instead of the total amount. This will help the sales team see exactly what's in stock and ready to ship.

  3. Do you have spoil dates? Since food can spoil it's a good idea to put an "in-date" with a 20 day prompt to throw away all fruit or dairy products. A simple mathematical equation can be used by adding 20 days to the system "in-date" of spoil-items.

  4. How much refrigeration space is there? Refrigeration items can have a designation within the system so that a report will show all items currently in the refrigerator. This will help the purchasing department limit refrigeration items so that all items can fit that need to be cool. If the space is full and more items need to be ordered a prompt will show the purchaser that the refrigerator is completely full.

System Development Life Cycle

The System Development Life Cycle will be difficult in the "planning" area because it's hard for end users to understand how to use the database even when it's complete. Careful planning will need to be done to ensure the easiest of usage for the company it's intended to serve.

Database Life Cycle

The Database Life Cycle will prove to be difficult in the maintenance stage of the process. End users that enter a NEW ITEM into the database will need to make sure they include the "R" for a refrigeration item or it will not pop up in the report designed to show the total refrigerator commitment level. I feel this could complicate the maintenance area because letting the end users update things on their own will create problems for both parties.