database setup for F28DM CW1

dbms.sql

-- Creates table for holding customer information
CREATE TABLE DBCustomer (
    customerId      INT PRIMARY KEY,
    mainAddress     VARCHAR(100) NOT NULL,
    postCode        VARCHAR(10) NOT NULL,
    phoneNumber     VARCHAR(15) NOT NULL,
    email           VARCHAR(50) NOT NULL
) ENGINE=INNODB;

-- Customer Mandatory Or with Consumer or Business
CREATE TABLE DBConsumerCustomer (
    customerId      INT PRIMARY KEY,
    firstName       VARCHAR(20) NOT NULL,
    lastName        VARCHAR(20) NOT NULL,
    CONSTRAINT fk_consumerCustId FOREIGN KEY (customerId) REFERENCES DBCustomer (customerId)
) ENGINE=INNODB;

CREATE TABLE DBBusinessCustomer (
    customerId          INT PRIMARY KEY,
    businessName        VARCHAR(100) NOT NULL,
    contactFirstName    VARCHAR(20) NOT NULL,
    contactLastName     VARCHAR(20) NOT NULL,
    CONSTRAINT fk_businessCustId FOREIGN KEY (customerId) REFERENCES DBCustomer (customerId)
) ENGINE=INNODB;
-- Creates table to hold individual payments
CREATE TABLE DBPayment (
    paymentId       CHAR(12) PRIMARY KEY,
    subTotal        FLOAT NOT NULL,
    tax             INT NOT NULL,
) ENGINE=INNODB;

-- Creates package table
CREATE TABLE DBPackage (
    trkNum              CHAR(12) PRIMARY KEY,
    pickUpAddr          VARCHAR(100) NOT NULL,
    pickUpPostCode      VARCHAR(10) NOT NULL,
    deliveryAddr        VARCHAR(100) NOT NULL,
    deliveryPostCode    VARCHAR(10) NOT NULL,
    deliveryType        ENUM('Standard', 'Express', 'NextDay12') NOT NULL,
    weight              FLOAT NOT NULL,
    dateTimePickUp      DATETIME,
    dateTimeDelivery    DATETIME,
    customerId          INT NOT NULL,
    paymentId           CHAR(12) NOT NULL,
    CONSTRAINT fk_custid2 FOREIGN KEY (customerId) REFERENCES DBCustomer (customerId),
    CONSTRAINT fk_payid FOREIGN KEY (paymentId) REFERENCES DBPayment (paymentId)
) ENGINE=INNODB;

-- Create Depot table
CREATE TABLE DBDepot (
    depotId     INT PRIMARY KEY,
    address     VARCHAR(100) NOT NULL,
    postCode    VARCHAR(10) NOT NULL,
    depotName   VARCHAR(30) NOT NULL
) ENGINE=INNODB;

-- Vehicle Table
CREATE TABLE DBVehicle (
    registration    VARCHAR(7) PRIMARY KEY,
    vehicleTax      DATE,
    mot             DATE,
    type            ENUM('Van','Truck','Lorry'),
    depotId         INT,
    CONSTRAINT fk_depotId FOREIGN KEY (depotId) REFERENCES DBDepot (depotId)
) ENGINE=INNODB;

--
CREATE TABLE DBStaff (
    niNum           CHAR(9) PRIMARY KEY,
    firstName       VARCHAR(20) NOT NULL,
    lastName        VARCHAR(20) NOT NULL,
    dateOfBirth     DATE NOT NULL,
    gender          ENUM('M','F') NOT NULL,
    jobTitle        ENUM('manager','admin','warehouse','driver') NOT NULL,
    depotId         INT,
    supNi           CHAR(9),
    CONSTRAINT fk_depotId2 FOREIGN KEY (depotId) REFERENCES DBDepot (depotId),
    CONSTRAINT fk_supNi FOREIGN KEY (supNi) REFERENCES DBStaff (niNum)
) ENGINE=INNODB;

--
CREATE TABLE DBDriver (
    niNum           CHAR(9) PRIMARY KEY,
    licenceNum      CHAR(18) NOT NULL,
    licenceExpire   DATE NOT NULL,
    CONSTRAINT fk_staffNi FOREIGN KEY (niNum) REFERENCES DBStaff (niNum)
) ENGINE=INNODB;

CREATE TABLE DBVehicleLog (
    logId       CHAR(10) PRIMARY KEY,
    vehicleReg  VARCHAR(7) NOT NULL,
    driverNi    CHAR(9) NOT NULL,
    timeOut     DATETIME NOT NULL,
    timeIn      DATETIME,
    CONSTRAINT fk_driverNi2 FOREIGN KEY (driverNi) REFERENCES DBStaff (niNum),
    CONSTRAINT fk_vehicReg2 FOREIGN KEY (vehicleReg) REFERENCES DBVehicle (registration)
) ENGINE=INNODB;

-- Create Consignment table
CREATE TABLE DBConsignment (
    consignmentId       CHAR(12) PRIMARY KEY,
    pickUpDepot         INT NOT NULL,
    deliveryDepot       INT NOT NULL,
    dateTimePickUp      DATETIME,
    dateTimeDelivery    DATETIME,
    vehicleReg          VARCHAR(7),
    driverNi            CHAR(9),
    CONSTRAINT fk_driverNi FOREIGN KEY (driverNi) REFERENCES DBStaff (niNum),
    CONSTRAINT fk_vehicReg FOREIGN KEY (vehicleReg) REFERENCES DBVehicle (registration),
    CONSTRAINT fk_fromDepot FOREIGN KEY (pickUpDepot) REFERENCES DBDepot (depotId),
    CONSTRAINT fk_toDepot FOREIGN KEY (deliveryDepot) REFERENCES DBDepot (depotId)
) ENGINE=INNODB;

--
CREATE TABLE DBConsignmentPackage (
    consignmentId   CHAR(12),
    trkNum          CHAR(12),
    PRIMARY KEY (consignmentId, trkNum),
    CONSTRAINT fk_consignment FOREIGN KEY (consignmentId) REFERENCES DBConsignment (consignmentId),
    CONSTRAINT fk_trk FOREIGN KEY (trkNum) REFERENCES DBPackage (trkNum)
) ENGINE=INNODB;

On line 34, change "CONSTRAINT fk_custid" to "CONSTRAINT fk_custid2"