Databases

A database is an organised, persistent collection of data stored electronically. Unlike simple flat-file databases (like a single spreadsheet), which are prone to data redundancy and inconsistency, a relational database organises data into one or more structured tables. This model is the foundation of most modern data management systems.

### Structure of a Relational Database

A relational database consists of tables, which are also known as relations. Each table is designed to hold information about a specific type of entity, such as 'Students' or 'Books'.

* Tables (Relations): A grid-like structure containing all the data for a single entity.

* Records (Rows/Tuples): Each row in a table represents a single instance of that entity. For example, one row in a 'Students' table would contain all the information about one specific student.

* Fields (Columns/Attributes): Each column in a table represents a specific piece of information about the entity. For instance, a 'Students' table might have fields for 'StudentID', 'FirstName', 'LastName', and 'DateOfBirth'.

Each field is assigned a specific data type to ensure data integrity. Common data types include:

* Text/String: For alphanumeric characters (e.g., names, addresses).

* Number (Integer/Real): For numerical data (e.g., age, price).

* Boolean: For data with only two possible values, such as True/False or Yes/No.

* Date/Time: For storing dates and times in a specific format.

### The Primary Key

To ensure every record in a table is unique and can be reliably identified, we use a primary key. A primary key is a field (or a combination of fields) that contains a unique value for each record. For example, in a 'Students' table, the 'StudentID' would be an excellent primary key because no two students can have the same ID. This uniqueness is crucial for accurately retrieving, updating, and linking data between tables.

### Database Management System (DBMS)

The software used to create, query, and manage databases is called a Database Management System (DBMS). A DBMS provides an interface for users and applications to interact with the database, handling tasks like data security, backups, and enforcing data integrity rules. Examples include MySQL, Microsoft SQL Server, and Oracle Database.

### Structured Query Language (SQL)

Structured Query Language (SQL) is the standard programming language used to communicate with a relational database. It allows us to perform various operations, collectively known as data manipulation. The most common SQL commands are:

* Process: `SELECT field1, field2 FROM TableName WHERE condition;`

* Example: To find the names of all students in Grade 10:

`SELECT FirstName, LastName FROM Students WHERE Grade = 10;`

* To retrieve all fields for a record, we use the wildcard `*`:

`SELECT * FROM Students WHERE StudentID = 'S105';`

* Process: `INSERT INTO TableName (field1, field2) VALUES (value1, value2);`

* Example: To add a new student:

`INSERT INTO Students (StudentID, FirstName, LastName, Grade) VALUES ('S121', 'Ali', 'Khan', 9);`

* Process: `UPDATE TableName SET field1 = newValue1 WHERE condition;`

* Example: To change Ali Khan's grade to 10:

`UPDATE Students SET Grade = 10 WHERE StudentID = 'S121';`

* Process: `DELETE FROM TableName WHERE condition;`

* Example: To remove the student with ID 'S121':

`DELETE FROM Students WHERE StudentID = 'S121';`

### Advanced Querying with SQL

SQL's `WHERE` clause can be combined with operators for more complex searches:

* Comparison Operators: `=`, `>`, `<`, `<>` (not equal).

* Logical Operators: `AND` (both conditions must be true), `OR` (at least one condition must be true), `NOT` (reverses the result of a condition).

* LIKE: Used for pattern matching in text fields (e.g., `WHERE FirstName LIKE 'A%'` finds all names starting with 'A').

Data can also be sorted using the ORDER BY clause.

* Process: `SELECT * FROM TableName ORDER BY fieldName ASC/DESC;`

* Example: To list all students alphabetically by their last name:

`SELECT * FROM Students ORDER BY LastName ASC;`