Flat files

Efficient for basic data but lack structure and querying abilities, making them not viable for massive data sets

Relational Databases

Offer structure and powerful querying, but struggle with scaling and performance as data volume grows

Relational Databases (MySQL & SQL Server)

MySQL

• Stores data in table files

• Open-source, lightweight

• Ideal for smaller scale applications

SQL Server

• Stores data in data and transaction log files

• Diverse storage engines for optimization

• Emphasis on scalability and security

• Suitable for mission critical applications with large data sets

Primary Keys

Uniquely identifies each row in a table

Secondary Keys

An alternative way to access rows but does not have to be unique

Foreign Keys

Establishes a link between two tables for consistency and integrity

Candidate Keys

Any set of columns that can uniquely identify rows in a table

<br>

SELECT

<br>

SELECT columns

FROM table_name

[WHERE conditions]

[ORDER BY column1, column2…]

<br>

INSERT

<br>

INSERT INTO table_name (column1, column2)

VALUES ( value1, value2,…);

<br>

JOIN

SELECT columns

FROM table1

INNER JOIN table2

ON table1.column = table2.column;

<br>

UPDATE

UPATE table_name

SET column1 = value1, column2 = value2,

WHERE conditions;

<br>

DELETE

DELETE FROM table_name

WHERE conditions;

<br>

Examples:

SELECT

SELECT first_name , last_name FROM customers WHERE city = 'New York' ORDER BY last_name;

<br>

INSERT

INSERT INTO employees ( name , department , salary)

VALUES ( 'John Doe' , 'Sales' , 50000);

<br>

JOIN

SELECT customers.name , orders.order_date

FROM customers

INNER JOIN orders

ON customer.customer_id = orders.customer_id;

<br>

UPDATE

UPDATE products

SET price = price * 1.1

WHERE category = 'Electronics' ;

<br>

DELETE

DELETE FROM employees

WHERE department = 'Marketing'

<br>

Query Strings

Quick searches & filters which are good for basic tasks but struggle with complex logic

<br>

Stored Procedures

Pre-written code blocks which are better for performance & security for complex operations

<br>

Database connections

Efficiently link apps to the database which is crucial to managing high traffic In large data systems.