Upgrade to remove ads
Chapters 7, 8, 9, 10, 12, 15
Terms in this set (82)
A type of subquery in which an element in the subquery refers to an element in the containing query. A subquery that requires nested processing.
the syntax used to return all rows from the left table or first table, even if there are no matches in the right table.
the process of reading and producing a data model from a database schema
A network of nodes and lines that represents the logical dependencies among tables, views triggers, stored procedures, indexes, and other database constructs.
changes table structure, properties, or constraints after it has been created
deletes a row from a specific table.
the process of applying data model changes to an existing database
A unique, system-supplied identifier used as the primary key of a relation. It is created when a row is created, it never changes, and is destroyed when the row is deleted.
Referential Integrity Actions
Rules that specify the activities that must take place when insert, update, or delete actions occur on either the parent or child entities in a relationship.
a virtual table that is constructed from other tables or views
a stored program that is executed by the DBMS whenever a specified event occurs on a specified table or view. The 3 types are BEFORE, INSTEAD OF, and AFTER.
a program that is stored within the database and is compiled when used. Stored procedures can receive input parameters and they can return results. Stored procedures can be called from programs written in standard languages, scripting languages, and SQL command prompts.
define certain properties that data in a database must comply with. They can apply to a column, a whole table, more than one table or an entire schema. ex. - NULL, NOT NULL, UNIQUE, Primary Key, CHECK, Foreign Key
a database that can allow simultaneous record entry by more than one person.
Ensure one user's work does not interfere with another's
Going back to the past to recover data
Logical Unit of Work
1- A group of actions that is performed on the database automatically. Either all actions are committed or none. 2- In general, the record of an event in the business world. Same as Transaction.
A group of logically related database operations that is performed as a unit. Either all of the operations are performed or none of them are.
Lost Update Problem
An error condition in which one user's data changes are overwritten by another user's data changes. Same as Concurrent Update Problem.
Inconsistent Read Problem
In a transaction, a series of reads of a set of rows in which some of the rows have been updated by a second transaction and some of the rows have not been updated by that second transaction. Can be prevented by two-phase locking and other strategies.
The process of allocating a database resource to a particular transaction in a concurrent processing system. The size of the resource locked is known as the lock granularity. With an exclusive lock, no other transaction may read or write the resource.
but no other transaction may write it.
A lock that is placed automatically by the DBMS
A lock requested by command from an application program.
The size of a locked data element. The lock of a column value of a particular row is a small granularity lock, and the lock of an entire table is a large granularity lock.
A lock on a data resource such that no other transaction can either read or update that resource.
The procedure by which locks are obtained and released in two phases. During the growing phase, the locks are obtained; during the shrinking phase, the locks are released. After a lock is released, no other lock will be granted that transaction. Such a procedure ensures consistency.
A condition that can occur during concurrent processing in which each of two (or more) transactions is waiting to access data that the other transaction has locked.
A locking strategy that assumes no conflict will occur, processes a transaction and then checks to determine whether conflict did occur. If conflict did occur, no changes are made to the database and the transaction is repeated.
A locking strategy that prevents conflict by locking data resources, processing the transaction, and then unlocking the data resources.
Reading data that has been changed but not yet committed to the database. Such changes may later be rolled back and removed from the database.
The situation that occurs when a transaction reads data it has previously read and finds modifications or deletions caused by a committed transaction.
The situation that occurs when a transaction reads data it has previously read and finds new rows that were inserted by a committed transaction.
A declaration on a cursor that determines how the DBMS places implicit locks. Four types are forward only, snapshot, keyset, and dynamic.
Recovering a database by restoring the last full backup, and then using data stored in a transaction log to modify the database as needed by adding transactions.
Recovering a database by restoring the last full backup, and then using data stored in a transaction log to modify the database as needed by removing uncommitted transactions.
A record of a database entity before a change. Used in recovery to perform rollback.
A record of a database entity after a change. Used in recovery to perform rollforwards.
The point of synchronization between a database and a transaction log. All buffers are force-written to external storage.
The person or group responsible for establishing policies and procedures to control and protect a database. The database administrator works within guidelines set by data administration to control the database structure, manage data changes, and maintain DBMS programs.
The enterprise-wide function that concerns the effective use and control of the organization's data assets. Data administration may be handled by an individual, but it is usually handled by a group. Specific functions include setting data standards and policies and providing a forum for conflict resolution.
Create Table (SQL)
The SQL command used to
create a database table.
Used to insert new records into an existing table.
Check constraints (SQL)
In SQL, a constraint that specifies what data values are allowed in a particular column.
A virtual table based on the VIEW statement
Cursor Variable (SQL)
creates a pointer. a place holder
Triggers an event based on some criteria
Before Trigger (SQL)
Trigger set to happen before an event.
After Trigger (SQL)
Trigger set to happen after an event.
Instead of Trigger (SQL)
A trigger that happens in place of another action.
ReDo File (SQL)
In Oracle Database, backups of rollback segments used for backup and recovery. May be online or offline.
OnLine Analytical Processing. Allows for arithmetic operations in SQL.
RFM Analysis (SQL)
A type of reporting system in which customers are classified according to how recently(R) how frequently(F) and how much money(M) they spend on their orders.
Business Intelligence Systems
Information Systems that assist managers and other professionals in the analysis of current and past activities and in the prediction of future events. Two major categories of BI systems are reporting systems and data mining systems.
Reverse-Engineered Data Model
The structure that results from reverse engineering. It is not really a data model, because it includes physical structures such as intersection tables. It is, instead, a thing unto itself; midway between a data model and a relational database design.
? A conceptual view or a conceptual map of the data entities and a map of the relationships
An indicator of the current position in a pseudofile for an SQL SELECT that has been embedded in a program; it shows the identity of the current row.
T/F: An analyst may redesign a database to improve application performance.
T/F: The EXISTS clause is a specialized case of a correlated subquery.
T/F: A reverse-engineered data model is the same as a conceptual view.
T/F: A disadvantage of stored procedures is that they increase network traffic.
T/F: It is not possible for a DBMS to solve a deadly embrace.
A lock placed on a table through a program command is:
A locking strategy that prevents conflict by locking data resources, processing the transaction, and then unlocking the data resources
T/F: The data administration function is more technical than the database administrator function.
T/F: The cursor type determines how an application can navigate a recordset and what changes are visible.
T/F: When creating a new database, Oracle automatically creates default files for transaction logs.
T/F: In Oracle, it is possible to have a constraint check to see if a value is in a table.
T/F: With Oracle, you can change a column's data type if the column contains only null values.
The Oracle command to take away a privilege from a user is:
T/F: RFM analysis is a way of analyzing and ranking customers according to their purchase patterns.
T/F: Online analytic processing (OLAP) is a type of data mining.
T/F: The marketing function of data administration is concerned with selling data to the users.
T/F: A data warehouse is like a smaller version of a data mart.
used to take existing permissions away from users and groups.
used to assign permissions to users and groups.
Procedural Language/SQL (PL/SQL)
An Oracle-supplied language that augments SQL with programming language structures such as while loops, if-then-else blocks, and other such constructs. PL/SQL is used to create stored procedures and triggers.
used to avoid the problem of too many values being returned.
Systems Development Life Cycle (SDLC)
a process used by a systems analyst to develop an information system.
First Normal Form Requirements
Rows contain data about an entity.
Columns contain data about attributes of the entities.
All entries in a column are of the same kind.
Each column has a unique name.
Cells of the table hold a single value.
The order of the columns is unimportant.
The order of the rows is unimportant.
No two rows may be identical.
Second Normal Form Requirements
It is in 1NF and all non-key attributes are determined by the entire primary key.
Third Normal Form Requirements
It is in 2NF and there are no non-key attributes determined by another non-key attribute. No transitive dependencies.
Boyce-Codd Normal Form Requirements
If it is in 3NF and every determinant is a candidate key.
THIS SET IS OFTEN IN FOLDERS WITH...
IE 224 Chapter 5 Questions
LU CSIS 325 - CH. 1-3 & 5-7 (Midterm)
MySQL PRIMARY KEY
YOU MIGHT ALSO LIKE...
C192 Data management for programmers
Chapter 4- Sargent ACCT 3303
AIS Chapter 4 Terms
AIS Ch 4
OTHER SETS BY THIS CREATOR
Operations Management Final
Finance Final Exam