DROP TABLE sales;
DROP TABLE salesperson;
DROP TABLE times;
DROP TABLE shop;
DROP TABLE city;
DROP TABLE region;

CREATE TABLE region (
  region_id NUMBER(3) NOT NULL PRIMARY KEY,
  name VARCHAR2(40)
);

CREATE TABLE city (
  city_id NUMBER(4) NOT NULL PRIMARY KEY,
  name VARCHAR2(30),
  region_id NUMBER(3),
  city_description VARCHAR2(50)
);

CREATE TABLE shop (
  shop_id NUMBER(5) NOT NULL PRIMARY KEY,
  name VARCHAR2(40),
  address VARCHAR2(40),
  city_id NUMBER(4),
  shop_description VARCHAR2(50)
);

CREATE TABLE times (
  time_id NUMBER(5) NOT NULL PRIMARY KEY,
  day_name VARCHAR2(15),
  day_no_in_week NUMBER(1),
  day_no_in_month NUMBER(2),
  week_no NUMBER(2),
  month_no NUMBER(2),
  month_name VARCHAR2(20),
  quarter_no NUMBER(1),
  quarter_description VARCHAR2(20),
  year NUMBER(4),
  year_description VARCHAR2(50)
);

CREATE TABLE salesperson (
  salespers_id NUMBER(5) NOT NULL PRIMARY KEY,
  first_name VARCHAR2(20),
  last_name VARCHAR2(20)
);

CREATE TABLE sales (
  sale_id NUMBER(10) NOT NULL PRIMARY KEY,
  nb_of_units NUMBER(4),
  retail_price NUMBER(6,2),
  time_id NUMBER(5) REFERENCES times(time_id),
  shop_id NUMBER(5) REFERENCES shop(shop_id),
  salespers_id NUMBER(5) REFERENCES salesperson(salespers_id)
);