Database Design and Relationships Quiz

Entities: Objects or things in the database (e.g., customers, orders).
Attributes: Information that describes entities (e.g., customer name, order date).
Keys:
- Primary Key: Unique identifier for each record in a table.
- Foreign Key: A field that links one table to another, establishing a relationship.
Normalization: Process of organizing data to reduce redundancy and improve integrity.
- 1st Normal Form (1NF): Ensure all columns contain atomic values.
- 2nd Normal Form (2NF): Meet 1NF and all non-key attributes must be fully functionally dependent on the primary key.
- 3rd Normal Form (3NF): Meet 2NF and no transitive dependencies exist.
Schema: Structure that defines how data is organized in a database.

Joins: Combine rows from two or more tables based on related columns. Types include:
- INNER JOIN: Returns records with matching values in both tables.
- LEFT JOIN (LEFT OUTER JOIN): Returns all records from the left table, and matched records from the right table. Non-matching records return NULL.
- RIGHT JOIN (RIGHT OUTER JOIN): Returns all records from the right table and matched records from the left table. Non-matching records return NULL.
- FULL JOIN (FULL OUTER JOIN): Returns all records when there is a match in either left or right table.
- CROSS JOIN: Returns the Cartesian product of both tables (all combinations).
Relationships:
- One-to-One: Each row in one table is linked to one row in another table.
- One-to-Many: A row in one table can relate to multiple rows in another table.
- Many-to-Many: Rows in one table can relate to multiple rows in another table through a junction table.

SQL Statements: Used to perform operations on data.
- INSERT: Adds new records to a table.
  - Syntax: INSERT INTO table_name (column1, column2) VALUES (value1, value2);
- UPDATE: Modifies existing records.
  - Syntax: UPDATE table_name SET column1 = value1 WHERE condition;
- DELETE: Removes existing records.
  - Syntax: DELETE FROM table_name WHERE condition;
Transactions: A sequence of operations treated as a single unit. Supports:
- COMMIT: Saves changes.
- ROLLBACK: Reverts changes since the last commit.
Constraints: Rules enforced on data columns to maintain integrity.
- Common types: NOT NULL, UNIQUE, CHECK, DEFAULT.

SELECT Statement: Retrieves data from one or more tables.
- Syntax: SELECT column1, column2 FROM table_name WHERE condition;
- * can be used to select all columns.
Filtering Rows: Use WHERE clause for conditions.
Sorting Results: Use ORDER BY clause to sort data.
- Syntax: ORDER BY column1 [ASC|DESC];
Aggregation Functions: Perform calculations on data.
- Common functions include: COUNT(), SUM(), AVG(), MAX(), MIN().
GROUP BY: Groups rows sharing a property so aggregate functions can be applied.
- Syntax: GROUP BY column;
HAVING Clause: Applies conditions to groups created by GROUP BY.
Subqueries: A query nested inside another query for complex operations.

Entities represent real-world objects, like customers or orders, within a database.
Attributes describe the properties of entities, providing information like customer name or order date.
Keys are crucial for data identification and relationships.
- Primary Keys uniquely identify each record in a table.
- Foreign Keys link tables together, establishing relationships between data.
Normalization is a process for organizing data to minimize redundancy and improve integrity.
- 1st Normal Form (1NF) ensures atomic values within each column.
- 2nd Normal Form (2NF) follows 1NF and ensures non-key attributes depend fully on the primary key.
- 3rd Normal Form (3NF) builds on 2NF by eliminating transitive dependencies, preventing indirect relationships between data.
Schema defines the structure and organization of a database.

Joins combine rows from multiple tables based on related columns.
- INNER JOIN retrieves matching rows from both tables.
- LEFT JOIN (LEFT OUTER JOIN) returns all rows from the left table, and matched rows from the right. Non-matching rows get NULL values.
- RIGHT JOIN (RIGHT OUTER JOIN) returns all rows from the right table and matched rows from the left. Non-matching rows get NULL values.
- FULL JOIN (FULL OUTER JOIN) returns all rows when there is a match in either left or right table.
- CROSS JOIN returns the Cartesian product, listing all combinations of rows.
Relationships describe how tables connect.
- One-to-One links each row in one table to a single row in another.
- One-to-Many allows a single row in one table to relate to multiple rows in another.
- Many-to-Many uses a junction table to accommodate multiple connections between entities.

SQL Statements are essential for manipulating database data.
- INSERT adds new records to a table.
  - Syntax: INSERT INTO table_name (column1, column2) VALUES (value1, value2);
- UPDATE modifies existing records.
  - Syntax: UPDATE table_name SET column1 = value1 WHERE condition;
- DELETE removes existing records.
  - Syntax: DELETE FROM table_name WHERE condition;
Transactions provide a mechanism for grouping database operations.
- COMMIT saves changes made in a transaction.
- ROLLBACK undoes changes, restoring the database to its prior state.
Constraints enforce data integrity by adding rules.
- NOT NULL disallows empty values in a column.
- UNIQUE ensures each value is unique within a column.
- CHECK verifies data meets specific conditions.
- DEFAULT assigns a default value to a column if none is provided.

SELECT Statement retrieves data from tables.
- Syntax: SELECT column1, column2 FROM table_name WHERE condition;
- * selects all columns from a table.
Filtering Rows uses the WHERE clause for conditions.
Sorting Results employs the ORDER BY clause.
- Syntax: ORDER BY column1 [ASC|DESC];
Aggregation Functions perform calculations on data.
- Functions include: COUNT(), SUM(), AVG(), MAX(), MIN().
GROUP BY groups rows sharing a specific characteristic.
- Syntax: GROUP BY column;
HAVING Clause filters results based on conditions applied to groups.
Subqueries nested within other queries for complex operations.