CiZZrzTE

1-- It's a backup of https://github.com/mrin9/northwind/blob/master/schema.sql
2/**
3 Author: Mrin 
4 Model : NorthWind
5**/
6
7DROP SCHEMA IF EXISTS northwind;
8
9CREATE SCHEMA northwind;
10USE northwind;
11
12/* Table: customers */
13CREATE TABLE customers (
14  id              INT NOT NULL,
15  last_name       VARCHAR(50) ,
16  first_name      VARCHAR(50) ,
17  email           VARCHAR(50) ,
18  company         VARCHAR(50) ,
19  phone           VARCHAR(25) ,
20  address1        VARCHAR(150),
21  address2        VARCHAR(150),
22  city            VARCHAR(50) ,
23  state           VARCHAR(50) ,
24  postal_code     VARCHAR(15) ,
25  country         VARCHAR(50) ,
26  PRIMARY KEY (id)
27);
28
29/* Table: employees */
30CREATE TABLE employees (
31  id              INT NOT NULL,
32  last_name       VARCHAR(50) ,
33  first_name      VARCHAR(50) ,
34  email           VARCHAR(50) ,
35  avatar          VARCHAR(250) ,
36  job_title       VARCHAR(50) ,
37  department      VARCHAR(50) ,
38  manager_id      INT ,
39  phone           VARCHAR(25) ,
40  address1        VARCHAR(150),
41  address2        VARCHAR(150),
42  city            VARCHAR(50) ,
43  state           VARCHAR(50) ,
44  postal_code     VARCHAR(15) ,
45  country         VARCHAR(50) ,
46  PRIMARY KEY (id)
47);
48
49/* Table: orders */
50CREATE TABLE orders (
51  id              INT NOT NULL,
52  employee_id     INT ,
53  customer_id     INT ,
54  order_date      DATETIME ,
55  shipped_date    DATETIME ,
56  ship_name       VARCHAR(50) ,
57  ship_address1   VARCHAR(150) ,
58  ship_address2   VARCHAR(150) ,
59  ship_city       VARCHAR(50) ,
60  ship_state      VARCHAR(50) ,
61  ship_postal_code VARCHAR(50) ,
62  ship_country    VARCHAR(50) ,
63  shipping_fee    DECIMAL(19,4) NULL DEFAULT '0.0000',
64  payment_type    VARCHAR(50) ,
65  paid_date       DATETIME ,
66  order_status    VARCHAR(25),
67  PRIMARY KEY (id)
68);
69
70/* Table: order_details */
71CREATE TABLE order_details (
72  order_id            INT NOT NULL,
73  product_id          INT ,
74  quantity            DECIMAL(18,4) NOT NULL DEFAULT '0.0000',
75  unit_price          DECIMAL(19,4) NULL DEFAULT '0.0000',
76  discount            DOUBLE NOT NULL DEFAULT '0',
77  order_detail_status VARCHAR(25),
78  date_allocated      DATETIME ,
79  PRIMARY KEY (order_id, product_id)
80);
81
82/* Table: products */
83CREATE TABLE products (
84  id              INT NOT NULL,
85  product_code    VARCHAR(25) ,
86  product_name    VARCHAR(50) ,
87  description     VARCHAR(250),
88  standard_cost   DECIMAL(19,4) NULL DEFAULT '0.0000',
89  list_price      DECIMAL(19,4) NOT NULL DEFAULT '0.0000',
90  target_level    INT ,
91  reorder_level   INT ,
92  minimum_reorder_quantity INT ,
93  quantity_per_unit VARCHAR(50) ,
94  discontinued    TINYINT NOT NULL DEFAULT '0',
95  category        VARCHAR(50),
96  PRIMARY KEY (id)
97);
98
99
100/* Foreign Key: orders */
101ALTER TABLE orders ADD CONSTRAINT fk_orders__customers FOREIGN KEY (customer_id) REFERENCES customers(id);
102ALTER TABLE orders ADD CONSTRAINT fk_orders__employees FOREIGN KEY (employee_id) REFERENCES employees(id);
103/* Foreign Key:  order_details */
104ALTER TABLE order_details ADD CONSTRAINT fk_order_details__orders      FOREIGN KEY (order_id) REFERENCES orders(id);
105ALTER TABLE order_details ADD CONSTRAINT fk_order_details__products FOREIGN KEY (product_id) REFERENCES products(id);