344

Front 1

Weak entity Types

Back 1

Do not have key attributes of their own. Identified by being related to instances of other entities. Maintain a foreign key to the identifying entity. Total participation.

Front 2

Domains

Back 2

set of atomic values associated with entity type

Front 3

Composite attribute

Back 3

Can be further divided into subparts

Front 4

Multi valued attribute

Back 4

Can have more than one value at a time for an attribute

Front 5

Superkey

Back 5

An attribute or set of attributes which can be used to identify a distinct tuple in a relation.

Front 6

Key

Back 6

A minimal superkey, a superkey where removing any of the attributes leaves it not a superkey.

Front 7

Candidate key

Back 7

One of the minimal superkeys which can be used to uniquely identify any distinct tuple in a relation.

Front 8

Primary key

Back 8

One of the candidate keys is designated to be the primary key.

Front 9

Foreign key

Back 9

A set of attributes in a relation which can be used to refer to tuples in another relation. This is due to the domains of the foreign key and the primary key being the same.

Front 10

First normal form

Back 10

All attribute values are atomic and single values. I.e. no multivalued or composite attributes. Designed to remove repeating groups.

Front 11

2NF

Back 11

1NF and every non-key attribute is fully functionally dependent on the primary key. Designed to remove partial dependencies.

Front 12

3NF

Back 12

2NF and every non-key attribute is non-transitively dependent on the primary key.

Front 13

PL/SQL stored subprogram

Back 13

Named unit (block) of code stored permanently in the database. Compiled seperately.

Front 14

PL/SQL Anonymous Block

Back 14

Program unit that has no name, is not stored in the database, and is compiled each time it is executed.

Front 15

PL/SQL Stored subprogram procedure

Back 15

Designed to change data inside of a database when executed. Sometimes has a return value.

Front 16

PL/SQL Stored subprogram Function

Back 16

Designed to always return a value. Does not change data, more of a retriever. Called in a statement.

Front 17

Trigger

Back 17

A stored subprogram associated with a table, automatically invoked as a reaction to specified changes to a database. Never returns a value.

Front 18

Some trigger uses

Back 18

Enforce complex business rules. Enforcing some types of referential integrity. Auditing purposes. Generating values for derived attributes. Creating replica tables or backups.

Front 19

Transaction

Back 19

A sequence of operations on a database which is either completely executed or not at all.

Front 20

Transaction rollback

Back 20

When errors occur during a transaction, any changes that have been made as a part of the transaction are undone, as the database may be in an inconsistent state.

Front 21

Transaction commit

Back 21

When a transaction executes without any errors, the database should be in a stable state after the transaction work is successful. Changes made are commited (saved).

Front 22

ACID

Back 22

The four properties transactions must have to deal with failures and concurrency problems.

Front 23

Atomicity

Back 23

The first property in ACID. Requires that each transaction is all or nothing. Execute in an atomic fashion.

Front 24

Isolation

Back 24

3rd property of ACID. Logical isolation between transactions. A transactions updates are not visible to other transactions until it either commits or rollbacks. This avoids the dirty read problem.

Front 25

Durability

Back 25

4th property of ACID. Once a change has been made to a database following a transaction, this commited change must persist regardless of failures until it is changed again.

Front 26

Consistency

Back 26

3rd property of ACID. A transaction performed on a consistent database must result in a still consistent database.

Front 27

Lost update problem

Back 27

Concurrency problem where the update made by one transaction is overriden by another transaction.

Front 28

Temporary update problem (dirty read)

Back 28

Concurrency problem where one transaction reads a value before the value has been committed by another transaction mid way through a process. The first transaction will become invalid and fail.

Front 29

Incorrect Summary problem

Back 29

Concurrency problem where a transaction reads some value to perform an operation e.g

To find the average, and another transaction updates the value during the transaction. This value will now be incorrect to find the average.

Front 30

Locking algorithm

Back 30

If transaction A holds a write lock on object p, then a request from other transactions for either a read or write lock on p will be denied. If transaction A holds a read lock on p, then only write locks will be denied to other transactions.

Front 31

Two phase locking

Back 31

Growing phase: locks are obtained, not released

Shrinking phase: locks are released, no new locks are acquired.

Ensures seriability.

Front 32

Variations of 2-phase locking

Back 32

Conservative: locks all items before the transaction begins.

Strict 2PL (most common): does not release any write locks until commit or rollback.

Front 33

View

Back 33

A Virtual table derived from other base tables or previously defined views with a select statement.

Doesn't necessarily exist in physical forms.

Provides a security and access control mechanism.

Allows easier access to elements of a table that may be accessed more frequently.

Front 34

View Materialization

Back 34

Creates a physical temporary view table when a view is first queried.

DBMS Incrementally updates tuples as they are manipulated.

If the view is not queried for a certain period of time, the system may remove the physical table and recompute it from scratch when future queries reference the view.

Front 35

Discretionary Database security

Back 35

A given user may have different access rights to different objects at the relation level. Access control for the relations themselves. Different users may have different rights on the same object. Discretionary is flexible, give or not give access. Each table has an owner with all privileges who can pass on privileges to other users.

Front 36

Mandatory Database security

Back 36

Each object is tagged with a classification level. Users are given clearance levels. Rigid system similar to a military or government.

Top secret > Secret > confidential > none.

Front 37

Bell lapadula Model mandatory access control

Back 37

Simple Security Property:

Users cannot read any data above their level, only at the same level or below.

Star Property:

Cannot write any data to the lower layer.

This is so they cannot lower the clearance of data to the lower levels of security.

Front 38

Indexes

Back 38

Additional files on a disk which provide alternative ways to access records inside a database without affecting the physical placement of records on the disk.

The index itself consists of a key field together with a list of pointers to disk blocks.

Front 39

Single-level ordered Index - Primary index

Back 39

For an ordered file whose ordering field is a key. A primary index is an ordered file whose records are of fixed length with two fields.

- Data from the ordering key field

- Pointer to the data block

A primary index is a Sparse (non-dense) index; meaning it only has index entries for some of the search values.

Front 40

Single-level ordered Index - Clustered index

Back 40

For an ordered file whose ordering field is not a key.

The clustering index used has the same value as the clustering field it is indexing.

Sparse Index.

Front 41

Single level ordered index - Secondary Indexes

Back 41

A secondary means of accessing a file for which a primary access already exists.

For out of order files or for attributes which are not the ordering field.

Key field is a unique value.

Dense Index.

Front 42

System Log File

Back 42

File maintained on disk of all changes to a database.

Front 43

System Log Checkpoints

Back 43

The point at which a record is written into the log when the system writes all modified buffers to the database on the disk.

-Suspends execution of transactions.

-'Force-writes' the contents of the buffers to the disk.

-Writes a Checkpoints record to the log and 'force-writes' the log to the disk

-Resumes transactions

Front 44

Steal/No Steal

Back 44

In the event of a failure a Steal recovery process allows writing an updated buffer to disk before the transaction commits.

No steal means that an UNDO action will never be needed during recovery.

Front 45

Force/No Force

Back 45

The force mechanism means that all pages updated by a transaction are immediately written to disk when the transaction commits.

Front 46

Steal/No-Force

Back 46

Updated buffers are written to the disk before the transaction commits.

Front 47

Finding the candidate keys

Back 47

Find the set of attributes not on the RHS of any FD. Every candidate key must contain these attributes.