DBMS Quick Reference Sheet

Basic SQL Commands

Command Description Example
SELECT Retrieve data from database SELECT * FROM users WHERE age > 25;
INSERT Add new records to table INSERT INTO users (name, age) VALUES ('John', 30);
UPDATE Modify existing records UPDATE users SET age = 31 WHERE name = 'John';
DELETE Remove records from table DELETE FROM users WHERE age < 18;
CREATE TABLE Create a new table CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(50));
DROP TABLE Delete a table DROP TABLE users;

Common SQL Clauses

Clause Description Example
WHERE Filter records based on condition SELECT * FROM products WHERE price > 100;
ORDER BY Sort the result set SELECT * FROM users ORDER BY name ASC;
GROUP BY Group rows with same values SELECT department, COUNT(*) FROM employees GROUP BY department;
HAVING Filter groups (used with GROUP BY) SELECT department, COUNT(*) FROM employees GROUP BY department HAVING COUNT(*) > 5;
LIMIT Limit number of returned rows SELECT * FROM products LIMIT 10;

SQL Joins

Join Type Description Example
INNER JOIN Returns records with matching values in both tables SELECT orders.id, customers.name FROM orders INNER JOIN customers ON orders.customer_id = customers.id;
LEFT JOIN Returns all records from left table, and matched records from right table SELECT customers.name, orders.id FROM customers LEFT JOIN orders ON customers.id = orders.customer_id;
RIGHT JOIN Returns all records from right table, and matched records from left table SELECT customers.name, orders.id FROM customers RIGHT JOIN orders ON customers.id = orders.customer_id;
FULL OUTER JOIN Returns all records when there's a match in either table SELECT customers.name, orders.id FROM customers FULL OUTER JOIN orders ON customers.id = orders.customer_id;

Database Normalization Forms

Form Description Requirements
1NF (First Normal Form) Eliminate repeating groups Each column contains atomic (indivisible) values
2NF (Second Normal Form) Eliminate partial dependencies Must be in 1NF; all non-key attributes fully functionally dependent on primary key
3NF (Third Normal Form) Eliminate transitive dependencies Must be in 2NF; no non-key attribute depends on another non-key attribute
BCNF (Boyce-Codd NF) Advanced version of 3NF Every determinant is a candidate key

ACID Properties

Property Description
Atomicity Ensures all operations in a transaction are completed successfully or none are
Consistency Ensures database remains in valid state before and after transaction
Isolation Ensures concurrent transactions don't interfere with each other
Durability Ensures committed transactions remain permanent even after system failure

Common Data Types

Type Description Example
INT Integer numbers age INT
VARCHAR(n) Variable-length string name VARCHAR(50)
CHAR(n) Fixed-length string country_code CHAR(2)
TEXT Long text data description TEXT
DATE Date values (YYYY-MM-DD) birth_date DATE
DATETIME Date and time created_at DATETIME
BOOLEAN True/False values is_active BOOLEAN
DECIMAL(p,s) Precise decimal numbers price DECIMAL(10,2)

Database Constraints

Constraint Description Example
PRIMARY KEY Uniquely identifies each record id INT PRIMARY KEY
FOREIGN KEY References primary key in another table customer_id INT REFERENCES customers(id)
UNIQUE Ensures all values in column are unique email VARCHAR(100) UNIQUE
NOT NULL Ensures column cannot have NULL value name VARCHAR(50) NOT NULL
CHECK Ensures values meet specific condition age INT CHECK (age >= 0)
DEFAULT Sets default value for column status VARCHAR(20) DEFAULT 'active'

Aggregate Functions

Function Description Example
COUNT() Counts number of rows SELECT COUNT(*) FROM users;
SUM() Calculates sum of numeric values SELECT SUM(price) FROM orders;
AVG() Calculates average of values SELECT AVG(age) FROM users;
MAX() Finds maximum value SELECT MAX(salary) FROM employees;
MIN() Finds minimum value SELECT MIN(price) FROM products;

Index Types

Type Description
Clustered Index Determines physical order of data in table; only one per table
Non-Clustered Index Separate structure from data; multiple allowed per table
Unique Index Ensures no duplicate values in indexed columns
Composite Index Index on multiple columns

Detailed Problem Example: E-commerce Database Design

Problem: Design a database for an e-commerce platform that tracks customers, products, orders, and payments. The system needs to handle customers placing multiple orders, each order containing multiple products, and each order having one payment.

Initial Design Issues (Before Normalization)

Let's look at an example of what NOT to do - an unnormalized table:

order_id customer_name customer_email product_name product_price quantity payment_method order_date
1001 John Smith john@email.com Laptop 999.99 1 Credit Card 2023-05-15
1001 John Smith john@email.com Mouse 25.99 2 Credit Card 2023-05-15
1002 Jane Doe jane@email.com Keyboard 79.99 1 PayPal 2023-05-16

Problems with This Design

Problem Description Example from Table
Data Redundancy Customer information is repeated for each product in an order John Smith's name and email appear twice for order 1001
Update Anomaly Changing customer info requires updating multiple rows If John changes his email, we need to update multiple rows
Insertion Anomaly Can't add a new customer without placing an order We can't store customer info without an associated order
Deletion Anomaly Deleting an order might lose customer information If we delete all orders for a customer, we lose their info too

Solution: Normalized Database Design

Breaking down the database into normalized tables:

1. Customers Table (in 3NF)

customer_id customer_name customer_email
1 John Smith john@email.com
2 Jane Doe jane@email.com

2. Products Table (in 3NF)

product_id product_name product_price
101 Laptop 999.99
102 Mouse 25.99
103 Keyboard 79.99

3. Orders Table (in 3NF)

order_id customer_id order_date payment_method
1001 1 2023-05-15 Credit Card
1002 2 2023-05-16 PayPal

4. Order Items Table (Junction Table)

order_id product_id quantity
1001 101 1
1001 102 2
1002 103 1

Benefits of Normalized Design

Benefit Description Example
Eliminates Redundancy Each piece of data is stored only once John Smith appears only once in customers table
Prevents Update Anomalies Changing data requires updating only one row Changing John's email requires only one update
Enables Flexibility Can add customers without orders, products without orders We can add a new customer before they place an order
Improves Data Integrity Foreign key constraints maintain consistency Can't create an order with non-existent customer

Sample Queries for the Normalized Design

Here are some example queries that demonstrate how the normalized design works: