Creating PostgreSQL Databases
| Course Details | ||||||||
|---|---|---|---|---|---|---|---|---|
|
||||||||
| Languages | ||||||||
|
||||||||
| Outcomes | ||||||||
|
||||||||
| Price & Terms | ||||||||
|
||||||||
| Technical | ||||||||
|
||||||||
| Specification | ||||||||
|
||||||||
Create PostgreSQL databases, schemas and tables, choose column data types, normalize designs to 3rd Normal Form and control user access.
Creating a PostgreSQL database yourself means making a run of design decisions: where each table sits among schemas, what kind of value every column holds, how to split records so nothing is stored twice, and who may read or change the data. Many of the exercises run against real U.S. Small Business Administration records, which keeps the design work tied to an actual dataset.
What You Will Achieve
- Write the commands that create a database, its schemas and the tables inside them, linking tables with a foreign key
- Choose text, numeric, boolean and temporal column types, with sensible defaults, to fit the values a table will store
- Bring a table design into 3rd Normal Form by removing redundant data and transitive dependencies
- Give a team’s members the rights they need by creating users and groups and assigning or revoking privileges
Program Overview
Database Structure and Data Types
Sets out how databases, schemas and tables fit together in PostgreSQL and which column types suit the data each table holds.
- Databases, tables and schemas in the object hierarchy
- The public schema and user-level schemas
- Text, numeric, temporal and boolean types
- Matching column types to stored values
Normalization and the Normal Forms
Shows why normalizing a database protects data integrity and cuts duplication, then works through each normal form with example table designs.
- Reasons to normalize and reducing redundancy
- Object-to-data mapping
- The three normal forms in sequence
- Avoiding too much normalization
Users, Groups and Access Privileges
Protects a database and its objects from unauthorized access by managing which users and groups can work with them.
- New users and password updates
- Granting and removing user privileges
- Group membership and hierarchical access
- Schema privileges
Certificate Awarded (Paid)
Job Outlook
These skills apply wherever an organization stores its records in PostgreSQL and someone must design the tables, keep the data consistent and decide which users and teams may reach it.
Good for: Analysts and developers who want to build and secure their own PostgreSQL databases instead of waiting on a database administrator.
Keep in mind: Normalization is taught up to 3rd Normal Form, with a lesson on when too much normalization makes a design worse.
Bottom line: Each of its four chapters handles one decision in building a PostgreSQL database, and the Small Business Administration records give the lessons a real dataset.
Frequently Asked Questions
Which command creates a new PostgreSQL database?
The CREATE DATABASE command does it. The first chapter shows it alongside the commands that build schemas and tables, so a new database gets its internal structure as well as its name.
What is the template1 database?
It is a system database that PostgreSQL copies each time the CREATE DATABASE command runs. Every new database therefore begins as a copy of template1, which serves as the template for all of them.
Whose records do the exercises work with?
Many exercises draw on U.S. Small Business Administration records, such as an appeals table and a task supporting a marketing campaign. Other examples include designing a course table and streamlining meal options during the normalization lessons.
How are permissions handled for a team of users?
The last chapter places users in groups and grants privileges to users and groups on database objects, so a team can share one set of rights. It also covers schema privileges, removing privileges and taking someone out of a group.
User Reviews
Write your review
You must be logged in to post a review.
Reviews can only be posted by registered learners. Log in or create a free account, then you will return here to post.
There are no reviews yet.