Use LEFT and RIGHT arrow keys to navigate between flashcards;
Use UP and DOWN arrow keys to flip the card;
H to show hint;
A reads text to speech;
84 Cards in this Set
- Front
- Back
- 3rd side (hint)
Anonymous PL/SQL block |
A PL/SQL block (procedure) that has not been given a name |
|
|
Base table |
The table on which a view is placed |
|
|
Batch update routine |
A routine that pools everything into a single batch to update the master table in a single operation |
|
|
Correlated subquery |
Subquery that executes once for each row in the OUTER query |
|
|
CREATE VIEW |
SQL command that creates a logical, virtual table based on stored end user tables. Treated as a real table |
|
|
Cross join |
A join that creates a relational product of 2 tables |
|
|
Cursor |
Used in procedural SQL, acts as an area of memory to store query output. Held in reserved memory wirhib DBMS server |
|
|
Dynamic SQL |
SQL statements are not known at run time and is instead generated by the program at run time. Required for ad hoc queries |
|
|
Embedded SQL |
SQL statements contained within programming languages |
|
|
Explicit cursor |
A created cursor in procedural SQL that may return 0, 1, or 2+ rows |
|
|
Host language |
Any language that containes embedded sql statements |
|
|
Implicit cursor |
A cursor that is automatically created in procedural sql when the output is only one value |
|
|
Inner join |
A join on rows that match a certain criteria. Can be equality or inequality condition |
|
|
Outer join |
Relational algebra join of all umatched pairs. Unmatched values are left null |
|
|
Persistent stored module (PSM) |
Block of code with standard and procedural extensions that are stored and executwd on DBMS server |
|
|
Procedural language SQL (PL/SQL) |
Allows the use of procedural code and then stored in DB as single callable object by name |
|
|
Row level trigger |
Trigger executed once for each row. Requires FOR EACH ROW keywords in declaration |
|
|
Set-oriented |
Related to sets. SQL operators are set oriented as they operate over entire sets of rows/columns |
|
|
Statement-level trigger |
Only executed once and is default. Is assumed if the FOR EACH ROW keywords are omitted |
|
|
Static SQL |
Embedded SQL where statemenets do not change while running |
|
|
Stored function |
A named group of procedural/SQL statements that return a value via the RETURN statement in its program code |
|
|
Stored procedure |
Named collection of procedural/SQL statements. OR business logic stored on a server |
|
|
Trigger |
Procedural SQL code that is automatically invoked by the system when data manipulation occurs |
|
|
Union-compatible |
Two or more tables that share the same column names and have compatible data types |
|
|
Updatable view |
A view that can update attributes in base tables that are used in the view |
|
|
View |
A virtual table based on a SELECT query |
|
|
Bottom-up design |
DB design where you define by attribute (small units) and then group into entities (large units) |
|
|
Boundaries |
The external limits. Ex: budgets, hardware, software |
|
|
Centralized design |
Single conceptual design model to match DB requirements. Small number of objects and procedures |
|
|
Clustered table |
Storage technique that stores rows from twk related tables in adjacent data blocks |
|
|
Cohesivity |
Strength of relationships between a module's components. Module cohesivity must be high |
|
|
Computer-aided systems engineering (CASE) |
Tools used to automate part or all of the systems development life cycle |
|
|
Conceptual design |
Data modeling technique that creates a model of a DB structure that realistically represents real world objects. BOTH software and hardware dependent |
|
|
Database Development |
Proceaa of DB design and implementation |
|
|
Database fragment |
Subset of a distributed database. All fragments are treated as single DB even though fragments can be stored at different sites |
|
|
Database life cycle (DBLC) |
Traces histoey of DB. 1. Initial study 2. design 3. Implementation n loading 4. Test n eval 5. Operation n maintenance 6. Evolution |
Contains 6 phases |
|
Database role |
Set of DB privileges assigned as unit or to a group/user |
|
|
Decentralized design |
Conceptual design is used to modem subsets of an organization's DB requirements. After verification subsets are then aggregated into a complete design. For complex systems with large number of objects/procedures |
|
|
Description of operations |
Document that provides detailed overview of activities of organizations operating environment |
|
|
Differential backup |
Level of DB backup where only last modifications are copied |
|
|
Full backup/Database dump |
Complete copy of entire DB that is periodically updated in seperate memory location. Ensures full recovery after physical disaster or DB integrity failure |
|
|
Information system |
System that provides for data collection, storage, retrieval, transformation, and management. Is composed of hardware, DBMS/software, people, n procedures |
|
|
Logical design |
Translating the conceptual design into the internal model for a selected DB management system (SQL, Oracle, etc) |
|
|
Minimul data rule |
All data elements required must be defined in model and all data elements defined in model must be used at least once |
|
|
Module |
Design element implemented aa autonomous unit and can be linked to produce a system. OR a info syst component that handles specific function (inventory, payroll, etc) |
|
|
Module coupling |
The extent to which modules are independent of one another |
|
|
Physical design |
Stage of DB design that maps data storage and access characteristics. Both hardware and software dependent |
|
|
Scope |
The part of a system that defines the extent of the design, according to operational requirements |
|
|
Systems analysis |
The process that establishes the need for ab info system and its extent |
|
|
Systems development |
The process of creating an info system |
|
|
Systems development life cycle (SDLC) |
Cycle that traces the history of info syst. Provides big picture as design and application development can be mapped out n evaluated |
|
|
Top-down design |
Design by defining main structures (entities) and then smaller units (attributes). |
|
|
Transaction log backup |
Backup of only the transaction log operations that are not reflected in previous backups |
|
|
Virtualization |
Creates logical representations of computing resources that are independent of the physical computing resources |
|
|
Atomic transaction property |
Property that requires all parts to ne treated as a single logical unit and all operations must be completed to produce a consistent DB |
|
|
Binary lock |
Can be locked or unlocked. When locked no other transactions can use that data item |
|
|
Checkpoint |
Transaction management where all the updated buffers are written to the disk |
|
|
Consistency |
DB condition where all data integrity constraints are satisfied. To ensure consistency a DB transaction must begin in a consistent state |
|
|
Database recovery |
The process of restoring a DB to a previous consistent state |
|
|
Database request |
A single SQL statement in an application program or a transaction |
|
|
Database level lock |
Lock that restricts DB access to the owner of the lock and only allows one user access. Works for batch processes but not online multi user DBMSs |
|
|
Deadlock |
Condition in which two or more transactions wait indefinitely for the other to release the locked item |
|
|
Deferred update |
Condition where transaction operations do not immediately yodate a physical DB |
|
|
Dirty read |
When a transaction reads data that is not yet committed |
|
|
Diskpage |
In permanent storage, the equivalent of a disk block. Has fixed size as 4k, 8k, or 16k |
|
|
Durability |
Indicates the permanence of a DB's consistent state. Transactions will not be lost in a system failure if proper durability |
|
|
Exclusive lock |
Does not allow other transactions to access the DB. |
|
|
Field level lock |
A lock that allows concurrent transactions to access same row as long as they use different rows. Yields most flexible multiuser data access but a high level of comouter overhead |
|
|
Immediate update |
Database update that is performed immediately during a transactions execution, even before it reaches commit point |
|
|
Inconsistent retrievals |
A concurrency control problem that arises when two transactions occur at once, one calculating and one updating data, yeilding erroneous results |
|
|
Isolation |
When a data item used by one transaction is not available to other transactions until the first one ends |
|
|
Lock |
Guarantees unique use of a data item in a particular transaction operation. Requires lock before and is then released after |
|
|
Lock granularity |
The level of lock use. Can occur at the following levels: DB, table, row, and field |
|
|
Lock manager |
DBMS component that is responsible for assigning and releasing locks |
|
|
Lost updates |
A concurrency control problem in which data updates are lost during the concurrent execution of transactions |
|
|
Monotonicity |
A quality that ensures that timestamp values always increase |
|
|
Mutual exclusive rule |
Only one transaction can own an exclusive lock on an object at one time |
|
|
Nonrepeatable read |
When a transaction reads a given row at a time t1, then reads the same row again t2, yeilding different results cuz origional row may have been updated/deleted |
|
|
Optimistic approach |
A concurrency control technique based on the assumption that most DB operations do not conflict |
|
|
Page level lock |
DB locks an entire diskpage, or section. Can contain one or more rows/tables |
|
|
Pessimistic locking |
The use of locks based on the assumption that conflict between transactions is likely |
|
|
Phantom read |
When running the same query twice yields additional rows that satisfy the query |
|
|
Read committed |
Isolation level that allows transactions to read only committed data. Default mode |
|
|
Read uncommitted |
Allows transactions to read uncommitted data and nonrepeatable and phantom reads. Least restrictive level |
|