Course Content
Project Brief
0/1
Project Brief
ShopX Database Design & Implementation

You have just joined ShopX, a growing startup in the e-commerce industry, as a Data Analyst. The company needs a basic database to collect information about customers, products, sales, and employees. This first version of the database is mainly for data collection and analysis. For now, there are no enforced relationships (foreign keys) — we only focus on creating the correct structure.Your task is to design and populate the database according to the specifications below.  

1. Database Creation

Create a new database named:  CREATE DATABASE ShopX_DB

Tables to Create

Table 1: Customers

Column Name Data Type Null Allowed Notes
CustomerID INT (Identity) NOT NULL Auto-increment primary column
FirstName VARCHAR(50) NOT NULL Required
LastName VARCHAR(50) NOT NULL Required
Email VARCHAR(100) NOT NULL Required
PhoneNumber VARCHAR(20) NULL Optional
Country VARCHAR(50) NOT NULL Required

 

 

Table 2: Products

Column Name Data Type Null Allowed Notes
ProductID VARCHAR(10) NOT NULL Manual code (e.g., PRD001)
ProductName VARCHAR(100) NOT NULL Required
Category VARCHAR(50) NOT NULL Required
UnitPrice DECIMAL(10,2) NOT NULL Required
StockQty INT NOT NULL Required

 

Table 3: Sales

Column Name Data Type Null Allowed Notes
SaleID INT (Identity, starts at 1000) NOT NULL Auto-increment
CustomerID INT NOT NULL Refers to a customer, but no enforced FK
ProductID VARCHAR(10) NOT NULL Refers to a product code
Quantity INT NOT NULL Required
SaleDate DATETIME NOT NULL Required

 

Table 4: Employees

Column Name Data Type Null Allowed Notes
EmployeeID VARCHAR(10) NOT NULL Manual code (e.g., E001)
FirstName VARCHAR(50) NOT NULL Required
LastName VARCHAR(50) NOT NULL Required
Position VARCHAR(50) NOT NULL Required
HireDate DATE NOT NULL Required
Salary DECIMAL(10,2) NOT NULL Required

  Hints:

  1. If you don’t specify NOT NULL for a column, it can remain empty (NULL).
    1. Example: Email in Customers must be NOT NULL, otherwise some customers could be missing email addresses.
  2. You cannot rely on manual entry alone to ensure IDs are unique. Use IDENTITY for auto-increment columns like CustomerID or SaleID to automatically generate unique numbers.
    1. Example: SaleID starts at 1000 and increments automatically to avoid duplicates.

  3. Pick the right data type to avoid errors later:

    • Use INT for whole numbers like StockQty or Quantity.

    • Use DECIMAL(10,2) for prices or salaries to include cents.

    • Use VARCHAR/NVARCHAR for text like names and emails.

  4. Some IDs are manual codes (ProductID, EmployeeID) and cannot be auto-generated. You need to enter them carefully and maintain consistency (e.g., PRD001, PRD002).

    • Auto-increment should only be used where specified (CustomerID, SaleID).

 

Part – 2      Inserting Data

1. Customers Table

The Customers table stores basic information about ShopX customers. Fields like FirstName, LastName, Email, and Country are required, while PhoneNumber is optional.

 

CustomerID FirstName LastName Email PhoneNumber Country
1 John Doe john.doe@email.com 1234567890 USA
2 Jane Smith jane.smith@email.com 0987654321 UK
3 Michael Lee michael.lee@email.com NULL Canada
4 Aisha Ahmed aisha.ahmed@email.com 4471234567 Nigeria
5 Carlos Martinez carlos.martinez@email.com 5556781234 Mexico
6 Sophia Brown sophia.brown@email.com NULL Australia 

Single-row SQL example: 
INSERT INTO Customers (FirstName, LastName, Email, PhoneNumber, Country) VALUES ('John', 'Doe', 'john.doe@email.com', '1234567890', 'USA')

 

2. Products Table

The Products table holds product master data. ProductID is a manual code and must be unique. Fields like ProductName, Category, UnitPrice, and StockQty are required.

ProductID ProductName Category UnitPrice StockQty
PRD001 Wireless Mouse Electronics 25.50 150
PRD002 Laptop Stand Accessories 40.00 75
PRD003 Smartphone Electronics 599.99 40
PRD004 Office Chair Furniture 120.00 20
PRD005 Headphones Electronics 75.00 100
PRD006 Notebook Pack Stationery 10.00 200 
Single-row SQL example: 
INSERT INTO Products (ProductID, ProductName, Category, UnitPrice, StockQty) VALUES ('PRD001', 'Wireless Mouse', 'Electronics', 25.50, 150)

 

3. Sales Table

 

The Sales table records customer purchases. SaleID is auto-incremented starting at 1000. Fields like CustomerID, ProductID, Quantity, and SaleDate are required.

SaleID CustomerID ProductID Quantity SaleDate
1000 1 PRD001 2 2025-09-01 10:30:00
1001 2 PRD003 1 2025-09-02 14:45:00
1002 3 PRD002 3 2025-09-03 09:15:00
1003 4 PRD005 1 2025-09-04 16:10:00
1004 5 PRD004 2 2025-09-05 11:00:00
1005 6 PRD006 5 2025-09-06 13:20:00 
Single-row SQL example: 
INSERT INTO Sales (CustomerID, ProductID, Quantity, SaleDate) VALUES (1, 'PRD001', 2, '2025-09-01 10:30:00')

 

4. Employees Table

 

The Employees table stores company staff information. EmployeeID is a manual code. Fields like FirstName, LastName, Position, HireDate, and Salary are required.

EmployeeID FirstName LastName Position HireDate Salary
E001 Samuel Green Manager 2022-01-15 5000.00
E002 Lara White Sales Executive 2023-03-20 3500.00
E003 David Johnson Accountant 2021-11-10 4000.00
E004 Emily Davis HR Officer 2022-07-01 3200.00
E005 Ahmed Khan IT Support 2024-02-05 2800.00
E006 Grace Taylor Marketing Officer 2023-09-18 3600.00 

Single-row SQL example:
 INSERT INTO Employees (EmployeeID, FirstName, LastName, Position, HireDate, Salary) VALUES ('E001', 'Samuel', 'Green', 'Manager', '2022-01-15', 5000.00)
    •