Chapter 6

1a.

(i)

A dairy farmer, who is also a part-time cartoonist, has several herds of cows. He has assigned each cow to a particular herd. In each herd, the farmer has one cow that is his favorite--often that cow is featured in a cartoon.

(ii)

A few malcontents in each herd, mainly those who feel they should have appeared in the cartoon, disagree with the farmer's choice of a favorite cow, whom they disparagingly refer to as the sacred cow. As a result, each herd now has elected a herd leader.

1b.

The originator of a pyramid marketing scheme has a system for selling ethnic jewelry. The pyramid has three levels--gold, silver, and bronze. New associates join the pyramid at the bronze level. They contribute 30 percent of the revenue of their sales of jewelry to the silver chief in charge of their clan. In turn, silver chiefs contribute 30 percent of what they receive from bronze associates to the gold master in command of their tribe. Finally, gold masters pass on 30 percent of what they receive to the originator of the scheme.

1c.

The legion, the basic combat unit of the ancient Roman army, contained 3,000 to 6,000 men, consisting primarily of heavy infantry (hoplites), supported by light infantry (velites), and sometimes by cavalry. The hoplites were drawn up in three lines. The hastati (youngest men) were in the first, the principes (seasoned troops) in the second, and the triarii (oldest men) behind them, reinforced by velites. Each line was divided into 10 maniples, consisting of two centuries (60 to 80 men per century) each. Each legion had a commander, and a century was commanded by a centurion. Julius Caesar, through one of his Californian channelers, has asked you to design a database to maintain details of soldiers. Of course, Julius is a little forgetful at times, and he has not supplied the titles of the officers who command maniples, lines, and hoplites, but he expects that you can handle this lack of fine detail.

1d.

A travel agency is frequently asked questions about tourist destinations. For example, customers want to know details of the climate for a particular month, the population of the city, and other geographic facts. Sometimes, they request the flying time and distance between two cities. The manager has asked you to create a database to maintain these facts.

1e.

The Center for the Study of World Trade keeps track of trade treaties between nations. For each treaty, it records details of the countries signing the treaty and where and when it was signed.

1f.

Design a database to store details about U.S. presidents and their terms in office. Also, record details of their date and place of birth, gender, and political party affiliation (e.g., Caluthumpian Progress Party). You are required to record the sequence of presidents so the predecessor and successor of any president can be identified. How will you model the case of Grover Cleveland who served nonconsecutive terms as president? Is it feasible that political party affiliation may change? If so, how will you handle it?

1g.

The IS department of a large organization makes extensive use of software modules. New applications are built, where possible, from existing modules. Software modules can also contain other modules. The IS manager realizes that she now needs a database to keep track of which modules are used in which applications or other modules. (Hint: it is helpful to think of an application as a module.)

1h.

Data modeling is finally getting to you. Last night you dreamed you were asked by Noah to design a database to store data about the animals on the ark. All you can remember from Sunday school is the bit about the animals entering the ark two-by-two, so you thought you should check the real thing. Take with you seven pairs of every kind of clean animal, a male and its mate, and two of every kind of unclean animal, a male and its mate, and also seven pair of every kind of bird, male and female. Genesis 7:2 Next time Noah disturbs your sleep, you want to be ready. So, draw a data model and make certain you record the two-by-two relationship.

2a.

Find the departments where all the employees earn less than their boss.

WITH
wrk as (SELECT * FROM emp),
boss as (SELECT * FROM emp)
SELECT DISTINCT (deptname) FROM dept 
WHERE deptname NOT IN 
(SELECT wrk.deptname FROM wrk JOIN boss 
ON wrk.bossno = boss.empno 
WHERE wrk.empsalary > boss.empsalary);

2b.

Find the names of employees who are in the same department as their boss (as an employee).

WITH
wrk as (SELECT * FROM emp),
boss as (SELECT * FROM emp)
SELECT wrk.empfname FROM wrk JOIN boss 
ON wrk.bossno = boss.empno
WHERE wrk.deptname = boss.deptname;

empfname
Andrew
Clare
Nancy
Sarah

2c.

List the departments having an average salary greater than $25,000.

SELECT deptname, AVG(empsalary) FROM emp
GROUP BY deptname HAVING AVG(empsalary) > 25000;

2d.

List the departments where the average salary of the employees, excluding the boss, is greater than $25,000.

SELECT deptname, AVG(empsalary) FROM emp 
WHERE empno NOT IN 
(SELECT empno FROM dept) 
GROUP BY deptname HAVING AVG(empsalary) > 25000;

deptname	expr1001
Purchasing	$56,000.00

2e.

List the names and manager of the employees of the Marketing department who have a salary greater than $25,000.

WITH
wrk as (SELECT * FROM emp),
boss as (SELECT * FROM emp)
SELECT wrk.empfname, boss.empfname 
FROM wrk JOIN boss 
ON wrk.bossno = boss.empno 
WHERE wrk.deptname = 'Marketing' 
AND wrk.empsalary > 25000;

2f.

List the names of the employees who earn more than any employee in the Marketing department.

SELECT empfname FROM emp 
WHERE empsalary > (SELECT MAX(empsalary) 
FROM emp WHERE deptname = 'Marketing');

empfname
Alice
Sarah

3a.

Who succeeded Victoria I?

SELECT monname, monnum FROM monarch 
WHERE premonname = 'Victoria' 
AND premonnum = 'I';

monname	monnum
Edward	VII

3b.

How many days did Victoria I reign?

WITH
suc as (SELECT * FROM monarch),
cur as (SELECT * FROM monarch)
SELECT suc.rgnbeg - cur.rgnbeg AS "days" 
FROM cur JOIN suc 
ON (suc.premonname = cur.monname AND suc.premonnum = cur.monnum) 
WHERE cur.monname = 'Victoria' AND cur.monnum = 'I';

3c.

How many kings are there in the table?

count
4

3d.

Which monarch had the shortest reign?

WITH
cur as (SELECT * FROM monarch),
pre as (SELECT * FROM monarch)
SELECT pre.montype, pre.monname, pre.monnum, (cur.rgnbeg - pre.rgnbeg) as "days" 
FROM cur JOIN pre 
ON (cur.premonname = pre.monname AND cur.premonnum = pre.monnum) 
WHERE (cur.rgnbeg - pre.rgnbeg) = 
(SELECT MIN(cur.rgnbeg - pre.rgnbeg) 
FROM monarch AS cur JOIN monarch AS pre 
ON (cur.premonname = pre.monname 
AND cur.premonnum = pre.monnum));

4a.

How many different items are there in the animal photography kit?

SELECT COUNT(*) FROM product JOIN assembly 
ON product.prodid = assembly.prodid
WHERE proddesc = 'Animal photography kit';

4b.

What is the most expensive item in the animal photography kit?

SELECT proddesc FROM product 
WHERE prodprice = 
(SELECT MAX(prodprice) FROM product 
WHERE prodid IN (SELECT subprodid FROM product JOIN assembly 
ON product.prodid = assembly.prodid
WHERE proddesc = 'Animal photography kit'));

proddesc
35mm camera

4c.

What is the total cost of the components of the animal photography kit?

SELECT SUM(PRODCOST*quantity) AS 'Total cost' FROM product JOIN assembly 
ON product.prodid = assembly.subprodid
WHERE product.prodid IN 
(SELECT subprodid FROM product JOIN assembly 
ON product.prodid = assembly.prodid
WHERE proddesc = 'Animal photography kit');

4d.

Compute the total quantity of all items required to assemble 15 animal photography kits.

SELECT 15*SUM(quantity) AS Quantity FROM product JOIN assembly 
ON product.prodid = assembly.subprodid 
WHERE product.prodid IN 
(SELECT subprodid FROM product JOIN assembly 
 ON product.prodid = assembly.prodid
 WHERE proddesc = 'Animal photography kit');

6 - One-to-One and Recursive Relationships

Answers to exercises

Date revised: 10-Dec-2021