IM501 Agricultural Data and Information Management Case Study 2 Sample

IM501 Agricultural Data and Information Management Case Study 2

Assignment Details

In this assessment, students are required to create a database 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

Instructions

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.

CASE STUDY

John McFarmer is local farmer in Brisbane and runs his business with 10 employees. His company is called AussieFarmerMate, and all data are managed paper-based. Due to a recent increase in sales recently, the employees cannot keep up with the current paper-based data management system. Because of your vast experience in database design, AussieFarmerMate has called upon you to design and implement a database to support the business. After your first planning meeting, the following requirements have been identified.

The system should:

• manage the details of customers.

• manage the details of farm products.

• categorise customers into four classes (bronze, silver, gold, and platinum) to receive 0%, 10%, 30%, or 50% discount.

• manage the details of all employees.

• manage the details of all orders from customers.

When designing your tables, please do not forget to identify key attributes and any foreign key attributes.

This assessment must be completed using two approaches:

• Microsoft Excel: as an improvement to the paper-based system

• MySQL: as an automated data management process

You are only required to design and create the tables in Excel and MySQL. There should be no queries. After designing and implementing the tables, make sure to fill each table with at least five records (that can be ‘made up’).

You are required to write a report for AussieFarmerMate with a minimum of 3000 ±10% words discussing your design (with ERD) as well as advantages and disadvantages of both manual and automatic data management for the business in this case study.

Solution

Introduction

An existing farming firm in Brisbane known as AussieFarmerMate has realized a massive sale hence an overwhelming record management via papers is almost out of date. Over time, this has seen the company expand and as such the necessity of a robust, scalable and automated data management system is clear. DBMS also provide a professional way of organizing, accessing and maintaining data and its features are numerous as compared to manual systems, for instance accuracy, efficiency and security among others.

The design and implementation of the DBMS being used in AussieFarmerMate is explained well in this report together with how AussieFarmerMate transitioned from using paper based system, then the Excel prototype and finally using MySQL database. It has been also include a comparison between manual and automated data management, the explanation of the database design by using ERDs and MySQL benefits in data management.

Advantages and Disadvantages of Manual vs. Automated Data Management

Manual Data Management (Paper-Based System)

Advantages:

Simplicity: Basically, paper-based systems have no complicated procedures in implementing and does not need any expert in information technology or any form of computer literacy.

Low Initial Cost: Compared to electronic system, the cost of implementing paper based system is very low since all that is needed some writing materials.

No Technology Dependency: It does not employ the usage of computer or software which makes it efficient for cultures with less innovation resources.

Disadvantages:

Prone to Human Error: By using manual methods to enter and manage data there is high tendency of having wrong data.

Difficult to Manage Large Volumes: According to Pulungan et al. (2023) asserted that as data increase the use of paper-based systems is a major problem due to the difficulties experienced when trying to locate important information, or when modifying data that is buoyant due to increased data for MBA assignment expert.

Time-Consuming: Such activities are resource intensive to manage as it consume a lot of time and effort most of which is counterproductive.

Lack of Data Security: Manual paper based records may be lost, damaged or more importantly accessed by unauthorized persons.

Poor Data Retrieval: Identification of specific information in paper based system is normally a lengthy process and demands a lot of efforts.

Automated Data Management (DBMS)

Advantages:

Improved Data Accuracy: Automated system are free from many human factors such as error due to fatigue, lack of concentration and carelessness.

Efficiency: DBMSs also enhance the operations efficiency in terms of entering data, retrieving data or even updating them.

Scalability: Compared to manual systems based on papers, DBMSs allow for a more simplistic integration of additional capacities corresponding to a growing business, and more efficient data storage.

Enhanced Data Security: Other elaborate security aspects include user login, privileges, and data encryption that prevent unauthorized persons to access data (Bernhardt et al. 2022).

Disadvantages:

Higher Initial Setup Costs: Adopting a DBMS involves the use of resources such as hardware, software besides putting more cash on training.

Requires Technical Expertise: DBMS has a number of complexities this means that it needs some expertise to install, administer and perform injury control.

Ongoing Maintenance: Otherwise, constant maintenance is required so that the system functions both efficiently and safely.

Database Design and ERD Explanation

Overview of Database Design

To address the data management issue of AussieFarmerMate, a structured model following relational database management system was developed with major concern of eliminating data duplication and data inconsistency. The database includes several key entities: Customers, Employees, Orders, Order_Items, Products, Categories and Customer_Discounts. Every company is depicted as a table wherein primary keys are used to define each record uniquely while the foreign keys relate the tables.

The objective it seek to achieve is to classify customers into four groups (bronze, silver, and gold, platinum), to deal with the specifics of the customers, employees and products and to control the customer orders. When normalizing data means arranging it systematically, thus, it are in a position to remove any duplicity and to enhance uniformity within the database (Nikiforova, 2022).

Entity-Relationship Diagrams (ERDs)

The ERDs presented below highlight the associations of the numerous entities in the database. With the help of such diagrams, one sees how data is linked and how individual tables relate to one another (Nguyen, 2023).

Figure 1: ERD Diagram of Whole Database
(Source: Implemented from Draw.io)

From this figure, the reader can be in a position to see all the entities and it relations on the AussieFarmerMate database. Every nameless entity is illustrated with its attributes, primary keys, and reference keys as well as with the relationships and constraints between the tables.

• Customer ERD: This candidate of the ERD illustrates the details of the Customers table and how it is connected to other other tables including Orders and Customer_Discounts. The first one is CustomerID and the table is related with the Orders table as the foreign data type.

Figure 2: ERD Diagram Customer and Customer Discount
(Source: Implemented from Draw.io)

Based on the description, the following diagrams display the attributes of Customers table in the ERD including the following; Customer ID, Customer Name, Contact Number and others. C – O also depicts the association between Customers and Orders where it are associated by CustomerID.

• Employee ERD: The following diagram is depicting the Employees table along with its associated table Orders table.

Figure 3: ERD Diagram of Employees
(Source: Implemented from Draw.io)

The Employees ERD presents the different fields that may exist in the Employees table such as EmployeeID, FirstName, LastName, and Position. It also demonstrates the relationship between Employees and Orders, through EmployeeID.

• Order ERD: This can be seen from the following structure of the Orders table and the relation with Order_Items and Customers. The Order ID is the primary key for this table and it has related foreign keys those are Customer ID and Employee ID.

Figure 4: ERD Diagram of Order and Order_Items
(Source: Implemented from Draw.io)

• Order Items ERD: The graphic depicted here presents the fields of the Order_Items table and how the table relates multiple products to a single-order. The main key is as follows: OrderID(ProductID) since every entry shown in order items is unique.

In the above Orders ERD, the Orders table is presented with attributes as OrderID, OrderDate, TotalAmount and relationships with Customers and Employees. It also relates to the Order_Items table with the help of OrderID.

Figure 5: ERD diagram of Products
(Source: Implemented from Draw.io)

The Products of this necessary ERD is depicted the structure for the table Products with the fields ProductID, ProductName, and Price. It also shows dependancy with the Categories table through the CategoryID field.

Through these ERDs, It increase the normalized state of the data reducing the possibility of data duplication and increasing overall data credibility (Mardian it et al. 2023).

Excel-Based Database Implementation

Initial Database Implementation in Excel

The current structure of the database was first tested in Excel prior to adopting a DBMS for the purpose of prototyping the structure. Tables were created in Excel and the relationships were then explicitly defined using two types of keys which are the primary and the foreign keys (Geisler and Quix, 2022).

Every single table in Excel was made in accordance with the planned database where every column referred to an attribute and every line was a record.

Figure 6: Customer Table
(Source: Implemented from Microsoft Excel)

Here is shown the Customers table in the Excel with the definition of fields and some examples of the records in this table with fields as CustomerID, CustomerName, ContactNumber, and E-mail.

Benefits and Limitations of Using Excel

While Excel is a versatile tool for prototyping and small-scale data management, it has several limitations when used as a database:

Advantages of Using Excel:

• Ease of Use: Excel is easy to use and readily available, therefore people who are not very technical can be able to create and manipulate small databases.

• Quick Setup: To place the tables and enter the data in Excel is quite easy and no extra knowledge of software is needed.

• Visualization Tools: Data visualization is also available within Excel and this is useful for initial examination of results and creating reports (Samidi, Sulad it and Lesmana, 2022).

Limitations of Using Excel:

• Data Redundancy: This is particularly something that might come as a result of realization of poor entrance controls that usually lead to creation of so many Excel tables with same data sets.

• Lack of Normalization: Although Excel has functionalities that enables organizing of data as it scales, it does not support data normalization.

• Scalability Issues: Excel is not scalable to organizations that are expanding, it cannot handle large data or users at a go.

• Data Security: Security in Excel in limited and does not offer strong permission and protection on sensitive data which make it prone to high risks of leakage or hacking.

• Manual Data Integrity Maintenance: Changing, deleting, and updating data in Excel may be completed by the user, however, this is time consuming, and in some instances may not be thoroughly done.

Figure 7: Orders_Table
(Source: Implemented from Microsoft Excel)

The following is also given in excel which represents the Orders table including the table details and some data elements like OrderID, OrderDate, Customer ID etc.
Based from these factors, four considerations came out, such that a more effective solution had to be established to cater the increasing data demand of AussieFarmerMate efficiently.

MySQL Database Implementation

Transition from Excel to MySQL

To move from Excel to MySQL there were several steps that had to be taken these are migration of data, designs of the schema, and definition of the relationships that would be required. MySQL database has been developed to emulate the structure that was created during the first phase in Excel with the additional functionality for data integrity, security and scalability.

Figure 8: Creating Tables in SQL
(Source: Obtained from MySQL Workbench)

This figure also illustrates the SQL statements used in the creation of the tables in the MySQL table together with the identification of the primary and foreign keys of each table.

Benefits of Using MySQL

The use of MySQL database relieved many of the issues and problems that arose from the manual phase in Excel in implementing the database.

• Data Normalization: The method used by MySQL to store data also enables normalization to occur in that repetitive data is eliminated. Every table has a specific set of data that is needed as the tables are related using primary and foreign keys to ensure the data’s coherence (Darmont et al. 2022).

• Data Independence: MySQL in this case frees data from being contingent on the application layer where modifications can be easily made on the MYSQL data structure.

• Data Consistency and Integrity: MySQL guarantees the integrity of the data by implementing constraints including primary and foreign key that checks on inter-related tables. Transaction management also ensures that data are also consistent and protected from errors and failures of the system.

• Data Security: MySQL has advanced security measures which incorporate user authentication, user authorization as well as encryption of data which prevents unauthorized access or data theft and loss (Kar, 2023).

• Scalability and Performance: MySQL is a high-performance database, well-suited for dealing with large amounts of data, and high traffic, which is perfect for the business like AussieFarmerMate. MySQL also supports multiple users at the same time and shares records which are faster and effective in an organizational situation as compared to Excel.

Figure 9: Inserting Data in the Table in SQL
(Source: Obtained from MySQL Workbench)

The following figure illustrates the SQL queries of inserting data into the MySQL tables and the manner in which data is combined through foreign keys.

SQL Queries for Database Operations

In MySQL, Structured Query Language, or SQL, is utilized in order to accomplish the many tasks related to managing a database, as well as in data manipulation, retrieval, and updates. These queries help in the management of data as well as the running of AussieFarmerMate business operations.

• Data Retrieval: Business intelligence is supported since SQL queries has been help to find certain information rapidly by the criterion using requests. For instance, queries can help it to find customer’s purchase records, performance of the employees, or sales of particular products.

• Data Updates: MySQL also offers the flexibility of updating data hence making information up to date. For instance, it may take a shorter time to update changed product price, change in contact information of a customer among others.

Comparison between Excel and MySQL

Data Redundancy and Normalization

This is because data normalization is not inherent in Excel and therefore there may be duplication as well as complexity in the process. On the other hand, MySQL is achieve normalization by its table design and constraints which make the data stored in an organized and concise manner without duplication (Krakowiak and Ziemba, 2022).

Data Integrity and Consistency

The main drawback of the method is that data validation and data consistency checks are carried in Excel manually and there may be several inconsistencies. MySQL on the other hand has field level integrity constraints for the data like the primary keys and the foreign keys hence improving the validity of the data in the entire database (Lambert, Davidson and LeMay, 2023).

Scalability and Performance

Excel is slow in data processing and not designed to cater for a large volume of data and several users at the same time which makes it unadeguate for expanding companies. MySQL is scalability designed to handle large volumes of data and can be used by users simultaneously, and also the performance has been not be hindered even if the business expands (Nurhayat it and Nasution, 2023).

Security and Data Sharing, and numerous security weaknesses which put data contained in Excel base at risk of being accessed by unauthorized people. Though, MySQL has superior security measures for the security of data such as user authentication, authorization, encryption among others in addressing the threats of breaches (Kossmann, Papenbrock and Naumann, 2022). Moreover, the MySQL database is also capable of data sharing as well as concurrent access in order to help multiple users.

Business Benefits of Using a DBMS

Improved Data Management

DBMS has been enable AussieFarmerMate to undertake systematic departmental database whereby data would be managed efficiently in one central structure. This approach reduces on the duplication of data hence makes it easier to retrieve and analyze data since it is under one control.

Enhanced Decision Making

Since a DBMS offers the current, accurate and readily accessible data, decision regarding business is likely to be enhanced. For instance, the sales records and customers’ buying patterns can be used in revealing areas of expansion and in the appropriate stocking of items.

Conclusion

This paper concluded that an implementation of DBMS, for instance MySQL is advantageous for the development of AussieFarmerMate since it resolves the challenges associated with manual management of data. The application of structured DBMS as revealed by the ERDs promotes an efficient way of storing and organizing data through minimizing on duplication and at the same time enhancing the quality and accuracy of data. The first Excel prototype was useful in exposing the data needs and dependencies required to build the model but it inadequate for accommodating the growing demand of the business. MySQL helps to make the data more secure and accurate, at the same time, the usage of MySQL increases the efficiency of operation, allowing the business develop new strategies for growth and constantly improving customer satisfaction.

References

Bernhardt, A., Tamimi, S., Stock, F., Vinçon, T., Koch, A. and Petrov, I., 2022. Cache-coherent shared locking for transactionally consistent updates in near-data processing DBMS on smart storage. In Proceedings of the 25th International Conference on Extending Database Technology (EDBT), 29th March-1st April 2022 (pp. 424-428). OpenProceedings.

Darmont, J., Novikov, B., Wrembel, R. and Bellatreche, L., 2022. Advances on data management and information systems. Information Systems Frontiers, 24(1), pp.1-10.
Geisler, S. and Quix, C., 2022. Database Management Systems (DBMS). In Encyclopedia of Big Data (pp. 352-358). Cham: Springer International Publishing.

Kar, S., 2023. Data Management for Social Scientists: From Files to Databases. Journal of Data Science, Informetrics, and Citation Studies, 2(3), pp.273-278.

Kossmann, J., Papenbrock, T. and Naumann, F., 2022. Data dependencies for query optimization: a survey. The VLDB Journal, 31(1), pp.1-22.

Krakowiak, M. and Ziemba, P., 2022. A SERM based framework for defining and processing rules supporting the verification of data consistency and integrity. Procedia Computer Science, 207, pp.4227-4236.

Lambert, S.L., Davidson, B.I. and LeMay, S.A., 2023. Survey of emerging blockchain technologies for improving the data integrity and auditability of manufacturing bills of materials in enterprise resource planning. Journal of emerging technologies in accounting, 20(2), pp.119-134.

Mardiani, E., Sriyeni, Y., Sitorus, A.N. and Muthmainah, H.N., 2023. The Impact of Scalability and Consistency Management on Database Management System Performance in Big Data Environment in Indonesia. The Eastasouth Journal of Information System and Computer Science, 1(02), pp.87-97.

Nguyen, A.T., 2023. Expanding the data normalization strategy to the MACONT method for multi-criteria decision making. Engineering, Technology & Applied Science Research, 13(2), pp.10489-10495.

Nikiforova, A., 2022, April. Data security as a top priority in the digital world: preserve data value by being proactive and thinking security first. In The International Research & Innovation Forum (pp. 3-15). Cham: Springer International Publishing.

Nurhayati, S.T. and Nasution, M.I.P., 2023. Database Management System Pada Perusahaan. Jurnal Akuntans it Keuangan dan Bisnis, 1(2), pp.62-64.

Pulungan, S.M., Febrianti, R., Lestari, T., Gurning, N. and Fitriana, N., 2023. Analisis Teknik Entity-Relationship Diagram Dalam Perancangan Database. Jurnal Ekonom it Manajemen dan Bisnis (JEMB), 1(2), pp.143-147.

Samidi, S., Suladi, R.Y. and Lesmana, A.B., 2022. Implementation of Database Distributed Sharding Horizontal Partition in MySQL. Case Study of Application of Food Serving On Kemkes. JURNAL SISFOTEK GLOBAL, 12(1), pp.50-57.