APAN 5400 - Managing Data - Assignment 2 - Relational Database and SQL

• 作业标题： APAN 5400 - Assignment 2 Relational Database and SQL
• 课程名称：C-lumbia University APAN 5400 Managing Data
• 完成周期：2天

In this module you have learned how to write SQL queries to extract information from a relational database. In the typical setting you are given a request in English (or Chinese or Hindi or Swahili, etc.) for certain information that may reside in the database in question. You have to translate the English request into a SQL query (which is actually a high-level program) such that when the SQL query is executed on the database, it returns the desired information if the database contains this information.

1. Overview

In this assignment you will be provided with a schema of a database and some requests for certain information from the database. You have to construct the appropriate SQL queries. You do not need to know what data is contained in the database because the correctness of the SQL query you write is independent of the current state of the database. Of course, the actual answers returned by the database if your SQL query is executed will depend on the state of the database, but the correctness of your query does not depend on what data is contained in the database. It depends entirely on the schema of the database.
The goal of this assignment is to evaluate and reinforce your understanding of how to link up different table-schemas in terms of joins (the WHERE clause) so that information from different tables can be brought together to answer a query.

2. Objectives

This assignment supports the following objectives:

• Identify different attributes and components of a relational database table
• Create SQL queries to extract data from relational database tables

3. Details

3.1. Schema

Consider the following schema:
Suppliers (sid, sname, address, state, zip_code)
Parts (pid, pname, color, part_creation_date)
Catalog (sid, pid, cost)

3.2. Questions

In a plain text file, write the following queries in SQL:

1. Find the names of all suppliers who supply a green part.
2. Find the names of all suppliers who are from Ohio. (note: Ohio could mean New/Old Ohio or North/South/East/West Ohio)
3. Find the names of all suppliers who sell a red part costing less than $100.
4. Find the names and colors of all parts that are green or red.

3.3. Assumptions

In writing these queries you may make the following assumptions:

1. Each part has only one color.
2. The cost field is a real number with two decimal places (e.g., 100.25, 93.00).
3. The sid field in Suppliers and the sid field in Catalog refer to the same field.
4. The pid field in Parts and the pid field in Catalog refer to the same field.
5. You cannot submit “SELECT*” queries for a final answer

4. Assessment

Please see the attached rubric for detailed assessment criteria.

5. Submission

To complete your submission,

1. Please submit a plain text file containing your SQL queries.
2. Click the blue Submit Assignment button at the top of this page.
3. Click the Choose File button, and locate your submission.
4. Feel free to include a comment with your submission.
5. Finally, click the blue Submit Assignment button.

。。。