There are 3 exams and Activities that represent the total grade. Exams scheduled prior to the due receive full credit.

Exam past due date or retakes are also available however the total credit will be maximized at 80% credit.

Approved Course Texts/Readings:

PostgreSQL: Up and Running, 3rd Edition

By Regina O. Obe and Leo S. Hsu

Publisher:O'Reilly Media, Inc.

 October 2017

Readings

The website https://www.postgresql.org/docs/ is an excellent reference for all things SQL. 

Approved Course Texts/Readings:

PostgreSQL: Up and Running, 3rd Edition

By Regina O. Obe and Leo S. Hsu

Publisher:O'Reilly Media, Inc.

 October 2017

Readings

The website https://www.postgresql.org/docs/ is an excellent reference for all things SQL. 

COURSE SCHEDULE

Pre: Setup Environment ( During Orientation )

• Installation of PostgreSQL using IntelliJ and Docker
• Configuring PostgreSQL instance using Docker yaml

Week 1: Introduction to Relational Databases and PostgreSQL Basics

• Day 1: Introduction to Relational Databases
  • Overview of relational databases
  • Explanation of SQL and language specific SQL
  • Introduction to PostgreSQL
• Day 2: Data Import
  • Connecting to the PostgreSQL server in IntelliJ
  • Importing data from CSV files
  • Introduction to database normalization
• Day 3: Basic SQL Queries
  • Understanding the SELECT statement
  • Retrieving data from a single table
• Day 4: More Advanced SQL Queries
  • Filtering data using WHERE clause
  • Sorting data using ORDER BY clause
  • Limiting and offsetting results

Week 2: Database Design and Data Manipulation

• Day 5: Database Design Fundamentals
  • Normalization introduction 1st Form and 2nd Form
  • Data Types
  • Designing tables with appropriate data types
• Day 6: Creating and Modifying Tables
  • Creating tables in PostgreSQL
  • Modifying table structure (ALTER TABLE)
• Day 7: Data Manipulation Language (DML)
  • Inserting data into tables
  • Updating and deleting data
  • Overview of transactions
• Day 8: Constraints and Indexes
  • Understanding constraints (PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK)
  • Creating and managing indexes
  • Table indexes

Week 3: Advanced SQL Queries and Joins

• Day 9: Advanced SELECT Queries
  • Using aggregate functions (SUM, AVG, COUNT, etc.)
  • Grouping data with GROUP BY
  • Filtering grouped data with HAVING
• Day 10: Subqueries and Common Table Expressions (CTEs)
  • Writing subqueries
  • Utilizing Common Table Expressions
  •  
• Day 11: Inner Joins
  • Understanding inner joins
  • Writing inner join queries
• Day 12: Outer Joins
  • Exploring left, right, and full outer joins
  • Handling null values

Week 4: Data Management

• Day 14: Views, Triggers, and Stored Procedures
  • Creating and managing views
  • Writing and executing stored procedures
  • Writing Triggers
• Day 15: Normalization Advanced
  • 3rd and 4th Order
  • Practical conversion to higher normalization
• Day 16: Final Project
• Day 17: Final Project

Final Project

• Week 4: Final Project
  • Students will work on a small project applying the knowledge gained throughout the course. This could involve designing a database schema, writing SQL queries, and implementing basic data manipulation tasks.

Enablement

• Daily/Weekly quizzes to test understanding of concepts covered during the week.
• Practical (hands on keyboards) exercises to accompany / reinforce presentation.

Course Objectives / Learning Outcomes:

1. Recognize the basic concepts of the analysis and design of a database.

2. Summarize the process of Database Development and Administration using SQL.

3. Enhance Programming and Software Engineering skills and techniques using SQL.

4. Develop a basic understanding of background materials needed for technical support using SQL.

5. Solve Database problems using PostgreSQL by applying SQL commands to

Create, Insert, Update, and Retrieve a simple database.

6. Recognize the services provided by a Database Management System.

7. Recognize the use and application of the Relational Database Model.

8. Recognize Transaction Processing and Multi-user Database support.

9. Recognize the difference between a Production transaction database and a

Data Warehouse.

10. Recognize the Client/Server structures used in Database Management Systems.

11. Develop and complete the design and Implementation of a basic database solution using the PostgrSQL Database Management System.