1 -state.edu



2.3 Using Inner Joins in a QuerySome programs, generally called file managers, are limited to manipulating and extracting data from only one table at a time. However, most commercial database management systems (DBMS) can have many tables ‘open’ at the same time and can combine information from them to provide answers to our questions. As we saw in the previous chapters, we can “relate” information from one table to another by the use foreign key fields, setting up one-to-one and one-to-many relationships.Thus far we have learned to use queries to sort records, filter data, aggregate information, and to perform arithmetic calculations on fields. In this chapter we will learn to do these tasks using information from two or more tables of data. Setting up Relationships – a sample databaseWe will use the sample database in Figure 1 to illustrate the concepts introduced in this chapter. This database is an expanded version of the one used in the previous chapter and contains three tables. The table designs and sample data are discussed below. After the database structures are examined, database relationships will be defined. This will require identifying what fields can be used as primary keys and what foreign key relationships exist in our database.CLUB Database Tables:The schema for the club database is as follows:Members(ID#, Name, Phone, Address, Active )This is the membership list of all active and retired members. The Active field is a “Yes/No” Boolean field, all other fields are Text. Officers(ID#, Year, Position )A list of all past and present officers of the organization and the year and office held. ID# and Position are type text, Year is type Number.Finances(ID#, Dues-Paid, Donation )This is a record of members who have paid or partially paid their dues. The amount they have paid is entered in the Dues-Paid field. Dues for the year are $100 for active members (Retired members are not charged dues). Additional voluntary contributions are recorded in the Donations field. The ID# field is type Text and Dues-Paid and Donation fields are type Number.Notice that the data types of the fields have been defined here. Later when setting up your own databases, you will need to pay attention to the field types. As already discussed, field names need not match to setup a Relationship, but field types must match.MembersID#NamePhoneAddressActive635Acquila, Jose614-927-8345Pickerington, OHYes534Adler, Lawrence614-746-2385Pataskala, OHYes345Amico, Donald419-345-8472Toledo, OHYes 546Anderson, William513-345-9384Cincinnati, OHNo903Applegate, Bernice513-485-3833Cleveland, OHYes002Aston, Martin614-485-3888Columbus, OHNo047Attwater, Lester614-292-1442Columbus, OHYesID#Dues-PaidDonation635100053450034510050546025090300ID#Year Position3451994President0021934President6351982Vice-President5461986Secretary0471986Treasurer0091940TreasurerFinancesOfficersFigure SEQ Figure \* ARABIC 1 -Club Database – Database View of TablesCLUB Database Relationships:So how can one determine from these tables whether Mr. Adler has paid his dues? Notice that the Finance table identifies payments by ID# and not by name. The actual names of Members can only be found on the Members table, and from there it can be determined that Mr. Adler’s ID# is 534. Can ID# on the Members table be related to records with the same ID# on the Finance table? Before answering this question we need to understand the relational database structure.As we learned in the previous sections, in order to relate two tables a common/valid foreign key must exist. Such a relationship requires the following:The same information is being related. The id# 2557892 is not related to the telephone# 2557892 just because they look the same (unless telephone number is actually being used as the member ID#).Fields must be of the same field type. The membership id can be either a Text field or as a Number field. When it is a Text field, the characters ‘645’ are stored as their ASCII 8 bit equivalent for 6 then 4 then 5. When it is a Number field, the quantity 645 is saved as its numerical binary equivalent. The computer does not recognize these two representations as the same and cannot match them. The field must be primary on at least one of the tables.Also recall that the same field name in two tables does not imply a relationship exists between them. Conversely, foreign keys can be setup between two fields that do not have the same name as long as they meet the requirements of being related fields.Now, keeping these rules in mind, consider the sample database:The Members Table has an easily identifiable primary key which is the member ID#. Looking at the Finances and Officers tables it appears that this same identification is being used on both the Finance table (ID#) and on the Officers table (ID#). Since it is primary on the Members table it can be used as a foreign key to set up the relationship from the Members table to the Finances table and from the Members table to the Officers table. Can the Finances table be directly related to the Officers table by this ID? No, since neither ID# fields are primary on their respective tables.The Finances Table does not contain a clear primary key field. If it is assumed that a member may make more than one payment of the same amount, there is no field or field combination that will uniquely identify a record on the Finances Table.The Officers Table also does not contain a primary key field. Figure SEQ Figure \* ARABIC 21 ∞∞ 1 ID#ID#MembersPrimary key: ID#FinancesPrimary Key: noneOfficersThe resulting Relationships are diagrammed in Figure 2. enforcing referencial data integrityA new transaction needs to be recorded on the Officers table as follows:ID#Year Position9992007PresidentThis new transaction contains the ID# 999. Notice that there is no member ID# 999 listed on the Members table. Is this a good idea? Certainly it makes sense that not all members will serve as officers, but can the organization have a president who is not listed as a member? If the membership list contains all members, where ID# is the primary key, it makes sense that related tables, such as the Officers table, only contain member ID# numbers for persons listed on the Members table. This concept of limiting entries made in a foreign key field to those items listed in the primary key field of the related table is known as Referential Data Integrity.In Access you can choose to physically enforce Referential Data Integrity in order to avoid any unwanted problems. This feature will prompt the user with an error message if they attempt to add a record that violates this property. Not enforcing this property does not automatically mean that the database violates this rule; just that there is no mechanism in place to ensure it is not violated. Referential Data Integrity can be enforced from the Relationships window, either when initially setting up a foreign key relationship or by modifying the relationship line. The steps are as follows:Open the Relationships window using the Relationships button on the Database Tools tab.Figure SEQ Figure \* ARABIC 32122805106362500If necessary, add the tables and create the relationships as described in chapter 2.1. If adding new relationships the Edit Relationships window will automatically open. If editing existing relationships either double click on the relationship line (this can be tricky) or right-click and select Edit Relationships as seen in Figure 3.Click on the check box for Enforce Referential Data Integrity.Click Create to save the changes.Enforcing Referential Data Integrity is specific to a relationship. Enforcing this property for the relationship between Officers and Members does not automatically enforce it for the relationship between Finances and Members. Each one must be set independently. Once Referential Data Integrity is enforced there will be two other options that can be selected: Cascade Update Related Fields and Cascade Delete Related Records. Cascade Update Related Fields allows for automatic updating of related database table records if the primary key field value were to change for a specific record. In other words if member #020 is assigned a new number, say #0200, and this property is enforced for the Members–Officers relationship, all Officer records for member #020 will be updated to #0200. Similarly, Cascade Delete Related Records would delete related records if the corresponding record were deleted on the primary table. So if member #020 is deleted from the Members table and this property is enforced for the Members-Officers relationship, all Officer records for member #020 will be deleted.Based on the data shown in the Officers table in Figure 1, do all of the ID# numbers appearing on the Officers table also appear on the Members table? No, the Officers table contains an entry for Member ID# 009 while no member 009 appears on the Members table. Hence, Referential Data Integrity has definitely not been enforced for this relationship. Similarly, consider the relationship between the Members table and the Finances table. Here all member ID# numbers contained on the Finances tables also appear on the related table; Referential Data Integrity has not been violated. To know if Referential Data Integrity is enforced, one would need to check the relationship properties in the Relationships window.The Joining ProcessThe queries presented in Chapter 2.2 used data from only one object (table or query). Queries become much more powerful when information from two or more objects can be combined. The join operation combines records from two tables. In order to properly employ this tool, it is necessary to understand, at least in part, the process the computer carries out for a join operation. This chapter will explore the default Inner Join operation.An ExampleConsider the Members and Finances tables from the sample database. The Members table contains 7 records and the Finances contains 5 records. As previously discussed, the records of these tables can be related by matching the foreign key ID#. So how does Access create the dynaset from a query listing by transaction the member name, dues-paid, and donations? This query will require the transaction information (dues and donations) from the Finance table and the corresponding member name from the Members table.When creating an inner join in Access, the computer first generates a “master list” of all combinations of the records from both tables. All the elements of each record are treated as a single unit regardless of the number of fields in that record. The entire record from the first table is matched to each complete record of the second table. The resulting master list will contain 35 (7*5) different combinations. The master list resulting from the join of these two tables is shown in Figure 4. What does each of these master records represent? On Figure 4, notice that many of these combinations generated have very little practical meaning. Records are put together that don’t make sense, the name of one member is matched with the dues paid of another. However, consider the master records that have been created where the foreign key field ID# values match (highlighted records) as seen in this example.members.ID#namephoneaddressactivefinances.ID#dues-paiddonation635Acquila, Jose614-927-8345Pickerington, OHYES6351000This combined record contains a Member ID# 0f 635 for both the part of the record from the Members table and the part of the record from the Finances table. This combined record successfully matches Jose Acquilla’s name and address to the dues he has paid.If the objects specified in a query have no foreign key relationship defined, the resulting dynaset will simply be this master list with both meaningful and meaningless combined records. However, if a foreign key relationship is specified, Access will continue the join process by selecting only those records with matching foreign key values, as highlighted in Figure 4. This then provides us with only related information.members.ID#namephoneaddressactivefinances.ID#dues-paiddonation635Acquila, Jose614-927-8345Pickerington, OHYES6351000635Acquila, Jose614-927-8345Pickerington, OHYES534500635Acquila, Jose614-927-8345Pickerington, OHYES34510050635Acquila, Jose614-927-8345Pickerington, OHYES5460250635Acquila, Jose614-927-8345Pickerington, OHYES90300534Adler, Lawrence614-746-2385Pataskala, OHYES6351000534Adler, Lawrence614-746-2385Pataskala, OHYES534500534Adler, Lawrence614-746-2385Pataskala, OHYES34510050534Adler, Lawrence614-746-2385Pataskala, OHYES5460250534Adler, Lawrence614-746-2385Pataskala, OHYES90300345Amico, Donald419-345-8472Toledo, OHYES6351000345Amico, Donald419-345-8472Toledo, OHYES534500345Amico, Donald419-345-8472Toledo, OHYES34510050345Amico, Donald419-345-8472Toledo, OHYES5460250345Amico, Donald419-345-8472Toledo, OHYES90300546Anderson, William513-345-9384Cincinnati, OHNO6351000546Anderson, William513-345-9384Cincinnati, OHNO534500546Anderson, William513-345-9384Cincinnati, OHNO34510050546Anderson, William513-345-9384Cincinnati, OHNO5460250546Anderson, William513-345-9384Cincinnati, OHNO90300903Applegate, Bernice513-485-3833Cleveland, OHYES6351000903Applegate, Bernice513-485-3833Cleveland, OHYES534500903Applegate, Bernice513-485-3833Cleveland, OHYES34510050903Applegate, Bernice513-485-3833Cleveland, OHYES5460250903Applegate, Bernice513-485-3833Cleveland, OHYES903002Aston, Martin614-485-3888Columbus, OHNO63510002Aston, Martin614-485-3888Columbus, OHNO5345002Aston, Martin614-485-3888Columbus, OHNO345100502Aston, Martin614-485-3888Columbus, OHNO54602502Aston, Martin614-485-3888Columbus, OHNO9030047Attwater, Lester614-292-1442Columbus, OHYES635100047Attwater, Lester614-292-1442Columbus, OHYES53450047Attwater, Lester614-292-1442Columbus, OHYES3451005047Attwater, Lester614-292-1442Columbus, OHYES546025047Attwater, Lester614-292-1442Columbus, OHYES90300Figure SEQ Figure \* ARABIC 4members.ID#namephoneaddressactivefinances.ID#dues-paiddonation635Acquila, Jose614-927-8345Pickerington, OHYES6351000534Adler, Lawrence614-746-2385Pataskala, OHYES534500345Amico, Donald419-345-8472Toledo, OHYES34510050546Anderson, William513-345-9384Cincinnati, OHNO5460250903Applegate, Bernice513-485-3833Cleveland, OHYES90300Figure SEQ Figure \* ARABIC 5When an inner join is performed on a specific field the computer will use only those records from the master list where the join on fields match. There are 5 records with matching values in the join field creating a joined record set, as seen in Figure 5. This record set does not include any members who did not make a payment nor would it include any payments associated with a member ID# not on the Members table (this would only happen if Referential Data Integrity is violated). Hence, an inner join will only include those records on one table that have matching records on the other table. Once the combined record set is determined, the additional parameters of the query will be applied; if the query also contains select criteria, sorting instructions, aggregate functions or calculated fields, these will then be applied based on the combined record set only.Query MethodologyBefore designing queries with joined tables, let us review the query methodology but this time include the steps required when fields from different record sets (tables, queries, etc) are required.Step 1: Plan your QBE gridWhen filling out the QBE grid it is always wise to plan ahead, since the first task you will need to do is select the tables you will be required to use. In order to know which tables are required, you will need to know the following:What fields should be “shown” in the resulting query?What additional fields will be needed to specify criteria? e.g., will all records be needed or only active member records? The latter requires a criterion of Yes in the Active field.In which tables are these shown and criteria fields?What are the foreign key(s) relating these tables? Use the relationship diagram to understand what tables will be required to relate all of the information needed. If all the information is contained on one table, there will be no foreign key joins.If the information resides on multiple tables that have direct relationships then just include those tables that will be needed. If fields are required from multiple tables that are not directly related, include all “intermediate” tables even if no fields from these tables are required for output or criteria.Step 2: Add TablesUsing the Show table feature, add all of the tables that are required in the query. If two tables are required that do not have a direct link, also include the “intermediate” tables. Set up any relationships which have not previously been entered in the relationships view. Step 3: Fill out the Grid List each of the fields to be shown on the resulting dynaset.In addition, list any fields not shown but that contain criteria. Make sure to uncheck the show box for such fields if they are not part of the desired output. Fill in the necessary criteria in the appropriate fields on the Criteria and OR lines.Use the Sort feature, Group by feature, and/or Expression builder as needed.Step 4: Save your Query. Designing QBE grids for queries with a single JoinNow consider some examples of writing queries that join two tables. The techniques/limitations of joining three or more tables will be discussed in a subsequent section.Query 1: List the names of each member and donation amounts for only those transaction with donations.First plan this query: What fields will be shown? member’s name (Members table), donations (Finances table). What fields will have criteria that are not shown? there are none. What criteria are needed? The donations field must be >0 indicating a donation was madeWhat tables will we need? Fields identified reside on either the Members table or on the Finances table.Join on: Both tables required, Members and Finances, contain an ID# field, where ID# represents a member’s unique identification number. This field is primary on the Members table. Thus, a direct relationship (valid foreign key) can be established. Notice that the join field (ID#) is not listed on the QBE grid. It will only need to be listed if the ID# field is displayed or contains a criterion. The resulting query grid and dynaset are displayed in Figure 6.namedonationAmico, Donald50Anderson, William250Figure SEQ Figure \* ARABIC 6Tables: Members, Finances Join on: ID#FieldNameDonationTableMembersFinancesTotalSortShowxxCriteria>0ORThe resulting dynaset consists of only two records, one for each of the members who made a donation. Also notice that even though the join on field is ID#, this field is not required on the QBE grid. It is only necessary to list this field if it is shown or contains a criterion.Query 2: Write a query that will list the names of each officer who has served the club.First plan this query:What fields will be shown? Only the member’s name (Members table ) is requiredWhat fields will have criteria that are not shown? No criteria is listedWhat criteria are needed? None What tables will we need? Since only a field from the Members table is shown, this table must be displayed. Now the tricky part: How can the dynaset be limited to only the names of officers? Officers is not a field that a criterion can be placed on, but a separate table. Since only records for those who have severed as an officer are desired, joining the Officer table to the Members table will limit the selected records to those who are listed on both tables.Join on: These tables can be joined by ID#.The query grid and resulting dynaset are displayed in Figure 7.Tables: Members, OfficersJoin on: ID#FieldNameTableMembersTotalSortShowxCriteriaORORnameAmico, DonaldAston, MartinAcquila, JoseAnderson, WilliamAttwater, LesterFigure SEQ Figure \* ARABIC 7What would happen if a member was listed several times on the Officers table (e.g., having served several different years in different positions)? The dynaset would list this person multiple times. Query 3: Write a query to list active members only. Include their name, the total dues they’ve paid and the total donations made to date.The query grid will require both the Members and Finances tables joined by ID#. The fields Name from Members, Dues Paid and Donations from Finances, and Active from Members are also needed on the grid. To aggregate by member use the Total line and place a Group by in the Name field and then Sum in the Dues paid and Donations fields. Since only Active members are to be listed, include the Active field on the grid but uncheck the show box. Also place Where on the Total line, indicating a criteria field during the aggregation process. Figure 8 details the required query grid and resulting dynaset.Tables: Members, Finances Join on: ID#FieldNameDues-PaidDonationActiveTableMembersFinancesFinancesMembersTotalGroup bySumSumWhereSortShowxxxCriteriaYesORnameSumOfdues-paidSumOf donationAcquila, Jose1000Adler, Lawrence500Amico, Donald10050Applegate, Bernice00Figure SEQ Figure \* ARABIC 8Notice that inactive members do not appear on the list, even if they have made payments. What would happen in this query if two members had the same exact name? Their payments would be combined. A better technique would be to list and summarize by the primary key field, member ID#, so that each member is uniquely identified.Designing Queries With Multiple Inner Joins – the case of a Many-to One-to Many RelationshipNow consider a slightly different database for this club as seen in Figure 9. Notice that the Finances table data has been separated into two tables, Dues-Paid and Donations. The records have also been modified to better illustrate the use of multiple joins. The tables and relationship diagram for this modified database can be seen in REF _Ref532270647 \h Figure 9.Members Dues-PaidDonationsID#NameStatus635Acquila, JoseActive534Adler, LawrenceActive345Amico, DonaldJunior 546Anderson, WilliamIn-activeID#DateAmountID#DateAmount6351/1/011005463/1/01105462/2/01503454/2/01755464/1/01405465/1/0120Figure SEQ Figure \* ARABIC 9DonationsPrimary Key: noneDues-PaidPrimary Key: noneMembersPrimary key: ID#ID#ID#Now write a query to list by member the total dollar amount paid by each member for dues and for donations. Can dues paid and donations be combined in one query? The tables Donations and Dues-Paid cannot be directly joined as there is no valid foreign key relationship; ID# is not a primary key on either table. Can the Donations table be joined to the Members table and then Members table joined to Dues-Paid directly in one query, mimicking the relationship diagram? To answer this question we need to understand how multiple joins are executed in a relational database. We also need to understand the type of relationships between our tables.In this particular case a many-to-one-to-many relationship exists between the three tables. Each member may be associated with more than one donation transaction and more than one dues payment transaction. A specific dues transaction does not correspond to any specific donations transaction.MS Access executes multiple joins sequentially. First two of the objects (tables, queries etc) are combined together into a “joined record set” using the process described earlier in this chapter. This “joined record set” object is then joined with the third object creating another record set. If there are additional joins, this process continues until all of the joins have been executed and a final record set obtained. As an example follow the join of the Dues-Paid, Members, and Donations tables.First join the Donations and Members Tables.When joining the Donations and Members tables the following master list is generated. This list is created by combining each of the 4 records in the Members table with each of 3 records in the Donations table. 12 combinations exist, of which only some have common foreign key fields. The results are illustration in Figure 10.Member.ID#NameStatusDonations.ID#DateAmount635Acquila, JoseActive5463/1/0110534Adler, LawrenceActive5463/1/0110345Amico, DonaldJunior 5463/1/0110546Anderson, WilliamIn-active5463/1/0110635Acquila, JoseActive3454/2/0175534Adler, LawrenceActive3454/2/0175345Amico, DonaldJunior 3454/2/0175546Anderson, WilliamIn-active3454/2/0175635Acquila, JoseActive5465/1/0120534Adler, LawrenceActive5465/1/0120345Amico, DonaldJunior 5465/1/0120546Anderson, WilliamIn-active5465/1/0120Figure SEQ Figure \* ARABIC 10 – Master ListBy joining on the foreign key ID#, the combined record set will have only those records with the same values in the foreign key fields. There are three records with common ID#s, as seen in Figure 11.Member.ID#NameStatusDonations.ID#DateAmount546Anderson, WilliamIn-active5463/1/0110345Amico, DonaldJunior 3454/2/0175546Anderson, WilliamIn-active5465/1/0120Figure SEQ Figure \* ARABIC 11Note that since there were no donations made by members 534 or 635, neither one of them appear in the combined record set for an inner join.2. Next this joined record set in Figure 11 it is joined with the Dues-Paid table. Each record set has three records, so the master list will have 9 records, only some of which will have matching foreign keys field values. The master list is given in Figure 12.Member.ID#NameStatusDonations.ID#DateAmountDues-Paid.ID#DateAmount546Anderson, WilliamIn-active5463/1/01106351/1/01100345Amico, DonaldJunior 3454/2/01756351/1/01100546Anderson, WilliamIn-active5465/1/01206351/1/01100546Anderson, WilliamIn-active5463/1/01105462/2/0150345Amico, DonaldJunior 3454/2/01755462/2/0150546Anderson, WilliamIn-active5465/1/01205462/2/0150546Anderson, WilliamIn-active5463/1/01105464/1/0140345Amico, DonaldJunior 3454/2/01755464/1/0140546Anderson, WilliamIn-active5465/1/01205464/1/0140Figure SEQ Figure \* ARABIC 12Since the join was again performed on the foreign key field ID#, only those records where these two values match will be included in the inner joined record set. Four separate records meet this criterion. Thus the combined record set is as follows:Member.ID#NameStatusDonations.ID#DateAmountDues-Paid.ID#DateAmount546Anderson, WilliamIn-active5463/1/01105462/2/0150546Anderson, WilliamIn-active5465/1/01205462/2/0150546Anderson, WilliamIn-active5463/1/01105464/1/0140546Anderson, WilliamIn-active5465/1/01205464/1/0140Figure SEQ Figure \* ARABIC 13Consider the contents of the combined record set given in Figure 13:Notice that even though member #345 made a donation, since he did not make a dues payment he is not listed on the combined record set. Similarly, member #645 has made a dues payment but no donation, and he too is not listed on the combined record set. The combined record set contains are four separate records for William Anderson, ID#546. ID#1 ∞ID#∞ 1MembersPrimary key: ID#Dues-PaidPrimary Key: noneDonationsPrimary Key: noneRecall that Mr. Anderson made two separate donation payments, one for $10 and another for $20, totaling $30. He also made 2 separate dues payments, one for $50 and another for $40, totaling $90. Summarizing the dataset resulting from this 3-way join, shows that Mr. Anderson had made a total of $60 in donations and $180 in dues payments. Thus, the resulting dataset is neither an accurate reflection of this member’s transactions nor a complete record of all transactions.Figure SEQ Figure \* ARABIC 14Again look at the relationship diagram and consider the foreign key relationships more closely. There is a one-to-many relationship between the Members Table and both the Donations and Dues-Paid tables. So the three-way join can be described as a many-to-one-to-many relationship. In such cases a single record from one many side cannot be matched to one single record from the other many side; in this case a specific donation does not correspond to a specific dues payment. In a case of a many-to-one-to-many relationship a multiple join does not result in a valid record set. While Access will let you physically combine these three tables in a query, the resulting dataset will be not be valid. Designing Queries With Multiple Inner Joins – the case of a One-Many-One RelationshipWe have established that three tables cannot be directly joined if they contain a many-one-many relationship. Now we will explore the case of multiple joins for a one to many to one relationship. Since we do not already have such a relationship among our database tables, consider the modified database shown in Figure15. Here we have added a table called Type describing the different acceptable forms of payment. We have also modified the dues-paid table to include a payment type field.Members Dues-PaidTypesID#NameStatus635Acquila, JoseActive534Adler, LawrenceActive345Amico, DonaldJunior 546Anderson, WilliamIn-activeID#DateAmountTypeTypeDescip.6351/1/01100CCcash5462/2/0150CCCCcredit card5464/1/0140CCMOmoney orderFigure SEQ Figure \* ARABIC 15Figure SEQ Figure \* ARABIC 16type1 ∞ID#∞ 1MembersPrimary key: ID#Dues-PaidPrimary Key: noneTypesPrimary Key: typeThe relationships between these three tables are described in Figure 16.The Dues-Paid table can be related to the Types table using a many to one relationship joining on the foreign key Type. The Type field is primary on the Types table. Similarly, the Dues Payments table can be related to the Members table using a many to one relationship joining on the foreign key ID#. The ID# is primary on the Members table. When joining these three together a one-to-many-to-one relationship exists. Can the records from the Dues Payments table (many side) be uniquely matched to a single record on the Types table and a single record on the Members table? In this case a single dues payment record corresponds to a single unique type record and a single unique member record. For example, the first payment ($100) listed contains the value 635 in the ID# field and the value C in the Type field. The ID#635 corresponds to a single member and the type C corresponds to a single payment type (cash). So, joining these tables makes sense. But can these objects be correctly joined in a query? Let’s apply the join process to the data and take a look at the resulting record.First combine the Members table with the Dues-Paid table, creating the master list and then the joined record set. The master list generated from a join of 4 records and 3 records contains the 12 possible combinations, as seen in Figure 17. Using an inner join on the type foreign key results in a dataset that will contain 3 records with matching values in the foreign key field as seen in Figure 18.Member.ID#NameStatusDues .Paid.ID#DateAmountType635Acquila, JoseActive6351/1/01100C534Adler, LawrenceActive6351/1/01100C345Amico, DonaldJunior 6351/1/01100C546Anderson, WilliamIn-active6351/1/01100C635Acquila, JoseActive5462/2/0150CC534Adler, LawrenceActive5462/2/0150CC345Amico, DonaldJunior 5462/2/0150CC546Anderson, WilliamIn-active5462/2/0150CC635Acquila, JoseActive5464/1/0140CC534Adler, LawrenceActive5464/1/0140CC345Amico, DonaldJunior 5464/1/0140CC546Anderson, WilliamIn-active5464/1/0140CCFigure SEQ Figure \* ARABIC 17Member.ID#NameStatusDues Paid.ID#DateAmountType635Acquila, JoseActive6351/1/01100C546Anderson, WilliamIn-active5462/2/0150CC546Anderson, WilliamIn-active5464/1/0140CCFigure SEQ Figure \* ARABIC 18In the next step, the resulting record set in Figure 18 will be joined with the Types table. Unlike the previous example, these records will be joined on a second foreign key: type. The master list will contain 3*3 records for a total of nine records (Figure 19). Of these, three contain matching member ID# and matching types (Figure 20).Member.ID#NameStatusDues .Paid.ID#DateAmountDues Paid. TypeTypes.TypeDescip.635Acquila, JoseActive6351/1/01100CCcash546Anderson, WilliamIn-active5462/2/0150CCCcash546Anderson, WilliamIn-active5464/1/0140CCCcash635Acquila, JoseActive6351/1/01100CCCcredit card546Anderson, WilliamIn-active5462/2/0150CCCCcredit card546Anderson, WilliamIn-active5464/1/0140CCCCcredit card635Acquila, JoseActive6351/1/01100CMOmoney order546Anderson, WilliamIn-active5462/2/0150CCMOmoney order546Anderson, WilliamIn-active5464/1/0140CCMOmoney orderFigure SEQ Figure \* ARABIC 19Member.ID#NameStatusDues .Paid.ID#DateAmountDues Paid. TypeTypes.TypeDescip.635Acquila, JoseActive6351/1/01100CCcash546Anderson, WilliamIn-active5462/2/0150CCCCcredit card546Anderson, WilliamIn-active5464/1/0140CCCCcredit cardFigure SEQ Figure \* ARABIC 20The final record set created in Access matches each dues payment to its appropriate member and payment type. Thus a valid record set is obtained when performing multiple inner joins on objects with a one-to-many-one relationship. Using a similar technique it can also be shown that joins on objects with relationships such as one to one to many or a many to one to one will also result in valid record sets. Indeed, we can even create three or more joins in a similar manner, as long as there are no many-to-one–to-many relationships. Exercise 2.3-1 – Employee database Using Inner Joins-593725966470The following Access Tables represents staff utilization of an ad agency. Each record in the Jobs1 and Jobs2 tables represents a task performed on that job. Answer the questions in this problem based on the tables below. For each of the queries you will be asked to list all tables required to complete the query and the field(s) (foreign key) they are joined on.1. (a) Draw the relationship diagram between these 3 tables. Label each primary key and the foreign keys on your relationship diagram.Table Name:Job2Primary Key:Table Name:Job1Primary Key:Table Name:EmployeesPrimary Key: (b) Is Referential Data Integrity violated for any of these relationships? Explain. 2. Design a query to list for each Job2 task the following information: the taskID#, employee’s name, and minutes they spent on that task.Tables ________________________Join on ________________________FieldTableTotalSortShow CriteriaORORAdditional Expressions:3. Design a query to list the names of Employees who performed tasks for Job1. Names may appear more than once on your list. Tables ________________________Join on _______________________FieldTableTotalSortShow CriteriaORORAdditional Expressions:4. Design a query to list the TaskID# and employee’s name for each Job1 task which lasted less than 15 minutes. Tables _________________________Join on _________________________FieldTableTotalSortShow CriteriaORORAdditional Expressions:5. Design a query summarizing the Job1 tasks by employee. For each employee, the list should include the employee#, the employee’s name, the total minutes spent on these tasks, the average number of minutes per task, and the total number of tasks they performed for Job1.Tables______________________________Join on ________________________FieldTableTotalSortShow CriteriaORORAdditional Expressions:6. If your wrote a query joining all 3 tables (as seen below) to calculate total minutes an employee worked, what value would you get for employee#7 – Miranda? Is this value correct? Please explain. Job1Job2\s\sEmployeeYou have added a new table named Skill_list to your database with the following structure: (Job Description, Pay rate minimum, Pay rate maximum, Specialty). This table lists by job description the corresponding specialty and payrate minimums and maximums. Each job description is unique and thus can be used as a primary key.Can a valid query be written to create a listing of the Job1 tasks that includes for each task, the taskid, minutes, employee’s name, and job specialty? If so, write the query. If not, simply put “not valid” on the join-on line.Tables____________________________Join on ____________________________FieldTableTotalSortShow CriteriaORORAdditional Expressions:8. If you only wanted the name of the tasked# and the specialty for that task (i.e., no fields from the Employee table is required for output), would you still need the Employee table added to your query? If so, is an Employee table field required on your QBE grid?Exercise 2.3-2 Music Database Problem A recording studio is using an Access database to keep track of their Music recordings and customer orders. The next two pages are a printout of the tables in this database. Each recording is identified by a recording number and associated with a specific artist, music category, format (Tape, CD), number of tracks, and selling price. Each order is identified by the recording number of the purchased recording and the quantity. Prices are listed on the recordings table; however, certain artist-specific discounts (listed in the Artist table) may apply.Category OrdersCategoryMusicDescription?Order#Recording IdQuantity1Classicalorchestral?1002212Rock1950-1990?1013123Rock1990-present?102344Countryincl. Pre.1980 folk?103985Metalheavy metal?104336Rapnon-rate rap?1052927Jazzjazz & blues?106361ArtistArtistidNameDiscount1Frank ZappaNo2Grateful DeadNo3Led ZepplinYes4AC/DCNo5RushYes6REMNo7Daniel BarenboimNo8ChopinYes9Eric ClaptonNo10BachNo11Steely DanNo12SpyrogyraYes13Lynyard SkynyardNo14Garth BrooksYes15Rolling StonesNo16Stone Temple PilotsNo17NirvanaYes18Pearl JamNo19Stephanie BentleyYes20CoolioYes21Cypress HillNoMusic Database Problem Recordings Recording IDTitleArtist#CategoryidFormat#TracksSelling Price1A Decade of Steely Dan114T1082Green66C1273Houses of the Holy32T10641991132C795Let there be Rock45T6126Top 15 classics81T5157Sounds Better Louder123T1038Fresh Horses144C11229Top 15 Classics101T15710Shut up 'n play your guitar13T12811Open Air Berlin '9073T14712Never Mind176C9613Signals52C8914Jeremy186C91215Time Pieces92T111516Anthem of the Sun22T23317Alive in America113C102218Core166T9719New Adventures in Hi Fi66T9820Hopechest194C11721Hot rats13T6622Strictly Commercial13T8923Ten186T121224Who made who45T101525In the dark22T7326Aoxomoxoa22C162227Crossroads92C9728Led Zepplin III32T8829Greatest hits101T13730Grace Under Pressure52C8631Monster66T10932Hemispheres52C71233Vs186T111534Voodoo Lounge152T12335Stripped152T92236It takes a thief2010T8737Insane in the brain2110T1010Music Database Problem Draw the relationship diagram for this database. Show all primary and foreign keys.Is Referential Data Integrity violated for any of the relationships you’ve identified?What are the possible primary keys for the Recordings table? Is one preferable to the other? Why?Write a query to list all the Recording Titles that have a discount.Tables _________________________________Join on ______________________FieldTableTotalSortShow CriteriaORORAdditional Expressions:5. Write a query to list the Recording Titles for all Rock albums that have either at least 8 tracks or are available as a CD.Tables _________________________________Join on ______________________FieldTableTotalSortShow CriteriaORORAdditional expressions:6. Write a query to List Recording Titles of all Rock CDs priced between $10 and $20 (inclusive).Tables _________________________________Join on ______________________FieldTableTotalSortShow CriteriaORORAdditional expressions:7. Write a query to list Recording Titles that either have a discount or are Classical.Tables ________________________________Join on __________________FieldTableTotalSortShow CriteriaORORAdditional expressions:8. Write a query to display the value of all orders. List the Recording title and value of the order (price * quantity).Tables ________________________________Join on __________________FieldTableTotalSortShow CriteriaORORAdditional expressions:9. How would you change this query to summarize the orders by Recording ID? Could you include the order number when doing this summary by Recording ID?10. Write a query to display the order number and name of the artist for each recording sold.Tables ________________________________Join on __________________FieldTableTotalSortShow CriteriaORORAdditional expressions:11. Write a query to summarize by Artist the number of different recordings offered for that artist and their average selling price.Tables ________________________________Join on __________________FieldTableTotalSortShow CriteriaORAdditional expressions:Practice GridsTables ________________________________Join on __________________FieldTableTotalSortShow CriteriaORORAdditional expressions:Tables ________________________________Join on __________________FieldTableTotalSortShow CriteriaORORAdditional expressions:-420370334645Exercise 2.3-3 Chapter Review – Lone Star Database The tables above represent the database system for the Lone Star Construction Company. The Employee table lists the names and addresses of all the company’s employees. The employee’s SSN is used as employee identification to track employee information. The Job table lists the hourly wages for each job. The Assignments table lists the hire dates and job assignments for the employees; an employee can have only one job assignment. The Timesheet table lists the number of hours that each employee worked each day. Note that only partial tables are listed above. The database contains much more data.Database Relationships. Set up the relationships of this database. Using the boxes below, fill in the primary key (if any) of each table and draw relationship lines between tables. Label each relationship with the name of the foreign key(s).Table name:JobPrimary KeyTable name:EmployeePrimary KeyTable name:TimesheetPrimary KeyTable name:AssignmentsPrimary KeyHas referential data integrity been violated for any of these relationships (consider only the data shown)? Explain.Using the query design view below, construct a query to list the names of all the Houston residents in alphabetical order by last name, then first name.Tables Used __________________________________ Join on __________________FieldTableTotalSortShow CriteriaORORList the names and social security numbers of all employees hired in the first half of the year 2000 (from January 1 through June 30).Tables Used __________________________________ Join on _____________________FieldTableTotalSortShow CriteriaORCreate a list of pay rates for all employees who live in Houston and earn more than $10 per hour. Include their last name, SSN, hire date, and hourly pay rate.Tables Used __________________________________ Join on _____________________FieldTableTotalSortShow CriteriaORORCreate a job assignment list. The list should include all employees’ full names and their job titles.Tables Used __________________________________ Join on _____________________FieldTableTotalSortShow CriteriaORORAdditional Expressions:The carpenters’ union has been complaining that you have been working them too many hours each day. Calculate the maximum, minimum, and average number of hours that the carpenters spend working each day.Tables Used __________________________________ Join on _____________________FieldTableTotalSortShow CriteriaORORCreate a pay sheet for the week of January 29, 2001, through February 4, 2001. For each employee, list his or her Employee ID, the number of days worked that week, the total number of hours worked, and the total amount to be paid on that week’s paycheck.Tables Used __________________________________ Join on _____________________FieldTableTotalSortShow CriteriaORORAdditional expressions: ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download