Table of contents
As I am preparing for job interviews that involve database management systems (DBMS), I took notes on the key concepts that I felt were important and often asked in these interviews. I noticed that while there are many websites online that cover these concepts, I wanted to have a single page that summarized them all. With this in mind, I decided to create a blog post to review and summarize these concepts.
DBMS vs RDBMS
RDBMS stores data in the form of related tables, while a DBMS may use a different organization method, such as a flat file or hierarchical structure. RDBMS, relationships between data are established using keys. While DBMS may use concepts such as pointers. RDBMS uses structured query language while DBMS may use some other query language.
Keys in DBMS
Primary key - Uniquely identifies each record in a table,
Foreign key - The primary key of another table
Candidate key - Something that could be used as a primary key
Composite key - primary key made up of two or more fields
Surrogate key - used for uniquely identifying records in a table, can be used as a primary key, it is something that may not be related to the data in the table.
Normalization
Normalization is the process of organizing a database in a way that reduces redundancy and dependency. It is a common technique used to design and structure databases in a way that is efficient, organized, and scalable.
There are several levels of normalization, known as normal forms, which build on each other and provide increasing levels of data organization and integrity. The most common normal forms are:
First normal form (1NF): In 1NF, a table must contain no repeating groups and no multi-valued attributes.
Second normal form (2NF): In 2NF, a table must be in 1NF and must not have any partial dependencies.
Third normal form (3NF): In 3NF, a table must be in 2NF and must not have any transitive dependencies.
Boyce-Codd normal form (BCNF): BCNF is a stronger version of 3NF, in which a table must be in 3NF and must not have any nontrivial functional dependencies.
Triggers in DBMS
In a database management system (DBMS), a trigger is a piece of code that is automatically executed in response to certain events on a table or view in the database. Triggers are used to enforce business rules and data integrity, perform tasks automatically when data is added, modified, or deleted, and audit or log changes to the data.
Triggers can be defined to execute before or after an INSERT, UPDATE, or DELETE operation is performed on a table or view. They can also be defined to execute INSTEAD OF an INSERT, UPDATE, or DELETE operation on a view.
CREATE TRIGGER update_customer_status
AFTER INSERT ON customers
FOR EACH ROW
BEGIN
UPDATE customers
SET status = 'active'
WHERE customer_id = NEW.customer_id;
END;
JOIN
INNER JOIN, OUTER JOIN, CROSS JOIN, and SELF JOIN
An OUTER JOIN returns all rows from one table, and any matching rows from the other table. There are three types of OUTER JOINs: LEFT JOIN, RIGHT JOIN, and FULL JOIN. A LEFT JOIN returns all rows from the left table and any matching rows from the right table. A RIGHT JOIN returns all rows from the right table and any matching rows from the left table. A FULL JOIN returns all rows from both tables, whether or not there is a match in the other table. SELECT customers.customer_id, orders.order_id FROM customers INNER JOIN orders ON customers.customer_id = orders.customer_id A CROSS JOIN returns the Cartesian product of the two tables, which is the set of all possible combinations of rows from both tables. A SELF JOIN is a type of INNER JOIN in which a table is joined to itself. It is used to compare rows within the same table.
Cursors
In a database management system (DBMS), a cursor is a control structure that enables traversal over the rows in a result set. Cursors are often used to process the results of a SELECT statement row by row, rather than returning the entire result set at once.
Here is a cheat sheet that I found very helpful - https://www.databasestar.com/sql-cheat-sheets/