Databases
Database terms and concepts: SQL, keys, joins, normalization, NoSQL and more.
36 pages
- ACID PropertiesAtomicity, Consistency, Isolation and Durability — the guarantees that make database transactions reliable.
- Alternate Keya candidate key that was not chosen as the primary key, usually enforced with a UNIQUE constraint.
- Candidate Keya minimal set of columns that uniquely identifies each row; one candidate key is chosen as the primary key.
- CAP Theorema distributed system can guarantee only two of Consistency, Availability and Partition tolerance at once.
- Composite Keytwo or more columns that together uniquely identify each row in a table, such as (student_id, course_id).
- Data Warehousea central database for analysis and reporting that stores cleaned, historical data from many sources (OLAP).
- Database Indexa data structure that speeds up finding rows by column values, at the cost of extra storage and slower writes.
- Database Replicationkeeping copies of a database on several servers for high availability and to spread read traffic.
- Database Shardingsplitting a large database across several servers by a shard key so storage and load scale horizontally.
- Database Transactiona group of SQL operations that succeed or fail together, controlled with BEGIN, COMMIT and ROLLBACK.
- Database TriggerSQL code that runs automatically before or after an INSERT, UPDATE or DELETE on a table.
- DBMS (Database Management System)software that stores, organises and manages data and controls access to it.
- DDL, DML, DCL and TCLthe groups of SQL commands for defining structure, changing data, managing permissions and controlling transactions.
- Deadlock (Database)two transactions each waiting for a lock the other holds; the database rolls one back so the other can finish.
- Denormalizationadding redundant data or merging tables on purpose so reads need fewer joins, at the cost of keeping copies in sync.
- ER Diagram (Entity-Relationship Diagram)a visual model of entities, their attributes and relationships (one-to-one, one-to-many, many-to-many) in a database.
- Foreign Keya column that refers to the primary key of another table, linking the tables and enforcing referential integrity.
- Functional DependencyX → Y means the value of X determines the value of Y; the basis of normalization and normal forms.
- MongoDBan open-source NoSQL document database that stores flexible JSON-like documents in collections.
- MySQLa widely used open-source relational database that stores data in tables and is queried with SQL.
- Normalizationorganising database tables to reduce duplicate data and anomalies, through normal forms such as 1NF, 2NF and 3NF.
- NoSQLnon-relational databases — document, key-value, wide-column and graph — built for flexible data and horizontal scaling.
- ORM (Object-Relational Mapping)a library that maps database tables to classes and objects so code can query data without writing SQL.
- PostgreSQLa free, open-source relational database known for reliability and advanced features such as JSONB, extensions and full-text search.
- Primary Keya column or set of columns that uniquely identifies each row in a database table and cannot be NULL.
- RDBMS (Relational Database Management System)a DBMS that stores data in related tables and is queried with SQL.
- Redisan in-memory key-value store used for caching, sessions, leaderboards and real-time data, with sub-millisecond speed.
- SQLSQL stands for Structured Query Language. It is, fundamentally, a programming language designed for accessing, modifying, and extracting information from..
- SQL ConstraintsNOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK and DEFAULT rules that the database enforces on data.
- SQL Joinscombine rows from two or more tables on a related column — INNER, LEFT, RIGHT, FULL OUTER and CROSS JOIN.
- SQL Viewa virtual table defined by a saved query, used to simplify complex queries and hide sensitive columns.
- Stored Procedurea named, reusable set of SQL statements saved in the database and run with a single call.
- Super Keyany set of columns that uniquely identifies each row in a table, possibly including extra columns.
- Surrogate Keya generated identifier with no business meaning, such as an auto-increment ID or UUID, used as the primary key.
- Types of Keys in DBMSsuper, candidate, primary, alternate, unique, composite, foreign and surrogate keys explained.
- Unique Keya constraint that stops duplicate values in a column; unlike a primary key a table can have many, and NULL is allowed.