6 - One-to-One and Recursive Relationships

1a.

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.

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.

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

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

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

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;

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

SELECT COUNT(montype) FROM monarch WHERE montype = 'King'; 

SELECT COUNT(*) FROM product JOIN assembly 
ON product.prodid = assembly.prodid
WHERE proddesc = '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');