Elect 3 Advance
Database System
Instructor: Rockie C. Oga
Schedule: Monday, 7:30-9:30 AM Lecture
Monday, 9:30-12:30 NN Laboratory
Misamis Oriental Institute of Science and Technology, Inc.
COLLEGE OF INFORMATION TECHNOLOGY
Elect 3 Advance Database System Units 3.0
COURSE
DESCRIPTION
This course covers both Database Administration and MySQL
Server, emphasizing the design, creation, and management
of relational databases. Students will explore advanced SQL
queries, data manipulation, and OLTP/OLAP concepts. The
course also introduces NoSQL databases and their application
in modern systems, along with data warehousing, the ETL
process, and advanced data management techniques.
Practical skills will be developed through real-world
applications, including data warehousing project
implementation.
1
Elect 3 Advance Database System Units 3.0
COURSE OUTCOMES
CO1. Design and create relational databases using MySQL Server with proper
normalization, data types, and indexing.
CO2. Write advanced SQL queries with JOINs, subqueries, and aggregate
functions for data manipulation and analysis.
CO3. Apply OLTP and OLAP concepts to design databases optimized for
transaction processing and analytics.
CO4. Design and implement data warehousing solutions using star and snowflake
schemas, and manage the ETL process.
CO5. Understand and implement NoSQL databases for applications that require
flexible, scalable, and high-performance data storage.
2
Elect 3 Advance Database System Units 3.0
COURSE CONTENT
Week TOPIC NAME
1 Database Administration and MySQL Server Basics, and
NoSQL Databases
2 Designing and Creating Databases with MySQL Server
3 MySQL Server – Data Types, Columns, and Indexing
4 Advanced SQL Queries – JOINS
5 PRELIM Examination
6 Advanced SQL Queries –Subqueries (Part 2)
7 Using MySQL Server – Aggregate Functions and Grouping
8 Advanced Data Manipulation in MySQL
9 MIDTERM Examination
10 OLTP and OLAP Concepts
11 Data Warehousing Concepts (Part 1)
12 Data Warehousing Concepts (Part 2)
13 SEMI-FINAL Examination
14 ETL Process in Data Warehousing (Part 1)
15 ETL Process in Data Warehousing (Part 2)
16 Introduction to NoSQL Databases
17 NoSQL – Data Modeling and Querying
18 FINAL Examination
3
Elect 3 Advance Database System Units 3.0
REFERENCES &
TEXTBOOK
https://dev.mysql.com/doc/refman/8.4/en/mysqld-server.html
https://www.mysql.com/products/workbench/
Pratt, Philip J. Database management systems, eight
edition. Singapore: Cengage Learning Asia, ©2016.
Pepito, Copernicus P. Introduction to SQL server 2008
database programming. Series 1 simple RDBMS
applications. Mandaluyong City: National Book Store,
©2010.
5
Elect 3 Advance Database System Units 3.0
GRADING SYSTEM
6
Elect 3 Advance Database System Units 3.0
COURSE
REQUIREMENTS
Passing Grade: The passing grade for this course is 3.0 or
equivalent to 75%.
Written Outputs: Through the entire course, students are required to
submit as part of the course requirement.
Laboratory Works: Submission of all programming activities and
project presentation are required.
Term Exams: There are four (4) major term examinations during the
course. Passing score for every term exam is 3.0 or 75%.
Removal Exam is applied only after final examinations.
Cheating: Anyone caught cheating or trying to cheat in any manner
will be given a final grade of 70% or 5.0.
7
Elect 3 Advance Database System Units 3.0
IMPORTANT
REMINDERS
1. Be respectful and kind.
2. Be on time and come prepared.
3. Don’t talk when someone is discussing in front.
4. No inappropriate language or no hand gestures.
5. Sit properly.
6. In laboratory classes, always wear foot maps.
7. Do not bring computer accessories outside laboratory.
8. Properly turn-off computers after using.
9. Submit activities, assignment, quizzes on time.
10. Always RESPECT each other.
11. Keep in mind the term CLAYGO (Clean as you go).
12. When going outside class hours, kindly raise your
hand and ask permission before leaving.
8
Elect 3 Advance Database System Units 3.0
CONSULTATION
HOURS
Monday to Friday
4-5 PM
Email:
oga.rockie@moist.edu.ph
Messenger: Rockie Oga
Room: IT Office
9
Elect 3 Advance Database System Units 3.0
Finals Topic 4 – Analytics and Optimization
Analytics and
Optimization
1
Elect 3 Advance Database System Units 3.0
Finals Topic 4 – Analytics and Optimization
OLAP vs. OLTP? Purpose: OLAP for analysis vs. OLTP for operations
OLAP (Online Analytical Processing) is a
technology for analyzing aggregated, historical Data: OLAP uses historical, aggregated data vs.
data from multiple perspectives to support OLTP uses current, detailed data
business intelligence and decision making. It’s
optimized for complex queries that read large Schema: OLAP typically uses star/snowflake
volumes of data. schemas vs. OLTP uses normalized schemas
Performance: OLAP optimized for complex reads vs.
OLTP (Online Transaction Processing) handles
OLTP optimized for fast writes
day-to-day transaction operations like inserting,
updating, and deleting data in real-time. It’s
Users: OLAP for analysts/management vs. OLTP for
optimized for speed and efficiency in
operational staff
processing many simple transactions.
12
Elect 3 Advance Database System Units 3.0
Finals Topic 4 – Analytics and Optimization
Types of OLAP
MOLAP (Multidimensional OLAP):
* Stores data in optimized multidimensional arrays (cubes)
* Fast query performance for predefined dimensions
* Requires pre-aggregation which can lead to data explosion
* Best for predictable, recurring analysis needs
ROLAP (Relational OLAP):
* Works directly with relational databases
* Uses star/snowflake schemas with fact and dimension tables
* No pre-aggregation – computes results on the fly
* More flexible but typically slower than MOLAP
HOLAP (Hybrid OLAP):
* Combines MOLAP and ROLAP approaches
* Stores some aggregates in cubes and accesses detail data
relationally
* Balances performance and flexibility
* Good for scenarios needing both detailed analysis and aggregated
views
13
Elect 3 Advance Database System Units 3.0
Finals Topic 4 – Analytics and Optimization
Elements:
COMPARISON
1.MOLAP (Left): Shows a cube icon
representing multidimensional storage with
pre-aggregated data, emphasizing speed
but with potential storage issues
MOLAP ROLAP HOLAP 2.ROLAP (Middle): Displays traditional
database tables (fact and dimension
(Multidimensional) (Relational) (Hybrid) tables) highlighting relational structure and
query flexibility
CUBE Pre-
FACT & DIMENSION 3.HOLAP (Right): Combines both
aggregation Storage
TABLES approaches with a cube for aggregates and
Fast queries relational tables for details, positioned as
Flexible queries • CUBE for the balanced solution
Pre-computed
On-demand computation Aggregates
aggregation 4.Connecting arrows: Show how HOLAP
Slower performance • RELATIONAL integrates aspects of both MOLAP and
Data explosion risk ROLAP
DETAILS
Balances speed & 5.Text callouts: Briefly note the key
characteristics of each type:
flexibility
1. MOLAP: Fast queries, pre-
computation, storage concerns
2. ROLAP: Flexible, on-demand
processing, slower
14
3. HOLAP: Hybrid approach
balancing both worlds
Elect 3 Advance Database System Units 3.0
Finals Topic 4 – Analytics and Optimization
3. Drill-down:
OLAP Operations
•Moves from higher-level summary to more
1. Slice: detailed data
•Extracts a subset of data by fixing one •Example: From annual sales → quarterly
dimension sales → monthly sales
4. Roll-up (or Drill-up):
•Example: Viewing sales data for just one
region •Aggregates data to a higher level of
2. Dice: abstraction
•Example: From monthly sales → quarterly
•Extracts a subcube by selecting specific
sales → annual sales
values on multiple dimensions
5. Other Operations:
•Example: Viewing sales for specific products
in specific regions during Q1 •Pivot (rotate cube to view different
dimensions)
•Drill-across (combine data from multiple
15
cubes)
Elect 3 Advance Database System Units 3.0
Finals Topic 4 – Analytics and Optimization
Data Warehouse Performance
Optimization
Schema Design:
•Implement star or snowflake
schemas appropriately
•Use surrogate keys for
dimension tables
•Consider slowly changing
dimensions strategies
16
Elect 3 Advance Database System Units 3.0
Finals Topic 4 – Analytics and Optimization
Data Warehouse Performance
Optimization
Indexing Strategies:
•Create bitmap indexes on
low-cardinality dimension
columns
•Use B-tree indexes on high-
cardinality columns
•Implement materialized
views for common queries
17
Elect 3 Advance Database System Units 3.0
Finals Topic 4 – Analytics and Optimization
Data Warehouse Performance
Optimization
Partitioning:
•Partition large fact tables by
date ranges
•Consider range, list, or hash
partitioning based on access
patterns
18
Elect 3 Advance Database System Units 3.0
Finals Topic 4 – Analytics and Optimization
Data Warehouse Performance
Optimization
Query Optimization:
•Use query rewrite with
materialized views
•Optimize join operations (star
join optimization)
•Implement aggregate
awareness in queries
19
Elect 3 Advance Database System Units 3.0
Finals Topic 4 – Analytics and Optimization
Data Warehouse Performance
Optimization
Hardware/Infrastructure:
•Use columnar storage for
analytical workloads
•Consider in-memory
processing for performance-
critical cubes
•Implement proper caching
strategies
20
Elect 3 Advance Database System Units 3.0
Finals Topic 4 – Analytics and Optimization
Data Warehouse Performance
Optimization
ETL Process Optimization:
•Parallelize data loading
processes
•Implement incremental
loading where possible
•Schedule resource-intensive
jobs during off-peak hours
21
Elect 3 Advance Database System Units 3.0
Finals Topic 4 – Analytics and Optimization
Data Warehouse Performance
Optimization
Monitoring and
Maintenance:
•Regularly update statistics
for query optimization
•Monitor and rebuild
fragmented indexes
•Archive old data to maintain
performance
21
Elect 3 Advance Database System Units 3.0
Finals Topic 4 – Analytics and Optimization
THANK YOU!
22
