2.3 Database Management Systems

Database management systems are essential tools for organizing and accessing data efficiently. They provide a structured way to store, retrieve, and manipulate information, acting as a bridge between users and databases. Understanding their fundamentals is crucial for effective data management.

Relational and non-relational databases offer different approaches to data storage. Relational databases use tables and SQL for structured data, while non-relational databases provide flexibility for various data models. Choosing the right type depends on specific data needs and application requirements.

Database Management Systems Fundamentals

Concept of database management systems

• Database Management System (DBMS) creates maintains and uses databases acting as interface between users and database
• Key functions include data storage organization retrieval querying security access control integrity maintenance concurrent access management
• Benefits encompass centralized data management reduced redundancy improved consistency enhanced security efficient access manipulation

Relational vs non-relational databases

• Relational databases organize data into tables with rows and columns use SQL for querying manipulation ensure data integrity through ACID properties (MySQL, PostgreSQL)
• Non-relational databases (NoSQL) use non-table-based structures designed for specific data models offer flexibility scalability
  • Document stores handle semi-structured data (MongoDB)
  • Key-value stores provide fast access to simple data (Redis)
  • Column-family stores optimize for column-based queries (Cassandra)
  • Graph databases excel at managing interconnected data (Neo4j)

Database Design and Implementation

Design of database schemas

• Entity-Relationship (ER) diagram components represent objects concepts (entities) describe properties (attributes) show connections (relationships)
• Cardinality in relationships defines associations between entities:
  1. One-to-one (1:1)
  2. One-to-many (1:N)
  3. Many-to-many (M:N)
• ER diagram creation process:
  1. Identify entities
  2. Determine attributes
  3. Establish relationships
  4. Specify cardinality
• Translating ER diagrams to relational schema involves creating tables for entities implementing relationships using foreign keys

SQL queries for data manipulation

• Basic SQL query structure uses SELECT for retrieval FROM specifies table WHERE filters data
• Common operations: INSERT adds records UPDATE modifies existing DELETE removes records
• SQL functions operators include:
  • Aggregate functions (COUNT, SUM, AVG, MAX, MIN)
  • String functions (CONCAT, SUBSTRING, UPPER, LOWER)
  • Comparison operators ($=, <>, <, >, <=, >=$)
  • Logical operators (AND, OR, NOT)
• Joining tables combines data from multiple tables (INNER JOIN, LEFT JOIN, RIGHT JOIN)
• Subqueries nested queries allow complex data retrieval
• Sorting grouping results use ORDER BY for sorting GROUP BY for grouping HAVING for filtering grouped results