Fundamentals of Database Management Systems (Classroom and Asynchronous e-learning)
About This Course
The module covers the fundamental concepts to build and work with data pipelines. Participants are taught how to work with relational database management system, non-relational (NoSQL) database, and large data stores such as data warehouses and how to integrate data from multiple data sources into a single repository using Extract, Transform and Load (ETL) workflows. Students are also introduced to concepts of big data and its related challenges.
What You'll Learn
1. Explain the characteristics of structured and unstructured data
2. Explain the differences between transactional (OLTP) versus analytical processing (OLAP)
3. Explain relational terminologies: relation, tuple, keys
4. Explain relational integrity rules: entity and referential integrity rules
5. Explain the purpose of database normalisation
6. Apply database normalisation until 3NF under given scenarios
7. Design Entity Relationship Diagram (ERD) under given scenarios
8. Create Relational Databases under given scenarios
9. Create and maintain tables using CREATE TABLE, DROP TABLE and ALTER TABLE commands
10. Maintain data in tables using INSERT, UPDATE and DELETE commands
11. Formulate simple queries in SQL using SELECT and WHERE clauses.
12. Explain the differences between JOIN and subqueries, and apply them appropriately
13. Formulate queries using GROUP functions, GROUP BY and HAVING clauses
14. Outline Data Warehousing Conceptual Layers.
15. Apply dimensional modelling such as Star and Snowflake Schema to design a data warehouse
16. Create a data warehouse based on business requirements
17. Apply data validation and data cleansing techniques
18. Explain Data Integration, and the Extraction, Transformation and Load (ETL) process
19. Apply ETL process to integrate data from various sources
Entry Requirements
At least a Higher Nitec qualification. Basic English Proficiency, foundational numeracy skills and familiarity with using digital tools such as Excel. Prior experience in programming or data analysis is advantageous but not mandatory. Participants without the necessary qualification can be considered on a case-by-case basis.