online transaction processing
online analytical processing
extract, transform, load
extract, load, transform
database design technique
divides tables into smaller tables and connects them via relationships
the goal is to reduce redundancy and increase data integrity
saves space by eliminating data redundancy
queries are longer and more complex
enforces data consistency
safer updating, removing and inserting
easier to redesign by extending
OLTP preferred method, not OLAP
#adding foreign key to a star table
ALTER TABLE main_table ADD CONSTRAINT new_normalized_table
FOREIGN KEY (new_normalized_key) REFERENCES star_table_name (star_table_key);
#Create a new table with a distinct column
CREATE TABLE new_normalized_table (
new_column DATATYPE NOT NULL
);
#Insert data from star table
INSERT INTO new_normalized_table
SELECT DISTINCT star_table_column FROM star_table_name;
#Add a primary key
ALTER TABLE new_normalized_table ADD COLUMN new_primary_key SERIAL PRIMARY KEY;
-deletion anomoly - deletion of record unintentionally causes loss of data
CREATE VIEW view_name AS
SELECT col1. col2
FROM table_name
WHERE condition;
CREATE MATERIALIZED VIEW mv_name AS
SELECT col1. col2
FROM table_name
WHERE condition;
REFRESH MATERIALIZED VIEW mv_name;
DROP VIEW view_name [CASCADE | RESTRICT]
CREATE OR REPLACE VIEW view_name AS new_query
SELECT * FROM view_name
#postgreSQL - will need to find reletive command per database type
SELECT * FROM INFORMATION_SCHEMA.views
#exclude system views
WHERE table_schema NOT IN ('pg_catalog', 'information_schema');
grant access
GRANT (type of privilege)
#type of privilege: SELECT, INSERT, UPDATE, DELETE
ON object #table, view, schema
TO role FROM role #user or group of users
revoke access
REVOKE (type of privilege)
#type of privileges: SELECT, INSERT, UPDATE, DELETE
FROM object #table, view, schema
TO role FROM role #user or group of users
avoid modifying data through views, create new views and delete old views
CREATE ROLE user_name or group_name
WITH PASSWORD 'password_string'
VALID UNTIL 'YYYY-MM-DD';
CREATE ROLE admin CREATEDB;
ALTER ROLE admin CREATEROLE;
GRANT (type of privilege)
#type of privilege: SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, CREATE, TEMPORARY, EXECUTE, USAGE
ON object #table, view, schema
TO role FROM role #user or group of
REVOKE (type of privilege)
#type of privilege: SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, CREATE, TEMPORARY, EXECUTE, USAGE
FROM object #table, view, schema
TO role FROM role #user or group of users
GRANT group_name TO user_name;
REVOKE group_name FROM user_name;
split tables over the rows
# Create a new table called table_partitioned
CREATE TABLE table_partitioned (
primary_key INT,
col1 TEXT NOT NULL,
col2 TEXT
)
PARTITION BY LIST (col2);
# Create the partitions for 2019, 2018, and 2017
CREATE TABLE table_2019
PARTITION OF table_partitioned FOR VALUES IN ('2019');
CREATE TABLE table_2018
PARTITION OF table_partitioned FOR VALUES IN ('2018');
CREATE TABLE table_2017
PARTITION OF table_partitioned FOR VALUES IN ('2017');
# Insert the data into table_partitioned
INSERT INTO table_partitioned
SELECT primary_key, col1, col2 FROM table;
# View film_partitioned
SELECT * FROM table_partitioned;