30-01-2013, 12:23 PM
The Relational database Design/Modeling/Implementation in MySQL & Oracle Project
1The Relational database.pdf (Size: 1.33 MB / Downloads: 53)
Introduction
Motivation
There are a lot of enterprise management software that allows a business to track almost every
imaginable aspect of the business. For example, in the manufacturing industry, there are software
packages that can track inventory levels, help manage requests for quote and work orders, levels of
productivity even down to the level of tracking the percentage of time a particular machine tool is in
use. These software packages can be very expensive to the point that they are out of reach of small and
medium sized businesses. In recent years, with the advent of the Internet, Service Oriented
Architectures (SOA), and web-based applications, the features of these expensive software applications
have started being available to smaller business in a cost-effective manner. For this project, the goal is
to create a small part of one of these possible web applications that would help a small manufacturing
business track its raw materials inventory. While I do not know if there are existing web applications
that serve the same purpose, assuming this project is properly designed and implemented it could easily
be used by many businesses.
Project Description and Scope
I propose to create a database and user interface that would allow a manufacturing supplier to be able to
track its raw materials inventory. This raw materials inventory tracking system (RMITS) will allow a
supplier to track amounts of the raw materials it has on hand and allow those amounts to be
dynamically adjusted when work orders are accepted and when materials are purchased. To complete a
work order to produce a certain product, some amount of raw materials is required. When work orders
are accepted, the inventory for the required material should be reduced from the available inventory.
When a manager purchases additional raw materials the inventory for the newly purchased materials
should be increased. The system should be able to show the current amount of raw materials currently
on hand, show a historical record of when materials are added or removed from inventory and for what
purpose.
Study of System Functional Requirements
Introduction
To help evaluate the needs and requirements of the manufacturing company and its employees who will
be using the RMITS system, a study of the system's functional requirements is performed. Since this
project is not being designed with any one particular company or a particular set of users in mind, the
interviews and business forms provided in this chapters are fictional. Section 2.2 includes fictional
interviews with the manufacturing business' owner, accountant and head machinist. Section 2.3 shows
example forms for what the manufacturing company's employees and customers would fill out to
provide to update information about the current raw materials inventory and projects to be performed.
Section 2.4 shows example forms that produces information about the current raw materials inventory
for the company and projects performed by the company.
Business Owner
As a small machining shop, we've never had a complete computer system to track all aspects of the
business. The biggest thing we are lacking now is a way to track our raw materials inventory. If I want
to know how much of a particular material we have on hand I'd have to go back to the store room and
physically count it. So when we get a work order in from a customer we don't necessarily know if we
have the materials on hand to complete the work order or if we need to place an order with one of our
vendors. This also affects how we bid on RFQs since we don't have good accounting for the cost of
what we have in stock versus what we need to order.
Accountant
I am responsible for determining the amount of profit the company earns. One difficulty in doing this
is knowing, when we complete a work order, how much it cost in raw materials. If we purchase
materials specifically for the work order then figuring the cost out is trivial. However, if the machinist
pulls the material from the store room it is much harder to determine how much the material cost is
since we don't have a good method for tracking our inventory. I would like to calculate the cost of the
materials used for a work order using a FIFO method.
Machinist
One of the big issues I face is making sure I am using the correct sized material for the project I'm
working on. We don't want to create too much waste. So if I need 10 pieces of a 5 by 5 steel block I
want to use that instead of a 7 by 8 steel block. The problem is I have to hunt around in the storage
room to see what the different sizes and shapes of materials we have are. If I could see a list that shows
for sure what is available that would be very useful.
Design of User Reports and Screens of the User's Views
The website which contains the user interface to RMITS will provide a number of user reports and
screens for filling in forms for data entry. This section provides a list of those reports and form screens
with brief descriptions of their purpose.
• Inventory Report: The inventory report will provide a list of all raw materials that are currently
being stored with the manufacturer. The report will essentially be a table showing, for each
material, the material's type, shape, dimensions, and how many of this raw material is on hand.
The column headers for this table will be clickable to allow for the information in the table to be
sorted based upon the header that was clicked.
• Work Order Status: This is a report to show the information available about a particular work
order. The report will include information collected about the work order by that period of time
which will include at least the RFQ information. Depending on the status of the work order it
may include RFQ bid amount, machinist assignment and specifications, and final completed
project information.
• Purchase Order Form: This is a form that will allow the user to enter information about a
purchase order. This form should be filled out just prior to sending the order to the vendor. The
information provided will include that materials that are expected to be purchased.
• Purchase Receipt Form: When a vendor fulfills a purchase order and delivers the materials to
the manufacturer, the purchase receipt form is filled out. This form will include the final price
of the purchased materials and quantities that were delivered. Any comments from the vendor
about the materials purchased can also be provided.