Database Systems Development and Implementation Plan
Abstract
This paper presents a Database Systems Development and Implementation Plan for Small Fresh Retail Inc, a fictional company, focusing on the payment scope encompassing product management and customer payments. The design incorporates tables for products, orders, customers, and payments, fulfilling the organization's goals of efficient product management, payment processes, enhanced customer experience, and reliable data management. The database enables centralized product information storage, accurate tracking of orders and customer payments, and maintains data integrity through relationships and constraints. The design aligns with the organization's mission by facilitating effective product management, smooth financial operations, personalized customer interactions, and reliable decision-making processes. This plan serves as a foundation for implementing a robust and efficient database system within Small Fresh Retail Inc.
Database Systems Overview
General business environment
The fresh retail industry plays an important role in meeting consumers' daily needs for high-quality fresh products. However, they often face various challenges such as the seasonality of products, price volatility, supply chain disruptions, and changing consumer preferences. To adapt to this industry, businesses must analyze and making-decisions in the dynamic business environment they operate in. The need for good technology is necessary in addressing these challenges and driving business growth.
Market Demand: In recent years, there has been an increasing demand for fresh retail by consumers' growing interest in healthy and organic food options. Consumers are becoming more careful of their health, and they are choosing fresh products. The COVID-19 pandemic has had a significant impact on consumer behavior and shopping habits, and it may have influenced the demand for fresh retail in various ways. Following the report by Deloitte China (2019), there has been an increasing demand for fresh retail since 2015. Especially, the need for fresh food e-commerce is increasing faster during the outbreak. The analysis of Vo, Thien, and Nassif (2023) showed that fresh goods such as meat, fish, eggs, vegetables, fruits, and milk are required by customers from residential areas.
Supply Chain: The supply chain in the fresh retail industry includes many complex processes such as sourcing raw goods, production, warehouse, shipping, and retailing. Efficient supply chain management ensures the availability of fresh products, maintains quality standards, minimizes wastage, and meets customer expectations for timely delivery.
Technological Advances: Technology plays an important role in optimizing operations and enhancing customer experience in the fresh retail industry. Advancements in inventory management systems, online platforms, shipping processes, and data analytics enable businesses to improve supply chain operations, personalize marketing strategies, and predict consumer behavior.
Data Analytics: The availability of vast amounts of data in the fresh retail industry presents an opportunity for businesses to leverage data analytics tools. According to Retail Insight (2023), by analyzing consumer purchasing patterns, preferences, and behaviors, businesses can gain valuable insights that inform decision-making processes. These insights can be used to optimize product assortments, tailor marketing strategies, and improve overall business performance.
All in all, the fresh retail industry operates in a dynamic and challenging business environment. The adoption of technology is important for businesses to overcome these challenges, adapt to changing consumer preferences, and drive growth. Technology provides fresh retail businesses with tools and opportunities to stay competitive, deliver fast high-quality products, and meet the evolving needs of consumers.
Database system goals and objectives
Database design is very important for retailers to ensure efficient storage, retrieval, and management of data. The retail industry collects amounts of data from various sources which are sales transactions, customer data, and inventory management. The goals and objectives of a database system are tailored to address specific challenges faced by retailers. This session explores these goals and objectives, focusing on their importance in managing data effectively and enabling retailers to analyze and making-decision their business.
Data Organization and Integration: In the fresh retail project, the data is very complex and needs a well-designed database that ensures efficient storage and organization of data. One of the primary goals of a database system in the retail industry is to efficiently organize and ability to integrate data from various sources, such as sales, inventory, and customer databases.
Customer Relationship Management: Customer data in the retail industry is very important. It provides an understanding of customer data, behavior, and purchase history. The important goal of a database system in the retail industry is the ability to store and manage customer data, allowing retailers to analyze the information, predict the need, and target the right customers.
Continuous inventory management: Continuous inventory management is an ability to inventory control. It continuously keeps tracking how much is sold and what inventory needs. With continuous inventory management, retailers can track sales in real-time as transactions occur.
Analytics and Business Intelligence: Database systems in the retail industry should support analytics and business capabilities. They should provide tools for data analysis, reporting, and visualization, allowing retailers to identify trends, optimize pricing strategies, and improve business processes.
Database systems play a main role in the retail industry, enabling retailers to effectively manage and leverage their data assets. The goals and objectives discussed in this session include data organization, integration, customer relationship management, inventory management, and analytics. They provide a comprehensive framework for retail database systems.
How database addresses business problems
Databases offer robust solutions to address retail business problems, providing real-time data management, data-driven decision-making, and enhanced customer experiences. This session explores how databases enable fresh retail businesses to overcome challenges.
Inventory Management:
Effective inventory management is important in the fresh retail industry to ensure product availability, minimize waste, and meet customer demands. Databases enable businesses to maintain accurate and real-time inventory records, track stock levels, and automate replenishment processes. By integrating data from various sources, including sales, suppliers, and warehouses, databases provide a centralized view of inventory, optimizing ordering and reducing stockouts.
Supply Chain Optimization:
The fresh retail industry's supply chain involves multiple stages, including sourcing, production, transportation, and distribution. Databases facilitate supply chain optimization by integrating data from different sources and enhancing visibility. With real-time updates on inventory levels, delivery schedules, and demand patterns, businesses can make informed decisions and minimize disruptions.
Customer Relationship Management (CRM):
Databases play a main role in managing customer relationships in the fresh retail industry. By capturing and analyzing customer data, including purchase history, preferences, and feedback, databases enable businesses to personalize marketing efforts, offer tailored promotions, and improve customer satisfaction. CRM functionalities within databases allow businesses to target the right customers, drive repeat purchases, and improve loyalty processes.
Data Analytics and Forecasting:
Data analytics is crucial for fresh retail businesses to gain insights into customer behavior, market trends, and product performance. Databases provide the foundation for data analytics by storing and organizing vast amounts of transactional and customer data. Leveraging analytics tools and techniques, businesses can perform trend analysis, demand forecasting, and market segmentation, enabling effective decision-making and proactive planning.
Traceability and Compliance:
Fresh retail businesses must comply with regulations and maintain the traceability of products. Databases play a crucial role in tracking product origins, expiration dates, and quality control information. By recording and managing this data, databases enable businesses to ensure compliance, mitigate risks, and maintain consumer trust in product quality and safety.
Databases offer powerful solutions to address key business problems in the fresh retail industry. Adopting databases effectively allows fresh retail businesses to optimize inventory management, improve supply chain operations, enhance customer relationships, utilize data analytics, improve operational efficiency, and ensure compliance. This session highlights the transformative impact that databases have on the fresh retail industry, enabling businesses to drive growth and enhance customer satisfaction.
How database system aligns to mission statement and strategic goals.
The previous sessions provide an overview of the importance of mission statements and strategic goals in the retail industry. It also introduces the role of database systems in harnessing data to achieve these objectives. This session focuses on how database systems align with the mission statements and strategic goals of retail businesses, enabling them to fulfill their purpose, meet customer expectations, and achieve growth.
Efficient data management: Database systems enable retail businesses to efficiently store, organize, and manage complex and big data. By utilizing normalization techniques, businesses can eliminate data duplication and ensure data integrity. This aligns with the strategic goal of maintaining accurate and reliable data. The use of data models, such as entity-relationship (ER) diagrams and Unified Modeling Language (UML) diagrams, simplifies the representation of data requirements. According to Teorey et al. (2011), these models facilitate understanding for both the database designer and the end user. They serve as valuable tools for verifying assumptions and capturing semantic details in a visual manner. Consequently, the need for lengthy explanations to communicate certain requirements and constraints can be reduced.
Comprehensive understanding of data entities: Through the analysis stage of database design, retail businesses can identify the core entities relevant to their specific domain. This involves understanding different types of data entities, their attributes, and their relationships. By aligning the database design with the specific requirements of the business, the mission statement and strategic goals can be effectively supported. According to Chudinov, Osipova, and Bobrova (2017), they proposed a methodology consisting of three stages in their paper. The first stage involves the analysis of needed information, where businesses can thoroughly assess their specific domain and identify the core entities that are relevant to their operations. This allows businesses to understand the key components of their data and determine the relationships and dependencies between them.
Data integrity and quality: Database systems allow retail businesses to define constraints, data types, and validation rules to maintain data integrity and quality. By enforcing these rules, businesses can prevent the entry of inconsistent, inaccurate, or invalid data. This aligns with the mission statement of providing reliable and high-quality products and services.
Teorey et al. (2011) highlighted the importance of the data design and modeling phase that help businesses define constraints, data types, and validation rules to ensure data integrity and quality. For example, businesses can specify that a certain attribute must be unique or that a certain attribute must have a specific format. By enforcing these rules, the database system can prevent the entry of inconsistent, inaccurate, or invalid data. This contributes to maintaining high data integrity and ensures that the data stored in the database is reliable and of high quality.
Scalability and adaptability: As the retail business grows, the database system needs to handle the increasing data load and adapt to changing business requirements. Database systems offer different options, such as centralized systems or distributed database systems, which can be selected based on the scalability and performance needs of the organization. This aligns with the strategic goal of supporting business growth and ensuring the system can handle expanding data volumes. In addressing these scalability issues, Teorey et al. (2011) provides numerous techniques in their work. These techniques can include implementing appropriate indexing methods to optimize data query, and partitioning the database to distribute data across multiple storage units. Moreover, according to Watt and Eng (2014) work offers classifications of systems that can be applied to the database context, such as centralized systems or distributed database systems. These classifications provide different architectural approaches that can enhance the scalability of a database.
In conclusion, the use of database systems in retail businesses plays a vital role in achieving their mission statements and strategic goals. Through efficient data management, understanding of data entities, data integrity, and scalability and adaptability features, database systems provide the necessary foundation for retail businesses' requirements and meet customer expectations.
Entity-Relationship Model
Subjects of Interest
Designing a comprehensive database for a fresh retail store is a complex task that demands careful consideration of numerous factors, including various subjects and business rules. This process involves capturing and organizing vast amounts of data related to product management and customer payment processes. Database design for a fresh retail store has multiple facets, such as inventory management, customer profiles, etc. However, in the scope of this paper, our focus will be specifically on the payment aspect, examining the intricate interplay between product management and the customer payment process.
Here are the eight subjects of interest (tables) for the Fresh Retail Scenario:
ID
Subject
Description
1
Customer
This table will store information about the customers who make purchases in the Fresh Retail system.
2
Category
The Category table represents different categories of products in the Fresh Retail system.
3
Product
This table will contain details about the products available in the Fresh Retail system.
4
PriceHistory
This table will track the historical price changes for products.
5
Orders
This table will record the orders placed by customers.
6
OrderDetails
The OrderDetails table represents the details of individual products included in an order within the Fresh Retail system
7
DeliveryAddress
The DeliveryAddress table stores the delivery addresses associated with customers in the Fresh Retail system.
8
Employee
This table will hold data related to the employees working in the Fresh Retail system.
9
Payment
This table will track the payments made by customers for their orders.
Table 1: Subjects of Interest
Business Rules
Business rules are essential for defining the relationships and constraints between different entities in the Fresh Retail system. These bellow rules define the relationships and constraints between different entities in the fresh retail system. They provide guidelines for how the data should be organized and connected in the database, ensuring accurate representation and efficient operations within the system.
Customer:
Each customer must have a unique CustomerID.
The FirstName and LastName fields of a customer must be provided and should not be empty.
The Email field of a customer must be provided and should be a valid email address.
The Phone field of a customer must be provided and should be a valid phone number.
Category:
Each category must have a unique CategoryID.
The Name field of a category must be provided and should not be empty.
Product:
Each product must have a unique ProductID.
The Name field of a product must be provided and should not be empty.
The Price field of a product must be provided and should be a valid decimal value.
The Stock field of a product must be provided and should be a non-negative integer.
The CategoryID field of a product must be provided and should reference an existing CategoryID in the Category table.
Employee:
Each employee must have a unique EmployeeID.
The FirstName and LastName fields of an employee must be provided and should not be empty.
The Position field of an employee must be provided and should not be empty.
The AccessPrivileges field of an employee must be provided and should indicate the access privileges of the employee.
(*) We assume that the company is small, so we are managing only basic information for each employee.
PriceHistory:
Each price history entry must have a unique combination of ProductID and EffectiveDate.
The Price field of a price history entry must be provided and should be a valid decimal value.
The EffectiveDate field of a price history entry must be provided and should be a valid date.
The ProductID field of a price history entry should reference an existing ProductID in the Product table.
Orders:
Each order must have a unique OrderID.
The OrderDate field of an order must be provided and should be a valid date.
The CustomerID field of an order must reference an existing CustomerID in the Customer table.
The EmployeeID field of an order must reference an existing EmployeeID in the Employee table.
The TotalAmount field of an order must be provided and should be a valid decimal value.
The DeliveryAddressID field of an order must reference an existing AddressID in the DeliveryAddress table
DeliveryAddress:
Each delivery address must have a unique AddressID.
The CustomerID field of a delivery address must reference an existing CustomerID in the Customer table.
The Street, City, State, PostalCode, and Country fields of a delivery address must be provided and should not be empty.
OrderDetails:
Each order detail must have a unique OrderDetailID.
The OrderID field of an order detail must reference an existing OrderID in the Orders table.
The ProductID field of an order detail must reference an existing ProductID in the Product table.
The Quantity field of an order detail must be provided and should be a positive integer.
Payment:
Each payment must have a unique PaymentID.
The OrderID field of a payment must reference an existing OrderID in the Orders table.
The CustomerID field of a payment must reference an existing CustomerID in the Customer table.
The Amount field of a payment must be provided and should be a valid decimal value.
The PaymentDate field of a payment must be provided and should be a valid date.
The PaymentMethod field of a payment must be provided and should indicate the payment method used.
Entitles, attributes, relationships & cardinality constraints
Entities (Tables)
Attributes
(Rows)
Relationships
Cardinality Constraints
Customer
CustomerID (PK) (int)
1:1
FirstName (varchar(50))
LastName (varchar(50))
Email (varchar(100))
Phone (varchar(20))
DeliveryAddress
AddressID (PK) (int)
1:1
CustomerID (int)
Has Delivery Addresses
1:Many
Street (varchar(100))
City (varchar(50))
State (varchar(50))
PostalCode (varchar(20))
Country (varchar(50))
Category
CategoryID (PK) (int)
1:1
Name (varchar(50))
Product
ProductID (PK) (int)
1:1
Name (varchar(100))
Description (text)
Price (decimal(10,2))
Stock (int)
CategoryID (int)
Has Products
1:Many
PriceHistory
ProductID (int)
Has Prices
1:Many
Price (decimal(10,2))
EffectiveDate (date)
ChangeReason (text)
Employees
EmployeeID (PK) (int)
1:1
FirstName (varchar(50))
LastName (varchar(50))
Position (varchar(50))
AccessPrivileges (varchar(100))
Orders
OrderID (PK) (int)
1:1
OrderDate (date)
CustomerID (int)
Has Orders
1: Many
EmployeeID (int)
Has Orders
1:Many
TotalAmount (decimal(10,2))
DeliveryAddressID (int)
Associated to Order
1:1
OrderDetails
OrderDetailID (PK) (int)
1:1
OrderID (int)
Has OrderDetails
1:Many
ProductID (int)
Has OrderDetails
1:Many
Quantity (int)
Payment
PaymentID (PK) (int)
1:1
OrderID (int)
Has Payments
1:Many
CustomerID (int)
Has Payments
1:Many
Amount (decimal(10,2))
PaymentDate (date)
PaymentMethod (varchar(50))
Table 2: Entitles, attributes, relationships & cardinality constraints
Entity Relationship Diagram (ERD)
Figure 1 - Table ER Diagram (Small Fresh Retail Inc)
How does this design fulfill the mission and/or goals of your target organization?
The design of the database system for the Fresh Retail scenario fulfills the mission and goals of the organization by providing a structured platform for managing customer information, orders, products, employees, payments, and other relevant aspects of the retail business. The design ensures data integrity, accuracy, and consistency, allowing for smooth operations and enhanced customer satisfaction.
The design organizes data into separate entities and establishes relationships between them. This enables efficient storage, retrieval, and manipulation of data, facilitating better data management for the organization's processing.
By using primary and foreign keys, the design ensures data integrity and prevents inconsistencies. This helps in maintaining accurate and reliable information within the database, which is important for decision-making processes.
By organizing customer information, such as names, contact details, and addresses, the database facilitates personalized customer service.
The inclusion of Delivery Address entities allows the organization to track customer information and manage their addresses effectively. This supports customer relationship management activities, such as order processing, delivery, and personalized customer service.
The association of orders with customers enables order tracking and efficient order processing.
The Product and Category entities provide a structure for managing product information and categorization. The inclusion of product details, categories, and historical price records improves product management, inventory control, and pricing strategies.
The Orders, Order Details, and Payment entities enable efficient order processing and payment tracking. This helps the organization improve sales processes, track order details, and manage customer payments. In addition, by storing the payment method chosen by each customer, Fresh Retail can offer a diverse range of payment options, such as credit cards, debit cards, cash on delivery, mobile payment apps, and more.
The Employee entity captures employee information and access privileges, facilitating employee management which brings many advantages to the task assignment and security measures
In conclusion, the design of the database system aligns with the mission and goals of the Fresh Retail organization by enhancing operational efficiency, customer satisfaction, and financial management.
Structured Query Language
Data manipulation scripts
ID
Table
Script
1
Customer
CREATE TABLE Customer (
CustomerID SERIAL PRIMARY KEY NOT NULL,
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL,
Email VARCHAR(100) NOT NULL,
Phone VARCHAR(20) UNIQUE NOT NULL,
CreatedBy VARCHAR(50) NOT NULL,
UpdatedBy VARCHAR(50) NOT NULL,
CreatedAt TIMESTAMP NOT NULL,
UpdatedAt TIMESTAMP NOT NULL
);
2
Category
CREATE TABLE Category (
CategoryID SERIAL PRIMARY KEY NOT NULL,
Name VARCHAR(50) NOT NULL,
CreatedBy VARCHAR(50) NOT NULL,
UpdatedBy VARCHAR(50) NOT NULL,
CreatedAt TIMESTAMP NOT NULL,
UpdatedAt TIMESTAMP NOT NULL
);
3
Product
CREATE TABLE Product (
ProductID SERIAL PRIMARY KEY NOT NULL,
Name VARCHAR(100) NOT NULL,
Description TEXT,
Price DECIMAL(10,2) NOT NULL,
Stock INT NOT NULL,
CategoryID INT NOT NULL,
CreatedBy VARCHAR(50) NOT NULL,
UpdatedBy VARCHAR(50) NOT NULL,
CreatedAt TIMESTAMP NOT NULL,
UpdatedAt TIMESTAMP NOT NULL,
CONSTRAINT fk_Product_Category FOREIGN KEY (CategoryID) REFERENCES Category(CategoryID)
);
4
Employee
CREATE TABLE Employee (
EmployeeID SERIAL PRIMARY KEY NOT NULL,
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL,
Position VARCHAR(50) NOT NULL,
AccessPrivileges VARCHAR(100) NOT NULL,
CreatedBy VARCHAR(50) NOT NULL,
UpdatedBy VARCHAR(50) NOT NULL,
CreatedAt TIMESTAMP NOT NULL,
UpdatedAt TIMESTAMP NOT NULL
);
5
PriceHistory
CREATE TABLE PriceHistory (
ProductID SERIAL NOT NULL,
Price DECIMAL(10,2) NOT NULL,
EffectiveDate DATE NOT NULL,
ChangeReason TEXT,
CreatedBy VARCHAR(50) NOT NULL,
UpdatedBy VARCHAR(50) NOT NULL,
CreatedAt TIMESTAMP NOT NULL,
UpdatedAt TIMESTAMP NOT NULL,
PRIMARY KEY (ProductID, EffectiveDate),
CONSTRAINT fk_PriceHistory_Product FOREIGN KEY (ProductID) REFERENCES Product(ProductID)
);
6
DeliveryAddress
CREATE TABLE DeliveryAddress (
AddressID SERIAL PRIMARY KEY NOT NULL,
CustomerID INT NOT NULL,
Street VARCHAR(100) NOT NULL,
City VARCHAR(50) NOT NULL,
State VARCHAR(50) NOT NULL,
PostalCode VARCHAR(20) NOT NULL,
Country VARCHAR(50) NOT NULL,
CreatedBy VARCHAR(50) NOT NULL,
UpdatedBy VARCHAR(50) NOT NULL,
CreatedAt TIMESTAMP NOT NULL,
UpdatedAt TIMESTAMP NOT NULL,
CONSTRAINT fk_DeliveryAddress_Customer FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)
);
7
Orders
CREATE TABLE Orders (
OrderID SERIAL PRIMARY KEY NOT NULL,
OrderDate DATE NOT NULL,
CustomerID INT NOT NULL,
EmployeeID INT NOT NULL,
TotalAmount DECIMAL(10,2) NOT NULL,
DeliveryAddressID INT NOT NULL,
CreatedBy VARCHAR(50) NOT NULL,
UpdatedBy VARCHAR(50) NOT NULL,
CreatedAt TIMESTAMP NOT NULL,
UpdatedAt TIMESTAMP NOT NULL,
CONSTRAINT fk_Orders_Customer FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID),
CONSTRAINT fk_Orders_Employee FOREIGN KEY (EmployeeID) REFERENCES Employee(EmployeeID),
CONSTRAINT fk_Orders_DeliveryAddress FOREIGN KEY (DeliveryAddressID) REFERENCES DeliveryAddress(AddressID)
);
8
OrderDetails
CREATE TABLE OrderDetails (
OrderDetailID SERIAL PRIMARY KEY NOT NULL,
OrderID INT NOT NULL,
ProductID INT NOT NULL,
Quantity INT NOT NULL,
CreatedBy VARCHAR(50) NOT NULL,
UpdatedBy VARCHAR(50) NOT NULL,
CreatedAt TIMESTAMP NOT NULL,
UpdatedAt TIMESTAMP NOT NULL,
CONSTRAINT fk_OrderDetails_Order FOREIGN KEY (OrderID) REFERENCES Orders(OrderID),
CONSTRAINT fk_OrderDetails_Product FOREIGN KEY (ProductID) REFERENCES Product(ProductID)
);
9
Payment
CREATE TABLE Payment (
PaymentID SERIAL PRIMARY KEY NOT NULL,
OrderID INT NOT NULL,
CustomerID INT NOT NULL,
Amount DECIMAL(10,2) NOT NULL,
PaymentDate DATE NOT NULL,
PaymentMethod VARCHAR(50) NOT NULL,
CreatedBy VARCHAR(50) NOT NULL,
UpdatedBy VARCHAR(50) NOT NULL,
CreatedAt TIMESTAMP NOT NULL,
UpdatedAt TIMESTAMP NOT NULL,
CONSTRAINT fk_Payment_Order FOREIGN KEY (OrderID) REFERENCES Orders(OrderID),
CONSTRAINT fk_Payment_Customer FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)
);
Table 2. Create scripts
ID
Table
Script
1
Customer
INSERT INTO Customer (CustomerID, FirstName, LastName, Email, Phone, CreatedBy, UpdatedBy, CreatedAt, UpdatedAt)
values
(1, 'John', 'Doe', '[email protected]', '1234567890', 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(2, 'Jane', 'Smith', '[email protected]', '9876543210', 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(3, 'Alice', 'Johnson', '[email protected]', '1111111111', 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(4, 'Bob', 'Williams', '[email protected]', '2222222222', 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(5, 'Emily', 'Brown', '[email protected]', '3333333333', 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(6, 'Michael', 'Jones', '[email protected]', '4444444444', 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(7, 'Olivia', 'Davis', '[email protected]', '5555555555', 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(8, 'William', 'Miller', '[email protected]', '6666666666', 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(9, 'Sophia', 'Wilson', '[email protected]', '7777777777', 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(10, 'James', 'Anderson', '[email protected]', '8888888888', 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(11, 'Emma', 'Taylor', '[email protected]', '9999999999', 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(12, 'Daniel', 'Thompson', '[email protected]', '1231231234', 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
2
Category
INSERT INTO Category (CategoryID, Name, CreatedBy, UpdatedBy, CreatedAt, UpdatedAt)
VALUES
(1, 'Fruits', 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(2, 'Beverages', 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(3, 'Disposable', 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(4, 'Instant Noodle', 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(5, 'Milk Products', 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(6, 'Frozen Food 2', 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(7, 'Food 3', 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(8, 'Organic', 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(9, 'Others', 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(10, 'Sweet', 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
3
Product
INSERT INTO Product (ProductID, Name, Description, Price, Stock, CategoryID, CreatedBy, UpdatedBy, CreatedAt, UpdatedAt)
VALUES
(1, 'Fresh Milk', 'Premium quality fresh milk', 3.99, 50, 8, 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(2, 'Organic Eggs', 'Farm-fresh organic eggs', 2.99, 30, 8, 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(3, 'Instant Noodles - Chicken Flavor', 'Quick and delicious chicken-flavored noodles', 0.99, 100, 4, 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(4, 'Organic Yogurt - Strawberry', 'Delicious strawberry-flavored organic yogurt', 1.99, 20, 8, 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(5, 'Whole Grain Cereal', 'Healthy whole grain cereal', 4.99, 15, 8, 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(6, 'Fresh Eggs', 'Farm-fresh eggs', 1.99, 40, 8, 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(7, 'Noodles - Beef Flavor', 'Tasty beef-flavored noodles', 0.99, 80, 4, 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(8, 'Low-Fat Milk', 'Low-fat and nutritious milk', 2.99, 25, 8, 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(9, 'Organic Eggs - Brown', 'Organic brown eggs from free-range chickens', 3.49, 35, 8, 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(10, 'Ramen Noodles - Spicy Flavor', 'Spicy and flavorful ramen noodles', 1.49, 60, 4, 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(11, 'Almond Milk', 'Creamy almond milk', 3.99, 30, 8, 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(12, 'Free-Range Eggs', 'Eggs from chickens raised in free-range conditions', 2.49, 45, 8, 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(13, 'Instant Noodles - Vegetable Flavor', 'Tasty vegetable-flavored instant noodles', 0.99, 70, 4, 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(14, 'Greek Yogurt - Blueberry', 'Creamy Greek yogurt with delicious blueberry flavor', 2.49, 20, 8, 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(15, 'Spaghetti', 'Classic spaghetti pasta', 1.99, 50, 4, 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(16, 'Soy Milk', 'Nutritious soy milk', 2.99, 35, 8, 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(17, 'Free-Range Organic Eggs', 'Organic eggs from free-range chickens', 4.49, 40, 8, 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(18, 'Cup Noodles - Shrimp Flavor', 'Tasty cup noodles with shrimp flavor', 1.29, 90, 4, 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(19, 'Oat Milk', 'Creamy and dairy-free oat milk', 3.49, 25, 8, 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(20, 'Rice Noodles', 'Thin and delicate rice noodles', 1.99, 60, 4, 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
4
Employee
INSERT INTO Employee (EmployeeID, FirstName, LastName, Position, AccessPrivileges, CreatedBy, UpdatedBy, CreatedAt, UpdatedAt)
VALUES
(1, 'John', 'Smith', 'Manager', 'Admin', 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(2, 'Jane', 'Doe', 'Sales Associate', 'Limited', 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(3, 'Michael', 'Johnson', 'Cashier', 'Limited', 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(4, 'Emily', 'Davis', 'Stock Clerk', 'Limited', 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(5, 'David', 'Anderson', 'Customer Service Representative', 'Limited', 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(6, 'Sarah', 'Wilson', 'Visual Merchandiser', 'Limited', 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(7, 'Matthew', 'Taylor', 'Inventory Manager', 'Limited', 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(8, 'Olivia', 'Thomas', 'Assistant Manager', 'Limited', 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(9, 'Daniel', 'Roberts', 'Sales Associate', 'Limited', 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(10, 'Sophia', 'Brown', 'Cashier', 'Limited', 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
5
DeliveryAddress
INSERT INTO DeliveryAddress (AddressID, CustomerID, Street, City, State, PostalCode, Country, CreatedBy, UpdatedBy, CreatedAt, UpdatedAt)
VALUES
-- Customer 1
(1, 1, '123 Main St', 'Cityville', 'Stateville', '12345', 'Countryland', 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(2, 1, '456 Elm St', 'Townsville', 'Stateville', '67890', 'Countryland', 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
-- Customer 2
(3, 2, '789 Oak St', 'Villagetown', 'Stateville', '34567', 'Countryland', 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(4, 2, '987 Pine St', 'Hamletville', 'Stateville', '89012', 'Countryland', 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
-- Customer 3
(5, 3, '321 Cedar St', 'Cityville', 'Stateville', '54321', 'Countryland', 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(6, 3, '654 Maple St', 'Townsville', 'Stateville', '87654', 'Countryland', 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
-- Customer 4
(7, 4, '987 Birch St', 'Villagetown', 'Stateville', '76543', 'Countryland', 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(8, 4, '210 Walnut St', 'Hamletville', 'Stateville', '21098', 'Countryland', 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
-- Customer 5
(9, 5, '543 Willow St', 'Cityville', 'Stateville', '98765', 'Countryland', 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(10, 5, '876 Oak St', 'Townsville', 'Stateville', '43210', 'Countryland', 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
-- Customer 6
(11, 6, '109 Elm St', 'Villagetown', 'Stateville', '56789', 'Countryland', 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(12, 6, '432 Pine St', 'Hamletville', 'Stateville', '90123', 'Countryland', 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
-- Customer 7
(13, 7, '765 Cedar St', 'Cityville', 'Stateville', '34567', 'Countryland', 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(14, 7, '098 Maple St', 'Townsville', 'Stateville', '89012', 'Countryland', 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
-- Customer 8
(15, 8, '210 Birch St', 'Villagetown', 'Stateville', '23456', 'Countryland', 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(16, 8, '543 Walnut St', 'Hamletville', 'Stateville', '78901', 'Countryland', 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
-- Customer 9
(17, 9, '876 Willow St', 'Cityville', 'Stateville', '32109', 'Countryland', 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(18, 9, '109 Oak St', 'Townsville', 'Stateville', '65432', 'Countryland', 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
-- Customer 10
(19, 10, '432 Elm St', 'Villagetown', 'Stateville', '98765', 'Countryland', 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(20, 10, '765 Pine St', 'Hamletville', 'Stateville', '21098', 'Countryland', 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
6
Orders
INSERT INTO Orders (OrderID, OrderDate, CustomerID, EmployeeID, TotalAmount, DeliveryAddressID, CreatedBy, UpdatedBy, CreatedAt, UpdatedAt)
VALUES
(1, '2023-07-01', 1, 1, 99.99, 1, 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(2, '2023-07-02', 2, 2, 149.99, 3, 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(3, '2023-07-03', 3, 3, 199.99, 5, 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(4, '2023-07-04', 4, 4, 249.99, 7, 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(5, '2023-07-05', 5, 5, 299.99, 9, 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(6, '2023-07-06', 6, 6, 349.99, 11, 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(7, '2023-07-07', 7, 7, 399.99, 13, 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(8, '2023-07-08', 8, 8, 449.99, 15, 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(9, '2023-07-09', 9, 9, 499.99, 17, 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(10, '2023-07-10', 10, 10, 549.99, 19, 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
7
OrderDetails
INSERT INTO OrderDetails (OrderDetailID, OrderID, ProductID, Quantity, CreatedBy, UpdatedBy, CreatedAt, UpdatedAt)
VALUES
-- Order 1
(1, 1, 1, 2, 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(2, 1, 3, 1, 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
-- Order 2
(3, 2, 2, 3, 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(4, 2, 4, 2, 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
-- Order 3
(5, 3, 3, 1, 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(6, 3, 5, 4, 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
-- Order 4
(7, 4, 4, 2, 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(8, 4, 6, 3, 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
-- Order 5
(9, 5, 5, 1, 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(10, 5, 7, 2, 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
-- Order 6
(11, 6, 6, 2, 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(12, 6, 8, 1, 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
-- Order 7
(13, 7, 7, 1, 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(14, 7, 9, 2, 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
-- Order 8
(15, 8, 8, 3, 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(16, 8, 10, 1, 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
-- Order 9
(17, 9, 9, 2, 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(18, 9, 11, 2, 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
-- Order 10
(19, 10, 10, 1, 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(20, 10, 12, 3, 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
8
Payment
INSERT INTO Payment (PaymentID, OrderID, CustomerID, Amount, PaymentDate, PaymentMethod, CreatedBy, UpdatedBy, CreatedAt, UpdatedAt)
VALUES
-- Payment for Order 1
(1, 1, 1, 1999.98, '2023-07-05', 'Credit Card', 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
-- Payment for Order 2
(2, 2, 1, 119.97, '2023-07-06', 'PayPal', 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
-- Payment for Order 3
(3, 3, 2, 221.96, '2023-07-07', 'Credit Card', 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
-- Payment for Order 4
(4, 4, 2, 189.96, '2023-07-08', 'Cash', 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
-- Payment for Order 5
(5, 5, 3, 55.97, '2023-07-09', 'PayPal', 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
-- Payment for Order 6
(6, 6, 3, 79.97, '2023-07-10', 'Credit Card', 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
-- Payment for Order 7
(7, 7, 4, 61.96, '2023-07-11', 'Cash', 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
-- Payment for Order 8
(8, 8, 4, 203.96, '2023-07-12', 'Credit Card', 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
-- Payment for Order 9
(9, 9, 5, 104.97, '2023-07-13', 'PayPal', 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
-- Payment for Order 10
(10, 10, 5, 249.97, '2023-07-14', 'Cash', 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO PriceHistory (ProductID, Price, EffectiveDate, ChangeReason, CreatedBy, UpdatedBy, CreatedAt, UpdatedAt)
VALUES
-- Price history for Product 1
(1, 999.99, '2023-01-01', 'Initial price', 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(1, 899.99, '2023-06-01', 'Discount promotion', 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
-- Price history for Product 2
(2, 19.99, '2023-01-01', 'Initial price', 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(2, 15.99, '2023-07-01', 'Summer sale', 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
-- Price history for Product 3
(3, 899.99, '2023-01-01', 'Initial price', 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(3, 799.99, '2023-05-01', 'Limited-time offer', 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
-- Price history for Product 4
(4, 24.99, '2023-01-01', 'Initial price', 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(4, 19.99, '2023-07-01', 'Clearance sale', 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
-- Price history for Product 5
(5, 12.99, '2023-01-01', 'Initial price', 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(5, 9.99, '2023-06-01', 'Special promotion', 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
-- Price history for Product 6
(6, 19.99, '2023-01-01', 'Initial price', 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(6, 16.99, '2023-07-01', 'Summer sale', 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
-- Price history for Product 7
(7, 29.99, '2023-01-01', 'Initial price', 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(7, 24.99, '2023-06-01', 'Discount promotion', 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
-- Price history for Product 8
(8, 599.99, '2023-01-01', 'Initial price', 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(8, 549.99, '2023-07-01', 'Limited-time offer', 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
-- Price history for Product 9
(9, 1499.99, '2023-01-01', 'Initial price', 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(9, 1399.99, '2023-06-01', 'Discount promotion', 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
-- Price history for Product 10
(10, 9.99, '2023-01-01', 'Initial price', 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(10, 7.99, '2023-07-01', 'Summer sale', 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
Table 3. Insert script (Table Convert Online, 2019), (DBEver, 2022).
-- Insert a record in the Employee table for delete and update
INSERT INTO Employee (EmployeeID, FirstName, LastName, Position, AccessPrivileges, CreatedBy, UpdatedBy, CreatedAt, UpdatedAt)
VALUES (11, 'Thien', 'Hang', 'Manager', 'Admin', 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
select * from Employee where EmployeeID = 11;
-- Delete a record from the "Employee"
DELETE FROM Employee WHERE EmployeeID = 11;
select * from Employee where EmployeeID = 11;
Table 4: Delete a record from any table using its unique identifier (primary key) field (DBEver, 2022).
-- Insert a record in the Employee table for delete and update
INSERT INTO Employee (EmployeeID, FirstName, LastName, Position, AccessPrivileges, CreatedBy, UpdatedBy, CreatedAt, UpdatedAt)
VALUES (11, 'Thien', 'Hang', 'Manager', 'Admin', 'Admin', 'Admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
select * from Employee where EmployeeID = 11;
-- Update a record in the Employee table
UPDATE Employee
SET Position = 'Assistant Manager'
WHERE EmployeeID = 11;
select * from Employee where EmployeeID = 11;
Table 5: Update a record in any table using its unique identifier (primary key) field (DBEver, 2022).
-- Update a record in the Customer table to change the email address:
UPDATE Customer
SET Email = '[email protected]'
WHERE CustomerID = 1;
select * from Customer where customerid = 1;
-- Update the stock quantity of a product in the Product table:
select * from Product where ProductID =1
UPDATE Product
SET Stock = 15
WHERE ProductID = 1;
select * from Product where ProductID =1
-- Delete a record from the Payment table based on the PaymentID:
select * from Payment where PaymentID =1;
DELETE FROM Payment
WHERE PaymentID = 1;
select * from Payment where PaymentID =1;
Table 6: Add 2 to 3 of your own DML scripts to manipulate data in your database (DBEver, 2022).
Reporting scripts
SELECT c.CustomerID, c.FirstName, c.LastName, COUNT(o.OrderID) AS TotalOrders
FROM Customer c
LEFT JOIN Orders o ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerID, c.FirstName, c.LastName;
Table 7: Total number of orders by customer (DBEver, 2022).
SELECT p.ProductID, p.Name, COUNT(od.OrderID) AS TotalOrders
FROM Product p
LEFT JOIN OrderDetails od ON p.ProductID = od.ProductID
GROUP BY p.ProductID, p.Name;
Table 8: Total number of orders by product (DBEver, 2022).
-- Calculate the average price of products:
SELECT AVG(Price) AS AveragePrice
FROM Product;
-- Find the maximum and minimum stock quantities of products:
SELECT MAX(Stock) AS MaximumStock, MIN(Stock) AS MinimumStock
FROM Product;
-- Count the number of customers in the Customer table:
SELECT COUNT(*) AS TotalCustomers
FROM Customer;
Table 9: Provide 2 to 3 aggregate function scripts to show use of AVG, MAX, MIN, COUNT or SUM (DBEver, 2022).
How does this design fulfill the mission and/or goals of your target organization?
This database design fulfills the mission and goals of a small fresh retail organization, which focuses on delivering fresh and high-quality products to customers. Let's explore how this design aligns with the mission and goals of such an industry:
The mission of the fresh retail industry is to provide customers with fresh and high-quality products while ensuring a continuous shopping experience.
The Customer table allows the industry to maintain a database of customer information, enabling personalized interactions and targeted marketing campaigns based on customer preferences.
The Product and Category tables support efficient management and categorization of fresh products, ensuring a diverse range of offerings for customers.
The PriceHistory table helps track and analyze price changes, allowing the industry to set competitive and fair prices for fresh products.
The Delivery Address and Payment tables facilitate smooth order processing, ensuring accurate delivery addresses and secure payment handling, resulting in a continuous shopping experience.
The fresh retail industry aims to optimize freshness, customer satisfaction, and operational efficiency:
The Employee table allows for efficient management of employee information, ensuring the need for staffing numbers and levels.
The Orders and Order Details tables help manage customer orders, track inventory levels, and ensure timely order fulfillment, ensuring freshness and customer satisfaction.
The aggregation functions (e.g., AVG, MAX, MIN, COUNT, SUM) enable the industry to analyze sales data, monitor product demand, and make data-driven decisions to optimize inventory levels, reduce waste, and improve operational efficiency.
The Customer table, along with the use of aggregate functions, allows the industry to identify valuable customer insights such as average order values, most loyal customers, and purchase patterns, enabling targeted marketing strategies and personalized experiences to enhance customer satisfaction.
Totally, this database design aligns with the mission and goals of the fresh retail industry by supporting efficient management of customers, products, orders, and employees. It enables the industry to maintain freshness standards, optimize customer satisfaction, and improve operational efficiency. By leveraging this database design effectively, the fresh retail industry can fulfill its mission of delivering fresh and high-quality products while ensuring a seamless shopping experience for its customers.