COP 5725 DATABASE MANAGEMENT POSTGRESQL TUTORIAL Prof Dr

COP 5725 DATABASE MANAGEMENT POSTGRESQL TUTORIAL Prof: Dr. Shu-Ching Chen TA: Sheng Guan

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

CONNECT TO SERVER Right click “COP 5725” 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

CONNECT TO SERVER If you are connecting from off-campus, you are required to input SSH tunnel password Input First Initial+ Panther. ID +Last Initial

SOME TIPS Here if you encounter error, please check your Pg. Admin version whether is updated – Pg. Admin III 1. 22 If you believe the order of your default first initial and last initial is wrong, please kindly try different combinations Sometimes, Pg. Admin will go back to normal after you close the program and restart

CONNECT TO DATABASE The server should now be shown in the left hand box and you can navigate your way round your database tables.

ADD TABLES THROUGH THE GUI Your. DB->Schemas->public->Tables, Right click to bring up create “New Table” dialogue

ADD TABLES THROUGH THE GUI Here, we use a “cities” table as an example

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

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:

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.

ADD TABLES THROUGH THE GUI 1 2 3 4

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).

ADD TABLES THROUGH SQL QUERY TOOL SQL Icon Run SQL Query Run and Save SQL Query Write SQL query here

SQL COMMAND TO CREATE TABLE You can select your query and press “F 5” in space line to save and run the above query

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.

TRY OTHER SQL QUERY IN QUERY TOOL 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; DELETE FROM products WHERE price = 10;