structure.sql

CREATE DATABASE IF NOT EXISTS db;
CREATE SCHEMA if not exists sale;

-- -----------------------------------------------------
-- Table `sale`.`manufacturers`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS db.sale.manufacturers (
  manufacturer_id INT NOT NULL,
  name CHAR(30) NULL DEFAULT NULL,
  country CHAR(30) NULL DEFAULT NULL,
  PRIMARY KEY (manufacturer_id)
 )
;

CREATE INDEX manufacturer_index ON db.sale.manufacturers (name ASC);


-- -----------------------------------------------------
-- Table `sale`.`articles`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS db.sale.articles (
  article_id INT NOT NULL,
  manufacturer_id INT NOT NULL,
  name CHAR(50) NULL DEFAULT NULL,
  price DECIMAL(7,2) NULL DEFAULT NULL,
  PRIMARY KEY (article_id)
 ,
  CONSTRAINT fk1
    FOREIGN KEY (manufacturer_id)
    REFERENCES db.sale.manufacturers (manufacturer_id)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
;

CREATE INDEX article_index ON db.sale.articles (name ASC);
CREATE INDEX fk_article_manufacturer1_idx ON db.sale.articles (manufacturer_id ASC);


-- -----------------------------------------------------
-- Table `sale`.`person`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS db.sale.persons (
  person_id INT NOT NULL,
  lastname CHAR(20) NULL DEFAULT NULL,
  firstname CHAR(20) NULL DEFAULT NULL,
  street CHAR(30) NULL DEFAULT NULL,
  city CHAR(30) NULL DEFAULT NULL,
  postal_code INT NULL DEFAULT NULL,
  country CHAR(20) NULL DEFAULT NULL,
  birthdate DATE NULL DEFAULT NULL,
  PRIMARY KEY (person_id)
 )
;

CREATE INDEX person_index ON db.sale.persons (lastname ASC);


-- -----------------------------------------------------
-- Table `sale`.`orders`
-- -----------------------------------------------------
CREATE SEQUENCE db.sale.orders_seq;

CREATE TABLE IF NOT EXISTS db.sale.orders (
  order_id INT NOT NULL DEFAULT NEXTVAL ('db.sale.orders_seq'),
  article_id INT NOT NULL,
  customer_id INT NOT NULL,
  article_amount INT NULL DEFAULT NULL,
  total_value DECIMAL(7,2) NULL DEFAULT NULL,
  PRIMARY KEY (order_id),
  CONSTRAINT fk2
    FOREIGN KEY (article_id)
    REFERENCES db.sale.articles (article_id)
    ON DELETE CASCADE,
  CONSTRAINT fk3
    FOREIGN KEY (customer_id)
    REFERENCES db.sale.persons (person_id)
    ON DELETE CASCADE)
;