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):
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|
||manages: PK should be (pid)
||project, new column manager
||FK (manager) references professor
||runs: PK should be (dno)
||Dept, new column chair (or chairman)
||FK (chair) references professor
||(no relationship table specified)
||graduates has column major
||FK (major) references depts
||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
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:
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:
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
primary key (member_id));
create table groups
primary key (group_id));
create table roles
group_id integer not null,
primary key (role_id)
foreign key (group_id) references groups
create table belongs
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.
6. a. The only needed change is to make the managerid column in dept "not
C:\cs\cs630>javac -version (on a Windows