Databases and Datamining Flashcards
What does an inner join do?
Returns records that have matching values in both tables. This is sometimes called a simple join and is the one created by the use of the WHERE clause to create the join.
What does a left (outer) join do?
Return all records from the left table, and the matched records from the right table (i.e. only those rows from the right table where the joined fields are equal (join condition is met)).
What does a right (outer) join do?
Return all records from the right table, and the matched records from the left table (i.e. only those rows from the left table where the joined fields are equal (join condition is met)).
What does a full (outer) join do?
Return all records when there is a match in either left or right table.
When are stored procedures useful?
When multiple client applications are written in different languages or work on different platforms, but need to perform the same database operations.
When security is paramount.
Stored routines also enable libraries of functions to be present in the database server.
What is transaction management?
It handles all transactions properly in DBMS. Database transactions are the events or activities such as series of data read/write operations on data object(s) stored in database system.
What is concurrency control?
It’s a process to ensure that data is updated correctly and appropriately when multiple transactions are concurrently executed in DBMS.
Concurrency control is a mechanism for ensuring correctness when two or more database transactions that access the same data or data set are executed concurrently with a time overlap.
What is pessimistic concurrency control?
It control delays the transactions if they conflict with other transactions at some time in the future by locking or a time-stamping technique.
What is optimistic concurrency control?
It assumes that conflict is rare and thus allows concurrent transactions to proceed without imposing delays then checks for conflicts only at the end, when a transaction commits.
What are the strengths of pessimistic concurrency control?
Guarantee that all transactions can be executed correctly.
Data is properly consistent by either rolling back to the previous state (Abort operation) or new content (Commit operation) when the transaction conflict is cleared.
Database is relatively stable and reliable.
What are the weaknesses of pessimistic concurrency control?
Transactions are slow due to the delay by locking or time-stamping event.
Runtime is longer. Transaction latency increases significantly.
Throughput or the amount of work (e.g. read/write, update, rollback operations, etc.) is reduced.
What are the strengths of optimistic concurrency control?
Transactions are executed more efficiently.
Data content is relatively safe.
Throughput is much higher.
What are the weaknesses of optimistic concurrency control?
There is a risk of data interference among concurrent transactions since conflict may occur during execution. In this case, data is no longer correct.
Database may have some hidden errors with inconsistent data; even the conflict check is performed at the end of transactions.
Transactions may be in deadlock that causes the system to hang.
