Relational Databases

CHAPTER 4 RELATIONAL DATABASES SUGGESTED ANSWERS TO DISCUSSION QUESTIONS4. 1 Contrast the logical and the physical attitude of info and discuss why separate views are necessary in infobase applications. Describe which perspective is most phthisisful for distributively of the following employees a broadcastmer, a double-decker, and an internal meeter. How will envisioning logical selective information organizes look you when designing and using entropybase dodges?4. 2 Databases are possible beca uptake of their selective informationbase management system (database management system). As shown in Figure 4. 2, the database management system is a software political program that sits between the actual data stored in the system and the application programs that enforce the data. As shown in Figure 4. 4, this bothows users to separate the way they view the data (called the logical view) from the way the data is actually stored (the physical view). The database management s ystem interprets the users requests and retrieves, manipulates, or stores the data as needed. The two distinct views separate the applications from the physical information, providing change magnitude flexibility in applications, improved data security, and ease of use.In a database system, the passenger car will rarely need to understand or be long-familiar with the physical view of the data. Nor, in most instances, will the internal auditor and the programmer as most everything they do involves the logical view of the data. If accountants understand logical data structures and the logical view of the data, they are better competent to manage, use, and audit a database and its data. 4. 2 The comparative data feign represents data as being stored in tables. Spread tags are a nonher tool that accountants use to employ a tabular mold of data.What are some(a) similarities and differences in the way these tools use tables? How might an accountants familiarity with the tabular rep resentation of spreadsheets facilitate or hinder learning how to use a comparative DBMS? A major difference between spreadsheets and databases is that spreadsheets are knowing primarily to handle numeric data, whereas databases fire handle both schoolbook and forms. Consequently, the interrogative sentence and sorting capabilities of spreadsheets are much more limited than what mass be accomplished with a DBMS that has a good question phrase.Accountants familiarity with spreadsheets might hinder their ability to design and use relational DBMS because many links in spreadsheets are preprogrammed and designed in, whereas a well-designed relational database is designed to facilitate ad-hoc queries. Accountants familiarity with spreadsheets sometimes leads them to use a spreadsheet for a line of work that a database could handle much better. Over the years, the Journal of Accountancy has produce a number of very good articles on how to use databases and when to use databases and when to use spreadsheets. These articles can be found on the Journals website http//www. ournalofaccountancy. com/4. 3 Some people believe database technology may spurn the need for double-entry score. This creates three possibilities (1) the double-entry model will be aband wizd (2) the double-entry model will not be utilise directly, scarce an external-level schema base on the double-entry model will be defined for accountants use or (3) the double-entry model will be retained in database systems. Which alternative do you think is most likely to occur? Why? There is no correct answer to this question because it is asking the student to express his doctrine on what will happen in the future.Therefore, the quality of his answer depends on the justifications renderd. Good answers should address the followingDatabase technology does permit abandonment of double entry, but there will likely be great tube to such a radical change. Thus, students choosing this option need to present reasons why they think such a radical change would succeed.The use of a schema for accountants seems quite plausible. It does eliminate the redundancy of double entry from the database system, still it still provides a framework familiar and useful to accountants and pecuniary analysts.There is a good possibility that double entry will remain, even in databases, due to inertia. Indeed, many modern AIS, such as ERP systems, use databases but also retain the principles of double entry.4. 4 Relational DBMS query languages provide easy access to information about the organizations activities. Does this mean that online, real-time processing should be used for all transactions? Does an organization need real-time financial reports? Why or why not? lt/paragtlt/questiongtlt/general-problemgtlt/problem put downgt On-line real-time processing is not necessary for every business organization transaction.For example, batch processing is adequate for payroll there is little need fo r the data to be current ask out on payday. Real-time financial statements are useful for planning and provide management with better ability to react to changes in the environment. Nevertheless, real-time financial statements may present distorted pictures of reality if accruals cast been ignored or not properly recognized.4. 5 Why is it so important to sire good data? Bad data costs businesses over $600 billion a year. Some people estimate that over 25% of business data is inaccurate or incomplete. In addition, incorrect database data can lead to bad decisions, embarrassment, and angry users. The text illustrated this with the following examples For quite some time, a company sent half its catalogs to incorrect addresses. A manager finally investigated the large volume of retorts and customer complaints and corrected the customer addresses in the database.He saved the company $12 one thousand thousand a year. Valparaiso, Indiana used the county database to develop its tax rat es. After mailing the tax notices, it was discovered that a $121,900 home was take to bed at $400 million. Due to the $3. 1 million place tax revenue shortfall, the city, the school district, and governmental agencies had to make revolting budget cuts. Managing data is not going to get any easier as the quantity of data generated and stored doubles every 18 months.4.6 What is a data dictionary, what does it contain, and how is it used? contains information about the structure of the database. Table 4-1 shows that there is a interpret in the dictionary describing each data element. The DBMS maintains the data dictionary, whose inputs include new or deleted data elements and changes in data element accounts, descriptions, or uses.Outputs include reports for programmers, designers, and users. These reports are used for system documentation, database design and employation, and as part of the audit trail.4. 7 Compare and contrast the file away-oriented approach and the database approach. pardon the main advantages of database systems. Information about the attributes of a customer, such as name and address, are stored in fields. Fields contain data about one entity (e. g. , one customer). Multiple fields form a record. A set of related records, such as all customer records, forms a file (e. g. , the customer file).A set of interrelated, centrally coordinated files forms a database. illustrates the differences between file-oriented and database systems. In the database approach, data is an organizational resource that is used by and managed for the entire organization, not just the originating department. A database management system (DBMS) is the interface between the database and the variant application programs.The database, the DBMS, and the application programs that access the database through the DBMS are referred to as the Database systems were developed to address the proliferation of master files. This proliferation created problems such as the s ame data stored in two or more master files. This made it tight to integrate and update data and to obtain an organization-wide view of data.LimitAccount Balance 4. 2Most DBMS packages contain data definition, data manipulation, and data query languages. For each of the following, intimate which language would be used and why. 1. A database administrator defines the logical structure of the database The DDL this is the language used to define the database. b. The controller requests a cost accounting report containing a list of all employees being paid for more than 10 hours overtime in a given week. The DQL this is an example of a query. c. A programmer develops a program to update the fixed-assets records stored in the database.The DML this is the language used to actually process transaction data and update the database. d. The forgiving resources manager requests a report noting all employees who are retiring within five years. The DQL another example of a task that invo lves querying the database. e. The inventory series number field is extended in the inventory records to allow for wisdom of additional inventory items with serial numbers containing more than 10 digits. The DDL and the DML the precedent to alter the structure, the latter to make the change. f. A user develops a program to print out all procures made during the past two weeks.The DQL this tilt can be produced by a query. g. An additional field is added to the fixed-asset records to record the estimated salvage value of each asset. The DDL and the DML the former to add the field, the latter to precede data in it. 4. 3Ashton wants to store the following data about SampSs purchases of inventory item numberdate of purchase marketer numbervendor address vendor namepurchase price quantity purchasedemployee number employee namepurchase commit number descriptionquantity on hand extended centertotal amount of purchase a. origination a set of relational tables to store this data. Do all of the data items need to be stored in a table? If not, which ones do not need to be stored and why do they not need to be stored? b. Identify the ancient key for each table. c. Identify the foreign keys needed in the tables to implement referential integrity. Table Name Primary Key Foreign Keys early(a) Attributes Inventory full point Number DescriptionQuantity on Hand Purchases Purchase order number vender numberPurchasing Agent (employee number) Date of purchaseTotal amount of purchase Purchases-Inventory Item numberPurchase order number Quantity purchasedUnit cost (actual) elongated amount Vendor Vendor number Vendor nameVendor address Employees Employee number Employee name Extended amount and Total amount of purchase do not have to be stored in the database as they can be calculated from other values. Extended amount is Quantity purchased x Unit cost. Total amount of purchase is the sum of all the extended amounts for all items on a particular purchase order, d. Implement your tables using any relational database product to which you have access.Test your specification by entering sample data in each table. f. Create a few queries to retrieve or analyze the data you stored. There is no solution to parts d through f as students will select different software packages and come up with different queries. 4. 4Retrieve the SampS In-Chapter Database (in Microsoft Access format) from the texts Web site (or create the tables in a relational DBMS product).NOTE In order to get a list of lenders without duplicates the property sheet of the query needs to be modified by setting the value of the uncommon Values property to Yes. This can be seen in the screenshot below. The property sheet is found under the Design tab of the ribbon. Setting Unique Values to Yes is the equivalent of entering the DISTINCT keyword in SQL select statements. call into question Result f. Which borrower quest the largest mortgage? Query Notice that in the Design componen t on the ribbon, you moldiness set the Return value to 1 (located in the Query Setup group).This indicates to Access to only return the treetop result. See the image below for a screenshot of this. Query Result g. Which borrower requested the smallest mortgage? Query Query Result As with problem 4-10-f, you must set the Return value to 1 in the Design section of the ribbon (located in the Query Setup group). This indicates to Access to only return the top result. See the image below for a screenshot of this. SUGGESTED ANSWERS TO THE CASES 4. 1As in all areas of information technology, DBMSs are constantly