[Assignment Spec]  [SQL Schema]  [SQL Data]  [Examples]  [Testing]  [Fixes+Updates]

The provided Dump File contains the pkmon schema and data.
So you do not need to load the Schema File into the database before loading the Dump File.

SQL Dump File

• Download Dump File

Copy the SQL Dump File

We do not recommend that you copy the dump file to your own directory for this assignment.
The dump file is over 22MB in size and will take up a sizable amount of your disk quota.
For easy access to the dump file you may create a symbolic link to the dump file in the assignment directory.

Download above or copy the SQL Dump File by running the command below.

$ ln -s /web/cs3311/current/assignments/ass2/database/pkmon.dump.sql .

Loading the SQL Dump File

To load the SQL Dump File into the database, run the commands below.

# Only run this first command if you downloaded the .gz file to your local machine
# If you used the `ln -s` command, then you can skip this command
$ gunzip pkmon.dump.sql.gz

# Remove an old database if it exists
# WARNING: This will delete all Views and Functions loaded into the database
$ dropdb --if-exists pkmon

# Create a new database
$ createdb pkmon

# Load the SQL Dump File into the database
$ psql pkmon -f pkmon.dump.sql
... lots of output (SET, CREATE, ALTER, ...)

# You can now access the database, run queries, create views, ect.
$ psql pkmon

Database Summery

We have provided a function pre-defined in the database called dbpop than can be used to get a summery of the database.
To make sure your database is loaded correctly, try run the command below.

# Access the database
$ psql pkmon

# Run the `dbpop` function
pkmon=# SELECT * FROM dbpop();
       tablename        | ntuples
------------------------+---------
 abilities              |     297
 egg_groups             |      15
 encounter_requirements |   44492
 encounters             |   40280
 evolution_requirements |     650
 evolutions             |     535
 games                  |      39
 in_group               |    1494
 knowable_abilities     |    2696
 learnable_moves        | 1262002
 locations              |    3542
 moves                  |     915
 pokedex                |   11819
 pokemon                |    1191
 requirements           |     830
 type_effectiveness     |     120
 types                  |      18
(17 rows)