TECH1400 Database Design and Management Report Sample

TECH1400 Database Design and Management

Assignment Brief

Your Task

For this assessment, you will construct an Entity Relationship Model (ERM) for a real-world case study of an organisation and outline the business rules governing its operations and data management. Additionally, this assessment will allow you to demonstrate the concepts of database relationships, keys, and cardinalities covered in the first three weeks of the subject.

Description

This assessment evaluates your ability to construct an Entity Relationship Model (ERM) for a real- world organisation that uses a relational database based on Crow’s Foot notation using MySQL Workbench. Also, it evaluates your understanding of database relationships, keys, cardinalities, and the application of business rules in database design.

Instructions

You are required to complete the following tasks:

1. Choose a real-world organisation that uses a relational database as part of its operations. This could be a business, government agency, non-profit organisation, or other type of entity.

2. Research and identify the business rules that govern the organisation's operations and data management. Additionally, Identify the types of data that are stored in the database, the relationships between the different data sets, and any key terminology or concepts that are relevant to the organisation's use of the database.

3. Construct an Entity-Relationship Model (ERM) for the organisation's database based on the Crow’s Foot notation using MySQL Workbench. The Entity-Relationship Model Diagram (ERM) must be fully labelled and implementable by including all necessary components, such as entities, relationships, cardinalities, and constraints. Each entity should have essential attributes, including primary keys (PKs), foreign keys (FKs), and other necessary attributes.

4. Provide a detailed, step-by-step explanation of the process you followed to create the ERM diagram, including the screenshot of the diagram during the four major construction steps (listed as the construction steps in item 7 below).

5. Your report should follow a professional report format. You can refer to resources in the MyKBS academic success centre regarding a professional report format. Click here to learn more about the professional format for this assessment. https://elearning.kbs.edu.au/mod/page/view.php?id=400053

6. Your report must include the following sections:

• Introduction: Provide a brief overview of the chosen organisation, its operations, and its use of a relational database.

• Business Rules: Identify and provide a detailed description of the business rules. You need to identify (at least 8) of the necessary business rules for your case study that govern the organisation's operations and data management.

• Entity Relationship Model Design: Present your ERM design, using appropriate notation and symbols to represent the data relationships and structures. Explain the design choices you made and how they relate to the key concepts of database relationships, keys, and cardinalities.

• Construction steps: Provide step-by-step details of the ERM construction process and necessary explanation of the steps.

(1) Entities: Initial diagram with entities and descriptions

(2) Attributes: Updated diagram with attributes

(3) Relationships: Updated diagram that shows added relationships.

(4) Final ERM: The complete final ERM diagram.

7. Your report should include at least 4 academic sources to support your arguments. Your ERM should demonstrate your understanding of the key concepts of database relationships, keys, and cardinalities covered in the first three weeks of the course.

Solution

1.0 Introduction

As one of the leading companies globally in the spectrum of electronic commerce and web-based data storage and computing services. It has a vast e-commerce marketing niche that connects millions of buyers to materials and goods from different vendors from within and outside Amazon. These are order processing, inventory management, and logistics as well as customer support for the company. A relational database management system is used to ensure Amazon’s efficiency in handling large data requirements where the capacity to integrate customers, products, orders, and suppliers must be guaranteed to function efficiently. This database technology enables Amazon to conduct optimal service delivery, keep track of specific details of the inventories, and provide personalized recommendations to clients, thus enhancing the overall shopping experience for MBA assignment expert.

2.0 Business Rules

Customer Registration: Customers are asked to create an account by providing some data such as name, email, and contact information. This regulation ensures that numbers are bought, stored, and retrieved accurately for future transactions with the client (Li et al. 2023).

Order Placement: Customers are also privy to the purchase this is because they have available options of choosing products and expressing their desired quantity. Customer account is mandatory in every order to ensure traceability and also to offer customer service.

Inventory Management: Inventory control should also immediately update as it processes orders to replenish stock or kit products for fulfillment. This regulation helps maintain accurate records of inventory and reduce the instances of over-selling.

Supplier Integration: There is a necessity to link the items to their providers in the context of the marketplace. This regulation ensures that all the information about the suppliers is well recorded and enhances the efficiency of the management of the suppliers (Adebayo et al. 2024).

Shipping and Logistics: The order should be split according to the delivery destination and the preferred shipping methods for shippers. This rule ensures that other logistical-related activities that would ensure swift delivery of these products are optimized.

Payment Processing: Payments must go through safe handling and this means that payment information should be kept on record. This guideline ensures that the information being stored is accurate and secure for financial usage.

Marketing and Promotions: Thus, customer data must be used to the extent it will allow for the targeting of marketing and promotions. This regulation helps promote targeted marketing strategies and actively engage the client (Du et al. 2021).

Data Security and Privacy: These accouterments require protection measures akin to encryption and access control to protect customer and transactional data. This legislation ensures compliance with data protection laws and safeguards proprietary information.

These rules ensure that all business activities are carried out properly through proper reporting, recording, and using information across Amazon. They ensure seamless interoperability in several business processing areas such as customer relations, procurement, and supply, and thus enhance industry performance and consumer satisfaction.

3.0 Entity-Relationship Model Design

The Crow’s Foot notation is applied to design the Entity-Relationship Model for Amazon by identifying different entities involved in the company’s operations. The primary objects include consumer information, order information, product information, carriers, transportation services, marketing information, sales information, stocks, and supplier information (Plomaritou et al. 2020). Primarily unique identifiers, including customer_id, order_id, and product_id of every entity, ensure record uniqueness and integrity. Foreign keys are used to build relationships between entities; for example, to map order information to customer information to identify which customer made which order. Cardinalities define the kind of relationships for example the one consumer- many orders and the many suppliers- many products relationships which are regulated through the use of the middle tables (Thimm et al. 2023). This approach ensures normalized data, reduces the extent of data replication, and allows for data to be retrieved as quickly as possible, which is fitting for Amazon’s complex data handling.

4.0 Construction Steps

4.1 Entities: Initial Diagram with Entities and Descriptions

● Customer Data: Stores customer information such as customer_id, customer_fname, customer_lname, customer_contact, customer_email, and order_history.

●Order Data: Captures order-specific details including order_id, order_products, order_quantity, order_delivery_status, and references to customer and transaction data.

●Product Data: Maintains product information like product_id, product_name, product_price, and product_specification.

● Shipper Data: Contains shipper information including shipper_id, shipper_address, and shipper_contact.

●Logistics Data: Records logistics details such as logistics_id, logistics_shippingroute, and logistics_deliverytime.

●Marketing Data: Includes marketing-related data like marketing_id, marketing_ads, and marketing_engagement_metrics.

● Transaction Data: Tracks transaction details with fields like transaction_id, transaction_amount, and billing_details.

●Inventory Data: Manages inventory specifics such as inventory_id, inventory_stock, and inventory_warehouse.

● Supplier Data: Stores supplier information including supplier_id, supplier_address, and supplier_contact.

4.2 Attributes: Updated Diagram with Attributes

The process that followed included allocating attributes to every identified entity there is. It ensured that the possession of all important qualities by each of the entities in the AHP enabled it to describe its data comprehensively. Primary keys (PKs) were defined to ensure the uniqueness of the records in each organization. FKs were rarely directly used; instead, they were designed to establish relations between entities. For instance, the order data entity had Foreign keys that related the entity with the customer data and a transaction data entity.

Figure 2:Initial ERM Diagram Description
(Source: Self-created)

The initial ERM diagram that has been developed for Amazon identifies some of the most essential entities of the company including customer details, data concerning orders, product data, the shipper, logistics information, data related to marketing activities, data concerning transactions, inventory, and supplier data. Every entity is represented in terms of characteristics and primary keys using placeholders. This step is critical for creating relationships and including specific characteristics, which are crucial for creating a fulfilling framework for a relational database.

4.3 Relationships: Updated Diagram that Shows Added Relationships

● Customer Data and Order Data: A one-to-many relationship was established, indicating that one customer could place multiple orders.

● Order Data and Product Data: A many-to-many relationship was defined, and managed through an intermediary entity, to indicate that each order could contain multiple products and each product could be part of multiple orders.

● Order Data and Shipper Data: A one-to-one relationship was established to indicate that each order is handled by a single shipper.

● Supplier Data and Product Data: A many-to-many relationship was defined, showing that multiple suppliers could provide the same product and vice versa.

Figure 3:Updated ERM Diagram Description
(Source: Self-created)

The elements in Amazon ERM diagram includes customer data order data, product data, shipper, logistics, market data, transaction data, inventory and supplier data in its modified structural diagram. Every entity has to have primary key and attributes. Foreign keys are another feature of databases which restores the links between the entities in order to provide good organization of the information. This assists in the preservation of multiple operational procedures that Amazon has implemented, it also helps in the sharing of information among the various functional areas of the company.

Final ERM: The Complete Final ERM Diagram

The final step in the process involved integrating all entities, attributes and relations into an ER Model known as Entity-Relationship Model (ERM). The diagram described a highly connected relational database design that was sufficient to handle Amazon’s data inputs. The optimum Enterprise Resource Management (ERM) system ensured reliable input generation, output quality, and proper documentation eliminating the problem of excessive replication while enabling efficient and speedy access to information in several operational field: customer care, warehousing, and material supply chain. The final echelon of the ERM diagram provided a clear and effective representation of the structure of the database which provided a better perspective about flow of data and integration within the Amazon business processes (Lee et al. 2023).

Figure 4: ERM Model
(Source: Self-created)

The last of the five diagrams of ERM for Amazon comprises a structure of the relational database in a concordant order. Some of the prominent entities of a virtual organization include; clients’ information, orders, products, the company to which shipment is made, distribution & supply chain, marketing info, transactions, inventory, and supplier info. These causes relationship to be created depending on keys from foreign types to make us have some order in the data provided. In the figure below, the customer table is joined with the order table to show the orders given by customers (Köhler et al. 2024). The various details about the product are kept connected to the order and supplier details of the product, helping in the efficient control of the inventories and the interactions with the suppliers.

5.0 Conclusion

The elements in ERM included the identification of critical entities, the definition of attributes, and the linkage among them and this was done using Crow’s Foot notation. Each activity adopted ensured the data was accorded the highest level. Data consistency, duplication, and accessibility were also always facilitated. The proposal for integrating Amazon’s Enterprise Risk Management (ERM) entails highly complex working assumptions and proven data management frameworks. This is a good approach when it comes to managing data for Amazon since it makes it easy to handle data, enhance functions, and support integration throughout the organization’s processes. The utilization of the ERM helps in decision-making and assists the highly complex and broad functioning of Amazon through a clear recognition of the storage and processing of data.

6.0 Reference

Adebayo, V.I., Paul, P.O. and Eyo-Udo, N.L., 2024. The role of data analysis and reporting in modern procurement: Enhancing decision-making and supplier management. GSC Advanced Research and Reviews, 20(1), pp.088-097.

Du, R.Y., Netzer, O., Schweidel, D.A. and Mitra, D., 2021. Capturing marketing information to fuel growth. Journal of Marketing, 85(1), pp.163-183.

Köhler, C., Campbell, A.M. and Ehmke, J.F., 2024. Data-driven customer acceptance for attended home delivery. Or Spectrum, 46(2), pp.295-330.

Lee, K., Cooper, A.F. and Grimmelmann, J., 2023. Talkin''Bout AI Generation: Copyright and the Generative-AI Supply Chain. arXiv preprint arXiv:2309.08133.
Li, J., Maiti, A. and Fei, J., 2023. Features and Scope of Regulatory Technologies: Challenges and Opportunities with Industrial Internet of Things. Future Internet, 15(8), p.256.

Plomaritou, E. and Patsiouras, C., 2020. Marketing Information System: A Success Factor of Shipping Business in Cyprus. Journal of Economics, Management and Trade, 26(10), pp.86-99.

Thimm, H., 2023. Data modeling and NLP-based scoring method to assess the relevance of environmental regulatory announcements. Environment Systems and Decisions, 43(3), pp.416-432.