Note for exam candidates: Sections marked with ☁️ AWS are directly relevant to the AWS Certified Cloud Practitioner (CLF-C02) exam. Key exam domains covered in this lecture:
Domain 2: Security & Compliance — database access control concepts
1. Career Pathways Mentioned
The instructor highlighted that students can pursue several IT paths:
Cloud Engineer
DevOps / System Administration
Data Analyst ← focus of this module
For Data Analyst (Junior Role), you need:
Strong SQL / database knowledge
Python (for data visualization)
Libraries: matplotlib, NumPy
Good job market in Malaysia, especially in fintech
2. What is Data & a Database?
Term
Definition
Data
A piece of information (e.g., one record about a country)
Database
A collection of data, organized into files, tables, or objects
Table
A collection of records; has columns (attributes) and rows (records)
Record / Row
A single unit of data representing one entity
Column
Holds the same type of data for all records
Think of it like an Excel sheet — first row = column headers, each subsequent row = one record.
3. Data Models
Model
Description
Example
Relational
Structured tables with predefined relations
MySQL, PostgreSQL, Aurora
Semi-structured
Partially organized, flexible
XML, CSV
Entity-Relationship (ER)
Visual model of entities and their relationships
ER diagrams
Object-Based
Data stored as objects
JSON (MongoDB, DocumentDB)
4. Relational Database
Built on mathematical relational algebra
Data stored in tables with rows and columns
Requires a fixed schema defined before inserting data
Also called: SQL database or CQL database
Uses Structured Query Language (SQL) for querying
Types of Relationships
Relationship
Description
Example
One-to-One
One record relates to exactly one record in another table
Passport ↔ Person
One-to-Many
One record relates to multiple records in another table
Country → Cities
Many-to-Many
Multiple records relate to multiple records
Students ↔ Subjects
Examples of Relational Databases
MySQL
PostgreSQL
Amazon Aurora ☁️
Microsoft SQL Server
Oracle DB
IBM DB2
5. Schema
A schema is a blueprint for a table (or whole database)
Defines: column names, data types, and relationships between tables
Analogous to a class in OOP (a blueprint for objects)
Can be modified later using ALTER TABLE
Schema Example — Country Table
Column
Data Type
Notes
code
CHAR(3)
Fixed-length, 3 characters
name
VARCHAR(100)
Variable-length, up to 100 chars
continent
VARCHAR(50)
Variable-length string
region
VARCHAR(50)
Variable-length string
6. SQL — Structured Query Language
SQL has several sub-groups:
Category
Full Name
Purpose
Common Commands
DDL
Data Definition Language
Define or modify database structure
CREATE, ALTER, DROP, TRUNCATE, RENAME
DML
Data Manipulation Language
Insert, update, or delete data
INSERT, UPDATE, DELETE, MERGE
DQL
Data Query Language
Retrieve data from tables
SELECT
DCL
Data Control Language
Manage user permissions and access
GRANT, REVOKE
TCL
Transaction Control Language
Manage transactions and data consistency
COMMIT, ROLLBACK, SAVEPOINT
Convention: SQL keywords are written in UPPERCASE (e.g., SELECT, CREATE TABLE).
7. SQL Data Types
Character Types
Type
Description
CHAR(n)
Fixed-length string of exactly n characters
VARCHAR(n)
Variable-length string, max n characters
Numeric Types
Type
Description
Use Case
SMALLINT
Small whole number (~2 bytes)
Age, small counts
INT / INTEGER
Standard whole number (~4 bytes)
IDs, quantities
BIGINT
Large whole number (~8 bytes)
Large IDs, big counts
DECIMAL(p, s)
Fixed decimal with p total digits, s after decimal
Currency
FLOAT / REAL
Floating-point number
Scientific values
Memory tip: Choose the smallest data type that fits your needs to save storage.
e.g., RGB values (0–255) → use SMALLINT, not BIGINT
Date & Time Types
Type
Description
DATE
Stores a date
TIME
Stores a time
TIMESTAMP
Stores date + time together
8. Constraints
Constraint
Description
NOT NULL
Field is required; cannot be empty
UNIQUE
All values in this column must be different
DEFAULT value
Used when no value is provided
PRIMARY KEY
Uniquely identifies each row in the table
FOREIGN KEY
References the primary key of another table; creates a relation
9. Primary Key vs Foreign Key
Primary Key
Foreign Key
Purpose
Uniquely identifies a row in its own table
Links a row to a row in another table
Quantity per table
Only one per table
Can have zero or more
Uniqueness
Must be unique
Can repeat (e.g., many cities share same country code)
Related Key Terms
Term
Description
Candidate Key
Any column (or combination) that could be a primary key (e.g., student ID, IC number)
Composite Key
A primary key made of two or more columns (e.g., country name + city name)
Example: city table has country_code column → this is a foreign key referencing country.code (primary key).
Use it to find which country a city belongs to.
10. Creating a Table — DDL Syntax
CREATE TABLE IF NOTEXISTS city (
id INTNOT NULLPRIMARY KEY,
name VARCHAR(20) DEFAULTNULL,
country_code VARCHAR(25) NOT NULL,
district INTNOT NULL
);
⚠️ Warning:DROP TABLE permanently deletes the table and all its data. Always backup first (e.g., to S3).
Modifying a Table (Schema Change):
ALTER TABLE city ADDCOLUMN population INT;
ALTER TABLE city RENAME TO cities;
11. Naming Conventions
Use purposeful names: users, orders, products — not table1, table2
Choose one convention and stick to it:
camelCase → countryCode
snake_case → country_code ✅ (more common in SQL)
Do not use SQL reserved keywords as identifiers (e.g., SELECT, DROP, ADD)
Identifiers = table names, column names (similar to variable names in Python)
12. Relational vs Non-Relational (NoSQL) Database
Feature
SQL (Relational)
NoSQL (Non-Relational)
Schema
Fixed, predefined
Flexible, schema-less
Data format
Tables (rows & columns)
JSON-like documents
Relations
Yes (FK, PK)
No built-in relations
Query language
SQL (standardized)
Varies by engine
Scaling
Vertical scaling
Horizontal scaling
Transactions (ACID)
Fully supported natively
Limited / costs extra
Complex queries
Excellent
Poor
Large data retrieval
Slower
Faster
Security defaults
Stronger
Weaker by default
Best for
CRM, ERP, financial apps, transactional apps
Real-time apps, big data, RAG systems
Examples
SQL Databases
NoSQL Databases
MySQL
MongoDB
PostgreSQL
Amazon DynamoDB ☁️
Amazon Aurora ☁️
Amazon DocumentDB ☁️
Microsoft SQL Server
Redis
13. Transactions
What is a Transaction?
A transaction is a collection of database operations that must be executed as a single unit — either all succeed or all fail (no partial completion).
Classic Example — Money Transfer:
Deduct $100 from Account A
Add $100 to Account B
Both steps must succeed together. If step 2 fails, step 1 must be rolled back.
Transaction States
BEGIN → ACTIVE → PARTIALLY COMMITTED → COMMITTED → END
↓
FAILED → ABORTED → END
State
Description
Begin
Transaction starts
Active
Operations are executing
Partially Committed
All operations done, pending final commit
Committed
All operations saved permanently
Failed
An operation failed
Aborted
Database rolled back to original state
14. ACID Properties
Property
Description
Example
Atomicity
All operations complete, or none do
Transfer either fully completes or fully reverts
Consistency
Transaction does not violate any database constraints
Account balance must not go below $20
Isolation
Concurrent transactions don't interfere with each other
Two simultaneous transfers don't corrupt balances
Durability
Once committed, changes are permanently saved
Data survives a system crash after commit
Key point: SQL databases support ACID natively. NoSQL databases have limited ACID support — transactional operations often cost extra (e.g., DynamoDB charges more for transactions).
15. Database Roles
Role
Responsibilities
Application Developer
Builds apps (web, mobile, desktop) that interact with the database; embeds SQL in code
End User
Uses the application; never sees SQL directly
Data Analyst
Queries data (SELECT), cleans data, visualizes/interprets data
Database Administrator (DBA)
Designs, implements, and maintains the database; manages all SQL operations
Database Engineer
Database design, strategy, scalability, integration — more architectural than DBA
16. Data Interaction Models
Client-Server Model
[Client / User] ←──────→ [Database Server]
Client communicates directly with the database server
inventory — book ID, title, author, publisher, published year, ISBN, product type, retail price
sales_report — title, retail price, quantity, net price after discount
event_schedule — author appearances
Key takeaway: Before designing a database, always gather information from existing documents (forms, spreadsheets, reports) to identify what data you need and what types they are.
20. Quick Reference — KC Quiz Answers
Question
Answer
What is a database?
A collection of data organized into tables
What are elements of a relational database?
Tables and columns
What type of database is "referred to as SQL database"?
Relational
What is the purpose of DBMS?
Software to create, manage, and interact with a database
What are the two data interaction models?
Client-server model & 3-tier web application model
Which ACID property ensures all-or-nothing operations?
Atomicity
What does the "I" in ACID stand for?
Isolation
When is a transaction considered successful?
When it reaches Committed state
What is the primary benefit of DBaaS?
Reduces cost of installing and maintaining servers
What is NOT a DBA responsibility?
Creating different applications (that's the Application Developer)
📝 Coming Up Next (Next Lecture)
Date/Time data types
Knowledge Check on today's material
Lab: Hands-on SQL — creating tables, defining schemas, primary & foreign keys in practice