Import database from ODBC (Firebird)

Hi Rod 

Thanks for posting! Unfortunately what you are trying to do is not currently supported in Lucidchart. However if you'd like this feature to be added in the future please add your request to our feature request form? Ideas submitted to this form are passed on directly to the product development team for their consideration.

Thanks for your help!

-- ===== Core Reference =====
CREATE TABLE AircraftBase (
  BaseID        VARCHAR(20) PRIMARY KEY,
  BaseName      VARCHAR(100) NOT NULL,
  City          VARCHAR(60),
  StateProvince VARCHAR(60),
  Country       VARCHAR(60)
);

CREATE TABLE Aircraft (
  AircraftID   VARCHAR(20) PRIMARY KEY,
  Model        VARCHAR(60) NOT NULL,
  Capacity     INTEGER     NOT NULL CHECK (Capacity > 0),
  Manufacturer VARCHAR(60),
  BaseID       VARCHAR(20) NOT NULL,
  CONSTRAINT fk_aircraft_base
    FOREIGN KEY (BaseID) REFERENCES AircraftBase(BaseID)
);

CREATE TABLE Passenger (
  PassengerID    VARCHAR(20) PRIMARY KEY,
  FirstName      VARCHAR(60) NOT NULL,
  MiddleName     VARCHAR(60),
  LastName       VARCHAR(60) NOT NULL,
  DateOfBirth    DATE        NOT NULL,
  Gender         VARCHAR(10),
  Email          VARCHAR(120) UNIQUE NOT NULL,
  Street         VARCHAR(120),
  City           VARCHAR(60),
  StateProvince  VARCHAR(60),
  Country        VARCHAR(60),
  PostalCode     VARCHAR(20)
  -- Age is derived; prefer a VIEW or generated column at DB level if supported
);

-- Multivalued attribute (phones)
CREATE TABLE PassengerPhone (
  PassengerID  VARCHAR(20) NOT NULL,
  PhoneNumber  VARCHAR(30) NOT NULL,
  PRIMARY KEY (PassengerID, PhoneNumber),
  CONSTRAINT fk_pp_passenger
    FOREIGN KEY (PassengerID) REFERENCES Passenger(PassengerID)
);

CREATE TABLE Flight (
  FlightID           VARCHAR(20) PRIMARY KEY,
  DepartureDateTime  TIMESTAMP NOT NULL,
  ArrivalDateTime    TIMESTAMP NOT NULL,
  Origin             VARCHAR(60) NOT NULL,
  Destination        VARCHAR(60) NOT NULL,
  AircraftID         VARCHAR(20) NOT NULL,
  CONSTRAINT fk_flight_aircraft
    FOREIGN KEY (AircraftID) REFERENCES Aircraft(AircraftID)
);

CREATE TABLE Reservation (
  ReservationID  VARCHAR(20) PRIMARY KEY,
  PassengerID    VARCHAR(20) NOT NULL,
  FlightID       VARCHAR(20) NOT NULL,
  BookingDate    TIMESTAMP   NOT NULL,
  SeatNumber     VARCHAR(10) NOT NULL,
  Status         VARCHAR(12) NOT NULL CHECK (Status IN ('confirmed','checked_in','canceled')),
  CONSTRAINT fk_res_passenger FOREIGN KEY (PassengerID) REFERENCES Passenger(PassengerID),
  CONSTRAINT fk_res_flight    FOREIGN KEY (FlightID)    REFERENCES Flight(FlightID),
  CONSTRAINT uq_flight_seat UNIQUE (FlightID, SeatNumber)
);

-- Weak entity: Baggage depends on Reservation
CREATE TABLE Baggage (
  ReservationID VARCHAR(20) NOT NULL,
  TagNumber     VARCHAR(30) NOT NULL,
  WeightKg      NUMERIC(6,2),
  Notes         VARCHAR(200),
  PRIMARY KEY (ReservationID, TagNumber),
  CONSTRAINT fk_bag_reservation
    FOREIGN KEY (ReservationID) REFERENCES Reservation(ReservationID)
);

-- ===== Loyalty (Supertype / Disjoint / Total) =====
CREATE TABLE LoyaltyMembership (
  MembershipID      VARCHAR(20) PRIMARY KEY,
  PassengerID       VARCHAR(20) NOT NULL UNIQUE,
  EnrollmentDate    DATE        NOT NULL,
  AccumulatedPoints INTEGER     NOT NULL DEFAULT 0 CHECK (AccumulatedPoints >= 0),
  CONSTRAINT fk_mem_passenger FOREIGN KEY (PassengerID) REFERENCES Passenger(PassengerID)
);

-- Subtypes (PK=FK)
CREATE TABLE Loyalty_Silver (
  MembershipID    VARCHAR(20) PRIMARY KEY,
  DiscountRatePct NUMERIC(5,2) NOT NULL CHECK (DiscountRatePct BETWEEN 0 AND 100),
  CONSTRAINT fk_silver_mem FOREIGN KEY (MembershipID) REFERENCES LoyaltyMembership(MembershipID)
);

CREATE TABLE Loyalty_Gold (
  MembershipID  VARCHAR(20) PRIMARY KEY,
  LoungeAccess  BOOLEAN NOT NULL DEFAULT TRUE,
  CONSTRAINT fk_gold_mem FOREIGN KEY (MembershipID) REFERENCES LoyaltyMembership(MembershipID)
);

CREATE TABLE Loyalty_Platinum (
  MembershipID         VARCHAR(20) PRIMARY KEY,
  ConciergeContactInfo VARCHAR(120) NOT NULL,
  CONSTRAINT fk_platinum_mem FOREIGN KEY (MembershipID) REFERENCES LoyaltyMembership(MembershipID)
);

-- Optional enforcement of disjointness can be done via triggers or exclusion constraints if supported.

-- ===== Crew (Supertype / Disjoint / Total) =====
CREATE TABLE Crew (
  CrewID            VARCHAR(20) PRIMARY KEY,
  Name              VARCHAR(100) NOT NULL,
  YearsOfExperience INTEGER      NOT NULL CHECK (YearsOfExperience >= 0)
);

CREATE TABLE Pilot (
  CrewID        VARCHAR(20) PRIMARY KEY,
  LicenseNumber VARCHAR(40) UNIQUE NOT NULL,
  FlightHours   INTEGER NOT NULL CHECK (FlightHours >= 0),
  CONSTRAINT fk_pilot_crew FOREIGN KEY (CrewID) REFERENCES Crew(CrewID)
);

CREATE TABLE CoPilot (
  CrewID               VARCHAR(20) PRIMARY KEY,
  BackupLicenseNumber  VARCHAR(40),
  TrainingLevel        VARCHAR(40),
  CONSTRAINT fk_copilot_crew FOREIGN KEY (CrewID) REFERENCES Crew(CrewID)
);

CREATE TABLE Attendant (
  CrewID         VARCHAR(20) PRIMARY KEY,
  LanguageSkills VARCHAR(200),
  CONSTRAINT fk_attendant_crew FOREIGN KEY (CrewID) REFERENCES Crew(CrewID)
);

-- M:N Crew assignments to flights (with group rules enforced procedurally)
CREATE TABLE FlightCrewAssignment (
  FlightID VARCHAR(20) NOT NULL,
  CrewID   VARCHAR(20) NOT NULL,
  AssignedRole VARCHAR(20), -- optional; type is implied by subtype, but kept for convenience
  PRIMARY KEY (FlightID, CrewID),
  CONSTRAINT fk_fca_flight FOREIGN KEY (FlightID) REFERENCES Flight(FlightID),
  CONSTRAINT fk_fca_crew   FOREIGN KEY (CrewID)   REFERENCES Crew(CrewID)
);

-- ===== Special Passenger Roles on Flights (Overlapping Subtypes) =====
CREATE TABLE PassengerFlightRole (
  PassengerID VARCHAR(20) NOT NULL,
  FlightID    VARCHAR(20) NOT NULL,
  RoleSeq     INTEGER     NOT NULL,
  PRIMARY KEY (PassengerID, FlightID, RoleSeq),
  CONSTRAINT fk_pfr_passenger FOREIGN KEY (PassengerID) REFERENCES Passenger(PassengerID),
  CONSTRAINT fk_pfr_flight    FOREIGN KEY (FlightID)    REFERENCES Flight(FlightID)
);

CREATE TABLE PFR_VIPGuest (
  PassengerID VARCHAR(20) NOT NULL,
  FlightID    VARCHAR(20) NOT NULL,
  RoleSeq     INTEGER     NOT NULL,
  VIPCategory VARCHAR(40) NOT NULL,
  PRIMARY KEY (PassengerID, FlightID, RoleSeq),
  CONSTRAINT fk_pfrvip_pfr FOREIGN KEY (PassengerID, FlightID, RoleSeq)
    REFERENCES PassengerFlightRole(PassengerID, FlightID, RoleSeq)
);

CREATE TABLE PFR_SafetyObserver (
  PassengerID    VARCHAR(20) NOT NULL,
  FlightID       VARCHAR(20) NOT NULL,
  RoleSeq        INTEGER     NOT NULL,
  ReportRefNumber VARCHAR(40) NOT NULL,
  PRIMARY KEY (PassengerID, FlightID, RoleSeq),
  CONSTRAINT fk_pfrso_pfr FOREIGN KEY (PassengerID, FlightID, RoleSeq)
    REFERENCES PassengerFlightRole(PassengerID, FlightID, RoleSeq)
);
 

Hi ​@najmy, 

Thanks for posting in the community! It looks like you're trying to create a table using SQL. The Lucid Community is a place for users to connect, ask questions, share use cases, and provide feedback.

Do you have a specific question or any feedback you'd like to share?