The case study retail store has provided a list of reports and data manipulation tasks that are needed in the processing of orders for their customers. Answer the following: What structured query lang
Database System for the Retail Store
In the current environment, one of the main success factors for retailers is the ability to manage data efficiently with special reference to order fulfillment, stock control, and supply chain. In light of the business needs of the retail store, the proposed relational database system will serve to automate existing processes. This paper also describes the subjects of interest and business rules, and an entity-relationship (E-R) diagram to define the Relational Database System and must be kept in Third Normal Form (3NF) for an efficient and redundant-free database.
Subjects of Interest (Proposed Entities)
The main objects of concern, which will be utilized as objects in the database, correspond to the store’s functions. These entities ensure proper tracking and management of business processes:
Customers: This entity holds all particulars relative to the customer including customer ID, name, email, and address.
Orders: This entity is used to denote the transactions that include order number, customer number, order date, and total price.
Products: This entity contains information concerning products including their identification number, name, description, price, and quantity available.
Additional Entities:
Categories: Helps the store to categorize products thus, the store can sort its products based on various classifications such as electronic products, grocery products, and so on.
Suppliers: Contains records concerning suppliers who deliver products to the store.
Payments: Handles payment information such as payment options, cost, and confirmation of the order.
Business Rules
A store implements certain regulations that define the management of data at the store. These are very important business rules which when followed would ensure that the database is in line with the store operations:
The implementation of a single category for each product is important, especially for product differentiation.
Since a customer is a person who has purchased at least one product, it means data on customers will only contain records of customers who have placed an order at least once in the shop.
Every product has a less than which point an order needs to be placed, to help keep optimum stock in the store.
Additional Business Rules:
Every order must be related to a valid customer so that the whole process of the transaction will be traceable.
Products do not belong to a particular supplier, and a supplier may offer many products meaning there is a balance in the supply of products in the store.
Sales are recorded as soon as customers complete payment, guaranteeing that sales are only made after goods are sold to customers.
Entity-Relationship Model
ERM stands for entity-relationship model that will depict the key entities and their attributes, relationships, and constraints. The relationships between the entities will adopt crow’s foot notation for better understanding.
An order is one but it can be placed by many customers hence many orders are many customers but each order belongs to a particular customer.
Orders contain Products: One order can have more than one product to it and this is covered under the OrderLineItem table.
Products belong to Categories: Individual products can be linked to just one category.
Suppliers Provide Products: To fulfill this many-to-many relationship, a linking table will be applied, for a supplier, he may supply many products and on the other hand, many suppliers may supply one product.
Normalization (Adhering to 3NF)
To avoid redundancy and the database being inefficient, it should be normalized up to the Third Normal Form (3 NF). The normalization process will take the following activities:
First Normal Form (1NF): In each of the tables there are no repeating groups, and all the values presented are atomic. For instance, the customers’ records will be organized whereby each record will be in different fields instead of a column.
Second Normal Form (2NF): The non-answer key attributes are fully dependent on the answer key. For example, in OrderLineItem sub-class, every line item is related to and dependent on the OrderID as well as ProductID.
Third Normal Form (3NF): Nonprimary key attributes cannot be depended on other non-primary key attributes. For example, supplier information will be stored in the Suppliers entity distinct from the Products entity, with corresponding foreign keys.
By following the 3NF, the database will reduce redundancies and improve the integrity of data as well as effective querying.
Entity-Relationship Diagram (ERD)
The E-R diagram to be drawn using Microsoft Visio or another effective tool will illustrate the relationships between entities and their attributes including:
Customers: Customer ID & Primary key, Name, Email address, Address.
Orders: There are four attributes which include: OrderID as a primary key, the CustomerID as a foreign key, OrderDate, and TotalAmount.
Products: ProductID (PK), ProductName, CategoryID (FK), Price, QuantityInStock
Categories: CategoryID (PK), CategoryName
Suppliers: SupplierID (PK), Supplier Name, Contact information
Payments: PaymentID (PK), OrderID (FK), PaymentMethod_ID (PK), Amount, MethodID (FK)
The diagram will help have a clear view of how these entities are related, for instance, customers and orders are one too many while products and suppliers are many too many.
How the System Fulfills Business Goals
The database design proposed for the store will help the store management to achieve its intended goals of increasing operational productivity, delivering high-quality service to the customers, and also managing the inventory efficiently:
Improving Inventory Management: The use of real-time inventory will assist the store in determining the right time to order stock and avoid running out of stock.
Supporting Online Purchases: The database system will be an extension to the store’s website where customers will be able to order goods online implying that the option will create goodwill and potentially raise sales.
Enhancing Supplier and Payment Management: Through the creation of a database, products will be restocked promptly, and payments will be made before products are shipped to customers with minimal or no mistakes.
Conclusion
The relational database system for the retail store proposed here can tackle some of the business demands, such as order processing, inventory, as well as supplier information management. Normalizing the database to 3NF and having a clear English-like entity-relationship model will help to ensure that the system is optimized for data loading and retrieval, is easily scalable, and is as free as possible from redundant data fields. This system will ensure that the store functions efficiently and delivers its mission statement, hence achieving good customer service and proper supply chain management.
References
Coronel, C., & Morris, S. (2019). Database systems: Design, implementation & management (13th ed.). Cengage Learning.
Dewi, D. W., & Ayuningsih, E. (2022). Web-based design of e-commerce for small and medium enterprises in Bengabing village. INFOKUM, 10(2), 838-848.
Hosen, M. S., Islam, R., Naeem, Z., Folorunso, E. O., Chu, T. S., Al Mamun, M. A., & Orunbon, N. O. (2024). Data-driven decision making: Advanced database systems for business intelligence. Nanotechnology Perceptions, 687-704.
Seifollahi, N. (2023). The impact of customer knowledge management on market performance: The mediating role of innovation capabilities. Public Management Researches, 15(58), 233-259.
Tian, Y., & Kamran, Q. (2021). A review of antecedents and effects of loyalty on food retailers toward sustainability. Sustainability, 13(23), 13419
