Want to learn more? Take the full course at [ Ссылка ] at your own pace. More than a video, you'll learn hands-on coding & quickly apply skills to your daily work.
---
We have learned about fundamental structural components of PostgreSQL, namely databases and tables. In this lesson, we will explore one more structural component, the schema.
A schema is similar to a directory on an operating system, however, instead of containing files, the schema contains a collection of tables.
In fact, schemas can also contain other database objects including data types and functions. Such uses of schemas will not be covered in this course.
Schemas have a number of use-cases. A primary use-case is
providing a way for a database with multiple users to grant each user her own set of tables to use and manipulate without interfering with the data of other users. Imagine a company with a number of web developers working on different parts of a web application wanting to have access to data used in the web application. During development, these users would like to manipulate the tables in the database in different ways.
Rather than giving each user his own database and being concerned with the maintenance of each, a database admin could instead give each user his own schema that includes replicas of the production database.
Another important use of schemas is to provide a way to organize components of a database. Perhaps a company has a number of very distinct business units.
Schemas provide a way for the company's data to be housed in a single database while having the components of the business that are represented in the database separated from each other through the use of a number of schemas -- one for each business unit.
In the last lesson, we discussed the CREATE TABLE statement and showed how to use this command.
By default, newly created tables are added to the public schema.
Recall this CREATE TABLE statement that we went over in the last lesson.
When this statement is executed, the name of the table is actually "public-dot-topic". If another topic table in a schema named commercial existed, performing operations on "public-dot-topic" would ensure that the correct topic table is being referenced.
Like the CREATE DATABASE command, the CREATE SCHEMA command is very simple.
The structure of the command is CREATE SCHEMA followed by the name of the schema.
Recall the school table that we created in the last lesson within the "n-c-a-a underscore b-b" database. The NCAA groups schools into divisions based on the school's philosophy towards athletics, level of competition, and scholarship opportunities. There are three such divisions. Within the "n-c-a-a underscore b-b" database, we can create a "division1" schema.
We can then create a school table using a similar definition to the one used previously. Now, we add a field to keep track of the number of athletic scholarships that are available at the school. The "DEFAULT 0" clause indicates that when entering data in this table, a default value of 0 will be used for the "num underscore scholarships" field if no value is explicitly provided.
Naming restrictions similar to those for databases and tables also apply to naming schemas.
The length of a valid schema name should be less than 32 characters given default settings.
The schema name should begin with a letter or underscore.
In addition, schema names cannot begin with "pg underscore" as PostgreSQL reserves names with this prefix for system-level schemas.
In this first chapter, we have gone over important structural components of PostgreSQL databases, namely databases, tables, and schemas and commands needed to create them. Before moving onto other important aspects of the database creation process, let's practice using the CREATE SCHEMA command.
#DataCamp #SQLTutorial #CreatingPostgreSQLDatabases #SQL #PostgreSQL
