Creating database structure using sql

Assignment Help Database Management System
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.

Reference no: EM131826

Questions Cloud

Identify the various costs associated with the deployment : Identify the various costs associated with the deployment, operation and maintenance of a mobile-access system. Identify the benefits to the various categories of user, arising from the addition of a mobile-access facility.
Compute area and perimeter of a polygon : Create a project that would let a user compute area and perimeter of a polygon
Standard argumentative essay : identifying a key issue or question about which there is some controversy or disagreement
Ziegler-nichols open loop tuning : Ziegler-Nichols Open Loop Tuning Often, when working with a faster loop, many people will opt only for a PI control philosophy
Creating database structure using sql : creating database structure using SQL
Describe main steps in the process of strategic management : Describe the main steps in the process of international strategic management
Increase the chances of getting your legislation passed : What will you do to increase the chances of getting your legislation passed?
What is city current average cost per hip replacement visits : They have offered to guarantee 1000 patient visits per year and want to pay $70 per visit. City Rehab currently receives $95 per visit directly from Medicare
Write a report to investigate the government planning : Report in which you investigate the Federal government's planning and response to hurricanes

Reviews

Write a Review

 

Database Management System Questions & Answers

  Analyse a set of data and write a memo

To analyse a set of data, and write a memo, identifying and explaining your insights into the operation of Todd Restaurants.

  Relational algebra operations

List the sequence of relational algebra operations needed to implement the queries.

  Stack and queue data structure

Identify and explain the differences between a stack and a queue data structure

  Knowledge and data warehousing

Design a dimensional model for analysing Purchases for Adventure Works Cycles and implement it as cubes using SQL Server Analysis Services. The AdventureWorks OLTP sample database is the data source for you BI analysis.

  Use cases perform a requirements analysis for the case study

Use Cases Perform a requirements analysis for the Case Study

  First - second or third normal form

How many entities are shown by this relation?

  Design relation schemas for the entire database

Design relation schemas for the entire database.

  Prepare the relational schema for database

Prepare the relational schema for database

  Knowledge and data warehousing

Knowledge and Data Warehousing

  Design a database schema

Design a Database schema

  Implement a database of courses and students for a school

Implement a database of courses and students for a school.

  Baxter aviation database

ER diagram for the Baxter Aviation database

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd