|
Field |
Either an attribute, or a column of attributes |
|
Tuple |
All fields in one table associated with a single item. A "row" of a table. |
|
Record |
See tuple. |
|
Relation |
A set of records with identical structure. A "table". |
|
Metadata |
A description of every table and the associated fields. |
|
Index |
A data structure that speeds access when searching, but does not change the semantic content of the database. |
|
BLOB |
A Binary Large Object. |
|
Key |
A field or set of fields that is unique for each record. For example, social security number is a key for people, and birthday is not. |
|
Projection |
To choose only certain fields of a table. |
|
Selection |
To choose only certain records of a table. |
|
Join |
To produce a new table that is the Cartesian product of the two tables. To "multiply" two tables. |
|
Scheme |
A set of table descriptions that describe the database. Just like metadata but without the field types. |
|
Consistent |
The database describes the real world as intended. |
|
Decomposition |
To break a single table into multiple tables. |
|
Database |
A set of tables |
|
Transaction |
The unit of work in a database. |
|
Commit |
Make the transaction's changes permement |
|
Abort |
Erase the transaction's changes |
|
Lock |
Rights to a data item that preclude other's from that data item |
|
Deadlock |
A set of transactions that will wait forever on each other because of conflicting locks. Normally the database will prevent this from ever happening. |
|
SQL |
The Structured Query Language |
|
Oracle |
The most popular commercial database |
|
DB2 |
The other most popular commercial database |
|
MySQL |
Our database software |
|
Optimizer |
The part of the database that figures out the fastest way to do the query. This can be very complex. |
Consider a database like this below.
Bank Branch |
Customer Number |
Loan Amount |
Bank Address |
Customer name |
|
Marquette |
000-001 |
$10,000 |
166 Front Street |
Randy |
|
Escanaba |
000-002 |
$5,000 |
1120 Lake Shore Drive |
Scott |
|
Marquette |
000-002 |
$7,000 |
188 Back Street |
Fred |
This database has several problems
The Marquette branch has two different addresses, which is normally impossible.
Customer 000-002 has two different names, which is normally impossible.
Perhaps the problem was bad data entry, or a program error. It is possible to make a different database design where these types of errors are IMPOSSIBLE to happen. Instead, by making three tables instead of one, the problems are IMPOSSIBLE. This breaking of one table into three is called decomposition.
Correct decomposition, informally
speaking, is done when there is no repatition of data. Note
that each address and customer name in the scheme below is listed
only once. In the scheme above, branch address and customer
name are listed for every loan the customer has. Also note that
each "entity", like a branch, loan, or person, has its own
table.
|
|
|
There are advantages and disadvantages of each scheme.
One Table Scheme |
Three Table Scheme |
|
Takes more space if there are lots of entries |
Takes more space if there are just a few entries. |
|
Can hold inconsistent data |
Cannot hold inconsistent data |
|
Searching is generally quick. |
Searching can be close because of all the joins needed. |
The ACID Properties are
Atomic -- Either
all operations of the transaction are present, or none are. No
partial transactions.
Consistent -- Execution
of a transaction by itself preserves the consistency of the database.
Isolation -- The system guarantees that for
every two transaction, there is SOME ordering between them.
Durable
-- Changes made to the database persist, even in the face of system
failure.
Indexes are auxillary data structures used to speed access to
comonly searched fields. Each index slows access down during
insert, delete and update operations, but speeds access on select
operations when searching by an indexed field. There is no
special syntax to use an index, it is automatic.