info.kshrd@gmail.com
(+855) 86 244 123
MediaFAQApply Now

Database Design and SQL

This course covers the principles of database design, including data modeling and normalization. You will learn to create and manage relational databases using SQL, and explore advanced topics such as full-text search and dimensional modeling for data warehouses.

Objective

  • Understand the basics of data modeling and RDBMS.
  • Create and manipulate databases and tables using SQL.
  • Learn about relationships, normalization, and indexing.
  • Write complex SQL queries and stored procedures.
  • Implement dimensional modeling for data warehousing.

Curriculum

Database Overview

  • Data Modeling
  • RDBMS

Databases & Tables

  • DDL
  • Data Type
  • Constraint
  • Data Manipulation
  • Selecting Data
  • Inserting Data
  • Updating Data
  • Deleting Data
  • Returning Data From Modified Rows

Introduction to Data Modeling

  • Overview
  • Data Modeling Techniques
  • Types/Levels of Data Model
  • Information Engineering (IE) Notation

SQL Relationships

  • Types of relationships
  • Identifying and Non Identifying
  • Recursive relationships

Data Modeling Exercise

  • Retail banking system

Normalization

  • Introduction to Normalization
  • First, Second, Third Normal Form
  • Foreign Key Constraints

Exercise

  • Logical data modeling practice - Public Library
  • Solution

Queries

  • Select List Items
  • Select Distinct
  • Where, Group By, Having, From Clause
  • Sorting Rows
  • Table Joins
  • Combine Queries
  • Create Indexes

SQL Function and View

  • PL/pgSQL
  • Stored Procedures
  • Functions
  • Using Built-In Function
  • Event Trigger Function
  • Subqueries
  • Creating Views

Fast Full-Text Search in PostgreSQL

  • PostgreSQL’s full-text search

Dimensional Modeling

  • Dimensional Modeling
  • Create a star schema
  • Documenting Source