Case Study : Optimizing Operations at IKEA Furniture Center

Introduction

IKEA, the world-renowned furniture retailer, has built its empire on offering affordable, welldesigned home furnishings and a unique self-assembly model. With hundreds of stores globally, a vast online presence, and millions of customers, managing its colossal volume of data effectively is paramount to its success. This case study explores the journey of a hypothetical IKEA Furniture Center in a rapidly growing market, focusing on its transition
from disparate, unintegrated data systems to a robust, centralized relational database management system (RDBMS) to address critical operational and informational challenges.

The Challenge: A Fragmented Data Landscape

Before embracing a structured RDBMS, IKEA Furniture Center faced significant hurdles stemming from its unintegrated data systems. Each department—from sales and inventory to customer service and supply chain—operated with its own set of applications and data storage methods, often relying on standalone spreadsheets, local databases, or even paper-based records. This fragmented approach led to a multitude of problems:

1. Data Silos: Information was isolated within departmental boundaries. The inventory team had one view of product stock, while the sales team might have another, leading to discrepancies and missed sales opportunities due to inaccurate stock levels.

2. Data Inconsistencies and Redundancy: The same piece of information, such as a product description or a customer’s address, might be entered multiple times across different systems, often with variations or errors. For example, a customer’s contact details updated by customer service might not propagate to the sales or delivery department, leading to incorrect shipments or frustrated customers.

3. Lack of Real-time Reporting: Generating comprehensive reports on sales
performance, inventory turnover, or customer purchasing trends was a manual, timeconsuming process. Managers struggled to get a unified, up-to-date view of business operations, hindering agile decision-making. Strategic initiatives were often based on outdated or incomplete information.

4. Challenges in Managing Cross-Channel Customer Data: With customers
interacting via physical stores, the IKEA website, and mobile apps, maintaining a single, consistent view of a customer’s purchasing history, preferences, and
interactions was nearly impossible. This made personalized marketing, effective loyalty programs, and consistent customer service across touchpoints extremely difficult.

5. Data Anomalies: The unintegrated nature of the systems led to various data anomalies.

• Insertion Anomalies: It was difficult to record details about a new product
  until it was associated with an order, or to register a new customer without an
  immediate purchase.
• Deletion Anomalies: Deleting a product entry from one system might inadvertently erase vital sales history associated with it in another, or removing a customer record might delete their entire transaction history, which was needed for analytics.
• Update Anomalies: Changing a supplier’s address required updating numerous scattered records, increasing the risk of inconsistencies if some instances were missed.

The Solution: Embracing a Relational Database Management System

Recognizing these inefficiencies and the impeding impact on growth, IKEA Furniture Center initiated a strategic shift towards a centralized RDBMS. The goal was to consolidate data, enforce data integrity, and provide a single source of truth for all business operations. This new system was designed to model the enterprise data efficiently, capture complex relationships, and provide robust data definition and manipulation capabilities.

The core of the solution involved identifying key business entities such as Customers, Products, Orders, Order Details, Employees, Stores, and Suppliers. Relationships between these entities were carefully mapped, considering appropriate cardinalities (e.g., one-to-many between Customers and Orders, many-to-many between Products and Orders (resolved via Order Details)).

The design process emphasized normalization, aiming to eliminate redundancy and improve data integrity, typically striving for 3rd Normal Form (3NF). This involved breaking down large, complex tables into smaller, related tables, defining appropriate primary keys for unique identification, foreign keys to establish relationships, and identifying candidate keys and composite keys where necessary.

The implementation leveraged SQL (Structured Query Language) for its power in Data Definition Language (DDL) commands (e.g., CREATE TABLE, ALTER TABLE, DROP TABLE) to define the database schema and enforce constraints, and Data Manipulation Language (DML) commands (e.g., INSERT, UPDATE, DELETE, SELECT) for managing the actual data within the tables. Tools like MySQL Server and MySQL Workbench were chosen for their robust capabilities in database design, implementation, and administration.

The Transformation: Improved Efficiency and Insights

The transition to an RDBMS brought about a significant transformation for IKEA Furniture Center:

• Integrated Data View: All departmental data, from sales transactions to inventory levels and customer profiles, became accessible through a unified system. This eliminated data silos, providing a holistic and accurate view of business operations.
• Enhanced Data Integrity: Normalization and the enforcement of referential integrity rules through foreign keys drastically reduced data inconsistencies and redundancy. Anomalies that plagued the old systems were largely mitigated.
• Real-time Decision Making: With consolidated and consistent data, managerscould generate complex reports and perform ad-hoc queries using SQL, enabling real-time insights into sales trends, stock availability, and customer behavior. This facilitated proactive adjustments to marketing campaigns, inventory levels, and staffing.
• Seamless Customer Experience: A single customer master record allowed for a unified view of customer interactions across all channels. This enabled personalized recommendations, efficient query resolution, and consistent service regardless of whether the customer interacted online or in-store.
• Operational Efficiency: Automated data management processes replaced manual efforts, reducing errors and freeing up staff to focus on more strategic tasks.

This strategic investment in a robust relational database system allowed IKEA Furniture Center to not only overcome its initial data management challenges but also to establish a scalable, efficient, and data-driven foundation for future growth and continued customer satisfaction.

Final Assessment Questions

Task 1

Explain the various data models and enterprise data modeling.

IKEA Furniture Center transitioned from a fragmented data landscape to a centralized Relational Database Management System (RDBMS).

a. Describe, in your own words, what a “fragmented data landscape” entails, as
experienced by IKEA Furniture Center before the RDBMS implementation. Provide specific examples from the case study to support your explanation.

b. Explain how the adoption of an RDBMS fundamentally addresses the problems associated with this fragmented data landscape.

Task 2

Explain the various data models and enterprise data modeling.

The case study highlights several “Data Anomalies” that plagued IKEA Furniture Center before the RDBMS implementation.

a. Identify and briefly explain the three types of data anomalies mentioned in the case study. For each anomaly, provide a clear, concise example distinct from those given in the document, but still relevant to a furniture retail context.

b. Explain how proper database normalization, specifically striving for 3rd Normal Form (3NF), helps mitigate these data anomalies.

Task 3

Explain the various data models and enterprise data modeling.

The core of IKEA Furniture Center’s RDBMS solution involved identifying key business entities and mapping their relationships.

a. List the six key business entities explicitly mentioned as the core of the solution in the case study.

b. For three of these entities (choose any three), propose appropriate data types for at least three attributes each (e.g., CustomerID as INT, ProductName as VARCHAR). Explain why the chosen data type is suitable for each attribute. You cannot use the examples CustomerID and ProductName in your answer.

c. Using standard Crow’s Foot notation, draw an Entity-Relationship (ER) diagram snippet for two of the entity relationships described in the case study. Ensure you clearly indicate the entities, attributes (primary and foreign keys only), and cardinalities.

Task 4

Apply data definition and data manipulation languages.

Based on the entities and relationships described in the case study, you are tasked with creating a simplified database schema for the IKEA Furniture Center. Screenshot your work, paste the image in the answer booklet. Make sure the image is properly numbered.

a. Write a SQL DDL script to create three tables: Customers, Products, and Orders.

i) For the Customers table, include columns for CustomerID, FirstName, LastName, Email, and PhoneNumber. Choose one of the attributes as a `primary key.

ii) For the Products table, include columns for ProductID (Primary Key),
ProductName, Description, Price (DECIMAL with appropriate precision and
scale), and StockQuantity.

iii) For the Orders table, include columns for Order ID (Primary Key), Customer ID (Foreign Key referencing Customers), Order Date, and Total Amount.

iv) Ensure appropriate data types are used for each column.

v) Define primary key constraints.

vi) Define the foreign key constraint for CustomerID in the Orders table, referencing the Customers table.

vii) Add a CHECK constraint to the Products table to ensure StockQuantity is not negative.

viii)Add a UNIQUE constraint to the Customers table to ensure Email addresses are unique.

b. SQL Output: Show the SQL output indicating successful table creation (e.g., Query OK, 0 rows affected).

Task 5

Apply data definition and data manipulation languages.

Now that the tables are created, you need to populate them with sample data and perform some basic data manipulation operations for the IKEA Furniture Center.

a. DML Script (INSERT): Write a SQL DML script to insert at least:

i) Three (3) records into the Customers table.

ii) Four (4) records into the Products table.

iii) Two (2) records into the Orders table, ensuring they link to existing customers and reflect plausible total amounts based on products (you don’t need an OrderDetails table for this task, just a reasonable TotalAmount).

b. DML Script (UPDATE): Write a SQL DML script to perform the following updates:

i) Update the StockQuantity for one product in the Products table, simulating a sale or restock.

ii) Update the PhoneNumber for one customer in the Customers table.

c. DML Script (DELETE): Write a SQL DML script to delete one record from the Products table

d. SQL Output: For each DML operation (INSERT, UPDATE, DELETE), show the SQL output indicating successful execution (e.g., Query OK, X rows affected). Also, show the SELECT * FROM TableName; output after all insertions, updates, and deletions have been performed, to demonstrate the final state of each table.

Task 6

Apply data definition and data manipulation languages.

The case study emphasizes the shift towards real-time decision-making enabled by the RDBMS. Managers need to quickly retrieve consolidated data.

a. DML Script (SELECT): Write a SQL DML script to:

i) Retrieve the FirstName, LastName, and Email of all customers who have placed an order, along with their OrderID and OrderDate. Order the results by Order Date (oldest first).

ii) Find the ProductName and Price of all products that have a StockQuantity less than 10.

b. SQL Output: Show the output of both SELECT queries.

Task 7

Apply data definition and data manipulation languages.

IKEA often needs to adjust its product offerings or pricing strategies. Suppose IKEA decides to introduce a new product category or update details for an existing one.

a. DDL Script (ALTER TABLE): Write a SQL DDL script to add a new column named Category (e.g., ‘Bedroom’, ‘Living Room’, ‘Kitchen’) to the Products table. Ensure the Category column can store varying lengths of text.

b. DML Script (UPDATE): After adding the new column, write a SQL DML script to update the Category for all existing products, assigning them relevant categories.

c. SQL Output: Show the SQL output for the ALTER TABLE command and the UPDATE command. Also, show the SELECT * FROM Products; output after these operations to demonstrate the updated Products table schema and data.

Task 8

Explain the various data models and enterprise data modeling and problemsolving/Applied Knowledge.

The case study mentions “Deletion Anomalies” as a significant problem before the RDBMS implementation, where deleting a record might inadvertently erase vital associated information.

a. Scenario Description: Imagine a simplified, non-normalized table named
Product Sales Info that combines product details with sales history. This table might look like: Product Sales Info (Product ID, Product Name, Product Description, Price, Order ID, Order Date, Customer ID). Provide a concrete example of how a “deletion anomaly” could occur in this Product Sales Info table if you were to delete a Product ID record. Explain what vital information would be lost.

b. RDBMS Mitigation: Explain how a properly normalized relational database design, specifically utilizing separate tables for Products and Orders (and potentially OrderDetails), prevents this deletion anomaly. Refer to the concepts of primary and foreign keys in your explanation.

c. DML Script (Demonstration of Anomaly Prevention): Using the Products and Orders tables created in Task 4, write a SQL DML script that attempts to delete a product that currently has associated orders.

d. SQL Output & Explanation: Show the SQL output of this deletion attempt. Explain why the RDBMS (due to referential integrity constraints) prevents the anomaly from occurring directly, thus safeguarding data integrity.

Task 9

Apply data definition and data manipulation languages and creativity/problem-solving.

IKEA wants to analyze the sales performance of its products, specifically identifying which products are frequently ordered together or are popular within specific date ranges. Since the Orders table only has TotalAmount and not individual product details per order, this requires a
more complex solution.

a. DDL Script (CREATE TABLE): Design and create a new table called Order Details that resolves the many-to-many relationship between Products and Orders. This table should include Order Detail ID (Primary Key), Order ID (Foreign Key to Orders), Product ID (Foreign Key to Products), Quantity, and Unit Price (representing the price of the product at the time of the order).

b. DML Script (INSERT): Populate the Order Details table with at least five (5) realistic records that link to your existing Orders and Products data, ensuring Unit Price matches the Price of the Product at the time of insertion, and Quantity is reasonable. These insertions should reflect items within the Total Amount of your existing orders, even if simplified.

c. DML Script (SELECT with JOIN and Aggregation): Write a SQL DML script to find the total quantity sold for each product and list the Product Name and Total Quantity Sold, ordered by Total Quantity Sold in descending order.

d. SQL Output: Show the SQL output for the CREATE TABLE and INSERT commands. Show the SELECT * FROM Order Details; output after insertions. Finally, show the output for the aggregated product sales query.

Experts Answer on Above Questions on Database Management

Fragmented data landscape

The data is stored separately in a fragmented data landscape without any kind of integration. At IKEA, there are separate systems utilised with respect to sales, inventory, customer service and supplies in management. This separation leads to inconsistent stock records, duplicate customer information and delay in the reporting process.

Solving problems – RDBMS can help by centralising data into one database, and it will help in reducing duplication and inconsistencies. It also supports data integrity, allows for real time reporting, and provides a single view of customers and business operations.

Data anomalies

The anomalies are insertion, deletion and update whereby insertion restricts the addition of data without related data, deletion removes data by erasing important information, and update anomaly is updating the same information in multiple places. In respect to furniture retail examples, the inability to add a new sofa until it is sold is an example of insertion anomaly, whereas deleting the record of a chair leading to removal of its entire sales history is an example of deletion anomaly. The requirement for changing the name of suppliers’ phone numbers across several records is an example of update.
How 3NF helps – it helps by eliminating redundant data, storing each fact only once, reduces all anomalies and improves consistency and integrity.

Six business entities

The business entities are customers, products, orders, employees, suppliers and stores.
Data types – For customers, first name – VARCHAR(50), Email – VARCHAR (100) and Phone Number – VARCHAR(20). For products, price – DECIMAL, StockQuantity – INT, and Description – TEXT. Orders include OrderDate – DATE, TotalAmount – DECIMAL and CustomerID – INT.

Want Detailed Answers with References?

With our programming experts in Malaysia, you can order complete assistance with writing assignments including detailed SQL codes. Simply visit our assignment help in Malaysia page to get a professional programming assignment helper, or access completed answers of assignments from Malaysia University of Science and Technology before proceeding to order assignments from us.