Sample IM501 Agricultural Data and Information Management Report 2

IM501 Agricultural Data and Information Management Report 2 Sample

In this assessment, students are required to create a databas and write a report in response to issues and problems raised in the case study provided. This assessment intends to assess the followings:

- Collect, store, and manage data in an agribusiness context
- Develop a collection of tables in a database to address agribusiness needs
- Database design
- Database modelling
- Database implementation
- Communicate database design to non-expert and expert stakeholders

The first step is to read the narrative in the case study, IM501 Assessment 2 CaseNarrative which is an attachment to this assessment brief.

- Step 1: Draw ERDs to model the database required for addressing the agribusiness needs in the case study. Note: The ERD must be drawn using LucidChart or an equivalent professional diagramming software package.

- Step 2: Design as many tables as required in Microsoft Excel to address the needs of the agribusiness in the case narrative

- Step 3: Write MySQL statements to create a database called Assessment 2 including all the tables created in step 2 above.

- Step 4: Write a report explaining your ERD diagrams, excel databased implementation, and MySQL implementation. Note that you write this report as an expert in database for stakeholders. Therefore, make sure to thoroughly explain your design and highly the data redundancy, data normalisation, data independence, data consistency, data integrity, data security and the scale of data sharing when using Excel or MySQL database to address business needs in organisations. You will be trying to convince stakeholders that DBMS is what they need to effectively address their needs.

Submission Instructions

Solution

Introduction

AussieFarmerMate is operated by a farmer John McFarmer from Brisbane. His business has recently expanded through sales which has overpowered the conventional handling of data in papers. Due to the small size of the company for the assignment helpline and having ten employees, there are problems with managing customer information, products from farms, and orders. In response to this, AussieFarmerMate has contracted your services in Database design. The first objective is to develop an efficient database management system to support organisational functions.

The database will be required to address a number of elemental areas of the business. It must manage detailed customer information, including categorisation into four discount tiers: The four contingents are bronze, silver, gold, and platinum with corresponding rebates of 0%, 10%, 30%, and 50% respectively. Further, the details of all the farm products will be dealt with through the database so that the concerned farm’s inventory and record will be well and professionally managed. Another key documentation is that of employee records since there has to be a way to store and modify the information associated with the workers. Also, it is necessary to design the manner in which customer orders are to be stored in the database in a way that will connect the data to the customer and product records.

The following are the requirements The analysis of the requirements will done in the body of this report. The first part will cover how to design tables for processing the customers’ data, their attributes and types of discounts. The second one will handle details of farm products which will help in proper classification and stock control. The third section will describe employee management with a focus on the accuracy of the information. The report will discuss order management with special emphasis on the integration of this table with other tables that will be implemented. With this approach, it is proposed to get a comprehensive solution for the client, AussieFarmerMate, which will be usable and can bring the company to a new level of business process management.

Main body

The `Class` field categorizes customers into different discount levels: There are four levels which include bronze level, silver level, gold level and platinum level. The field named `Discount` provides equivalent discount rates that vary from zero per cent to fifty per cent. The `INSERT” statements feed into the Table with sample customers and give discounts according to the class of the customer.

This structure makes it easy to contain the customers’ information and where they stand with regard to discounts. The customer’s record is arranged in a systematic manner that makes it easy to locate the details of a given customer. The structure of the database enables flexibility in the capacity for which the number of customers and discount levels can be accommodated.

Employees Table

Table 3: Query for creating employee table
(Source: Self-developed in MySQL)

Table 4: Employee table
(Source: Self-developed in MySQL)

The above codes create the `Employees` table for the AussieFarmerMate database. Some of the fields contained in this table are Employee_ID, Name, Role as well as Contact. There is the primary key field known as `Employee_ID` which determines that every record of the employee will be unique. In the ‘Name’ field, we record the first and last name of each employee. The `Role` field defines the employee’s position within the firm where they fill different posts for example a Farm Manager a Sales Manager or a Marketing Specialist. The `Contact` field retains the employee’s contact number which is crucial in internal communication amongst other employees.

The `INSERT’ statements introduce ten records into the table; each record represents an employee who has a particular role and contacts phone number. This structure enables AussieFarmerMate to manage its employees’ details so that their roles in the company can be well-defined. Table makes it possible for employee data to be incorporated effectively within other business activities helping to enhance coordination of functions across employees.

Products Table

Table 5: Query for creating products table
(Source: Self-developed in MySQL)

Table 6: Products table
(Source: Self-developed in MySQL)

From the above code, it can be noted that the Products table is to be created in AussieFarmerMate’s database. This table starts from the fields of `Product_ID’, ‘Name’, ‘Type’ and up to the field of ‘Price’ and the last one of the field of ‘Stock’. The name Product_ID has been given since it is relevant in defining each record that would be entered in the table as unique. The first field which is the standard field is named the ‘Name’ field that has details regarding the product such as Wheat or Milk. As discussed in the following section, the `Type’ field—tries to determine the kind of product by categorizing it as grain, dairy or poultry among others. Some fields have a Selling Price while other some have a unit price in decimal for accurate value such as the ’Price’ field in certain fields. The `Stock` field maintains a record of the number of positions available at the store at that moment.

Below are the `INSERT’ statements that are used to input five products with their types, prices and the available stocks respectively. From this structure, there is easy administrative control of inventory and stock; thus, proper records as far as the pricing of the products are concerned. From the discussions above, it can be agreed that a specifically designed table helps in the organization and update of the product inventory of AussieFarmerMate hence, the efficient running of the business.
Orders Table

Table 7: Query for creating orders table
(Source: Self-developed in MySQL)

Table 8: Orders table
(Source: Self-developed in MySQL)

The above code specifies the schema for the Orders table in the AussieFarmerMate database. In the table, some of the fields are `Order_ID’, ‘Order_Date’, ‘Customer_ID’ & ‘Employee_ID’. The `Order_ID’ is used as the basis of the primary key whereby there cannot be two records of an order. The `Order_Date` field records the date of placement of every order carried out in the firm. The first two fields, `Customer_ID` and `Employees_ID` create foreign keys to the `Customers` and `Employees` tables, respectively. These fields are linked by foreign keys, thus maintaining coherence between orders and their related customers and employees.

The `INSERT” statements add data to the order table for five orders, as well as linking each order to a customer and an employee. Due to such a setup, AussieFarmerMate is in a position to track the various orders under the following format: date, customer, and the involved employee. It specifies the organization structure to stipulate the orders of the customers, which is vital to the efficiency and effectiveness of the business as far as proper and acceptable order delivery is concerned.

Order_Details Table

Table 9: Query for creating order details table
(Source: Self-developed in MySQL)

Table 10: Order details table
(Source: Self-developed in MySQL)

The above code creates the attribute’s structure for the `Order_Details` table in the AussieFarmerMate.dbc. Some of the fields in this table include ‘Order_Detail_ID’, ‘Order_ID’, ‘Product_ID’, and ‘Quantity’. Order detail ID also has a primary key role and makes order detail table entries unique in each case. The `Order_ID` and `Product_ID` fields import from the `Order_Details` table to the `Orders` and `Products` tables, respectively, have been defined to support referential integrity. The `Quantity` field is used for the description of the amount of every product which is bought, which provides convenient tracking of all ordered items.

The `INSERT` statements semantically load the table with ten records each conveying order number, product number as well as quantity for distinct orders. This structure allows AussieFarmerMate to have a clear record of the products in order to facilitate sales and track the number of products that have been in demand and the ones that are not well in demand in the market. To add on this ordered structure makes efficient disposition of order processed and the actual product.

Conclusion

In conclusion, as seen in the above breakdown of the database’s design, AussieFarmerMate optimizes functions. When the customers’ data, products of farms, and records of employees are arranged in the best way, there is success in efficiency. The system handles the customer and pricing discount mechanics properly so that the customers get the correct price that applies to them. This includes human resources data that are securely stored to meet the various needs of the company’s employees. The flow of orders is controlled with fine detail and associate customers and their products efficiently. Another strength that is apparent with the database structure is the fact that it can grow in the future and adjust to new additions. This design helps make data more accurate and easy to access thus improving the decision-making process. In general, the solution relates to the change of business processes while considering difficulties connected with paper-based management. This particular database will greatly enhance the operational efficiency as well as the productivity of the company so much that it would be hard to quantify. Hence, AussieFarmerMate has an understanding of the growth in sales and managing of resources in the future which places it in a better position to execute such a plan. They are easily expandable and can include new and/ or modified features in the system as well.

Recommendations

- DBMS backups should be made regularly to avoid a recurrence of this kind of mishap. Data integrity and availability are necessary mostly for business purposes. The use of some of the automated backup measures will help to ensure that there is no loss of data through corruption or accidental deletion.

- To achieve that, employee training programs on database usage need to be designed so that all the workers start using the new system to their best abilities. This is because knowing the various functions in a database shall help in improving efficiency and minimizing mistakes in input and processing of data.

- As the business expands it is important to review and optimize the performance of the database regularly to ensure it continues to meet the business needs’ performance as expected. More data volume also requires performance metrics monitoring and database configuration tuning that will allow us to continue delivering queries within an acceptable time.