End-User Requirements
Mrs. Rachel would specifically like a new system, which can:
- Hold and identify accurate details of her customers, products and orders. Monitor and take control of stock.
- Keep information and automatic invoicing, print weekly/monthly invoice to all her customers and keep track or evidence of invoicing and receipts.
- Calculate the customer’s weekly bill and print receipt to them, and update their record after payment is received.
- Mrs. Rachel wants a system that would be secure, that no unauthorized personnel can use or gain access to.
- She wants a system with which she can easily update her customer, product, and order details.
- She wants a system which can keep track of customer’s invoices that have been paid and the ones that are still outstanding.
- She wants a system which can hold her Company name, Company logo, business address, order no, order date, product, quantity, unit cost, total price and signature on her customer’s invoice.
- She wants a system which can produce weekly report of all her owing customers.
Requirement Specification
User Friendly
One of the main features of this new system is that it will be very easy to use. I intend creating a user manual for the end-user of this system, and there will be possible solutions in the manual incase the user encounters problems with the system. There will be useful hints for running the system.
Expandable
Another important feature of this system is that it can be expanded to any length that she wants to. She would be able to enter new data and new customer details in seconds. She can update/ add new products to the database.
Since this system is been developed because Divine foods is increasing (and rapidly) the system will be useless in future if it can not be expanded or updated.
Easy to manage and maintain
The system will be very easy to manage or maintain, like I said earlier, she can update this system whenever she likes. She would use the system regularly, she can easily add or delete a record, and she can easily edit and read details of her customers and their orders. Orders can be entered daily as soon as they are been made. The database will give the orders automatic order number, therefore making the management task easier.
Advantages of this new system to the manual file based system.
Independence of Data: Any changes to the structure of the database, e.g. adding a table or a field, will not affect any of the programs that access the data.
-
Sufficient Information Available to End-users: In this database system, all information is stored together centrally. Authorized users have access to all this information. In a manual filling system, data is held in different departments, sometimes on incompatible systems.
-
Ease of use: The database system provides easy to use queries that enable users to obtain instant answer. In a manual file based system, a query would have to be specially (manually) written by a programmer/ secretary.
-
Greater Security and Integrity of Data: The database system will ensure that only authorized users are allowed access to data. Different users can have different access privileges, depending on their needs. In a manual file-based system, using a number of files is difficult to control access.
-
No data Redundancy: This occurs when data is duplicated unnecessarily. In a file based system, the same information may be held on several different files, wasting space and making up-dating more difficult.
-
Data Consistency: Each data item is stored only once, how ever many applications it is used for. There is no damage of an item, such as a customer address, being updated on one system and not another. If this happened the data will be inconsistent.
Performance Indicators
- This system should be able to print invoices with the company’s logo on them.
Design
Possible Solutions to the improvement of Divine foods system of operation
There are possible solutions to the development/design of the proposed new system of operation.
Manual System
Firstly, I cannot use a manual system for this new design; this is because it will be time consuming, and if a manual system is been used to administer her business, it might result in unnecessary errors in calculations and order taking. I cannot also use the existing system of accounts, because it is unreliable.
Word Processing Packages
I cannot use Word Processing packages for the implementation this system, this is because it does not have the required features for the development of the system. This proposed system needs spreadsheets, and databases.
I can only use it in typesetting, making the user manual, writing out my plans and design for the system; in fact I’ll use it through out the project but not for the main database.
Spreadsheets
Therefore I would use a spreadsheet system; this will store and manipulate tables of her customers, their orders and her products with numerical data. This spreadsheet system will store calculate and display financial information such as cash flow forecasts, balance sheets and accounts. This system will be able to set up different types of graphs perform many different functions and use macros, customizing and wizards.
Access Packages
Finally, I will use Access package because it is the appropriate program that can be used for the project. It can create spreadsheets, macros, forms, etc.
Final Choice of Hardware and Software
I have decided to that a new dell computer would be good for this new system, this is because the present system. Like I stated earlier, the computer owned by Mrs. Rachel is very old and very slow, due to poor maintenance, therefore new computer hardware would be needed.
These are other features of the computer.
- Excellent BRAND COMPLETE NEW System.
- Including Monitor Keyboard, Mouse
- 300W ATX Midi case
- 1GB (1000MB) PC2700 DDR
- 120GB HDD
- DVD DRIVE
- FAST CD WRITER 52x24x 52
- AMD XP3000+ processor
- Asus HIGH CHIPPED A7N8XX MOTHERBOARD
- 3.5' 1.44Mb floppy drive
- FX5600 256MB GRAPHICS
- 56k AMR Modem
- 5.1 CHANNEL SOUND
- 10/100 Network Adapter
- 4x USB Ports -
- 17 inch BRAND NEW MONITOR
The computer is been sold with a mouse, keyboard and a monitor, so Mrs. Rachel does not have to buy those anymore.
I have also decided to use a new HEWLETT PACKARD DJ9300 printer, because Mrs. Rachel does not have a printer. Here are some features of the printer
- Up to 4800 x 1200 dpi resolution
- Up to 14 pages per minute in black
- Up to 11 pages per minute in colour
- Prints up to A3 size
- Automatic paper type sensor
- Windows & Mac compatible
- USB & Parallel interfaces
- Software included
- Black Cartridge - HP 45
- 150 Sheet paper tray capacity
Justification of software
Operating System – Windows XP
I have decided to use this computer because it already has Windows XP Operating System. Microsoft® Windows® XP Home Edition is the next version of the Windows operating system, which is designed exclusively for home computing. Built on the solid foundation of Windows 2000, Windows XP Home Edition also sets the new standard in efficient and dependable computing. A new visual design, reliable Windows engine, and new Internet security features combine with capabilities for sharing the computer
This Computer also has the latest version of office tool (Office2000).
This will help me organize this small business projects with this essential kit. It has Works 7.0 which includes a word processor*, spreadsheet, database and calendar, and email and Internet tools. It will help me produce documents, create spreadsheets, keep track of my end-user’s contacts and appointments, and synchronize with email and the web. I will be able to share and edit word processing and spreadsheet files with Microsoft Word and Excel users, and customize my work with hundreds of new types of clip art. This latest version of Access has many new features that are not in the previous one (Access97)
Features of Access2000
Microsoft® Access 2000—the latest version of Microsoft’s desktop relational database management application. Allows simple database manipulation as well as full turnkey application creation. Seamless integration with the Microsoft Office family of applications.
- The Office XP Interface
- Adaptive Menus and Toolbars
- Task Panes
- Common Dialog Boxes
- New Features Across the Suite
- Data Recovery
- Security Options
- Administrative Features
- Working with Web Documents
- Web Page Features
- Web Pages/Data Access Pages in ACCESS 2000
- VBA Developers
- Macros, VBA and Modules (Overview)
- New Security and Conversion Issues
Feasibility studies
Technical Feasibility
Due to my interview and in depth investigation into the possibility of this project, I have come to conclusions that this project is technically feasible, as there is every equipment and software I need to make this project a success.
Economic Feasibility
The development of this new system is economically feasible because I have explored all the costs and benefits in a cost/benefit analysis, and have found out that the benefits of this system will outweigh the amount of money that will be spent on it. Mrs. Rachel looses a lot of money, due to incorrect invoicing and inconsistent system of operation.
There will be no cost for transfer of data, and there will be no cost also in training the users of this system, because Mrs. Rachel has a little understanding of Access packages, and she intends employing a qualified personnel to take care of the system, when she won’t be available.
From my enquiries about the hardware needed for this new system, I think it will cost Mrs. Rachel £800 to buy the hardware.
PC £600
Printer £200
These are the two basic things she needs at the moment, so I’ll just start up the design of the new system with this hardware.
Social Feasibility
There will be no loss of jobs due to the creation of this new system, no environmental effects, not even loss of skills, because Mrs. Rachel is the one and only person in her business, therefore there is no fear of job loss. Since she is already familiar with Access packages, there will be no fear of deskilling.
The development of this system will reduce the workload that she already has on her shoulder; it will improve her social life, because less work time would be needed. This system will also increase her level of service and customer satisfaction, and it will increase her company prestige.
INPUT – PROCESSING – OUTPUT
Input
I will enter all the customer details such as customer ID no, Name of the Shop, address, telephone number, etc. And the different products that are available to order, the products description, and their prices, and the details of the customer that is placing an order.
Once the data has been input from all the existing orders from the manual file based system, the other part of the input will be done by Mrs. Rachel after every order is been placed. She will do every other data entry through the Customer, Product and Order forms. This information will go into the Order table and then be stored on the system. It will be readable in related tables or forms.
Since Divine foods is expanding, more customers and products will be inputted into the system, by Mrs. Rachel.
Processing
The system must process the data that is been entered into the tables in a manner that it will be checked against all validation rules and input masks to make sure it is valid. These valid data will be sorted, and then be stored on the hard disk or any other storage means, e.g., CD ROM.
One of the most important processes, is that the system will do calculations based on every order been made. The system will calculate the total price due for each customer from the unit price of the products and the quantity ordered.
Since she has special customers which she gives 10% discount, the system will calculate the total pay of her customers and that of the special customers based on their 10% discount.
Output
The output of this system will be in form of queries and reports. When the data has been input into the tables, and forms, and then processed, it can be view in queries. Mrs. Rachel can view a list of her customers, available products, and the orders been made. She can also view print her invoices from the reports.
Database Design
Entity-relationship Diagrams
The first step in the design of this new system is to identify the entities involved. What are or whom am I holding data about. The most obvious entities in this system are CUSTOMER and PRODUCT.
Relationships in a database allow relations to be established between tables and so making the data co-dependent. Below is an Entity relationship diagram which will show all the relationships present in the tables I want to create.
Types of Relationships
Next, is to identify how these entities are related. Any customer can order as many products as possible, and many different customers may order the same product.
There are many kinds of relationships that I can use for this system, depending on the type of data that is involved. In this database, many customers order many products therefore it will be a many-many relationship as shown below.
There is a many-many relationship between these two entities.
Orders
Customer Product
As shown above, I cannot directly implement a many-to-many relationship; therefore I need a “link” table to specify which product a customer wants to order. Therefore I will name the third table the ORDER table.
Customer Order Product
The entity relationship diagram in this system is one-to-many and many-to-one. Each entity in this database must have one or more attributes called a primary key, which uniquely identifies it; for example Customer ID would be an obvious primary key of the Customer entity.
Key:
Many link
One link
Attributes
Each entity will have attributes that are associated with it. An attribute is a column of data in a table, a property or characteristic, for example the entity Customer will have attributes such as Customer ID, Customer Name, Address, etc.
There are various objects, which I’ll be working with in the design of this system. They are:
Tables: This is a storage structure for data that is composed of like columns of information, and repeated in rows. Or it is a collection of records, which can be viewed in rows and columns.
Forms: this is used to format the output of a query or table for display or to allow data entry. OR it is used for editing and viewing information.
Queries: This is used for asking questions or making changes to data contained in tables or other queries. It’s a simple language used to search a database.
Reports: This is a formatted output of data for printing; receives data from tables and queries.
Macros: This is a small program routine usually designed for performing tasks automatically.
Modules: This is used to customize database, using Visual Basic for Applications (VBA).
Naming Conventions
I will use various conventions in naming the objects listed above, because this will make the creation of the database easier to create and maintain.
First Level
Second Level
Table Design
The second stage in the design of this new system is the creation or design of Tables this database will be a flat file database. This is the process of storing data using just one table. There will be three main tables in this database they are Customer table, Product table and Order table
These are the most common data types that will be on the tables I will be using for this system:
Customer Table
The first stage of the table design is the design of the Customer’s table. This table will contain the customer’s ID, the name of the shop, the address of the shop, telephone number, and a column that asks if the customer is a special customer or not. This is because she has some specific customers, which she treats specially and gives discounts on bulk orders.
Naming Systems
Customer Table: This table will be defined as follows;
(Table Name: tblCustomer)
This is how the customer Table will look in design view
*I will define the TelephoneNumber field as txt because they stsrt with 0 and Number fields do not accept a field starting wit 0.
*This field will help Mrs. Rachel to know which customer is eligible for a discount in bulk order.
When I finish creating the Customer table, this is what it would look like in datasheet view.
Product Table
After creating the Customer table, I will create the Product table. This table will contain the ProductID, the ProductName, the UnitPrice and the quantityinStock. This time, the ProductID will be text and not AutoNumber.
The table will be defined as follows.
(Table Name: tblProduct)
As usual, this is how the table will look like in design view.
This is how the Product table will look like in datasheet view
Order Table
Finally, the last table in this system will be the Order Table. This table will hold information of orders that are been made by Customers. It will contain two key fields, the CustomerID and ProductID. This table will also contain other fields such as, OrderCode, Quantity, CollectionDay, TotalPrice, Paid, which will be a yes or no field and Balance, if the order was not paid in full.
I will use the lookup wizard, this is because it will make it easier for me, and even the end-user if I can lookup and enter the customer name rather than the CustomerID, and the Product name instead of the productID.
CustomerID will be looked up from the records on the customer table.
The table will be defined as follows:
(Table Name: tblOrder)
Now, this is how the Order table will look like when I create it in datasheet view.
Form Design
When I finish creating tables for all the entities in my database, and create relationships between them, I will the turn my attention to how the end-user (Mrs. Rachel) will enter data on to the database.
This is where Forms come in, (I have already defined forms in the early part of my design.).. I will design a special screen, which will be called forms to make data entry and editing easier for Mrs. Rachel. The forms will make the database easy to pilot, easy to use, instinctive, and user friendly
The Product Form (frmProduct): This form will be used to add and edit each product details. This form will be created using the Product table (tblProduct) as the source.
The Customer Form (frmCustomer): This form will be used to enter edit or delete each of Mrs. Rachel’s customer details. It’s source would be the tblCustomer.
Order Form (frmOrder): This form will be used to input customer details, the product they want to order. And every other order detail, such as the total price of the products ordered, the day of collection etc.It’s source would be from the three tables.( tblCustomer, tblProduct and tblOrder).
After creating these forms, I will create the switchboard forms after creating the queries and macros. Because these forms need macros to function effectively. These are the switchboard forms.
Customer Menu (frmCustomerMenu): This form will be the customer Menu form, which will link to all information relating to the Customers. It will contain the following commands: Add new Customer, Edit/delete Customer Record, View all Customer records, View owing ustomers, Print List of owing Customers, Print List of all the Customers and Go back to Welcome Form
Product Menu (frmProductMenu): This form will be the product menu form. It will link to all the forms, and reports concerning the products. It will contain the following commands: Add new Product, Edit product Details, View Products, Print List of Products and Go Back to Welcome Form.
Order Menu (frmOrderMenu): This form is will be the order menu form. It will link to all the forms and reports which contain order information. This form will contain the following commands: Enter new Order, Delete Order, View all Orders, Print all Orders and Go Back to Welcome Form.
Invoicing and Receipt (frmInvoicingandReceipt): This form will be the Invoicing and receipt Menu. It will link to the reports on Invoicing and receipts. It will cntain the following commands; Prepare Invoices, View Receipt of Payment, Print Invoices, Print Receipts, Go Back to Welcome Form.
Welcome Form (frmWelcome): This form will be the main Switchboard form. This will be the main menu of the database, all other forms will be linked to this form by the Macros. It will hold the Company logo, and I will place command buttons on this form which will link the form to frmCustomerMenu, frmProductMenu, frmOrderMenu and frmInvoicingandReceipts. The next command butons on this form will be the Exit Application button.
I will create the three forms above after creating the tables.
For the main time, I will be hand drawing the forms; this will give me an idea on what macros or how many macros I will need for the operation of the buttons in these forms, how I would like them to be, but there might be some changes in the implementation.
Queries
These are one of the most important objects in a database. It is used for converting raw data into useful form. It is a request to find all records satisfying certain criteria so that they can be displayed in forms or record.
A query can also be used to amend, replace, or update tables.
I will create six queries. These queries will help me when creating the reports. These queries will be defined as follows.
qryCustomer
The objects or fields that will be on this query will be from tblCustmer this query will be a list of all the current customers of Mrs. Rachel. The query will be defined as follows:
qryCustomersWithOutstandingDebts
This query will hold information about customers that have not paid for their orders. The objects or fields of this query will be from tblCustmer and tblOrder, this query will be a list of all customers of Mrs. Rachel who still owe. The query will be defined as follows:
I will type in No in the criteria field so that whenever the query is been run, it will show all the customers who have no paid for their orders.
qryOrder
This query will hold full information of all the Orders been made. I am creating this query because I will use it in creating the Order report. The fields in this query will come from the three tables ( tblCustomer, tblOrder and tblProduct ). The query will be defined as follows.
I will sort the ShopName field in ascending order, so that it will show the names of the shops alphabetically.
qryAvailableProducts
This query will hold a full detailed information of all Mrs. Rachel products. This query will be really helpful in creating the product report. With this query, Mrs. Rachel will be able to view, all the products that are still in stock, and the ones that are finished. The fields in this query will be from the product table (tblProduct).
I will sort the ProductName field in ascending order, to alphabetically list the products.
qryPaidOrders
This order will tell Mrs. Rachel the customers that have paid for all the orders they made. This query will halp me in creating the report on receipt. This query is defined as follows. This query will be based on tblCustomer and tblOrder
I will sort the ShopName field in ascending order. On the show row under the Paid column, I will type in Yes this will display all the Customers that have paid.
qrySpecialCustomer
This will be the last query in this databse. It will hold and show details of all the special customers of Mrs. Rachel. This fields in this query will come from tblCustomer.
I will sort the CustomerID field in ascending order, and I will hide the SpecialCustomer field, since it is the basis of this query. On the Criteria row under the SpecialCustomer field, I will type in Yes for the query to list all the special customer