Data Manipulation Essay Example for Free

Data Manipulation Essay The tables required in the system will be Customer, Products, Staff and salary. The individual fields in each table will be as follows: CUSTOMER (CustomerID, Surname, FirstName and customer address) PRODUCTS (ProductID, Title, Description, Type and Amount) STAFF (StaffID, Name, address and contact number) STAFF SALARY (wages per hour, hours per day and days per week) There will be two reports produced. To produce a report for the products purchased; a query will be run to combine the data from the customer table and the product table. The user will be able to specify the products bought by a customer with the entire amount calculated to give the customer benefits of getting more offers. The second report will print the amount of salary each staff earns per hour, hours/day and days/week. The user is asked to enter the StaffID and name to enter its database. Output The two reports should be able to be viewed on screen with an option of printing it as well. The product purchased report should be printed so that they can give some discounts on particular customers who shop a lot at the store. The staff salary report should also be printed before any meetings so that they can decide on who to get paid more and who shouldnt. Backup / Security Strategy The main computer that has all the data needed should be backed up on a CD every week or even when a large amount of data has been input to the system. This CD should be kept away the main computer incase of a fire, theft or flood. There should be password security on the system so that people cannot hack into it. Access has a password protection before entering the data so that no one can hack into it. Part Three Initial Designs Form Design There will be 4 forms for the data entry. Frm Customer This form will be used for entering customer details. My initial design is shown below Frm Product This form is used to enter all the products available at the store and the prices of each product. Frm Staff This form will be used to enter the staff details. Frm Salary This form will be used to enter the salary of each staff working in the store. Report Design There will be 2 reports. These can be either printed or viewed on screen. Report customer list This report will come from a query, which combines data the customer table and the product table. The user will be able to enter the Customer name to enter the report for the customer report This initial design is shown below Report Staff Salary This report will show how a staff details and how much they earn and how are they rated between 1 to 5 stars for working. Mr. Smith can rate this every week. Menu Design The menu structure for this system will look like this: User Feedback on Initial designs The initial designs were shown to the manager of the store for his feedback and approval. Mr. Smith had the following comments and suggestions: I have looked at your designs very carefully and they appear to be useful for me and I did however think of 1 or 2 changes that could perhaps change 1) The product types should include which category it is in such as racing, shooting. It would also be easier and helpful if everytime you dont have to type in what category it is in. 2) Can the 2 reports be printed all together with a click of a button because it can be time consuming 3) Can you also do something that can calculate the amount of the products and salary for the staff Final Design Table design Four tables are required in this system. They are related as follows: The tables are defined as follows: Form Design The form designs were amended in accordance with Mr. Smiths suggestions and final designs are shown below. There is a combo box added for the category of the product, which can be easier to select the category instead typing it all the time. Query Design I will need to create 2 different queries. These will form the sources for my reports. QryCustomerlist This query will combine data from 2 tables. Using the customer table and product table. It will ask the user to give the customer name. This will then be asked to find out the customer details or the product details. For e.g. [Enter Customer name: ] QryStaffSalary This query will combine from the two tables, which are the staff table and the salary table. It will ask the user to enter the staff name to see the list of that particular staff detail For example: [enter staff name: ] Implementation We will be using Ms Access to make the system needed. I will show in steps how to create this type of a system. First open Ms Access then you will see Now the same way I need to enter 30 customer list Now we can type the detail of 30 customers without entering the product ID With the same way I also need to create the product table. After creating it the design looks like this Now I need to enter the details of the products Now we know the product ID so then I will need to add them in the customer table which does not need to be in an order because a customer may want something else. Now we need to create the relationship between these two table. Like these tables I created I will need to create another 2 more. After creating the table page will look like this After creating the staff tables I now need to create the details of them. Now for the staff salary table I need to enter the details Now I need to create the relationship between the Staff and the Staff Salary the same way as I did for Customer an Product. So open the relationship window Now as I have created the relationship between the staff and staff salary when I will open their table it will show After creating the tables I now need to create 2 query. Now I need to create a formula for which the system can calculate the total amount of the product from the quantity bought Now for calculating the VAT on these products I will do: Now I need to calculate the bill amount, which is, Amount adds the VAT. To check if these formulas work I will now test the system After seeing the amount formula working I will now need to save the work and so I will: This is how it will look like when it closes the query For the same way I need to create another query, which relates with staff salary and after it has been done it will look like this Now that Tables and Querys are finished I now need to create forms. Now need to align these in order Now I need to create title for the form . so I need to open the toolbox. It will look like this when I open the toolbox then where it says Aa click on it and make a box on the form Now I need to type in the title name. Now I have to organize the font and the font size Now I need to create command buttons for the system. After creating the commands I now need to create a the background colour now I need to save this form For the same way I will create another 4 forms and after I created it, it looks like Now I need to create reports Now finally I need to create a Main Menu, which indicate the user to use the system easily. Main Menu will also be create in the Form