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.

Creating a database

New database creation

Motivation for a new database

Creating tables

Name that table

Two tables and a foreign key connection

Creating schemas

User-level schemas

The public schema

Creating tables in existing schemas

Structure of PostgreSQL Databases

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.

Introduction to PostgreSQL data types

Matching data representations and categories

Choosing data types at table creation

Defining text columns

Matching text types

SBA appeals table

Defining numeric data columns

Using integer types

Supporting an SBA marketing campaign

Defining boolean and temporal data columns

Revisiting the appeals table

Boolean defaults

Choosing data types representations

PostgreSQL Data Types

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.

The importance of data normalization

Reasons for normalizing databases

Reducing data redundancy

Improving object-to-data mapping

1st Normal Form

Simplifying database records

Too much normalization

2nd Normal Form

Designing a course table

Streamlining meal options

3rd Normal Form

Identifying transitive dependencies

Table definitions for 3rd Normal Form

Working through the normalization process

Database Normalization

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.

Introduction to access control

Creating a new user

Updating user passwords

PostgreSQL access privileges

Granting user privileges

Using the granted privileges

Hierarchical access control

Working with users and groups

Schema privileges

Removing access

Removing user privileges

Rescinding group membership

Implementing access control for teams

Course wrap-up

Access Control in PostgreSQL

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.

<h2>Learn How the PostgreSQL Create Database Function Works</h2>
Have you ever wanted to create and manage your own databases but thought such knowledge is reserved for database administrators? This course teaches you the skills and knowledge necessary create your databases and effectively manage them yourself. 

You'll cover the structure of PostgreSQL databases, PostgreSQL datatypes, and the normalization of databases to remove data duplication, increase data consistency and improve data organization. 

<h2>Discover the Structure of PostgreSQL Databases and Learn PostgreSQL Data Types</h2>
You’ll start this course by exploring the fundamentals of creating a Postgres database by covering databases, tables, and schemas, understanding how each fits into the PostgreSQL object hierarchy. 

Next, you’ll look at PostgreSQL data types, including text, numeric, temporal, and boolean data. You’ll also learn the appropriate times to use each data type. 

As you progress, you’ll learn about database normalization, understanding how it maintains the integrity of your data and reduces duplication. Finally, you’ll cover PostgreSQL access control and how you can protect your databases from unauthorized access. 

<h2>Create Your Own Database Using PSQL</h2>
As you master the skills to create a PostgreSQL database, you’ll work with real data from the U.S. Small Business Administration (SBA) to guide you through the lessons and provide context for the concepts covered in the course.

By the time you've completed this PostgreSQL training, you’ll be able to confidently create your own databases using PostgreSQL. "

Intermediate SQL

Learn to create PostgreSQL databases with this four-hour course. You’ll explore the structure, data types, and normalization of databases using PostgreSQL.

Creating PostgreSQL Databases

Learn how to create a PostgreSQL database and explore the structure, data types, and how to normalize databases.

SQL pour les administrateurs de bases de données

Industry Assistant Professor, New York University

Access control with schemas

Example: schema use in finances database

Granting schema privileges

Using groups

Shared and individual data access

Let's practice!

https://s3.amazonaws.com/assets.datacamp.com/production/course_20864/subtitles/course_20864_341fd286ad9a243242e7fe4aa8b91eb1.vtt

Access Control in PostgreSQL - Hierarchical access control

Hierarchical access control

Create Your Free Account