SEE problem 4.25 which is attached in pdf format.

I need detailed and correct answer

148 PART TWO / Databases Purchasing agent Order goods Merchandise inventory Vendor Receive goods Receiving clerk Cash Pay for goods Cashier or A/P clerk FIGURE 4-21 An E-R diagram for the purchasing system of Souder, Oles, and Franek LLP. 4-24. Souder, Oles, and Franek LLP (Data Modeling with REA) Souder, Oles, and Franek is an international consulting firm headquartered in Chicago, Illinois. The Entity-Relationship diagram in Figure 4-21 shows a simplified version of the company's process for purchasing and paying for equipment and supplies. Requirements 1. Insert appropriate pairs of cardinalities for the relationships in the Entity-Relationship model developed with the REA data modeling approach. 2. Describe the database table attributes for this model. You will need a table for each entity, as well as one or more relationship tables. First identify the table name and then indicate the primary key by underlining it. Show any foreign keys by framing them in brackets (e.g., [Vendor#]). Include at least three fields in each table. Below is an example for the Vendor table and the Order Goods table: Vendor#, Name, Street Address 1, Street Address 2, City, State, Zip Code, Phone, Email, Fax, Contact, Comments. Order#, Date, [Vendor#], [Employee#], Shipping Instructions, Comments. 4-25. BSN Bicycles (Creating a Database from Scratch with Microsoft Access) Bill Barnes and Tom Freeman opened their BSN bicycle shop in 2005. Not counting Jakea friend who helps out occasionally at the storeBill and Tom are the only employees. The shop occupies a small commercial space that was once a restaurant. The former kitchen now stores spare parts and provides space for bicycles repairs, while the former dining CHAPTER 4 / Data Modeling 149 area in the front is now the retail sales area. The ''corporate office'' is just a desk and file cabinet in the back corner of the retail area. Bill and Tom are more friends and bicycling enthusiasts than businessmen. They've pretty much sunk their life savings into the shop and are anxious that it succeed. In the first year of operations, they worked hard to convert the space into its present condition, which includes an old-timey sign above the door with their name ''BSN Bicycles.'' With all the other work that had to be done the first year, marketing efforts have been limited to chatting with friends, distributing flyers at bicycle races and similar sporting events, and placing a few ads in the local newspaper. Similarly, the owners haven't paid much attention to accounting tasks. Who has time with all the other things that had to get done? But at least two things are now clear to the owners: (1) some of their loyal customers prefer to buy items on credit, and (2) all of their suppliers want to be paid on time. Right now, BSN's ''customer credit system'' is a box of 3x5 cards. Each hand-written card contains customer information on the front and invoice information on the back (Figure 4-22). When a customer pays an invoice, one of the owners simply crosses off the invoice information on the card. The ''supplier accounts system'' is similar, except that the vendor box of 3x5 cards is green whereas the customer box is grey. Jake is a part-time student at the local community college. He recently completed a course on microcomputer applications that included a segment on Microsoft Access. He doesn't know very much about database theory, but thinks that converting the shop's current ''accounting systems'' to a DBMS might be a good idea. He thinks, for example, (a) The front of a 3x5 BSN customer card. (b) The back of a 3x5 BSN customer card. FIGURE 4-22 A customer record for the BSN company. 150 PART TWO / Databases that BSN needs a customer table and a vendor (supplier) table. He also thinks that BSN will need an inventory table to keep track of inventory, but that even more tables might be required. Can you help them? Requirements 1. Identify the resources, events, and agents for BSN's accounting systems. Draw one or more E-R diagrams that illustrate the relationships between these items. 2. Identify the tables that you would need to create a working database for the company's receivables, payables, and inventory. 3. Using Access or similar software as required by your instructor, create at least three records for each of the tables you identified in part 2. Hints: (1) Use the information on the front of the 3x5 card in Figure 4-22 for the customer record structure. (2) The data fields for the Vendors table should include the vendor ID, vendor name and address information, phone number, fax number, and contact person. (3) The data fields for the Inventory table should include item number, item description, units (e.g., dozen, each, etc.), unit cost, unit retail sales price, and quantity on hand. 4. Create relationships for your various tables. 5. Document your work by printing hard copies of each table in data sheet view and each relationship. REFERENCES AND RECOMMENDED READINGS Ames, Anton & Ben Scaff. ''Preparing for a Data Disaster'' Benefits & Compensation Digest Vol 45, No. 11 (November 2008), pp. 40-45. Barbellotto, Gianluca. ''How Good Are Your Standards?'' Strategic Finance Vol. 89, No. 12 (June 2008), pp. 67-68. Britt, Phil. ''Records Management: Beware, Prepare'' KM World Vol. 17, No. 10 (November 2008), pp. 12-26. Carlson, Caron. ''GAO Reports Rampant Federal Data Mining'' eWeek Vol. 21, No. 23 (June 7, 2004), p. 31. Codd, E. F., The Relational Model for Database Management Version 2 (Reading, MA: Addison-Wesley, 1990). Curtin, Matthew. ''Database Security: Solve the Right Problem Now for Fewer Headaches Tomorrow'' Accounting Today Vol. 21, No. 4 (February 26, 2007), pp. 18-26. Egana, Nilva & Fiona, Bruinsma. ''Data DictionariesWhat do They Have to do with Public Health?'' Australian & New Zealand Journal of Public Health Vol. 32, No. 3 (June 2008), pp. 286- 287. Farrell, Mike, ''Linking Databases, Easing Headaches'' Multichannel News Vol. 26, No. 30 (July 25, 2005), pp. 58-59. Garbellotto, Glanluca. ''How Good are Your (XBRL) Standards?'' Strategic Finance Vol. 89, No. 12 (June 2008), pp. 67-68. Gerard, Gregory J. ''The REA Pattern, Knowledge Structures, and Conceptual Modeling Performance'' Journal of Information Systems Vol. 19, No. 2 (Fall 2005), pp. 57-77. Gruman, Galen. ''Whipping Data Into Shape'' InfoWorld Vol. 28, No. 6 (February 6, 2006), pp. 26-32. 148 PART TWO / Databases Purchasing agent Order goods Merchandise inventory Vendor Receive goods Receiving clerk Cash Pay for goods Cashier or A/P clerk FIGURE 4-21 An E-R diagram for the purchasing system of Souder, Oles, and Franek LLP. 4-24. Souder, Oles, and Franek LLP (Data Modeling with REA) Souder, Oles, and Franek is an international consulting firm headquartered in Chicago, Illinois. The Entity-Relationship diagram in Figure 4-21 shows a simplified version of the company's process for purchasing and paying for equipment and supplies. Requirements 1. Insert appropriate pairs of cardinalities for the relationships in the Entity-Relationship model developed with the REA data modeling approach. 2. Describe the database table attributes for this model. You will need a table for each entity, as well as one or more relationship tables. First identify the table name and then indicate the primary key by underlining it. Show any foreign keys by framing them in brackets (e.g., [Vendor#]). Include at least three fields in each table. Below is an example for the Vendor table and the Order Goods table: Vendor#, Name, Street Address 1, Street Address 2, City, State, Zip Code, Phone, Email, Fax, Contact, Comments. Order#, Date, [Vendor#], [Employee#], Shipping Instructions, Comments. 4-25. BSN Bicycles (Creating a Database from Scratch with Microsoft Access) Bill Barnes and Tom Freeman opened their BSN bicycle shop in 2005. Not counting Jakea friend who helps out occasionally at the storeBill and Tom are the only employees. The shop occupies a small commercial space that was once a restaurant. The former kitchen now stores spare parts and provides space for bicycles repairs, while the former dining CHAPTER 4 / Data Modeling 149 area in the front is now the retail sales area. The ''corporate office'' is just a desk and file cabinet in the back corner of the retail area. Bill and Tom are more friends and bicycling enthusiasts than businessmen. They've pretty much sunk their life savings into the shop and are anxious that it succeed. In the first year of operations, they worked hard to convert the space into its present condition, which includes an old-timey sign above the door with their name ''BSN Bicycles.'' With all the other work that had to be done the first year, marketing efforts have been limited to chatting with friends, distributing flyers at bicycle races and similar sporting events, and placing a few ads in the local newspaper. Similarly, the owners haven't paid much attention to accounting tasks. Who has time with all the other things that had to get done? But at least two things are now clear to the owners: (1) some of their loyal customers prefer to buy items on credit, and (2) all of their suppliers want to be paid on time. Right now, BSN's ''customer credit system'' is a box of 3x5 cards. Each hand-written card contains customer information on the front and invoice information on the back (Figure 4-22). When a customer pays an invoice, one of the owners simply crosses off the invoice information on the card. The ''supplier accounts system'' is similar, except that the vendor box of 3x5 cards is green whereas the customer box is grey. Jake is a part-time student at the local community college. He recently completed a course on microcomputer applications that included a segment on Microsoft Access. He doesn't know very much about database theory, but thinks that converting the shop's current ''accounting systems'' to a DBMS might be a good idea. He thinks, for example, (a) The front of a 3x5 BSN customer card. (b) The back of a 3x5 BSN customer card. FIGURE 4-22 A customer record for the BSN company. 150 PART TWO / Databases that BSN needs a customer table and a vendor (supplier) table. He also thinks that BSN will need an inventory table to keep track of inventory, but that even more tables might be required. Can you help them? Requirements 1. Identify the resources, events, and agents for BSN's accounting systems. Draw one or more E-R diagrams that illustrate the relationships between these items. 2. Identify the tables that you would need to create a working database for the company's receivables, payables, and inventory. 3. Using Access or similar software as required by your instructor, create at least three records for each of the tables you identified in part 2. Hints: (1) Use the information on the front of the 3x5 card in Figure 4-22 for the customer record structure. (2) The data fields for the Vendors table should include the vendor ID, vendor name and address information, phone number, fax number, and contact person. (3) The data fields for the Inventory table should include item number, item description, units (e.g., dozen, each, etc.), unit cost, unit retail sales price, and quantity on hand. 4. Create relationships for your various tables. 5. Document your work by printing hard copies of each table in data sheet view and each relationship. REFERENCES AND RECOMMENDED READINGS Ames, Anton & Ben Scaff. ''Preparing for a Data Disaster'' Benefits & Compensation Digest Vol 45, No. 11 (November 2008), pp. 40-45. Barbellotto, Gianluca. ''How Good Are Your Standards?'' Strategic Finance Vol. 89, No. 12 (June 2008), pp. 67-68. Britt, Phil. ''Records Management: Beware, Prepare'' KM World Vol. 17, No. 10 (November 2008), pp. 12-26. Carlson, Caron. ''GAO Reports Rampant Federal Data Mining'' eWeek Vol. 21, No. 23 (June 7, 2004), p. 31. Codd, E. F., The Relational Model for Database Management Version 2 (Reading, MA: Addison-Wesley, 1990). Curtin, Matthew. ''Database Security: Solve the Right Problem Now for Fewer Headaches Tomorrow'' Accounting Today Vol. 21, No. 4 (February 26, 2007), pp. 18-26. Egana, Nilva & Fiona, Bruinsma. ''Data DictionariesWhat do They Have to do with Public Health?'' Australian & New Zealand Journal of Public Health Vol. 32, No. 3 (June 2008), pp. 286- 287. Farrell, Mike, ''Linking Databases, Easing Headaches'' Multichannel News Vol. 26, No. 30 (July 25, 2005), pp. 58-59. Garbellotto, Glanluca. ''How Good are Your (XBRL) Standards?'' Strategic Finance Vol. 89, No. 12 (June 2008), pp. 67-68. Gerard, Gregory J. ''The REA Pattern, Knowledge Structures, and Conceptual Modeling Performance'' Journal of Information Systems Vol. 19, No. 2 (Fall 2005), pp. 57-77. Gruman, Galen. ''Whipping Data Into Shape'' InfoWorld Vol. 28, No. 6 (February 6, 2006), pp. 26-32. CHAPTER 5 / Organizing and Manipulating the Data in Databases 183 7. Now again attempt to create a record in the ticket file that contains a nonexistent ticket code in the parking Violations Code File. Were you successful this time? 8. Finally, attempt to delete a record in the Parking Violations Code File. Why can't you do it? 9. If required by your instructor, create an example of the parking-violations-by-type report desired by the Board of Directors using the database you just created. 5-26. BSN Bicycles II (Creating Queries in Access) Business has been growing at BSN Bicycles, and the store owners have been using their Access database to store information about their customers. Now that the store is a little more established, the owners are thinking more about how best to attract more customers to their store. One idea is to see where their current customers live. The owners also want a complete list of their credit customers. Requirements: 1. If you have not already done so, create a database for BSN and the customer's table described in Case 4-25 in Chapter 4. Be sure to create at least 10 customer records for the company, including one with your name. Several of the customers should also live in the state of Virginia (VA) and several customers should have zip code ''12345.'' The Virginia customers and the customers with zip code 12345 do not have to be the same. 2. If you have not already done so, create several invoices for your customers. 3. Create a query that selects all customers living in Virginia. Print your results. 4. Create a query that selects all customers living in zip code 12345. Print your results. 5. Create a query that selects all customers living in Virginia who also have zip code 12345. Print your results. 6. Create a query that selects all credit customers. (Hint: use the word ''Yes'' for the criteria in this query.) Print your results. 5-27. Furry Friends Foundation II (Creating Queries for Databases) Recall from Case 4-21 in Chapter 4 that the Furry Friends Foundation is a nonprofit organization that finds homes for abandoned animals. The foundation has recently computerized some of its operations by storing its accounting data in a relational database. One reason for this was to enable it to more easily answer questions about donations. This portion of the case provides some examples of such questions and gives you practice creating database queries to answer them. Requirements: 1. If you have not already done so, create the tables and relationships described in Case 4-21. 148 PART TWO / Databases Purchasing agent Order goods Merchandise inventory Vendor Receive goods Receiving clerk Cash Pay for goods Cashier or A/P clerk FIGURE 4-21 An E-R diagram for the purchasing system of Souder, Oles, and Franek LLP. 4-24. Souder, Oles, and Franek LLP (Data Modeling with REA) Souder, Oles, and Franek is an international consulting firm headquartered in Chicago, Illinois. The Entity-Relationship diagram in Figure 4-21 shows a simplified version of the company's process for purchasing and paying for equipment and supplies. Requirements 1. Insert appropriate pairs of cardinalities for the relationships in the Entity-Relationship model developed with the REA data modeling approach. 2. Describe the database table attributes for this model. You will need a table for each entity, as well as one or more relationship tables. First identify the table name and then indicate the primary key by underlining it. Show any foreign keys by framing them in brackets (e.g., [Vendor#]). Include at least three fields in each table. Below is an example for the Vendor table and the Order Goods table: Vendor#, Name, Street Address 1, Street Address 2, City, State, Zip Code, Phone, Email, Fax, Contact, Comments. Order#, Date, [Vendor#], [Employee#], Shipping Instructions, Comments. 4-25. BSN Bicycles (Creating a Database from Scratch with Microsoft Access) Bill Barnes and Tom Freeman opened their BSN bicycle shop in 2005. Not counting Jakea friend who helps out occasionally at the storeBill and Tom are the only employees. The shop occupies a small commercial space that was once a restaurant. The former kitchen now stores spare parts and provides space for bicycles repairs, while the former dining CHAPTER 4 / Data Modeling 149 area in the front is now the retail sales area. The ''corporate office'' is just a desk and file cabinet in the back corner of the retail area. Bill and Tom are more friends and bicycling enthusiasts than businessmen. They've pretty much sunk their life savings into the shop and are anxious that it succeed. In the first year of operations, they worked hard to convert the space into its present condition, which includes an old-timey sign above the door with their name ''BSN Bicycles.'' With all the other work that had to be done the first year, marketing efforts have been limited to chatting with friends, distributing flyers at bicycle races and similar sporting events, and placing a few ads in the local newspaper. Similarly, the owners haven't paid much attention to accounting tasks. Who has time with all the other things that had to get done? But at least two things are now clear to the owners: (1) some of their loyal customers prefer to buy items on credit, and (2) all of their suppliers want to be paid on time. Right now, BSN's ''customer credit system'' is a box of 3x5 cards. Each hand-written card contains customer information on the front and invoice information on the back (Figure 4-22). When a customer pays an invoice, one of the owners simply crosses off the invoice information on the card. The ''supplier accounts system'' is similar, except that the vendor box of 3x5 cards is green whereas the customer box is grey. Jake is a part-time student at the local community college. He recently completed a course on microcomputer applications that included a segment on Microsoft Access. He doesn't know very much about database theory, but thinks that converting the shop's current ''accounting systems'' to a DBMS might be a good idea. He thinks, for example, (a) The front of a 3x5 BSN customer card. (b) The back of a 3x5 BSN customer card. FIGURE 4-22 A customer record for the BSN company. 150 PART TWO / Databases that BSN needs a customer table and a vendor (supplier) table. He also thinks that BSN will need an inventory table to keep track of inventory, but that even more tables might be required. Can you help them? Requirements 1. Identify the resources, events, and agents for BSN's accounting systems. Draw one or more E-R diagrams that illustrate the relationships between these items. 2. Identify the tables that you would need to create a working database for the company's receivables, payables, and inventory. 3. Using Access or similar software as required by your instructor, create at least three records for each of the tables you identified in part 2. Hints: (1) Use the information on the front of the 3x5 card in Figure 4-22 for the customer record structure. (2) The data fields for the Vendors table should include the vendor ID, vendor name and address information, phone number, fax number, and contact person. (3) The data fields for the Inventory table should include item number, item description, units (e.g., dozen, each, etc.), unit cost, unit retail sales price, and quantity on hand. 4. Create relationships for your various tables. 5. Document your work by printing hard copies of each table in data sheet view and each relationship. REFERENCES AND RECOMMENDED READINGS Ames, Anton & Ben Scaff. ''Preparing for a Data Disaster'' Benefits & Compensation Digest Vol 45, No. 11 (November 2008), pp. 40-45. Barbellotto, Gianluca. ''How Good Are Your Standards?'' Strategic Finance Vol. 89, No. 12 (June 2008), pp. 67-68. Britt, Phil. ''Records Management: Beware, Prepare'' KM World Vol. 17, No. 10 (November 2008), pp. 12-26. Carlson, Caron. ''GAO Reports Rampant Federal Data Mining'' eWeek Vol. 21, No. 23 (June 7, 2004), p. 31. Codd, E. F., The Relational Model for Database Management Version 2 (Reading, MA: Addison-Wesley, 1990). Curtin, Matthew. ''Database Security: Solve the Right Problem Now for Fewer Headaches Tomorrow'' Accounting Today Vol. 21, No. 4 (February 26, 2007), pp. 18-26. Egana, Nilva & Fiona, Bruinsma. ''Data DictionariesWhat do They Have to do with Public Health?'' Australian & New Zealand Journal of Public Health Vol. 32, No. 3 (June 2008), pp. 286- 287. Farrell, Mike, ''Linking Databases, Easing Headaches'' Multichannel News Vol. 26, No. 30 (July 25, 2005), pp. 58-59. Garbellotto, Glanluca. ''How Good are Your (XBRL) Standards?'' Strategic Finance Vol. 89, No. 12 (June 2008), pp. 67-68. Gerard, Gregory J. ''The REA Pattern, Knowledge Structures, and Conceptual Modeling Performance'' Journal of Information Systems Vol. 19, No. 2 (Fall 2005), pp. 57-77. Gruman, Galen. ''Whipping Data Into Shape'' InfoWorld Vol. 28, No. 6 (February 6, 2006), pp. 26-32. CHAPTER 5 / Organizing and Manipulating the Data in Databases 183 7. Now again attempt to create a record in the ticket file that contains a nonexistent ticket code in the parking Violations Code File. Were you successful this time? 8. Finally, attempt to delete a record in the Parking Violations Code File. Why can't you do it? 9. If required by your instructor, create an example of the parking-violations-by-type report desired by the Board of Directors using the database you just created. 5-26. BSN Bicycles II (Creating Queries in Access) Business has been growing at BSN Bicycles, and the store owners have been using their Access database to store information about their customers. Now that the store is a little more established, the owners are thinking more about how best to attract more customers to their store. One idea is to see where their current customers live. The owners also want a complete list of their credit customers. Requirements: 1. If you have not already done so, create a database for BSN and the customer's table described in Case 4-25 in Chapter 4. Be sure to create at least 10 customer records for the company, including one with your name. Several of the customers should also live in the state of Virginia (VA) and several customers should have zip code ''12345.'' The Virginia customers and the customers with zip code 12345 do not have to be the same. 2. If you have not already done so, create several invoices for your customers. 3. Create a query that selects all customers living in Virginia. Print your results. 4. Create a query that selects all customers living in zip code 12345. Print your results. 5. Create a query that selects all customers living in Virginia who also have zip code 12345. Print your results. 6. Create a query that selects all credit customers. (Hint: use the word ''Yes'' for the criteria in this query.) Print your results. 5-27. Furry Friends Foundation II (Creating Queries for Databases) Recall from Case 4-21 in Chapter 4 that the Furry Friends Foundation is a nonprofit organization that finds homes for abandoned animals. The foundation has recently computerized some of its operations by storing its accounting data in a relational database. One reason for this was to enable it to more easily answer questions about donations. This portion of the case provides some examples of such questions and gives you practice creating database queries to answer them. Requirements: 1. If you have not already done so, create the tables and relationships described in Case 4-21