Creating a database
C R E AT I N G P O S T G R E S Q L D ATA B A S E S
Darryl Reeves
Industry Assistant Professor, New York
University
Course topics
Commands for building databases
PostgreSQL data types
Database normalization
Database access management
CREATING POSTGRESQL DATABASES
The PostgreSQL Database Management System
object-relational database management system
system components are objects
database is top-level object
CREATING POSTGRESQL DATABASES
The CREATE DATABASE command
CREATE DATABASE db_name;
CREATE DATABASE my_db;
CREATE DATABASE _my_db;
CREATE DATABASE 321_db; -- Invalid
CREATING POSTGRESQL DATABASES
Scenarios for new database creation
CREATE DATABASE ncaa_bb;
CREATE DATABASE auto_depot;
CREATE DATABASE pod;
CREATING POSTGRESQL DATABASES
Let's practice!
C R E AT I N G P O S T G R E S Q L D ATA B A S E S
Creating tables
C R E AT I N G P O S T G R E S Q L D ATA B A S E S
Darryl Reeves
Industry Assistant Professor, New York
University
The database table
Variable number of rows
Fixed number of columns (structure can be altered)
Columns have specific data type
Each row is a record
CREATING POSTGRESQL DATABASES
The CREATE TABLE command
CREATE TABLE table_name (
column1_name column1_datatype [col1_constraints],
column2_name column2_datatype [col2_constraints],
...
columnN_name columnN_datatype [colN_constraints]
);
Name Restrictions
maximum length of 31 characters
must begin with letter or underscore ("_")
CREATING POSTGRESQL DATABASES
Example table 1
CREATE TABLE school (
id serial PRIMARY KEY,
name TEXT NOT NULL,
mascot_name TEXT
);
CREATING POSTGRESQL DATABASES
Example table 2
CREATE TABLE topic (
id SERIAL PRIMARY KEY,
description TEXT NOT NULL
);
CREATING POSTGRESQL DATABASES
Table organization
Which fields should I use?
How many tables should I add?
Which data types are best to use for the fields of my table?
CREATING POSTGRESQL DATABASES
Let's practice!
C R E AT I N G P O S T G R E S Q L D ATA B A S E S
Creating schemas
C R E AT I N G P O S T G R E S Q L D ATA B A S E S
Darryl Reeves
Industry Assistant Professor, New York
University
PostgreSQL schemas
A named container for tables
1 https://www.postgresql.org/docs/9.1/ddl-schemas.html
CREATING POSTGRESQL DATABASES
Schema uses
Providing database users with separate environments
CREATING POSTGRESQL DATABASES
Schemas uses
Organizing database objects into related groups
CREATING POSTGRESQL DATABASES
The default schema
The public schema is the default schema in PosgreSQL
CREATE TABLE topic (
id serial PRIMARY KEY,
description TEXT NOT NULL
);
public.topic
CREATING POSTGRESQL DATABASES
The CREATE SCHEMA command
CREATE SCHEMA schema_name;
CREATE SCHEMA division1;
CREATE TABLE division1.school (
id serial PRIMARY KEY,
name TEXT NOT NULL,
mascot_name TEXT,
num_scholarships INTEGER DEFAULT 0
);
CREATING POSTGRESQL DATABASES
Schema naming restrictions
Length of name less than 32
Name begins with letter or underscore ("_")
Schema name cannot begin with "pg_"
CREATING POSTGRESQL DATABASES
Let's practice!
C R E AT I N G P O S T G R E S Q L D ATA B A S E S
