1 / 24

Relational example using donor, donation and drive tables

Relational example using donor, donation and drive tables. For additional information see the speaker notes!. Donor table. SQL> DESC donor; Name Null? Type ------------------------------- -------- ---- IDNO VARCHAR2(5)

bedros
Télécharger la présentation

Relational example using donor, donation and drive tables

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. Relational example using donor, donation and drive tables For additional information see the speaker notes!

  2. Donor table SQL> DESC donor; Name Null? Type ------------------------------- -------- ---- IDNO VARCHAR2(5) NAME VARCHAR2(15) STADR VARCHAR2(15) CITY VARCHAR2(10) STATE VARCHAR2(2) ZIP VARCHAR2(5) DATEFST DATE YRGOAL NUMBER(7,2) CONTACT VARCHAR2(12) SQL> SELECT * FROM donor; IDNO NAME STADR CITY ST ZIP DATEFST YRGOAL CONTACT ----- --------------- --------------- ---------- -- ----- --------- --------- ------------ 11111 Stephen Daniels 123 Elm St Seekonk MA 02345 03-JUL-98 500 John Smith 12121 Jennifer Ames 24 Benefit St Providence RI 02045 24-MAY-97 400 Susan Jones 22222 Carl Hersey 24 Benefit St Providence RI 02045 03-JAN-98 Susan Jones 23456 Susan Ash 21 Main St Fall River MA 02720 04-MAR-92 100 Amy Costa 33333 Nancy Taylor 26 Oak St Fall River MA 02720 04-MAR-92 50 John Adams 34567 Robert Brooks 36 Pine St Fall River MA 02720 04-APR-98 50 Amy Costa 6 rows selected.

  3. Donation table SQL> DESC donation; Name Null? Type ------------------------------- -------- ---- IDNO VARCHAR2(5) DRIVENO VARCHAR2(3) CONTDATE DATE CONTAMT NUMBER(6,2) SQL> SELECT * FROM donation; IDNO DRI CONTDATE CONTAMT ----- --- --------- --------- 11111 100 07-JAN-99 25 12121 200 23-FEB-99 40 23456 100 03-MAR-99 20 33333 300 10-MAR-99 10 22222 100 14-MAR-99 10 12121 100 04-JUN-99 50 11111 200 12-JUN-99 35 23456 300 14-JUN-99 10 8 rows selected.

  4. Drive table SQL> DESC drive; Name Null? Type ------------------------------- -------- ---- DRIVENO VARCHAR2(3) DRIVENAME VARCHAR2(15) DRIVECHAIR VARCHAR2(12) LASTYEAR NUMBER(8,2) THISYEAR NUMBER(8,2) SQL> SELECT * FROM drive; DRI DRIVENAME DRIVECHAIR LASTYEAR THISYEAR --- --------------- ------------ --------- --------- 100 Kids Shelter Ann Smith 10000 0 200 Animal Home Linda Grant 5000 0 300 Health Aid David Ross 7000 0 400 Half Way Robert Doe 0 0

  5. Relationships SQL> SELECT * FROM donor; IDNO NAME STADR CITY ST ZIP DATEFST YRGOAL CONTACT ----- --------------- --------------- ---------- -- ----- --------- --------- ------------ 11111 Stephen Daniels 123 Elm St Seekonk MA 02345 03-JUL-98 500 John Smith 12121 Jennifer Ames 24 Benefit St Providence RI 02045 24-MAY-97 400 Susan Jones 22222 Carl Hersey 24 Benefit St Providence RI 02045 03-JAN-98 Susan Jones 23456 Susan Ash 21 Main St Fall River MA 02720 04-MAR-92 100 Amy Costa 33333 Nancy Taylor 26 Oak St Fall River MA 02720 04-MAR-92 50 John Adams 34567 Robert Brooks 36 Pine St Fall River MA 02720 04-APR-98 50 Amy Costa The relationship or link between the donor table and the donation table is based on idno. In other words, the idno appears in both tables. SQL> SELECT * FROM donation; IDNO DRI CONTDATE CONTAMT ----- --- --------- --------- 11111100 07-JAN-99 25 12121 200 23-FEB-99 40 23456 100 03-MAR-99 20 33333 300 10-MAR-99 10 22222 100 14-MAR-99 10 12121 100 04-JUN-99 50 11111 200 12-JUN-99 35 23456 300 14-JUN-99 10 The relationship or link between the donation table and the drive table is based on driveno. In other words, the driveno appears in both tables. SQL> SELECT * FROM drive; DRI DRIVENAME DRIVECHAIR LASTYEAR THISYEAR --- --------------- ------------ --------- --------- 100 Kids Shelter Ann Smith 10000 0 200 Animal Home Linda Grant 5000 0 300 Health Aid David Ross 7000 0 400 Half Way Robert Doe 0 0 NOTE: There is no direct link between the donor table and the drive table.

  6. Types of relationships • In a relational database there are the following kinds of relationships: • One to One • One to Many • Many to Many

  7. One to One relationship One to One Relationship: If the donation table only allowed one donation for every donor then we would have a one to one relationship. SQL> SELECT * FROM one_donor; IDNO NAME STADR CITY ST ZIP DATEFST YRGOAL CONTACT ----- --------------- --------------- ---------- -- ----- --------- --------- ------------ 11111Stephen Daniels 123 Elm St Seekonk MA 02345 03-JUL-98 500 John Smith 12121 Jennifer Ames 24 Benefit St Providence RI 02045 24-MAY-97 400 Susan Jones 22222 Carl Hersey 24 Benefit St Providence RI 02045 03-JAN-98 Susan Jones 23456 Susan Ash 21 Main St Fall River MA 02720 04-MAR-92 100 Amy Costa 33333 Nancy Taylor 26 Oak St Fall River MA 02720 04-MAR-92 50 John Adams 34567 Robert Brooks 36 Pine St Fall River MA 02720 04-APR-98 50 Amy Costa 6 rows selected. SQL> SELECT * 2 FROM one_donation 3 ORDER BY idno; IDNO DRI CONTDATE CONTAMT ----- --- --------- --------- 11111100 07-JAN-99 25 12121 200 23-FEB-99 40 22222100 14-MAR-99 10 23456 100 03-MAR-99 20 33333300 10-MAR-99 10 34567 200 05-NOV-97 35 6 rows selected.

  8. One to Many relationship One to Many Relationship: In this example, one donor can give multiple donations but each donation only has one donor. SQL> SELECT * FROM one_donor; IDNO NAME STADR CITY ST ZIP DATEFST YRGOAL CONTACT ----- --------------- --------------- ---------- -- ----- --------- --------- ------------ 11111Stephen Daniels 123 Elm St Seekonk MA 02345 03-JUL-98 500 John Smith 12121 Jennifer Ames 24 Benefit St Providence RI 02045 24-MAY-97 400 Susan Jones 22222 Carl Hersey 24 Benefit St Providence RI 02045 03-JAN-98 Susan Jones 23456 Susan Ash 21 Main St Fall River MA 02720 04-MAR-92 100 Amy Costa 33333 Nancy Taylor 26 Oak St Fall River MA 02720 04-MAR-92 50 John Adams 34567 Robert Brooks 36 Pine St Fall River MA 02720 04-APR-98 50 Amy Costa 6 rows selected. SQL> SELECT * 2 FROM donation 3 ORDER BY idno; IDNO DRI CONTDATE CONTAMT ----- --- --------- --------- 11111 100 07-JAN-99 25 11111 200 12-JUN-99 35 12121 200 23-FEB-99 40 12121 100 04-JUN-99 50 22222 100 14-MAR-99 10 23456 100 03-MAR-99 20 23456 300 14-JUN-99 10 33333 300 10-MAR-99 10 8 rows selected. NOTE: There is no donation for 34567 in this example.

  9. Many to many relationship Many to Many Relationship: In this example, each movie can have many stars and each star can have many movies, therefore this is a many to many relationship. SQL> SELECT * 2 FROM rel_movie; MOVIENO MOVIENAME --------- -------------------- 1111SQL movie 2222 Oracle movie 3333 BCC movie SQL> SELECT * 2 FROM rel_star; STARNO STARNAME --------- -------------------- 1234 Stephen Davis 2345 Jennifer Ames 3456Carl Hersey 4567 Maureen Taylor Bridge table - used with many to many. SQL> SELECT * 2 FROM 3 movie_star; MOVIENO STARNO --------- --------- 11111234 11112345 1111 3456 22222345 2222 3456 33331234 33334567

  10. Keys - primary PRIMARY KEY Each table should have a primary key that identifies one and only one record. Sometimes the primary key is one column/field, sometimes it is a combination of column/fields. In this example it is one column/field, the IDNO. SQL> SELECT * FROM donor; IDNO NAME STADR CITY ST ZIP DATEFST YRGOAL CONTACT ----- --------------- --------------- ---------- -- ----- --------- --------- ------------ 11111 Stephen Daniels 123 Elm St Seekonk MA 02345 03-JUL-98 500 John Smith 12121 Jennifer Ames 24 Benefit St Providence RI 02045 24-MAY-97 400 Susan Jones 22222 Carl Hersey 24 Benefit St Providence RI 02045 03-JAN-98 Susan Jones 23456 Susan Ash 21 Main St Fall River MA 02720 04-MAR-92 100 Amy Costa 33333 Nancy Taylor 26 Oak St Fall River MA 02720 04-MAR-92 50 John Adams 34567 Robert Brooks 36 Pine St Fall River MA 02720 04-APR-98 50 Amy Costa 6 rows selected In the donor table, it can be seen that there is a different IDNO for each record/row. In other words, the IDNO uniquely identifies a record/row. If I do a SELECT using a where for a particular IDNO, it will only return one record.

  11. Keys - primary In this example, the primary key is not as easy to determine. Clearly the IDNO does not work as the primary key because there are multiple of the same IDNO. This means IDNO needs to be combined with something else. If we combine it with DRIVENO, it will work for these 8 records because there is no record/row where the IDNO plus the DRIVENO are the same. However, it is very possible that a donor could contribute multiple times to the same drive so this is not sufficient. If we combine IDNO plus DRIVE plus CONTDATE as the primary key we will be as long as we decide that the same donor cannot contribute to the same drive on the same date. That is reasonable so we will go with the combination of those three fields to make the primary key. SQL> SELECT * 2 FROM donation 3 ORDER BY idno; IDNO DRI CONTDATE CONTAMT ----- --- --------- --------- 11111 100 07-JAN-99 25 11111 200 12-JUN-99 35 12121 200 23-FEB-99 40 12121 100 04-JUN-99 50 22222 100 14-MAR-99 10 23456 100 03-MAR-99 20 23456 300 14-JUN-99 10 33333 300 10-MAR-99 10 8 rows selected.

  12. Keys - primary In this example, the primary key is DRIVENO. DRIVENO uniquely defines each record/row in the table. Another drive with the same drive number would not make sense in this table. SQL> SELECT * FROM drive; DRI DRIVENAME DRIVECHAIR LASTYEAR THISYEAR --- --------------- ------------ --------- --------- 100 Kids Shelter Ann Smith 10000 0 200 Animal Home Linda Grant 5000 0 300 Health Aid David Ross 7000 0 400 Half Way Robert Doe 0 0

  13. Keys - foreign SQL> SELECT * 2 FROM donation 3 ORDER BY idno; IDNO DRI CONTDATE CONTAMT ----- --- --------- --------- 11111 100 07-JAN-99 25 11111 200 12-JUN-99 35 12121 200 23-FEB-99 40 12121 100 04-JUN-99 50 22222 100 14-MAR-99 10 23456 100 03-MAR-99 20 23456 300 14-JUN-99 10 33333 300 10-MAR-99 10 8 rows selected. The DRIVENO on the donation table is called a foreign key because it links into the primary key on the drive table. SQL> SELECT * FROM drive; DRI DRIVENAME DRIVECHAIR LASTYEAR THISYEAR --- --------------- ------------ --------- --------- 100 Kids Shelter Ann Smith 10000 0 200 Animal Home Linda Grant 5000 0 300 Health Aid David Ross 7000 0 400 Half Way Robert Doe 0 0

  14. Select Donation table: IDNO DRI CONTDATE CONTAMT ----- --- --------- --------- 11111 100 07-JAN-99 25 11111 200 12-JUN-99 35 12121 200 23-FEB-99 40 12121 100 04-JUN-99 50 22222 100 14-MAR-99 10 23456 100 03-MAR-99 20 23456 300 14-JUN-99 10 33333 300 10-MAR-99 10 Drive table: DRI DRIVENAME DRIVECHAIR LASTYEAR THISYEAR --- --------------- ------------ --------- --------- 100Kids Shelter Ann Smith 10000 0 200Animal Home Linda Grant 5000 0 300Health Aid David Ross 7000 0 400 Half Way Robert Doe 0 0 SQL> SELECT idno, donation.driveno, drivename, contamt 2 FROM donation, drive 3 WHERE donation.driveno = drive.driveno; IDNO DRI DRIVENAME CONTAMT ----- --- --------------- --------- 11111 100 Kids Shelter 25 23456 100 Kids Shelter 20 22222 100 Kids Shelter 10 12121 100 Kids Shelter 50 12121 200 Animal Home 40 11111 200 Animal Home 35 33333 300 Health Aid 10 23456 300 Health Aid 10 8 rows selected. The select takes the idno from the donation table, the driveno from the donation table, the drivename from the drive table and the contamt from the donation table. The driveno from the donation table links to the matching driveno in the drive table and retrieves the drive name from that row.

  15. Select In the SELECT for this example, all columns are presented with the table name.column name format. This is a good way to code for clarity. The person reading the code knows exactly where the data is coming from. The FROM clause lists the table used for this query. SQL> SELECT donation.idno, donation.driveno, drive.drivename, donation.contamt 2 FROM donation, drive 3 WHERE donation.driveno = drive.driveno; IDNO DRI DRIVENAME CONTAMT ----- --- --------------- --------- 11111 100 Kids Shelter 25 23456 100 Kids Shelter 20 22222 100 Kids Shelter 10 12121 100 Kids Shelter 50 12121 200 Animal Home 40 11111 200 Animal Home 35 33333 300 Health Aid 10 23456 300 Health Aid 10 8 rows selected. The link is made between the two tables. The donation.driveno will be linked by driveno to the drive.driveno so that the name for the drive can be retrieved.

  16. Select In this example, I am using aliases. The alias gets set up in the FROM clause when the table names are listed. The alias is then used in the SELECT and in the WHERE. This provides a shortened version of the table name. SQL> SELECT d.idno, d.driveno, r.drivename, d.contamt 2 FROM donation d, drive r 3 WHERE d.driveno = r.driveno; IDNO DRI DRIVENAME CONTAMT ----- --- --------------- --------- 11111 100 Kids Shelter 25 23456 100 Kids Shelter 20 22222 100 Kids Shelter 10 12121 100 Kids Shelter 50 12121 200 Animal Home 40 11111 200 Animal Home 35 33333 300 Health Aid 10 23456 300 Health Aid 10 8 rows selected.

  17. Select This involves three tables: donation donor drive The donation table is being processed, for each donation I want to link into the donor table to get the donor name and link into the drive table to get the drive name. These links are handled in the WHERE clause where I establish the relationship between the idno on the donation table and the idno on the donor table as well as the relationship between the driveno on the donation table and the driveno on the drive table. Once the links have been established the SELECT will retrieve the appropriate data according to the link paths. SQL> SELECT donation.idno, donor.name, donation.driveno, drive.drivename, donation.contamt 2 FROM donation, donor, drive 3 WHERE donation.idno = donor.idno AND donation.driveno = drive.driveno; IDNO NAME DRI DRIVENAME CONTAMT ----- --------------- --- --------------- --------- 11111Stephen Daniels100Kids Shelter25 23456Susan Ash100Kids Shelter20 22222Carl Hersey100Kids Shelter10 12121Jennifer Ames100 Kids Shelter50 12121Jennifer Ames 200Animal Home40 11111Stephen Daniels200Animal Home35 33333Nancy Taylor300Health Aid10 23456Susan Ash300 Health Aid10 8 rows selected.

  18. JOIN • In a relational database, frequently you need to connect the data in two or more tables. The process that is used to do this is called a join. • There are four kinds of joins that we will look at in this presentation: • equijoins • non-equijoins • outer joins • self joins

  19. Equijoin The examples that we have been looking at are equijoins. The WHERE clause establishes the link between tables and the values being compared in the two tables must be equal. SQL> SELECT donation.idno, donation.driveno, drive.drivename, donation.contamt 2 FROM donation, drive 3 WHERE donation.driveno = drive.driveno; IDNO DRI DRIVENAME CONTAMT ----- --- --------------- --------- 11111 100 Kids Shelter 25 23456 100 Kids Shelter 20 22222 100 Kids Shelter 10 12121 100 Kids Shelter 50 12121 200 Animal Home 40 11111 200 Animal Home 35 33333 300 Health Aid 10 23456 300 Health Aid 10 8 rows selected.

  20. Non-equijoin SQL> SELECT * from grade; NAME GRADELEVEL -------------------- ---------- First Grade 1 Second Grade 2 Third Grade 3 Seventh Grade 7 Freshman 9 Senior 12 SQL> SELECT * FROM schools; GROUPNAME LOWGRADE HIGHGRADE -------------------- --------- --------- Elementary 1 6 Junior High 7 8 High School 9 12 SQL> SELECT grade.name, grade.gradelevel, schools.groupname 2 FROM grade, schools 3 WHERE grade.gradelevel BETWEEN schools.lowgrade AND schools.highgrade; NAME GRADELEVEL GROUPNAME -------------------- ---------- -------------------- First Grade 1 Elementary Second Grade 2Elementary Third Grade 3 Elementary Seventh Grade 7 Junior High Freshman 9 High School Senior 12 High School 6 rows selected. Looking at the second record on the grade, the 2 is checked against the lowgrade and highgrade on schools. It falls between 1 and 6 so the name associated with the 1 and 6, Elementary is displayed.

  21. Outer join SQL> SELECT * FROM donation; IDNO DRI CONTDATE CONTAMT ----- --- --------- --------- 11111 100 07-JAN-99 25 12121 200 23-FEB-99 40 23456 100 03-MAR-99 20 33333 300 10-MAR-99 10 22222 100 14-MAR-99 10 12121 100 04-JUN-99 50 11111 200 12-JUN-99 35 23456 300 14-JUN-99 10 SQL> SELECT driveno, drivename FROM drive; DRI DRIVENAME --- --------------- 100 Kids Shelter 200 Animal Home 300 Health Aid 400 Half Way Driveno 400 is not listed on the donation table. It is listed on the drive table. SQL> SELECT donation.driveno, donation.contamt, drive.driveno, drive.drivename 2 FROM donation, drive 3 WHERE donation.driveno(+) = drive.driveno; DRI CONTAMT DRI DRIVENAME --- --------- --- --------------- 100 25 100 Kids Shelter 100 20 100 Kids Shelter 100 10 100 Kids Shelter 100 50 100 Kids Shelter 200 40 200 Animal Home 200 35 200 Animal Home 300 10 300 Health Aid 300 10 300 Health Aid 400 Half Way 9 rows selected. Driveno 400 is missing on the donation side but it is present on the drive side. Therefore in setting up the test, the (+) is put on the donation side to indicate that the data may be missing on this side. In the output, the drive information shows up even though there is no matching donation information.

  22. equijoin This is a continuation of the previous slide, notice that there is no (+) in the where. Therefore drive 400 does not appear in the results. This was matching on equality and drive 400 does not have any matches on the donation side. SQL> SELECT donation.driveno, donation.contamt, drive.driveno, drive.drivename 2 FROM donation, drive 3 WHERE donation.driveno = drive.driveno; DRI CONTAMT DRI DRIVENAME --- --------- --- --------------- 100 25 100 Kids Shelter 100 20 100 Kids Shelter 100 10 100 Kids Shelter 100 50 100 Kids Shelter 200 40 200 Animal Home 200 35 200 Animal Home 300 10 300 Health Aid 300 10 300 Health Aid 8 rows selected.

  23. Self join SQL> SELECT * FROM boss; IDNO NAME MANAGER --------- -------------------- --------- 11 John Doe 33 22 Mary Smith 55 33 David Wall 22 44 Susan Ash 22 55 Linda Jones 66 Roger Costa 55 The goal of this select is to list the manager name for each person. Notice that since Linda Jones has no manager, she is not listed. SQL> SELECT fst.idno, fst.name, fst.manager, snd.idno, snd.name 2 FROM boss fst, boss snd 3 WHERE fst.manager = snd.idno 4 ORDER BY fst.idno; IDNO NAME MANAGER IDNO NAME --------- -------------------- --------- --------- -------------------- 11 John Doe 33 33 David Wall 22 Mary Smith 55 55 Linda Jones 33 David Wall 22 22 Mary Smith 44 Susan Ash 22 22 Mary Smith 66 Roger Costa 55 55 Linda Jones

  24. Self join/outer join SQL> SELECT * FROM boss; IDNO NAME MANAGER --------- -------------------- --------- 11 John Doe 33 22 Mary Smith 55 33 David Wall 22 44 Susan Ash 22 55 Linda Jones 66 Roger Costa 55 Manager has a null that does not appear in the idno field. This code assures that Linda Jones who has no manager will appear. SQL> SELECT fst.idno, fst.name, fst.manager, snd.idno, snd.name 2 FROM boss fst, boss snd 3 WHERE fst.manager = snd.idno(+) 4 ORDER BY fst.idno; IDNO NAME MANAGER IDNO NAME --------- -------------------- --------- --------- -------------------- 11 John Doe 33 33 David Wall 22 Mary Smith 55 55 Linda Jones 33 David Wall 22 22 Mary Smith 44 Susan Ash 22 22 Mary Smith 55 Linda Jones 66 Roger Costa 55 55 Linda Jones

More Related