What the 2020 model got right, and wrong

-- MySQL Workbench Forward Engineering (regenerated from coursework/1118472.mwb)

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
USE `mydb` ;

-- -----------------------------------------------------
-- Table `mydb`.`Ticket`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Ticket` (
  `Ticket ID` INT NOT NULL AUTO_INCREMENT COMMENT 'Each visitor has unique ticket ID, unique bar code record ticket ID',
  `Invoices_Invoice ID` INT NOT NULL COMMENT 'Use as ticket purchase reference',
  `Transportation` ENUM('Walk', 'Metro', 'Train', 'Bus', 'Taxi', 'Private Vehicle', 'Other') NULL COMMENT 'As visitor first time enter the museum, the transportation must be record.',
  `Booked ID` INT NULL COMMENT 'Use to check special exhibition booking',
  `Exhibition Booking ID` INT NULL COMMENT 'Special exhibition book ID',
  PRIMARY KEY (`Ticket ID`),
  INDEX `fk_Ticket_Invoices1_idx` (`Invoices_Invoice ID` ASC) VISIBLE,
  INDEX `fk_Ticket_Booking Ticket1_idx` (`Booked ID` ASC, `Exhibition Booking ID` ASC) VISIBLE,
  CONSTRAINT `fk_Ticket_Invoices1`
    FOREIGN KEY (`Invoices_Invoice ID`)
    REFERENCES `mydb`.`Invoices` (`Invoice ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Ticket_Booking Ticket1`
    FOREIGN KEY (`Booked ID` , `Exhibition Booking ID`)
    REFERENCES `mydb`.`Booking Ticket` (`Booked ID` , `Exhibition Booking ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `mydb`.`Invoices`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Invoices` (
  `Invoice ID` INT NOT NULL AUTO_INCREMENT COMMENT 'Unique invoice number',
  `Product ID` INT NOT NULL COMMENT 'Use to reference which product were purchased',
  `Payment Method_Payment ID` VARCHAR(45) NOT NULL COMMENT 'Use to reference the payment method',
  `Purchase Quantity` INT NOT NULL,
  PRIMARY KEY (`Invoice ID`, `Product ID`),
  INDEX `fk_Invoices_Payment Method1_idx` (`Payment Method_Payment ID` ASC) VISIBLE,
  INDEX `fk_Invoices_Product1_idx` (`Product ID` ASC) VISIBLE,
  CONSTRAINT `fk_Invoices_Payment Method1`
    FOREIGN KEY (`Payment Method_Payment ID`)
    REFERENCES `mydb`.`Payment Methods` (`Payment Method ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Invoices_Product1`
    FOREIGN KEY (`Product ID`)
    REFERENCES `mydb`.`Product` (`Product ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `mydb`.`Product`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Product` (
  `Product ID` INT NOT NULL,
  `Product Name` VARCHAR(20) NOT NULL,
  `Product Price` FLOAT NOT NULL,
  PRIMARY KEY (`Product ID`))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `mydb`.`EFTPOS`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`EFTPOS` (
  `EFTPOS ID` INT NOT NULL AUTO_INCREMENT,
  `EFTPOS Types` ENUM('Credit Card', 'Debit Card', 'Digital Wallet') NOT NULL COMMENT 'Record card type',
  `Purchase Channel` ENUM('Online', 'Offline') NOT NULL COMMENT 'Channel: Online / Offline',
  `Card Issued Country` VARCHAR(45) NOT NULL,
  `Financial Institution Name` VARCHAR(45) NOT NULL,
  `Issued Bank Branch` VARCHAR(45) NOT NULL,
  `Account Name` VARCHAR(30) NOT NULL,
  `Card Number` INT(8) NOT NULL COMMENT 'Card Number only record the first 4 and end 4 numbers.',
  `Expiry Date` DATE NOT NULL,
  PRIMARY KEY (`EFTPOS ID`))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `mydb`.`Cash`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Cash` (
  `Cash Payment ID` INT NOT NULL AUTO_INCREMENT,
  `City` VARCHAR(50) NOT NULL,
  `Country` VARCHAR(50) NOT NULL,
  `First Name` VARCHAR(30) NOT NULL,
  PRIMARY KEY (`Cash Payment ID`))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `mydb`.`Payment Methods`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Payment Methods` (
  `Payment Method ID` INT NOT NULL AUTO_INCREMENT COMMENT 'Check the payment method',
  `EFTPOS_EFTPOS ID` INT NULL COMMENT 'EFTPOS is for card and digital wallet payment method',
  `Cash_Cash ID` INT NULL COMMENT 'All cash payment will consider as a offline trade',
  PRIMARY KEY (`Payment Method ID`, `EFTPOS_EFTPOS ID`, `Cash_Cash ID`),
  INDEX `fk_Payment Method_EFTPOS1_idx` (`EFTPOS_EFTPOS ID` ASC) VISIBLE,
  INDEX `fk_Payment Method_Cash1_idx` (`Cash_Cash ID` ASC) VISIBLE,
  CONSTRAINT `fk_Payment Method_EFTPOS1`
    FOREIGN KEY (`EFTPOS_EFTPOS ID`)
    REFERENCES `mydb`.`EFTPOS` (`EFTPOS ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Payment Method_Cash1`
    FOREIGN KEY (`Cash_Cash ID`)
    REFERENCES `mydb`.`Cash` (`Cash Payment ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `mydb`.`Entrance`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Entrance` (
  `Gate Name` ENUM('Pyramid', 'Carrousel de Louvre', '99 rue de Rivioli', 'Passage Richelieu', 'Portes de Lion') NOT NULL COMMENT 'Identify which gate',
  `Ticket ID` INT NOT NULL COMMENT 'Record Ticket ID',
  `Entrance Date/Time` DATETIME NOT NULL,
  `Business Day` ENUM('True', 'False') NOT NULL COMMENT 'To justify is working day or not',
  PRIMARY KEY (`Gate Name`, `Ticket ID`),
  INDEX `fk_Entrance_Ticket1_idx` (`Ticket ID` ASC) VISIBLE,
  CONSTRAINT `fk_Entrance_Ticket1`
    FOREIGN KEY (`Ticket ID`)
    REFERENCES `mydb`.`Ticket` (`Ticket ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `mydb`.`Wings`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Wings` (
  `Wings Name` ENUM('Richelieu', 'Denon', 'Sully') NOT NULL COMMENT 'Identify which wing',
  `Ticket_Ticket ID` INT NOT NULL COMMENT 'Record ticket id',
  `Visit Time` DATETIME NOT NULL,
  PRIMARY KEY (`Wings Name`),
  INDEX `fk_Wings_Ticket1_idx` (`Ticket_Ticket ID` ASC) VISIBLE,
  CONSTRAINT `fk_Wings_Ticket1`
    FOREIGN KEY (`Ticket_Ticket ID`)
    REFERENCES `mydb`.`Ticket` (`Ticket ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `mydb`.`Hired Audio Guide`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Hired Audio Guide` (
  `Hired ID` INT NOT NULL AUTO_INCREMENT COMMENT 'Unique hiring id',
  `Serial Number` VARCHAR(16) NOT NULL COMMENT 'Audio device serial number',
  `Language Setting` ENUM('French', 'English', 'Italian', 'Russian', 'Chinese Mandarin', 'Spanish', 'Portuguese', 'Korean', 'Dutch', 'Polish', 'Swedish', 'Norwegian', 'Finnish') NOT NULL,
  `Issued Wing` ENUM('Richelieu', 'Denon', 'Sully') NOT NULL,
  `Returned Wing` ENUM('Richelieu', 'Denon', 'Sully') NULL,
  `Issued Time` DATETIME NOT NULL,
  `Return Time` DATETIME NULL,
  `Invoice ID` INT NOT NULL,
  `Product ID` INT NOT NULL,
  `Ticket ID` INT NOT NULL,
  PRIMARY KEY (`Hired ID`, `Serial Number`),
  INDEX `fk_Hiring Audio Guide_Audio Guide Device1_idx` (`Serial Number` ASC) VISIBLE,
  INDEX `fk_Hired Audio Guide_Invoices1_idx` (`Invoice ID` ASC, `Product ID` ASC) VISIBLE,
  INDEX `fk_Hired Audio Guide_Ticket1_idx` (`Ticket ID` ASC) VISIBLE,
  CONSTRAINT `fk_Hiring Audio Guide_Audio Guide Device1`
    FOREIGN KEY (`Serial Number`)
    REFERENCES `mydb`.`Audio Guide Device` (`Serial Number`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Hired Audio Guide_Invoices1`
    FOREIGN KEY (`Invoice ID` , `Product ID`)
    REFERENCES `mydb`.`Invoices` (`Invoice ID` , `Product ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Hired Audio Guide_Ticket1`
    FOREIGN KEY (`Ticket ID`)
    REFERENCES `mydb`.`Ticket` (`Ticket ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `mydb`.`Audio Guide Device`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Audio Guide Device` (
  `Serial Number` VARCHAR(16) NOT NULL COMMENT 'Audio device serial number',
  `Activated Date` DATE NOT NULL,
  `Retired Date` DATE NOT NULL,
  PRIMARY KEY (`Serial Number`))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `mydb`.`Exhibition Booking`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Exhibition Booking` (
  `Booking ID` INT NOT NULL AUTO_INCREMENT COMMENT 'Booking id',
  `Exhibition ID` INT NOT NULL COMMENT 'Identify booked exhibition',
  `Time Slot ID` INT NOT NULL COMMENT 'Identify which time slot has been selected',
  `Ticket ID` INT NULL COMMENT 'Record ticket ID / multi-value attribute',
  `Booking Date` DATE NOT NULL COMMENT 'Booking Date',
  PRIMARY KEY (`Booking ID`, `Exhibition ID`, `Time Slot ID`),
  INDEX `fk_Exhibition Booking_Ticket1_idx` (`Ticket ID` ASC) VISIBLE,
  INDEX `fk_Exhibition Booking_Time Slot1_idx` (`Time Slot ID` ASC) VISIBLE,
  INDEX `fk_Exhibition Booking_Special Exhibition1_idx` (`Exhibition ID` ASC) VISIBLE,
  CONSTRAINT `fk_Exhibition Booking_Ticket1`
    FOREIGN KEY (`Ticket ID`)
    REFERENCES `mydb`.`Ticket` (`Ticket ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Exhibition Booking_Time Slot1`
    FOREIGN KEY (`Time Slot ID`)
    REFERENCES `mydb`.`Time Slot` (`Time Slot ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Exhibition Booking_Special Exhibition1`
    FOREIGN KEY (`Exhibition ID`)
    REFERENCES `mydb`.`Special Exhibition` (`Exhibition ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `mydb`.`Booking Ticket`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Booking Ticket` (
  `Booked ID` INT NOT NULL AUTO_INCREMENT COMMENT 'Booking ID',
  `Exhibition Booking ID` INT NOT NULL,
  PRIMARY KEY (`Booked ID`, `Exhibition Booking ID`),
  INDEX `fk_Booking Ticket_Exhibition Booking1_idx` (`Exhibition Booking ID` ASC) VISIBLE,
  CONSTRAINT `fk_Booking Ticket_Exhibition Booking1`
    FOREIGN KEY (`Exhibition Booking ID`)
    REFERENCES `mydb`.`Exhibition Booking` (`Booking ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `mydb`.`Special Exhibition`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Special Exhibition` (
  `Exhibition ID` INT NOT NULL,
  `Exhibition Title` VARCHAR(45) NOT NULL COMMENT 'Name of exhibition',
  `Opening Date` DATETIME NOT NULL,
  `Closing Date` DATETIME NOT NULL,
  PRIMARY KEY (`Exhibition ID`))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `mydb`.`Time Slot`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Time Slot` (
  `Time Slot ID` INT NOT NULL COMMENT 'Available time slot number',
  `Time Period` TIME NOT NULL COMMENT 'Time slot date is fixed',
  `Time Slot Capacity` INT NOT NULL,
  PRIMARY KEY (`Time Slot ID`))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `mydb`.`Hall Napoleon`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Hall Napoleon` (
  `Exhibiton Log ID` INT NOT NULL AUTO_INCREMENT COMMENT 'Record how many visitor enter the hall Napoleon',
  `Time Slot ID` INT NOT NULL COMMENT 'Record the time slot',
  `Date` DATE NOT NULL COMMENT 'Record the date',
  `Ticket_Ticket ID` INT NULL COMMENT 'Record visited visitor ticket id',
  PRIMARY KEY (`Exhibiton Log ID`),
  INDEX `fk_Hall Napoleon_Ticket1_idx` (`Ticket_Ticket ID` ASC) VISIBLE,
  INDEX `fk_Hall Napoleon_Time Slot1_idx` (`Time Slot ID` ASC) VISIBLE,
  CONSTRAINT `fk_Hall Napoleon_Ticket1`
    FOREIGN KEY (`Ticket_Ticket ID`)
    REFERENCES `mydb`.`Ticket` (`Ticket ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Hall Napoleon_Time Slot1`
    FOREIGN KEY (`Time Slot ID`)
    REFERENCES `mydb`.`Time Slot` (`Time Slot ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `mydb`.`Actual Number of Visitor`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Actual Number of Visitor` (
  `Vistor ID` INT NOT NULL AUTO_INCREMENT,
  `Exhibiton Log ID` INT NOT NULL,
  `Ticket ID` INT NOT NULL,
  PRIMARY KEY (`Vistor ID`, `Exhibiton Log ID`),
  INDEX `fk_Actual Number of Visitor_Hall Napoleon1_idx` (`Exhibiton Log ID` ASC) VISIBLE,
  CONSTRAINT `fk_Actual Number of Visitor_Hall Napoleon1`
    FOREIGN KEY (`Exhibiton Log ID`)
    REFERENCES `mydb`.`Hall Napoleon` (`Exhibiton Log ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

-- Louvre Ops DB: the 2020 design, ported faithfully to SQLite
-- Generated from coursework/1118472.mwb (schema `mydb`, "EER Diagram1").
-- Names, types, NOT NULL flags, keys and foreign keys are unchanged. ENUMs are CHECK
-- constraints. VARCHAR lengths are not enforced by SQLite. Foreign keys that point at
-- part of a composite key are kept as designed; SQLite rejects them when enforcement
-- is on ("foreign key mismatch"), so this script leaves foreign_keys off.

PRAGMA foreign_keys = OFF;

CREATE TABLE "Ticket" (
  "Ticket ID" INTEGER NOT NULL, -- AUTO_INCREMENT (rowid alias); Each visitor has unique ticket ID, unique bar code record ticket ID
  "Invoices_Invoice ID" INTEGER NOT NULL, -- Use as ticket purchase reference
  "Transportation" TEXT CHECK ("Transportation" IN ('Walk', 'Metro', 'Train', 'Bus', 'Taxi', 'Private Vehicle', 'Other')), -- As visitor first time enter the museum, the transportation must be record.
  "Booked ID" INTEGER, -- Use to check special exhibition booking
  "Exhibition Booking ID" INTEGER, -- Special exhibition book ID
  PRIMARY KEY ("Ticket ID"),
  CONSTRAINT "fk_Ticket_Invoices1" FOREIGN KEY ("Invoices_Invoice ID") REFERENCES "Invoices" ("Invoice ID"),
  CONSTRAINT "fk_Ticket_Booking Ticket1" FOREIGN KEY ("Booked ID", "Exhibition Booking ID") REFERENCES "Booking Ticket" ("Booked ID", "Exhibition Booking ID")
);
CREATE INDEX "fk_Ticket_Invoices1_idx" ON "Ticket" ("Invoices_Invoice ID");
CREATE INDEX "fk_Ticket_Booking Ticket1_idx" ON "Ticket" ("Booked ID", "Exhibition Booking ID");

CREATE TABLE "Invoices" (
  "Invoice ID" INTEGER NOT NULL, -- AUTO_INCREMENT (not possible in a composite key); Unique invoice number
  "Product ID" INTEGER NOT NULL, -- Use to reference which product were purchased
  "Payment Method_Payment ID" VARCHAR(45) NOT NULL, -- Use to reference the payment method
  "Purchase Quantity" INTEGER NOT NULL,
  PRIMARY KEY ("Invoice ID", "Product ID"),
  CONSTRAINT "fk_Invoices_Payment Method1" FOREIGN KEY ("Payment Method_Payment ID") REFERENCES "Payment Methods" ("Payment Method ID"),
  CONSTRAINT "fk_Invoices_Product1" FOREIGN KEY ("Product ID") REFERENCES "Product" ("Product ID")
);
CREATE INDEX "fk_Invoices_Payment Method1_idx" ON "Invoices" ("Payment Method_Payment ID");
CREATE INDEX "fk_Invoices_Product1_idx" ON "Invoices" ("Product ID");

CREATE TABLE "Product" (
  "Product ID" INTEGER NOT NULL,
  "Product Name" VARCHAR(20) NOT NULL,
  "Product Price" FLOAT NOT NULL,
  PRIMARY KEY ("Product ID")
);

CREATE TABLE "EFTPOS" (
  "EFTPOS ID" INTEGER NOT NULL, -- AUTO_INCREMENT (rowid alias)
  "EFTPOS Types" TEXT NOT NULL CHECK ("EFTPOS Types" IN ('Credit Card', 'Debit Card', 'Digital Wallet')), -- Record card type
  "Purchase Channel" TEXT NOT NULL CHECK ("Purchase Channel" IN ('Online', 'Offline')), -- Channel: Online / Offline
  "Card Issued Country" VARCHAR(45) NOT NULL,
  "Financial Institution Name" VARCHAR(45) NOT NULL,
  "Issued Bank Branch" VARCHAR(45) NOT NULL,
  "Account Name" VARCHAR(30) NOT NULL,
  "Card Number" INTEGER NOT NULL, -- was INT(8); Card Number only record the first 4 and end 4 numbers.
  "Expiry Date" DATE NOT NULL,
  PRIMARY KEY ("EFTPOS ID")
);

CREATE TABLE "Cash" (
  "Cash Payment ID" INTEGER NOT NULL, -- AUTO_INCREMENT (rowid alias)
  "City" VARCHAR(50) NOT NULL,
  "Country" VARCHAR(50) NOT NULL,
  "First Name" VARCHAR(30) NOT NULL,
  PRIMARY KEY ("Cash Payment ID")
);

CREATE TABLE "Payment Methods" (
  "Payment Method ID" INTEGER NOT NULL, -- AUTO_INCREMENT (not possible in a composite key); Check the payment method
  "EFTPOS_EFTPOS ID" INTEGER, -- EFTPOS is for card and digital wallet payment method
  "Cash_Cash ID" INTEGER, -- All cash payment will consider as a offline trade
  PRIMARY KEY ("Payment Method ID", "EFTPOS_EFTPOS ID", "Cash_Cash ID"),
  CONSTRAINT "fk_Payment Method_EFTPOS1" FOREIGN KEY ("EFTPOS_EFTPOS ID") REFERENCES "EFTPOS" ("EFTPOS ID"),
  CONSTRAINT "fk_Payment Method_Cash1" FOREIGN KEY ("Cash_Cash ID") REFERENCES "Cash" ("Cash Payment ID")
);
CREATE INDEX "fk_Payment Method_EFTPOS1_idx" ON "Payment Methods" ("EFTPOS_EFTPOS ID");
CREATE INDEX "fk_Payment Method_Cash1_idx" ON "Payment Methods" ("Cash_Cash ID");

CREATE TABLE "Entrance" (
  "Gate Name" TEXT NOT NULL CHECK ("Gate Name" IN ('Pyramid', 'Carrousel de Louvre', '99 rue de Rivioli', 'Passage Richelieu', 'Portes de Lion')), -- Identify which gate
  "Ticket ID" INTEGER NOT NULL, -- Record Ticket ID
  "Entrance Date/Time" DATETIME NOT NULL,
  "Business Day" TEXT NOT NULL CHECK ("Business Day" IN ('True', 'False')), -- To justify is working day or not
  PRIMARY KEY ("Gate Name", "Ticket ID"),
  CONSTRAINT "fk_Entrance_Ticket1" FOREIGN KEY ("Ticket ID") REFERENCES "Ticket" ("Ticket ID")
);
CREATE INDEX "fk_Entrance_Ticket1_idx" ON "Entrance" ("Ticket ID");

CREATE TABLE "Wings" (
  "Wings Name" TEXT NOT NULL CHECK ("Wings Name" IN ('Richelieu', 'Denon', 'Sully')), -- Identify which wing
  "Ticket_Ticket ID" INTEGER NOT NULL, -- Record ticket id
  "Visit Time" DATETIME NOT NULL,
  PRIMARY KEY ("Wings Name"),
  CONSTRAINT "fk_Wings_Ticket1" FOREIGN KEY ("Ticket_Ticket ID") REFERENCES "Ticket" ("Ticket ID")
);
CREATE INDEX "fk_Wings_Ticket1_idx" ON "Wings" ("Ticket_Ticket ID");

CREATE TABLE "Hired Audio Guide" (
  "Hired ID" INTEGER NOT NULL, -- AUTO_INCREMENT (not possible in a composite key); Unique hiring id
  "Serial Number" VARCHAR(16) NOT NULL, -- Audio device serial number
  "Language Setting" TEXT NOT NULL CHECK ("Language Setting" IN ('French', 'English', 'Italian', 'Russian', 'Chinese Mandarin', 'Spanish', 'Portuguese', 'Korean', 'Dutch', 'Polish', 'Swedish', 'Norwegian', 'Finnish')),
  "Issued Wing" TEXT NOT NULL CHECK ("Issued Wing" IN ('Richelieu', 'Denon', 'Sully')),
  "Returned Wing" TEXT CHECK ("Returned Wing" IN ('Richelieu', 'Denon', 'Sully')),
  "Issued Time" DATETIME NOT NULL,
  "Return Time" DATETIME,
  "Invoice ID" INTEGER NOT NULL,
  "Product ID" INTEGER NOT NULL,
  "Ticket ID" INTEGER NOT NULL,
  PRIMARY KEY ("Hired ID", "Serial Number"),
  CONSTRAINT "fk_Hiring Audio Guide_Audio Guide Device1" FOREIGN KEY ("Serial Number") REFERENCES "Audio Guide Device" ("Serial Number"),
  CONSTRAINT "fk_Hired Audio Guide_Invoices1" FOREIGN KEY ("Invoice ID", "Product ID") REFERENCES "Invoices" ("Invoice ID", "Product ID"),
  CONSTRAINT "fk_Hired Audio Guide_Ticket1" FOREIGN KEY ("Ticket ID") REFERENCES "Ticket" ("Ticket ID")
);
CREATE INDEX "fk_Hiring Audio Guide_Audio Guide Device1_idx" ON "Hired Audio Guide" ("Serial Number");
CREATE INDEX "fk_Hired Audio Guide_Invoices1_idx" ON "Hired Audio Guide" ("Invoice ID", "Product ID");
CREATE INDEX "fk_Hired Audio Guide_Ticket1_idx" ON "Hired Audio Guide" ("Ticket ID");

CREATE TABLE "Audio Guide Device" (
  "Serial Number" VARCHAR(16) NOT NULL, -- Audio device serial number
  "Activated Date" DATE NOT NULL,
  "Retired Date" DATE NOT NULL,
  PRIMARY KEY ("Serial Number")
);

CREATE TABLE "Exhibition Booking" (
  "Booking ID" INTEGER NOT NULL, -- AUTO_INCREMENT (not possible in a composite key); Booking id
  "Exhibition ID" INTEGER NOT NULL, -- Identify booked exhibition
  "Time Slot ID" INTEGER NOT NULL, -- Identify which time slot has been selected
  "Ticket ID" INTEGER, -- Record ticket ID / multi-value attribute
  "Booking Date" DATE NOT NULL, -- Booking Date
  PRIMARY KEY ("Booking ID", "Exhibition ID", "Time Slot ID"),
  CONSTRAINT "fk_Exhibition Booking_Ticket1" FOREIGN KEY ("Ticket ID") REFERENCES "Ticket" ("Ticket ID"),
  CONSTRAINT "fk_Exhibition Booking_Time Slot1" FOREIGN KEY ("Time Slot ID") REFERENCES "Time Slot" ("Time Slot ID"),
  CONSTRAINT "fk_Exhibition Booking_Special Exhibition1" FOREIGN KEY ("Exhibition ID") REFERENCES "Special Exhibition" ("Exhibition ID")
);
CREATE INDEX "fk_Exhibition Booking_Ticket1_idx" ON "Exhibition Booking" ("Ticket ID");
CREATE INDEX "fk_Exhibition Booking_Time Slot1_idx" ON "Exhibition Booking" ("Time Slot ID");
CREATE INDEX "fk_Exhibition Booking_Special Exhibition1_idx" ON "Exhibition Booking" ("Exhibition ID");

CREATE TABLE "Booking Ticket" (
  "Booked ID" INTEGER NOT NULL, -- AUTO_INCREMENT (not possible in a composite key); Booking ID
  "Exhibition Booking ID" INTEGER NOT NULL,
  PRIMARY KEY ("Booked ID", "Exhibition Booking ID"),
  CONSTRAINT "fk_Booking Ticket_Exhibition Booking1" FOREIGN KEY ("Exhibition Booking ID") REFERENCES "Exhibition Booking" ("Booking ID")
);
CREATE INDEX "fk_Booking Ticket_Exhibition Booking1_idx" ON "Booking Ticket" ("Exhibition Booking ID");

CREATE TABLE "Special Exhibition" (
  "Exhibition ID" INTEGER NOT NULL,
  "Exhibition Title" VARCHAR(45) NOT NULL, -- Name of exhibition
  "Opening Date" DATETIME NOT NULL,
  "Closing Date" DATETIME NOT NULL,
  PRIMARY KEY ("Exhibition ID")
);

CREATE TABLE "Time Slot" (
  "Time Slot ID" INTEGER NOT NULL, -- Available time slot number
  "Time Period" TIME NOT NULL, -- Time slot date is fixed
  "Time Slot Capacity" INTEGER NOT NULL,
  PRIMARY KEY ("Time Slot ID")
);

CREATE TABLE "Hall Napoleon" (
  "Exhibiton Log ID" INTEGER NOT NULL, -- AUTO_INCREMENT (rowid alias); Record how many visitor enter the hall Napoleon
  "Time Slot ID" INTEGER NOT NULL, -- Record the time slot
  "Date" DATE NOT NULL, -- Record the date
  "Ticket_Ticket ID" INTEGER, -- Record visited visitor ticket id
  PRIMARY KEY ("Exhibiton Log ID"),
  CONSTRAINT "fk_Hall Napoleon_Ticket1" FOREIGN KEY ("Ticket_Ticket ID") REFERENCES "Ticket" ("Ticket ID"),
  CONSTRAINT "fk_Hall Napoleon_Time Slot1" FOREIGN KEY ("Time Slot ID") REFERENCES "Time Slot" ("Time Slot ID")
);
CREATE INDEX "fk_Hall Napoleon_Ticket1_idx" ON "Hall Napoleon" ("Ticket_Ticket ID");
CREATE INDEX "fk_Hall Napoleon_Time Slot1_idx" ON "Hall Napoleon" ("Time Slot ID");

CREATE TABLE "Actual Number of Visitor" (
  "Vistor ID" INTEGER NOT NULL, -- AUTO_INCREMENT (not possible in a composite key)
  "Exhibiton Log ID" INTEGER NOT NULL,
  "Ticket ID" INTEGER NOT NULL,
  PRIMARY KEY ("Vistor ID", "Exhibiton Log ID"),
  CONSTRAINT "fk_Actual Number of Visitor_Hall Napoleon1" FOREIGN KEY ("Exhibiton Log ID") REFERENCES "Hall Napoleon" ("Exhibiton Log ID")
);
CREATE INDEX "fk_Actual Number of Visitor_Hall Napoleon1_idx" ON "Actual Number of Visitor" ("Exhibiton Log ID");

-- Louvre Ops DB: refined schema (2026)
--
-- A cleaned-up version of the 2020 INFO20003 design, written for SQLite
-- (3.37+, STRICT tables). It keeps the same business scope and the same
-- entities wherever they worked, and fixes the problems listed on the site's
-- design-review page. Each table notes the 2020 table it replaces.
--
-- Conventions: snake_case names; money in integer euro cents; timestamps as
-- ISO-8601 text in museum local time ('YYYY-MM-DD HH:MM:SS'); dates as
-- 'YYYY-MM-DD'; enumerations as CHECK constraints or lookup tables.

PRAGMA foreign_keys = ON;

-- ---------------------------------------------------------------------------
-- Reference data
-- ---------------------------------------------------------------------------

-- 2020: the ENUM on Entrance."Gate Name" (with one spelling slip, '99 rue de Rivioli';
-- two other names followed the brief's own spelling).
CREATE TABLE entrance (
  entrance_id INTEGER PRIMARY KEY,
  name        TEXT NOT NULL UNIQUE
) STRICT;

-- 2020: the ENUM on Wings."Wings Name" and Hired Audio Guide's wing columns.
CREATE TABLE wing (
  wing_id INTEGER PRIMARY KEY,
  name    TEXT NOT NULL UNIQUE CHECK (name IN ('Richelieu', 'Denon', 'Sully'))
) STRICT;

-- 2020: the 13-value ENUM on Hired Audio Guide."Language Setting".
CREATE TABLE language (
  language_code TEXT PRIMARY KEY CHECK (length(language_code) BETWEEN 2 AND 6),
  name          TEXT NOT NULL UNIQUE
) STRICT;

-- 2020: Product (price was FLOAT).
CREATE TABLE product (
  product_id  INTEGER PRIMARY KEY,
  code        TEXT NOT NULL UNIQUE
              CHECK (code IN ('TICKET_ONLINE', 'TICKET_ON_SITE', 'AUDIO_GUIDE', 'APP')),
  name        TEXT NOT NULL,
  price_cents INTEGER NOT NULL CHECK (price_cents >= 0)
) STRICT;

-- 2020: part of EFTPOS (institution name was one free-text column).
CREATE TABLE financial_institution (
  institution_id INTEGER PRIMARY KEY,
  name           TEXT NOT NULL UNIQUE,
  short_name     TEXT NOT NULL,
  country        TEXT NOT NULL
) STRICT;

-- ---------------------------------------------------------------------------
-- Payments: one supertype row per payment, exactly one subtype row
-- 2020: Payment Methods + EFTPOS + Cash
-- ---------------------------------------------------------------------------

CREATE TABLE payment (
  payment_id   INTEGER PRIMARY KEY,
  method       TEXT NOT NULL
               CHECK (method IN ('credit_card', 'debit_card', 'digital_wallet', 'cash')),
  amount_cents INTEGER NOT NULL CHECK (amount_cents >= 0),
  paid_at      TEXT NOT NULL CHECK (paid_at GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9] [0-9][0-9]:[0-9][0-9]:[0-9][0-9]')
) STRICT;

-- Cards and digital wallets. Only the first and last four digits are kept,
-- as text so leading zeros survive; no CCV column exists.
CREATE TABLE card_payment (
  payment_id     INTEGER PRIMARY KEY REFERENCES payment (payment_id),
  institution_id INTEGER NOT NULL REFERENCES financial_institution (institution_id),
  issuing_branch TEXT NOT NULL,
  issued_country TEXT NOT NULL,
  account_name   TEXT NOT NULL,
  card_first4    TEXT NOT NULL CHECK (length(card_first4) = 4 AND card_first4 NOT GLOB '*[^0-9]*'),
  card_last4     TEXT NOT NULL CHECK (length(card_last4) = 4 AND card_last4 NOT GLOB '*[^0-9]*'),
  expiry_month   INTEGER NOT NULL CHECK (expiry_month BETWEEN 1 AND 12),
  expiry_year    INTEGER NOT NULL CHECK (expiry_year BETWEEN 2000 AND 2099),
  wallet         TEXT CHECK (wallet IN ('Apple Pay', 'Google Pay'))
) STRICT;

-- Cash (euros only): first name, city and country, nothing else.
CREATE TABLE cash_payment (
  payment_id INTEGER PRIMARY KEY REFERENCES payment (payment_id),
  first_name TEXT NOT NULL,
  city       TEXT NOT NULL,
  country    TEXT NOT NULL
) STRICT;

-- A payment has exactly one subtype, and it must match payment.method.
CREATE TRIGGER card_payment_matches_method
BEFORE INSERT ON card_payment
WHEN (SELECT method FROM payment WHERE payment_id = NEW.payment_id) = 'cash'
  OR EXISTS (SELECT 1 FROM cash_payment WHERE payment_id = NEW.payment_id)
  OR ((SELECT method FROM payment WHERE payment_id = NEW.payment_id) = 'digital_wallet') <> (NEW.wallet IS NOT NULL)
BEGIN
  SELECT RAISE(ABORT, 'card details do not match the payment method');
END;

CREATE TRIGGER cash_payment_matches_method
BEFORE INSERT ON cash_payment
WHEN (SELECT method FROM payment WHERE payment_id = NEW.payment_id) <> 'cash'
  OR EXISTS (SELECT 1 FROM card_payment WHERE payment_id = NEW.payment_id)
BEGIN
  SELECT RAISE(ABORT, 'cash details recorded for a non-cash payment');
END;

-- ---------------------------------------------------------------------------
-- Orders and tickets
-- 2020: Invoices (one product per invoice, keyed by invoice + product)
-- ---------------------------------------------------------------------------

-- channel says where the sale happened: the website, a ticket desk at one of
-- the five entrances, an audio-guide desk in a wing, or an app store.
CREATE TABLE purchase_order (
  order_id    INTEGER PRIMARY KEY,
  channel     TEXT NOT NULL
              CHECK (channel IN ('online', 'ticket_desk', 'audio_guide_desk', 'app_store')),
  entrance_id INTEGER REFERENCES entrance (entrance_id),
  wing_id     INTEGER REFERENCES wing (wing_id),
  payment_id  INTEGER NOT NULL UNIQUE REFERENCES payment (payment_id),
  ordered_at  TEXT NOT NULL,
  CHECK ((channel = 'ticket_desk') = (entrance_id IS NOT NULL)),
  CHECK ((channel = 'audio_guide_desk') = (wing_id IS NOT NULL))
) STRICT;

CREATE TRIGGER purchase_order_cash_on_site_only
BEFORE INSERT ON purchase_order
WHEN NEW.channel IN ('online', 'app_store')
 AND (SELECT method FROM payment WHERE payment_id = NEW.payment_id) = 'cash'
BEGIN
  SELECT RAISE(ABORT, 'cash is accepted only at museum desks');
END;

CREATE TABLE order_line (
  order_id         INTEGER NOT NULL REFERENCES purchase_order (order_id),
  product_id       INTEGER NOT NULL REFERENCES product (product_id),
  quantity         INTEGER NOT NULL CHECK (quantity > 0),
  unit_price_cents INTEGER NOT NULL CHECK (unit_price_cents >= 0),
  PRIMARY KEY (order_id, product_id)
) STRICT;

-- 2020: Ticket (the barcode was the ticket ID; transport mode lived here).
CREATE TABLE ticket (
  ticket_id INTEGER PRIMARY KEY,
  barcode   TEXT NOT NULL UNIQUE CHECK (length(barcode) = 13 AND barcode NOT GLOB '*[^0-9]*'),
  order_id  INTEGER NOT NULL REFERENCES purchase_order (order_id)
) STRICT;
CREATE INDEX ticket_order ON ticket (order_id);

-- ---------------------------------------------------------------------------
-- Admission and movement
-- 2020: Entrance (keyed by gate + ticket), Wings (keyed by wing name)
-- ---------------------------------------------------------------------------

-- Every scan at one of the five entrances. The first scan of a ticket opens
-- its day of validity and is the only one that records the transport mode.
CREATE TABLE entry_scan (
  scan_id        INTEGER PRIMARY KEY,
  ticket_id      INTEGER NOT NULL REFERENCES ticket (ticket_id),
  entrance_id    INTEGER NOT NULL REFERENCES entrance (entrance_id),
  scanned_at     TEXT NOT NULL,
  is_first_entry INTEGER NOT NULL CHECK (is_first_entry IN (0, 1)),
  transport_mode TEXT CHECK (transport_mode IN ('Walk', 'Metro', 'Train', 'Bus', 'Taxi', 'Private Vehicle', 'Other')),
  CHECK ((is_first_entry = 1) = (transport_mode IS NOT NULL))
) STRICT;
CREATE UNIQUE INDEX entry_scan_one_first_entry ON entry_scan (ticket_id) WHERE is_first_entry = 1;

-- A ticket is valid from its first scan until closing on the same day.
CREATE TRIGGER entry_scan_same_day
BEFORE INSERT ON entry_scan
WHEN NEW.is_first_entry = 0
 AND date(NEW.scanned_at) IS NOT (
   SELECT date(scanned_at) FROM entry_scan WHERE ticket_id = NEW.ticket_id AND is_first_entry = 1
 )
BEGIN
  SELECT RAISE(ABORT, 'ticket is not valid today: re-entry is allowed only on the day of first entry');
END;

CREATE TABLE wing_scan (
  scan_id    INTEGER PRIMARY KEY,
  ticket_id  INTEGER NOT NULL REFERENCES ticket (ticket_id),
  wing_id    INTEGER NOT NULL REFERENCES wing (wing_id),
  scanned_at TEXT NOT NULL
) STRICT;
CREATE INDEX wing_scan_ticket ON wing_scan (ticket_id);

CREATE TRIGGER wing_scan_valid_ticket
BEFORE INSERT ON wing_scan
WHEN date(NEW.scanned_at) IS NOT (
  SELECT date(scanned_at) FROM entry_scan WHERE ticket_id = NEW.ticket_id AND is_first_entry = 1
)
BEGIN
  SELECT RAISE(ABORT, 'wing scan without a valid admission on that day');
END;

-- ---------------------------------------------------------------------------
-- Audio guides
-- 2020: Audio Guide Device (retired date was NOT NULL), Hired Audio Guide
-- ---------------------------------------------------------------------------

CREATE TABLE audio_guide_device (
  serial_number TEXT PRIMARY KEY
                CHECK (length(serial_number) = 16 AND serial_number NOT GLOB '*[^0-9A-Z]*'),
  activated_on  TEXT NOT NULL,
  retired_on    TEXT,
  CHECK (retired_on IS NULL OR retired_on >= activated_on)
) STRICT;

CREATE TABLE audio_guide_hire (
  hire_id          INTEGER PRIMARY KEY,
  ticket_id        INTEGER NOT NULL REFERENCES ticket (ticket_id),
  serial_number    TEXT NOT NULL REFERENCES audio_guide_device (serial_number),
  order_id         INTEGER NOT NULL UNIQUE REFERENCES purchase_order (order_id),
  language_code    TEXT NOT NULL REFERENCES language (language_code),
  issued_wing_id   INTEGER NOT NULL REFERENCES wing (wing_id),
  issued_at        TEXT NOT NULL,
  returned_wing_id INTEGER REFERENCES wing (wing_id),
  returned_at      TEXT,
  CHECK ((returned_at IS NULL) = (returned_wing_id IS NULL)),
  CHECK (returned_at IS NULL OR returned_at > issued_at)
) STRICT;
CREATE INDEX audio_guide_hire_ticket ON audio_guide_hire (ticket_id);
CREATE INDEX audio_guide_hire_issued ON audio_guide_hire (issued_at);
CREATE INDEX audio_guide_hire_device ON audio_guide_hire (serial_number, issued_at);

-- A device can be out with only one visitor at a time, and only while in service.
CREATE TRIGGER audio_guide_hire_device_available
BEFORE INSERT ON audio_guide_hire
WHEN EXISTS (
  SELECT 1 FROM audio_guide_hire h
  WHERE h.serial_number = NEW.serial_number
    AND h.issued_at < coalesce(NEW.returned_at, '9999')
    AND coalesce(h.returned_at, '9999') > NEW.issued_at
) OR NOT EXISTS (
  SELECT 1 FROM audio_guide_device d
  WHERE d.serial_number = NEW.serial_number
    AND d.activated_on <= date(NEW.issued_at)
    AND (d.retired_on IS NULL OR d.retired_on > date(NEW.issued_at))
)
BEGIN
  SELECT RAISE(ABORT, 'audio guide is already out or not in service');
END;

-- ---------------------------------------------------------------------------
-- Special exhibitions in the Hall Napoleon
-- 2020: Special Exhibition, Time Slot (no date), Exhibition Booking,
--       Booking Ticket, Hall Napoleon, Actual Number of Visitor
-- ---------------------------------------------------------------------------

CREATE TABLE exhibition (
  exhibition_id INTEGER PRIMARY KEY,
  title         TEXT NOT NULL,
  opens_on      TEXT NOT NULL,
  closes_on     TEXT NOT NULL,
  slot_capacity INTEGER NOT NULL DEFAULT 45 CHECK (slot_capacity > 0),
  CHECK (closes_on >= opens_on)
) STRICT;

-- One row per bookable 15-minute slot. Slots are created when the first
-- place in them is booked, with the exhibition's capacity at that time.
CREATE TABLE exhibition_slot (
  slot_id       INTEGER PRIMARY KEY,
  exhibition_id INTEGER NOT NULL REFERENCES exhibition (exhibition_id),
  slot_date     TEXT NOT NULL,
  start_time    TEXT NOT NULL CHECK (start_time GLOB '[0-2][0-9]:[0-5][0-9]' AND substr(start_time, 4, 2) IN ('00', '15', '30', '45')),
  capacity      INTEGER NOT NULL CHECK (capacity > 0),
  UNIQUE (exhibition_id, slot_date, start_time)
) STRICT;

CREATE TRIGGER exhibition_slot_within_run
BEFORE INSERT ON exhibition_slot
WHEN NOT EXISTS (
  SELECT 1 FROM exhibition e
  WHERE e.exhibition_id = NEW.exhibition_id AND NEW.slot_date BETWEEN e.opens_on AND e.closes_on
)
BEGIN
  SELECT RAISE(ABORT, 'slot date is outside the exhibition run');
END;

CREATE TABLE exhibition_booking (
  booking_id INTEGER PRIMARY KEY,
  slot_id    INTEGER NOT NULL REFERENCES exhibition_slot (slot_id),
  ticket_id  INTEGER NOT NULL REFERENCES ticket (ticket_id),
  booked_at  TEXT NOT NULL,
  UNIQUE (slot_id, ticket_id)
) STRICT;
CREATE INDEX exhibition_booking_ticket ON exhibition_booking (ticket_id);

-- No more than the slot's capacity can ever be booked.
CREATE TRIGGER exhibition_booking_capacity
BEFORE INSERT ON exhibition_booking
WHEN (SELECT count(*) FROM exhibition_booking WHERE slot_id = NEW.slot_id)
  >= (SELECT capacity FROM exhibition_slot WHERE slot_id = NEW.slot_id)
BEGIN
  SELECT RAISE(ABORT, 'this 15-minute slot is fully booked');
END;

-- Scans at the Hall Napoleon door; one admission per booking.
CREATE TABLE hall_napoleon_scan (
  scan_id    INTEGER PRIMARY KEY,
  booking_id INTEGER NOT NULL UNIQUE REFERENCES exhibition_booking (booking_id),
  scanned_at TEXT NOT NULL
) STRICT;

-- Booked, admitted and remaining places for every slot (replaces the 2020
-- "Actual Number of Visitor" table, which stored a count that can be derived).
CREATE VIEW slot_availability AS
SELECT
  s.slot_id,
  s.exhibition_id,
  e.title,
  s.slot_date,
  s.start_time,
  s.capacity,
  count(b.booking_id)              AS booked,
  count(h.scan_id)                 AS admitted,
  s.capacity - count(b.booking_id) AS remaining
FROM exhibition_slot s
JOIN exhibition e ON e.exhibition_id = s.exhibition_id
LEFT JOIN exhibition_booking b ON b.slot_id = s.slot_id
LEFT JOIN hall_napoleon_scan h ON h.booking_id = b.booking_id
GROUP BY s.slot_id;

-- One row per admitted ticket: the day's first entry, for convenience.
CREATE VIEW museum_visit AS
SELECT
  e.ticket_id,
  date(e.scanned_at) AS visit_date,
  e.scanned_at       AS first_entry_at,
  e.entrance_id,
  e.transport_mode
FROM entry_scan e
WHERE e.is_first_entry = 1;