=============================================================== Using PostgreSQL on Prism =============================================================== Here's how to use Postgres on Prism (red/indigo): SSH to Prism using a terminal window: $ ssh YorkUID@eecs.yorku.ca Connect to your database by using the command psql: % psql -h db (connects to the database '' by default) (or just psql -h db -d ) % your default password is your student number (e.g., '21233XXXX') To change the password, within psql, execute: > \password Type help in the psql command window to see what is possible: > \help To exit psql type: > \q If you have followed the steps above, you should be able to connect to your database using psql. psql is a terminal-based front-end to PostgreSQL that enables you to type in queries interactively, issue them to PostgreSQL, and see the query results. Later in the term, we'll see how you can connect to a DBMS, submit queries a get an answer through a program (embedded SQL). Here are some useful psql commands. The parenthesis include description of what the command does: > \dt (Show description of all the tables - use 'q' to go back to the prompt) > \d (Show description of a specific table- use 'q' to go back to the prompt) You can find more details about psql online in the following link: https://www.postgresql.org/docs/11/app-psql.html =============================================================== Load a Sample Database in your PostgreSQL =============================================================== The next steps describe how you can load a sample database to your PostgreSQL using psql: Login to your Prism, create a new directory and navigate in the newly created directory: $ mkdir eecs3421db $ cd eecs3421db Download the online eecs3421world.sql sample database and copy it in the new directory: $ cp -pr /path/to/the/database/dump/eecs3421world.sql . Connect to your database using psql from within that directory (eecs3421db): $ psql -h db -d (or just $psql -h db) From inside the psql command prompt execute the following command to load the sample database in your database: > \i eecs3421world.sql From inside the psql command prompt execute the following command to see a brief description of the tables loaded: > SET search_path TO world; > \dt If everything worked well, you should be able to see three tables (eecs3421city, eecs3421country, eecs3421countrylanguage). You can also see that the name of our schema is "world". In a database there might be many schemata defined. Each of them defines a new namespace with a number of relations. To specify which schema we want to use we have to first run the psql command: > SET search_path TO world; You will need to make sure that you are using the right schema for the questions below. =============================================================== Database description =============================================================== The sample data you just loaded in your database is Copyright Statistics Finland, http://www.stat.fi/worldinfigures. The schema consists of three tables: * eecs3421country: Information about countries of the world. * eecs3421city: Information about some of the cities in those countries. * eecs3421countrylanguage: Languages spoken in each country. Moreover, each of the table has been populated with a number of tuples. Please use an editor, such as vim, nano, pico, etc. to open and examine the file: eecs3421world.sql There are three main parts in this file: * In the first part we define the schema of the database. This includes defining each relation and its attributes. * In the second part we generate tuples to be copied in each of the tables. * In the third part we define primary key and foreign key constraints of our tables.