Data Management: Chapter 6 exercises

1.

Draw data models for the following problems:

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.

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

2.

Write SQL to answer the following queries using the dept and emp tables described in this chapter:

2a.

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

SELECT DISTINCT (deptname) FROM dept 
WHERE deptname NOT IN 
(SELECT wrk.deptname FROM emp AS wrk, emp AS boss 
WHERE wrk.bossno = boss.empno 
AND wrk.empsalary > boss.empsalary);

SELECT wrk.empfname FROM emp AS wrk, emp AS boss 
WHERE wrk.bossno = boss.empno
AND wrk.deptname = boss.deptname;

empfname
Andrew
Clare
Nancy
Sarah

3.

Write SQL to answer the following queries using the monarch table described in this chapter:

3a.

Who succeeded Victoria I?

3b.

How many days did Victoria I reign?

SELECT suc.rgnbeg - cur.rgnbeg AS "days" 
FROM monarch cur 
JOIN monarch 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?

3d.

Which monarch had the shortest reign?

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

4.

Write SQL to answer the following queries using the product and assembly tables:

4a.

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

SELECT COUNT(subprodid) FROM product, assembly 
WHERE proddesc = 'Animal Photography Kit' 
AND product.prodid = assembly.prodid;

4b.

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

4c.

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

SELECT SUM(PRODCOST*quantity) FROM product, assembly 
WHERE product.prodid =
assembly.subprodid
AND product.prodid IN 
(SELECT subprodid FROM product,assembly 
WHERE proddesc = 'Animal Photography Kit' 
AND product.prodid = assembly.prodid);

4d.

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

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;

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.

SELECT wrk.empfname, boss.empfname 
FROM emp AS wrk, emp AS boss 
WHERE wrk.bossno = boss.empno 
AND wrk.deptname = 'Marketing' 
AND wrk.empsalary > 25000;

2f.

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

3.