CS430/630 Homework 3 Solution

For scripts, use Oracle, and also optionally Mysql, in which case name t 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.

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

RA:  ((πsid,pidCatalog)/(πsidSuppliers) ) ⨝ Parts

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).

RA: ((πsid,pidCatalog)/(πsidσname >= 'A' ^ name<'B' Suppliers) ) ⨝ Parts

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

RA: πeid,salary(((πeid,aidCertified)/(πaidσcruisingrange<2000Aircraft) )⨝ Employees)

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 list each relationship (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, what FK could be used to implement it. Note the discussion on pp. 78-79 about two ways to implement a N-1relationship, 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 does neither, proposing relationship tables with two-column PKs. To answer this question, fill the following table out. 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) If N-1, Table to add column and FK to, to avoid relationship table 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
Work_dept N-N

Runs N-1
runs: PK should be (dno)
Dept, new column chair (or chairman)
FK (chair) references professor
Major N-1
(no relationship table specified)
graduates has column major
FK (major) references depts
Work_proj N-N

Advisor N-1
advisor: PK should be (grad_ssn)
graduates, new column advisor
FK (advisor) references graduates

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 (by single-column PK but not two-column one)
i. Two professors managing a certain project.
ii. Two Projects managed by one professor.

Answer: i. Discussion (not required): the two-column PK allows multiple rows for one pid, i.e. multiple ssns for one pid, whereas single-column PK would be pid, allowing only one row for a pid, i.e. one prof_ssn for a pid.

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

Answer: The arrow from Project to Manages means that Manages is many-to-one, with many projects to one professor, i.e., i. is disallowed.

Alternative Answer: The arrow from Project to Manages means that the PK of Projects is also the PK of Manages, so each pid shows up at most once in Manages, with a certain prof_ssn, disallowing i.

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).
Answer: The thick line means that each project has full participation in Manages, i.e., each project has a managing professor, answer ii.

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 problem3.sql and problem3.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.

Discussion: A member belongs to each of several groups,  now each relationship is further specified as being in a certain role, or perhaps without a role. So we can make the role_id an (nullable) attribute of the Member-Group relationship.  That idea leads to tables easily. However, there is more here. The role_id is specific to a group, so Roles and Groups have a many-to-one relationship. No problem for tables: the Role table has a FK to the Group table. So the needed tables are:

members(member_id, name, address)
groups(group_id, name)
role(role_id, name, group_id)  group_id not-null FK to groups
belongs(member_id, group_id, role_id) member_id FK to members, group_id FK to groups (note PK does not include role_id)

create table members
       (member_id integer,
        name char(20),
        address char(40),
        primary key (member_id));
create table groups
       (group_id integer,
        name char(20),
        primary key (group_id));

create table roles
       (role_id integer,
        name char(20),
        group_id integer not null,
        primary key (role_id)
        foreign key (group_id)  references groups

create table belongs
       (member_id integer,
        group_id integer,
        role_id integer,  -- nullable in case member has no role in group
        primary key (member_id, group_id),
        foreign key (member_id)  references members,
        foreign key (group_id)  references groups
        foreign key (role_id)  references roles);

Looking at the relationship table belongs, we see two entity keys in the PK, so this is a binary relationship. But instead of ordinary attribute, we have another entity key that is not in the PK. This means the Member-Group relationship basically has an entity (Role) working like an attribute. This case is covered in the book only under the "Aggregation" section, a section that was not assigned.

We can convert a relationship to an entity. Instead of belongs, we can invent entity membership, with many-to-one relationships to Members, Groups, and Roles, and PK of (member_id, group_id), just like belongs. This says that a membership is defined by a member and a group, and has a Role for that combo. It uses the same tables except the relationship table name changes. This approach is straightforward to show in an E-R diagram. However, the fact that Membership has a PK made up of two entity keys means it's a disguised binary relationship.

5. Install Java if necessary. Report on your progress: Answer varies.

a.  What is your development system OS?  Windows (if no, 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.

C:\cs\cs630>javac -version    (on a Windows system)
javac 1.8.0_65

6. a. The only needed change is to make the managerid column in dept "not null".