Subtopic Notes
9 Databases
9. Databases
Database Terminologies
- Database: Organized collection of non-redundant interrelated data stored electronically
- DBMS (Database Management System): Software that facilitates the creation, maintenance, and manipulation of databases
- Entity: A distinct object or concept that can be identified and stored in a database, often representing a real-world object
- Table: A structured arrangement of data in rows and columns within a database, representing entities
- Record: A single entry in a table, consisting of fields that contain related data about an entity
- Field/Column/Attribute: Contains specific property about a record
- Validation: DBMS automatically provides some validation checks to ensure that data comes in the required format. For example range check for age, format check for dates.
- Primary key: Attribute/s which is the unique identifier
- Primary fields must have values that are never repeated within the table
Example Database (Vehicle Table)
| VIN | Make | Model | Year | Mileage | Body | Price |
|---|---|---|---|---|---|---|
| TYT1 | Toyota | Prado | 2023 | 15000 | SUV | 55000 |
| HND1 | Honda | Vezel | 2022 | 20000 | Crossover | 25000 |
| BUG1 | Bugatti | Veyron | 2012 | 10000 | Supercar | 2200000 |
| HND2 | Honda | CR-V | 2021 | 30000 | SUV | 30000 |
| TYT2 | Toyota | C-HR | 2020 | 25000 | Crossover | 22000 |
| LAM1 | Lamborghini | Gallardo | 2008 | 40000 | Supercar | 150000 |
| ASM1 | Aston Martin | DB12 | 2019 | 12000 | Grand Tourer | 300000 |
Examples from the above database:
- Tables: Vehicle
- Entity: Vehicle
- Record: Row 2 to 8
- Field/Column/Attribute: Make, Model, Year
- Primary Key: VIN
Data Types:
-
CHARACTER
Represents one character only
-
Text/Alphanumeric
Represents a string enclosed in “double quotes” -
Boolean
TRUE/FALSE -
Integer
A whole number (Maybe +ve, -ve or 0) -
Real
A number with decimal -
Date/Time
May use string with dd/mm/yyyy format and HH:mm AM/PM format
<Conditions> SQL Operators
| Operator | Evaluation |
|---|---|
| = | Equals to |
| > | Greater Than |
| < | Less Than |
| >= | Greater than or equal to |
| <= | Less than or equal to |
| <> | Not equal to |
| BETWEEN | Between a range of two values Eg. BETWEEN '2018-08-01' AND '2018-08-31' |
| LIKE | Searching for a patten ‘a%’ Starts with character a ‘a_’ - 1 character after a (Each _ represents 1 character) |
| IS NULL | Check for null values |
| AND | Multiple conditions |
| OR | Multiple conditions |
| NOT | Specify a condition that might be false |
Query General Format
SELECT <Fields/Functions separated by ,>
FROM <table-name>
WHERE <conditions separated by AND or OR>
ORDER BY <field-names> ASCENDING/DESCENDING;
SELECT Command
SELECT *: Selects all the records from specified table
SELECT <field-name>: Returns the select field name/names (separated using ,)
SELECT COUNT(<field-name>): Returns no. of field-names
SELECT SUM(<field-name>): Returns sum of field-names