Unit17 Database Design Concepts

Database Management Systems

 Database Management Systems

Database Management SystemsAssignment 1: Part C (Analytical Essay)


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

Database Management Systems

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)

Relational Database

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)

Use of a database in this Project

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.

Relational Database Development

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.

Database Query

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

Query 1

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;

Query 2

UPDATE tblItems SET tblItems.[CostPrice(IE)] = Round([CostPrice(NZ)]+[CostPrice(IE)]*0.0995,2)

WHERE (((tblItems.InStock)=”No”) AND ((tblItems.OnOrder)=”Yes”));

Query 3

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;

Database Report

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

Decision Support Systems in short are known as the DSS. There are various classes of information systems that are used in organizations. The decision support systems is one such class that  supports the business functions and decision making  by providing the information that is essential for business operations. The software in such systems is interactive to help the users compile useful and valuable information from raw –data which would otherwise remain hidden from the decision makers. The systems use all types of data to create information. The software gathers information from:

Current assets

Relational data

Data cubes

Comparative sales figures

Projected revenue figures

Provides the consequences of different decision options

Such systems have a wide reach in data gathering and the interactive facilities helps the system to extract data from all sources and structure it providing an accurate picture of the business and the type of decision that needs to be taken for a competitive advantage. The system is fully computerized and the users can sift through the different types of data to analyze all types of data that could help them to take a decision. Problem solving information is also created by theDSS. The advantages that the system provides is efficiency, fast resolutions to issues and decisions that are informed decision backed up with evidence and data analytics. (Techopedia,2017)

In Planning and operations the DSS is sued for compiling information from raw data and creating intelligence that is actionable. The various types of information that is gathered by the Decision Support Systems are revenue figures, sales figures, projected figures market conditions, demand and supply positions, inventory data, data from relational databases, analytical results from the data gathered by the system. The high light of the system is that it can provide the options for the types of decisions that can be taken based on the information that is displays and it also provides the consequences of the decision options if taken in the business.

The system is sued in all types of businesses and it is predictive to the extent that it can provide forecasts of market trends and where the business is likely to be in the changes that take place in the business environment and the types of changes that need to be made to survive in the industry and to gain market leadership. This is a technology that is becoming more and more popular and is being used by most of the large and medium sized businesses that can afford the DSS. system in order to increase the business prospects in the market and prepare for the future market conditions (Investopedia,2017)

Importance of DSS for this Project

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.

DSS findings for Memorabilia mail order business

  • 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

David Marks

Dear sir/Ma'am

Our dedication and hard work towards developing quality content has made us competent to provide excellent services to the clients as per their needs. We ensure plagiarism free writing obeying academic integrity and honesty making us a dedicated team towards developing original content helping students to ensure high grades. Our services are hassle free, timely yet at an affordable rate driving clients to avail it for a longer period of time making them loyal and satisfied towards us.

Services we offer:
Essay writing
Report writing
Assignment writing
Reflective writing
Proposal writing
Dissertation writing
Thesis writing
CDR writing
Annotated bibliography
PowerPoint presentation
Article/book review
Why us?
Plagiarism free original content
On-time services maintaining deadlines
Experienced writers
Plagiarism and Grammarly report
Dedicate Team Leaders and Quality Checkers
Subjects we cover:
Marketing management
Human resource management
Environmental science
Business studies
Political science
Journalism and Masscommunication

Declaration: Working with us will give you the opportunity to avail divergent range of academic services at affordable rates in assistance with the dedicated team having members from different disciplines holding high degrees in their respective domains. We are experienced in developing B-plan, writing dissertations and theses having employed highly qualified and experienced writers.

You can reach us at-
Email:- ozpaperhelp@gmail.com
Oz Paper Help

1 Step 1