1 / 25

Database Management Fall 2003 The one-to-many relationship Joins, Views, Subqueries & Group by

Database Management Fall 2003 The one-to-many relationship Joins, Views, Subqueries & Group by. Add exchange rate information. Update Anomaly. Insert Anomaly. Delete Anomaly – India exchrate data lost. NATION and STOCK. Foreign Key. Update anomaly eliminated. Insert anomaly

phelan-olson
Download Presentation

Database Management Fall 2003 The one-to-many relationship Joins, Views, Subqueries & Group by

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Database Management Fall 2003 The one-to-many relationship Joins, Views, Subqueries & Group by

  2. Add exchange rate information

  3. Update Anomaly

  4. Insert Anomaly

  5. Delete Anomaly – India exchrate data lost

  6. NATION and STOCK Foreign Key

  7. Update anomaly eliminated

  8. Insert anomaly eliminated

  9. Delete anomaly eliminated

  10. Data modeling – representing the 1:M relationship Watson’s looks like this: Ours will look like this:

  11. A foreign key is a column that is a primary key of another table natcode in stock is a foreign key because natcode is the primary key of nation Record a 1:m relationship the foreign key goes on the M side foreign key-primary key matching Names for a foreign key and the corresponding primary key are often the same, but not always Foreign keys

  12. For every value of a foreign key there is a primary key with that value For every value of natcode in stock there is a value of natcode in nation A foreign key can never be null A primary key must exist before the foreign key can be defined Must create the nation before its stocks Referential integrity constraint

  13. CREATE TABLE nation ( natcode CHAR(3), natname VARCHAR(20), exchrate DECIMAL(9,5), PRIMARY KEY (natcode)); CREATE TABLE stock ( stkcode CHAR(3), stkfirm VARCHAR(20), stkprice DECIMAL(6,2), stkqty DECIMAL(8), stkdiv DECIMAL(5,2), stkpe DECIMAL(5), natcode CHAR(3), PRIMARY KEY(stkcode), CONSTRAINT fk_stock_nation FOREIGN KEY(natcode) REFERENCES nation) Creating the tables

  14. Other basic SQL o Doing a join using WHERE with = (an equijoin) and qualification. o Using GROUP BY with one or more operations (e.g., COUNT, SUM) that produce a single result (results row) for each group. o Using HAVING (functionally similar to WHERE) to pick a subset of the groups identified by a GROUP BY. o Using a subquery (inner query, nested SELECT) to define a condition to evaluate in the WHERE clause of an outer query. Also – the correlated subquery. o Using CREATE VIEW to create a view or virtual table.

  15. Join • Create a new table from two existing tables by matching on a common column • SELECT * FROM stock, nation • WHERE stock.natcode = nation.natcode

  16. Join • Report the value of each stock holding in UK pounds. • Sort the report by nation and firm. • SELECT natname, stkfirm, stkprice, stkqty, exchrate, • stkprice*stkqty*exchrate AS stkvalue • FROM stock, nation • WHERE stock.natcode = nation.natcode • ORDER BY natname, stkfirm

  17. Report by nation the total value of stockholdings. SELECT natname, SUM(stkprice*stkqty*exchrate) AS stkvalue FROM stock, nation WHERE stock.natcode = nation.natcode GROUP BY natname GROUP BY - reporting by groups

  18. Report the total value of stocks for nations with two or more listed stocks. SELECT natname, SUM(stkprice*stkqty*exchrate) AS stkvalue FROM stock, nation WHERE stock.natcode = nation.natcode GROUP BY natname HAVING COUNT(*) >= 2; HAVING - the WHERE clause of groups

  19. A query nested within another query Report the names of all Australian stocks: SELECT stkfirm FROM stock WHERE natcode IN (SELECT natcode FROM nation WHERE natname = 'Australia') Subqueries

  20. Solves the inner query many times Find those stocks where the quantity is greater than the average for that country: SELECT natname, stkfirm, stkqty FROM stock, nation WHERE stock.natcode = nation.natcode AND stkqty > (SELECT AVG(stkqty) FROM stock WHERE stock.natcode = nation.natcode) Correlated subquery

  21. An imaginary table constructed by the DBMS when required Only the definition of the view is stored, not the result CREATE VIEW stkvalue (nation, firm, price, qty, exchrate, value) AS SELECT natname, stkfirm, stkprice, stkqty, exchrate, stkprice*stkqty*exchrate FROM stock, nation WHERE stock.natcode = nation.natcode Views - virtual tables

  22. Query exactly as if a table SELECT nation, firm FROM stkvalue WHERE value > 100000 Views - querying

  23. Implementing the 1:M relationship in the actual database … Key ideas (… so to speak) o the foreign key goes on the M side o the foreign key doesn’t go on the 1 side (this would create a multi-valued attribute – bad) o foreign key-primary key matching o names for a foreign key and the corresponding primary key are often the same, but not always o referential integrity constraint: … when a record (row) on the M side references a record on the 1 side, the latter must exist! This implies that the 1-side record must be added first; it also implies that it cannot be deleted as long as any record on the M side references it.

More Related