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