DBMS Data Models and SQL Queries

DBMS Study Notes

Data Models

• Definition: Frameworks that determine how data is stored, organized, and manipulated.
• Types:
  • Hierarchical Model: Data organized in a tree-like structure; parent-child relationships.
  • Network Model: Allows more complex relationships with multiple parent nodes.
  • Relational Model: Uses tables (relations) for data storage; relationships via foreign keys.
  • Object-oriented Model: Integrates object-oriented programming principles; data as objects.
  • Key-Value Store: Data stored as a dictionary; simple, fast retrieval.
  • Document Store: Data stored in documents (e.g., JSON, XML); flexible schema.

SQL Queries

• SQL (Structured Query Language): Primary language for managing and querying databases.
• Key Components:
  • SELECT: Retrieve data from one or more tables.
  • INSERT: Add new records to a table.
  • UPDATE: Modify existing records.
  • DELETE: Remove records from a table.
• Joins:
  • INNER JOIN: Returns only matching rows from both tables.
  • LEFT JOIN: Returns all rows from the left table and matched rows from the right table.
  • RIGHT JOIN: Returns all rows from the right table and matched rows from the left table.
  • FULL OUTER JOIN: Returns all records when there is a match in either left or right table.

Database Normalization

• Purpose: Organizing data to minimize redundancy and improve data integrity.
• Normal Forms:
  • First Normal Form (1NF): Eliminate repeating groups; ensure atomicity.
  • Second Normal Form (2NF): Remove partial dependencies; all non-key attributes must depend on the entire primary key.
  • Third Normal Form (3NF): Remove transitive dependencies; non-key attributes must not depend on other non-key attributes.
  • Boyce-Codd Normal Form (BCNF): A stronger version of 3NF; addresses issues of functional dependencies.

Transaction Management

• Definition: Ensures database integrity by managing a sequence of operations.
• ACID Properties:
  • Atomicity: Transactions are all or nothing.
  • Consistency: Transactions must leave the database in a valid state.
  • Isolation: Concurrent transactions do not affect each other.
  • Durability: Once a transaction is committed, it remains so even in the event of a crash.
• Control Mechanisms:
  • Locking: Prevents data inconsistency due to concurrent transactions.
  • Timestamp Ordering: Ensures transaction order based on timestamps.

Data Security

• Importance: Protects data against unauthorized access and breaches.
• Techniques:
  • Authentication: Verifying user identity (e.g., passwords, biometrics).
  • Authorization: Granting permissions to authenticated users.
  • Encryption: Encoding data to prevent unauthorized access (e.g., AES, RSA).
  • Backups: Regularly backing up data to prevent loss.
  • Audit Trails: Monitoring database access and changes for accountability.

Data Models

• Frameworks defining data storage, organization, and manipulation.
• Hierarchical: Tree-like structure; parent-child relationships.
• Network: Complex relationships; multiple parent nodes.
• Relational: Tables (relations); relationships via foreign keys.
• Object-oriented: Integrates OOP principles; data as objects.
• Key-Value Store: Dictionary-like storage; simple, fast retrieval.
• Document Store: Documents (JSON, XML); flexible schemas.

SQL Queries

• SQL (Structured Query Language): Database management and querying.
• SELECT: Data retrieval from one or more tables.
• INSERT: Adding new records.
• UPDATE: Modifying existing records.
• DELETE: Removing records.
• Joins combine data from multiple tables:
  • INNER JOIN: Matching rows only.
  • LEFT JOIN: All rows from the left table, matching rows from the right.
  • RIGHT JOIN: All rows from the right table, matching rows from the left.
  • FULL OUTER JOIN: All rows when a match exists in either table.

Database Normalization

• Organizing data to minimize redundancy, improve integrity.
• Normal Forms:
  • 1NF: Eliminate repeating groups, atomic values.
  • 2NF: Remove partial dependencies; non-key attributes depend on the entire primary key.
  • 3NF: Remove transitive dependencies; non-key attributes don't depend on other non-key attributes.
  • BCNF: Stronger 3NF; addresses functional dependency issues.

Transaction Management

• Ensures database integrity by managing operation sequences.
• ACID Properties:
  • Atomicity: All-or-nothing transactions.
  • Consistency: Database remains valid.
  • Isolation: Concurrent transactions don't interfere.
  • Durability: Committed transactions persist even after crashes.
• Control Mechanisms:
  • Locking: Prevents inconsistencies from concurrent transactions.
  • Timestamp Ordering: Orders transactions based on timestamps.

Data Security

• Protects data from unauthorized access and breaches.
• Techniques:
  • Authentication: Verifying user identity (passwords, biometrics).
  • Authorization: Granting permissions to authenticated users.
  • Encryption: Encoding data for security (AES, RSA).
  • Backups: Preventing data loss.
  • Audit Trails: Monitoring access and changes for accountability.

Test your understanding of data models and SQL queries in Database Management Systems. This quiz covers different types of data models, including hierarchical, network, relational, object-oriented, key-value, and document stores, as well as key SQL commands like SELECT, INSERT, UPDATE, and DELETE.