CS430/630 Homework 3 RA/SQL Division, Outer Join, E-R, Views

For scripts, use Oracle, and also optionally Mysql, in which case name the output samename_mysql.txt. For Oracle, use "set echo on" so the SQL is shown in the output.

1. Relational Division. Using schemas from the provided createdb.sql. For Oracle but not mysql, we can use MINUS for EXCEPT. Provide RA and SQL.

a. Find parts (pids, pnames and colors) that are supplied by all suppliers. Give RA and SQL

b. Find parts (pids, pnames and colors) that are supplied by all suppliers with names starting with 'A' (use sname >= 'A' and sname < 'B' for RA, LIKE for SQL).

c. Find employees (eid and salary) certified for all aircraft of cruising range under 2000.

d. Among employees (eid and salary) certified for all aircraft of cruising range under 2000, find the one(s) with maximal salary (SQL only)

Put the SQL in problem1.sql and its output in problem1.txt, as we did for hw2. Submit the RA on paper.

2. Outer Join. Do Problem 6 of Chapter 4 of Murach, and problem 3 of Chap. 6 of Murach. To test this, load the Invoices, etc. tables into Oracle using $cs630/murach/create_ap_tables.sql. This file can be copied to your current directory on topcat by the command (don't forget the dot at the end):

cp /data/htdocs/cs630/murach/create_ap_tables.sql .

Put the SQL in problem2.sql and its output in problem2.txt. Don't worry about mysql for this problem. The script is full of Oracle-only data types.

3. E-R a. Study the solved problem 3.13 in R&G and the solved problem 2.3 on which it is based. The R&G solutions (odd numbers only) are available at the site linked from the class web page under Textbook Resources. Examine Figure 2.1 of the solutions, and classify each relationship except Supervises (i.e. name in a diamond), whether it is a N-1 (many-to-one or one-to-many) or N-N (many-to-many) binary relationship, and if it is N-1, figure out what FK could be used to implement it. Note the discussion on pp. 78-79 about two ways to implement a N-1 relationship, a whole relationship table like Manages with a single-column PK, or an added FK column on the "many" side Dept, making a Dept_Mgr table as on pg. 79. The solution of problem 3.13 does neither, proposing relationship tables with two-column PKs. We want to replace these with one-column PKs, to better implement the N-1 relationships. To answer this question, fill the following table out and include it in your paper document. Note that relationship Supervises is not listed since it is more complicated.

Relationship N-1 or N-N If N-1, name of relationship table and corrected PK for it (one column only: give column name) If N-1, Table to add column and FK to, to avoid relationship table, and what column to add If N-1, FK constraint to add to that table
work_in N-N

Manages N-1
manages: PK should be (pid)
project, new column manager
FK (manager) references professor


Major N-1
(no relationship table specified)
graduates has column major
FK (major) references depts


b. In relationship Manages, determine what is allowed by the two-column PK on the relationship table (as in the 3.13 solution) that is disallowed by the single-column PK on the relationship table. Specifically, which of the following is disallowed:
i. Two professors managing a certain project.
ii. Two Projects managed by one professor.

c. For relationship Manages, explain how you know the answer to b. from the E-R diagram alone.

d. In relationship Manages, explain what is meant by the thick line from project to Manages. Specifically, which of the following is meant:
i. Every professor manages some project.
ii. Every project has a manager (a professor).

4. E-R a. Design a database for tracking memberships of an organization and of a set of groups within the organization. The members of the organization can belong to any number of the groups, and the groups can have any number of members.  Note that the organization, being singular, does not need its own entity: it's just the universe here. Each member has a member_id, unique, and a name and address. Each group has a group_id, unique, and a name. Provide an E-R diagram on paper and a set of create tables in problem4.sql and problem4.txt. You can just draw the E-R diagram freestyle. Try to be neat but you don't need to use a ruler. b. [cs630 only] Modify the membership design to keep track of the role served by each member in each group. Assume that each individual can only serve one role within a group (e.g. leader, treasurer, etc.) and that each group has its own set of roles that members can fulfill. Each role has an role_id, unique, and name and one other needed attribute. Draw an E-R diagram if you think you can (tricky), and show the modified set of create-tables (this is easier.) Submit this on paper. You can just draw the E-R diagram freestyle.

5. Install Java if necessary. Report on your progress, in your paper document:

a.  What is your development system OS?  Windows (if so, version 7, 8, or 10?), Linux, or Mac? 

c. Did you download Java, or did you already have a recent Java installation? Report any problems you had downloading or installing.

d. What version of Java did you end up with?  Show javac -version output.


Consider a database schema with three relations:
emp (eid:integer, ename:string, age:integer, salary:decimal(10,2))
works (eid:integer, did:integer, pct_time:integer)
dept(did:integer, dname:string, budget:decimal(10,2), managerid:integer)

The keys are underlined in each relation. Relation emp stores employee information such as unique identifier eid, employee name ename, age and salary. Relation dept stores the department unique identifier did, unique department name dname, the department budget and managerid which is the eid of the employee who is managing the department. The managerid value must always be found in the eid field of a record of the emp relation. The works relation tracks which employee works in which department, and what percentage of the time s/he allocates to that department. Each eid and each did in works appears in emp or dept respectively. Note that, an employee can work in several departments. Note that this setup is almost exactly what we have specified in createdb.sql, where "string" is translated to char or varchar.

a. Explain how to change the create tables in createdb.sql to match these specifications. Put this answer in the paper submission.
b. Create a view ManagerSummary that lists for every department the department name, manager ID and manager name, manager salary and the number of employees in that department. The view will have five columns with headings: DeptName, MgrID, MgrName, MgrSalary and EmpCount. Put this SQL in createview.sql and drop-view in dropview.sql.
c. Query the view above to retrieve the set of distinct salaries of managers who manage a department called “Sales”. Put this SQL in queryview1.sql.
d. Query the view above to find the name of the manager who manages most employees. If the same employee works in several departments, that employee is counted once in each of the departments. The manager is included in the count the same as all other employees, i.e., based on his or her records in the Works table. Put this SQL in queryview2.sql.

Make a script problem6.sql of dropping and then creating the view, then doing the two queries. Supply the output in problem6.txt.

For Delivery:

Paper delivery: Please make sure the answers are in this order.

RA for problem 1
E-R diagram for problem 4
Problem 5
Problem 6

Files in cs630/hw3:

problem1.sql, problem1.txt
problem2.sql, problem2.txt
problem4.sql, problem4.txt
problem6.sql, queryview1.sql, queryview2.sql, problem6.txt