DATABASES - CS101

Joins

Database Inner join

Retrieves records where there is a match in both tables

SELECT column1, column2

Definition FROM example_table

A database is a structured collection of data that is organized and stored in a computer INNER JOIN another_table
system. It allows for efficient data storage, retrieval, and management.
ON example_table.column1 = another_table.column1;

Key Database Terminology Left join

Retrieves all records from the left table and matching records from the right table

SELECT column1, column2

Table Row/Record Column/Field Primary key Foreign key
FROM example_table

A table is a A foreign key is a LEFT JOIN another_table

collection of data A primary key is a field in a table
A row or record is A column or field unique identifier ON example_table.column1 = another_table.column1;
organized into that links to the
a single entry in a is a vertical for each record in
rows and primary key in
table containing section in a table a table, ensuring
columns. It is the another table,
data related to a representing a
basic structure
of a relational
specific entity. specific data
consistency and
enabling efficient
establishing a
relationship
Right join
attribute. data retrieval. Retrieves all records from the right table and matching records from the left table
database. between the two
tables.
SELECT column1, column2
FROM example_table

SQL (Structured Query Language) RIGHT JOIN another_table

ON example_table.column1 = another_table.column1;
Definition
SQL is a language used for managing and manipulating relational databases.
Full outer join
Retrieves all records when there is a match in either table

Data Types SELECT column1, column2

FROM example_table
FULL OUTER JOIN another_table
Numeric Text Date Boolean ON example_table.column1 = another_table.column1;

A data type that

represents
numeric values, A data type for A data type for A data type
Indexes
such as integers storing character storing date representing
and numbers strings. For values. For true/false values. Command to create index
with decimal example, example, DATE, For example, Creates an index on one or more columns of a table
points. For VARCHAR, etc. etc. BOOL, etc.
example, INT, CREATE INDEX index_name
DEC, etc. ON example_table (column1, column2);

Command to show index

Creating and Managing Tables and Views Displays the indexes on a table

SHOW INDEX
Command to create table
Creates a new table with specified columns and data types FROM example_table;

CREATE TABLE example_table (

Command to drop index
column1 INT, Removes an index from a table

column2 VARCHAR(50), DROP INDEX index_name

column3 DATE ON example_table;
);

Command to alter table Aggregate Functions

Modifies an existing table by adding a new column
Definition
ALTER TABLE example_table Aggregate functions operate on a set of values and return a single result.
ADD column4 FLOAT;
Count function
Counts the number of non-null entries in a column. To count all rows including null values, use COUNT(*).
Command to drop table
Removes an existing table and its data SELECT COUNT(column1)
DROP TABLE example_table; FROM example_table;

Sum function
Querying Data Calculates the sum of a numeric column

SELECT SUM(column1)
Command to select data FROM example_table;
Retrieves data from a table

SELECT column1, column2

Avg function
FROM example_table; Calculates the average of a numeric column

SELECT AVG(column1)
Command to filter data FROM example_table;
Filters the result set based on specified conditions

SELECT column1, column2 Min function

Finds the minimum value in a column
FROM example_table
WHERE column1 >= 3; SELECT MIN(column1)
FROM example_table;

Manipulating Data Max function

Finds the maximum value in a column

Command to insert data SELECT MAX(column1)

Adds new records to a table
FROM example_table;
INSERT INTO example_table (column1, column2)
VALUES (7, “it is a text entry”); Normalization
Command to update data Definition
Modifies existing records in a table
Normalization is the process of organizing data in a database to reduce redundancy
and improve data integrity.
UPDATE example_table
SET column1 = 8, column2 = “another text” Benefits

WHERE column1 > 5; Reduces the likelihood of Improves data integrity and
Eliminates data duplication
data anomalies consistency

Command to delete data

Removes records from a table
Backup and Restore
DELETE FROM example_table
WHERE column1 > 5; Backup: Regularly create backups of the database to prevent data loss in case of system failures or errors.
Restore: Restore the database from a backup to recover lost or corrupted data.
DATABASES - CS101
RDBMS
Definition ACID Properties
A relational database organizes data into tables, each consisting of rows and columns, Guarantees the reliability of database transactions
and establishes relationships between these tables.
Atomicity

Popular DBMS Ensures that a transaction is treated as a single, indivisible unit

Consistency
Microsoft SQL Ensures that a transaction brings the database from one valid state to another
MySQL PostgreSQL SQLite Server Oracle
Isolation

SQL (Structured Query Language) Ensures that the execution of one transaction is isolated from other transactions

Durability
Definition
Ensures that committed transactions survive permanently
SQL is a standardized language for managing and manipulating relational databases.

Describe the Table

SQL
Definition
Provides information about the table structure and constraints
DDL DML DQL DCL TCL
Describe command
Provides information about the table structure and constraints.
DDL (Data Definition Language)
It includes commands to define and manage database objects, e.g., CREATE, ALTER, TRUNCATE, DROP, etc. DESCRIBE employee

DML (Data Manipulation Language)

It includes commands to manage and manipulate data in the database objects, e.g., INSERT, DELETE, etc. CRUD Operations
CRUD (Create/Read/Update/Delete) operations are fundamental database operations that can be performed
DQL (Data Query Language) on data and tables. CRUD operations are illustrated below with the help of the employee table.
It includes commands to retrieve and filter data from the database objects, e.g., SELECT, WHERE, etc.
employee_id first_name last_name salary age
DCL (Data Control Language)
It includes commands to manage access and permission for database objects, e.g., GRANT, REVOKE, etc.
1 John Doe 50000 19
TCL (Transaction Control Language)
It includes commands to manage transactions within the database, e.g., COMMIT, ROLLBACK, etc.
2 Sam Robert 55000 20

More Terminology and Concepts

3 Michael William 60000 18
Schema
A schema is a collection of database objects, including tables, views, and procedures,
grouped together under a common name. 4 Allen George 40000 21

Normalization
5 Davis Brown 45000 22
The process of organizing data to eliminate redundancy and improve data integrity.

1NF (First Normal Form)

Ensures that each column contains atomic values Create operation
The CREATE TABLE command is used to perform this operation at the table level.
2NF (Second Normal Form) The INSERT INTO command is used to perform this operation at the data level.
Eliminates partial dependencies by ensuring all non-prime attributes are fully functionally dependent on
the primary key Read operation
The DESCRIBE command is used to perform this operation for the table structure.
3NF (Third Normal Form) The SELECT command is used to perform this operation for the data retrieval.
Removes transitive dependencies, ensuring that non-prime attributes are not dependent on other
non-prime attributes Update operation
The ALTER TABLE command is used to perform table modification.
BCNF (Boyce-Codd Normal Form) The UPDATE command is used for updating data.
It ensures that there are no non-trivial functional dependencies on the super key. The following are the
related concepts: Delete operation
Primary key: The primary key is the selected candidate key used as the main identifier for records in a The DROP TABLE command is used to delete an entire table, not individual rows of data. It removes the
table, ensuring uniqueness and serving as the primary means of reference. table schema and all its data.
The DELETE FROM command is used to remove one or more rows from a table while retaining the table
Candidate key: The candidate key is a minimal super key, chosen from the set of super keys, that structure.
uniquely identifies each record in a table.

Super key: A set of attributes that, when taken together, uniquely identify each record in a table. More on Managing Tables
Integrity Constraints
Ensures the accuracy and consistency of data Rules are applied to data columns to enforce data integrity.

Entity integrity Unique constraint

Ensures that values in a column are unique
Ensures that each row in a table has a unique identifier
ALTER TABLE employee
Referential integrity
ADD CONSTRAINT unique_email UNIQUE (email);
Ensures that foreign key values reference existing primary key values

Not null constraint

Index Types Ensures that a column cannot have NULL values

These refer to indexing methods to enhance data retrieval performance. Some popular types are primary CREATE TABLE employee (
index, unique index, clustered index, composite index, and bitmap index.
employee_id INT NOT NULL,
first_name VARCHAR (50) NOT NULL,
Relationships );
Describes how tables are related to each other
Check constraint
Enforces a condition on the values that can be entered into a column
Relationships ALTER TABLE employee
ADD CONSTRAINT check_age CHECK (age >= 18);

1-1 (One-to-One) 1-m (One-to-Many) m-n (Many-to-Many)

Default constraint
Provides a default value for a column, which is used when its value is not mentioned in the
Multiple records in INSERT command
A single record in A single record in one
one table can be
one table is related table can be related to
related to multiple
to a single record in multiple records in ALTER TABLE employee
records in another
another table. another table.
table. MODIFY status VARCHAR (20) DEFAULT ‘Active’;
DATABASES - CS101
More on Filtering Data
Presentation TCL (Transaction Control Languages)
Techniques for presenting and filtering data Manages transactions within the database

Rollback
Distinct clause
Returns distinct values in a row and column Reverts the database to the last committed state

SELECT DISTINCT first_name

Savepoint
FROM employee; Sets a point in a transaction to which you can later roll back

Order by clause Commit

Sorts the result set by specified rows and columns
Makes changes in a transaction permanent
SELECT first_name, salary
FROM employee BEGIN;

ORDER BY salary; UPDATE employee

SET salary = 1000

Limit clause
Limits the number of rows and columns returned in the result set WHERE salary > 1000;

SELECT * SAVEPOINT my_savepoint;

FROM employee UPDATE employee
LIMIT 10; SET age = 10

WHERE > 2;
Matching Rows ROLLBACK TO my_savepoint;
Techniques for matching rows based on certain criteria COMMIT;

In operator
Filters rows based on a list of specified values DCL (Data Control Language)
SELECT * Manages access and permissions of tables and other objects

FROM employee
Grant function
WHERE salary IN (50000, 60000, 70000); Provides specific privileges to a user

GRANT SELECT ON employee TO user_name;

Between operator
Filters rows based on a range of values
Revoke function
SELECT * Removes specific privileges from a user

FROM employee
employees REVOKE SELECT ON employee FROM user_name;
WHERE salary BETWEEN 10 AND 1000;
Role function
Users are individual accounts, while roles are collections of privileges assigned to users
Like operator
Filters rows based on a specified pattern CREATE ROLE my_role;

SELECT * GRANT my_role TO user_name;

FROM employee
WHERE check_age LIKE ‘pattern%’;

And, or, not operators

Logical operators are used to combine conditions

SELECT *
FROM employee
WHERE salary > 10 AND age > 20;
WHERE salary > 100 OR age < 20;
WHERE NOT salary < 2

Any operator
Compares a value to any value returned by a subquery and returns true if the comparison is true for
at least one value

SELECT *
FROM employee
WHERE salary = ANY (SELECT salary FROM employee WHERE age > 20);

All operator
Compares a value to all values in a result set

SELECT *
FROM employee
WHERE salary > ALL (SELECT salary FROM employee WHERE age > 20);

More on Aggregate Functions

Aggregates are functions that perform a calculation on a set of values and return a single value.

Group by clause
Groups rows based on specified columns

SELECT salary, COUNT(*)

FROM employee
GROUP BY salary;

Having clause
Filters the results of a GROUP BY clause based on a specified condition on the result of an aggregate
function

SELECT salary, COUNT(*)

FROM employee
GROUP BY salary
HAVING COUNT (*) > 1;

Database Administration
How to backup data
Regularly creating backups to prevent data loss

BACKUP DATABASE database_name TO disk = 'backup_location';

How to recover data

Command for recovering data in case of failures

RESTORE DATABASE database_name FROM disk = 'backup_location';