Goal of this essay is to critically evaluate the database design and development techniques using MS Access DB and Excel functions. Mail order system for Memorabilia mail order business is designed and developed based on the text data given for customers, orders, items and suppliers and the detailed explanation is as given.
The information of an organization is stored collectively and this is known as the data base of an organization. Software that is created to manage such databases is known as the database management system. It provides the methods to recover data, manage data, create data and update it to keep the database up to data with the latest information. The D.B.M.S is also the interface between the users, their queries and the database. (Tech Target,2016)
A relational database is made up of multiple sets of data that are formatted into tables and columns, records and rows. It shows a definite relationship between the different data set tables that are graphically represented. This makes searching for data easier and the tables can communicate data accurately. The organization and the reporting formats are also maintained in relational databases. The language used in relational databases is SQL. It is a user application that makes the programming for database interaction simple. It is based on the concept of mapping data which is a mathematical function.(Techopedia,2017)
The databases carry out the functions of the business processes maintain the important business intelligence to provide the business with a competitive advantage. It also carries out several thousands of transactions per day and makes data readily available. The information in database management systems is kept short and concise and it appears only once this reduces the redundancy.
The same data is not repeated making it cost effective and efficient. Information can be retained at lower costs. The data in a DBMS is consistent and accurate which shows the integrity of the data. Different uses can access the same data simultaneously reducing the delays in files reaching other member of the project and delaying the project phases. The DBMS minimizes the loss of data and prevents errors from taking place which is why it is suitable for this project (Centriq,2017). For the current decision support system of Memorabilia mail order business, database functions of Excel and MS Access database are used to model the crucial business decisions. Cost prices, suppliers, sale prices, items discounts and order discounts are calculated from the data of customer, orders, suppliers and items.
A database has been developed as a relational database to support decision making by placing all the data on one display screen or one format so that all aspects of the business can be viewed before business decisions can be taken. The aim is to reduce the time taken to gather the data to take decisions and to ensure that the decisions are correct. It provides a picture of the direction that the business is taking and if any modification and changes need to be made to meet the changes in the business environment. The data is structured, the customized to suit the needs of a specific business, it is stored and can be retrieved on demand. It is flexible and allows data manipulation.
This is a question that is put to the system as a select query or an action query. Data retrieval queries are select queries and action queries are for updating data or inserting new data or deleting data (Rouse, 2017). For this project of Memorabilia mail order business, three queries are designed to update the data, retrieve the data based on conditions of the customer states, dispatch dates, order dates and in stock items status. For the 2nd query, customer states, supplier names and in stock items are used and the queries are as given below
SELECT tblCustomers.CustID AS CustomerID, tblCustomers.Title AS Title, tblCustomers.FamilyName AS FamilyName, tblCustomers.GivenNames AS GivenName, tblCustomers.DateofBirth AS DateofBirth, tblCustomers.Address AS CustomerAddress, tblCustomers.State AS CustomerState, tblCustomers.EmailAddress AS CustomerMail, tblCustomers.MailingList AS CutomerMailList, tblItems.ItemID AS ItemID, tblItems.Type AS ItemType, tblItems.Description AS ItemDescription, tblItems.[FreightWeight(Kg)] AS FreignWt, tblSuppliers.SupplierName AS SupplierName, tblOrders.DispatchDate AS DispatchDate, tblOrders.OrderDate AS OrderDate, tblOrders.OrderQty FROM tblSuppliers INNER JOIN (tblItems INNER JOIN (tblCustomers INNER JOIN tblOrders ON tblCustomers.CustID=tblOrders.CustID) ON tblItems.ItemID=tblOrders.ItemID) ON tblSuppliers.SupplierID=tblItems.SupplierID WHERE (((tblCustomers.State)="VIC") AND ((tblOrders.DispatchDate) Between #2/28/2017# And Date()) AND ((tblOrders.OrderDate)<#2/21/2017#) AND ((tblItems.InStock)="Yes")) OR (((tblCustomers.State)="NSW") AND ((tblOrders.DispatchDate) Between #3/5/2017# And Date()) AND ((tblOrders.OrderDate)<#2/28/2017#) AND ((tblItems.InStock)="Yes")) ORDER BY tblCustomers.State, tblOrders.OrderDate;
UPDATE tblItems SET tblItems.[CostPrice(IE)] = Round([CostPrice(NZ)]+[CostPrice(IE)]*0.0995,2)
WHERE (((tblItems.InStock)="No") AND ((tblItems.OnOrder)="Yes"));
SELECT tblCustomers.CustID AS CustomerID, tblCustomers.Title AS CustomerTitle, tblCustomers.FamilyName AS FamilyName, tblCustomers.GivenNames AS CustomerGivenName, tblCustomers.Address AS CustomerAddress, tblCustomers.City AS CustomerCity, tblCustomers.State AS CustomerState, tblCustomers.Postcode AS PostalCode, tblItems.ItemID AS ItemID, tblItems.Type AS ItemType, tblItems.Description AS ItemDescription, tblItems.Size AS [Size], tblItems.[FreightWeight(Kg)] AS FreightWt, tblSuppliers.SupplierName AS SupplierName, tblOrders.OrderDate AS OrderDate, tblOrders.OrderQty AS OrderQuantity, Round([CostPrice(NZ)]*0.93*(1+[tblSuppliers].[RecommMarkUp%]),2) AS [SellingPrice(AU)], [SellingPrice(AU)]*tblOrders.OrderQty AS [OrderTotal(AU)], tblItems.InStock FROM tblSuppliers INNER JOIN (tblItems INNER JOIN (tblCustomers INNER JOIN tblOrders ON tblCustomers.CustID=tblOrders.CustID) ON tblItems.ItemID=tblOrders.ItemID) ON tblSuppliers.SupplierID=tblItems.SupplierID WHERE (((tblCustomers.State)<>"NSW" And (tblCustomers.State)<>"VIC") And ((tblItems.Description) Like "*TARDIS*") And ((tblSuppliers.SupplierName)="Rassilon Ltd.") And ((tblItems.InStock)="Yes")) ORDER BY tblCustomers.CustID, tblItems.ItemID, tblOrders.OrderDate;
It is a reporting tool in database management where it places all the data queries responses into one format and displays it on the dashboard for decision making process and information analysis. It provides the data upfront as a visual display. For the current project, database report is designed and developed using the MS Access database features to display all the orders and query results from the query 3 as given above. Report headers, footers and body are customized as per the query results by mapping the field sources and the orders of 8 customers are displayed along with their invoices and final order totals.
Decision support system for this project is helpful to evaluate the key figures of sales, freight charges, cost prices, selling prices, item discounts and order discounts based on various customer categories and suppliers values like the supplier names and item availabilities.
• Store is the recommended mark-up type for the current Memorabilia mail order business
• Mark-up type is selected based on the total profit computed
• If large discounts are given to the order, profits will be increased as for the current case, all the discounts are computed to 0
• Lethbridge-Stewart Transport is the recommended freight type and this value is computed based on the profit
• If the cost is transferred to, business, mail order system should provide good discounts on the order and items and thus the overall profit will be increased
• Business should be improved in New Zealand as the profits are less and operating costs are more
Centriq (2017) What are DBMS Database Management Features?, retrieved on May 10th 2017
Investopedia (2017) Decision Support System (DSS), retrieved on May 10th 2017
Rouse, M.(2017) database Query, Tech Target, retrieved on May 10th 2017
Techopedia (2017) Relational Database (RDB), retrieved on May 10th 2017
Techopedia (2017) Decision Support Systems, retrieved on May 10th 2017
Tech Target (2016) database management systems (DBMS), retrieved on May 10th 2017
Analytical Essay Assignment Help, Online Analytical Essay Assignment Help, business studies assignment help, project management assignment help, strategic management assignment help, marketing management assignment help, database management homework help, online management business assignment help