COP5725 DATABASE MANAGEMENT POSTGRESQL TUTORIAL

1. COP5725 DATABASE MANAGEMENTPOSTGRESQL TUTORIAL Prof: Dr. Shu-Ching Chen TA: Sheng Guan

2. Procedure outline • Connect to server • Connect to database • Add tables through the gui • Add tables through sql query tool • Check your result after changing • Run other sql queries in sql query tool

3. Connect to server • Right click “COP5725” at the left “Object browser” panel to connect host server, and select “Connect”, and enter your password Right click and select “Connect” Input your panther ID

4. Connect to server • If you are connecting from off-campus, you are required to input SSH tunnel password Input First Initial+ PantherID +Last Initial

5. Some tips • Here if you encounter error, please check your PgAdmin version whether is updated – PgAdmin III 1.22 • If you believe the order of your default first initial and last initial is wrong, please kindly try different combinations • Sometimes, PgAdmin will go back to normal after you close the program and restart

6. Connect to database • The server should now be shown in the left hand box and you can navigate your way round your database tables.

7. Add tables through the gui • YourDB->Schemas->public->Tables, Right click to bring up create “New Table” dialogue

8. Add tables through the gui • Here, we use a “cities” table as an example

9. Add tables through the gui • Add attributes to the table: Click “Columns” tab, click “Add” button, and enter “name” in the Name text box, “Data type” as character and “Length” as 60

10. Add tables through the gui • You can add more attributes to the table, such as “location” , “country”, and so on using the same way • Your “cities” table will look like:

11. Add tables through the gui Finally, add one constraint for “cities” table. This would be the primary key. • Click “Constraints” tab -> “Add” button. • A new dialogue for adding a new primary key will show up. Enter the “pk_city_name” as the key name, • Click “Columns”, and connect the key to a city name field of the table we created by selecting “name”. • Then we will see that our primary key is created and added in the panel.

12. Add tables through the gui 1 2 4 3

13. Add tables through the gui Now we have specified the • Table name, • Attribute (column) names, • Data types, • Constraint (primary key , to prevent from duplicated records with the same city being added to the table).

14. Add tables through sql query tool SQL Icon Run SQL Query Run and Save SQL Query Write SQL query here

15. SQL command to create table You can select your query and press “F5” in space line to save and run the above query

16. Check your result after changing • Similarly, we create a new “weather” table in the DB • Now we want to check the changes: • Now back to Object browser and right click “Your Database”, select Refresh, expand your database, you will notice our new “weather” table with columns and constraint (primary key) created.

17. Try other sql query in query tool ALTER TABLE products ADD CONSTRAINT namecheckCHECK (name <> ''); ALTER TABLE products ADD CONSTRAINT some_nameUNIQUE (product_no); ALTER TABLE products ADD FOREIGN KEY (product_group_id) REFERENCES product_groups; ALTER TABLE products ALTER COLUMN product_no SET NOT NULL; DELETEFROM products WHERE price = 10;