Background information of the company
PDP systems is a computer memory manufacturer and peripheral distributor with a corporate office located in Fremont California. The company consists of 9 departments and 160 employees with 80 networked computer users in the corporate office. The data system for the company currently consists of some database and some file based systems used by departments and individuals.
The company is not only a memory manufacturer but also a peripherals distributor. Thus, product information such as price, quantity and date product received is essential for the business to operate. The product information is shared among eight of the nine departments. Presently the product information is maintained in three separate files; purchasing, accounting, and warehouse. Maintenance of three separate files of the same information is not only expensive, but also redundant and it slows business decisions down.
Purchasing is responsible for purchasing peripherals from vendors. The purchaser keeps his/her own records of the products on order. At the same time the warehouse department is responsible for entering the product data when the products are received. Then the accounting department receives the information to issue a check for the vendors.
Like dusting powder on fingerprints, new e-marketing tracking tools are out now that make vital information visible. Certain tools and feedback loops can show e-marketers which content customer segments are interested in by disclosing viewing patterns, which are then automatically turned into rich, detailed reports that clearly define customer trends and preferences. Placing value on learning is ...
The accounting department maintains the product information for the sales department because all sales orders must pass through the accounting department for a credit check. Accounting is also responsible for checking the sales orders for product pricing and sales amount.
The sales department requires pricing and quantity on the products from the purchasing department. The accounting department must gather information on the product from both the purchase and warehouse departments to verify the quantity and price. Then, when the sales order is approved it is given to data entry for processing the same information to the warehouse department for shipping and handling. Often times the information on the products is inconsistent. The inconsistent information on the products has cause a lot of friction between the purchasing, sales, and warehouse department. Which cause the company to lose a lot of its sales orders.
Objective of the project
The creation of a database that maintains product information, average cost of product on hand with new product, quantity, and date products received all in one database will provide the company with benefits such as; data consistency and reduction of administrative costs.
Goals of PDP Database Systems
+ The reduction of administrative and maintenance of product information costs by 15 %.
+ Eliminating the friction between departments on product information; such as pricing, quantity, and specifications, etc.
+ Eliminating the duplication of data and extra labor costs.
The maintenance cost for the new database system would need to be calculated so that it would actually provide the company with more benefits.
Proposed Database Scope
The proposed database will provide data access for 50 users, and only 10 of the users can modify or change the data on the products. The database would maintain data for approximately 368 types of products. Requirements for the new PDP Systems database are as follows:
What is a Database? A database is a software program arranged to collect, hold and process information. There are many software packages that help you handle information. However, what makes databases different is that once you enter the information into it, the database will operate the information in ways that allow you to analyze the data. It is designed in such a way as to make it easy to ...
1. Consistent product information such as product specifications.
2. Costs of products including shipping and insurance.
3. Cost averaging with existing inventory on hand and new product.
4. Track products (price, quantity, date) received from vendors and shipped to customers.
5. Consistency of products on order from purchasing and receiving at the warehouse.
+ The new database would require a new operating system that it is universal throughout the company.
+ Training would be required for all personals who would be using the database.
+ The sales price must be greater than the product cost.
+ The product quantity received and on hand must be equal or greater than the sales quantity.
+ Product shipment date and time must be later than the product received date and time.
+ Each customer is assigned to one sales representative.
+ Each sale or purchase order must have a date and time.
+ All purchase order and sales order must be approve by the accounting department.
+ Once the sales order is approved by the accounting department, it would be sent to the warehouse for processing and shipping.
+ Each vendor can only have one purchase representative.
+ Only the accounting department can access account transactions.
+ Only sales representatives are allowed to make sales orders for their customers.
+ Sales representatives cannot modify account activity and account history.
+ Each product must have a product identification number with descriptions stored in the database.
+ All customer accounts must have a shipping address and a contact s name.
+ All product Information must be enter in the warehouse.
+ Each Sales Order can have many products.
+ All products are assigned to one purchase order.
+ All orders are assigned to one EmployeeID and one CustomerID with at least one ProductID.
1. Every employees (Sales Representatives) assists at least one customers, every customers can only have one employee assisting them.
As you recall, data is a collection of facts (numbers, text, even audio and video files) that is processed into usable information. Much like a spreadsheet, a database is a collection of such facts that you can then slice and dice in various ways to extract information or make decisions. However, the advantage and primary use of a database over a spreadsheet is its ability to handle a large volume ...
2. The customer requests merchandise through the employee who enters order in the database.
3. The order checks the inventory for the quantity on hand.
a. If there is enough inventory, the products are sent to the customer through the shipment.
b. If there is not enough inventory, a backorder will be generated, creating a purchase from the supplier.
– The supplier receives the purchase and provides the products to be sold.
– The products are then prepared in the shipment to be delivered to the customer.
See Appendix for ER Diagram and Normalization Tables
Recommendations and Current Enhancements
To implement this project, we recommend using Microsoft software and platform due to the wide range of users. Instead of installing a new platform, we believe that using the existing platform along with upgrading the server and choosing new database software-Access 2000 is better.
As a database system Access has many good points and many bad points. Fortunately, most of the cons related to the ‘class’ of application that Access is. It is important to clarify the ‘class’ that Access falls into. Access is a desktop database package. It is not designed to compete with systems such as Oracle or SQL Server – full database servers – whose engines are superior in terms of speed and multi-user capabilities. This is usually the first perceived bad point: it does not provide good performance when run across a network and when more than a handful of people use it at once. However, if one considers what Access is designed as – a desktop database with limited multi-user capabilities – its performance capabilities are good. In addition, Access can and does make good front-end package larger engines such as Oracle and SQL Server. Against other desktop database package, Access has one huge advantage. It is likely that you are running Windows as your operating system and using Microsoft Office as your application base. Access integrates well with these packages and data transfer between Access and the other Office components is relatively easy. In addition, against the other desktop databases Access is both rich in features and powerful. Access 2000 probably puts Access above any other desktop database you can buy. Access has a user-base of millions and therefore there are a lot of add-ons and other third party software available. Finally, Access can be used by almost anyone of any level. Beginners can get to grips with it very easily using the wizards and the easy-to-understand interface whilst developers can push it to its limits and do some extraordinary things with it. It is a system for everyone created with a mixed user-level in mind.
. Discuss what allows Internet access to users, with particular reference to protection against threats: viruses, spamming, and hackers. In our network design, Internet access will be granted through a centralized point located at the Los Angeles Data Center. Corporate policy dictates that the corporate internet connection is to be utilized for business purposes only, and use for any purpose other ...