18/04/2024, 15:11 COMP3311 24T1 - Assignment 2
https://cgi.cse.unsw.edu.au/~cs3311/24T1/assignments/ass2/data.php 1/2
COMP3311 24T1 Assignment 2
SQL Data
Database Systems
Last updated: Friday 5th April 2:39am
Most recent changes are shown in red ... older changes are shown in brown.
[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.d
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
# If you used the `ln -s` command, then you can skip this com
$ gunzip pkmon.dump.sql.gz
# Remove an old database if it exists
# WARNING: This will delete all Views and Functions loaded in
$ 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,
$ psql pkmon
Database Summery
18/04/2024, 15:11 COMP3311 24T1 - Assignment 2
https://cgi.cse.unsw.edu.au/~cs3311/24T1/assignments/ass2/data.php 2/2
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)