CS 377 - Database Systems
SQL Project 1 (Serie A)

Due: see class web page




Before you start on this project, you must read

You must read the information/description on the SPJ project database in the URL below to do the project.

Specifically: pay attention to what the meaning of each relation is.



You can do the project using the cs377-sql GUI application. To access the Supplier/Part/Project database, you must enter:

in the database field and click the select button.

How to set up your Emory Account to run cs377-sql:



You can also do the SQL project using this webpage:

Type the query into the Query box and click send to execute the query.

Use the back button on your browser to return to the query web page









1. Assignment

Formulate the queries in Section 2 in the SQL language

  • Important note:

    Note: You should be aware that sometimes, an incorrect query can produce the same outputs as the right query. Incorrect queries that happens to produce the same answers as the solution query will not receive full credits. In fact, we use a different database content to grade the SQL projects, so do NOT rely solely on visual output to check for correctness. You should understand SQL and the techniques discussed in class.

    Note: In some dubious cases, you may want to insert extra data into the database to test the correctness of your query. You can make your requests by sending an email to cheung@mathcs.emory.edu. You must state what extra tuples you need and why you need them. If justified, I will add the extra data to the project database.





    2. SQL Queries Files

    You should use one file to hold one SQL query.

    Follow the following guidelines for each file:



    *** Checking for correctness of your queries ***




    3. SQL Queries

    Express the following queries in SQL:
    1. Get part numbers of parts supplied by a supplier in London to a project in London.

        pnum   
        ------              
        p1     
        p2     
        p3     
        p7  
        


      Answer: click here

    2. Get project numbers of projects that is supplied by some a supplier in the same city as the project with a part manufactured in the same city as the project

        jnum   
        ------              
        j1     
        j2     
        j4     
        j8     
        j9  
        


      Answer: click here

    3. Get part numbers for parts supplied to some project in Paris.

        pnum   
        ------              
        p1     
        p2     
        p3     
        p4     
        p5     
        p6     
        p7     
        p8 
        


      Answer: click here

    4. Get part numbers for parts that are not supplied to any project in Paris.

        pnum   
        ------           
        p9 
        


      Answer: click here

    5. Get project numbers for projects that use at least one part that is available from supplier s4.
      Note: get the availability information from the spj relation.
      Note: the project need not be supplied by 's4' as long as it uses some part that `s4' supplies.

        jnum   
        ------                  
        j1     
        j15    
        j16    
        j17    
        j18    
        j19    
        j2     
        j3     
        j4     
        j5     
        j6     
        j7     
        j8     
        j9    
        


      Answer: click here

    6. Find sname of suppliers who do not supply any part heavier than 18 (to any project).

        sname      
        ----------                  
        Blake      
        Green      
        Jackson    
        Johnson    
        Jones      
        Klein      
        Smith   
        


      Answer: click here

    7. Find snum and sname of suppliers that supplies a `Nut'.

        +------+---------+
        | snum | sname   |
        +------+---------+
        | s1   | Smith   |
        | s10  | Jackson |
        | s11  | Bond    |
        | s12  | Newton  |
        | s2   | Jones   |
        | s5   | Adams   |
        | s6   | Brown   |
        | s7   | Klein   |
        | s8   | Green   |
        +------+---------+               
        


      Answer: click here

    8. Find snum and sname of suppliers who do not supply any `Nut's.

        +------+---------+
        | snum | sname   |
        +------+---------+
        | s13  | Lofton  |
        | s3   | Blake   |
        | s4   | Clark   |
        | s9   | Johnson |
        +------+---------+                    
        


      Answer: click here

    9. Find jnum of projects that do not use any locally made parts (i.e., if the project takes place in city x, then it does not use any part made in city x).

        +------+
        | jnum |
        +------+
        | j10  |
        | j11  |
        | j12  |
        | j13  |
        | j3   |
        | j5   |
        | j6   |
        | j7   |
        +------+              
        


      Answer: click here

    10. Find jnum and jname of projects that do not use any red parts.

        +------+----------+
        | jnum | jname    |
        +------+----------+
        | j10  | Scanner  |
        | j6   | Terminal |
        +------+----------+                   
        


      Answer: click here

    11. Find jnum and jname of projects that only uses red parts.

        +------+--------+
        | jnum | jname  |
        +------+--------+
        | j14  | Disk   |
        | j17  | Disk   |
        | j18  | Camera |
        | j19  | CPU    |
        +------+--------+                      
        


      Answer: click here

    12. Get snum and sname for suppliers that supply to all (every single) projects in "Rome".

        +------+---------+
        | snum | sname   |
        +------+---------+
        | s11  | Bond    |
        | s9   | Johnson |
        +------+---------+              
        


      Answer: click here

    13. Get snum and sname for suppliers that do not supply to all (every single) projects in "Rome".

        +------+---------+
        | snum | sname   |
        +------+---------+
        | s1   | Smith   |
        | s10  | Jackson |
        | s12  | Newton  |
        | s13  | Lofton  |
        | s2   | Jones   |
        | s3   | Blake   |
        | s4   | Clark   |
        | s5   | Adams   |
        | s6   | Brown   |
        | s7   | Klein   |
        | s8   | Green   |
        +------+---------+                 
        


      Answer: click here

    14. Get snum and sname for suppliers that supply to some projects in "Rome" but not to all (every single) projects in "Rome".

        +------+---------+
        | snum | sname   |
        +------+---------+
        | s1   | Smith   |
        | s10  | Jackson |
        | s2   | Jones   |
        | s6   | Brown   |
        | s8   | Green   |
        +------+---------+               
        


      Answer: click here

    15. Find jnum and jname of projects that use all (every single) parts that are used in project 'j3'.

        +------+----------+
        | jnum | jname    |
        +------+----------+
        | j1   | Sorter   |
        | j2   | Punch    |
        | j3   | Reader   |
        | j4   | Console  |
        | j5   | Collator |
        | j7   | Tape     |
        +------+----------+                   
        


      Answer: click here

    16. Find jnum and jname of projects that only use parts that are manufactured in the same city where the project takes place.

        +------+-------+
        | jnum | jname |
        +------+-------+
        | j14  | Disk  |
        +------+-------+              
        


      Answer: click here

    17. Find snum and sname of suppliers who supply all (every single) blue parts but do not supply any green part.

        +------+--------+
        | snum | sname  |
        +------+--------+
        | s13  | Lofton |
        | s2   | Jones  |
        | s7   | Klein  |
        +------+--------+                 
        


      Answer: click here

    18. Find snum and sname of suppliers who supply at least one part of every possible color.

        +------+-------+
        | snum | sname |
        +------+-------+
        | s1   | Smith |
        | s5   | Adams |
        +------+-------+            
        


      (More clarification: suppose there are only 3 parts p1, p2 and p3. Part p1 is blue, p2 is green and p3 is blue. Then the suppliers that qualify are those that supply (p1 and p2) or (p2 and p3). In these cases, the supplier supplies parts of every color.)
      Answer: click here

    19. Find snum and sname of suppliers who supply all (every single) "Bolt" parts.

        +------+-------+
        | snum | sname |
        +------+-------+
        | s6   | Brown |
        +------+-------+                 
        


      (More clarification: suppose p1 and p2 are "Bolt" parts and there are no more "Bolt" parts other than p1 and p2. Then you must find suppliers that supply both p1 and p2).
      Answer: click here

    20. Find snum and sname of suppliers who only supply to projects in "Rome".

        +------+---------+
        | snum | sname   |
        +------+---------+
        | s10  | Jackson |
        | s9   | Johnson |
        +------+---------+                      
        


      Answer: click here



    Turn in





    Extension request





    Statement of Policy on Computer Assignments

    Students will be graded partially on the basis of their programming assignments. These programming assignments are to be treated as examinations, and are expected to be your individual work. While discussions with other students in the course may be permitted or encouraged by your instructor, you should write your program yourself. The mathlab representatives are available to explain error messages, discuss briefly technical details with which you may not be familiar, and give short suggestions as to how you might detect logic errors. The reps should not, however be asked to write part or all of your program. Your instructor (and any teaching assistants assigned to the course) will be glad to help you to the extent that he or she feels reasonable.

    Submissions based on other students solutions in prior offerings of the course specifically violate these guidelines, as do submissions prepared with the help of an outside "tutor".

    You should take precautions to protect the confidentiality of your work: preserve the secrecy of your password, do not make files or directories sharable, pick up your printouts promptly and dispose of printouts where they will not tempt other students. All work should be done either in the class directory of your ITD account (preferred) or in your "priv" directory (only if you do not have a class directory).

    All submissions should include a comment statement near the top of the program of the form:

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

    Cases of apparent plagiarism or collusion will be referred to the Honor Council.