Description

Description

see

College of Computing and Informatics

Project
Deadline: Tuesday 04/22/2025@ 23:59
[Total Mark for this Project is 14]
Student Details:
Name:
Name:
Name:
Name:

ID:
ID:
ID:
ID:

Instructions:

• You must submit two separate copies (one Word file and one PDF file) using the
Assignment Template on Blackboard via the allocated folder. These files must not be in
compressed format.

• It is your responsibility to check and make sure that you have uploaded both the correct files.
• Zero mark will be given if you try to bypass the SafeAssign (e.g., misspell words, remove spaces
between words, hide characters, use different character sets, convert text into image or languages
other than English or any kind of manipulation).

• Email submission will not be accepted.
• You are advised to make your work clear and well-presented. This includes filling your information
on the cover page.

• You must use this template, failing which will result in zero mark.
• You MUST show all your work, and text must not be converted into an image, unless specified
otherwise by the question.

• Late submission will result in ZERO mark.
• The work should be your own, copying from students or other resources will result in ZERO mark.
Restricted – ‫مقيد‬

Project Instructions
• You can work on this project as a group (minimum 3 and maximum 4 students).
Only the group leader must submit the project with all group member names.

• This project worth 14 marks and will be distributed as in the following:
a) Identify the entity types, attributes, keys.
b) Identify relationships and cardinalities.
c) Draw the ERD.
d) Schemas before Normalization.
e) Schemas after Normalization.
f) Create the tables.
g) Populate your tables with at least 5 rows.
h) Execute the requested sample queries.

(2 marks)
(2 marks)
(2 marks)
(1.5 marks)
(1.5 marks)
(1.5 marks)
(1.5 marks)
(2 marks)

• Each group must submit one report about their chosen project via the Blackboard
(Email submission will not be accepted which will be awarded ZERO marks)

• Screenshots for answering SQL questions (f, g, and h).
• You are advised to make your work clear and well presented; marks may be reduced
for poor presentation. This includes filling your information on the cover page.

• You MUST show all your work, and text must not be converted into an image, unless
specified otherwise by the question.
A) Late submission will result in ZERO marks being awarded.
B) The work should be your own, copying from students or other resources will
result in ZERO marks.

Restricted – ‫مقيد‬

Learning
Outcome(s):

LO 4

Design a
database starting
from the
conceptual
design to the
implementation
of database
schemas.

LO 3

Create EntityRelationship
model, Relational
model, and write
SQL queries.

E-Commerce Inventory and Order Management System
Background: An emerging online retail company specializing in artisanal products
wants to develop a database system to manage its inventory, customer orders, and
shipping operations. The system should facilitate real-time inventory updates, order
processing, and customer management to enhance operational efficiency and customer
satisfaction.
Project Objective: Design and implement a database system for the e-commerce
platform that supports inventory tracking, order processing, and customer relationship
management. The system should be scalable, reliable, and secure, ensuring smooth
operations as the business grows.
Requirements:
1. Data Storage:
o Product information: Includes product ID, name, description, price, and
stock level.
o Customer information: Includes customer ID, name, contact details, and
order history.
o Orders: Details about customer orders, including order ID, product(s)
ordered, quantities, prices, and status.
o Shipping: Information on shipping logistics, such as tracking numbers,
shipping status, and delivery dates.

2. Project Deliverables:
o An Entity-Relationship (ER) diagram representing the database structure.
o A fully normalized database schema.
o Implementation of the database in a suitable DBMS.

By implementing a robust database system for the E-Commerce Inventory and Order,
the management can efficiently handle the operations from managing product inventory
to processing customer orders and handling shipping logistics.

Restricted – ‫مقيد‬

E-Commerce Inventory and Order Management System
a) Fill the table below with all the Entities based on the given requirements:

Restricted – ‫مقيد‬

Entity
Type

Attributes of the Entity

Key

Product

ProductID, Name, Description, Price,
StockLevel

ProductID

Customer

CustomerID, Name, ContactDetails

CustomerID

Order

OrderID, CustomerID, OrderDate, Status

OrderID

OrderDetail

OrderDetailID, OrderID, ProductID,
Quantity, Price

OrderDetailID

Shipping

ShippingID, OrderID, TrackingNumber,
ShippingStatus, DeliveryDate

ShippingID

b) Fill the table below with all the relationship types based on the given requirements.

Relationship
Type

Entity types
participating in
the relationship
type

Places

Customer to Order

One-to-Many
(1:N)
One-to-Many
(1:N)
Many-to-One
(N:1)
One-to-One
(1:1)

Relationship
attribute (if any)
OrderDate

References

Order to
OrderDetail
OrderDetail to
Product

Tracks

Order to Shipping

Updates

OrderDetail to
Product

Many-to-One
(N:1)

StockLevel
(decreases when
order is placed)

Has

Customer to Order

One-to-Many
(1:N)

None

Contains

Restricted – ‫مقيد‬

Cardinality

Quantity, Price
None
None

c) Draw the ER Diagram

Restricted – ‫مقيد‬

d) Schemas before the normalization
1. Unnormalized Product Table
CREATE TABLE UnnormalizedProduct (
ProductID INT PRIMARY KEY,
Name VARCHAR(255),
Description TEXT,
Price DECIMAL(10, 2),
StockLevel INT,
CustomerID INT,
CustomerName VARCHAR(255),
OrderID INT,
OrderDate DATETIME,
Quantity INT
);
2. Unnormalized Customer Table
CREATE TABLE UnnormalizedCustomer (
CustomerID INT PRIMARY KEY,
Name VARCHAR(255),
Email VARCHAR(255),
Phone VARCHAR(15),
Address TEXT,
OrderID INT,
ProductID INT,
ProductName VARCHAR(255),
Quantity INT,
TotalAmount DECIMAL(10, 2)
);
3. Unnormalized Order Table
CREATE TABLE UnnormalizedOrder (
OrderID INT PRIMARY KEY,

Restricted – ‫مقيد‬

CustomerID INT,
CustomerName VARCHAR(255),
OrderDate DATETIME,
ProductID INT,
ProductName VARCHAR(255),
Quantity INT,
Price DECIMAL(10, 2),
TotalAmount DECIMAL(10, 2),
ShippingID INT,
TrackingNumber VARCHAR(255),
ShippingDate DATETIME,
DeliveryDate DATETIME,
Status VARCHAR(50)
);

e) Schemas after the normalization
Product Table
CREATE TABLE Product (
ProductID INT PRIMARY KEY,
Name VARCHAR(255) NOT NULL,
Description TEXT,
Price DECIMAL(10, 2) NOT NULL,
StockLevel INT NOT NULL
);

Restricted – ‫مقيد‬

Customer Table
CREATE TABLE Customer (
CustomerID INT PRIMARY KEY,
Name VARCHAR(255) NOT NULL,
Email VARCHAR(255) UNIQUE NOT NULL,
Phone VARCHAR(15),
Address TEXT
);

Order Table
CREATE TABLE Order (
OrderID INT PRIMARY KEY,
CustomerID INT,
OrderDate DATETIME NOT NULL,
TotalAmount DECIMAL(10, 2) NOT NULL,
Status VARCHAR(50) NOT NULL,
FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)
);

OrderItem Table
CREATE TABLE OrderItem (
OrderItemID INT PRIMARY KEY,
OrderID INT,
ProductID INT,
Quantity INT NOT NULL,
Price DECIMAL(10, 2) NOT NULL,

Restricted – ‫مقيد‬

FOREIGN KEY (OrderID) REFERENCES Order(OrderID),
FOREIGN KEY (ProductID) REFERENCES Product(ProductID)
);
Shipping Table
CREATE TABLE Shipping (
ShippingID INT PRIMARY KEY,
OrderID INT,
TrackingNumber VARCHAR(255) UNIQUE NOT NULL,
ShippingDate DATETIME,
DeliveryDate DATETIME,
Status VARCHAR(50) NOT NULL,
FOREIGN KEY (OrderID) REFERENCES Order(OrderID)
);
f) Create the normalized tables, write the necessary SQL statements to create the
tables.
— Create Product Table
CREATE TABLE Product (
ProductID INT PRIMARY KEY,
Name VARCHAR(255) NOT NULL,
Description TEXT,
Price DECIMAL(10, 2) NOT NULL,
StockLevel INT NOT NULL
);

— Create Customer Table
CREATE TABLE Customer (
CustomerID INT PRIMARY KEY,

Restricted – ‫مقيد‬

Name VARCHAR(255) NOT NULL,
Email VARCHAR(255) UNIQUE NOT NULL,
Phone VARCHAR(15),
Address TEXT
);

— Create Order Table
CREATE TABLE Order (
OrderID INT PRIMARY KEY,
CustomerID INT,
OrderDate DATETIME NOT NULL,
TotalAmount DECIMAL(10, 2) NOT NULL,
Status VARCHAR(50) NOT NULL,
FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)
);

— Create OrderItem Table
CREATE TABLE OrderItem (
OrderItemID INT PRIMARY KEY,
OrderID INT,
ProductID INT,
Quantity INT NOT NULL,
Price DECIMAL(10, 2) NOT NULL,
FOREIGN KEY (OrderID) REFERENCES Order(OrderID),
FOREIGN KEY (ProductID) REFERENCES Product(ProductID)
);

Restricted – ‫مقيد‬

— Create Shipping Table
CREATE TABLE Shipping (
ShippingID INT PRIMARY KEY,
OrderID INT,
TrackingNumber VARCHAR(255) UNIQUE NOT NULL,
ShippingDate DATETIME,
DeliveryDate DATETIME,
Status VARCHAR(50) NOT NULL,
FOREIGN KEY (OrderID) REFERENCES Order(OrderID)
);
g) Insert at least five rows into each table.
— Insert into Product Table
INSERT INTO Product (ProductID, Name, Description, Price, StockLevel)
VALUES
(1, ‘Handmade Soap’, ‘Organic lavender soap’, 5.99, 100),
(2, ‘Wooden Bowl’, ‘Hand-carved wooden bowl’, 15.99, 50),
(3, ‘Wool Scarf’, ‘Soft wool scarf in red’, 25.99, 75),
(4, ‘Ceramic Mug’, ‘Hand-painted ceramic mug’, 12.99, 60),
(5, ‘Leather Journal’, ‘Hand-stitched leather journal’, 29.99, 40);

— Insert into Customer Table
INSERT INTO Customer (CustomerID, Name, Email, Phone, Address)
VALUES
(1, ‘John Buck’, ‘[email protected]’, ‘123-456-7890’, ‘123 Main St’),
(2, ‘Harry Brown’, ‘[email protected]’, ‘234-567-8901’, ‘456 Elm St’),
(3, ‘Alice Johnson’, ‘[email protected]’, ‘345-678-9012’, ‘789 Oak St’),
(4, ‘Bob Brown’, ‘[email protected]’, ‘456-789-0123’, ‘321 Pine St’),

Restricted – ‫مقيد‬

(5, ‘Charlie Davis’, ‘[email protected]’, ‘567-890-1234’, ‘654 Maple St’);

— Insert into Order Table
INSERT INTO Order (OrderID, CustomerID, OrderDate, TotalAmount, Status)
VALUES
(1, 1, ‘2023-10-01 10:00:00’, 11.98, ‘Pending’),
(2, 2, ‘2023-10-02 11:00:00’, 41.98, ‘Shipped’),
(3, 3, ‘2023-10-03 12:00:00’, 25.99, ‘Delivered’),
(4, 4, ‘2023-10-04 13:00:00’, 29.99, ‘Pending’),
(5, 5, ‘2023-10-05 14:00:00’, 15.99, ‘Shipped’);

— Insert into OrderItem Table
INSERT INTO OrderItem (OrderItemID, OrderID, ProductID, Quantity, Price)
VALUES
(1, 1, 1, 2, 5.99),
(2, 2, 3, 1, 25.99),
(3, 2, 4, 1, 12.99),
(4, 3, 5, 1, 29.99),
(5, 4, 2, 1, 15.99);

— Insert into Shipping Table
INSERT INTO Shipping (ShippingID, OrderID, TrackingNumber, ShippingDate,
DeliveryDate, Status)
VALUES
(1, 2, ‘ABC123’, ‘2023-10-02 12:00:00’, ‘2023-10-07 12:00:00’, ‘Delivered’),
(2, 5, ‘DEF456’, ‘2023-10-05 15:00:00’, ‘2023-10-10 15:00:00’, ‘In Transit’),
(3, 3, ‘GHI789’, ‘2023-10-03 13:00:00’, ‘2023-10-08 13:00:00’, ‘Delivered’),

Restricted – ‫مقيد‬

(4, 4, ‘JKL012’, ‘2023-10-04 14:00:00’, ‘2023-10-09 14:00:00’, ‘In Transit’),
(5, 1, ‘MNO345’, ‘2023-10-01 11:00:00’, ‘2023-10-06 11:00:00’, ‘Pending’);
h) Write SQL queries to find the following:
1. List all products currently out of stock to check for products that need
restocking, helping manage inventory efficiently.
SELECT ProductID, Name, Description, Price
FROM Product
WHERE StockLevel = 0;
2. Find total sales per product to help analyze which products are generating
the most revenue, aiding in strategic business decisions.
SELECT
p.ProductID,
p.Name,
SUM(oi.Quantity * oi.Price) AS TotalSales
FROM
Product p
JOIN
OrderItem oi ON p.ProductID = oi.ProductID
GROUP BY
p.ProductID, p.Name
ORDER BY
TotalSales DESC;
3. Identify orders that are delayed to proactively address delays and
communicate with affected customers.
SELECT
o.OrderID,
o.CustomerID,
o.OrderDate,

Restricted – ‫مقيد‬

s.DeliveryDate,
s.Status
FROM
Order o
JOIN
Shipping s ON o.OrderID = s.OrderID
WHERE
s.DeliveryDate 3;
5. Calculate Inventory Value to provide the total value of all items currently
in stock, this is crucial for financial reporting and inventory management.
SELECT
SUM(StockLevel * Price) AS TotalInventoryValue

Restricted – ‫مقيد‬

FROM
Product;
6. Show Detailed Information for all orders placed today to provide a
snapshot of the day’s business activities.
SELECT
o.OrderID,
o.CustomerID,
c.Name AS CustomerName,
o.OrderDate,
o.TotalAmount,
o.Status,
s.TrackingNumber,
s.ShippingDate,
s.DeliveryDate
FROM
Order o
JOIN
Customer c ON o.CustomerID = c.CustomerID
LEFT JOIN
Shipping s ON o.OrderID = s.OrderID
WHERE
DATE(o.OrderDate) = CURDATE();

Restricted – ‫مقيد‬

Purchase answer to see full
attachment

Share This Post

Email
WhatsApp
Facebook
Twitter
LinkedIn
Pinterest
Reddit

Order a Similar Paper and get 15% Discount on your First Order

Related Questions

Description

Description – I want original text, no plagiarism. – You can find the instructions in the file. Please read it carefully. – APA Style Thanks – Textbook: Aguinis, H. (2023). Performance management (5th ed.). Chicago Business Press. ISBN: 978-1-948426-48-0 Textbook: Aguinis, H. (2023). Performance management (5th ed.). Chicago Business Press.

Description

Description Guidelines for the Presentation (part C): There must be 10 slides in the presentation. The slides should have a clear background design, readable font size and style with appropriate color. The power-point presentation must answer all the above parts. Make sure to include the cover page in the first

Description

Description see Grader – Instructions Word 2022 Project Word_1G_Sports_Photography Project Description: In the following project, you will edit a handout that describes sports photography services offered by Light Magic Studios. Steps to Perform: Points Possible Step Instructions 1 Open the Word document Student_Word_1G_Sports_Photography_AS.docx downloaded with this project. 0 2 Type

Description

Description Hi, i already answered the project but i want you to revise, modify and check if its meets the requirements + to provide Data Flow Diagram) for the system and design the database of the information system. Using AI is not acceptable. College of Computing and Informatics CS352 Project

Description

Description Reply to discussion (Module 12: Developing Employees through Performance Management) Q – Please read the discussion Attached and prepare a Reply to this discussion post with comments that further and advance the discussion topic. The reply needs to be substantial and constructive in nature. it should add to the

Description

Description Reply to discussion (Module 12: Developing Employees through Performance Management) Q – Please read the discussion Attached and prepare a Reply to this discussion post with comments that further and advance the discussion topic. The reply needs to be substantial and constructive in nature. it should add to the

Description

Description Form No 4- Internship Report Cover Page Student`s Name: Student`s ID: Training Organization: Trainee Department: Field Instructor Name: Field Instructor Signature: Course Title: MGT 430 CRN Internship Start Date: Internship End Date: Academic Year/Semester: For Instructor’s Use only Instructor’s Name: Total Training Hours /280 Students’ Grade:Marks Obtained/30 Level of

Description

Description SEE College of Health Sciences Department of Public Health ASSIGNMENT COVER SHEET Course name: Applied Biostatistics Course number: PHC321 CRN: 20627 Paper Assignment-1 Answer the following questions in a Word document using the provided datasheet (you may use SPSS or MS Excel for your analysis). The datasheet contains national

Description

Description Can you please write full report about this topic. This report should focus on this subjects: 1- Introduction Fast food is a quickly prepared and served food that is often high in calories, fat, and salt. However, these foods can lead to serious health problems, including hypertension. 2- Hypertension

Description

Description Define the term of global warming and present arguments for and against the proposition that global warming has occurred during the past century and what environmental outcomes have been attributed to global warming? Instructions for Completing the Discussion Questions: Please post your original response by Tuesday 25/2/2025 at 11:59

Description

Description The Assignment must be submitted on Blackboard (WORD format only) via allocated folder. • Assignments submitted through email will not be accepted. • Students are advised to make their work clear and well presented, marks may be reduced for poor presentation. This includes filling your information on the cover

Description

Description I want presentation p.p Saudi Electronic University College of Administrative and Financial Sciences E-commerce Department Student Name: 1.Shekah Saad Alqahtani 2.Dana Sattam Alharbi 3.Abrar Almutairi 4.Randa Kayid Alotaibi 5.Ghala Bashmil Student ID: -230027645 -230044487 -230048240 -230032061 -230049707 Course Title: E-commerce Course Code: ECOM101 Academic Year/ Semester: 2024/2025 _Second semester

Description

Description first, plagiarism should be avoided sconed , the sources used must be accredited. You must follow all the instructions in the file AVOID USE artificial intelligence the most important dont use chatgpt and sources is very important answer dont be very short

Description

Description I don’t want a solution copied from another student’s paper. ‫المملكة العربية السعودية‬ ‫وزارة التعليم‬ ‫الجامعة السعودية اإللكترونية‬ Kingdom of Saudi Arabia Ministry of Education Saudi Electronic University College of Administrative and Financial Sciences Assignment 3 Decision Making and Problem Solving (MGT 312) Due Date: End of week 12,

Description

Description CAREFULLY • The Assignment must be submitted on Blackboard (WORD format only) via allocated folder. • Assignments submitted through email will not be accepted. • Students are advised to make their work clear and well presented, marks may be reduced for poor presentation. This includes filling your information on

Description

Description Accounting Coop training report3000words include points below : 1. Process supplier payments to ensure accurate and timely payment 2. Review incoming invoices for proper coding and approvals 3. Record, review, and post journal entries to the general ledger 4.Manging and ensuring compliance with all zakat & VAT polices &

Description

Description Release date: 06-April-2025__________________Due date: 03-May-2025 Instructions for submission: Assignment must be submitted with properly filled cover sheet (Name, ID, CRN, Submission date) in word document, Pdf is not accepted. Length of the write-up should be 1 to 2 pages (500 -750 words). Text size 12-Times New Roman with double

Description

Description Release date: 06-April-2025__________________Due date: 03-May-2025 Instructions for submission: Assignment must be submitted with properly filled cover sheet (Name, ID, CRN, Submission date) in word document, Pdf is not accepted. Length of the write-up should be 1 to 2 pages (500 -750 words). Text size 12-Times New Roman with double