Courses / HEC NSCT – National Skill Competency Test: Complete Preparation for Computing Graduates
4.5 Indexing, Query Optimization, NoSQL and Data Warehousing
Area 4 – Databases (10%) · 45 min read · 12 MCQs to check yourself
Indexes
An index is like a book's index – it speeds up searches (WHERE, JOIN, ORDER BY) but slows down inserts/updates and uses extra space.
- B+ tree index (default) – good for equality and range queries.
- Hash index – fast equality only.
- Clustered index – table rows physically stored in key order (only one per table); non-clustered – separate structure pointing to rows.
- Query optimizer chooses the cheapest plan; use
EXPLAINto see it. AvoidSELECT *, functions on indexed columns, and leading%in LIKE.
Security and distribution
- SQL injection – use parameterised queries / prepared statements.
- Least privilege with GRANT/REVOKE; encrypt sensitive columns; backups.
- Replication (copies for availability), sharding/partitioning (split data across servers). CAP theorem: a distributed system can guarantee at most two of Consistency, Availability, Partition tolerance.
NoSQL
| Type | Example | Good for |
|---|---|---|
| Document | MongoDB | JSON-like flexible records |
| Key–value | Redis | Caching, sessions |
| Column-family | Cassandra | Huge write-heavy data |
| Graph | Neo4j | Social networks, recommendations |
| NoSQL systems often follow BASE (Basically Available, Soft state, Eventual consistency) instead of strict ACID. |
Data warehousing and mining
OLTP = day-to-day transactions (normalized); OLAP = analysis on a data warehouse (star/snowflake schema, fact + dimension tables). ETL = Extract, Transform, Load. Data mining finds patterns (association rules, clustering, classification).
What is a database index? (Hussein Nasser)
This lesson ends with a quiz of 12 MCQs. Open the lesson on PrepOasis to take it and save your progress.
Previous: 4.4 Transactions, Concurrency and Recovery · Next: Area 4 Review, Checklist and Area Test