CS 377 - Database Systems

Hw4: SQL Queries

Out: Tuesday, September 27

Due: Tuesday, October 4, 11:59pm

 

Formulate queries in the SQL language using the MathCS Oracle Server for the project database. 

The Project Database

The project database consists of 3 entity relations and 1 ternary relationship relation.  It keeps track of projects in project relation, maintains information on suppliers in supplier relation and parts in part relation.  Finally, it maintains shipment information in the spj relation. The spj relation tells us which supplier supplies which part to which project. Note that a supplier may have multiple shipments of the same part to the same project.

 

·         supplier(snum, sname, status, city)

snum = supplier number (key)

sname = supplier name

status = supplier status (how good he/she is)
city = location of the supplier

·         part(pnum, pname, color, weight, city)

pnum = part number (key)

pname = part name

color = color of part

city = city where the part is made

·         proj(jnum, jname, city)

jnum = project number (key)

jname = project name

city = city where the project takes place

·         spj(snum, pnum, jnum, qty)

snum = supplier number (foreign key)

pnum = part number (foreign key)

jnum = project number (foreign key)
qty = quantity

Queries

1.      Get the supplier numbers of suppliers who supply a blue part to some project in Paris.

2.      Get the project numbers of projects that use one or more red part(s).

3.      Get the part number and name of the heaviest part(s)

4.      Get supplier numbers for suppliers who do not supply to any project in Rome.

5.      Get supplier numbers for suppliers who only supply to projects in Rome (the supplier does not supply to any project not in Rome).

6.      Get names of suppliers who supply to a project in their own city 

7.      Get part numbers of parts that are not available from supplier s2.

8.      Find project numbers for projects that only use parts weighing more than 13

9.      Get supplier numbers and names for suppliers that supply all (every single) blue parts

10.  Find project numbers and names for projects that use all bolt parts.

 

Requirements

 

·         Please remove duplicates in your queries (use select distinct) to facilitate grading.

·         Please be aware that sometimes, an incorrect query can produce the same outputs as the right query. Incorrect queries that happen to produce the same answers as the solution query will not receive full credits.  So do not rely solely on output to check for correctness without understanding the query logic.

 

Getting Started

 

·         You can directly query against the database in the class account. You can list the content of the database by issuing these select commands. Copy and paste the results into a file and print it for testing purposes.

       select * from cs377000.supplier;
       select * from cs377000.part;
       select * from cs377000.proj;
       select * from cs377000.spj;

 

You can create synonyms for the tables using the following commands. 

create synonym supplier for cs377000.supplier;

create synonym part for cs377000.part;

create synonym proj for cs377000.proj;

create synonym spj for cs377000.spj;

 

Then you can directly issue a select command such as 

select * from supplier;

 

·         Alternatively, you can create your own copy of the database using the following create commands.  This would allow you to insert/delete/update tuples in your tables in order to test the correctness of your queries in dubious cases.

create table supplier as (select * from cs377000.supplier);

create table part as (select * from cs377000.part);

create table proj as (select * from cs377000.proj);

create table spj as (select * from cs377000.spj);

Submission

·           You can use one .sql file for each SQL query when testing.  For submission, please concatenate all your query files (from query 1 to 10) into one file hw4.sql and place it in your cs377 directory ~/cs377/hw4/hw4.sql

 

·           Please include an honor code statement (use -- comments) near the top of your submission:

THIS CODE IS MY OWN WORK, IT WAS WRITTEN WITHOUT CONSULTING A TUTOR OR CODE WRITTEN BY OTHER STUDENTS - your name