Introduction to Databases
Database: An organized collection of data
Relational Database: A database with tables that connect to and reference each other as needed
Entity Relationship Diagram (ERD): Shows the relationship between tables in a relational database
Databases are important for organizing data in a way that makes the data easy to access and update
Database Schema: Shows the database structure, including tables, columns, types, relationships, constraints, etc
Database - set of relations
Relation - set of rows
Schema of relation - name and attributes
Attributes - name and domain
Key - smallest set of attributes so that no two tuples have the same key attribute values; all relations must have a key
Keys must be unique values and help us understand what the table is about
Foreign Key - Column or set of columns in one table that references the primary key of another table, creating a relationship between those tables
Super Key - Key + additional columns if needed; every key is a super key.
Specification
Select tables and name
Select columns for each table; give domain for each column
Specify keys for each table
Specify formal keys
Database Management System (DBMS): Software that enables control of databases
Structures data
Stores massive data
Reads, writes, updates data efficiently
Makes data durable
Allows concurrent changes to data
Terminology
Database administrator / DBA
Data Definition Language / DDA
Data Manipulation Language / DML
Table Instance: Current contents of a table or view
Size of Table
Degree (arity): the number of columns
Cardinality: the number of rows
Commands
DDL Commands
CREATE
RENAME
TRUNCATE
ALTER
DROP
DML Commands
SELECT
INSERT
DELETE
UPDATE
Normal Forms
Anomalies
Update Anomalies: Inconsistencies from changing data
Insertion Anomalies: Inconsistencies from adding data
Deletion Anomalies: Inconsistencies from deleting data
Relational databases help prevent all of these anomalies
Normalizing Data
Data Normalization: Whenever you can use the value in a column or columns to predict the value of another column or columns, that whole set of columns should be its own table
Helps us to reduce redundancy and increase integrity
UNF (Unnormalized Form)
1NF (First Normal Form)
Values are atomic; Find all columns uniquely possible from one column and expand to those columns
Primary Key
Order of rows or columns must not be relevant to interpret data
2NF
1NF + Values do not depend on subsets of candidate key
3NF
2NF + All values that are not part of the key depend only on the key
If x implies y, which implies z, that is a problem
TO TIDY DATA: Each variable forms a column
BCNF
3NF + No functional dependencies
4NF
No multivalued dependencies
5NF
Table cannot be losslessly decomposed into smaller tables
6NF
Conforms to all lower normal forms + each table is at most a primary key and one column (about speed rather than data integrity)
Functions
melt() transforms a dataframe by taking columns and turning them into rows
pivot_table() turns unique values of a column into separate columnn headers and summarizes metrics