Database Fundamentals
Database fundamentals covers how structured data is stored, organized, queried, and protected. It introduces relational and non-relational models, SQL, transactions, indexing, normalization, and the design decisions that determine a database's correctness and performance.
itDatabases and data storage | OpenSkills.info
Intro
Database Fundamentals
A database is an organized collection of data. A database management system, or DBMS, stores that data and controls how applications read and change it.
The distinction matters. The database is the data and its structure. The DBMS is the software that manages storage, queries, concurrent access, permissions, and recovery.
Why databases exist
A plain file can hold data. It becomes difficult to manage when several users update it, records refer to one another, or a failed change must be undone.
A DBMS gives you shared rules around the data:
- a schema describes its structure;
- constraints reject invalid states;
- queries retrieve and combine records;
- transactions group related changes;
- permissions limit who can perform each operation;
- backup and restore procedures protect against data loss.
These features make a database useful as a system of record. An application can change while the data keeps a defined shape and meaning.
The relational mental model
A relational database organizes data into tables. Each table has named columns and rows. A column has a data type. A row holds one occurrence of the thing the table represents.
Continue the course
This section is part of the paid course.
See pricing to subscribe, or log in if you already have access.
Where this skill leads
Relevant careers
See how this topic contributes to broader role-level skill maps.
Sources
- https://www.postgresql.org/docs/current/tutorial.html
Supports
- PostgreSQL's official tutorial introduces relational concepts and SQL
- The tutorial covers tables, rows, queries, joins, aggregates, updates, foreign keys, and transactions
- https://www.postgresql.org/docs/current/tutorial-arch.html
Supports
- A database server manages database files, accepts client connections, and performs database actions for clients
- Client and server responsibilities can run on different hosts
- https://www.postgresql.org/docs/current/tutorial-concepts.html
Supports
- A relational database stores data in relations represented as tables
- Tables contain rows with the same named, typed columns
- SQL does not guarantee table row order without explicit sorting
- https://www.postgresql.org/docs/current/tutorial-sql.html
Supports
- SQL supports table creation, row insertion, queries, joins, aggregates, updates, and deletion
- https://www.postgresql.org/docs/current/tutorial-join.html
Supports
- A join combines rows from multiple tables through a matching condition
- Inner and outer joins retain different sets of matched and unmatched rows
- https://www.postgresql.org/docs/current/ddl-constraints.html
Supports
- Check, not-null, unique, primary-key, and foreign-key constraints reject invalid table states
- A primary key is unique and not null
- A foreign key preserves referential integrity between related tables
- A junction table with overlapping foreign keys can implement a many-to-many relationship
- https://learn.microsoft.com/en-us/previous-versions/troubleshoot/microsoft-365/microsoft-365-apps/access/database-normalization-description
Supports
- Normalization organizes tables and relationships to reduce redundancy and inconsistent dependencies
- The first three normal forms separate repeating groups and facts that depend on different keys
- Deliberate departures from normalization require attention to redundancy and consistency
- https://www.postgresql.org/docs/current/functions-comparison.html
Supports
- Ordinary comparisons produce null when an input is null
- SQL provides IS NULL and IS NOT NULL predicates
- https://www.postgresql.org/docs/current/tutorial-transactions.html
Supports
- Transactions bundle multiple steps into an all-or-nothing operation
- Commit makes changes visible and durable while rollback discards them
- Savepoints allow partial rollback within a transaction
- https://www.postgresql.org/docs/current/transaction-iso.html
Supports
- Isolation levels define which concurrency phenomena can occur
- Serializable transactions can require retries after serialization failures
- https://www.postgresql.org/docs/current/mvcc-intro.html
Supports
- Multiversion concurrency control gives each statement a snapshot of data
- Concurrent reading and writing can proceed with reduced lock contention
- https://www.postgresql.org/docs/current/indexes-intro.html
Supports
- Indexes can locate selected rows without scanning an entire table
- Indexes add storage and data-modification overhead
- The planner decides whether an index is useful for a query
- https://dev.mysql.com/doc/refman/8.4/en/optimization-indexes.html
Supports
- Indexes can improve selected queries by locating matching rows
- Unnecessary indexes consume space and add insert, update, and delete cost
- https://www.postgresql.org/docs/current/ddl-priv.html
Supports
- Database objects have owners and configurable privileges
- Grant and revoke control privileges for roles
- https://www.postgresql.org/docs/current/backup.html
Supports
- PostgreSQL documents SQL dumps, file-system backups, and continuous archiving as backup approaches
- Recovery planning must select and operate an appropriate backup approach
- https://www.postgresql.org/docs/current/continuous-archiving.html
Supports
- A base backup plus archived write-ahead log supports point-in-time recovery
- Administrators should test recovery procedures and protect archived data
- https://www.mongodb.com/docs/manual/data-modeling/
Supports
- MongoDB documents can vary in fields and field types within a collection
- Document schemas can embed or reference related data according to access patterns
- https://redis.io/docs/latest/develop/data-types/
Supports
- Redis stores values by key and provides multiple value data types
- Redis data types support different access and update operations
- https://neo4j.com/docs/getting-started/appendix/graphdb-concepts/
Supports
- A property graph represents data with nodes, relationships, labels, and properties
- Relationships connect nodes and carry direction, type, and properties