Lynn DB

db.sql

-- Create database
CREATE DATABASE "RENTALS";

CREATE TABLE CUSTOMER (
  CID SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  address TEXT NOT NULL,
  phone TEXT NOT NULL
);

CREATE TABLE EQUIPMENT (
  SKU SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  description TEXT NOT NULL,
  features TEXT NOT NULL,
  specifications TEXT NOT NULL
);

CREATE TABLE DURATION (
  DCode SERIAL PRIMARY KEY,
  DUnit TEXT NOT NULL,
  Duration INT NOT NULL
);

CREATE TABLE RENTAL_RATE (
  SKU UUID,
  DCode UUID,
  PRIMARY KEY (SKU, DCode),
  FOREIGN KEY (SKU) REFERENCES EQUIPMENT (SKU),
  FOREIGN KEY (DCode) REFERENCES DURATION (DCode),
  Rate MONEY NOT NULL
);

CREATE TABLE BRANCH (
  BCode SERIAL PRIMARY KEY,
  Address TEXT NOT NULL,
  POSTCODE TEXT NOT NULL,
  STATE TEXT NOT NULL,
  HOURS TEXT NOT NULL,
  PHONE TEXT NOT NULL
);

CREATE TABLE BRANCH_EQUIPMENT (
  SKU UUID,
  BCode UUID,
  PRIMARY KEY (SKU, BCode),
  FOREIGN KEY (SKU) REFERENCES EQUIPMENT (SKU),
  FOREIGN KEY (BCode) REFERENCES BRANCH (BCode),
  Quantity INT NOT NULL
);

CREATE TABLE BOOKING_DETAIL (
  BID UUID,
  SKU UUID,
  PRIMARY KEY (BID, SKU),
  FOREIGN KEY (BID) REFERENCES BOOKING (BID),
  FOREIGN KEY (SKU) REFERENCES EQUIPMENT (SKU)
);

CREATE TABLE BOOKING (
  BID SERIAL PRIMARY KEY,
  CID UUID,
  BCode UUID,
  DCode UUID,
  FOREIGN KEY (CID) REFERENCES CUSTOMER (CID),
  FOREIGN KEY (BCode) REFERENCES BRANCH (BCode),
  FOREIGN KEY (DCode) REFERENCES DURATION (DCode),
  Starting DATE NOT NULL,
  Duration INT NOT NULL
);

CREATE TABLE RENTAL (
  BID UUID,
  FOREIGN KEY (BID) REFERENCES BOOKING (BID),
  Started DATE NOT NULL,
  Ended DATE NOT NULL,
  Total_Amount MONEY NOT NULL,
  Tax_Amount MONEY NOT NULL
);

-- Insert sample data into CUSTOMER table
INSERT INTO
  CUSTOMER (name, address, phone)
VALUES
  ('John Doe', '123 Main St', '555-1234-5678'),
  ('Jane Smith', '456 Elm St', '555-9876-5432');

-- Insert sample data into EQUIPMENT table
INSERT INTO
  EQUIPMENT (name, description, features, specifications)
VALUES
  (
    'Lawn Mower',
    'Powerful lawn mower for large yards',
    'Gas-powered, self-propelled',
    'Engine: 200cc, Cutting width: 22"'
  ),
  (
    'Chainsaw',
    'Heavy-duty chainsaw for professional use',
    '18-inch bar, anti-vibration system',
    'Engine: 50cc, Chain speed: 3000 RPM'
  );

-- Insert sample data into DURATION table
INSERT INTO
  DURATION (DUnit, Duration)
VALUES
  ('Day', 1),
  ('Week', 7);

-- Insert sample data into RENTAL_RATE table and reference values using WITH and FROM
WITH equipment_duration_data AS (
  SELECT
    SKU,
    DCode
  FROM
    EQUIPMENT,
    DURATION
  WHERE
    name = 'Lawn Mower'
    AND DUnit = 'Day'
)
INSERT INTO
  RENTAL_RATE (SKU, DCode, Rate)
SELECT
  SKU,
  DCode,
  25.00
FROM
  equipment_duration_data;

-- Insert sample data into BRANCH table
INSERT INTO
  BRANCH (Address, POSTCODE, STATE, HOURS, PHONE)
VALUES
  (
    '789 Oak St',
    '12345',
    'CA',
    '9 AM - 6 PM',
    '555-2468-1357'
  ),
  (
    '456 Maple Ave',
    '67890',
    'NY',
    '10 AM - 7 PM',
    '555-9876-5432'
  );

-- Insert sample data into BRANCH_EQUIPMENT table and reference values using WITH and FROM
WITH branch_equipment_data AS (
  SELECT
    SKU,
    BCode
  FROM
    EQUIPMENT,
    BRANCH
  WHERE
    name = 'Lawn Mower'
    AND STATE = 'CA'
)
INSERT INTO
  BRANCH_EQUIPMENT (SKU, BCode, Quantity)
SELECT
  SKU,
  BCode,
  5
FROM
  branch_equipment_data;

-- Insert sample data into BOOKING table and reference values using WITH and FROM
WITH customer_data AS (
  SELECT
    CID
  FROM
    CUSTOMER
  WHERE
    name = 'John Doe'
),
branch_data AS (
  SELECT
    BCode
  FROM
    BRANCH
  WHERE
    STATE = 'CA'
),
duration_data AS (
  SELECT
    DCode
  FROM
    DURATION
  WHERE
    DUnit = 'Day'
)
INSERT INTO
  BOOKING (CID, BCode, DCode, Starting, Duration)
SELECT
  CID,
  BCode,
  DCode,
  '2023-08-27',
  1
FROM
  customer_data,
  branch_data,
  duration_data;