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):
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. 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.
4. 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 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)||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
||(no relationship table specified)
||graduates has column major
||FK (major) references depts
b. [cs630 only] Examine the create table for supervises and classify the Supervises relationship: is it N-N binary? or what? See Section 3.5.2 of R&G. Then consider the 8th bullet point in problem 2.3 and propose a change to the PK clause. In particular, consider a certain grad student, a certain project, and two profs supervising--is that allowed? Submit this on paper.
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.6. Views [This problem is postponed to after midterm exam]
Consider a database schema with three relations:
ename:string, age:integer, salary:decimal(10,2))
works (eid:integer, did:integer, pct_time:integer)
dept(did:integer, dname:string, budget:
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.
Paper delivery: Please make sure the answers are in this order.
RA for problem 1
E-R diagram for problem 3, and for cs630, the modified setup as well.
Files in hw3: