Due Date: May 9, 2017
- In this exercise you’ll be working with the gep664 database.
- This exercise is worth 6 points. Each question is worth 1 point each.
- Be sure to follow the proper conventions for SQL statements and the guidelines for submitting assignments, as posted on the course website. Failure to do so will result in point deductions.
- For the following, provide SQL statements (there are no row counts to return).
This assignment will give you additional practice in working with some of the database management concepts we covered in class. You will need to use the airport_geog table that we created from a transaction in class 10. If you don’t have this table in the public schema in gep664, you will need to get the file from the course website and run this transaction to create the table.
For guidance, refer to the slides for class 11 and the links in the slides to the PostgreSQL documentation. You can also refer back to Connelly Chapter 6 (in the library e-reserve’s readings).
Part I – Transactions and Efficiencies
1. Write a transaction where you add the following values to the airport geography table and build geography for them.
2. Use the Explain statement to test how long it would take to execute a query that selects all airports that are within 3000 kilometers of Chicago O’Hare (IATA code ORD). Remember that distances in the geography type are in meters.
3. Create a spatial index on the geography column. Then re-run the Explain statement in the previous question and see how long it would take. Provide just the index statement in your answer.
Part II – Users
4. Write a statement to create a role and password for a new user called testuser. Then, in a second statement grant testuser SELECT and UPDATE privelages for the following tables in the nys schema: acs2014_labforce, zbp2014, zbp_flags, and zips.
5. Write a statement to create a role and password for a new user called testadmin. Then, in a second statement grant testadmin all privelages on all tables in the nys schema, with the ability to grant privileges to other users.
6. Revoke testuser’s UPDATE privileges on all tables in the nys schema.
Within pgAdmin you can see the results of these statements by selecting one of the tables in the nys schema, right clicking and choosing properties, and then select the Privileges tab. You can also disconnect from the database and log back in as one of these users, to see what they would see.