Database Management Systems - Comprehensive Study Notes
Databases 1: Principles of a Database Management System & Databases in the Enterprise
Course Contributors
Tom Beurskens (INF1-A)
Christiane Holz (INF1-B)
Geert Monsieur (INF1-C)
Contents
Database Development Process Revisited
ANSI-SPARC-Architecture and Views
Transactions
Functioning of a Database Management System
Which DBMSs Are Used?
Database Development Process Revisited
Creation Methods
"Top Down" Approach:
ER-Model Creation: Entity-Relationship model used for designing data that represents the database structure.
Seven-Step Method: Methodology to produce a relational model (RM) for databases.
"Bottom Up" Approach
Intuitive Table Creation: Developing tables for all data, which includes value ranges and primary keys.
Normalization: Process that produces a well-structured relational model.
UML-Model Creation: Unified Modeling Language model that aids in designing database schemas.
Validation by Normalization: A testing phase ensuring that the database structure is optimal and free from redundancy.
ANSI-SPARC Architecture (3-Level Architecture)
Levels of the Architecture:
External Level:
User's view of the database, describing parts relevant to individual users.
Allows for personalized user experiences.
Conceptual Level:
Describes the structure of the data in terms of entities, relations, and integrity conditions.
Internal Level:
Describes the physical representation and storage of the database data in the computer.
Details where and how data is stored (related to physical files).
Data Independencies
Logical Data Independency:
The conceptual schema can be altered without affecting the external level (user views).
Physical Data Independency:
The internal schema can change without impacting the conceptual schema.
Advantages of ANSI-SPARC Architecture:
Unified Data Access: All users have a single dataset with individualized views.
No Physical Storage Knowledge Required: End users do not need insight into how data is physically stored.
Flexibility: Modifications to the conceptual schema do not disturb the users' views, as structure is independent of physical storage.
Transaction Management
Definition of a Transaction
A transaction is a combination of one or more operations (statements) initiated by a user or application that access or modify data in a database.
Main Transaction Statements:
START TRANSACTION: Initiates a transaction.
COMMIT: Marks the successful end of a transaction, executing all operations.
ROLLBACK: Reverses a transaction, restoring the database to its state before the transaction began. Can be triggered by the developer or system error handling.
ACID Properties of Transactions
Atomicity:
Example: A marriage transaction requires both individuals to be updated simultaneously in records. All operations must succeed or none at all.
Defined as having a transaction executed fully or not at all, hence treated as an atomic entity.
Consistency:
A consistent state must be preserved within the database. Example illustrates ensuring one-sided marriages are invalid.
Constraints and triggers are evaluated before a transaction is committed to maintain consistency.
Isolation:
Transactions should not interfere with one another in a concurrent environment. They need to execute independently, ensuring results mirror sequential execution outcomes.
Durability:
Changes made by a committed transaction must persist, even after system failures. Example of marriage records persists even post-transaction.
DBMS Components and Functioning
Core Components:
Authorization Control: Ensures the requester of a query has the requisite permissions.
Command Processor: Determines actions associated with queries, ensuring proper execution.
Integrity Control: Maintains data integrity as per defined rules.
Query Optimizer: Identifies the most efficient way to execute a query; important especially in correlated subqueries.
Transaction Manager: Manages concurrent transactions to prevent interference, utilizing timestamps as a control mechanism.
Scheduler: Arranges incoming query execution sequences ensuring order and efficiency.
Recovery Manager: Prepares to revert a database to a prior state in case of rollbacks.
Models for Structuring Data in DBMS
Hierarchical Model: Organizes data in a tree-like structure.
Network Model: Focuses on interconnections via graphs.
Relational Model: Widely used, exemplified by DBMS like Access, MySQL, etc.
Object-oriented Model: Facilitates complex data types, e.g., ObjectStore.
Post-relational and No-SQL Databases: Technologies like Hadoop, Cassandra, MongoDB for unstructured data management.
Current DBMS Examples
PostgreSQL: Relational and object-relational open-source DBMS.
DB2, Informix: RDBMS offerings from IBM.
MS Access, MS SQL Server: Microsoft products for personal and enterprise DBMS.
MySQL: Open-source RDBMS owned by Oracle.
MongoDB, Redis: No-SQL databases catering to document and key-value storage.
Neo4J: Graph-based No-SQL DBMS.
DBMS Suppliers and Market Trends
2023 Gartner Data:
The DBMS market saw a 14.4% growth in 2022, surpassing 11.3% in the overall software market.
Cloud DB platforms (dbPaaS) had a significant contribution, now over 55% of the market.
Non-relational DBMS gained 26.8% growth, holding a 21% market share.
Case Study: Facebook's DBMS Techniques
Database Usage:
Facebook utilizes MySQL primarily for key-value persistent storage, running optimizations server-side to boost performance.
Memcached: A technology to cache data and objects in RAM for improved retrieval speeds and reduced database load.
Performance Considerations:
Relying on open-source solutions enables rapid internal problem-solving and performance fine-tuning compared to traditional commercial software support cycles.
Distributed databases combined with logging data enhances analytics capabilities, informing feature development.