Skip to main content

Creating PostgreSQL Databases

This course teaches you the skills and knowledge necessary to create and manage your own PostgreSQL databases.

Start Course for Free
4 Hours16 Videos51 Exercises7,520 Learners4100 XPSQL for Database Administrators Track

Create Your Free Account

GoogleLinkedInFacebook

or

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.

Loved by learners at thousands of companies

Course Description

You have experience working with databases or, perhaps, you have heard how useful databases can be for organizing your data. Have you ever wanted to create and manage your own databases but thought such knowledge is reserved for database administrators? Well, it is not! This course teaches you the skills and knowledge necessary to create and manage your own PostgreSQL databases. Topics that will be covered include the structure of PostgreSQL databases, PostgreSQL datatypes, and normalization of databases to efficiently store data and avoid data loss. These topics will be taught using data from the U.S. Small Business Administration (SBA) to guide the lessons and provide context for the concepts covered in the course.

  1. 1

    Structure of PostgreSQL Databases

    Free

    In this chapter, you will be introduced to the fundamental objects in a PostgreSQL database: the database, tables, and schemas. After completing this chapter, you will know how to issue commands to create all three types of objects, and understand how each fits into the hierarchy of PostgreSQL objects.

    Play Chapter Now
    Creating a database
    50 xp
    New database creation
    50 xp
    Motivation for a new database
    50 xp
    Creating tables
    50 xp
    Name that table
    100 xp
    Two tables and a foreign key connection
    100 xp
    Creating schemas
    50 xp
    User-level schemas
    100 xp
    The public schema
    100 xp
    Creating tables in existing schemas
    100 xp
  2. 2

    PostgreSQL Data Types

    In this chapter, you will be introduced to text, numeric, temporal, and boolean data types in PostgreSQL. After completing this chapter, you will be able to define the appropriate data types for table columns based on the data values to be stored.

    Play Chapter Now
    Introduction to PostgreSQL data types
    50 xp
    Matching data representations and categories
    100 xp
    Choosing data types at table creation
    100 xp
    Defining text columns
    50 xp
    Matching text types
    100 xp
    SBA appeals table
    100 xp
    Defining numeric data columns
    50 xp
    Using integer types
    100 xp
    Supporting an SBA marketing campaign
    100 xp
    Defining boolean and temporal data columns
    50 xp
    Revisiting the appeals table
    100 xp
    Boolean defaults
    100 xp
    Choosing data types representations
    100 xp
  3. 3

    Database Normalization

    In this chapter, database normalization is introduced. Database normalization maintains data integrity and reduces data duplication. 1st, 2nd, and 3rd Normal Form are defined as steps in the process of normalizing a database. Examples to clarify concepts are provided throughout the chapter.

    Play Chapter Now
    The importance of data normalization
    50 xp
    Reasons for normalizing databases
    50 xp
    Reducing data redundancy
    100 xp
    Improving object-to-data mapping
    100 xp
    1st Normal Form
    50 xp
    Simplifying database records
    100 xp
    Too much normalization
    100 xp
    2nd Normal Form
    50 xp
    Designing a course table
    100 xp
    Streamlining meal options
    100 xp
    3rd Normal Form
    50 xp
    Identifying transitive dependencies
    50 xp
    Table definitions for 3rd Normal Form
    100 xp
    Working through the normalization process
    100 xp
  4. 4

    Access Control in PostgreSQL

    In this chapter, the topic of access control in PostgreSQL is introduced. Access control is presented as granting and revoking privileges to users and groups on database objects. Using these tools, a database and its objects can be protected from unauthorized access, helping to ensure the integrity of the stored data.

    Play Chapter Now
    Introduction to access control
    50 xp
    Creating a new user
    100 xp
    Updating user passwords
    100 xp
    PostgreSQL access privileges
    50 xp
    Granting user privileges
    100 xp
    Using the granted privileges
    100 xp
    Hierarchical access control
    50 xp
    Working with users and groups
    100 xp
    Schema privileges
    100 xp
    Removing access
    50 xp
    Removing user privileges
    100 xp
    Rescinding group membership
    100 xp
    Implementing access control for teams
    100 xp
    Course wrap-up
    50 xp

In the following tracks

SQL for Database Administrators

Collaborators

maggiematsui
Maggie Matsui
davidventuri
David Venturi

Prerequisites

Intermediate SQL
Darryl Reeves Ph.D Headshot

Darryl Reeves Ph.D

Industry Assistant Professor, NYU Tandon School of Engineering

Darryl is a computational scientist with expertise in utilizing data-driven approaches to solve complex problems in both academic and business settings. He worked for a number of years in a variety of technical roles including software development and technology-based client services mostly within start-up organizations in the finance and online advertising industries. He has a love for technology and education and enjoys solving interesting problems across diverse domains.
See More

What do other learners have to say?

    Join over 10 million learners and start Creating PostgreSQL Databases today!

    Create Your Free Account

    GoogleLinkedInFacebook

    or

    By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.