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:
Obtain a general description of the company operations
Interview the shop manager
Interview the mechanics
Create the file (table) structures
Draw a data flow diagram and system flowcharts
Create a conceptual model using ER diagrams
Normalize the conceptual model
Load the database
Create a description of each system process
Create the application programs
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:
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?
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.
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.
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:
What are the current departments? Each department will have a manager, employees and tasks to complete which is necessary information for the database.
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.
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.
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.