Top DB theory

What is a database and what types of databases do you know?

A database is an organized collection of data, generally stored and accessed electronically from a computer system. Databases allow for the efficient storage, retrieval, and manipulation of data.

Types of Databases:

Relational Databases (RDBMS):

• Stores data in tables (rows and columns).
• Uses SQL (Structured Query Language) for data manipulation.
• Examples: MySQL, PostgreSQL, Oracle, SQL Server.

NoSQL Databases:

• Designed for unstructured or semi-structured data.
• Provides flexible schema design.
• Types include document stores (e.g., MongoDB), key-value stores (e.g., Redis), column-family stores (e.g., Cassandra), and graph databases (e.g., Neo4j).

In-Memory Databases:

• Stores data directly in the memory (RAM) for faster data access.
• Often used for caching and real-time applications.
• Examples: Redis, Memcached.

Distributed Databases:

• Data is distributed across multiple nodes or locations.
• Provides scalability and fault tolerance.
• Examples: Apache Cassandra, Google Bigtable.

Object-Oriented Databases:

• Stores data in the form of objects, as used in object-oriented programming.
• Examples: db4o, ObjectDB.

Hierarchical Databases:

• Organizes data in a tree-like structure.
• Example: IBM Information Management System (IMS).

What is normalization? Explain normal forms.

Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity. The goal of normalization is to ensure that each piece of data is stored in only one place, which minimizes anomalies and inconsistencies.

Normal Forms:

First Normal Form (1NF):

• Ensures that the table has no repeating groups or arrays.
• Each field contains only atomic (indivisible) values.
• Example: A table where each column contains a single value, not a list of values.

Second Normal Form (2NF):

• Achieved when the table is in 1NF and all non-key attributes are fully functionally dependent on the primary key.
• Eliminates partial dependency, where non-key attributes depend only on part of a composite primary key.

Third Normal Form (3NF):

• Achieved when the table is in 2NF and all non-key attributes are only dependent on the primary key and not on any other non-key attribute.
• Eliminates transitive dependency, where one non-key attribute depends on another non-key attribute.

Boyce-Codd Normal Form (BCNF):

• A stronger version of 3NF where every determinant is a candidate key.
• Addresses certain anomalies not covered by 3NF.

Fourth Normal Form (4NF):

• Achieved when the table is in BCNF and has no multi-valued dependencies.
• Ensures that one attribute does not have multiple values associated with another attribute in the same table.

Fifth Normal Form (5NF):

• Achieved when the table is in 4NF and cannot be further decomposed without losing information.
• Addresses join dependency and ensures that the data is reconstructed correctly after splitting.

Explain the difference between SQL and NoSQL databases.

SQL Databases:

• Schema: Have a fixed schema that defines tables, columns, and data types.
• Data Model: Relational, where data is stored in tables with rows and columns.
• Scalability: Vertical scaling (increasing the power of a single server).
• Transactions: Supports ACID (Atomicity, Consistency, Isolation, Durability) transactions.
• Query Language: Uses SQL (Structured Query Language) for defining and manipulating data.
• Examples: MySQL, PostgreSQL, Oracle, SQL Server.

NoSQL Databases:

• Schema: Typically schema-less, allowing for dynamic and flexible data models.
• Data Model: Non-relational, supports various models like document, key-value, column-family, and graph.
• Scalability: Horizontal scaling (adding more servers).
• Transactions: Generally support BASE (Basically Available, Soft state, Eventual consistency) principles, though some NoSQL databases also support ACID transactions.
• Query Language: Uses different query languages, depending on the type of database (e.g., MongoDB uses a query language similar to JSON).
• Examples: MongoDB, Cassandra, Redis, Neo4j.

What is a primary key? Provide examples.

A primary key is a unique identifier for a record in a database table. It ensures that each record in the table is unique and can be referenced uniquely. A primary key cannot contain NULL values and must have unique values for every row in the table.

Examples:

• Single-column Primary Key:

CREATE TABLE users (
user_id INT PRIMARY KEY,
username VARCHAR(50),
email VARCHAR(100)
);

In this example, user_id is the primary key, uniquely identifying each user in the users table.

• Composite Primary Key:

CREATE TABLE orders (
order_id INT,
product_id INT,
PRIMARY KEY (order_id, product_id)
);

Here, the combination of order_id and product_id forms the primary key, uniquely identifying each order-product pair in the orders table.

What is a foreign key and how is it used?

A foreign key is a field (or collection of fields) in a table that uniquely identifies a row in another table. It establishes a relationship between two tables, enforcing referential integrity. A foreign key in one table points to a primary key in another table.

Usage:

• Enforcing Relationships: Foreign keys ensure that the data in one table corresponds to valid data in another table.
• Cascading Actions: Foreign keys can be configured to cascade updates or deletes. For example, deleting a record in the parent table can automatically delete related records in the child table.

Example:

CREATE TABLE customers (
customer_id INT PRIMARY KEY,
name VARCHAR(50)
);
CREATE TABLE orders (
order_id INT PRIMARY KEY,
order_date DATE,
customer_id INT,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

In this example, customer_id in the orders table is a foreign key that references the customer_id in the customers table.

What is a transaction and what are its properties (ACID)?

A transaction in a database is a sequence of one or more SQL operations that are executed as a single unit of work. A transaction ensures that the operations are completed successfully; otherwise, none of the operations are applied.

ACID Properties:

• Atomicity: Ensures that all operations within a transaction are completed successfully or none of them are. If any operation fails, the transaction is rolled back.
• Consistency: Ensures that a transaction takes the database from one valid state to another, maintaining all predefined rules, such as constraints, triggers, and cascades.
• Isolation: Ensures that transactions are isolated from each other, meaning that the intermediate state of a transaction is invisible to other transactions until it is complete.
• Durability: Ensures that once a transaction is committed, it will remain in the database even in the case of a system crash.

Example:

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
COMMIT;

In this example, the transaction ensures that money is deducted from one account and added to another atomically. If any part of the transaction fails, the entire operation is rolled back.

How to create and use a stored procedure?

A stored procedure is a precompiled collection of SQL statements that can be executed as a single unit. Stored procedures allow you to encapsulate logic and reuse it across applications, improving performance and maintainability.

Creating a Stored Procedure:

CREATE PROCEDURE getCustomerOrders (IN customerId INT)
BEGIN
SELECT order_id, order_date FROM orders WHERE customer_id = customerId;
END;

In this example, a stored procedure named getCustomerOrders is created to fetch orders for a specific customer.

Using a Stored Procedure:

CALL getCustomerOrders(1);

This call executes the stored procedure, passing 1 as the customerId parameter.

What types of relationships between tables do you know? Explain each of them.

Types of Relationships:

One-to-One (1:1):

• Each record in Table A is related to one and only one record in Table B, and vice versa.
• Example: A user table and a user_profile table where each user has one profile.

Example:

CREATE TABLE user_profiles (
user_id INT PRIMARY KEY,
profile_id INT UNIQUE,
FOREIGN KEY (user_id) REFERENCES users(user_id)
);

One-to-Many (1):

• Each record in Table A can be related to one or more records in Table B, but each record in Table B is related to only one record in Table A.
• Example: A customer table and an orders table where each customer can have multiple orders.

Example:

CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

Many-to-One (N:1):

• This is essentially the reverse of a One-to-Many relationship, where multiple records in Table A are related to a single record in Table B.
• Example: Multiple orders related to a single customer.

Many-to-Many (N):

• Records in Table A can have relationships with multiple records in Table B, and vice versa. This is usually implemented using a junction table.
• Example: A students table and a courses table where each student can enroll in multiple courses, and each course can have multiple students.

Example:

CREATE TABLE student_courses (
student_id INT,
course_id INT,
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id) REFERENCES students(student_id),
FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

How do you perform database backups?

Performing database backups is crucial for ensuring that data can be restored in case of data loss or corruption.

Methods for Performing Backups:

Full Backup:

• A complete copy of the entire database is made.
• Command for MySQL:

mysqldump -u username -p database_name > backup.sql

Incremental Backup:

• Only the data that has changed since the last backup is saved.
• Often used in conjunction with full backups to save time and storage space.

Differential Backup:

• Similar to incremental backups, but saves all changes made since the last full backup.

Point-in-Time Recovery:

• Captures the state of the database at a specific moment in time using transaction logs or binlogs.
• Useful for recovering data to a specific point before a failure occurred.

Database Snapshots:

• A snapshot captures the state of a database at a particular point in time, often used in cloud environments.
• Example with AWS RDS: Create a snapshot via the AWS Management Console or using the AWS CLI.

Automated Backups:

• Setting up automated scheduled backups using cron jobs, database management tools, or cloud services.

Cloud Backup Services:

• Using cloud services like AWS S3, Google Cloud Storage, or Azure Blob Storage to store backup files securely.

Example of Full Backup and Restore in MySQL:

• Backup:

mysqldump -u username -p database_name > backup.sql

• Restore:

mysql -u username -p database_name < backup.sql

Difference between procedure and transaction

Characteristic	Procedure	Transaction
Purpose	Automating the execution of a sequence of operations	Guaranteeing atomicity and data integrity
Integrity Control	Does not guarantee atomicity without using transactions	Ensures atomicity (all or nothing)
Usage	Can contain transactions within itself	Includes one or more SQL queries
Error Handling	Errors may not roll back all previous operations	In case of an error, all operations are rolled back
Example Use Case	Complex business logic, automation of tasks	Operations that must either all succeed or none at all

• Procedure is a block of SQL code used to automate tasks that can include multiple SQL queries and control logic.
• Transaction is a mechanism to ensure data integrity, guaranteeing that all operations are either fully completed or none are executed at all.