COP4710 Database Management Connect to PostgreSQL sever via pgAdmin

1. COP4710 Database ManagementConnect to PostgreSQLsever viapgAdmin Prof: Dr. Shu-Ching Chen Ta: Yimin Yang

2. Procedure outline • Download pgAdmin from http://www.pgadmin.org/ • Install pgAdmin • Connect to PostgreSQL through pgAdmin • Start creating your own database.

3. Download pgAdmin (1) Go to PgAdmin Website Click download

4. Download pgAdmin(2) Select the latest reliable version v1.12.3

5. Download pgAdmin(3)

6. Download pgAdmin(4) • Download pgadmin3-1.12.3.zip and extracted.

7. Install pgAdmin (1) • After extracting the zip file “pgadmin3-1.12.3.zip” , execute the file pgadmin3.msi and start the installation process.

8. Install pgAdmin(2)

9. Install pgAdmin(3)

10. Install pgAdmin(4) The installation is successfully finished

11. Connect to PostgreSQL (1) • Open pgAdmin and add a connection to a server

12. Connect to PostgreSQL(2) Name for server connection Hosted server: ocelot.aul.fiu.edu Database Name: fall11_”fiu_account” Username: fall11_”fiu_account” Password: Panther ID

13. Create a database (1) • Create table • Constraints: • NOT NULL • UNIQUE • CHECK Boolean expression • For instance CHECK (price>0) • (4) PRIMARY KEY • (5) FOREIGN KEY CREATE TABLE products ( product_no integer PRIMARY KEY, name text NOT NULL, price numeric ); • Primary key and Foreign key CREATE TABLE order_items ( product_no integer REFERENCES products, order_id integer REFERENCES orders, quantity integer, PRIMARY KEY (product_no, order_id), CONSTRAINT cq CHECK (quantity > 5) );

14. Create a database (2) • Foreign Key CREATE TABLE other_table ( c1 integer PRIMARY KEY, c2 integer ); CREATE TABLE t1 ( a integer PRIMARY KEY, b integer, c integer, FOREIGN KEY (b, c) REFERENCES other_table (c1, c2) );

15. Create a database (3) CREATE TABLE products ( product_no integer PRIMARY KEY, name text, price numeric ); CREATE TABLE orders( order_id integer PRIMARY KEY, shipping_address text, … ); CREATE TABLE order_items ( product_no integer REFERENCES products ON DELETE RESTRICT, order_id integer REFERENCES orders ON DELETE CASCADE, quantity integer, PRIMARY KEY (product_no, order_id) );

16. Create a database (4) • Insert Data INSERT INTO products (product_no , name ,price) VALUES (1,'cheese',5); INSERT INTO products VALUES (1,'cheese',5), (2,’cake’,10) ;

17. Create a database (5) • Import Data • Export Data COPY country FROM '/usr1/proj/bray/sql/country_data.csv' WITH DELIMITER ',' ; COPY country TO '/sql/country_data.csv' WITH DELIMITER '|';

18. Create a database (6) • ALTER Table • Add columns • Remove columns • Add constraints ALTER TABLE products ADD COLUMN description text; ALTER TABLE products DROP COLUMN description; ALTER TABLE products ADD CONSTRAINT namecheck CHECK (name <> ''); ALTER TABLE products ADD CONSTRAINT some_name UNIQUE (product_no); ALTER TABLE products ADD FOREIGN KEY (product_group_id) REFERENCES product_groups; ALTER TABLE products ALTER COLUMN product_no SET NOT NULL;

19. Create a database (7) • ALTER Table • Remove constraints • Change column data types • Rename columns • Rename tables ALTER TABLE products DROP CONSTRAINT some_name; ALTER TABLE products ALTER COLUMN product_no DROP NOT NULL; ALTER TABLE products ALTER COLUMN price TYPE numeric(10,2); ALTER TABLE products RENAME COLUMN product_no TO product_number; ALTER TABLE products RENAME TO items;

20. Create a database (8) • Delete Data DELETE FROM products WHERE price = 10; DELETE FROM products