CS 377 - Database Systems
Homework 4

Due: See class webpage

1. Company Database





  • Extension request









    The questions below will be assigned only when enrollment is below 15 (otherwise, grading this homework will take too long)







    2. Location Database

    Consider the following relational database:

       lives(person_name, street, city)
       works(person_name, company_name, salary)
       located_in(company_name, city)
       manages(person_name, manager_name)
    

    The meaning of the relations are as follows:

    The underline attribute(s) in each relation is the primary key. The foreign keys are:

    Formulate a Relational Algebra Query on the Location database for the following:

    1. Find the name for the company (or companies) that pay the highest salary

    2. Find the name for all companies that only have employees living in the city of 'Atlanta' (i.e., every single employees of the company live in 'Atlanta')

    3. Find the name for all persons who live in the same city in which their company is located

    4. Find the name of all companies that have 50 or more of their employees living in the city of 'Atlanta'.

    5. Find the name for all persons that do not work for the company "Acme"

    6. Find the name of the person(s) that earn the highest salary in the company "Acme"

    7. For each company, list the company name, the number empoyees, the average salary paid, the lowest salary paid and the highest salary paid.

    8. Find the city with the highest number (i.e., most number) of employees of company "Acme" living in that city.

    9. Find the name of companies with the highest number of inter-city commuters (an inter-city commuter is an employee that do not live in the city where the company is located in)

    10. Find the name of companies where all its (every single) employees live in the same city where the company is located in (i.e., companies that do not have any commuting employee).

    Statement of Policy on Homework Assignments

    Students will be graded partially on the basis of their homework assignments. These homework 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. 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, do not collaborate on questions that you turn in for a grade, do not show your solution to a fellow student, not even after the due date for some students may have receive extension.

    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.