What are Databases?
A database is an organized collection of data stored and accessed electronically from a computer system in computing.
What is DBMS?
The database management system (DBMS) is the software that interacts with end-users, applications, and the database itself to capture and analyze the data.
The DBMS software additionally encompasses the core facilities provided to administer the database.
The total of the database, the DBMS, and the associated applications can be referred to as a "database system".
Often the term "database" is also used to loosely refer to any of the DBMS, the database system, or an application associated with the database.
Database languages
Database languages are special-purpose languages, which allow one or more of the following tasks:
- Data control language (DCL): controls access to data;
- Data definition language (DDL): defines data types such as creating, altering, or dropping tables and the relationships among them;
- Data manipulation language (DML): performs tasks such as inserting, updating, or deleting data occurrences;
- Data query language (DQL): allows searching for information and computing derived information.
SQL (Structured Query Language) is one of the most popular database languages.
As a database language, SQL supports the following sublanguages through the mentioned commands:
- Data control language (DCL): GRANT/REVOKE
- Data definition language (DDL): CREATE/DROP/ALTER
- Data manipulation language (DML): INSERT/UPDATE/DELETE
- Data query language (DQL): SELECT
Relational Databases
A relational database is a database based on the relational model of data.
The relational model organizes data into one or more tables (or "relations") of columns and rows, with a unique key identifying each row.
Rows are also called records or tuples. Columns are also called attributes.
Generally, each table/relation represents one "entity type" (such as customer or product). The rows represent instances of that type of entity (such as "Lee" or "chair") and the columns representing values attributed to that instance (such as address or price).
In SQL terminology,
- "Tuple" is known as "Row".
- "Attribute" is known as "Column".
- "Relation" is known as "Table".
Popular Relational Databases: Oracle, MySQL, MariaDB, Microsoft SQL Server, PostgresSQL, SQLite.
Relational Database Management System (RDBMS)
Relational Database Management System (RDBMS) is a database management system used when the data is relational.
ACID Transactions
A database transaction is a unit of work, typically encapsulating several operations over a database (e.g., reading a database object, writing, acquiring a lock, etc.), an abstraction supported in the database, and also other systems.
Each transaction has well-defined boundaries in terms of which program/code executions are included in that transaction (determined by the transaction's programmer via special transaction commands).
The acronym ACID describes some ideal properties of a database transaction: atomicity, consistency, isolation, and durability.
ACID Properties
In computer science, ACID (atomicity, consistency, isolation, durability) is a set of properties of database transactions intended to guarantee data validity despite errors, power failures, and other mishaps.
In the context of databases, a sequence of database operations that satisfies the ACID properties (which can be perceived as a single logical operation on the data) is called a transaction.
For example, a transfer of funds from one bank account to another, even involving multiple changes such as debiting one account and crediting another, is a single transaction.
The characteristics of these four properties are defined as follows:
- Atomicity: Transactions are often composed of multiple statements. Atomicity guarantees that each transaction is treated as a single "unit", which either succeeds completely or fails completely: if any of the statements constituting a transaction fails to complete, the entire transaction fails and the database is left unchanged.
- Consistency/Correctness: Consistency ensures that a transaction can only bring the database from one valid state to another, maintaining database invariants: any data written to the database must be valid according to all defined rules, including constraints, cascades, triggers, and any combination thereof. This prevents database corruption by an illegal transaction but does not guarantee that a transaction is correct. Referential integrity guarantees the "primary key"-"foreign key" relationship.
- Isolation: Transactions are often executed concurrently (e.g., multiple transactions reading and writing to a table at the same time). Isolation ensures that concurrent execution of transactions leaves the database in the same state that would have been obtained if the transactions were executed sequentially.
- Durability: Durability guarantees that once a transaction has been committed, it will remain committed even in the case of a system failure (e.g., power outage or crash). This usually means that completed transactions (or their effects) are recorded in non-volatile memory.
Normalization and Denormalization
Normalization
The process of creating a logical database design using the relational model uses a methodical approach known as normalization. The goal of normalization is to ensure that each elementary "fact" is only recorded in one place, so that insertions, updates, and deletions automatically maintain consistency.
When an attempt is made to modify (update, insert into, or delete from) a relation, the following undesirable side-effects may arise in relations that have not been sufficiently normalized:
- Update anomaly: The same information can be expressed on multiple rows; therefore updates to the relationship may result in logical inconsistencies.
For example, each record in an "Employees' Skills" relation might contain an Employee ID, Employee Address, and Skill; thus a change of address for a particular employee may need to be applied to multiple records (one for each skill). If the update is only partially successful: the employee's address is updated on some records but not others: then the relation is left in an inconsistent state. Specifically, the relation provides conflicting answers to the question of what this particular employee's address is. This phenomenon is known as an update anomaly. - Insertion anomaly: There are circumstances in which certain facts cannot be recorded at all.
For example, each record in a "Faculty and Their Courses" relation might contain a Faculty ID, Faculty Name, Faculty Hire Date, and Course Code. Therefore, the details of any faculty member who teaches at least one course can be recorded, but a newly hired faculty member who has not yet been assigned to teach any courses cannot be recorded, except by setting the Course Code to null. This phenomenon is known as an insertion anomaly. - Deletion anomaly: Under certain circumstances, deletion of data representing certain facts necessitates deletion of data representing completely different facts.
The "Faculty and Their Courses" relation described in the previous example suffers from this type of anomaly, for if a faculty member temporarily ceases to be assigned to any courses, the last of the records on which that faculty member appears must be deleted, effectively also deleting the faculty member, unless the Course Code field is set to null. This phenomenon is known as a deletion anomaly.
A fully normalized database allows its structure to be extended to accommodate new types of data without changing the existing structure too much. As a result, applications interacting with the database are minimally affected.
Denormalization
Denormalization is a strategy used on a previously normalized database to increase performance.
In computing, denormalization is the process of trying to improve the read performance of a database, at the expense of losing some write performance, by adding redundant copies of data or by grouping data.
DBMS Keys
Keys are used to uniquely identify a row in a table. There are different types of keys based on the generalization or use case.
- Super Key: A super key is a set of attributes of a relation schema upon which all attributes of the schema are functionally dependent. No two rows can have the same value of super key attributes.
- Candidate Key: A Candidate key is a minimal superkey, i.e., no proper subset of Candidate key attributes can be a superkey. There can be more than one candidate key.
- Primary Key: A Primary Key is one of the candidate keys. One of the candidate keys is selected as most important and becomes the primary key. There cannot be more than one primary key in a table.
- Unique Key: The unique key is very similar to the primary key except that primary keys don’t allow NULL values in the column but unique keys allow them. So essentially unique keys are primary keys with NULL values.
- Foreign Key: A Foreign key is a field (or collection of fields) in one table that uniquely identifies a row of another table.
Joins
Joins are used in relational databases to get data across multiple tables based on some common attributes. A foreign key may be used to reference a row in another table and join can be done based on those columns.
There are five standard types of Joins:
- Cross Join: CROSS JOIN returns the Cartesian product of rows from tables in the join. In other words, it will produce rows that combine each row from the first table with each row from the second table. If the first table has 5 rows and the second table has 6 rows, the joined table would have 30 rows.
- Inner Join: An inner join requires each row in the two joined tables to have matching column values. The result of the join can be defined as the outcome of first taking the Cross join of all rows in the tables and then returning all rows that satisfy the join predicate.
- Left Outer Join: The result of a left outer join (or simply left join) for tables A and B always contains all rows of the "left" table (A), even if the join-condition does not find any matching row in the "right" table (B). This means that if the ON clause matches 0 (zero) rows in B (for a given row in A), the join will still return a row in the result (for that row)—but with NULL in each column from B. A left outer join returns all the values from an inner join plus all values in the left table that do not match the right table, including rows with NULL (empty) values in the link column.
- Right Outer Join: A right outer join (or right join) closely resembles a left outer join, except with the treatment of the tables reversed. Every row from the "right" table (B) will appear in the joined table at least once. If no matching row from the "left" table (A) exists, NULL will appear in columns from A for those rows that have no match in B.
- Full Outer Join: Conceptually, a full outer join combines the effect of applying both left and right outer joins. Where rows in the FULL OUTER JOINed tables do not match, the result set will have NULL values for every column of the table that lacks a matching row. For those rows that do match, a single row will be produced in the result set (containing columns populated from both tables).
Database Indexing
A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of additional writes and the use of more storage space to maintain the extra copy of data.
A database index provides a quick lookup of data in a column or columns of a table. It enhances the speed of operations accessing data from a database table at the cost of additional writes and memory to maintain the index data structure.
Data can be stored only in one order on a disk. To support faster access according to different values, faster search like binary search for different values is desired, For this purpose, indexes are created on tables. These indexes need extra space on the disk, but they allow faster search according to different frequently searched values.
