Chamberlain College of Nursing - MIS 561 Week 4 Midterm Exam Questions and Answers Rated A

Document Content and Description Below

MIS 561 Midterm Exam (TCO 1) We know that a DBA (Database Administrator) should have knowledge of database structures, such as tables and SQL. List and describe at least four other categories of tas... ks expected from a DBA. Answer: A DBA must ensure that: databases are useful, usable, available, and correct. there is database performance, monitoring, and tuning. there is access authorization and security. there is database backup and recovery. there are interfaces with databases. (TCO 2) Before a newly installed DBMS can be used effectively, standards and procedures must be developed for database usage. Name some of these standards that, if practiced, could help in reducing cost of supporting end users. Answer: Standards are common practices that ensure the consistency and effectiveness of the database environment, such as database naming conventions. Procedures are defined, step-by-step instructions that direct the processes required for handling specific events, such as a disaster recovery plan. Failure to implement database standards and procedures will result in a database environment that is confusing and difficult to manage. The DBA should develop database standards and procedures as a component of corporate-wide IT standards and procedures. They should be stored together in a central location as a printed document, in an online format, or as both. Several vendors offer canned standards and procedures that can be purchased for specific DBMS products. (TCO 3) Designing and implementing a physical database from a logical data model is not just a simple matter of mapping entities to tables, attributes to columns, and relationships to referential constraints. Quite a few other database design issues must be addressed. Effective storage planning is part of the physical process design. As a DBA, how do you calculate the amount of storage space usage required to store a table? Answer: To calculate the amount of storage required to store a table, the DBA must first establish the row size. This is accomplished by adding up the maximum size of all of the columns, based on their assigned data type and length. An average size can be substituted for variable-length columns. An estimate for the number of bytes required can be calculated by multiplying the row size by the number of rows planned to be stored in the table. Of course, the DBA must also factor in any storage overhead required by the DBMS for things like row and page headers, pointers, and the like. Because each DBMS uses different techniques, each DBMS will have different overhead requirements. TCO 4) When implementing a physical database from a logical data model, you must consider database performance by allowing data in the database to be accessed more rapidly. One way to improve performance is to reduce the number of input/output when requesting data from a database. Which method do you recommend for possible reduced I/O when querying chunks of data? How does it improve the amount of input/output and what are the disadvantages of the method? Answer: An access request using an index can perform better than a table scan because the requested data can be accessed directly using the pointers from the leaf node of the index. This reduces I/O and enhances performance for most data access requests. If you need to retrieve ranges of data, hashing is not optimal because the data will be spread out instead of clustered, and therefore, I/O costs will be substantial. Additionally, hashing requires a unique key to minimize collisions. So, hashing should be considered only when an overwhelming majority of the queries against the table are based on lookups using the key and will return small result sets. (TCO 5) What is a lock in a database and what does it accomplish? Answer: The DBMS uses a locking mechanism to enable multiple, concurrent users to access and modify data in the database. By using locks, the DBMS automatically guarantees the integrity of data. The DBMS locking strategies permit multiple users from multiple environments to access and modify data in the database at the same time. Locks are used to ensure the integrity of data. When a database resource is locked by one process, another process is not permitted to change the locked data. Locking is necessary to enable the DBMS to facilitate the ACID properties of transaction processing. As a DBA, you are asked to add a column in the middle of an existing table that is already populated with data. What steps do you take in accomplishing this task? With what factors are you concerned? Answer: To accomplish such a task, the DBA must drop the table and recreate it with the new column in the middle. But what about the data? When the table is dropped, the data are deleted unless the DBA was wise enough to first unload the data. But what about the indexes on the table? Well, they too are dropped when the table is dropped, so unless the DBA knows this and recreates the indexes too, performance will suffer. The same is true for database security: When the table is dropped, all security for the table is also dropped. And this is but one example of a simple change that becomes difficult to implement and manage. Adding to this dilemma is the fact that most organizations have at least two, and sometime more, copies of each database. At the very least, a test and production version will exist. (TCO 7) Downtime is not tolerated by some businesses. The cost of downtime varies from business to business. Which factors do you consider when estimating the cost of downtime? Answer: When estimating the cost of downtime, remember to factor in all of the costs, including lost business during the outage. cost of catching up after systems are once again available. legal costs of any lawsuits. impact of reduced stock value (especially for dotcoms that rely on computerized systems for all of their business). Additionally, downtime can negatively impact a company’s image. In this day and age, an outage of any length that impacts business, particularly e-business, will be reported by the press—and if the story is big enough, not just the computer press, but the business press as well. Let’s face it, bad news travels fast. Recovering from negative publicity can be a difficult, if not impossible, task. (TCO 2) Each of the major DBMS products changes quite rapidly, and keeping DBMS software up-to-date can be a difficult task. As a DBA, what are the issues to consider when upgrading to a new DBMS version to have little or no disruption? Answer: A DBMS version upgrade can be thought of as a special case of a new installation. All of the procedures required of a new installation apply to an upgrade: you must plan for appropriate resources, reconsider all system parameters, and ensure that all supporting software is appropriately connected. However, another serious issue must be planned for: existing users and applications. An upgrade needs to be planned to cause as little disruption to the existing users as possible. Furthermore, any additional software that works with the DBMS (such as purchased applications, DBA tools, utilities, and so on) must be verified to be compatible with the new DBMS version. (TCO 5) What is a COMMIT in a database and what does it accomplish? Answer: The results of running a transaction will record the effects of a business process—a complete business process. The data in the database must be correct and proper after the transaction executes. When all of the steps that make up a specific transaction have been accomplished, a COMMIT is issued. The COMMIT signals that all work since the last COMMIT is correct and should be externalized to the database. At any point within the transaction, the decision can be made to stop and roll back the effects of all changes since the last COMMIT. (TCO 1) What are the duties of a system administrator (SA) in regards to a database, and how are a DBA’s (Database Administrator) responsibilities different from an SA when supporting a database? Answer: A system administrator role is IT infrastructure and has typically no responsibility for the database design and support. The SA is responsible for networking protocols, operating systems parameters, message queuing software and ensuring that IT infrastructure is operational for database development. Setting up the DBMS and applying the vendor maintenance and migration to new DBMS releases and versions are tasks of SA. The DBA is responsible for the database, and the SA is responsible for DBMS installation, modification, and support. (TCO 2) Suppose that as a DBA, you are tasked with installing a new database management system in your organization. Would you choose the default system parameters when installing the system software? Explain and support your answer with reasoning. Answer: Student reasoning in support of their choice is important here. System parameters configure the DBA authorization to DBMS, active log files, amount of memory used for data, and turning program features on or off. These values are not the same for all DBMSs and depend on the DBMS specification. It could result in poor performance if system resources are not allocated properly during the installation or setup. (TCO 3) Designing and implementing a physical database from a logical data model is not just a simple matter of mapping entities to tables, attributes to columns, and relationships to referential constraints. Quite a few other database design issues must be addressed. What are the physical data storage issues with a table’s data that must be addressed by the DBA? Answer: Although relational data are expressed to the user by means of a table, underlying files or data sets must exist to store the actual data—and those files are not necessarily stored as a simple grid of rows and columns. During the physical design process, the DBA must map each table to a physical structure to store the table’s data. These physical structures are commonly called tablespaces (or data spaces). A database comprises one or more tablespaces; and each tablespace contains one or more tables. Depending on the DBMS, a table may be able to span multiple tablespaces, too. The DBA decides how to map tables to tablespaces based on the anticipated usage of the data, the type of tablespace, and the features of the DBMS. (TCO 6) How could a DBA make changes to the amount of free space for a database object? What steps should be taken to accomplish this task? Answer: A somewhat more difficult change is modifying the amount of free space for a database object. Such a change typically is accomplished by using an ALTER statement, but additional work is required after the ALTER statement has been issued. The additional free space does not magically appear after issuing an ALTER statement. In order to reclaim the free space for the tablespace, the DBA will have to reorganize the tablespace after successfully issuing the ALTER statement. Additional work is also required to ensure that sufficient disk space is available for the increased amount of free space. Therefore, the DBA needs to understand how each parameter that can be altered is actually impacted by the ALTER statement. Furthermore, the DBA needs to understand when additional work is required to fully implement the desired change. (TCO 7) Data are generated more than ever with business intelligence applications, data warehousing, data marts, and advanced analytics for business decision support. What kind of challenges with respect to data availability does a DBA face in this environment? Answer: The unloading and loading of external data to operational data stores, and then on to data warehouses and data marts, has increased the number of database utility operations that need to be run and administered. The time taken to propagate data has conversely affected the overall availability window of both the data sources and data targets during unload and load processing. More and more companies are finding new ways to use core business data for decision support. For example, credit card companies maintain a basic body of information that they use to list purchases and prepare monthly statements. This same information can be used to analyze consumer spending patterns and design promotions that target specific demographic groups and, ultimately, individual consumers. This means that core business data must be replicated across multiple database environments and made available in user-friendly formats. Therefore, the availability of operational data can be negatively impacted by the requirements of decision support users, since large amounts of data are not available for update during bulk data unload processing. (TCO 1) What are the duties of a data administrator (DA) with regards to a database, and how are a DBA’s (Database Administrator) tasks different from a DA’s in dealing with a database? Answer: DA separates the business aspects of data resource management from the technology used to manage data. There are more technical aspects of database design and daily operation requirements of a database. The DBA must understand the data models built by a DA. The DBA uses the logical model to build the physical model. The DA is responsible for issues such as: • Identifying data owners and stewards • Setting standards for control and usage of data • Identifying and cataloging the data required by business users • Production of conceptual and logical data models to accurately depict the relationship among data elements for business processes • Production of an enterprise data model that incorporates all of the data used by all of the organization’s business processes • Setting data policies for the organization (TCO 2) Before a newly installed DBMS can be used effectively, standards and procedures must be developed for database usage. Name some of these standards that, if practiced, could help in reducing cost of supporting end users. Answer: Standards are common practices that ensure the consistency and effectiveness of the database environment, such as database naming conventions. Procedures are defined, step-by-step instructions that direct the processes required for handling specific events, such as a disaster recovery plan. Failure to implement database standards and procedures will result in a database environment that is confusing and difficult to manage. The DBA should develop database standards and procedures as a component of corporate-wide IT standards and procedures. They should be stored together in a central location as a printed document, in an online format, or as both. Several vendors offer canned standards and procedures that can be purchased for specific DBMS products. (TCO 6) What is the role of DDL? What are the limitations of DDL in modifying a database? Answer: DDL consists of three SQL verbs: CREATE, DROP, and ALTER. The CREATE statement is used to create a database object initially, and the DROP statement is used to remove a database object from the system. The ALTER statement is used to make changes to database objects. Not every aspect of a database object can be changed by using the ALTER statement. Some types of changes require the database object to be dropped and recreated with the new parameters. For example, you cannot use ALTER to add a column between two existing columns. Additionally, typically, you cannot remove columns from a table. To add a column anywhere but at the end of the column list, or to remove a column from a table, you must first drop the table and then recreate it with the desired changes. Every DBMS has limitations on what can be changed by using the ALTER statement. Furthermore, not just tables, but most database objects have certain aspects that cannot be changed using ALTER. (TCO 7) How is availability different from performance in a database? How do you define availability for a database? Answer: Another definition of availability is the percentage of time that a system can be used for productive work. The required availability of an application will vary from organization to organization, within an organization from system to system, and even from user to user. Database availability and database performance are terms that are often confused with each other. The major difference lies in the user’s ability to access the database. It is possible to access a database suffering from poor performance. Availability and performance are different and must be treated by the DBA as separate issues—even though a severe performance problem is a potential availability problem. Availability comprises manageability, recoverability, reliability, and serviceability. Manageability—the ability to create and maintain an effective environment that delivers service to users. Recoverability—the ability to reestablish service in the event of an error or component failure. Reliability—the ability to deliver service at specified levels for a stated period. Serviceability—the ability to determine the existence of problems, diagnose their cause(s), and repair the problems. All four of these abilities impact the overall availability of a system, database, or application. (TCO 4) When implementing a physical database from a logical data model, you must consider database performance by allowing data in the database to be accessed more rapidly. One way to improve performance is to reduce the number of input/output when running queries against a table. If you are retrieving a row or small set of data using the key values, which method will enable quick direct access to data? Explain the method by listing advantages and disadvantages? Answer: Hashing is a technique that uses key values to enable quick direct access to data. An algorithm is used to transform the key values into a pointer to the physical location of the rows that have those key values. The algorithm is typically referred to as a randomizer, because the goal of the hashing routine is to spread the key values evenly throughout the physical storage. When the randomizer generates the same pointer for two different key values, a collision occurs. Different techniques can be used to resolve collisions. Typically, the collision resolution algorithm attempts to keep the data on the same page to avoid additional I/O. When pages fill up and collisions force the data to another page, performance rapidly degrades. Hashing works better with a large amount of free space. A disadvantage of hashing is the amount of space that must be pre-allocated for data to be hashed into. Hashing has a big advantage, in that normally, only one database I/O request is needed to retrieve a row of data using the key. Hashing works best for direct data lookup of one row, or a small number of rows. (TCO 5) Name two popular standard interfaces that enable application programs to access databases using SQL. What are the similarities and differences between them? Answer: There are several popular standard interfaces or APIs (Application Programming Interfaces) for database programming, including ODBC, JDBC, SQLJ, and OLE DB. The two popular ones are ODBC and JDBC. ODBC provides routines to allocate and deallocate resources, control connections to the database, execute SQL statements, obtain diagnostic information, control transaction termination, and obtain information about the implementation. ODBC is basically a call-level interface (CLI) for interacting with databases. Basically, the CLI issues SQL statements against the database by using procedure calls instead of direct embedded SQL statements. JDBC relies on drivers. JDBC enables Java to access relational databases. Similar to ODBC, JDBC consists of a set of classes and interfaces that can be used to access relational data. Anyone familiar with application programming and ODBC (or any call-level interface) can get up and running with JDBC quickly. (TCO 3) Designing and implementing a physical database from a logical data model is not just a simple matter of mapping entities to tables, attributes to columns, and relationships to referential constraints. Quite a few other database design issues must be addressed. Effective storage planning is part of physical process design. As a DBA, you do consider the size of table structures. Here, focus on other structures that need to be considered when determining the required storage size. Answer: To prepare storage devices for the database, though, the DBA must determine the size of not only table structures but also index structures. Indexes are covered in more depth in the next section. The DBA will also build some free space into database design for future reorganization and restructuring. Also, if third-party tools are used for data compression, it reduces the amount of storage required. [Show More]

Last updated: 1 year ago

Preview 1 out of 8 pages