hitting the database is expensive. follow an optimistic approach. assume sql operations will succeed. handle errors with exception handling. use merge if available.
5 of 26
merge
a combo of insert and update. update if row exists, insert otherwise.
An interaction between two or more entities. Individuals, organisations. Usually follow explicit or implied norms (contracts etc). Usualy involve an exchange of resources (money/goods/info) Considered a single unit (indivisiable)
8 of 26
Transactions from database perspective
Group of database operations considered a single logical unit. eg transfer between bank accounts - read and update on account. Customer sale: read customer, insert into sale_head and sale_line, update prodcut
9 of 26
Transactions are supposed to be atomic
ALL operations must succeed or ENTIRE transaction rolled back. Succeed = no sigificant failures or errors. ll operations should be related in some way
10 of 26
What does consistent mean in regards to databases?
ALL integrity rules are satisfied. Database may be inconsistent DURING transaction - eg cycles of foreign keys
11 of 26
What does it mean for transactions to be isolated?
Concurrent transactions shouldnt intefere with each other. Ideally behave as if other transactions dont exist.
12 of 26
What can happen with improper isolation?
One person updating an address, another person opens and updates the same field which changes it
13 of 26
What does a durable transaction mean?
COMMITTED transactions are permanent (persistent). Committed transactions survive crashes - uncommitted dont. Auto rollback of uncomitd changes
14 of 26
Commit and Rollback?
Changes are usualy made to live data. "Optimistic approach". Commit = makes database changes permanent. Rollback = UNDO, removes ALL changes since transaction start.
15 of 26
Transaction design
Should mirror real world transactions as closely as possible. Keep short. NO USER INTERACTION while transaction is active.
16 of 26
Transactions in JDBC
The default is auto-commit. Each statement is a separate transaction. If your transaction requires multiple statements, then disable auto commit and manually manage commit and rollback.
17 of 26
Coditional Computation using CASE
Select * ** case when Mark between 0 and 39 then 'E' when Mark between ....
18 of 26
Set Operators in SQL
Can combine rows of two tables vertically if the header and datatypes are the same. Union = put all the rows in one table (removes any duplicates). Intersect = Rows that appear in both tables (removes all others) Difference = only distinct tupples
19 of 26
What is validation?
Checks the entered values are plausible. AUtomated by check constraints.
20 of 26
Verification?
Check that the correct value was entered. - double checking input. A manual process.
21 of 26
Example of validation:
Requiring a new password to be 10 characters. Verification = double entry of new password to ensure consistency. Validation = identifying that a mark of 223 out of 25 is impossible.
22 of 26
Types of constraints
Column (in line) and Table
23 of 26
Sequences
Generate integer values. H2 usings auto_increment = eg is the customer number in the shopping system.
24 of 26
Triggers?
Specific operations on table automatically trigger other operations.
25 of 26
Stored procedures?
Programming code stored within the database. This reduces database round trips. Encapsulates database code in the database for re-use
26 of 26
Other cards in this set
Card 2
Front
Database
Back
collection of data and metadata that is managed by a DBMS. Metadata is data about data
Comments
No comments have yet been made