Database and SQL Fundamentals
Database Basics
A database is an ordered collection of related data accessed by a computer system.
A Database Management System (DBMS) provides the services necessary for users or software to interact with the data.
The DBMS provides the ability to read, write, update, and delete data, structure data, and set user permissions.
MySQL
MySQL is a DBMS for relational databases, which store data in related rows and columns within tables.
It uses SQL (Structured Query Language) to interact with the data.
SQL statements can display, erase, add data, or change the database structure.
DBMSs have tools to manage the administration of the database instance, such as monitoring its performance
SQL
SQL is a declarative language where programmers state the desired end result, and the database engine determines the steps.
SQL queries are succinct and human-readable.
Basic SQL query structure:
SELECT specifies what data to select (e.g.,
*for everything).FROM indicates the table to select from.
WHERE filters the results based on criteria.
ORDER BY describes how the data should be displayed.
SQL statements include DELETE, UPDATE, and INSERT for data manipulation.
CREATE TABLE, ALTER TABLE, TRUNCATE, and DROP statements change the database structure.
Warning: Many SQL queries will immediately and permanently change data in a database and there is no undo button.
Advantages of Databases
Databases offer flexibility, scalability, and integrity compared to spreadsheets and flat files.
Flexibility: SQL allows pulling data from different tables and displaying it in a useful order.
Scalability: Databases can handle vast amounts of data (thousands, millions, or billions of pieces of data) that other programs cannot.
Integrity: DBMS ensures that multiple users can interact with the database without affecting each other's queries.
Database Terminology
Relational Databases use rows and columns within tables to organize data.
Table: A collection of closely related data.
Columns: Define the data present in each row.
Rows: Contain data referring to the same entity.
Value: The intersection of each row and column.
Primary Key: A unique identifier for each row.
Foreign Key: A key in one table that references the primary key in another.
SQL Statements: Valid commands that interact with a database.
CRUD Statements: Create (INSERT), Read (SELECT), Update (UPDATE), and Delete (DELETE).
Clauses: Building blocks of an SQL statement (e.g., SELECT, FROM, WHERE).
Predicate: Filters results based on user-defined criteria.
Joins: Establish relationships between tables in a query based on overlapping data.
Database Normalization
Normalization minimizes data redundancy and maximizes data integrity.
Each row-column pair should have a single value related to the table's key values.
Create separate tables for distinct entities and link them logically.
Avoid including unrelated data in a table.
It is easier to create normalized databases than updating existing databases to optimize them.
Database Basics
A database is an ordered collection of related data accessed by a computer system.
A Database Management System (DBMS) provides the services necessary for users or software to interact with the data.
The DBMS provides the ability to read, write, update, and delete data, structure data, and set user permissions.
MySQL
MySQL is a DBMS for relational databases, which store data in related rows and columns within tables.
It uses SQL (Structured Query Language) to interact with the data.
SQL statements can display, erase, add data, or change the database structure.
DBMSs have tools to manage the administration of the database instance, such as monitoring its performance
SQL
SQL is a declarative language where programmers state the desired end result, and the database engine determines the steps.
SQL queries are succinct and human-readable.
Basic SQL query structure:
SELECT specifies what data to select (e.g.,
*for everything).FROM indicates the table to select from.
WHERE filters the results based on criteria.
ORDER BY describes how the data should be displayed.
SQL statements include DELETE, UPDATE, and INSERT for data manipulation.
CREATE TABLE, ALTER TABLE, TRUNCATE, and DROP statements change the database structure.
Warning: Many SQL queries will immediately and permanently change data in a database and there is no undo button.
Advantages of Databases
Databases offer flexibility, scalability, and integrity compared to spreadsheets and flat files.
Flexibility: SQL allows pulling data from different tables and displaying it in a useful order.
Scalability: Databases can handle vast amounts of data (thousands, millions, or billions of pieces of data) that other programs cannot.
Integrity: DBMS ensures that multiple users can interact with the database without affecting each other's queries.
Database Terminology
Relational Databases use rows and columns within tables to organize data.
Table: A collection of closely related data.
Columns: Define the data present in each row.
Rows: Contain data referring to the same entity.
Value: The intersection of each row and column.
Primary Key: A unique identifier for each row.
Foreign Key: A key in one table that references the primary key in another.
SQL Statements: Valid commands that interact with a database.
CRUD Statements: Create (INSERT), Read (SELECT), Update (UPDATE), and Delete (DELETE).
Clauses: Building blocks of an SQL statement (e.g., SELECT, FROM, WHERE).
Predicate: Filters results based on user-defined criteria.
Joins: Establish relationships between tables in