Database systems exam 3

Chaper9 Briefly describe five difficult problems for organizations that create and use multi user databases

1) Multiuser databases are complicated to design and develop because they support many overlapping user views. (2) Requirements change over time, and those changes necessitate other changes to the database structure. Such structure changes must be carefully planned and controlled so that a change made for one group does not cause problems for another. (3) When users process a database concurrently, special controls are needed to ensure that the actions of one user do not inappropriately influence the results for another. (4) Processing rights and responsibilities need to be defined and enforced. (5) Effective backup and recovery plans, techniques, and procedures are essential and complicated.

Describe the process of changing a table name. Create a new table with the new name.

1. Alter any constraints, triggers or views that may be affected 2. Copy all data from the old table to the new table 3. Create any needed foreign keys constraints 4. Drop the old table

Summarize the key advantages of stored procedures.

1. Greater security 2. Decreased network traffic 3. Sql can be optimized 4. Code sharing 5. Less work 6. Standardized processing 7. Specialization among developers

What is a stored procedure? How do they differ from triggers?

A stored procedure is a program that performs some common action on database data and that is stored in the database. Unlike triggers, which are attached to a given database or view, stored procedures are attached to the database. They can be executed by any process using the database that has permission to use the procedure.

What is a trigger?

A trigger is a stored program that is attached to a table or view. The trigger code is invoked by the DBMS when an insert, update or delete request is issued on the table or view to which the trigger is attached

Describe four uses for triggers

A. validity checking B. assigning default values C. updating views D. enforcing referential integrity actions

Define an atomic transaction and explain why atomicity is important.

An atomic transaction is a series of actions to be taken on the database so that either all of them are performed successfully or none of them are performed at all, in which case the database remains unchanged. Such a transaction is sometimes called a Logical Unit of Work because it is performed as a unit. ex bank transfer

Explain how the reduction of maximum cardinalities causes data loss.

Anytime you reduce maximum cardinalities you will lose instances of relationships. This causes data loss. For example an N:M relationship between Student and Class was reduced to a 1:N relationship, a Student could register for only one class. The relationship (intersection records) would be lost for all but one record.

What is the advantage of using different types of cursors?

Because cursors require considerable memory, having many cursors open at the same time for, say, a thousand concurrent transactions can consume considerable memory and CPU time. One way to reduce cursor overhead is to define reduced-capability cursors and use them when a full-capability cursor is not needed.

which data type changes are the least risky? which datatype changes are the most risky?

Generally, converting numeric to char or varchar is not risky. Converting date or money or other more specific data type to char or varchar will usually succeed. Converting char or varchar back to date, money, or numeric is risky; and it may or may not be possible.

Explain the trade-off that exists in concurrency control.

High level of concurrency control is easier for the DBMS to administer but causes poor throughput. A low level of concurrency control is more difficult to administer for the DBMS, but provides better throughput.

Chapter 7 What is an SQL view? What purposes do views serve?

In SQL, a view is a virtual table based on the result-set of a SQL statement. A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database. It is used to implement the security mechanism in the SQL Server. 1. Views can hide complexity 2. Views can be used as a security mechanism 3. Views can simplify supporting legacy cod

Chapter 8 Explain why database redesign is necessary

It is not easy to build a database correctly the first time. Even if we can obtain all of the users' requirements, the tasks of building a correct data model and of transforming that data model into a correct database design are difficult . During those stages, some aspects of the database will need to be redesigned. Also inevitably, mistakes are made that must be corrected. When a new information system is installed the user can behave in new ways. As the users behave in those new ways, they will want changes to the information system to accommodate their new behaviors. As those changes are made, the user will have more new behaviors, they will request more changes to the information system, and so forth in a never-ending cycle.

Explain the meaning of the word inappropriately in the phrase "one user's work does not inappropriately influence another user's work."

It means that any overlap of work results is anticipated and is in accordance with the requirements. In some applications, the work of two users is supposed to be completely isolated from each other, such as with patient records processing for two different patients in a hospital. In other applications, two users' work need not be so isolated — someone who wants the latest stock price won't mind if the price is updated as the list is being produced — as long as they know that it may happen.

Explain the difference between an explicit and an implicit lock

Locks placed by the DBMS are called implicit locks; those placed by command are called explicit locks

What are the risks and problems of forward engineering?

Much depends on the nature of the changes to be made and the quality of the forward engineering features of the data modeling product. Some products show the SQL they are generating, some do not. Changes to a database need to be carefully considered before they are implemented.

Explain two different types of test databases that should be used when testing database redesign changes.

One is a small test database that can be used for initial testing. The second is a large test database, which may even be a full copy of the operational database, which is used for secondary testing.

Explain the following statement: "The processing of correlated subqueries is nested, whereas that of regular subqueries is not."

Regular subqueries can be processed from the bottom up. That is, the bottom or inside subquery can be executed then the next subquery can be processed. There is no need to jump back-and-forth between the SELECT statements. Correlated subqueries are nested because the inner query must jump back to the outer query to see if a value exists in both queries.

What is reverse engineering? Why is it important to carefully evaluate the results of reverse engineering?

Reverse engineering is the process of reading a database schema and producing a data model from that schema. You are in essence working backwards, from implementation to design. It is important to carefully evaluate the results of reverse engineering because the model produced will not be a truly logical model. For example, there will be entities created for every intersection table in the database, which should be modeled as many-to-many relationships in the data model

What is lock granularity?

The size of a lock is referred to as the lock granularity. Locks with large granularity are easy for the DBMS to administer, but frequently cause conflicts. Locks with small granularity are difficult to administer (there are many more details for the DBMS to track and check), but conflicts are less common.

Give an example of the lost update problem.

Transaction 1 changes the value of X but it gets overwritten by the update done by transaction 2 on X

Explain two-phased locking.

Transactions are allowed to obtain locks as necessary, but once the first lock is released, no other lock can be obtained. Transactions thus have a growing phase, in which the locks are obtained, and a shrinking phase, in which the locks are released.

What is the relationship between a trigger and a table or view?

Triggers are attached to tables. The trigger is fired when an update is made to the table the trigger is assigned to

Explain the following statement in your own words: "Information systems and organizations create each other." How does this relate to database redesign?

When a new information system is installed, the users can behave in new ways. As the users behave in those new ways, they will want changes to the information system to accommodate their new behaviors. As those changes are made, the users will have more new behaviors, they will request more changes to the information system, and so forth in a never-ending cycle.

Why is it important to analyze the database before implementing database redesign tasks? What can happen if this is not done?

You must analyze the database because you must know the impact of any changes you may make. You must also know where any change may force other changes to occur. If you do not know this, a change you make during redesign may create a problem somewhere else and you may not know about this problem for days or even months after the redesign change is made. This problem may be severe enough that no processing can occur, in essence, closing the business during repairs.

What is a data repository? A passive data repository? An active data repository?

a. A data repository is a collection of metadata about databases, database applications, Web pages, users, and other application components. b. A passive data repository is filled only when someone takes the time to generate the needed metadata and place it in the repository. c. An active data repository is part of the system development process in such a way that metadata is created automatically as the system components are created.

With regard to security, what actions should the DBA take on user accounts and passwords?

a. All accounts within the DBMS should be protected by strong passwords. Such passwords have at least eight characters; and contain upper- and lowercase letters, numbers, and special unprintable key combinations (certain Alt + key combinations).\ example: making a password literally anywhere

Describe the advantages and disadvantages of DBMS-provided and application-provided security.

a. DBMS-provided i. Advantages: Easier to implement, it will be done regardless of the source of data changes and activities, probably more consistent. ii. Disadvantages: May not suffice for particular needs. Works best for vertical security. b. Application-provided i. Advantages: Can be tailored to unique requirements. Can provide horizontal security. ii. Disadvantages: May be done poorly or inconsistently, must be programmed and maintained, may not be as robust

What is deadlock? How can it be avoided? How can it be resolved once it occurs?

a. Deadlock occurs when User1 locks a resource needed by User2 and User2 locks a resource needed by User1. Each is waiting for a resource that the other person has locked. b. One way of avoiding a deadlock is to allow users to issue all lock requests at one time. Users must lock all the resources they want at once. c. When deadlock occurs, the normal solution is to rollback one of the transactions to remove its changes from the database.

Define rollback and rollforward.

a. In a rollback, we undo changes made by erroneous or partially processed transactions by undoing the changes they have made in the database. We apply before images to the changed database data. Then, the valid transactions that were in process at the time of the failure are restarted. b. In a rollforward, the database is restored using the saved data, and all valid transactions since the save are reapplied. We apply after images to the restored database data. Minecraft server example

Explain why a transaction may have many cursors. Also, how is it possible that a transaction may have more than one cursor on a given table?

a. The transaction may need to process several tables at one time. b. A cursor can be opened on two different views of a table (these are SQL views, not application views), in which case there will be two cursors open on the same table for that transaction. Furthermore, there are some transactions in which the logic requires that two cursors process the same table

Describe transaction-level consistency. What disadvantage can exist with it?

a. Transaction-level consistency means that all rows impacted by any of the SQL statements in a transaction are protected from changes during the entire transaction. b. The disadvantage is that this is more restrictive setting, and may result in less throughput.

Explain the difference between optimistic and pessimistic locking.

a. With optimistic locking, the assumption is made that no conflict will occur. Data are read, the transaction is processed, updates are issued, and then a check is made to see if conflict occurred. If not, the transaction is finished. If so, the transaction is repeated until it processes with no conflict. b. With pessimistic locking, the assumption is made that conflict will occur. First, locks are issued, the transaction is processed, and then the locks are freed.

Explain the difference between an exclusive lock and a shared lock.

b. Tl;dr i. Exclusive lock, blocks all access to the item ii. Shared lock, makes the item read-only, so it can be read but not changed

What is a dependency graph? What purpose does it serve?

they are diagrams that consist of nodes and arcs (or lines) that connect those nodes. A dependency graph shows the relationships between tables, triggers, and views. It helps us determine the impact of and those things impacted by a change.