Chapter 4 Security Part II: Auditing Database Systems Review Questions 1. What is a legacy system?
Response: Legacy systems are large mainframe systems systems that were implemented implemented from the late 1960s through the 1980s. Organizations today still make extensive use of these systems. 2. What is the flat-file model? Response: Flat files are data files that contain records with no structured relationships to other files. The flat-file model describes an environment in which individual data files are not
related to other files. End users in this environment own their data files rather than share them with other users. Data files are therefore structured, formatted, and arranged to suit the specific needs of the owner or primary user of the data. Such structuring, however, may exclude data attributes that are useful to other users, thus preventing successful integration of data across the organization. 3. What are four primary elements of the database environment? Response: The database management system (D!S", users, the database administrator, administrator, the physical database, and D!S models. 4. What types of problems does data redundancy cause?
Response: a. data storage problems b. data updating problems c. currency of information problems 5. What flat-file data management problems are solved as a result of using the database concept?
Response: a. data storage problem b. data update problem c. currency problem d. task-data dependency problem 6. What are four four ways ways in which which databas databasee managem management ent systems systems provide provide a control controlled led environment to manage user access and the data resources?
Response: Response: Program Program development, development, backup and recovery, recovery, database database usage usage reporting, reporting, and database access. 7. Explain the relationship between the three levels of the data definition language. As a user, which level would you be most interested in?
Response: One level is the schema, which is the conceptual conceptual view of the data. The schema describes the entire database and it represents the database logically. The second level is the internal view, which is the physical arrangement of the records. At this level, the data records are described as well as linkages between files. The next level is the subschema, which is the external
view of the database that specific users have authorization to use. This is also called the user view and is the level that users find of most interest. 8. What is the internal view of a database?
Response: The internal view of a database is the physical arrangement of the records. It describes the data structure, the linkages between files, and the physical arrangement of the records. 9. What is SQL?
Response: Structured Query Language (SQL) is a fourth-generation, nonprocedural language that allows users to easily input, retrieve, and modify data. It is the standard query language for both mainframe and microcomputer DBMSs. 10. What is DML?
Response: Data manipulation language (DML) is the proprietary programming language that a particular DBMS uses to retrieve, process, and store data. Entire user programs may be written in the DML or, alternatively, selected DML commands can be inserted into programs that are written in universal languages, such as JAVA, C++, and even older languages such as COBOL and FORTRAN. Inserting DML commands enables standard programs, which were originally written for the flat-file environment, to be easily converted to work in a database environment. 11. What is a data dictionary, and what purpose does it serve?
Response: The data dictionary describes every data element in the database. It enables all users (and programmers) to share a common view of the data resource, thus greatly facilitating the analysis of user needs. The data dictionary may be in both paper form and online. Most DBMSs employ special software for managing the data dictionary. 12. What are the two fundamental components of data structures?
Response: Organization and access method. 13. What are the criteria that influence the selection of the data structure?
Response: a. rapid file access and data retrieval b. efficient use of disk storage space c. high throughput for transaction processing d. protection from data loss e. ease of recovery from system failure f. accommodation of file growth 14. What is a data attribute (or field)?
Response: A data attribute, or field, is a single item of data, such as customer name, account balance, or address. 15. Define a data record.
Response: A record is a group of closely related fields that describe the relevant characteristics of an instance of the entity being tracked. A record can be visualized as similar to a
row in a table of data. 16. What is a record association?
Response: Record types exist in relation to other record types. This is called a record association. There are three basic record associations: one-to-one, one-to-many, and many-tomany.
17. What is a database?
Response: A database is the set of record types that an organization needs to support its business processes. 18. What is an enterprise database?
Response: An enterprise database is a common set of data files or tables for the entire organization, or enterprise. The most recent application development systems, such as Oracle’s latest package or Microsoft’s dot-net, focus on the ability to use an enterprise database as the foundation for applications that interface across the entire enterprise. The Enterprise Resource Planning (ERP) software so popular in the 1990s is also based on the principle of an enterprise database. 19. Discuss and give an example of one-to-one, one-to-many, and many-to-many record associations.
Response: A one-to-one association means that for every occurrence in record type X, either zero or one occurrence exists of record type Y. An example would be that for every student, only one social security number exists. A one-to-many association means that for every occurrence in record type X, either zero, one, or many occurrences exist of record type Y. An example would be buyers of assigned seating at concerts. Each potential buyer would leave the sales box office with zero, one, or many seats. A many-to-many association is a two-way relationship. For each occurrence of record types X and Y, zero, one, or many occurrences exist of record type Y and X, respectively. An example would be a student-professor relationship. Each student has multiple professors each semester, and each professor has multiple students each semester. 20. Why is a hierarchical data model considered to be a navigational database? What are some limitations of the hierarchical database model?
Response: The hierarchical database model is considered to be a navigational database because traversing through it requires predefined linkages between related records that start at the root. A limitation of this model is that a parent record may own one or more child, but no child record may have more than one parent. If a child logically needs to be linked to one or more parents, duplication of files with different predefined linkages is required. 21. What is a partitioned database and what are its advantages? Specify any disadvantages.
Response: The partitioned database approach splits the central database into segments or partitions that are distributed to their primary users. The advantages of this approach follow: having data stored at local sites increases users’ control, transaction processing response time is improved by permitting local access to data and reducing the volume of data that must be transmitted between IT units, and partitioned databases can reduce the potential effects of a disaster. By locating data at several sites, the loss of a single IT unit does not eliminate all data processing by the organization.
22. What is a replicated database, and why is concurrency control difficult to manage in this setting?
Response: Replicated databases are effective in companies where there exists a high
degree of data sharing but no primary user. Since common data are replicated at each IT unit site, the data traffic between sites is reduced considerably. Figure 4.18 illustrates the replicated database model. The primary justification for a replicated database is to support read-only queries. With data replicated at every site, data access for query purposes is ensured, and lockouts and delays due to data traffic are minimized. The problem with this approach is maintaining current versions of the database at each site. Since each IT unit processes only its transactions, common data replicated at each site are affected by different transactions and reflect different values. 23. What is time-stamping, and why is it useful?
Response: The second part of the concurrency control process is to time-stamp each transaction. A system-wide clock is used to keep all sites, some of which may be in different time zones, on the same logical time. Each time stamp is made unique by incorporating the site’s ID number. Time-stamping is used in distributed data processing environments to help ensure the presence of complete and accurate data by avoiding the processing of conflicting transactions. 24. Explain the grandparent–parent–child backup technique. Is it used for sequential files or direct access techniques? Why? How many generations can be backed up?
Response: This procedure is when the current master file (the parent) is processed against the transaction file to produce a new master file (the child). With the next batch of transactions, the child becomes the current master file (the parent), and the original parent becomes the backup (the grandparent) file. The new master file that emerges from the update process is the child. This procedure is continued with each new batch of transactions, creating generations of backup files. When the desired number of backup copies is reached, the oldest backup file is erased (scratched). This process is for sequential files and batch direct access systems. The updates for online, real-time, direct access systems occur continuously throughout the day. The master files are backed up at certain intervals. The number of backed up master files kept varies from firm to firm. Typically a firm will have many backups during the day. During the evening, the master file from the end of the day will be the file retained for backup purposes. 25. Distinguish between data access and access privileges. Give an example by designing and explaining a database authorization table.
Response: Data access indicates whether or not a user can access data from a particular application. Access privileges define if the user can only read the data, or if she or he can insert new records and modify or delete existing records. 26. What are inference controls? Why are they needed?
Response: Inference controls are implemented to preserve the confidentiality and integrity of the database from users who try to infer specific data values by using query features. The query feature might not allow certain types of direct questions to be asked, but a clever user may determine a way to ask multiple questions, which together give the desired answer to the question. 27. What are the four basic backup and recovery features necessary in a DBMS? Briefly explain each.
Response: a. Backup. This feature makes a periodic backup of the entire database. b. Transaction Log (Journal). This feature tracks all transactions in a transaction log.
c. Checkpoint Feature. This feature suspends all data processing while the system reconciles the transaction log and the database change log against the database. d. Recovery Module. This feature use the logs and backup files to restart the system after a failure.
28. What is data encryption?
Response: Data encryption uses an algorithm to scramble selected data, thus making them unreadable to an intruder browsing the database. In addition to protecting stored data, encryption is used for protecting data that are transmitted over communications lines. 29. What are biometric devices?
Response: Biometric devices measure various personal characteristics, such as fingerprints, voice prints, retina prints, or signature characteristics. These user characteristics are digitized and stored permanently in a database security file or on an identification card that the user carries. When an individual attempts to access the database, a special scanning device captures his or her biometric characteristics, which it compares with the profile data stored on file or the ID card. If the data do not match, access is denied. 30. What is a user-defined procedure?
Response: A user-defined procedure allows the user to create a personal security program or routine to provide more positive user identification than a single password. Thus, in addition to a password, the security procedure asks a series of personal questions (such as the user’s mother’s maiden name), which only the legitimate user should know.
Discussion Questions 1. In the flat-file data management environment, users are said to own their data files. What is meant by this ownership concept?
Response: In the traditional data management environment, applications are developed with data and program dependency. Typically, these programs are application specific. Thus, the users of the application data tend to be proprietary about the data in “their” applications and may not be amenable to sharing such data. 2. Discuss the potential aggravations you might face as a student as a result of your university using a traditional data management environment—that is, different databases for the registrar, library, parking permits, and so on.
Response: If your university used different databases for the registrar, library, parking, food services, and computing services, the number of forms that you would have to fill out if your personal data changes would be large. For example, if you moved during the semester to a different apartment, the university should be notified. In this situation, you might be required to go to each service individually and fill out an address form, or you might go to one central location and fill out a form that is copied and sent to the various areas for update. Assume that in this case your address was keyed in correctly by the registrar. You received correspondence from the registrar and assumed that the address correction was made. However, a keying error was made by the library staff, and you did not receive notification that you had a forgotten past due library book. After the end of the semester, you did not receive your final grade report. When you called the registrar, you found out that the library reported that you had an overdue book and your grades should be held until the book is returned and the fine is paid. 3. Discuss why control procedures over access to the database become more crucial under the database concept than in the flat-file data management environment. What role does the
DBMS play in helping to control the database environment?
Response: Under the database concept, the data is centrally stored with many different users accessing the database. However, each user should not have access to the whole database. Under the flat-file data management environment where the data and programs were linked, the user access problem was not as great a threat. The DBMS is a special software system that is programmed to know which data each user is authorized to access. This controlled authorization is crucial in centrally stored DBMSs. 4. What is the relationship between a schema and a subschema?
Response: The schema represents the physical storage of the entire database. A subschema is a subset of the entire database; it represents a user’s view of the database. Numerous subschemas exist for every schema. 5. Discuss the two ways in which users can access the database in a database environment.
Response: The first way the user may access the database is through user programs prepared by systems professionals. These programs send data access requests to the DBMS. The DBMS then validates the requests and retrieves the data for processing. The presence of the DBMS is transparent to the user. The second method involves direct inquiry on the part of the user. DBMSs have built-in data inquiries that allow authorized users to retrieve and m anipulate data without the assistance of the professional programmers. 6. How are special database commands inserted into conventional application programs? Why is this necessary?
Response: A data manipulation language, which can either retrieve, modify, or control, is used to insert special database commands into conventional application programs. This may be necessary to allow standard programs such as COBOL and FORTRAN to interact with and manipulate the database. 7. Why might it be advantageous for an organization to use DML commands written in COBOL versus a proprietary programming language?
Response: Using a standard programming language, such as COBOL, with DML commands inserted on an as-needed basis to retrieve, process, and store data, may be beneficial in maintaining some degree of independence from the DBMS vendor. If the organization switches vendors and the new vendor uses a different DML, only the DML commands need to be replaced, not the entire program. 8. SQL has been said to place power in the hands of the user. What is meant by this statement?
Response: SQL allows users to retrieve data from many different files without the assistance of programming professionals. Thus, if the user has access to data files and knows the SQL, which is very user friendly, the user may retrieve the data instantaneously. 9. Discuss the importance of the role of the database administrator. Why wasn’t such a role necessary in the traditional data management environment? What tasks are performed by the DBA?
Response: In the traditional data management environment, the data were not centrally
stored for many different applications to use. Because the data are centrally stored and shared by many users in a database environment, the need for an individual to care for and control these files arose. The database administrator is responsible for database planning, developing the data requirements and data dictionary, database design and controls, database implementation and access controls, operation and maintenance, and establishing and reviewing the standards and procedures.
10. As users determine new computer application needs, requests must be sent to both the system programmers and the DBA. Why is it important that these two groups perform separate functions, and what are these functions?
Response: The system programmers program the manner in which the data will be retrieved, manipulated, reported, and stored. They do not need, and should not have, access to the data, except perhaps temporarily to test the programs. The database administrator controls access to the data. If one person has the authority to write programs and access data, then control issues become a concern. The potential to commit fraud or embezzlement or destroy or alter the company’s records becomes too great. 11. How can data be centralized in a distributed data processing system?
Response: The data are stored centrally, but updated or processed, at the local (remote) site. Thus, data are retrieved from the centralized data store, processed locally, and then sent back to the centralized data store. 12. In a distributed data processing system, why can tempora ry inconsistencies result in permanent damage to accounting records? Explain with an example.
Response: In between the time a processing application (a) updates a subsidiary account and (b) updates the control account, a temporary inconsistency exists. Permanent damage may result between the time an account value is read and the time it is written or updated. Consider the following example where a payment for sub account 1 is received for $500 and a payment is received for sub account 2 for $800.
Time 1:00:001 1:00:001 1:00:002 1:00:002 1:00:003 1:00:003 1:00:004 1:00:005
Site A B A B A B A B
Instruction Sub Account 1 Read sub acct 1 1,000 Read sub acct 2 Update sub acct 1 500 Update sub acct 2 Read control acct Read control acct Update control acct Update control acct
Sub Account 2
AR Control Account
3,000 2,200 20,000 20,000 19,500 19,200
Thus, the data never reflect the second to last instruction indicated. The AR control account should reflect payments received of $1,300 and have a value of $18,700, but reflect payments of only $800. Thus, the transaction of $500 is lost, and the control and subsidiary ledgers are out of balance. 13. Explain the deadlock phenomenon. Discuss how it could occur with a phone-in mail order system that locks the inventory records until the order is complete.
Response: Deadlock occurs when multiple sites lock each other out. Take, for example, a process where Customer 1 wants to order two items—Item A and Item B. The customer informs the phone clerk that he or she wants Item A, and the record for Item A is locked until the order is complete in case any changes are made. Customer 1 then requests Item B, but it is locked by another customer’s order. The phone clerk will apologize for the delay and say the system is slow today. Meanwhile, Customer 2 who has just ordered Item B and locked it requests Item A, which is locked because of Customer 1’s order. The phone clerk who is helping Customer 2 will
apologize for the delay and say the system is slow today. Unfortunately, neither record can be unlocked until the competing orders are complete, which results in deadlock. This condition will not be resolved until some intervention occurs.
14. Which database method would be most appropriate for ticket sales at thirty different outlets to an assigned seating concert? Why?
Response: Partitioned databases would not be appropriate because the seats received by the customer would be a function of the ticket outlet where the purchase is made. Replicated databases would not be appropriate because duplicate tickets would be sold. Centralized databases with local processing are the appropriate method. The seat data would be centrally stored. Only the data for the seat being purchased would be locked and sent to the appropriate node. Once the seat is sold, the record is processed locally and then the data are transmitted back to the central computer. 15. Why is it risky to allow programmers to create user subschemas and assign access authority to users? What unethical technique do programmers sometimes use when they are not allowed to assign access authority to users?
Response: Such a subschema is a subset of the total database that defines the user’s access authority. The database administrator has primary responsibility for designing these authorization tables. The programmers should not perform this task because the concept of authorization could be circumvented. The tasks of programming and the creation of user authorization tables are incompatible tasks and should be performed by different individuals. 16. Is access control of greater concern in the flat-file or database file environment?
Response: In the flat-file environment, data and programs were dependent. User authorization was tied to each specific application. Under the database file environment, all of the data is centrally stored and shared, and access control becomes a greater concern. It is dealt with by the use of data authorization tables. 17. How can passwords actually circumvent security? What actions can be taken to minimize this?
Response: Users may share their passwords, write down their passwords, or use easily guessed passwords. Protection against these includes software that allows only smart passwords and one-time passwords used in conjunction with smart cards. 18. Describe the characteristics of properly designed relational tables.
Response: Properly designed tables possess the following characteristics: a. All occurrences at the intersection of a row and a column are a single value. No multiple values (repeating groups) are allowed. b. The attribute values in any column must all be of the same class. c. Each column in a given table must be uniquely named. However, different tables may contain columns with the same name. d. Each row in the table must be unique in at least one attribute. This attribute is the primary key. 19. In a database environment, individual users may be granted summary and statistical query access to confidential data to which they normally are denied direct access. Describe how security can be preserved through inference controls.
Response: Inference controls are used to prevent users from inferring, through query features, specific data values that they otherwise are unauthorized to access. Inference controls attempt to prevent three types of compromises to the database.
a. Positive compromise. The user determines the specific value of a data item. b. Negative compromise. The user determines that a data item does not have a specific value. c. Approximate compromise. The user is unable to determine the exact value of an item but is able to estimate it with sufficient accuracy to violate the confidentiality of the data. 20. Describe the backup and recovery features of centralized DBMSs.
Response: a. Backup. The backup feature makes a periodic backup of the entire database. This is an automatic procedure that should be performed at least once a day. The backup copy should then be stored in a secure remote area. b. Transaction log (journal). The transaction log feature provides an audit trail of all processed transactions. It lists transactions in a transaction log file and records the resulting changes to the database in a separate database change log. c. Checkpoint feature. The checkpoint feature suspends all data processing while the system reconciles the transaction log and the database change log against the database. At this point, the system is in a quiet state. Checkpoints occur automatically several times an hour. If a failure occurs, it is usually possible to restart the processing from the last checkpoint. Thus, only a few minutes of transaction processing must be repeated. d. Recovery module. The recovery module uses the logs and backup files to restart the system after a failure. Multiple Choice Questions
1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12.
C D C B D D A C B C B E
Problems
1. DBMS versus Flat-File Processing The Werner Manufacturing Corporation has a flat-file processing system. The information processing facility is very large. Different applications, such as order processing, production planning, inventory management, accounting systems, payroll, and marketing systems, use separate tape and disk files. The corporation has recently hired a consulting firm to investigate the possibility of switching to a database management system. Prepare a memo to the top management team at Werner explaining the advantages of a DBMS. Also, discuss the necessity of a database administrator and the job functions this person would perform.
Response: August 23, 20XX MEMO TO: Al Brindifi, VP Operations Carla Glasser, VP Finance James Closter, VP Marketing Julia Tinner, Controller Beth Clark, Manager of Information Systems FROM: Solutions Consultants SUBJ: Conversion of data processing systems After examining the operations of your organization and deriving a rough-cut estimate of your needs, this consulting team feels that your organization would benefit greatly from a database management system. A database management system will allow data to be shared among the departments, thus facilitating communication. One of the problems that has repeatedly been brought to our attention is the need by user groups for information that is currently kept by the organization. These user groups are having trouble gaining access to the data because the data does not reside in their applications. A database management system will provide a central data source whereby each authorized user group may gain access to the data. Further, we see inefficiencies in processing data due to data duplication by various applications. An even greater problem is that the data are oftentimes not consistent from one application to another. The DBMS will reduce data duplication and data redundancy. We propose that you begin a conversion to a DBMS. This conversion will require that a database administrator be appointed or hired. A DBMS requires that the central data store be diligently planned, managed, and maintained. The database administrator would perform these functions as well as control user authorization of the data. 2. Database Design Design a relational database system for a large costume rental store. The store has approximately 3,200 customers each year. It is stocked with over 500 costumes in various sizes. The rental costumes and other items that may be purchased by the customer (e.g., make-up and teeth) are purchased from approximately thirty-five different suppliers. Design the necessary database files. Make sure they are in third normal form, and indicate the necessary linkages. Response: On the following page is a list of database files unique to this type of business. Additionally, traditional accounting files would be necessary. The key fields used for linking are in boldface.
Costume Master Costume ID Number Description No. of Costumes No. of Times Rented No. of Times Rented past yr Vendor ID Number
Costume Inventory Number Costume ID Number Copy Number Purchase Date Size No. of Times Rented Last Date Rented Status-Checked Out
Customer Master Customer ID Number Last Name First Name Middle Initial Address1 Address2 City State Zip Code Last Date Rented a Costume
Open Costume Transaction Log Customer ID Number Costume ID Number Copy Number Date Rented Date Due Transaction Number
Closed Costume Transaction Log Customer ID Number Costume ID Number Copy Number Date Rented Date Due Transaction Number Date Back
Sundries Inventory Item Number Description Quantity on Hand EOQ Safety Stock
Sundries Transaction Log Item Number Quantity Date sold
Vendor Master Vendor ID Company Name Address1 Address2 City State Zip Code
Fine Log Customer ID Number Transaction Number Amount Due Amount Paid Date Paid
3. Database Design Sears Roebuck, the most well-known and oldest mail-order retailer in the country, discontinued its mail-order operations a few years ago. Other mail-order marketers are beginning to use information systems to trim printing and postage costs of their catalogs. They also want to more effectively target their customers. Explain how an appropriately designed coding system for inventory items incorporated in a database management system with SQL capabilities could allow more cost-efficient and effective mail-order operations. Sketch the necessary database files. Response: Mail-order catalogs are beginning to be customized based upon the preferences and purchasing patterns of the customers. If data are stored regarding customer preferences and buying patterns, then catalogs containing only the items that are believed to be of value to the customer need to be contained in the catalog. Printing costs and postage costs would be saved. Also, more items that fit the pattern of the customer could be included. Further, the customer would not have to weed through the catalog to find the items that she or he likes. The changing technology makes customized or semi-customized (for groups of people) catalogs a reality.
Listed below are some files and some fields in the files that would be necessary, in addition to the traditional accounts receivable, cash receipts, vendor, and general ledger files. Customer Master File Customer Number Name Address Phone Credit Card Type Credit Card Number Credit Card Expiration Last Purchase Date Cumulative Purchase Amount Purchases in Last 12 months Purchases in Last 3 months $ Purchases of Type A $ Purchases of Type B $ Purchases of Type C $ Purchases of Type D Request Type A catalog Request Type B catalog Request Type C catalog Request Type D catalog
Sales File Customer Number Sales Order Number $ Amount of Items Taxes Freight Total Amount
Inventory Master File Item Number Item Description Product Type Quantity on Hand Selling Price
Inventory Type File Product Type Product Classification
Sales Order Detail File Sales Order Number Item Number Quantity Discount
4. Database Deadlock How is a lockout different from a deadlock? Give an accounting example to illustrate why a database lockout is necessary and how a deadlock can occur. Use actual table names in your example. Response: Lockout. The example below illustrates how two different transactions are being processed against the same AR control account within the same time frame. The individual logic steps are shown in their actual sequence of execution. If no database lockout were in place, the AR control value of $20,000 is retrieved by both users of the system. One user is posting a payment to sub account 1 of $500 and the other is posting a payment to sub account 2 for $800.
Time 1:00:001 1:00:001 1:00:002 1:00:002 1:00:003 1:00:003 1:00:004 1:00:005
Site A B A B A B A B
Instruction Sub Account 1 Read sub acct 1 1,000 Read sub acct 2 Update sub acct 1 500 Update sub acct 2 Read control acct Read control acct Update control acct Update control acct
Sub Account 2
AR Control Account
3,000 2,200 20,000 20,000 19,500 19,200
The update process does not reflect the second to last instruction executed. The AR control account should reflect payments received of $1,300, and have a value of $18,700, but only $800 of payments are accounted for. Thus, a transaction is lost, and the control and subsidiary
ledgers are out of balance. Deadlock. A deadlock occurs when multiple sites lock out each other. Take for example a mail
order company in which two customers are processing transactions simultaneously. Customer 1 wants to order 2 items— Item A and Item B. Customer 2 is purchasing Item B and Item A. Customer 1 informs the phone clerk that he or she wants Item A and the record for Item A is locked until the order is complete. Meanwhile, Customer 2 orders Item B from another phone clerk who locks it. Customer 1 then requests Item B, which is locked by Customer 2’s order. The phone clerk apologizes for the delay and says the system is slow today. Customer 2 then requests Item A, which is locked by Customer 1’s order. The phone clerk who is helping Customer 2 apologizes for the delay and says the system is slow today. Unfortunately, neither transaction can be completed, resulting in deadlock. This condition will not be resolved unless some type of intervention occurs. 5. System Configuration First State Bank provides full banking services to its customers through • automatic teller machines. • checking and saving accounts. certificates of deposits. • loans. • electronic payroll. • electronic payment of customers’ bills. The bank has eleven branch offices that cover a 30-mile radius. The main office maintains a mainframe computer that serves the branch offices. The competitive nature of the banking industry requires that customer satisfaction be considered. Customers want prompt and accurate servicing of transactions. Thus, accuracy and speed are crucial to the success of First State Bank. How would you suggest the databases and data communications facilities be configured for First State Bank? •
Response: This configuration will entail a hybrid of centralized and decentralized processing. Because customers should be able to bank at any of the 11 branches, cash withdrawal transactions from each branch should be centrally processed and the customer databases updated. Deposits of cash and checks and payment of customers’ bills can be stored on remote computers, with the data being sent to the mainframe periodically. Banks tend to be more concerned with the promptness in recording cash withdrawals than cash receipts. Thus, the data representing the payment of customers’ bills may be sent to the mainframe computer four to six times a day for updating, while deposits of cash may be transmitted only twice a day. The payroll accounts can be stored and processed on the remote computers because these accounts do not have ATM cards. Further, the mainframe at the main office can process more efficiently if a front-end processor is used to relieve the mainframe of data communication processes. A common-carrier network is probably a good communication medium, but the data should be encrypted. The individual teller terminals should be connected to the remote main computer via dumb terminals. The tellers should not have any reason to download data regarding customer accounts.
6. Database Authorization Table The following information is stored in two relational database files. Employee Master File Social Security number Name Address Date hired Hourly wage rate Marital status Number of exemptions
Weekly Payroll File Social Security number Hours worked Deductions Bonuses
Required: a. Bogey works in personnel and Bacall works in payroll. Prepare a database authorization table that you believe is appropriate for Bogey and Bacall. b. Discuss any potential exposure if the right prevention devices are not in place or if Bogey and Bacall collude.
Response: a. See table below.
Depar t ment
Per s on nel
Per s on nel
Pay r ol l
Pay r ol l
Us er
Bogey
Bac al l
Bogey
Bac al l
Pas s wor d
c as a
bl anc a
c as a
bl anc a
Aut hor i t y : Read I n s e r t Modi f y Del et e
Y Y Y Y
Y N N N
N N N N
Y Y Y Y
b. If the right prevention device is not in effect, then Bacall may insert fictitious employees and write checks to employees who do not exist and keep the checks for herself. Further, Bacall may be able to pay her friends or relatives more than they earned by increasing their hourly wage rates. If Bogey and Bacall collude, then they can perform these tasks. Other controls are needed, such as supervisor review of paychecks and/or a paymaster other than Bacall, distributing checks. 7. Distributed Databases The XYZ Company is a geographically distributed organization with several sites around the country. Users at these sites need rapid access to common data for read-only purposes. Which distributed database method is best under these circumstances? Explain your reasoning. Response: The best distributed approach is a replicated database. Reasoning: • The users are distributed around the country and need rapid access to data. A centralized model may result in long delays because of network traffic and database lockout.
•
•
User data needs are common, not unique. Because there are no identifiable primary users, partitioning the database will accomplish nothing. Because usage is read-only, changes will not occur and database concurrency is not a problem.
8. Distributed Databases The ABC Company is a geographically distributed organization with several sites around the country. Users at these sites need rapid access to data for transaction processing purposes. The sites are autonomous; they do not share the same customers, products, or suppliers. Which distributed database method is best under these circumstances? Explain your reasoning. Response: The best distributed approach is a partitioned database. Reasoning: • The users are distributed around the country and need rapid access to data. A centralized model may result in long delays because of network traffic and database lockout. • User data needs are unique with identifiable primary users. There is no need to replicate the entire database. • Because users are unique, changes to the database will not cause database concurrency problems.
