Database Relationships and Integrity

Relationships between tables

• Relationships occur when a primary key in one table matches a foreign key in another table.
• Foreign keys can have null or duplicate values, and they reference primary keys in other tables.
• One-to-one: A single record in the first table corresponds to a single record in the second table, and vice versa.
• One-to-many: A single record in the first table can be related to multiple records in the second table, but a single record in the second table can only correspond to one in the first.
• Many-to-many: A single record in the first table can be related to multiple records in the second table, and vice versa. A linking table is created to handle this relationship by copying the primary keys from each table into the new table.

Database Schema and Integrity

• A schema defines the database structure, including tables, fields, relationships, etc.
• Integrity constraints ensure compatibility between various components of the schema.
• Formulas within a schema can represent integrity constraints for specific applications.

Normalization

• A process of optimizing database schemas to minimize redundancies and issues with data insertion, deletion, and updating.
• A complex schema is often decomposed into smaller schemas for optimization.
• Normal forms (at least five exist) are sets of rules that assess table structure for soundness and error-free designs.

SQLite

• SQLite is an open-source, self-contained, serverless, zero-configuration, SQL database engine.
• It is widely deployed and stores data directly in disk files with an .sqlite extension.
• Python can create and access SQLite databases.

Finance.sqlite

• This database holds daily historical price data for 13 stocks and the S&P 500.
• Data tables are named after their corresponding tickers (e.g., AXP, GE, IBM, etc.).
• A Tickers validation table contains a single column (Symbols) listing the unique ticker symbols.

Options.sqlite

• This database utilizes a relational structure to store information about stocks, options, and related trades.
• It consists of four tables: Stocks, OptionContracts, StockTrades, and OptionTrades.
• Relationships are established through primary and foreign keys (e.g., StockSymbol in the Stock table and the StockTrades table).
• The relationships in this database are all one-to-many.

Structured Query Language (SQL)

• SQL is a standardized language for database communication and interaction, designed to be cross-platform.
• Database vendors often extend ANSI/ISO SQL with their own versions (e.g., Transact-SQL, PL/SQL).
• Python can embed SQL statements, enabling various database operations from data retrieval to structural changes.

SQL Statement Categories

• Data Query Language (DQL): Used for data retrieval.
• Data Manipulation Language (DML): Used for writing, changing, or deleting data.
• Data Definition Language (DDL): Used to modify the database structure.
• Data Control Language (DCL): Involves commands like GRANT and REVOKE, which control access privileges.

DML (Data Manipulation Language)

• SELECT: The primary command used to retrieve data from a database.
• SELECT Statement Clauses:
  • FROM: Required clause specifying the table to query.
  • WHERE: Used to filter results based on specific conditions.
  • ORDER BY: Used to sort the returned results.
  • GROUP BY: Used to group rows with the same values based on specified columns.
  • HAVING: Used to filter grouped results.
• Comparison Operators:
  • <: Less than.
  • >: Greater than.
  • >=: Greater than or equal to.
  • =: Equals.
  • <>: Not equal to.
  • BETWEEN: To check if the value lies within a specified range.

This quiz explores the concepts of table relationships within a database, including one-to-one, one-to-many, and many-to-many relationships. It also covers the importance of database schema and integrity constraints that maintain the consistency and validity of data. Test your knowledge and understanding of these fundamental database principles.