create a simple database with some tables

for the purpose and example, we will be creating a database-schema.

during the exercise of this blog, we will be using the Account Status table.

1. start the admin-console of PostgreSQL by executing C:\tools\PostgreSQL\9.5\bin\pgAdmin3.exe
2. create a new role easy and make him superuser.
3. create a new schema easy
4. when we have created the user and schema, we will be executing the sql-script to create the tables and fill account-status table

    -- create account table
    CREATE TABLE ACCOUNT(
      ID SERIAL
    , STATUS INTEGER
    , TYPE INTEGER
    , PASSWORD INTEGER
    , DATE_CREATED DATE
    );

    CREATE TABLE CUSTOMER(
      ID SERIAL
    , ACCOUNT_ID INTEGER
    , INITIALS VARCHAR(10)
    , FIRST_NAME VARCHAR(50)
    , LAST_NAME VARCHAR(50)
    , PREPOSITION VARCHAR(10)
    , EMAIL VARCHAR(50)
    , PHONE VARCHAR(15)
    );

    CREATE TABLE SUPPLIER(
      ID SERIAL
    , ACCOUNT_ID INTEGER
    , SUPP_NAME VARCHAR(50)
    , SUPP_DESC TEXT
    , EMAIL VARCHAR(50)
    , PHONE VARCHAR(15)
    );

    -- create password table
    CREATE TABLE PASSWORD(
      ID SERIAL
    , PWD VARCHAR(50)
    , USERNAME VARCHAR(20)
    , DATE_CREATED DATE
    , DATE_EXPIRED DATE
    , ACC_ID INTEGER
    );

    -- create address table
    CREATE TABLE ADDRESS(
      ID SERIAL
    , ADDRESS_USER INTEGER
    , STREET VARCHAR(20)
    , STREET_NR INTEGER
    , STREET_ADD VARCHAR(10)
    , ZIPCODE VARCHAR(6)
    , CITY VARCHAR(20)
    );

    -- create shopping cart table
    CREATE TABLE SHOPPING_CART(
      ID SERIAL
    , ACCOUNT_ID INTEGER
    , DATE_CREATED DATE
    , DATE_EXPIRED DATE
    );

    -- create orderline table
    CREATE TABLE SHOPPING_ORDER_LINE(
      ID SERIAL
    , SHOPPING_CART_ID INTEGER
    , SHOPPING_ORDER_ID INTEGER
    , PRODUCT_ID INTEGER
    , QUANTITY INTEGER
    );

    -- create product table
    CREATE TABLE PRODUCT(
      ID SERIAL
    , CATEGORY_ID INTEGER
    , NAME VARCHAR(20)
    , PROD_DESC TEXT
    , IMG BYTEA
    );

    -- create category table
    CREATE TABLE CATEGORY(
      ID SERIAL
    , NAME VARCHAR(15)
    , CAT_DESC TEXT
    );

    -- create table stock
    CREATE TABLE STOCK(
      ID SERIAL
    , SUPPLIER_ID INTEGER
    , PRODUCT_ID INTEGER
    , MIN_LEVEL INTEGER
    );


    -- create order table
    CREATE TABLE SHOPPING_ORDER(
      ID SERIAL
    , ACCOUNT_ID INTEGER
    , ORDER_NR INTEGER
    , ORDER_DATE DATE
    , SHIPPING_DATE DATE
    , BILLING_ADDRESS INTEGER
    , SHIPPING_ADDRESS INTEGER
    , STATUS INTEGER
    );

    CREATE TABLE ORDER_STATUS(
      ID SERIAL
    , NAME VARCHAR(10)
    );

    CREATE TABLE ACCOUNT_TYPE(
      ID SERIAL
    , NAME VARCHAR(10)
    );

    CREATE TABLE ACCOUNT_STATUS(
      ID SERIAL
    , NAME VARCHAR(10)
    );


    -- add primary key constraints
    ALTER TABLE PASSWORD
    ADD PRIMARY KEY (ID);

    ALTER TABLE ACCOUNT
    ADD PRIMARY KEY (ID);

    ALTER TABLE ACCOUNT_TYPE
    ADD PRIMARY KEY (ID);

    ALTER TABLE ACCOUNT_STATUS
    ADD PRIMARY KEY (ID);

    ALTER TABLE CUSTOMER
    ADD PRIMARY KEY (ID);

    ALTER TABLE SUPPLIER
    ADD PRIMARY KEY (ID);

    ALTER TABLE ADDRESS
    ADD PRIMARY KEY (ID);

    ALTER TABLE SHOPPING_ORDER
    ADD PRIMARY KEY (ID);

    ALTER TABLE ORDER_STATUS
    ADD PRIMARY KEY (ID);

    ALTER TABLE SHOPPING_CART
    ADD PRIMARY KEY (ID);

    ALTER TABLE SHOPPING_ORDER_LINE
    ADD PRIMARY KEY (ID);

    ALTER TABLE PRODUCT
    ADD PRIMARY KEY (ID);

    ALTER TABLE CATEGORY
    ADD PRIMARY KEY (ID);

    ALTER TABLE STOCK
    ADD PRIMARY KEY (ID);

    -- create a relation between ACCOUNT and ACCOUNT_TYPE table
    ALTER TABLE ACCOUNT
    ADD CONSTRAINT FK_ACC_TYP
    FOREIGN KEY(TYPE)
    REFERENCES ACCOUNT_TYPE(ID);

    -- create a relation between ACCOUNT and ACCOUNT_STATUS table
    ALTER TABLE ACCOUNT
    ADD CONSTRAINT FK_ACC_STS
    FOREIGN KEY(STATUS)
    REFERENCES ACCOUNT_STATUS(ID);

    -- create a relation between ACCOUNT and PASSWORD table
    ALTER TABLE PASSWORD
    ADD CONSTRAINT FK_ACC_PWD
    FOREIGN KEY(ACC_ID)
    REFERENCES ACCOUNT(ID);

    -- create a relation between ACCOUNT and ORDER table
    ALTER TABLE SHOPPING_ORDER
    ADD CONSTRAINT FK_ACC_ORD
    FOREIGN KEY(ACCOUNT_ID)
    REFERENCES ACCOUNT(ID);

    -- create a relation between ACCOUNT and CUSTOMER table
    ALTER TABLE CUSTOMER
    ADD CONSTRAINT FK_ACC_CUST
    FOREIGN KEY(ACCOUNT_ID)
    REFERENCES ACCOUNT(ID);

    -- create a relation between ACCOUNT and SUPPLIER table
    ALTER TABLE SUPPLIER
    ADD CONSTRAINT FK_ACC_SUPP
    FOREIGN KEY(ACCOUNT_ID)
    REFERENCES ACCOUNT(ID);

    -- create a relation between ADDRESS and ORDER table
    ALTER TABLE SHOPPING_ORDER
    ADD CONSTRAINT FK_SHIP_ADR_ORD
    FOREIGN KEY(SHIPPING_ADDRESS)
    REFERENCES ADDRESS(ID);

    -- create a relation between SHOPPING_ORDER_LINE and SHOPPING_CART table
    ALTER TABLE SHOPPING_ORDER_LINE
    ADD CONSTRAINT FK_SOL_CART
    FOREIGN KEY(SHOPPING_CART_ID)
    REFERENCES SHOPPING_CART(ID);

    -- create a relation between SHOPPING_ORDER_LINE and SHOPPING_ORDER table
    ALTER TABLE SHOPPING_ORDER_LINE
    ADD CONSTRAINT FK_SOL_ORD
    FOREIGN KEY(SHOPPING_ORDER_ID)
    REFERENCES SHOPPING_ORDER(ID);

    -- create a relation between SHOPPING_ORDER_LINE and PRODUCT table
    ALTER TABLE SHOPPING_ORDER_LINE
    ADD CONSTRAINT FK_SOL_PRD
    FOREIGN KEY(PRODUCT_ID)
    REFERENCES PRODUCT(ID);

    -- create a relation between PRODUCT and CATEGORY table
    ALTER TABLE PRODUCT
    ADD CONSTRAINT FK_PRD_CAT
    FOREIGN KEY(CATEGORY_ID)
    REFERENCES CATEGORY(ID);

    -- create a relation between STOCK and SUPPLIER table
    ALTER TABLE STOCK
    ADD CONSTRAINT FK_STK_SUP
    FOREIGN KEY(SUPPLIER_ID)
    REFERENCES SUPPLIER(ID);

    -- create a relation between STOCK and PRODUCT table
    ALTER TABLE STOCK
    ADD CONSTRAINT FK_STK_PRD
    FOREIGN KEY(PRODUCT_ID)
    REFERENCES PRODUCT(ID);


    -- create a relation between ADDRESS and ORDER table
    ALTER TABLE SHOPPING_ORDER
    ADD CONSTRAINT FK_BILL_ADR_ORD
    FOREIGN KEY(BILLING_ADDRESS)
    REFERENCES ADDRESS(ID);

    -- create a relation between ADDRESS and ORDER table
    ALTER TABLE SHOPPING_ORDER
    ADD CONSTRAINT FK_ORD_STS
    FOREIGN KEY(STATUS)
    REFERENCES ORDER_STATUS(ID);

    -- insert data in the list tables
    INSERT INTO ORDER_STATUS 
      (ID, NAME)
    VALUES
      (1, 'NEW');
    INSERT INTO ORDER_STATUS 
      (ID, NAME)
    VALUES
      (2, 'HOLD');
    INSERT INTO ORDER_STATUS 
      (ID, NAME)
    VALUES
      (3, 'SHIPPED');
    INSERT INTO ORDER_STATUS 
      (ID, NAME)
    VALUES
      (4, 'DELIVERED');
    INSERT INTO ORDER_STATUS 
      (ID, NAME)
    VALUES
      (5, 'CLOSED');

    INSERT INTO ACCOUNT_TYPE 
      (ID, NAME)
    VALUES
      (1, 'CUSTOMER');
    INSERT INTO ACCOUNT_TYPE 
      (ID, NAME)
    VALUES
      (2, 'SUPPLIER');

    INSERT INTO ACCOUNT_STATUS
      (ID, NAME)
    VALUES
      (1, 'NEW');
    INSERT INTO ACCOUNT_STATUS
      (ID, NAME)
    VALUES
      (2, 'ACTIVE');
    INSERT INTO ACCOUNT_STATUS
      (ID, NAME)
    VALUES
      (3, 'BLOCKED');
    INSERT INTO ACCOUNT_STATUS
      (ID, NAME)
    VALUES
      (4, 'BANNED');