classic sales database model

 

1. Offices

  • Stores office location information.

  • officeCode is the primary key.

  • Related to Employees via officeCode.

2. Employees

  • Contains employee details.

  • employeeNumber is the primary key.

  • officeCode is a foreign key from Offices.

  • reportsTo is a self-referencing foreign key (hierarchical reporting).

  • Related to Customers as sales representatives via salesRepEmployeeNumber.

3. Customers

  • Holds customer information.

  • customerNumber is the primary key.

  • Linked to:

    • Employees (via salesRepEmployeeNumber)

    • Payments and Orders (via customerNumber)

4. Payments

  • Tracks payments made by customers.

  • Composite primary key: checkNumber + customerNumber.

  • Foreign key: customerNumber from Customers.

5. Orders

  • Represents sales orders.

  • orderNumber is the primary key.

  • Linked to:

    • Customers (via customerNumber)

    • OrderDetails (one-to-many relationship)

6. OrderDetails

  • Line items for each order.

  • Composite primary key: orderNumber + productCode.

  • Foreign keys:

    • orderNumber from Orders

    • productCode from Products

7. Products

  • Product catalog information.

  • productCode is the primary key.

  • Foreign key: productLine from ProductLines.

8. ProductLines

  • Categorizes products into lines like "Classic Cars", "Ships", etc.

  • productLine is the primary key.





CREATE TABLE Offices (
  officeCode INT PRIMARY KEY,
  city VARCHAR(50),
  phone VARCHAR(20),
  addressLine1 VARCHAR(100),
  addressLine2 VARCHAR(100),
  state VARCHAR(50),
  country VARCHAR(50),
  postalCode VARCHAR(15),
  territory VARCHAR(50),
  officeLocation VARCHAR(100)
);

INSERT INTO Offices VALUES (1, 'New York', '1234567890', '123 Main St', NULL, 'NY', 'USA', '10001', 'East Coast', 'Floor 5');
INSERT INTO Offices VALUES (2, 'Los Angeles', '2134567890', '456 Sunset Blvd', 'Suite 201', 'CA', 'USA', '90001', 'West Coast', '2nd Floor');
INSERT INTO Offices VALUES (3, 'Chicago', '3124567890', '789 Lake Shore Dr', NULL, 'IL', 'USA', '60601', 'Midwest', '8th Floor');
INSERT INTO Offices VALUES (4, 'Dallas', '9724567890', '321 Elm St', 'Suite 300', 'TX', 'USA', '75201', 'South Central', 'Tower A');
INSERT INTO Offices VALUES (5, 'Seattle', '2064567890', '654 Pine St', NULL, 'WA', 'USA', '98101', 'Northwest', 'Level 4');
INSERT INTO Offices VALUES (6, 'Miami', '3054567890', '987 Ocean Dr', 'Apt 9A', 'FL', 'USA', '33101', 'Southeast', 'Penthouse');
INSERT INTO Offices VALUES (7, 'Denver', '3034567890', '234 Mountain Rd', NULL, 'CO', 'USA', '80201', 'Rocky Mountain', '5th Floor');
INSERT INTO Offices VALUES (8, 'Boston', '6174567890', '345 Freedom Trail', NULL, 'MA', 'USA', '02101', 'New England', 'Main Wing');
INSERT INTO Offices VALUES (9, 'San Francisco', '4154567890', '876 Market St', 'Suite 101', 'CA', 'USA', '94101', 'West Coast', 'HQ');
INSERT INTO Offices VALUES (10, 'Atlanta', '4044567890', '135 Peach St', NULL, 'GA', 'USA', '30301', 'Southeast', 'Bldg B');
==========================================================================================================
CREATE TABLE Employees (
  employeeNumber INT PRIMARY KEY,
  lastName VARCHAR(50),
  firstName VARCHAR(50),
  extension VARCHAR(10),
  email VARCHAR(100),
  reportsTo INT,
  jobTitle VARCHAR(50),
  officeCode INT,
  FOREIGN KEY (officeCode) REFERENCES Offices(officeCode)
);

INSERT INTO Employees VALUES (1001, 'Smith', 'John', 'x123', 'john.smith@example.com', NULL, 'Sales Manager', 1);
INSERT INTO Employees VALUES (1002, 'Johnson', 'Emily', 'x124', 'emily.johnson@example.com', 1001, 'Sales Rep', 1);
INSERT INTO Employees VALUES (1003, 'Davis', 'Michael', 'x125', 'michael.davis@example.com', 1001, 'Sales Rep', 1);
INSERT INTO Employees VALUES (1004, 'Brown', 'Linda', 'x126', 'linda.brown@example.com', 1008, 'Marketing Rep', 1);
INSERT INTO Employees VALUES (1005, 'Wilson', 'Robert', 'x127', 'robert.wilson@example.com', NULL, 'VP Sales', 1);
INSERT INTO Employees VALUES (1006, 'Taylor', 'Sophia', 'x128', 'sophia.taylor@example.com', 1005, 'HR Manager', 1);
INSERT INTO Employees VALUES (1007, 'Martinez', 'David', 'x129', 'david.martinez@example.com', 1005, 'IT Analyst', 1);
INSERT INTO Employees VALUES (1008, 'Anderson', 'Olivia', 'x130', 'olivia.anderson@example.com', 1001, 'Sales Rep', 1);
INSERT INTO Employees VALUES (1009, 'Thomas', 'Daniel', 'x131', 'daniel.thomas@example.com', 1005, 'Logistics Officer',1);
INSERT INTO Employees VALUES (1010, 'White', 'Emma', 'x132', 'emma.white@example.com', 1001, 'Sales Assistant', 1);
==========================================================================================================

CREATE TABLE Customers (
  customerNumber INT PRIMARY KEY,
  customerName VARCHAR(100),
  contactLastName VARCHAR(50),
  contactFirstName VARCHAR(50),
  phone VARCHAR(20),
  addressLine1 VARCHAR(100),
  addressLine2 VARCHAR(100),
  city VARCHAR(50),
  state VARCHAR(50),
  postalCode VARCHAR(15),
  country VARCHAR(50),
  salesRepEmployeeNumber INT,
  creditLimit DECIMAL(10, 2),
  customerLocation VARCHAR(100),
  FOREIGN KEY (salesRepEmployeeNumber) REFERENCES Employees(employeeNumber)
);

INSERT INTO Customers VALUES (2001, 'ABC Corp', 'Doe', 'Jane', '9876543210', '456 Elm St', NULL, 'Boston', 'MA', '02101', 'USA', 1001, 10000.00, 'East Division');
INSERT INTO Customers VALUES (2002, 'XYZ Ltd', 'Brown', 'Lisa', '1234567890', '123 Oak St', 'Suite 100', 'New York', 'NY', '10001', 'USA', 1002, 15000.00, 'North Division');
INSERT INTO Customers VALUES (2003, 'Global Inc.', 'Smith', 'John', '4567891230', '789 Maple Ave', NULL, 'Chicago', 'IL', '60601', 'USA', 1003, 20000.00, 'Midwest Division');
INSERT INTO Customers VALUES (2004, 'Tech Solutions', 'Lee', 'Kevin', '3216549870', '987 Tech Rd', 'Building A', 'San Francisco', 'CA', '94101', 'USA', 1001, 18000.00, 'West Division');
INSERT INTO Customers VALUES (2005, 'BrightWorks', 'Kim', 'Sara', '7418529630', '654 Innovation Dr', NULL, 'Seattle', 'WA', '98101', 'USA', 1002, 12000.00, 'Pacific Northwest');
INSERT INTO Customers VALUES (2006, 'Quantum LLC', 'Wong', 'Amy', '9638527410', '321 Energy Ln', 'Floor 3', 'Dallas', 'TX', '75201', 'USA', 1004, 9500.00, 'South Central');
INSERT INTO Customers VALUES (2007, 'NextGen', 'Patel', 'Raj', '8529637410', '147 Future Ave', NULL, 'Denver', 'CO', '80201', 'USA', 1005, 8700.00, 'Mountain Region');
INSERT INTO Customers VALUES (2008, 'Elite Holdings', 'Garcia', 'Luis', '7894561230', '258 Legacy Dr', 'Ste 200', 'Miami', 'FL', '33101', 'USA', 1006, 16000.00, 'Southeast Market');
INSERT INTO Customers VALUES (2009, 'Apex Partners', 'Nguyen', 'Linh', '6547893210', '369 Vision Blvd', NULL, 'Atlanta', 'GA', '30301', 'USA', 1007, 14000.00, 'Southern Sales');
INSERT INTO Customers VALUES (2010, 'Horizon Ltd.', 'Miller', 'Ben', '3217896540', '753 Skyway Rd', 'Unit 5B', 'Los Angeles', 'CA', '90001', 'USA', 1001, 10500.00, 'West Coast');
==========================================================================================================

CREATE TABLE Payments (
  checkNumber VARCHAR(20) PRIMARY KEY,
  paymentDate DATE,
  amount DECIMAL(10, 2),
  customerNumber INT,
  FOREIGN KEY (customerNumber) REFERENCES Customers(customerNumber)
);

INSERT INTO Payments VALUES ('CHK1001', TO_DATE('2024-06-01', 'YYYY-MM-DD'), 2000.00, 2001);
INSERT INTO Payments VALUES ('CHK1002', TO_DATE('2024-06-02', 'YYYY-MM-DD'), 3500.50, 2002);
INSERT INTO Payments VALUES ('CHK1003', TO_DATE('2024-06-03', 'YYYY-MM-DD'), 1500.75, 2003);
INSERT INTO Payments VALUES ('CHK1004', TO_DATE('2024-06-04', 'YYYY-MM-DD'), 2700.00, 2004);
INSERT INTO Payments VALUES ('CHK1005', TO_DATE('2024-06-05', 'YYYY-MM-DD'), 4200.00, 2005);
INSERT INTO Payments VALUES ('CHK1006', TO_DATE('2024-06-06', 'YYYY-MM-DD'), 1900.25, 2006);
INSERT INTO Payments VALUES ('CHK1007', TO_DATE('2024-06-07', 'YYYY-MM-DD'), 3100.00, 2007);
INSERT INTO Payments VALUES ('CHK1008', TO_DATE('2024-06-08', 'YYYY-MM-DD'), 2300.10, 2008);
INSERT INTO Payments VALUES ('CHK1009', TO_DATE('2024-06-09', 'YYYY-MM-DD'), 1800.00, 2009);
INSERT INTO Payments VALUES ('CHK1010', TO_DATE('2024-06-10', 'YYYY-MM-DD'), 3600.00, 2010);
==========================================================================================================
CREATE TABLE Orders (
  orderNumber INT PRIMARY KEY,
  orderDate DATE,
  requiredDate DATE,
  shippedDate DATE,
  status VARCHAR(20),
  comments VARCHAR(255),
  customerNumber INT,
  FOREIGN KEY (customerNumber) REFERENCES Customers(customerNumber)
);

INSERT INTO Orders VALUES (3001, TO_DATE('2024-06-10', 'YYYY-MM-DD'), TO_DATE('2024-06-15', 'YYYY-MM-DD'), TO_DATE('2024-06-12', 'YYYY-MM-DD'), 'Shipped', 'First Order', 2001);
INSERT INTO Orders VALUES (3002, TO_DATE('2024-06-11', 'YYYY-MM-DD'), TO_DATE('2024-06-16', 'YYYY-MM-DD'), TO_DATE('2024-06-13', 'YYYY-MM-DD'), 'Shipped', 'Repeat order - priority', 2002);
INSERT INTO Orders VALUES (3003, TO_DATE('2024-06-12', 'YYYY-MM-DD'), TO_DATE('2024-06-17', 'YYYY-MM-DD'), TO_DATE('2024-06-14', 'YYYY-MM-DD'), 'In Process', 'Rush delivery requested', 2003);
INSERT INTO Orders VALUES (3004, TO_DATE('2024-06-13', 'YYYY-MM-DD'), TO_DATE('2024-06-18', 'YYYY-MM-DD'), NULL, 'On Hold', 'Awaiting stock clearance', 2004);
INSERT INTO Orders VALUES (3005, TO_DATE('2024-06-14', 'YYYY-MM-DD'), TO_DATE('2024-06-19', 'YYYY-MM-DD'), NULL, 'Cancelled', 'Customer cancelled due to delay', 2005);
INSERT INTO Orders VALUES (3006, TO_DATE('2024-06-15', 'YYYY-MM-DD'), TO_DATE('2024-06-20', 'YYYY-MM-DD'), TO_DATE('2024-06-19', 'YYYY-MM-DD'), 'Shipped', 'Delivered with tracking', 2006);
INSERT INTO Orders VALUES (3007, TO_DATE('2024-06-16', 'YYYY-MM-DD'), TO_DATE('2024-06-21', 'YYYY-MM-DD'), NULL, 'In Process', 'Packaging in progress', 2007);
INSERT INTO Orders VALUES (3008, TO_DATE('2024-06-17', 'YYYY-MM-DD'), TO_DATE('2024-06-22', 'YYYY-MM-DD'), TO_DATE('2024-06-20', 'YYYY-MM-DD'), 'Shipped', 'Bulk order dispatched', 2008);
INSERT INTO Orders VALUES (3009, TO_DATE('2024-06-18', 'YYYY-MM-DD'), TO_DATE('2024-06-23', 'YYYY-MM-DD'), NULL, 'On Hold', 'Waiting for payment', 2009);
INSERT INTO Orders VALUES (3010, TO_DATE('2024-06-19', 'YYYY-MM-DD'), TO_DATE('2024-06-24', 'YYYY-MM-DD'), TO_DATE('2024-06-21', 'YYYY-MM-DD'), 'Shipped', 'Delivered and confirmed', 2010);
==========================================================================================================
CREATE TABLE ProductLines (
  productLine VARCHAR(50) PRIMARY KEY,
  textDescription VARCHAR(255)
);

INSERT INTO ProductLines VALUES ('Classic Cars', 'Model cars from the 60s and 70s.');
INSERT INTO ProductLines VALUES ('Motorcycles', 'Replica motorcycles in various scales and eras.');
INSERT INTO ProductLines VALUES ('Planes', 'Model airplanes including WW2 and commercial planes.');
INSERT INTO ProductLines VALUES ('Ships', 'Historic ships and modern cruisers.');
INSERT INTO ProductLines VALUES ('Trains', 'Steam and electric model trains.');
INSERT INTO ProductLines VALUES ('Trucks and Buses', 'Miniature trucks, pickups, and buses.');
INSERT INTO ProductLines VALUES ('Vintage Cars', 'Early 1900s and antique car collections.');
INSERT INTO ProductLines VALUES ('Construction Vehicles', 'Bulldozers, cranes, and other work vehicles.');
INSERT INTO ProductLines VALUES ('Race Cars', 'Formula 1 and rally car models.');
INSERT INTO ProductLines VALUES ('Military Vehicles', 'Tanks, armored cars, and missile carriers.');
==========================================================================================================
CREATE TABLE Products (
  productCode VARCHAR(20) PRIMARY KEY,
  productName VARCHAR(100),
  productScale VARCHAR(10),
  productVendor VARCHAR(100),
  productDescription VARCHAR(255),
  quantityInStock INT,
  buyPrice DECIMAL(10, 2),
  MSRP DECIMAL(10, 2),
  productLine VARCHAR(50),
  FOREIGN KEY (productLine) REFERENCES ProductLines(productLine)
);

INSERT INTO Products VALUES ('S10_1678', '1969 Harley Davidson Ultimate Chopper', '1:10', 'Min Lin Diecast', 'Detailed replica', 100, 48.81, 95.70, 'Classic Cars');
INSERT INTO Products VALUES ('S10_1949', '1952 Alpine Renault 1300', '1:12', 'Classic Metal Creations', 'Accurate build with alloy body', 75, 98.58, 214.30, 'Vintage Cars');
INSERT INTO Products VALUES ('S10_2016', '1996 Moto Guzzi 1100i', '1:10', 'Highway 66 Mini Classics', 'Custom paint & working suspension', 89, 68.99, 120.00, 'Motorcycles');
INSERT INTO Products VALUES ('S10_4698', '2003 Harley-Davidson Eagle Drag Bike', '1:10', 'Red Start Diecast', 'True-scale drag racing replica', 50, 91.02, 193.66, 'Motorcycles');
INSERT INTO Products VALUES ('S12_1099', '1968 Ford Mustang', '1:18', 'Classic Metal Creations', 'Muscle car series limited edition', 120, 34.00, 65.00, 'Classic Cars');
INSERT INTO Products VALUES ('S12_1666', '1937 Lincoln Berline', '1:18', 'Welly Diecast Productions', 'Luxury vehicle with rich detail', 66, 40.22, 85.00, 'Vintage Cars');
INSERT INTO Products VALUES ('S12_2823', '2001 Ferrari Enzo', '1:18', 'AutoArt Studio Design', 'Ferrari racing series', 33, 92.75, 210.00, 'Race Cars');
INSERT INTO Products VALUES ('S18_1129', 'Titanic Ocean Liner Model', '1:700', 'ShipWorks Co.', 'Highly detailed historic ship model', 25, 150.00, 295.00, 'Ships');
INSERT INTO Products VALUES ('S18_1589', 'Steam Locomotive 1890', '1:72', 'TrainZone', 'Old Western-style steam engine', 45, 80.50, 150.00, 'Trains');
INSERT INTO Products VALUES ('S24_1937', 'Military Tank M4 Sherman', '1:35', 'Battleground Miniatures', 'WW2 US tank replica with treads', 40, 70.00, 149.00, 'Military Vehicles');

==========================================================================================================
CREATE TABLE OrderDetails (
  orderNumber INT,
  productCode VARCHAR(20),
  quantityOrdered INT,
  priceEach DECIMAL(10, 2),
  orderLineNumber INT,
  PRIMARY KEY (orderNumber, productCode),
  FOREIGN KEY (orderNumber) REFERENCES Orders(orderNumber),
  FOREIGN KEY (productCode) REFERENCES Products(productCode)
);

INSERT INTO OrderDetails VALUES (3001, 'S10_1678', 2, 95.70, 1);
INSERT INTO OrderDetails VALUES (3001, 'S10_1949', 1, 214.30, 2);
INSERT INTO OrderDetails VALUES (3002, 'S10_2016', 3, 120.00, 1);
INSERT INTO OrderDetails VALUES (3003, 'S10_4698', 1, 193.66, 1);
INSERT INTO OrderDetails VALUES (3003, 'S12_1099', 2, 65.00, 2);
INSERT INTO OrderDetails VALUES (3004, 'S12_1666', 4, 85.00, 1);
INSERT INTO OrderDetails VALUES (3005, 'S12_2823', 1, 210.00, 1);
INSERT INTO OrderDetails VALUES (3006, 'S18_1129', 1, 295.00, 1);
INSERT INTO OrderDetails VALUES (3007, 'S18_1589', 2, 150.00, 1);
INSERT INTO OrderDetails VALUES (3008, 'S24_1937', 3, 149.00, 1);

==========================================================================================================
Subscribe to: Posts (Atom)