Database Fundamentals, Design, and Programming Flashcards

Core Principles and Fundamentals of Databases

  • Database: An organized collection of data stored persistently for easy access, management, and updating.

  • Data Integrity: The accuracy, consistency, and completeness of data throughout its lifecycle.

  • Data Persistence: Ensuring data remains stored safely on non-volatile media (like a hard drive) even when the system or app turns off.

  • Analytical Query Execution: Running complex searches on data to find patterns, trends, and summary information.

  • Concurrent Multi-User Access: Allowing multiple users to read and write data at the same time without creating conflicts or errors.

Database Structures and SQL Integration

  • Relational Entity (Table): A grid of rows and columns used to store information about a single real-world object or idea.

  • Attributes (Columns): The characteristics or properties stored for an entity (e.g., Name, Age).

  • Records (Rows / Tuples): A single complete set of data fields for one specific item in a table.

  • Structured Query Language (SQL): The standard programming language used to create, query, update, and manage relational databases.

  • Data Query Language (DQL): The part of SQL (primarily SELECT) used to search for and view data.

  • Data Manipulation Language (DML): The part of SQL (INSERT, UPDATE, DELETE) used to change or edit the actual data in tables.

  • Data Definition Language (DDL): The part of SQL (CREATE, ALTER, DROP, TRUNCATE) used to create or change the structure/schema of the database.

Relational Database Models and Connections

  • Relational Databases: Databases that store data in separate tables connected to each other through logical links.

  • Primary Key: A unique attribute that identifies each row in a table so no two rows are identical.

  • Foreign Key: A column in one table that links to a primary key in another table to connect related data.

  • Entity Relationships: The ways tables connect to each other:

    • One-to-One (1:1): One record in Table A connects to exactly one record in Table B.

    • One-to-Many (1:N): One record in a parent table connects to many records in a child table.

    • Many-to-Many (M:N): Many records in Table A connect to many records in Table B (requires a junction table).

  • Data Redundancy: Storing the same data in multiple places unnecessarily, which wastes storage and causes mistakes.

  • Data Anomalies: Errors caused by bad database structure:

    • Insertion Anomaly: Unable to add new data because required related fields are missing.

    • Update Anomaly: Inconsistent data caused by updating some duplicate records but forgetting others.

    • Deletion Anomaly: Losing wanted data accidentally when deleting an unrelated record.

Database Management Systems (DBMS) Frameworks

  • Database Management System (DBMS): Software that sits between the user/applications and the database to manage data safely.

  • Schema Management: Tools in a DBMS to design and edit the layout, structures, and data types of the database.

  • Data Integrity and Security: Built-in features like passwords, access rules, and encryption to protect data.

  • Transaction Management and Concurrency Control: Systems that control simultaneous actions so users don't overwrite each other's changes.

  • ACID Properties: Four rules that ensure database transactions are reliable:

    • Atomicity: "All or nothing" — if one step of a transaction fails, everything is canceled (e.g., bank transfer cancels if the deposit fails).

    • Consistency: Ensures the database follows all rules and constraints before and after a change (e.g., account balance cannot go below $0).

    • Isolation: Keeps concurrent transactions separate so they don't interfere with each other (e.g., prevents two people from buying the same ticket at once).

    • Durability: Ensures committed changes are saved permanently, even if the system crashes right after.

  • Backup and Recovery Interfaces: Tools that regularly back up data and restore it if the system crashes.

Detailed Real-World Applications and Use Cases

  • Retail and E-Commerce Databases: Used to track product stock, prices, customer orders, and payments.

  • Customer Relationship Management (CRM): Systems storing client contact details, interaction history, and sales steps.

  • Social Media Platforms: Databases that store user profiles, posts, comments, friend connections, and feeds.

  • Healthcare Systems: Secure systems storing medical records, lab results, and medication history.

  • Interactive Gaming Infrastructures: High-speed databases tracking player scores, inventory items, quest progress, and online status.