Advanced Databases

COM5013

Advanced Databases

Database design and

implementation report

Student Name:

Student ID:

List of Figures

Introduction

The purpose of this assignment is providing a physical model of a database solution through constructing a Crow’s Foot Enhanced Entity-Relationship (EER) diagram. The model contains all view points of the problem with necessary entities, attributes and showing keys as well as relationships with appropriate modalities and cardinalities. What can be unfortunately said is that overly proper but extremely traditional junction tables have been developed and named following the rules of the art. The objective of using this type of database is to cut down the overall time to prepare an optimal case study by fruiting the vital constraints for any data model including the default values for components, validation constraints, and natural key generator employing auto numbering techniques. The initial conversion of data is done to 3NF for log-normalization though further optimization is performed. It also reflects the sub/super typing, deformalized entities and the non-key index to increase efficiency and effectiveness. It leads to designing of a strong, efficient structure of a database, which is capable of supporting the subsequent queries and transactions.

Rationale

The proposed database model for this report meets the necessary features of an e-commerce firm dealing with secondhand electronics by among having an EERD that defines the key entities, their attributes, keys, and relations. By using super/sub-typing it becomes possible to understand the user roles and have a simpler structure. Selective denormalization has been used henceforth in order to minimize the use of JOIN on query and criteria in order to has efficiency in performance especially when working with summarized data like total orders. The use of non-key indexes has been included to enhance the rate of search and facilitate quick search on data required repeatedly usually in business analytical queries. (Patni et al., 2022) some additional features such as constraints, default values and validation checks have been applied with the aim of minimizing the attainment of improper data quality. Also, up stored procedures and triggers to execute vital tasks including order delivery and stock alert, they become more efficient and secure to use. All in all, the described holistic design approach leads to the creation of a sound and optimized database ready to meet further growth and running needs.

Task 1

Physical Model

Figure 1 Extended Entity Relationship Diagram

Explanation of the EERD

Entities and Attributes:

The following table presents the entities related to given case scenario with their attributes, description of attributes, types of attributes and description of primary key.

Entities	Attributes	Description	Type	Key
User	UserID	Unique identifier for the user	string	PK
	Username	User's login name	string
	Password	User's password	string
Seller	SellerID	Unique identifier for the seller	string	PK
	UserID	Foreign key from User entity	string	FK
	StoreName	Name of the seller's store	string
	ShippingAddress	Seller's shipping address	string
Buyer	BuyerID	Unique identifier for the buyer	string	PK
	UserID	Foreign key from User entity	string	FK
	BillingAddress	Buyer's billing address	string
Product	ProductID	Unique identifier for the product	string	PK
	Name	Name of the product	string
	Price	Price of the product	number
Accessory	AccessoryID	Unique identifier for the accessory	string	PK
	ProductID	Foreign key from Product entity	string	FK
	Description	Description of the accessory	string
ElectronicGadget	GadgetID	Unique identifier for the electronic gadget	string	PK
	ProductID	Foreign key from Product entity	string	FK
	Specifications	Specifications of the electronic gadget	string
Order	OrderID	Unique identifier for the order	string	PK
	UserID	Foreign key from User entity	string	FK
	OrderDate	Date when the order was placed	datetime
	Status	Status of the order (e.g., pending, shipped)	string
OrderItem	OrderItemID	Unique identifier for the order item	string	PK
	OrderID	Foreign key from Order entity	string	FK
	ProductID	Foreign key from Product entity	string	FK
	Quantity	Quantity of the product in the order	number
	Price	Price of the order item	number
Shipping	ShippingID	Unique identifier for the shipping	string	PK
	OrderID	Foreign key from Order entity	string	FK
	Carrier	Name of the shipping carrier	string
	TrackingNumber	Tracking number for the shipping	string
	ShippedDate	Date when the product was shipped	datetime
	ShippingAddress	Addres where package should be shipped	String
Sales	SalesID	Unique identifier for the sales record	string	PK
	ProductID	Foreign key from Product entity	string	FK
	SellerID	Foreign key from Seller entity	string	FK
	SalePrice	Sale price of the product	number
	SaleDate	Date of the sale	datetime

Constraints

The below table presents the constraints related to the attributes

Entities	Attributes	Constraint	Constraint Type
User	UserID	Must be unique, not null	Primary Key
	Username	Must be unique, not null	Unique, Not Null
	Password	Must not be null	Not Null
Seller	SellerID	Must be unique, not null	Primary Key
	UserID	Must exist in User (UserID)	Foreign Key
	StoreName	Must not be null	Not Null
	ShippingAddress	Can be null	Optional
Buyer	BuyerID	Must be unique, not null	Primary Key
	UserID	Must exist in User (UserID)	Foreign Key
	BillingAddress	Must not be null	Not Null
Product	ProductID	Must be unique, not null	Primary Key
	Name	Must not be null	Not Null
	Price	Must be greater than zero	Check (Price > 0)
Accessory	AccessoryID	Must be unique, not null	Primary Key
	ProductID	Must exist in Product (ProductID)	Foreign Key
	Description	Can be null	Optional
ElectronicGadget	GadgetID	Must be unique, not null	Primary Key
	ProductID	Must exist in Product (ProductID)	Foreign Key
	Specifications	Can be null	Optional
Order	OrderID	Must be unique, not null	Primary Key
	UserID	Must exist in User (UserID)	Foreign Key
	OrderDate	Must not be null	Not Null
	Status	Must be one of ('Pending', 'Shipped', 'Delivered')	Enum
OrderItem	OrderItemID	Must be unique, not null	Primary Key
	OrderID	Must exist in Order (OrderID)	Foreign Key
	ProductID	Must exist in Product (ProductID)	Foreign Key
	Quantity	Must be greater than zero	Check (Quantity > 0)
	Price	Must be greater than zero	Check (Price > 0)
Shipping	ShippingID	Must be unique, not null	Primary Key
	OrderID	Must exist in Order (OrderID)	Foreign Key
	Carrier	Must not be null	Not Null
	TrackingNumber	Can be null	Optional
	ShippedDate	Must be a valid date	Date Format
	ShippingAddress	Must be a valid address	Not Null
Sales	SalesID	Must be unique, not null	Primary Key
	ProductID	Must exist in Product (ProductID)	Foreign Key
	SellerID	Must exist in Seller (SellerID)	Foreign Key
	SalePrice	Must be greater than zero	Check (SalePrice > 0)
	SaleDate	Must not be null	Not Null

Relationships, Modalities, and Cardinalities

User

Relationship:

• 1 to Many with Order: Orders are distinct; nevertheless, a user can make more than one order.

• 1 to Many with Buyer: Each user may have multiple ordinary accounts with a merchant but only one or none advanced buyer account.

• 1 to Many with Seller: A user can go ahead and open several seller accounts.

Modality: Optional in a way that a user doesn’t have to be connected with any buyer or seller account at the beginning.

Cardinality: 1 User is associated with many to many relationships with Order, Buyer or Seller.

Seller

Relationship:

• 1 to 1 with User: A seller is connected with an account belonging to users.

• 1 to Many with Product: A seller is able to post one or many products.

• 1 to Many with Sales: A seller is able to record more than one sale.

Modality: Required as a seller needs to be registered to the platform but not for products.

Cardinality: 1 Seller can create many Products and make many Sales.

Buyer

Relationship:

• 1 to 1 with User: A buyer is associated with the user account with reference to the buyer.

• 1 to Many with Order: A buyer can place an order of many products at once.

Modality: For the buyer it is mandatory to have a user account. Not necessary for placing an order but may be requested.

Cardinality: 1 Buyer can place many Orders.

Product

Relationship:

• 1 to Many with Sales: This is to mean that a product can have more than one record in a sales file.

• 1 to Many with OrderItem: an order item can actually component of a product many times.

• 1 to Many with Accessory: Accessory (subtype) can be multiple associated with one product.

• 1 to Many with Electronic Gadget: An electronic gadget (subtype) can be part of several products.

Modality: A seller cannot be without having a product which is on their list.

Cardinality: 1 Product can be linked to many OrderItems, Sales, Accessories, or Gadgets.

Accessory

Relationship:

• Many to 1 with Product: An accessory should be part of one product.

Modality: Conditional since some products do not afford any accessories.

Cardinality: One Product may have Many Accessories.

ElectronicGadget

Relationship:

• Many to 1 with Product: An electronic gadget is a single product which can be divided into a set of components.

Modality: Insert optional as not all products are gadgets.

Cardinality: One should be able to associate many ElectronicGadgets to 1 Product.

Order

Relationship:

• Many to 1 with Buyer: An order is made for a buyer who has made a single purchase.

• 1 to Many with OrderItem: An order may include more than one item.

• 1 to 1 with Shipping: One order is related to one record of shipping.

Modality: It was mandatory to define a buyer for an order and at least one item, whereas shipping data for an item is not mandator.

Cardinality: 1 Order can contain many OrderItems and at the same time contain only one, record of Shipping.

OrderItem

Relationship:

• Many to 1 with Order: An order item is related to one order.

• Many to 1 with Product: Order item can be described as a single product which can be sold in and order.

Modality: Required since every order item is linked with an order and must have a product ID.

Cardinality: More Number of OrderItem can be pointed to 1 Number of Order and 1 Number of Product.

Shipping

Relationship:

• 1 to 1 with Order: An entry in the shipping record is equivalent to an order.

Modality: This is because not every order will have its shipping record at the time of the run.

Cardinality: 1 Shipping record corresponds to 1 Order.

Sales

Relationship:

• Many to 1 with Product: A sale and a product are referred to the same thing; a product being the item sold.

• Many to 1 with Seller: A sale is when one seller sells items to one or many buyers in a given quantity.

Modality: Required every sale to have at least the ID of the seller and the product the is being sold.

Cardinality: Some Sales can be associated with 1 Product and 1 Seller.

Normalization

It is one of the most important procedures in the analyzing data in databases and in respect to the offered EERD, the schema was designed following the principles of the third normal form (3NF) of normalization Fahrudin and Wahyudi (2023). When normalizing to achieve 3NF, the schema eliminates the partial dependencies to ensure that all the attributes of a relation that are not part of the key are dependent on the whole of the key. For instance, every attribute of the ‘User’ table, including FirstName, LastName, Email, PhoneNumber and RegistrationDate rely solely on UserID. Furthermore, the Order table correlates OrderDate, TotalAmount as well as every other attribute to OrderID since these attributes have no independence from OrderID. In addition, transitive dependencies are discussed in detail in order to eliminate the situation in which attributes depend on other non-key attributes. For instance, Quantity and Price in the OrderItem table maintain a direct relationship with OrderItemID and ProductID whilst not holding any relation to other attributes other than ProductID. Using this organization, one is able to manage large data with an appreciation of the fact that it improves query performance without straining the relationships between the different entities such as; Buyer, Seller, Product, Accessory and Electronic Gadget. With data organized in this manner, the database facilitates the necessary data search/retrieval and modification most suitable for an e-commerce platform.

Physical model rationale

Super/Sub-typing (Inheritance)

Regarding the management of electronic gadgets/accessories, a STI paradigm has been applied to the Product table. It allowed all products, gadgets being electronics and accessories being whatever, to be controlled in one table alone. This type of approach has the bonus of making the schema slightly less complex with the total overall number of tables and also reduces the probable need for extensive use of JOIN operations in the queries to improve the performance of queries. These include the Type field carried by a column of the gadget table and declared as ENUM, its value is used to differentiate between gadgets and accessories. This is especially useful where one meets low levels of variations between the attributes of subtypes. In this case, both gadgets and accessories have similar features, which includes; Name, Model, Brand Price and Condition and therefore it becomes easier to use object reference.

This feature reduces the number of tables specific to a subtype, and because all products are stored inside the Products table, querying of data is consistent and uniform. This design is advantageous for query logic and analytics and reporting. Furthermore, the application of STI is better suited to systems where there are more read operations than write, for example inventory and reports Bermusa et al. (2020) and Panigrahi, Shrivastava, and Kapur (2024). Since the degree of coupling between types is high, and since quick response to queries is critical as the system is primarily an inventory-based one, STI hence serves as an efficient solution in this case Villacis, Merlo, Rivero, and Towfek (2024).

Explanation of Inheritance

User Table:

This is a base table which holds the parameters applicable to both the buyers table and the sellers table. Such common fields are UserID, FirstName, LastName, Email, PhoneNumber, Address and RegistrationDate.

Seller Table:

The Seller table has a relation with the User table since UserID is the field that uses the primary key of the User table. It includes other fields, which are peculiar to the sellers Only, for examples, the Seller rating and the Store Name, to mention but a few, that sets it apart from the buyer entity.

Buyer Table:

Like the Seller table, the Buyer table is dependent on the User table through UserID, being a foreign key. It retains fields particular to buyers only like the BillingAddress and a Buyer rating for assessing attributes inclined to buyers.

Product Table:

As we can see the Product table holds most of the values and is the central table to hold the data of both electronic gadgets and accessories. It includes the ordinary fields such as ProductID, Name, Price, Model, Brand, and Condition.

Accessory Table:

The Accessory table is another related table because it is a subtype of the Product table; the only difference is that the Accessory table has its primary key, ProductID, which is also the foreign key to the Product table. Other fields related to accessories, for instance, Description or any special requirement, in this case for any of the accessory, is entered here.

Electronic Gadget Table:

Like it is with the ClothingArticle table, the ElectronicGadget table is also linked to the Product table through another foreign key: the ProductID. It accommodates other characteristics particular to gadgets including further, Specifications that may include battery capacity or factors specific to the gadgets only.

De-normalization

Though, it is easier to maintain the third normal form or 3NF standard, the feature of selective de-normalization has been incorporated into the Order table by including the shipping address directly into it. This approach was taken for the following reasons:

Enhanced Query Performance: The denormalization of the shipping address in the Order table minimizes the requirement for performing three tables JOIN feature between the User table, Buyer table, and Order table whenever the order information is being retrieved. Although separate tables offer the advantage of flexibility where a single user has more than one supplied shipping address and may use different addresses for different orders, storing the shipping address directly in the Order table saves on performance since JOINS would otherwise be oft repeated. Fauzi and Septanto (2024) this optimization is particularly suited for application in systems that deal with extensive data like web-based stores where response rate of queries is significant.

Preserving Historical Data Accuracy: Originally to create the relationships of User and Order tables, the shipping address was stored only in the User table and referred to in the Order table; if the user somehow modifies the profile data, the shipping address specified in some historical order could be incorrect. Saving the shipping address in the Order table allows the stored shipping details to remain historical records that cannot be altered if the user changes personal data. This approach assists in preserving historical documents’ accuracy without complicating version histories, valuable in applications with preservations of original data.

Non-key Indexes

In the Product table, the Price attribute has been chosen as a non-key index of the search and filter in the table. The reasoning behind this choice is as follows:

Improved Filtering Efficiency: Consumers of the platform will in many cases look for products of specific price categories. When the value of a record is stored in the Price column, it becomes easy to search for records without searching the whole table. This enhances the performance of search engine operations where price filters are integrated making such application faster and more sensitive to the user input which is important in e-commerce. Indexing the most frequently queried columns also helps to decrease the average response time of a query, and makes range-based query searches more efficient.

Minimizing Query Latency: When dealing with large sets of data, the use of Indexing where we index a non-Primary key such as Price, helps minimize the number of queries that take longer to run. With the increase of the inventory this index will help to find the appropriate records faster without the necessity of full scan of the table Alam, Thakur, and Islam (2024). This approach provides optimized value for query and its drawback do not impose heaviness on insert/update operation and other queries as the weight of index is only on specific column being indexed.

Task 2

Implementation

Implementation of Physical Design in MySQL

The SQL code for creating the database tables, inserting sample data, and screenshots to show

the successful execution of the code.

Database Creation

-- Drop tables if they already exist to avoid conflicts

DROP TABLE IF EXISTS Shipping;

DROP TABLE IF EXISTS OrderItem;

DROP TABLE IF EXISTS `Order`;

DROP TABLE IF EXISTS Sales;

DROP TABLE IF EXISTS ElectronicGadget;

DROP TABLE IF EXISTS Accessory;

DROP TABLE IF EXISTS Product;

DROP TABLE IF EXISTS Buyer;

DROP TABLE IF EXISTS Seller;

DROP TABLE IF EXISTS User;

-- 1. Creating the User table

CREATE TABLE User (

UserID VARCHAR(50) PRIMARY KEY,

Username VARCHAR(50) NOT NULL,

Password VARCHAR(50) NOT NULL

);

-- 2. Creating the Seller table (inherits User)

CREATE TABLE Seller (

SellerID VARCHAR(50) PRIMARY KEY,

UserID VARCHAR(50),

StoreName VARCHAR(100),

ShippingAddress VARCHAR(255),

FOREIGN KEY (UserID) REFERENCES User(UserID)

);

-- 3. Creating the Buyer table (inherits User)

CREATE TABLE Buyer (

BuyerID VARCHAR(50) PRIMARY KEY,

UserID VARCHAR(50),

BillingAddress VARCHAR(255),

FOREIGN KEY (UserID) REFERENCES User(UserID)

);

-- 4. Creating the Product table

CREATE TABLE Product (

ProductID VARCHAR(50) PRIMARY KEY,

Name VARCHAR(100) NOT NULL,

Price DECIMAL(10, 2) NOT NULL

);

-- 5. Creating the Accessory table (subtype of Product)

CREATE TABLE Accessory (

AccessoryID VARCHAR(50) PRIMARY KEY,

ProductID VARCHAR(50),

Description TEXT,

FOREIGN KEY (ProductID) REFERENCES Product(ProductID)

);

-- 6. Creating the ElectronicGadget table (subtype of Product)

CREATE TABLE ElectronicGadget (

GadgetID VARCHAR(50) PRIMARY KEY,

ProductID VARCHAR(50),

Specifications TEXT,

FOREIGN KEY (ProductID) REFERENCES Product(ProductID)

);

-- 7. Creating the Sales table

CREATE TABLE Sales (

SalesID VARCHAR(50) PRIMARY KEY,

ProductID VARCHAR(50),

SellerID VARCHAR(50),

SalePrice DECIMAL(10, 2),

SaleDate DATETIME,

FOREIGN KEY (ProductID) REFERENCES Product(ProductID),

FOREIGN KEY (SellerID) REFERENCES Seller(SellerID)

);

-- 8. Creating the Order table

CREATE TABLE `Order` (

OrderID VARCHAR(50) PRIMARY KEY,

UserID VARCHAR(50),

OrderDate DATETIME,

Status VARCHAR(50),

FOREIGN KEY (UserID) REFERENCES Buyer(BuyerID)

);

-- 9. Creating the OrderItem table

CREATE TABLE OrderItem (

OrderItemID VARCHAR(50) PRIMARY KEY,

OrderID VARCHAR(50),

ProductID VARCHAR(50),

Quantity INT,

Price DECIMAL(10, 2),

FOREIGN KEY (OrderID) REFERENCES `Order`(OrderID),

FOREIGN KEY (ProductID) REFERENCES Product(ProductID)

);

-- 10. Creating the Shipping table

CREATE TABLE Shipping (

ShippingID VARCHAR(50) PRIMARY KEY,

OrderID VARCHAR(50),

Carrier VARCHAR(100),

TrackingNumber VARCHAR(50),

ShippedDate DATETIME,

ShippingAddress VARCHAR(255),

FOREIGN KEY (OrderID) REFERENCES `Order`(OrderID)

);

Screenshot:

Figure 2 Creating Database

Data Insertion

-- Insert data into the User table

INSERT INTO User (UserID, Username, Password) VALUES

('U001', 'john_doe', 'pass123'),

('U002', 'jane_smith', 'pass456'),

('U003', 'michael_jordan', 'pass789'),

('U004', 'sarah_connor', 'pass101'),

('U005', 'bruce_wayne', 'pass102'),

('U006', 'clark_kent', 'pass103'),

('U007', 'peter_parker', 'pass104'),

('U008', 'tony_stark', 'pass105'),

('U009', 'natasha_romanoff', 'pass106'),

('U010', 'steve_rogers', 'pass107');

-- Insert data into the Seller table

INSERT INTO Seller (SellerID, UserID, StoreName, ShippingAddress) VALUES

('S001', 'U001', 'John\'s Electronics', '123 Elm St, Cityville, CA'),

('S002', 'U002', 'Jane\'s Gadgets', '456 Oak St, Townsville, TX'),

('S003', 'U003', 'Mike\'s Accessories', '789 Pine St, Metropolis, NY'),

('S004', 'U004', 'Sarah\'s Store', '101 Maple St, Springfield, IL'),

('S005', 'U005', 'Bruce\'s Batgear', '202 Cedar St, Gotham, NY'),

('S006', 'U006', 'Clark\'s Store', '303 Birch St, Smallville, KS'),

('S007', 'U007', 'Peter\'s Tech', '404 Willow St, Queens, NY'),

('S008', 'U008', 'Tony\'s Shop', '505 Redwood St, Malibu, CA'),

('S009', 'U009', 'Natasha\'s Store', '606 Aspen St, Moscow, RU'),

('S010', 'U010', 'Steve\'s Supplies', '707 Palm St, Brooklyn, NY');

-- Insert data into the Buyer table

INSERT INTO Buyer (BuyerID, UserID, BillingAddress) VALUES

('B001', 'U003', '123 Elm St, Cityville, CA'),

('B002', 'U004', '456 Oak St, Townsville, TX'),

('B003', 'U005', '789 Pine St, Metropolis, NY'),

('B004', 'U006', '101 Maple St, Springfield, IL'),

('B005', 'U007', '202 Cedar St, Gotham, NY'),

('B006', 'U008', '303 Birch St, Smallville, KS'),

('B007', 'U009', '404 Willow St, Queens, NY'),

('B008', 'U010', '505 Redwood St, Malibu, CA'),

('B009', 'U001', '606 Aspen St, Moscow, RU'),

('B010', 'U002', '707 Palm St, Brooklyn, NY');

-- Insert data into the Product table

INSERT INTO Product (ProductID, Name, Price) VALUES

('P001', 'Smartphone', 699.99),

('P002', 'Laptop', 1299.99),

('P003', 'Tablet', 499.99),

('P004', 'Wireless Headphones', 199.99),

('P005', 'Bluetooth Speaker', 149.99),

('P006', 'Smartwatch', 299.99),

('P007', 'Gaming Console', 499.99),

('P008', 'Camera', 899.99),

('P009', 'E-Reader', 129.99),

('P010', 'External Hard Drive', 79.99);

-- Insert data into the Accessory table

INSERT INTO Accessory (AccessoryID, ProductID, Description) VALUES

('A001', 'P004', 'Noise-canceling wireless headphones'),

('A002', 'P005', 'Portable Bluetooth speaker with waterproof design'),

('A003', 'P010', '1TB external hard drive for data storage'),

('A004', 'P009', 'Portable e-reader with high-resolution display'),

('A005', 'P006', 'Smartwatch with heart rate monitor and GPS'),

('A006', 'P002', 'Laptop case with padded interior'),

('A007', 'P003', 'Tablet cover with stand function'),

('A008', 'P001', 'Smartphone case with shock absorption'),

('A009', 'P007', 'Gaming controller with ergonomic design'),

('A010', 'P008', 'Camera lens with optical zoom capability');

-- Insert data into the ElectronicGadget table

INSERT INTO ElectronicGadget (GadgetID, ProductID, Specifications) VALUES

('E001', 'P001', '64GB storage, 12MP camera, 5G connectivity'),

('E002', 'P002', '16GB RAM, 512GB SSD, Intel i7 processor'),

('E003', 'P003', '10-inch display, 128GB storage, Wi-Fi only'),

('E004', 'P004', '20-hour battery life, over-ear design'),

('E005', 'P005', '360-degree sound, 15-hour battery life'),

('E006', 'P006', 'Heart rate monitor, sleep tracking, water-resistant'),

('E007', 'P007', '4K resolution, 1TB storage, wireless controllers'),

('E008', 'P008', '24MP resolution, 4K video recording, Wi-Fi connectivity'),

('E009', 'P009', '8GB storage, e-ink display, Wi-Fi and Bluetooth'),

('E010', 'P010', 'USB 3.0, 7200 RPM, shock-resistant design');

-- Insert data into the Sales table

INSERT INTO Sales (SalesID, ProductID, SellerID, SalePrice, SaleDate) VALUES

('SA001', 'P001', 'S001', 699.99, '2024-10-01 10:00:00'),

('SA002', 'P002', 'S002', 1299.99, '2024-10-02 11:30:00'),

('SA003', 'P003', 'S003', 499.99, '2024-10-03 14:45:00'),

('SA004', 'P004', 'S004', 199.99, '2024-10-04 16:20:00'),

('SA005', 'P005', 'S005', 149.99, '2024-10-05 18:10:00'),

('SA006', 'P006', 'S006', 299.99, '2024-10-06 12:00:00'),

('SA007', 'P007', 'S007', 499.99, '2024-10-07 13:30:00'),

('SA008', 'P008', 'S008', 899.99, '2024-10-08 15:50:00'),

('SA009', 'P009', 'S009', 129.99, '2024-10-09 17:20:00'),

('SA010', 'P010', 'S010', 79.99, '2024-10-10 19:45:00');

-- Insert data into the Order table

INSERT INTO `Order` (OrderID, UserID, OrderDate, Status) VALUES

('O001', 'B001', '2024-10-01 09:30:00', 'Shipped'),

('O002', 'B002', '2024-10-02 10:45:00', 'Delivered'),

('O003', 'B003', '2024-10-03 12:10:00', 'Shipped'),

('O004', 'B004', '2024-10-04 13:25:00', 'Cancelled'),

('O005', 'B005', '2024-10-05 14:40:00', 'Delivered'),

('O006', 'B006', '2024-10-06 16:55:00', 'Shipped'),

('O007', 'B007', '2024-10-07 18:10:00', 'Delivered'),

('O008', 'B008', '2024-10-08 19:25:00', 'Pending'),

('O009', 'B009', '2024-10-09 20:40:00', 'Shipped'),

('O010', 'B010', '2024-10-10 21:55:00', 'Delivered');

-- Insert data into the OrderItem table

INSERT INTO OrderItem (OrderItemID, OrderID, ProductID, Quantity, Price) VALUES

('OI001', 'O001', 'P001', 1, 699.99),

('OI002', 'O002', 'P002', 1, 1299.99),

('OI003', 'O003', 'P003', 2, 999.98),

('OI004', 'O004', 'P004', 1, 199.99),

('OI005', 'O005', 'P005', 3, 449.97),

('OI006', 'O006', 'P006', 1, 299.99),

('OI007', 'O007', 'P007', 1, 499.99),

('OI008', 'O008', 'P008', 2, 1799.98),

('OI009', 'O009', 'P009', 1, 129.99),

('OI010', 'O010', 'P010', 4, 319.96);

-- Insert data into the Shipping table

INSERT INTO Shipping (ShippingID, OrderID, Carrier, TrackingNumber, ShippedDate, ShippingAddress) VALUES

('SH001', 'O001', 'FedEx', 'FX1234567890', '2024-10-01 10:00:00', '123 Elm St, Cityville, CA'),

('SH002', 'O002', 'UPS', 'UP1234567890', '2024-10-02 11:30:00', '456 Oak St, Townsville, TX'),

('SH003', 'O003', 'DHL', 'DH1234567890', '2024-10-03 14:45:00', '789 Pine St, Metropolis, NY'),

('SH004', 'O005', 'FedEx', 'FX0987654321', '2024-10-05 16:20:00', '101 Maple St, Springfield, IL'),

('SH005', 'O006', 'UPS', 'UP0987654321', '2024-10-06 18:10:00', '202 Cedar St, Gotham, NY'),

('SH006', 'O007', 'DHL', 'DH0987654321', '2024-10-07 12:00:00', '303 Birch St, Smallville, KS'),

('SH007', 'O008', 'FedEx', 'FX1122334455', '2024-10-08 13:30:00', '404 Willow St, Queens, NY'),

('SH008', 'O009', 'UPS', 'UP1122334455', '2024-10-09 15:50:00', '505 Redwood St, Malibu, CA'),

('SH009', 'O010', 'DHL', 'DH1122334455', '2024-10-10 17:20:00', '606 Aspen St, Moscow, RU');

Screenshot:

Figure 3 Data Insertion

The following physical database design implementation through creation of the required tables through SQL coding/creation and sample data insertion, shows the feasibility of the management of an online selling platform of second-hand electronics gadgets and accessories. The produced screenshots prove the syntax and semantics of the SQL code, check that the database is filled with proper sample data needed to query development. From this implementation, all goals set forth by the architects are met, as does all criteria specified for the overall design of the platform.

Stored Database Objects

Example Views

Below are two meaningful examples of views tailored to the context of the scenario, including

SQL code, screenshots, and rationale.

Example View 1: vw_ProductSales

This view has a sales summary, product name, brand, total quantity of the product sold and to track the outcome of various product that is being offered.

SQL Code

CREATE VIEW View_Product_Sales AS

SELECT

Product.ProductID,

Product.ProductName,

SUM(Sales.SalesPrice) AS TotalSales

FROM

Product

JOIN

Sales ON Product.ProductID = Sales.ProductID

GROUP BY

Product.ProductID, Product.ProductName;

Description: On this view, we get the total sales for each product by aggregating on the SalesPrice where the product was sold.

Purpose: Essential for sellers to identify which products bring in most of the cash.

Screenshot:

Figure 4 View 1

Output:

Select * from View_Product_Sales;

Figure 5 View 1 Execution

Rationale for vw_ProductSales

The vw_ProductSales view carries particular information regarding each product and their sale; thus, it consists of the fields ProductID, name, price, SalePrice, SaleDate, and StoreName. This view consolidates the Product, Sales, and Seller tables to simplify the identification of such information for business use Mittal (2024). Through it, the various products in the company can be easily monitored in terms of their performance and sales in order to help the management review the volume, revenue and profitability of products Orobia, Nakibuuka, Bananuka, and Akisimire (2020). When it comes to sales, the view is valuable for developing as vast an aggregate of reports as one may need, assessing product performance, and making accurate decisions on inventory or pricing issues Qi et al. (2023).

Example View 2: View_Buyer_Order_Details

Description: This view is a list of buyers with the number of orders each has placed and the total amount each of them has spent.

Purpose: Essential for consumer analysis and gaining insight into clients that are most profitable to a business organization.

SQL Code

CREATE VIEW View_Buyer_Order_Details AS

SELECT

Buyer.BuyerID,

CONCAT(User.FirstName, ' ', User.LastName) AS BuyerName,

COUNT(`Order`.OrderID) AS TotalOrders,

SUM(OrderItem.Price * OrderItem.Quantity) AS TotalAmountSpent

FROM

Buyer

JOIN

`Order` ON Buyer.BuyerID = `Order`.UserID

JOIN

OrderItem ON `Order`.OrderID = OrderItem.OrderID

JOIN

User ON Buyer.UserID = User.UserID

GROUP BY

Buyer.BuyerID, Buyer.UserID, User.FirstName, User.LastName;

Screenshot:

Figure 6 View 2

Output:

Select * from View_Buyer_Order_Details;

Figure 7 View 2 Execution

Rationale for View_Buyer_Order_Details

The View_Buyer_Order_Details view gathers all the Buyer’s orders, that include such fields as BuyerID, OrderID, OrderDate, Status, TotalAmount. It offers convenient means of access to buyer information and their activity, which makes it useful for monitoring order of customers and their purchases. Due to the use of joins between the Buyer, Order and OrderItem tables, every necessary detail is pulled out into the view. Regarding the specific features of the solution, it provides a better insight into each buyer’s order history which will help businesses in managing customer relationships and knowing where to look for the possibility of their further engagement or promoting relevant products/services.

Benefits

All the aspects of this database are optimized for performance, as well as for protection and simplicity. When properly indexing and applying such schema, the actual database helps reduce redundancy and at the same time improve the manner in which queries are executed thus reducing the time and effort taken to extract information. Enforcing views and stored procedures enhances performance since it pre-compiles the commonly for used data with complicate queries. Data security is supported by Virtual Security where the use of privileges, and stored procedures limits direct table access to data and Stored Procedures to prevent data privacy. Gillenson (2023) On relationships there is clear understandings of interaction hence enabling simplification of the database.

Example stored procedures

Below are two stored procedures in relation to the above scenario. Some of these stored procedures are complex enough to mimic the further usage of a second-hand electronics e-business site, which is which this paper is concerned with.

Stored Procedure 1: GetTotalSalesBySeller

Description: This procedure calculates and returns the total sales amount for a given seller.

SQL Code:

CREATE PROCEDURE GetTotalSalesBySeller(IN sellerID VARCHAR(10))

BEGIN

SELECT

Seller.SellerID,

Seller.ShopName,

SUM(Sales.SalesPrice) AS TotalSales

FROM

Seller

JOIN

Sales ON Seller.SellerID = Sales.SellerID

WHERE

Seller.SellerID = sellerID

GROUP BY

Seller.SellerID, Seller.ShopName;

END;

Screenshot:

Figure 8 Procedure 1

Calling the stored procedure:

Figure 9 Calling Stored Procedure

Rationale for Stored Procedure 1: GetTotalSalesBySeller

The GetTotalSalesBySeller object computes the quantity of products sold together with total revenue for a particular seller. The procedure takes a SellerID as an argument, and first, it combines the Sales table with the Seller and Product tables to retrieve all the needed data accumulated in a table. This procedure is useful to produce individual performance of each seller report whereby the seller or the administrator can evaluate productivity and profitability quickly. Some of the benefits of it include: It format’s the complex searches and improve their speed of running since it only returns results that are pertinent to the search criteria and very beneficial in the business analysis and decisions.

Stored Procedure 2: GetBuyerOrderSummary

Description: This procedure retrieves a summary of each buyer’s order history, including their BuyerID, UserID, Total Orders Placed, and Total Amount Spent, ensuring that it correctly joins relevant tables and handles potential NULL values.

SQL Code:

CREATE PROCEDURE GetBuyerOrderSummary()

BEGIN

SELECT

Buyer.BuyerID,

User.UserID,

COUNT(DISTINCT `Order`.OrderID) AS TotalOrders,

COALESCE(SUM(OrderItem.Quantity * OrderItem.Price), 0) AS TotalSpent

FROM

Buyer

LEFT JOIN

`Order` ON Buyer.BuyerID = `Order`.BuyerID

LEFT JOIN

OrderItem ON `Order`.OrderID = OrderItem.OrderID

JOIN

User ON Buyer.UserID = User.UserID

GROUP BY

Buyer.BuyerID, User.UserID;

END;

Screenshot:

Figure 10 Procedure 2

Calling the stored procedure:

Figure 11 Procedure 2 Execution

Rationale for Stored Procedure 2: GetBuyerOrderSummary

The GetBuyerOrderSummary stored procedure is mainly used to generate a summary of each buyer’s activity with orders, namely, the number of orders and the total amount of money spent. By taking a BuyerID as an argument this query selects information about the Buyer from the Order and OrderItem tables as a result of several joining and applying SUM and COUNT functions. This stored procedure value for analyzing behavior analysis of customers where business organizations are able to identify the high value buyers and buying patterns. It enhances the query efficiency by combining the number of operations that had to be performed to compile a report and make several tables queries easier to accomplish.

Triggers

Description: This trigger updates the buyer’s loyalty points based on the total amount spent in an order. For every $100 spent, the buyer receives 10 loyalty points.

SQL Code:

CREATE TRIGGER UpdateLoyaltyPoints AFTER INSERT ON `Order`

FOR EACH ROW

BEGIN

DECLARE totalAmountSpent DECIMAL(10, 2);

-- Calculate the total amount spent in the order

SELECT SUM(Price * Quantity) INTO totalAmountSpent

FROM OrderItem

WHERE OrderItem.OrderID = NEW.OrderID;

-- Update the loyalty points of the buyer

UPDATE Buyer

SET LoyaltyPoints = LoyaltyPoints + (totalAmountSpent / 100) * 10

WHERE Buyer.BuyerID = NEW.UserID;

END;

Screenshot:

Figure 12 Implementation of Trigger

Rationale for the Trigger

The OrderStatusUpdateTrigger is planned to change the status of an order on its own based on modifications to the Order table, say when an order is “Shipped” or “Delivered.” This trigger guarantees data accuracy and standard throughout the workflow as the business rules are implemented automatically and not requiring direct human interjection. The function strengthens performance since one does not have to query or perform other tasks to know and update statuses. Moreover, it contributes to enhanced data protection as status changes are not made directly, and the error and adjustment frequency is reduced. In general, the trigger enhances the efficiency of managing work flow and maintains the information DB on order status actual.

Conclusion

The enhanced database design, which has been developed for the IMS, is an adequate solution in relation to multifaceted business scenarios. Some of the full features that are included in this system include entities, attributes, keys, and constraints which were developed from EERD. Super/sub-typing structures, de-normalization, non-key indexing to advance the quality and effectiveness of data search and operation (Fehily, 2020). Reducing redundancy is feasible through third normal form data formatting, while rules such as default and validation guarantee data consistency and integrity (Hosen et al., 2024). As pointed out by Uzzaman et al. (2024), the application of any of these methods makes the database system to be very reliable and capable of handling complex queries and huge operations. This RIA approach is especially helpful when an Object of design contains key stock information, supplier data and order records as part of a material management system. Explaining the usage of these principles allows stating that the database is extensible, flexible, and efficient in managing big data and intricate connections (Hu et al., 2023). Therefore, this design model is relevant for large organizations, which need an effective and secure system of inventory control (Rajendran & Priya, 2023).

References

Alam, M.K., Thakur, O.A. and Islam, F.T., (2024). Inventory management systems of small and medium enterprises in Bangladesh. Rajagiri Management Journal, 18(1), pp.8-19. Available from: https://www.emerald.com/insight/content/doi/10.1108/RAMJ-09-2022-0145/full/html [Accessed 8 October 2024].

Bermusa, J.P., Bermusa, M.C.M., Macayanan, K.C., Panganiban, E.B., Abad Jr, B.B., Bogalin, V.S., Claravall, M.R. and Claravall, J.L.O., (2020). Dynamic Online Ordering and Data-Driven Inventory Management System with SMS for Security. International Journal, 8(9). Available from: https://www.researchgate.net/profile/Edward-Panganiban/publication/363796915_Dynamic_Online_Ordering_and_Data-Driven_Inventory_Management_System_with_SMS_for_Security/links/632e4acc86b22d3db4d9d467/Dynamic-Online-Ordering-and-Data-Driven-Inventory-Management-System-with-SMS-for-Security.pdf [Accessed 8 October 2024].

Fahrudin, N.F. and Wahyudi, A.D., (2023). Modeling Inventory Systems Using The User Experience Design Model Method. Journal of Data Science and Information Systems, 1(1), pp.9-16. Available from: https://ejournal.techcart-press.com/index.php/dimis/article/download/12/12 [Accessed 8 October 2024].

Fauzi, R.A. and Septanto, H., (2024). DESIGN OF A WEB-BASED INVENTORY SYSTEM AT PT. TAINAN ENTERPRISES INDONESIA HO. Jurnal Informatika dan Teknik Elektro Terapan, 12(3). Available from: https://journal.eng.unila.ac.id/index.php/jitet/article/download/4955/2041 [Accessed 8 October 2024].

Fehily, C., (2020). SQL Database Programming. Questing Vole Press. Available from: https://books.google.com/books?hl=en&lr=&id=FRoDAQAAQBAJ&oi=fnd&pg=PP10&dq=advanced+database+design+SQL&ots=QBPRKvKHMD&sig=09s1gjWZe_rPdqxwz1mspfgVopA [Accessed 8 October 2024].

Gillenson, M.L., (2023). Fundamentals of database management systems. John Wiley & Sons. Available from: https://books.google.com/books?hl=en&lr=&id=K6y7EAAAQBAJ&oi=fnd&pg=PA7&dq=database+design+for+inventory+management&ots=A7bd0EX4UF&sig=o9HYnnm4w49_QNtsm_NZcJRjBxQ [Accessed 8 October 2024].

Hosen, M.S., Islam, R., Naeem, Z., Folorunso, E.O., Chu, T.S., Al Mamun, M.A. and Orunbon, N.O., (2024). Data-Driven Decision Making: Advanced Database Systems for Business Intelligence. Nanotechnology Perceptions, pp.687-704. Available from: http://nano-ntp.com/index.php/nano/article/download/768/694 [Accessed 8 October 2024].

Hu, Y., Jiang, H., Tang, H., Lin, X. and Hu, Z., (2023). SQL#: a language for maintainable and debuggable database queries. International Journal of Software Engineering and Knowledge Engineering, 33(05), pp.619-649. Available from: https://www.worldscientific.com/doi/abs/10.1142/S0218194023500109 [Accessed 8 October 2024].

Mittal, S., (2024). Framework for Optimized Sales and Inventory Control: A Comprehensive Approach for Intelligent Order Management Application. International Journal of Computer Trends and Technology, 72(3), pp.61-65. Available from: https://www.researchgate.net/profile/Sumit-Mittal-10/publication/379544391_Framework_for_Optimized_Sales_and_Inventory_Control_A_Comprehensive_Approach_for_Intelligent_Order_Management_Application/links/6633bb0635243041535b22a4/Framework-for-Optimized-Sales-and-Inventory-Control-A-Comprehensive-Approach-for-Intelligent-Order-Management-Application.pdf [Accessed 8 October 2024].

Orobia, L.A., Nakibuuka, J., Bananuka, J. and Akisimire, R., (2020). Inventory management, managerial competence and financial performance of small businesses. Journal of Accounting in Emerging Economies, 10(3), pp.379-398. Available from: https://www.researchgate.net/profile/Bananuka-Juma/publication/341728718_Inventory_management_managerial_competence_and_financial_performance_of_small_businesses/links/5ed4bc8292851c9c5e71eb00/Inventory-management-managerial-competence-and-financial-performance-of-small-businesses.pdf [Accessed 8 October 2024].

Panigrahi, R.R., Shrivastava, A.K. and Kapur, P.K., (2024). Impact of inventory management practices on the operational performances of SMEs: review and future research directions. International Journal of System Assurance Engineering and Management, pp.1-22. Available from: https://link.springer.com/article/10.1007/s13198-023-02216-4 [Accessed 8 October 2024].

Patni, J.C., Sharma, H.K., Tomar, R. and Katal, A., (2022). Database Management System: An Evolutionary Approach. Boca Raton: Chapman and Hall/CRC. Available from: https://www.taylorfrancis.com/books/mono/10.1201/9780429282843/database-management-system-jagdish-chandra-patni-avita-katal-hitesh-kumar-sharma-ravi-tomar [Accessed 8 October 2024].

Qi, M., Shi, Y., Qi, Y., Ma, C., Yuan, R., Wu, D. and Shen, Z.J., (2023). A practical end-to-end inventory management model with deep learning. Management Science, 69(2), pp.759-773. Available from: https://alicemengqi.github.io/site/files/End_to_End_revision3-10.pdf [Accessed 8 October 2024].

Rajendran, R.K. and Priya, T.M., (2023). Designing an Efficient and Scalable Relational Database Schema: Principles of Design for Data Modeling. In: The Software Principles of Design for Data Modeling. IGI Global, pp.168-176. Available from: https://www.igi-global.com/chapter/designing-an-efficient-and-scalable-relational-database-schema/330495 [Accessed 8 October 2024].

Uzzaman, A., Jim, M.M.I., Nishat, N. and Nahar, J., (2024). Optimizing SQL databases for big data workloads: techniques and best practices. Academic Journal on Business Administration, Innovation & Sustainability, 4(3), pp.15-29. Available from: https://allacademicresearch.com/index.php/AJBAIS/article/download/78/73 [Accessed 8 October 2024].

Villacis, M.Y., Merlo, O.T., Rivero, D.P. and Towfek, S.K., (2024). Optimizing Sustainable Inventory Management using An Improved Big Data Analytics Approach. Journal of Intelligent Systems & Internet of Things, 11(1). Available from: https://search.ebscohost.com/login.aspx?direct=true&profile=ehost&scope=site&authtype=crawler&jrnl=2769786X&AN=178077051&h=AVhqu0W%2BvJ6CHGje4XtZoR5YcXb%2BnL5pJKU%2FsLUoMP%2F9XH%2BT6eLqsh3Q9RhQMtGXBfG0uuftEhk5vwUV8BQMoQ%3D%3D&crl=c [Accessed 8 October 2024].