Comp 305/488: Database Administration, Corboy 208, 4:15
Tuesdays
Week 7
Read in Elmasri & Navathe (EN)
- Chapter 5, sections 5.1 (advanced SQL), 5.3, (views), 5.4 (schema
change)
The first week after the break is the midterm.
Look at study guide
A brief word on auto-commit
To commit your SQL updates is to
write the changes to the permanent database; in this sense, it is like save. So far we've assumed that there
is a commit operation performed after every insert or update; this situation
is called auto-commit. Generally,
auto-commit mode is an attribute of your database connection; if auto-commit
is true then a commit is performed
after every SQL statement that potentially alters the database.
The alternative to auto-commit is to execute a group of updates, and then
explicitly invoke the commit
operation at the end, to commit all the updates together. A group of SQL
statements between consecutive commits is then called a transaction;
all the statements of the transaction are committed
together.
Usually, though, we want a stronger assurance: that all the statements of
the transaction either succeed, or none of them do (this is implicit if we
know that commits always succeed,
but this is not the case in the real world). This is known as the atomicity
requirement, the first part of the ACID test (atomicity, consistency,
isolation, durability). The idea is that a transaction should be atomic,
that is, indivisible: the individual queries that make it up should be
executed as a unit.
Instead of a commit, a user may
also issue a rollback, which means
to throw away all the actions back to the previous commit, thus discarding
the transaction.
We'll stick with auto-commit for a while longer, but be aware of two things:
- This is why there is no "save" operation
- auto-commit is not universal; sometimes you need manual control
ER diagrams
Start with Step 5: many-to-many relationships
(min,max) notation, UML
Foreign keys arising out of ER diagrams:
- Any join used to recover an original ER relationship is always
a join of a primary key in one table to a column with a FK constraint
back to that primary key.
Invoice problem
Higher-degree relationships
EER
Oracle data
dictionary
Programming