MySQL

RDBMS (Relational Database Management System)
- Software to create and manage databases
  - Manage large amounts of information
  - Handles security
  - Backups
  - Importing/exporting data
  - Concurrency
  - Interact with software applications
- Websites interact with DBMS to CRUD information
- MySQL, Oracle, PostgreSQL, MariaDB, etc
Databases
- Any collection of related information
- Types
  - Relational DBs (SQL)
    - Organize data into one or more tables
      - Each table has columns and rows
      - A unique key identifies each row
  - Non-Relational (NoSQL)
    - Organize data in anything but a table
      - Key-value stores
      - Documents
      - Graphs
      - Flexible tables
Tables & Keys
- A primary key is an attribute that uniquely identifies the row in the table
  - Surrogate key is a PK that has no mapping to the real word
  - Natural key is a PK that has a mapping to the real word (e.g: SSN)
- A foreign key is an attribute in the table that links to another table in the DB
  - Stores the primary key of a row in another table in the DB
  - A table can have more than one primary key
- A composite key is a key that needs two or more attributes
  - Only together can they uniquely identify a row
  - Values can be repeated, but a combination can only appear once
SQL
- Standardized language for interacting with RDBMS
- Used to perform CRUD operations and admin tasks
- Used to define tables and structures
- SQL code used on one RDBMS is not always portable
- A hybrid language that combines 4 types of languages
  - Data Query Language (DQL)
    - Used to query to DB for information
    - Get information that is already stored
  - Data Definition Language (DDL)
    - Used for defining DB schemas
  - Data Control Language (DCL)
    - Used for controlling access to the data in the DB
    - User & permissions management
  - Data Manipulation Language (DML)
    - Used for inserting, updating, and deleting data from the DB
- SQL data types
  - int, decimal(m, n), varchar(1), blob (Binary Large Object, stores large binary data, images and videos), date, timestamp
- Constraints
  - not null, unique, default, auto_increment, primary key (not null & unique)
- Comparison operations
  - =, <>, <, >, >=, <=, AND, OR, IN
- DQL
  - select-from-(where)-order by-limit, union, joins
- DDL
  - create table, drop table
- DML
  - alter table, add column, insert into, update-set-(where), delete from-(where)
- Queries
  - Requests made to the DBMS for specific info from the DB
  - Can be nested
- Joins
  - Combines rows from different tables into a single result based on related columns
  - 4 types: inner, left, right, full outer join
- On delete: handles when a row with a foreign key is deleted
  - Set null: the affected row will have the related values set to NULL
  - Cascade: delete the entire row
- Triggers
  - A block of SQL that defines a certain action that should happen when a certain operation gets performed on the DB
Entity Relationship (ER) Diagrams
- DB schema
  - All the tables & attributes & relationships
- ER diagram
  - Middleman between data storage requirements (business requirements) and the actual DB schema
- Entity
  - An object we want to model & store information about
- Attributes
  - Specific pieces of information about an entity
- Primary key
  - An attribute(s) that unique identify an entry in the DB table
- Composite attribute
  - An attribute that can be broken up into sub-attributes
- Multi-valued Attribute
  - An attribute that can have more than one value
- Derived Attribute
  - An attribute that can be derived from other attributes
  - Can also not track this in the actual DB
- Multiple Entities
  - More than one entity can be defined in the diagram
- Relationships
  - Defines a relationship between two entities
- Total Participation
  - All members must participate in the relationship
- Relationship Attribute
  - An attribute about the relationship
- Relationship Cardinality
  - The number of instances of an entity from a relation that can be associated with the relation
  - One-to-one, one-to-many, many-to-many
- Weak Entity
  - An entity that cannot be uniquely identified by its attributes alone
- Identifying relationship - A relationship that serves to uniquely identify the weak entity

Weak identity

Data requirements (describes all of the different data and the relationships between them) → ER diagram → DB schema
Converting ER diagram to DB schema

Mapping of Regular Entity Types a. For each regular entity create a relation (table) that includes all the simple attributes of that entity
Mapping of Weak Entity Types a. For each weak entity type create a relation (table) that includes all simple attributes of the weak entity b. The primary key of the new relation should be the partial key of the weak entity plus the primary key of its owner
Mapping of Binary 1:1 Relationship Types a. Include one side of the relationship as a foreign key in the other b. Favor total participation
Mapping of Binary 1:N Relationship Types a. Include the 1 side’s primary key as a foreign key on the N side relation (table)
Mapping of Binary M:N Relationship Types a. Create a new relation (table) whose primary key is a combination of both entities’ primary keys b. Also include any relationship attributes