Mis 4330 - Exam 1

Front 1

Anonymous PL/SQL block

Back 1

A PL/SQL block (procedure) that has not been given a name

Front 2

Base table

Back 2

The table on which a view is placed

Front 3

Batch update routine

Back 3

A routine that pools everything into a single batch to update the master table in a single operation

Front 4

Correlated subquery

Back 4

Subquery that executes once for each row in the OUTER query

Front 5

CREATE VIEW

Back 5

SQL command that creates a logical, virtual table based on stored end user tables. Treated as a real table

Front 6

Cross join

Back 6

A join that creates a relational product of 2 tables

Front 7

Cursor

Back 7

Used in procedural SQL, acts as an area of memory to store query output. Held in reserved memory wirhib DBMS server

Front 8

Dynamic SQL

Back 8

SQL statements are not known at run time and is instead generated by the program at run time. Required for ad hoc queries

Front 9

Embedded SQL

Back 9

SQL statements contained within programming languages

Front 10

Explicit cursor

Back 10

A created cursor in procedural SQL that may return 0, 1, or 2+ rows

Front 11

Host language

Back 11

Any language that containes embedded sql statements

Front 12

Implicit cursor

Back 12

A cursor that is automatically created in procedural sql when the output is only one value

Front 13

Inner join

Back 13

A join on rows that match a certain criteria. Can be equality or inequality condition

Front 14

Outer join

Back 14

Relational algebra join of all umatched pairs. Unmatched values are left null

Front 15

Persistent stored module (PSM)

Back 15

Block of code with standard and procedural extensions that are stored and executwd on DBMS server

Front 16

Procedural language SQL (PL/SQL)

Back 16

Allows the use of procedural code and then stored in DB as single callable object by name

Front 17

Row level trigger

Back 17

Trigger executed once for each row. Requires FOR EACH ROW keywords in declaration

Front 18

Set-oriented

Back 18

Related to sets. SQL operators are set oriented as they operate over entire sets of rows/columns

Front 19

Statement-level trigger

Back 19

Only executed once and is default. Is assumed if the FOR EACH ROW keywords are omitted

Front 20

Static SQL

Back 20

Embedded SQL where statemenets do not change while running

Front 21

Stored function

Back 21

A named group of procedural/SQL statements that return a value via the RETURN statement in its program code

Front 22

Stored procedure

Back 22

Named collection of procedural/SQL statements. OR business logic stored on a server

Front 23

Trigger

Back 23

Procedural SQL code that is automatically invoked by the system when data manipulation occurs

Front 24

Union-compatible

Back 24

Two or more tables that share the same column names and have compatible data types

Front 25

Updatable view

Back 25

A view that can update attributes in base tables that are used in the view

Front 26

View

Back 26

A virtual table based on a SELECT query

Front 27

Bottom-up design

Back 27

DB design where you define by attribute (small units) and then group into entities (large units)

Front 28

Boundaries

Back 28

The external limits. Ex: budgets, hardware, software

Front 29

Centralized design

Back 29

Single conceptual design model to match DB requirements. Small number of objects and procedures

Front 30

Clustered table

Back 30

Storage technique that stores rows from twk related tables in adjacent data blocks

Front 31

Cohesivity

Back 31

Strength of relationships between a module's components. Module cohesivity must be high

Front 32

Computer-aided systems engineering (CASE)

Back 32

Tools used to automate part or all of the systems development life cycle

Front 33

Conceptual design

Back 33

Data modeling technique that creates a model of a DB structure that realistically represents real world objects. BOTH software and hardware dependent

Front 34

Database Development

Back 34

Proceaa of DB design and implementation

Front 35

Database fragment

Back 35

Subset of a distributed database. All fragments are treated as single DB even though fragments can be stored at different sites

Front 36

Database life cycle (DBLC)

Back 36

Traces histoey of DB.

1. Initial study

2. design

3. Implementation n loading

4. Test n eval

5. Operation n maintenance

6. Evolution

Hint 36

Contains 6 phases

Front 37

Database role

Back 37

Set of DB privileges assigned as unit or to a group/user

Front 38

Decentralized design

Back 38

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

Front 39

Description of operations

Back 39

Document that provides detailed overview of activities of organizations operating environment

Front 40

Differential backup

Back 40

Level of DB backup where only last modifications are copied

Front 41

Full backup/Database dump

Back 41

Complete copy of entire DB that is periodically updated in seperate memory location. Ensures full recovery after physical disaster or DB integrity failure

Front 42

Information system

Back 42

System that provides for data collection, storage, retrieval, transformation, and management.

Is composed of hardware, DBMS/software, people, n procedures

Front 43

Logical design

Back 43

Translating the conceptual design into the internal model for a selected DB management system (SQL, Oracle, etc)

Front 44

Minimul data rule

Back 44

All data elements required must be defined in model and all data elements defined in model must be used at least once

Front 45

Module

Back 45

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)

Front 46

Module coupling

Back 46

The extent to which modules are independent of one another

Front 47

Physical design

Back 47

Stage of DB design that maps data storage and access characteristics. Both hardware and software dependent

Front 48

Scope

Back 48

The part of a system that defines the extent of the design, according to operational requirements

Front 49

Systems analysis

Back 49

The process that establishes the need for ab info system and its extent

Front 50

Systems development

Back 50

The process of creating an info system

Front 51

Systems development life cycle (SDLC)

Back 51

Cycle that traces the history of info syst. Provides big picture as design and application development can be mapped out n evaluated

Front 52

Top-down design

Back 52

Design by defining main structures (entities) and then smaller units (attributes).

Front 53

Transaction log backup

Back 53

Backup of only the transaction log operations that are not reflected in previous backups

Front 54

Virtualization

Back 54

Creates logical representations of computing resources that are independent of the physical computing resources

Front 55

Atomic transaction property

Back 55

Property that requires all parts to ne treated as a single logical unit and all operations must be completed to produce a consistent DB

Front 56

Binary lock

Back 56

Can be locked or unlocked. When locked no other transactions can use that data item

Front 57

Checkpoint

Back 57

Transaction management where all the updated buffers are written to the disk

Front 58

Consistency

Back 58

DB condition where all data integrity constraints are satisfied. To ensure consistency a DB transaction must begin in a consistent state

Front 59

Database recovery

Back 59

The process of restoring a DB to a previous consistent state

Front 60

Database request

Back 60

A single SQL statement in an application program or a transaction

Front 61

Database level lock

Back 61

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

Front 62

Deadlock

Back 62

Condition in which two or more transactions wait indefinitely for the other to release the locked item

Front 63

Deferred update

Back 63

Condition where transaction operations do not immediately yodate a physical DB

Front 64

Dirty read

Back 64

When a transaction reads data that is not yet committed

Front 65

Diskpage

Back 65

In permanent storage, the equivalent of a disk block. Has fixed size as 4k, 8k, or 16k

Front 66

Durability

Back 66

Indicates the permanence of a DB's consistent state. Transactions will not be lost in a system failure if proper durability

Front 67

Exclusive lock

Back 67

Does not allow other transactions to access the DB.

Front 68

Field level lock

Back 68

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

Front 69

Immediate update

Back 69

Database update that is performed immediately during a transactions execution, even before it reaches commit point

Front 70

Inconsistent retrievals

Back 70

A concurrency control problem that arises when two transactions occur at once, one calculating and one updating data, yeilding erroneous results

Front 71

Isolation

Back 71

When a data item used by one transaction is not available to other transactions until the first one ends

Front 72

Lock

Back 72

Guarantees unique use of a data item in a particular transaction operation. Requires lock before and is then released after

Front 73

Lock granularity

Back 73

The level of lock use. Can occur at the following levels: DB, table, row, and field

Front 74

Lock manager

Back 74

DBMS component that is responsible for assigning and releasing locks

Front 75

Lost updates

Back 75

A concurrency control problem in which data updates are lost during the concurrent execution of transactions

Front 76

Monotonicity

Back 76

A quality that ensures that timestamp values always increase

Front 77

Mutual exclusive rule

Back 77

Only one transaction can own an exclusive lock on an object at one time

Front 78

Nonrepeatable read

Back 78

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

Front 79

Optimistic approach

Back 79

A concurrency control technique based on the assumption that most DB operations do not conflict

Front 80

Page level lock

Back 80

DB locks an entire diskpage, or section. Can contain one or more rows/tables

Front 81

Pessimistic locking

Back 81

The use of locks based on the assumption that conflict between transactions is likely

Front 82

Phantom read

Back 82

When running the same query twice yields additional rows that satisfy the query

Front 83

Read committed

Back 83

Isolation level that allows transactions to read only committed data. Default mode

Front 84

Read uncommitted

Back 84

Allows transactions to read uncommitted data and nonrepeatable and phantom reads. Least restrictive level