4 - The One-to-Many Relationship

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.

A foreign key is used to implement a 1:m relationship. In fact, the foreign key is an extra column which has been added to the table at the many end of the 1:m relationship. This extra column contains the value of the primary key of the table at the one end of the 1:m relationship. Through the foreign key, the two tables can be linked.

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.

The character's name would not be a sufficient identifier since the same character name could be used in many plays. However, each character name is typically only used once in each play. If this is not true for these plays, one would have to create a characterid to uniquely distinguish each character.