-- Denormalization

-- DeNormalized Form (OLAP)

DROP TABLE all_details_tb;

CREATE TABLE all_details_tb
AS SELECT * FROM employees;

ALTER TABLE all_details_tb
ADD ( department_name VARCHAR2(30)
    , dmanager_id      NUMBER(6)
    , location_id     NUMBER(4));
	
ALTER TABLE all_details_tb
ADD ( street_address  VARCHAR2(40)
    , postal_code     VARCHAR2(12)
    , city            VARCHAR2(30)
    , state_province  VARCHAR2(25)
    , country_id      CHAR(2));

	 
UPDATE all_details_tb a
   SET a.department_name = (SELECT d.department_name
                              FROM departments d
                             WHERE department_id = a.department_id),
       a.dmanager_id     = (SELECT d.manager_id
                              FROM departments d
                             WHERE department_id = a.department_id),
       a.location_id     = (SELECT d.location_id
                              FROM departments d
                             WHERE department_id = a.department_id);
							 
UPDATE all_details_tb a
   SET street_address = (SELECT l.street_address
                           FROM locations l
                          WHERE location_id = a.location_id),
       postal_code    = (SELECT l.postal_code
                           FROM locations l
                          WHERE location_id = a.location_id),
       city           = (SELECT l.city
                           FROM locations l
                          WHERE location_id = a.location_id),
       state_province = (SELECT l.state_province
                           FROM locations l
                          WHERE location_id = a.location_id),
       country_id     = (SELECT l.country_id
                           FROM locations l
                          WHERE location_id = a.location_id);
						  
Pros of Denormalization:

    Retrieving data is faster since we do fewer joins
    Queries to retrieve can be simpler(and therefore less likely to have bugs), 
    since we need to look at fewer tables.

Cons of Denormalization:

    Updates and inserts are more expensive.
    Denormalization can make update and insert code harder to write.
    Data may be inconsistent.
    Data redundancy necessitates more storage.