Refer to the assignment 3 for definitions of what is meant by a database design outline and an entity-relationship diagram.    
 

Q1.  Queries with a unary relationship:  Download the employees-self-ref.mdb database from the December 3 class.  For each of the following queries, hand in the query results and a screen shot of your query design. 

1. All cases in which an employee has a higher salary than his or her supervisor.  Include the first name, last name, job title (not the job title ID), and salary for both the employee and the supervisor. 
    
2. A list of all employee first names, last names, and their locations (not locations IDs).  Also display the first and last name of the supervisor, showing blanks if the employee has no supervisor.  For employees whose supervisor has a supervisor, also show the first and last name of that person, and otherwise blanks.
    
3. The first name, last name, and salary of all supervisors, and the average salary of the employees they directly supervise.
    

Q2.  Movie catalog:   to make the Sickle and Evert movie guide easier to maintain, your firm is trying to create a database from which future editions of the guide will be generated.  For each movie, you want to store its title, date of first release, critics' rating (between 0 and 5 stars), motion picture rating (G, PG, PG-13, R, or NC-17), plot synopsis, and critic review.  The plot synopsis and critic review are each large text or "memo" fields.  You also assign movies to categories, such as "action", "animated", and so forth.  For each category, there is a 3-letter code, a name, and a description.  A movie may fall into more than one category: for example, "Spaceballs" is both "science fiction" and "humor".  

Each movie as exactly one director and any number of actors.  The system should be able to list the director and all actors for each movie.  Sometimes the same person will both direct movies and act in them.  For each person (director, actor, or both), you want to store a first name, last name, middle name/initial, date of birth, nationality, and date of death (if any).  

Sometimes a movie is a sequel to another movie: for example, "The Two Towers" is the sequel to "Fellowship of the Ring".  The database should also keep track of such sequel relationships between movies. 

Design a database to hold all this information.  Draw an entity-relationship diagram and hand in a database design outline.  You may create additional "ID" fields where necessary.
 

Q3.  Company ownership and boards:  a federal regulatory agency is trying to track the relationships between a large number of companies and individuals.  For the purposes of this exercise, assume that the agency is interested only in the present state of these relationships, and not in how they may have changed over time.

For each individual, you want to store the social security number, first name, last name, middle name/initial, date of birth, address, city, state, zip code, e-mail address, and phone number.  Assume the agency has access to a zip code table.  For each company, you want to store a tax ID number, name, mailing address, city, state, and zip code.  Assume that all individual and company addresses are within the USA.

The agency wants to understand the ownership relationships between the individuals and companies.  The system should record which companies each individual owns stock in, and how many shares are involved.  Furthermore, some of the companies hold stock in other companies.  You want to record each such situation, including the number of shares held.

In addition to stock ownership, an individual may also be related to a company by membership on its board of directors.  You want the system to record all such board memberships.

Design a database to hold all this information.  Draw an entity-relationship diagram and hand in a database design outline.