Clinical Professor, School of Information
Database Design and Basic SQL in PostgreSQL
What You'll Learn
- Utilize psql and SQL commands to implement CRUD (Create, Read, Update, and Delete) operations for tables in a PostgreSQL database.
- Identify and utilize the functions of primary, logical, and foreign keys within a database.
- Build and differentiate between one-to-many and many-to-many relationships within PostgreSQL.
- Recall key people, organizations, and innovations that were instrumental to building the SQL standard
4 Modules
12 Hours
3 hrs per module (approx.)
Rating
About Database Design and Basic SQL in PostgreSQL
In this course you will learn more about the historical design of databases and the use of SQL in the PostgreSQL environment. Using SQL techniques and common commands (INSERT INTO, WHERE, ORDER BY, ON DELETE CASCADE, etc) will enable you to create tables, column types and define the schema of your data in PostgreSQL. You will learn about data modeling and how to represent one-to-many and many-to-many relationships in PostgreSQL. Students will do hands-on assignments creating tables, inserting data, designing data models, creating relational structures and inserting and querying relational data in tables.
Skills You'll Gain
- Database Design
- PL/pgSQL
- PostgreSQL
- SQL (Programming Language)
What You'll Earn
- Certificate of Completion:
- Certificates of completion acknowledge knowledge acquired upon completion of a non-credit course or program.
Welcome Message
Database Design and Basic SQL in PostgreSQL introduces relational database concepts and hands-on SQL practice. Learners design schemas, create tables, and model one-to-many and many-to-many relationships using PostgreSQL tools and structured query language fundamentals.
This abbreviated syllabus description was created with the help of AI tools and reviewed by staff. The full syllabus is available to those who enroll in the course.
Course Schedule
Module 1: Introduction to SQL
- Video: Welcome to PostgreSQL!
- Reading: Help Us Learn More About You
- Reading: Syllabus
- Video: History of Relational Databases
- Reading: Elizabeth Fong and SQL Standards
- Video: Bonus Video: Elizabeth Fong and SQL Standards
- Video: SQL Architecture
- Reading: Assessment Philosophy
- Reading: How Assignments Work
- Video: Using PythonAnywhere to Run SQL
- Reading: Connecting to Your Database Server
- Video: Using the DBeaver Client to Run SQL Commands
- App Item: A Class Photo Gallery
- App Item: A Map of Student Locations
- Video: Dr. Chuck Goes Racing - Joliet, Illinois, USA
- Video: Office Hours - London (Very close to PythonAnywhere)
- Graded: Introductory SQL
- Graded: Initial Database Setup
- Graded: Making Our First Tables
- Graded: Inserting Some Data into a Table
Module 2: Single Table SQL
- Video: Working with Tables and PostgreSQL
- Video: Data Types in PostgreSQL
- Video: Database Keys and Indexes in PostgreSQL
- Video: Musical Track Database (CSV)
- Video: Dr. Chuck Sings the Blues in Geneva, Switzerland (Near CERN)
- Video: Office Hours - Kyoto, Japan
- Graded: Single Table SQL
- Graded: SERIAL fields / Auto Increment
- Graded: Musical Track Database (CSV)
Module 3: One-To-Many Data Models
- Video: Relational Database Design Part 1
- Video: Relational Database Design Part 2
- Video: Keys
- Video: Database Normalization
- Video: Building Tables
- Video: Inserting Data
- Video: Using JOIN Across Tables
- Video: Office Hours at Bletchley Park, Milton Keynes, England
- Video: Office Hours, Philadelphia, Pennsylvania, USA
- Graded: One to Many Data Models
- Graded: Entering Many-to-One Data - Automobiles
Module 4: Many-To-Many Data Models
- Video: Many-to-Many Relationships
- Video: Demonstration: Database Design and Many to Many
- Video: Up Next - Beyond CRUD (Create, Read, Update, Delete) and JOIN
- Reading: Post-Course Survey
- Video: Office Hours - Capetown, South Africa
- Video: Office Hours - Raleigh, North Carolina, USA
- Graded: Many-to-Many Data Models
- Graded: Building a Many-to-Many Roster
Grading Policy
Assessments are distributed across quizzes and applied database-building assignments totaling 100%. Individual quizzes and graded assignments range from 4% each to 12% of your final grade.
Course content developed by U-M faculty and managed by the university. Faculty titles and affiliations are updated periodically.
Intermediate Level
Completion of the first two courses in the Python for Everybody specialization or an equivalent basic understanding of Python
