Purpose: - Home | Computer Science and Engineering



CS&E 1111/1112 PRE Lab: 8 Access Tables Purpose: The purpose of this lab is to introduce databases and the use of Microsoft Access. This lab will cover the basic elements of setting up and using Tables.Pre-Lab Assignment –complete before coming to lab:Part 1 – Using AccessBEFORE BEGINNING THIS LAB: Read the Chapter 2.1 Course Notes posted on Carmen. (Pages 1-14) Skip pages 11-12—Indexing Tables and Search SchemesAfter reading Chapter 2.1, read the information below.What is a Database?A database is used to store information about a person, place, or thing. Almost all information retrieved from a computer is stored in a database. There are several different types of databases or what we call database models. One of the database models used is called the relational database model. This model stores information in files, tables, records, and fields. Steps when creating a DatabaseThere are several steps that should be taken when creating a new database. These steps should usually be in the following order, although sometimes that may not be the case.Part 1 – Design and Document the databaseThe first four steps when creating a database entail documenting and designing the database. This document will serve as a map for creating the actual database itself.Decide what information you will store in the database and document it on paper.Create a layout of the database and document it on paper.Normalize the database and document it on paper. Create a Relationship Diagram on paper.Identify the Tables, Relationships, Primary Keys and Foreign Keys.Part 2 – Create the database using Ms Office Access 2010 or any other database software.The final steps entail the creation and setup of the database using the MS Access 2010 software.Create the database structure in MS Access 2010. Create the table relationships and Enforce Referential Integrity in MS Access 2010.Populate the MS Access 2010 database.This entails typing the information into the newly created database.Step 1 – Decide what information you will store in the database.One of the first steps in creating a database is to decide what information you will store in the database. For example, let’s say you have started a small business using MS Word to type research papers for the students at your school. You want to create a database to store all your charges and payments so you can collect your fees in a timely manner. What information do you need to store and how will you make the information for each person unique? For the purpose of this example, we will only store the minimum amount of information needed to keep track of your charges and payments received. Figure 1.1 shows the basic information needed to create this database.First NameCharge AmountLast NameCharge DateAddressPayment AmountCityPayment TypeStatePayment DateZip CodeHome PhoneFigure 1.1This database is a very simplistic one. Most databases you create will be much more complex. Therefore, when you are collecting the information needed for your database, you should always consult the people who actually do the work. You must know the business in order to create the database, so involve the people who do the business!Step 2 – Create the layout of your database on paperOnce you have decided the information to be included in your database, you must now create the layout of the database. The layout consists of the tables, records, fields, field types, and field properties that will be included in your database. Basically, this process of designing a solid and accurate database is called Normalization. In order to normalize the database, you must group related fields into tables, while eliminating any duplicate data in the database except the primary keys and foreign keys which will relate the tables together. You also must create a primary key for most of the tables in your database. What is a primary key? A primary key is the one field that will make each record unique in your database. (Note: You can create a primary key from more than one field, but that is beyond the scope of this book.) Have you ever ordered a pizza? What is the first question usually asked when you call…Could I have your home phone number please? It’s the home phone number that makes you unique as a Client. Your social security number makes you unique in the United States, and your Student ID makes you unique at most Universities. We will create a unique field for each of our clients ourselves. Figure 1.2 displays the table I have created from the information I have decided I need to store in Figure 1.1. I have created one table called Client, which will store all of the personal information needed for each client, and all the charges and payments for each client. Each piece of this information is called a field, and all of the fields combined for one person constitute one record. Hence, if I have 3 clients in the table in Figure 1.2, I have 3 records and 39 fields. Notice this database has not been normalized.Research Papers Database (Not Normalized)Table Name:ClientField TypeProperties(Field Size)Fields:ClientIDText5Primary KeyFirstNameText25LastNameText50AddressText60CityText25StateText2ZipCodeText5HomePhoneText10ChargeAmountCurrencyNoneChargeDateDateNonePaymentAmountCurrencyNonePaymentDateDateNonePaymentTypeText2Figure 1.2Why not just create one big table like the one above and call it a day? The problem here is that every time a client is charged or a payment is made, all of the information needs to be re-input for each client. This causes quite a bit of information to be duplicated and could also leave room for inaccurate information. What if the first record has our Client listed as Rebecca Simpson, and the 2nd record for her has her listed as Becky Simpson? Now our information is incorrect. Are Rebecca Simpson and Becky Simpson the same people? What if we have a Rebecca Simpson at PO Box 189 and a Rebecca Simpson at PO Box 198? Easy, you say, just look at the database to see if the information looks the same. That is fine with a small database, but what if you have a database with thousands of records? That would be a waste of time. Another problem that this creates is wasted disk space, because we are duplicating our information over and over. Step 3 -Normalize the DatabaseAs previously stated, in order to normalize the database, you must group related fields into tables while eliminating any duplicate data in the database except the primary keys and foreign keys which will relate the tables together. You also must create a primary key for most of the tables in your database. Figure 1.4 shows the database normalized. (There are many different forms of Normalization. This book will not detail the different forms.)Research Papers Database (Normalized)Table Name:ClientField TypePropertiesFields:Client IDText5 Characters longPrimary KeyFirst NameText25 Characters longLast NameText50 Characters longAddressText60 Characters longCityText25 Characters longStateText2 Characters longDefault ValueZip CodeText5 Characters longHome PhoneText10 Characters longInput MaskTable Name:ChargesField TypePropertiesFields:Client IDText5 Characters longCharge AmountCurrencyNoneCharge DateDateInput MastTable Name:PaymentsField TypePropertiesFields:Client IDText5 Characters longPayment AmountCurrencyNonePayment TypeText2 Characters longPayment DateDateInput MaskTable Name:PaymentMethodField TypePropertiesFields:MethodIDText2 Characters longMethodTypeText20 Characters longFigure 1.4Notice now there are four tables created.Client Table: Stores all the personal information about the client. Each client will only need one record in this table, thus duplication of information and inaccuracy has been reduced drastically. Notice the primary key is ClientID. This is the field we will use to make each record unique in the Client table.Charges Table: This table stores all information on client charges. Because we may have many charges for one client, we will not have a primary key on this table. However, we will need a field that tells us what charge is related to what client. For this we will use the ClientID field. Therefore, for every Client in the Client table, we can have zero to many charges for that Client in the Charges table.Payments Table: This table stores all information on client payments. Because we may have many payments for one client, we will not have a primary key on this table. However, we will need a field that tells us what payment is related to what client. For this we will use the ClientID field. Therefore, for every Client in the Client table, we can have zero to many payments for that Client in the Payments table.PaymentMethod Table: This table stores all information on payment methods. The client can pay by Visa, Mastercard, Purchase Order (PO), Check, or Cash. We will document this on every payment.Step 4 - Create a Relationship Diagram to identify the Tables, Relationships, Primary Keys, and the Foreign Keys4203065981075Foreign Key:ClientIDOn Payments table00Foreign Key:ClientIDOn Payments table2080260981075Table Name:ClientPrimary Key:ClientID00Table Name:ClientPrimary Key:ClientIDThe tables have been created, but how can the database know what record in the Client table relates to records in the Charges and Payments table. In other words, how can I make sure that I can pull the correct charges and payments information for each client? This is done by creating relationships between the tables; hence we will create a relational database. We will depict this on paper by creating a Relationship Diagram.-133350205105Foreign Key:ClientIDOn Charges table00Foreign Key:ClientIDOn Charges table405638099060003299460990600013233409906000132334099060003427095315595Table Name:PaymentsPrimary Key:None00Table Name:PaymentsPrimary Key:None733425315595Table Name:ChargesPrimary Key:None00Table Name:ChargesPrimary Key:None4716780250190Foreign Key:PaymentTypeOn Payments table00Foreign Key:PaymentTypeOn Payments table405638029210003427095245745Table Name:PaymentMethodPrimary Key:None00Table Name:PaymentMethodPrimary Key:NoneIt is obvious that the field which makes each record in the Client table unique is the ClientID field, but what about the Charges and Payments tables? These two tables can have many charges and payments from one client; therefore they will not have a primary key. But, how do we relate the records in each table. We use the ClientID field on the Charges table to relate each charge to one client on the client table, and we use the ClientID field on the Payments table to relate each payment to one client on the Client table. Therefore, there is a one-to-many relationship between the Client table and the Charges table, and there is a one-to-many relationship between the Client table and the Payments table. The ClientID field on the Charges table is called a Foreign Key, because it will relate all the records on the Client table which uses ClientID as its Primary Key. What would be the Foreign Key on the Payments table that will relate the Payments and Client table? It will be the ClientID on the Payments plete the following steps:Step 5 - Create the database structure in MS Access 2010.Open Ms Office Access 2010269557516522700034404301505585Click on the Blank Database Icon00Click on the Blank Database IconClick on the Blank Database selection 200025115570-168021077470If this box displays, click the OK button00If this box displays, click the OK button-22815552095500-57150040640-156146567310If this box displays, click the OK button00If this box displays, click the OK button-2692406858000Click on the Folder to browse.188785515113000 15690852673350Click on the Browse Folder00Click on the Browse Folder45059602854960003714750285496000Save the file to the appropriate location on your computer. Name the file Research Papers.2119630594360002864485447831Save the file to the correct location00Save the file to the correct location 01816100030753052992120Name the file Research Papers00Name the file Research Papers233045031388530034728153768090006655283625599Click the OK button00Click the OK buttonClick on the Create button.277882640776980017329153731442Click on the Create button00Click on the Create button2369185267335006. Click on the arrow under the view icon and then click on Design View.281305880110001026160733680Click on arrow under the View Icon00Click on arrow under the View Icon89916014719300016440151325245Click on the Design View icon00Click on the Design View icon7.Name the table Client, and click the OK button.1704442534670Type in Client00Type in Client26841451048385Click the OK Button00Click the OK Button193929011950700095631075501500Change the first field name (ID) to Client ID.27279609817101. Change the field name to ClientID001. Change the field name to ClientID1975105120906500NOTEAs stated previously, a Primary Key is the one field that will make each record unique in the database table. In this table, ClientID is the field that will make each client’s information unique. Notice below that the field ClientID has a key next to it. This indicates that you have selected that field as the primary key. When you create your first table in Access a field called ID is automatically created and a primary key is assigned to that field. We have changed the field name to ClientID. Sometimes you will not need a primary key on a table. You can remove the key by clicking on the field name, and then clicking on the Key icon. You can also assign a primary key to a field by clicking on the field and then clicking on the Key icon.Change the Data Type to Text. (Note the terms data type and field type can be used interchangeably)2492375268605Click the arrow00Click the arrow322389561404500330009512573000040449501062990Click on the Text Data Type00Click on the Text Data Type13525502879090Make sure the ClientID field is selected00Make sure the ClientID field is selectedChange the Field Size to 5. (You must have the ClientID field selected to show the properties for the ClientID field.(Note: This will limit the maximum number of characters in the field to 5)(Note: Field size is one type of property. Notice all the other properties that can be set)-285750109855-435483096202500-4476750305752500-373189529108402. Change the field size to 5002. Change the field size to 5Create the remainder of the Client table as shown on Figure 1.5 and then close the Client table.Table Name:ClientField TypeProperties (Field Size)Fields:ClientIDText5Primary KeyFirstNameText25LastNameText50AddressText60CityText25StateText2ZipCodeText5HomePhoneText10Figure 1.527724101094105003048000109410500279146021272500038576258045452. Close the table002. Close the table5288280100838000311658015735300038614351426845Create the fields00Create the fields12704359910Figure 1.200Figure 1.215936594049143. Click the Yes button to save the table.003. Click the Yes button to save the table.8426455873750011.Click on the State field in the upper pane to select the field. Change the default value in the field properties to OH.277177530994352. Change the default value in the Field Properties to, OH002. Change the default value in the Field Properties to, OH202311035553650028428951597025Click on the State Field to select it.00Click on the State Field to select it.2096135172021500925830112204500 We have added a default value of OH to the state field properties. This will cause the state field to initially store the value OH in every record. However; this value is not set in stone. You can change the value in the field at any time. The default value property is good to use if a certain field will contain the same information in it most of the time. For example, we do most of our business in Ohio, therefore; we have set the default value to OH.Click on the HomePhone field in the upper pane to select the field. Click on the Input Mask selection in the Field Properties, and then click on the ellipsis ( … ) next to the Input Mask selection.304800040366953. Click on the ellipsis ( … )003. Click on the ellipsis ( … )419227034086800080010023907752. Click on the Input Mask Selection002. Click on the Input Mask Selection194945028994100026371551736090Click on the HomePhone field to select it00Click on the HomePhone field to select it1892300192341500931545110490000 14941551091565002239010944880Click on the Yes Button00Click on the Yes ButtonClick on the Phone Number selection, and then click on Finish.317182525533352. Click on the Finish Button002. Click on the Finish Button4109085302958500715010731520Select the Phone Number Input Mask00Select the Phone Number Input Mask2204085107696000We have added an input mask of (999) 999-9999 to the HomePhone field property. This will save us the time and hassle of having to add the parentheses and hyphen to the home phone field when we type it in. Basically, the field is preformatted for us. All we have to do is input the actual phone number.Close the Client table.9372601106170002350135760730Close the Client Table00Close the Client Table51574709429750022669508597902. Click on the Yes Button002. Click on the Yes Button1522730109029500Click on the Create tab, and then click on the Table Design icon to create the Charges table.1871980358775Click on theTable Design icon00Click on theTable Design icon89725522098000344170056515Click on the Create tab00Click on the Create tab120523056578500Create the structure for the Charges table using the following information.Table Name:ChargesField TypeField Size (Properties)Fields:ClientIDText5 AmountCurrencyNoneChargeDateDateNone19050326390-2947035187325Create the fields for the Charges table00Create the fields for the Charges table-5000626743585MAKE SURE YOUR TABLE LOOKS EXACTLY LIKE THIS ONE.00MAKE SURE YOUR TABLE LOOKS EXACTLY LIKE THIS ONE.Click on the ChargeDate field in the upper pane to select the field. Click on the Input Mask selection in the Field Properties, and then click on the ellipsis ( … ) next to the Input Mask selection. Click the yes button, when asked to Save now.9429753507740Click in the Input Mask Text Box00Click in the Input Mask Text Box2146935298767500210375512274550030092651029335Click on the ChargeDate field00Click on the ChargeDate field34290003507740Click on the ellipsis (…)00Click on the ellipsis (…)4251960298767500296545024136350038709602179955Save the changes00Save the changesName the table, Charges and click the OK button. Click the No button when asked, Do you want to create a primary key now? Click on the short date input mask option, and click on finish.2946400985520Press the Ok button00Press the Ok button19564351147445001739265414020Type in Charges as the table name 00Type in Charges as the table name 74930067437000273113512484100032067502524125Click the Finish button00Click the Finish button-322580944880Click the Short Date selection00Click the Short Date selection187325023495Click the No button00Click the No button6858001350645004102100292989000Close the table and save the changes.4718050794385003362325591185Close the table00Close the table13201651268730Click the Yes button00Click the Yes button2328545167449500Follow the same steps to create the Payments table and the PaymentMethod table. Be sure to create an input mask of short date for the PaymentDate field.Table Name:PaymentsField TypeField Size (Properties)Fields:ClientIDText5No Primary KeyAmountCurrencyNonePaymentDateDateNonePaymentTypeText2Table Name:PaymentMethodField TypeField Size (Properties)Fields:MethodIDText2Primary Key4692652366645You should now have 4 Tables. Client—Primary Key ClientIDCharges—No Primary KeyPaymentMethod—Primary Key MethodIDPayments—No Primary Key(Note: These tables do not have to be in this order)00You should now have 4 Tables. Client—Primary Key ClientIDCharges—No Primary KeyPaymentMethod—Primary Key MethodIDPayments—No Primary Key(Note: These tables do not have to be in this order)MethodTypeText20-321310225425-545528522415500-517969522415500-5455285144399000-517461549276000Step 4 - Enforce Referential Data IntegrityNow that the relationships between the tables have been established, we need to closely examine the relationships between each of these tables. First, let’s take a look at the relationship between the Client table and the Charges table. Each record in the Client table has a ClientID field that makes each record unique. Thus we know that J7500 is the record for Nancy Davolio only. What if we had a record in the Charges table with a ClientID of J9900 but, there was no J9900 record in the Client table. How would we know what Client to charge, or where to send the bill? We won’t, and therefore records in the Charges table should always have a matching record in the Client table. The Client table is known as the parent in this relationship, and the Charges table is known as the child. When designing databases we never want to have orphans in our tables. We can eliminate orphans by enforcing referential data integrity. Therefore, if you try to add record J9900 to the Charges table, which is the child table in this relationship, and there is no record J9900 in the Client table, which is the parent table in the relationship, you will receive an error message.In our relationship diagram we have two relationships.The Client table is related to the Charges table.Client Table is the parent in this relationship.Charges Table is the child in this relationship.There is a one-to-many relationship between the Client Table and the Charges tableFor every one record on the Client table there can be zero to many related records on the Charges tableThe Client table is related to the Payments table.Client Table is the parent in this relationship.Payments Table is the child in this relationship.For every one record on the Client table there can be zero to many related records on the Payments tableThe PaymentMethod table is related to the Payments table.PaymentMethod Table is the parent in this relationship.Payments Table is the child in this relationship.For every one record on the PaymentMethod table there can be zero to many related records on the Payments tableStep 6 - Create the Table Relationships, and Enforce Referential Integrity using MS Access 2010.Click on the Database Tools tab, and then click on the Relationships Icon.14097002249170MAKE SURE ALL TABLES ARE CLOSED00MAKE SURE ALL TABLES ARE CLOSED2000250316230003125470149225Click on the Database Tools tab00Click on the Database Tools tab5111751395730Click on the Relationships icon00Click on the Relationships icon169100568072000Click on the Charges selection and click on the add button, then add the remainder of the tables as shown, and finally click on the Close button.41446452887980Click the Add button00Click the Add button333438530429200036614101576705Click on the Charges table00Click on the Charges table24872951767205002696210281305Click on the Show Table icon00Click on the Show Table icon152336548831500 Arrange the tables on the screen as shown.476253080385MAKE SURE YOUR TABLES LOOK EXACTLY LIKE THE ONES SHOWN.00MAKE SURE YOUR TABLES LOOK EXACTLY LIKE THE ONES SHOWN.Click on the left mouse button on the ClientID field in the Client table. While holding down the left mouse button drag the field over to the ClientID in the Charges table. You will have a relationship dialog box pop up as shown below.1830070137922000306260513811250042386251002030Left click on the ClientID field in the Client table00Left click on the ClientID field in the Client table282194088201500449580251460While holding the left mouse button down, drag the ClIientID field from the Client table over to the ClientID field on the Charges table Charges table00While holding the left mouse button down, drag the ClIientID field from the Client table over to the ClientID field on the Charges table Charges tableClick on the Enforce Referential Integrity, Cascade Update Related fields, and Cascade Delete Related Records check boxes and then click the Create button.**NOTE** We will discuss later the importance of the Cascade Update Related Fieldsand Cascade Delete Related Fields Check box.14287503377565001733550319659000173355034893250017335503195320001432560338264500173355033820100046120052465705Click the Create button00Click the Create button39535102602865003854453995420Click on the Enforce Referential Integrity checkboxClick on the Cascade Update Related fields checkboxClick on the Cascade Delete Related Records checkbox00Click on the Enforce Referential Integrity checkboxClick on the Cascade Update Related fields checkboxClick on the Cascade Delete Related Records checkboxClick the left mouse button on the ClientID field in the Client table and drag it over the ClientID in the Payments table. You will have a relationship dialog box pop up as shown below. Click on the Enforce Referential Integrity, Cascade Update Related fields, and Cascade Delete Related Records check boxes and then click the Create button. **NOTE** We will discuss later the importance of the Cascade Update Related Fieldsand Cascade Delete Related Fields Check box.2260602633345Click the Create button00Click the Create button20288252826385001724025361061000202882534677350020358103467735001729105360807000203009536074350020300953714115001143001134110Left click on the ClientID field in the Client table00Left click on the ClientID field in the Client table68072042297353. Click on the Enforce Referential Integrity checkboxClick on the Cascade Update Related fields checkboxClick on the Cascade Delete Related Records checkbox003. Click on the Enforce Referential Integrity checkboxClick on the Cascade Update Related fields checkboxClick on the Cascade Delete Related Records checkbox179324014592300034124901025525001040130394970While holding the left mouse button down, drag the ClIientID field from the Client table over to the ClientID field on the Payments table Charges table00While holding the left mouse button down, drag the ClIientID field from the Client table over to the ClientID field on the Payments table Charges table3658870145923000Click the left mouse button on the MethodID field in the PaymentMethod table and drag it over to the PaymentType field in the Payments table. You will have a relationship dialog box pop up as shown below. Click on the Enforce Referential Integrity, Cascade Update Related fields, and Cascade Delete Related Records check boxes and then click the Create button.183959533693100018357853233420001830070352425000151447534067750018288003225800001529080340804500774702441575Click the Create button00Click the Create button18815052604770005447665131127500435800524491950041529003971925Left click on the MethodID field in the PaymentMethod table00Left click on the MethodID field in the PaymentMethod table51339753336925001573530568325While holding the left mouse button down, drag the MethodID field from the PaymentMethod table over to the PaymentType field on the Payments table00While holding the left mouse button down, drag the MethodID field from the PaymentMethod table over to the PaymentType field on the Payments table-17589540246303. Click on the Enforce Referential Integrity checkboxClick on the Cascade Update Related fields checkboxClick on the Cascade Delete Related Records checkbox003. Click on the Enforce Referential Integrity checkboxClick on the Cascade Update Related fields checkboxClick on the Cascade Delete Related Records checkbox5020310260794500Close and save the relationships.35280602355850Click the Yes button00Click the Yes button27184352510790003528060336550Click the Close icon00Click the Close icon235521552768500Step 7 – Populate the database.Now that you have created the structure of the database, it is time to input your client information.Double click on the Client Table to open it up in Datasheet View and type in the information for the following records.152402632710NOTE * indicates a blank record where you can input information.This row will always show up. It is not considered a record. It is just a holding place to add new records.OH is the default for the state field.It will always show up at first, but you can change the OH to any other state you wish.When you type in the information, don’t worry if it doesn’t display completely in the cell. It is there, you just can’t see it.00NOTE * indicates a blank record where you can input information.This row will always show up. It is not considered a record. It is just a holding place to add new records.OH is the default for the state field.It will always show up at first, but you can change the OH to any other state you wish.When you type in the information, don’t worry if it doesn’t display completely in the cell. It is there, you just can’t see it.2461260155130500106553015513050039382701125220001059180112522000-4724401878965Double click on the Client Table00Double click on the Client Table365760118618000Type in the information in shown in figure 1.6.Client TableClientIDFirstNameLastNameAddressCityStateZipcodeHomePhoneJ7500NancyDavolio507 - 20th Ave. E. Apt. 2ASeattleWA98122(614)-292-3900J7505JohnSmith123 Main StreetColumbusOH43081(740)-365-9192J7510MarkJohnson420 Broad StreetColumbusOH42001(740)-365-7700J7515JenGriffin500 High StreetColumbusOH43081(614)-292-4566J7520JenniferKing400 Walnut StreetCincinnatiOH41098(614)-888-9930J7525KarenDay250 Fifth StreetCincinnatiOH41077(614)-293-5001J7530JeffBair125 Plum StreetCincinnatiOH41077(740)-365-4478J7535NancyBeard10 South WackerChicagoIL60639(740)-365-4879J7540BradWallace120 South LaSalleChicagoIL60639(740)-365-4421J7545JoeDavis15 West WashingtonChicagoIL60221(614)-443-0988J7550TimSmith908 W. Capital WayTacomaWA98401(614)-293-9655J7555BrandonCoake722 Moss Bay Blvd.KirklandWA98033(614)-555-9333J7560MargaretPeacock4110 Old Redmond Rd.RedmondWA98052(614)-293-8857J7565MatthewDunn14 Garrett HillSeattleWA98105(740)-365-4433J7570GeorgeNagy722 DaVinci Blvd.KirklandWA98034(740)-365-2241J7575DeborahPeterson305 - 14th Ave. S. Suite 3BSeattleWA98128(740)-365-9031Figure 1.6Input the information in the PaymentMethod table as shown in figure 1.6. DO THIS BEFORE ADDING INFORMATION TO THE Charges and Payments Table.Input the information in the Charges table, and the Payments table as shown in figure 1.6.273367529019500After you have finished inputting all of the information, completely close the Research Papers database.Charges TableClientIDAmountChargeDateJ7500$100.005 /5 /2008J7500$150.003 /22/2008J7505$150.003 /22/2008J7510$300.008 /8 /2008J7515$50.004 /15/2008J7520$50.004 /21/2008J7520$50.003 /27/2008J7520$100.007 /6 /2008J7525$100.009 /13/2008J7530$200.0010/13/2008J7535$100.0012/1 /2008J7540$100.0011/28/2008J7540$100.005 /12/2008J7545$100.006 /17/2008J7550$200.007 /24/2008J7555$50.008 /19/2008J7560$50.004 /24/2008J7565$50.004 /15/2008J7565$100.004 /22/2008J7565$100.005 /24/2008J7565$100.005 /22/2008J7570$50.004 /21/2008J7575$50.003 /27/2008Figure 1.6PaymentMethod TableMethodIDMethodType1Visa2Mastercard3PO4Check5Cash4699053340Type in the information for the PaymentMethod table First00Type in the information for the PaymentMethod table First-76200220980Payments Table00Payments TableClientIDPaymentTypeAmountPaymentDateJ75001$100.001 /16/2008J75003$200.003 /3 /2008J75005$50.002 /12/2008J75052$250.001 /16/2008J75102$250.002 /22/2008J75102$100.002 /28/2008J75101$100.001 /2 /2008J75151$75.001 /21/2008J75304$50.003 /8 /2008J75355$200.003 /8 /2008J75455$125.002 /16/2008Cascade Update Related Records/Cascade Delete Related RecordsIf you recall, when we enforced referential data integrity in MS Access 2010, we checked two boxes to Cascade Update Related Records and Cascade Delete Related Records. These options ensure that our database has no orphans. If you remember correctly, when we enforce referential data integrity the database will not allow any orphans to be input in the tables. But, orphans can still exist in your database even if you click the enforce referential data integrity checkbox. For example, if you look in the database above you will notice Nancy Davolio, ClientID J7500, has 2 records in the Charges table, and 3 records in the Payments table. What would happen if I deleted the record for Nancy Davolio in the Client table? Now there are 2 orphans in the Charges table, and 2 orphans in the Payments table. Likewise, if I change the ClientID for Nancy Davolio to J7900, I also produce orphans in both the Charges table and the Payments table. However, if I click the checkboxes to Cascade Update Related Records, and Cascade Delete Related Records, if I delete the parent, Nancy Davolio, ClientID J7500, I will delete all the J7500 children in all of the related tables. But, if I delete the children, J7500, in the Charges or Payments table, I will not delete the parent J7500 in the Client table. The Update Related Records checkbox works the same way as the Cascade Delete Related Records, only it updates the children ClientID when the parent ClientID is updated. Remember, it only goes one way. If I delete the parent, I delete the children, but if I delete the children, I do not delete the parent. If I update the ClientID for the parent, the ClientID for all the related children will be updated, but if I update the clientID for the children table, the ClientID for the parent table will not be updated. Possible Scenarios when the following boxes are checked when creating relationships:Enforce Referential Integrity, Cascade Update Related Fields, Cascade Delete Related Records NOTE: DO NOT MAKE THESE CHANGES IN THE DATABASETHESE ARE EXAMPLES ONLYActionAssociated ActionDelete record J7500 in the Client tableAll records in the Charges table with ClientID J7500 will be deleted.All records in the Payments table with ClientID J7500 will be deleted.Delete any J7500 record in the Charges TableOnly that one J7500 record will be deleted in the Charges TableDelete any J7500 record in the Payments TableOnly that one J7500 record will be deleted in the Payments TableChange ClientID J7500 to J9900 in the Client tableAll records in the Charges table withClientID J7500 will be changed to ClientID J9900All records in the Payments table withClientID J7500 will be changed to ClientID J9900Delete record with MethodID of 1 in the PaymentMethod table.All records in the Payments table with PaymentType of 1 will be deleted.Change record with MethodID 1 to MethodID 6 in the PaymentMethod table.All records in the Payments table with PaymentType of 1 will be changed to PaymentType of 6.Change record J7500 from PaymentType of 1 to PaymentType of 6 in the Payments table.If a record with MethodID 6 exists in the PaymentMethod table the record in the Payments table with will change accordingly.If a record with MethodID 6 does not exist in the PaymentMethod table, you will receive the message, “Referential Data Integrity has been Violated”, and you will not be able to make the change to the record. Part 2 – Snacks Database problemYou will be modifying the Snacks.accdb database which currently contains a Suppliers table with the primary key SupplierID. As part of this exercise you will be creating a new table named Products and setting its primary key, ProductID. The two tables will be related via the foreign key field SupplierID. Your tasks will also include inserting and editing records to this new table, creating reports and forms from the wizards based on these tables. Complete the following steps:Copy the Snacks.accdb database file from Carmen into your personal directory. Be sure to save it – do not open it directly from Carmen.Once the Snacks.accdb is saved, open it in Access.Create a new table using the Design View named Products based on the following structure:Insert the following records into the table:Open up the Suppliers table (which has already been created for you in this database) and modify it as follows:The contact person for Crackle Snacks has changed. Navigate to record 5 and change the contact name to Mary Stuart.One of your suppliers has changed her phone number. Navigate to record 10 and change the phone number for Samantha Rider to be: 888-3437.Close the database and Access application.The Snacks.accdb file should automatically be saved for later submission.Checklist (SUBMIT THESE FILES BEFORE YOUR IN-LAB SESSION)Research Papers.accdbSnacks.accdb ................
................

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

Google Online Preview   Download