Chapter 4

Answers to exercises

1.
Draw data models for the following entities. In each case, make certain that you show the attributes and feasible identifiers:
1a.
A farmer can have many cows, but a cow belongs to only one farmer.
1c.
An aircraft can have many passengers, but a passenger can be on only one flight at a time.
1e.
An art researcher has asked you to design a database to record details of artists and the museums in which their paintings are displayed. For each painting, the researcher wants to know the size of the canvas, year painted, title, and style. The nationality, date of birth, and death of each artist must be recorded. For each museum, record details of its location and specialty, if it has one.
2.
Report all values in British pounds:
2a.
Report the value of stocks listed in Australia. 
SELECT stkcode, stkfirm, stkprice*stkqty*exchrate 
FROM stock, nation 
WHERE stock.natcode = nation.natcode 
AND natname = 'Australia';
2c.
Report the total dividend payment by nation. 
SELECT natname, SUM(stkdiv*stkqty*exchrate) 
FROM stock, nation 
WHERE stock.natcode = nation.natcode 
GROUP BY natname;
2e.
Report the average yield by nation. 
SELECT natname, AVG(stkdiv/stkprice*100) 
FROM stock, nation 
WHERE stock.natcode = nation.natcode group BY natname;
2g.
Report the nations where the average yield of stocks exceeds the average yield of all stocks. 
SELECT natname, AVG(stkdiv/stkprice*100) 
FROM stock, nation 
WHERE stock.natcode = nation.natcode 
GROUP BY natname 
HAVING AVG(stkdiv/stkprice*100) >
(SELECT AVG(stkdiv/stkprice*100) FROM stock);
3.
How would you change the queries in exercise 4 -2 if you were required to report the values in American dollars, Australian dollars, or Indian rupees?

All exchange rates in nation are based on the UK pound, which as a result has a value of 1.00.

To convert to US dollars, all currency values must be divided by the US exchange rate (0.67 in nation). Similarly, dividing by 0.46 will convert currency values to Australian dollars. Thus, the total value of stocks in US dollars is: 

SELECT SUM(stkqty*stkprice*exchrate/0.67) 
FROM stock, nation 
WHERE stock.natcode = nation.natcode;

The question becomes more taxing when you want to extract the exchange rate from the nation table. Exchange rates vary daily, so the SQL code above is not very useful.

The query would be easy if in one row you had the stock, its exchange rate, and the exchange rate of the currency in which values should be expressed. To get a stock and its exchange rate in one row, you join the two tables. Then you must concatenate to each row the exchange rate of the target currency -- this is done by using a product (see pages 258 of the text), which is like a join without a matching condition. The other difficult part is that you need to use the nation table twice (once for the exchange rate and once for the target currency). The copies are called nat and convert and specified by nation nat, nation conver in standard SQL (nation AS nat, nation AS conver in MS Access). The final query in MS Access format is: 

SELECT SUM(stkqty*stkprice*nat.exchrate/convert.exchrate) FROM stock, nation AS nat, nation AS convert 
WHERE stock.natcode = nat.natcode 
AND convert.natname = 'United States';

Now, calculate the value of Australian stocks in Australian dollars.

5.
What is the referential integrity constraint? Why should it be enforced?

The referential integrity constraint demands that for every foreign key in a table there is a corresponding primary key in another table (later, students will learn it can be in the same table, but at this stage it is easier to handle assuming another table). In other words, for every value of the foreign key, an identical value for the corresponding primary key must exist.

7.
An orchestra has four broad classes of instruments (strings, woodwinds, brass, and percussion). Each class contains musicians who play different instruments. For example, the strings section of a full symphonic orchestra contains 2 harps, 16 to 18 first violins, 14 to 16 second violins, 12 violas, 10 cellos, and 8 double basses. A city has asked you to develop a database to store details of the musicians in its three orchestras. All the musicians are specialists and play only one instrument for one orchestra.
8.
Answer the following queries based on the following database for a car dealer:
8a.
What is the personid of Sheila O'Hara? 
SELECT personid FROM person 
WHERE persfname = 'Sheila' 
AND perslname = "O'Hara";
personid
21

Teaching Tip: The class should discuss the problems of handling O'Hara.

8c.
List details of the sales made by Bruce Bush. 
SELECT persfname, perslname, carid, carcost, carsell, cartype 
FROM person, car
WHERE person.personid = car.personid 
AND persfname = 'Bruce' 
AND perslname = 'Bush';
cartype
carcost
carsell
coupe $15,500.00 $14,750.00
sports $1,255.00 $1,355.00
sedan $950.00 $2,000.00
8e.
Report the number of cars sold of each type. 
SELECT cartype, COUNT(cartype) 
FROM car GROUP BY cartype;
cartype
expr1001
coupe 2
sedan 5
sports 3
8g.
Report details of all sales where the gross profit is less than the average. 
SELECT persfname, perslname, cartype, carsell-carcost 
FROM car, person 
WHERE person.personid = car.personid 
AND (carsell-carcost) 
< (SELECT AVG(carsell-carcost) FROM car);
persfname perslname cartype expr1003
Nolan Haley sedan $295.00
Bruce Bush coupe ($750.00)
Bruce Bush sports $100.00
Bruce Bush sedan $1,050.00
Sue Lim sedan $600.00
Barbara Capelli sedan $500.00
8i.
What is the total gross profit? 
SELECT SUM (carsell-carcost) FROM car;
expr1000
$11,295.00
8k.
Create a view containing all the details in the car table and the gross profit. 
CREATE VIEW PROFIT(id, type, cost, sell, profit) 
AS SELECT carid, cartype,carcost, carsell,carsell-carcost FROM car;

This page is part of the promotional and support material for Data Management (fifth edition) by Richard T. Watson
For questions and comments please contact the author