Reference no: EM131826

The purpose of this assignment is to give you practice with creating database structure using SQL and using the INSERT command to add data.

Read the University Archaeology Museum case. Then use the appropriate command-line client software with Postgres or mySQL to create the database structures necessary for the tables in this document. (They represent only a portion of the tables necessary for the complete museum database.)

Name your database museum. Please also adhere to column names and data types as specified in this document. (This will make it possible for me to use my own database when grading Assignment 7.)

Once you have created the tables, load them with the data provided.

Hand in output that lists your tables, their columns, and the test data. How you get the first two depends on the DBMS you are using:

• PostgreSQL: Issue the following two commands. Paste the output into a Word or plain text document to submit.
  • SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';
  • SELECT column_name FROM information_schema.columns WHERE table_name = 'table_name';
• MySQL: Issue the following two commands. Paste the output into a Word or plain text document to submit.
  • SHOW TABLES
  • DESCRIBE table_name

Then issue SELECT * FROM table_name for each table in the database. Paste the output into the Word or plain text document.

Please keep in mind that you will need this database to complete assignment 6. Therefore, it is important that you create it and populate the tables correctly.

Shorthand: \d table_name

Using the database you created for Assignment 5, develop and test the command-line SQL syntax to perform the data retrieval requests described below. Turn in your query and the output table by copying the output of your SQL command processor and pasting it into a Word or text document.

1. List the grant sources and award amounts for all grants awarded to principle researcher #001.
2. List the names of all researchers who received grants of more than $100,000.
3. List the contents of purchase order #000003, including the name of each item, how many were ordered, the unit cost, and the line cost (unit cost * quantity).
4. List the first and last names of the people working on dig #004.
5. Display the total cost of the items on purchase order #000003.
6. List the names of the vendors with whom an order for a single item costing over $1000 has been placed.
7. List the name of the principle researcher for each grant to which "Archaeology Supply Co." has sold something.
8. List all the employees who are not assigned to any dig.
9. List all grants to which "Charter Airlines" has not sold supplies.