Answers to exercises

1.

Draw data models for the following situations. 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 JOIN nation 
ON stock.natcode = nation.natcode  
WHERE natname = 'Australia';

2c.

Report the total dividend payment by nation.

SELECT natname, SUM(stkdiv*stkqty*exchrate) 
FROM stock JOIN nation 
ON stock.natcode = nation.natcode 
GROUP BY natname;

2e.

Report the average yield by nation.

SELECT natname, AVG(stkdiv/stkprice*exchrate*100) 
FROM stock JOIN nation 
ON 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 JOIN nation 
ON 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 JOIN nation 
ON 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 and save as tempory table T1. 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). 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). This query challenges your skills as this stage, but it is important to know that SQL can handle such requests.

SELECT `Value`/exchrate FROM (SELECT SUM(stkqty*stkprice*exchrate) AS `Value` 
FROM stock JOIN nation 
ON stock.natcode = nation.natcode) AS T1, nation 
WHERE 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 `person first` = 'Sheila' 
AND `person last` = "O'Hara";

personid
21

8c.

List details of the sales made by Bruce Bush.

SELECT cartype, carcost, carsell 
FROM person JOIN car
ON person.personid = car.personid 
WHERE `person first` = 'Bruce' 
AND `person last` = 'Bush';

cartype	carcost	carsell
coupe	15500.00	14750.00
sports	1255.00	1355.00
sedan	950.00	2000.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 `person first`, `person last`, cartype, carsell-carcost AS profit
FROM car JOIN person 
ON person.personid = car.personid 
WHERE (carsell-carcost) 
< (SELECT AVG(carsell-carcost) FROM car);

person first	person last	cartype	profit
Nolan	Haley	sedan	295.00
Bruce	Bush	coupe	-750.00
Bruce	Bush	sports	100.00
Bruce	Bush	sedan	1050.00
Sue	Lim	sedan	600.00
Barbara	Capelli	sedan	500.00

8i.

What is the total gross profit?

SELECT SUM(carsell-carcost) as `Gross profit` FROM car;

Gross profit
11295.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;

9.

Find stocks where the third or fourth letter in their name is an 'm'.

SELECT stkfirm FROM stock WHERE stkfirm REGEXP '^(.){2}m|^(.){3}m';

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

4 - The One-to-Many Relationship

Answers to exercises

Date revised: 17-Oct-2022