Relational Database Management Systems (RDBMS)

Programme Outline

Learning Objectives

• Understand the types of data and databases
• Appreciate the use of data dictionary and harness the potential metadata for data science
• Acquire organisational dataset from data lakes and other democratised data sources for d. data enrichment purposes
• Create a RDBMS to support Data Science pipeline
• Familiar with common RDBMS tools for ETL process
• Deconstruct complex relational tables
• Leverage RDBMS to support big structural data preparation works for Data Science

Day 1

• Types and Components of IT Infrastructure
• File-based System vs Database Management System
• Applications of Database Management System
• Types of Data Models
• Phases of Database Designing
• First Phase: Requirement Collection and Analysis
• Second Phase: Conceptual Design
• Third Phase: Logical Design
• Fourth Phase: Physical Design

Day 2

• Introduction of Structured Query Language (SQL): DDL, DML, TCL, DQL, DCL
• Creating Database with Data Definition Language (DDL)
• Type of SQL Constraints
• Modifying Database with Data Manipulation Language (DML)
• Retrieving data from single table with Data Query Language (DQL)
• Types of SQL operators: Logical, Comparison and Arithmetic
• Constructing queries with comparison, logical and arithmetic operators

Day 3

• Using functions related text searches: String and Wildcard functions
• Using functions related date searches: Data and Time functions
• Using SQL aggregate functions: Count, Sum, Avg, Min, Max
• Using other clauses: Distinct, Fetch First, Top and Limit clauses
• Retrieving data from multiple tables with Data Query Language (DQL)
• Types of join operators: Inner, Full Outer, Left Outer, Right Outer joins
• Using set operators: Union, Intersect, Except

Day 4

• Constructing nested queries
• Advanced SQL features: Views, Triggers, Functions
• Writing efficient conditional expression
• Understanding distributed database management systems
• Business Intelligence and Data Warehouses
• Big Data and NoSQL
• Database Administration and Security

Day 5

• Project Consultation (half-day)

Day 6

• Project Presentation (half-day)

Assessment

• In-class quizzes
• Individual assessments
• Group project