Docstoc

Final Database Project

Document Sample
Final Database Project Powered By Docstoc
					         IMSE 4410 FALL 2010




Final Database Project
    Machine Shop Database
          By: Stacy Thompson
             12/15/2010
Table of Contents:

   1.   Introduction
   2.   Tables
   3.   Queries
   4.   Forms
   5.   Reports
   6.   Switchboard
   7.   Conclusion




Stacy Thompson
                       Page 1
Introduction

        This database was created to enhance the management at a machine manufacturing company.
The database contains several tables, queries, forms, and reports that will be helpful to the company
when managing sales, customers, and employees. These resources will help managers track activity
within sales and marketing while managing the personal information of the stakeholders involved. This
database contains personal information about employees, customers, and customer’s spending habits.
Databases are crucial to the management of a company and are fairly simple to interpret. This paper will
help managers navigate through the tables, queries, forms and reports that I have created to prepare a
better working environment and in turn save money for the company.

Tables

         The first table that I created for this database was a customer information table. As shown the
blow the customers’ information table shows information about each customer in the companies
system. Although customers may be on the table they may not have orders placed at this point in time.
It is important to store the customer information anyway because a customer may decide to order
another time or the management might want to contact a customer if they haven’t ordered in a while to
see if they are interested in ordering.




The table above shows the customer ID, First Name, Last Name, Address, City, State, Zip Code, Email,
and Phone Number. This information will be helpful when customers place orders and orders need to be
shipped to them after production.

       The next table created was the employees table. This table shows the names of the employee
ID, employee first and last name, their job title, and salary.




Stacy Thompson
                                                                                                 Page 2
        The next table created was the Inventory table. This table shows the item ID, the name of the
item, Re-Order Amount, In Stock, Price, and Cost for items. The manager can access this information to
see what items are in stock, what needs to be ordered, and what items have the highest price.




         The next table that was created is the invoice table. This table was created to show the orders in
process at a given time for one customer. This table shows the most important information, the
customer subtotal, the discount, and the final total. The Invoice report following is a great way for
customers and managers to display the amount of money needed to complete the transaction. This
invoice is completed later in the report under the reports section.




Stacy Thompson
                                                                                                    Page 3
        The next table is the order summery table shows all the orders. Notice that there are many sub-
tables associated with each order. This shows the items for that order. This is helpful to show the
manager all the orders that pertain to that customer and that sales person.




        The next table shows all the orders that have been placed. This is helpful for the manager to sit
down with the workers and set out priorities as to which orders need to be completed first. This is also
the table that I liked to the order summerys table shown earlier.




        The last table that was made is the switch board Items table. This table will be explained in the
Switch board part of this report but here is a preview of it now.




Stacy Thompson
                                                                                                    Page 4
Queries

       Queries are used to narrow down information to make it easier to access for the manager. If the
manager is looking for just a specific customer’s invoice the query is used to create a report of just the
information for one customer. The first query that I made was used to show the best sales employee.
The query counted all the sales made by one employee and ranked them based on that.




The SQL code is as fallows, SELECT [Orders Stacy Thompson].[Employee Last name], Count([Orders Stacy
Thompson].[Employee Last Name]) AS [Number of sales]FROM [Orders Stacy Thompson]GROUP BY
[Orders Stacy Thompson].[Employee Last name];

       The next query that was made is the discounts query. This shows the discounts given to some
customers. Notice that there is no discount given to customers that do not spend over $1,000. If the
customer does spend over $1,000 then the discount associated with their transaction is 15%.




Stacy Thompson
                                                                                                   Page 5
The SQL code is as fallows, SELECT [Customer Stacy Thompson].[Customer ID], [Customer Stacy
Thompson].[First Name], [Customer Stacy Thompson].[Last Name], [Orders Stacy Thompson].Quantity,
[Inventory Stacy Thompson].Price, [Quantity]*[Price] AS Subtotal, IIf([Subtotal]>1000,[Subtotal]*0.15,0)
AS Discount, [Subtotal]-[Discount] AS [Final Total]FROM [Customer Stacy Thompson] INNER JOIN
([Inventory Stacy Thompson] INNER JOIN ([Order Summery Stacy Thompson] INNER JOIN [Orders Stacy
Thompson] ON [Order Summery Stacy Thompson].[Customer Last Name] = [Orders Stacy
Thompson].[Customer Last Name]) ON [Inventory Stacy Thompson].ID = [Orders Stacy Thompson].ID)
ON [Customer Stacy Thompson].[Customer ID] = [Order Summery Stacy Thompson].[Customer ID];

         The next query that was made is the invoice query. This query has a criteria function associated
with it. Meaning, when a manager accesses this information he or she is asked for the customer id to
show one customers information. The customer ids in this database range from 8 to 15.




The SQL code is as fallows, SELECT [Customer Stacy Thompson].[Customer ID], [Customer Stacy
Thompson].[First Name], [Customer Stacy Thompson].[Last Name], [Customer Stacy
Thompson].Address, [Orders Stacy Thompson].Item, [Orders Stacy Thompson].Quantity, [Discounts
Stacy Thompson].Subtotal, [Discounts Stacy Thompson].Discount, [Discounts Stacy Thompson].[Final
Total]FROM ([Inventory Stacy Thompson] INNER JOIN (([Customer Stacy Thompson] INNER JOIN [Order
Summery Stacy Thompson] ON [Customer Stacy Thompson].[Customer ID] = [Order Summery Stacy
Thompson].[Customer ID]) INNER JOIN [Orders Stacy Thompson] ON [Order Summery Stacy
Thompson].[Customer Last Name] = [Orders Stacy Thompson].[Customer Last Name]) ON [Inventory
Stacy Thompson].ID = [Orders Stacy Thompson].ID) INNER JOIN [Discounts Stacy Thompson] ON
[Customer Stacy Thompson].[Customer ID] = [Discounts Stacy Thompson].[Customer ID]WHERE
((([Customer Stacy Thompson].[Customer ID])=[Input Customer ID]));

       The next query that was created is the profit on sales query. This query shows the profit that is
made off of one sale after the discount. This is based on the selling price, discount, and cost to make the
item.




Stacy Thompson
                                                                                                    Page 6
The SQL code is as fallows, SELECT [Inventory Stacy Thompson].[ID], [Inventory Stacy Thompson].[Type
of Product], [Inventory Stacy Thompson].[Item], [Inventory Stacy Thompson].[Price], [Inventory Stacy
Thompson].[Cost for Items], [Orders Stacy Thompson].[Quantity], [Quantitiy]*([Price]-[Cost for Items])
AS Profit FROM [Inventory Stacy Thompson] INNER JOIN [Orders Stacy Thompson] ON [Inventory Stacy
Thompson].ID = [Orders Stacy Thompson].ID;

       The last query that was created is the Re-Order Quantity Table. This table shows the amount
needed to be re-ordered after the orders have been placed.




The SQL code is as fallows, SELECT [Orders Stacy Thompson].ID, [Orders Stacy Thompson].Quantity,
[Inventory Stacy Thompson].[In Stock], [Inventory Stacy Thompson].Price, [Price]*[Quantity] AS Cost,
[Quantity]-[In Stock] AS [Need to Order], IIf([Need to Order]>"10","10",[Need to Order]) AS [Order
Placed] FROM [Inventory Stacy Thompson] INNER JOIN [Orders Stacy Thompson] ON [Inventory Stacy
Thompson].ID = [Orders Stacy Thompson].ID WHERE ((([Quantity]-[In Stock])>0) AND ((IIf([Need to
Order]>"10","10",[Need to Order]))>0));

Forms

        Forms are used as a tool to add information in an orgnaized fashion. For example, some
companies might want to use a form to send to customers to be put on their mailing list or customer
directory. The first form created was a New Customer Form. This form as all the information from the
customers table in a form view to make it easier for customers to use. The form also shows the
infromation for the current customers.




Stacy Thompson
                                                                                                 Page 7
       The next form created was the new employee form. This form does about the same thing as the
new customer form but it is for new employees. This form also shows information on the current
employees.




       The next form that was created was the New Order Form.This form can be used to place orders
and change orders. This from also shows the information on the current orders that have been placed.




Stacy Thompson
                                                                                              Page 8
Stacy Thompson
                 Page 9
Also notice that if customers would like to place more orders and do not feel comfortable submitting
order forms online they are instructed to contact me, Stacy Thompson, at my email,
Sat2p3@mail.missouri.edu.

        The last form that was created is the Switchboard form. This form will be explained in detain the
the switchboard section of this report. Here is a preview as to what the first part of the switchboard
form looks like.




Reports

         The reports part of the database is used to show management important information needed to
run the company. The first form created was the Best Sales Employee Report. This shows the
information presented in the Best Sales Employees Query. Notice the logo that is presented on the top
of the report. This helps anyone looking at the report identify the report came from the machine shop
company.




Stacy Thompson
                                                                                                 Page 10
       The next report that was created was the customer discounts report. This reports all the
discounts given to customers.




        The next report shows invoices for the customers. This is a report that should be mailed out to
each customer prior to payment. Then after the company has received payment for the products
delievered then the invoice will be deleted from the database. Notice that each invoice shows only one
customer so the billing system can be made possible.




Stacy Thompson
                                                                                                  Page 11
        The next report that was created was the Hot Items Report to show the items that are being
ordered the most. This is shown by the qantities of items that are purchased. The hot items list is ranked
by decending order. The hottest items are on the bottom of the list. This way managers can see which
items need to be promoted more on the top of the report.




Stacy Thompson
                                                                                                 Page 12
       The last report that was generated the was the Profit on Sales Report. This report shows the
information in the Profit on Sales Query generated perviously.




Stacy Thompson
                                                                                               Page 13
Switchboard

         The switch board is used to switch between forms and reports easily. The switchboard should
appear when the report is opened and should be able to navigate through forms and reports to make it
easier for the manager to navigate through the database. The opening switch board looks like the print
screen shown below.




Stacy Thompson
                                                                                               Page 14
This switchboard dircects users to either the forms switchboard or the reports switchboard. Users are
then prompted to open certain forms or reports. The forms switchboard is pictured below.




The reports switch board is pictured below.




Users can click the radio buttons above to navigate through froms and reports and to go back to other
switchboards to visit different parts of the database. The switchboard items table is created to show the
naviagation that the switch board does to get to the forms that were asked for. This switchboard table is
pictured below.




Stacy Thompson
                                                                                                Page 15
Concusion

        Databases are used in industry to show infromation in a more organized and user friendly way.
Databases are crucial to the existance of a company. Errors occur far less times when a database is in
place. The information in databases can be used to the managers advantage. Through the use of queries
managers can access information in a secure, fast, and easy way.




Stacy Thompson
                                                                                              Page 16

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:1
posted:10/21/2011
language:English
pages:17