Database Systems Basics
A transaction is a group of queries that must be either completed or rejected as a whole.
- when processing transactions, DBMS must:
- ensure transactions are processed completely or not at all
- prevent conflicts between concurrent transactions
- ensure transaction results are never lost
Architecture
Database architecture describes the internal components and the relationships between components.
- components:
- query processor
- interprets queries, creates a plan to modify the database or retrieve data, and returns query results to the application
- performs query optimization to ensure the most efficient instructions are executed
- storage manager
- translates the query processor instructions into low-level file-system commands that modify or retrieve data
- uses indexes to quickly locate data
- log
- is a file containing a complete record of all inserts, updates and deletes processed by the database
- transaction manager writes to log before applying changes to the database
- catalog
- aka data dictionary
- is a directory of tables, columns, indexes, and other database objects
- used to process and execute queries
- query processor
Metadata is data about the database, such as column names and the number of rows in each table.
Products
A relational database stores data in tables, columns, and rows.
- similar to a spreadsheet
- most database systems are relational
- all data in a column has the same format
- all data in a row represents a single object
- all relational database systems support Structured Query Language (SQL)
- uses statements that read and write data, create and delete tables, and administer the database system
- ideal for databases that require an accurate record of every transaction
- not ideal for big data
- massive volumes of poorly structured or incomplete data
- thus non-relational systems were created optimized for big data
- called NoSQL
- db-engines.com ranks systems