Database Flashcards
(146 cards)
1
Q
is the process of determining the data storage organization and data access
characteristics of the database to ensure its integrity, security, and performance. This is
the last stage in the database design process.
A
Physical Design
2
Q
must perform such basic
functions with minimal user effort, and its command structure and syntax must be easy to learn
A
database language
3
Q
It is the component of a database management system that attempts to select the
most efficient way to execute a query.
A
Query Optimizer
4
Q
defines transaction management based on transaction isolation levels.
A
ANSI SQL standard (1992)
5
Q
allows you to create database and table structures, perform basic data management chores (add, delete, and modify), and perform complex queries designed to transform the raw data into useful information.
A
Database language
6
Q
A — statement is reached, in which case all changes are aborted and the
database is rolled back to its previous consistent state.
A
ROLLBACK
7
Q
the changes are permanently applied to the database.
A
write phase
8
Q
The scheduler’s main job is to create a —- of a transaction’s
operations, in which the interleaved execution of the transactions (T1, T2, T3, etc.) yields the same results as if the transactions were executed in serial order (one after
another).
A
serializable schedule
9
Q
It occur when a transaction accesses data before and after one or more other transactions
finish working with such data.
A
Inconsistent Retrieval
10
Q
Refers to an attribute or field that serves as a unique identifier
for a particular record within a relation.
A
Primary Key
11
Q
A process that uses data modelling techniques to create a model of a database structure that represents real world objects as realistically as possible. The design is both software- and hardware-independent
A
Conceptual Design
12
Q
(several copies of the transaction log) ensure that a physical disk failure will not impair the DBMS’s ability to recover data
A
Redundant transaction logs
13
Q
forces transactions to
read only committed data. This is the default mode of operation for most databases
(including Oracle and SQL Server). At this level, the database will use exclusive locks on data, causing other transactions to wait until the original transaction commits.
A
Read Committed
14
Q
Symbols in Crowfoot’s Notation:
A
Entities (Tables)
Relationships
Cardinality (Multiplicity)
15
Q
A —- statement is reached, in which case all changes are permanently
recorded within the database. The —- statement automatically ends the
SQL transaction
A
COMMIT
16
Q
must be portable; that is, it must conform to some basic standard, so a person does not have to relearn the basics when moving from one RDBMS to another
A
Structured Query Language (SQL)
17
Q
refers to a set of activities and procedures designed to reduce the response time of the database system—that is, to ensure that an end-user query is processed by the DBMS in the minimum amount of time
A
Database performance tuning
18
Q
the over-all design of a database.
A
Schema
19
Q
Evolution of Data Dodels
A
- HIERARCHICAL MODEL
- NETWORK MODEL
- RELATIONAL MODEL
- ENTITY RELATIONSHIP MODEL
- OBJECT-ORIENTED MODEL
- EXTENSIBLE MARKUP LANGUAGE
- Emerging Data Model: BIG DATA
- Emerging Data Model: NoSQL
20
Q
is the process of database design and implementation
A
Database Development
21
Q
It occurs when two transactions, T1 and T2, are executed concurrently and the first transaction (T1) is rolled back after the second transaction (T2) has already accessed
the uncommitted data—thus violating the isolation property of transactions.
A
Uncommitted Data
22
Q
constitutes the building blocks of information.
A
Data
23
Q
The use of locks based on the assumption that conflict between transactions is likely is
usually referred to as —–.
A
pessimistic locking
24
Q
All lock information is handled by a —-, which is responsible for assigning
and policing the locks used by the transactions.
A
lock manager
25
The objective of ---- is to ensure the serializability of transactions in a
multiuser database environment.
Concurrency Control
26
is the process of creating an information system
System Development
27
Introduced by Peter Chen in 1976
Entity Relationship Model
28
is the process that establishes the need for an information system and its extent.
System Analysis
29
ensures that time stamp values always increase
monotonicity
30
A sequence of database requests that accesses the database.
Transaction
31
ensures that once transaction changes are done and committed, they
cannot be undone or lost, even in the event of a system failure.
Durability
32
is a cycle that traces the history of an information system. It provides the big picture within which database design and application development can be mapped out and evaluated.
System Development Life Cycle (SDLC)
33
return rows when there is at least one match in both tables.
INNER Join
34
Types of Read Operation con’t
Read Uncommitted
Read Committed
The Repeatable Read
35
are one of the most common techniques used in concurrency
control because they facilitate the isolation of data items used in concurrently executing transactions.
Locking methods
36
approach to scheduling concurrent transactions assigns a global, unique time stamp to each transaction. The time stamp value produces an explicit order in which transactions are submitted to the DBMS.
time stamping
37
---- is important because the simultaneous execution of transactions
over a shared database can create several data integrity and consistency problems.
Concurrency control
38
Types of Relationships
- One to one
- One to many
- Many to Many
39
guarantees exclusive use of a data item to a current transaction. In other words, transaction T2 does not have access to a data item that is currently being used by transaction T1.
lock
40
may be a subset of rows or columns from one or multiple tables
database fragment
41
are described based on what data other transactions can see (read) during execution
isolation levels
42
On the server side, the DBMS environment must be properly configured to respond to
clients’ requests in the fastest way possible, while making optimum use of existing
resources. The activities required to achieve that goal are commonly referred to as
DBMS performance tuning
43
File Base Limitations
- Data Redundancy
- Data Integrity Issues
- Limited Data Access
- Scalability Problems
- Difficulties in Data Analysis
- Lack of Standardization
44
is an information system component that handles a specific business function, such as inventory, orders, or payroll.
Module
45
Symbols in Crowfoot’s Notation:
Uses crow’s foot symbols to define relationships
Cardinality (Multiplicity)
46
The first step in conceptual design is to discover the characteristics of the data elements
DATA ANALYSIS AND REQUIREMENT
47
are temporary storage areas in primary memory used to speed up disk operations
Database buffers
48
refer to the degree to which transaction data is “protected or isolated” from other concurrent transactions
Transaction isolation levels
49
Yields a graphical representation of entities and their relationships in database structure
Entity Relationship Model
50
The program is abnormally terminated, in which case the database changes are
aborted and the database is rolled back to its previous consistent state. This action is equivalent to ---.
ROLLBACK
51
means that the data used during the execution of a transaction cannot
be used by a second transaction until the first one is completed
Isolation
52
Symbols in Crowfoot’s Notation:
Represented as rectangles
Entities (Tables)
53
are used to ensure accuracy and
consistency of data in a relational database.
Integrity Constraint
54
is used to combine records from two or more tables in a database, based on a common field between them other keywords are combined with the SELECT statement
SQL Joins
55
isolation level ensures that queries return consistent results. This type of isolation level uses shared locks to ensure other transactions do not update a row after
the original query reads.
The Repeatable Read
56
will read uncommitted data from other transactions. At this isolation level, the database does not place any locks on the data, which increases transaction performance but at the cost of data consistency.
Read Uncommitted
57
A DBMS feature that coordinates the simultaneous execution of transactions in a multiprocessing database system while preserving data integrity
Concurrency Control
58
Transaction support is provided by two SQL statements
COMMIT and ROLLBACK
59
ensures that no equal time stamp values can
exist
Uniqueness
60
Symbols in Crowfoot’s Notation:
Shown as lines connecting
entities
Relationships
61
A system that provides for data collection, storage, and retrieval; facilitates the transformation of data into information; and manages both data and information.
Information System
62
Recovery techniques are based on the -----: all portions of the transaction must be treated as a single, logical unit of work in which all operations are applied and completed to produce a consistent database.
atomic transaction property
63
are used to control access to
data objects, such as giving a one user permission to only view the certain table, and giving another use permission to change the data in the given table.
Data Control Language (DCL)
64
ACID properties
Atomicity
Consistency
Isolation
Durability
65
refers to an organization of components that define and regulate the collection, storage,
management, and use of data within a database environment
DATABASE SYSTEM
66
is a process of reducing data redundancy in a database.
Normalization
67
Types of Read Operation
Dirty read
Nonrepeatable read
Phantom read
68
the transaction is validated to ensure that the changes made
will not affect the integrity and consistency of the database
validation phase
69
An information system is composed of
* hardware,
* the DBMS and other software, database(s),
* People
* Procedures
70
manages the interaction between the End-User and Database
DBMS
71
What are the components of the DBMS Environment?
1. Data
2. Hardware
3. Software
4. End Users
5. Procedure
72
A cycle that traces the history of a database within an information system.
DATABASE LIFE CYCLE
73
a transaction executes a query at time t1, and then it runs the same query at time t2, yielding additional rows that satisfy the query.
Phantom read
74
A concurrency control scheme in which an older transaction can request the lock, pre-empt the younger transaction, and reschedule it. Otherwise, the newer transaction
waits until the older transaction finishes.
Wound/Wait Scheme
75
In relational database, the table columns correspond to attributes.
Attribute
76
Outer joins subdivide further into:
- left outer joins,
- right outer joins, and
- full outer joins, depending on which table(s) one retains the rows from (left, right, or
both)
77
The access plan describes which tables to scan, which index, if any, to use for each
table, which joins strategy to use and to what order the tables are read
Query Optimizer
78
through which the end-user data is integrated and managed
Metadata, or data about data
79
is used to reveal the meaning of data.
Information
80
SQL includes commands to insert, update, delete, and retrieve data within the database tables.
Data Manipulation Language (DML)
81
allows the user to create objects related to user access and also control the distribution of privileges among users.
Data Control Language (DCL)
82
allows the user to select a data or a group of data.
Data Query Language (DQL)
83
The different levels of normal forms:
1. First Normal Form (1NF)
2. Second Normal Form (2NF)
3. Third Normal Form (3NF)
84
produced by processing data.
Information
85
Components of Entity Relationship Model:
o Entity
o Attributes
o Relationship
86
interleaves the execution of database operations to ensure
serializability and isolation of transactions
scheduler
87
Time stamps must have two properties:
uniqueness and monotonicity
88
A shared, integrated computer
structure that houses a collection of related data
DATABASE
89
allows the user to manipulate data within objects of a relational database.
Data Manipulation Language (DML)
90
Two Schemes for Time Stamping Method:
Wait/Die Scheme
Wound/Wait Scheme
91
is a subset of a database that is stored at a given location.
database fragment
92
Conceptual Design Steps:
1. DATA ANALYSIS AND REQUIREMENT
2. Entity Relationship Modeling and Normalization
3. Data Model Verification
4. Distributed Database Design
5. DBMS Software Selection
93
ensures that transaction logs are always written before any database data is actually updated.
write-ahead-log protocol
94
is a special DBMS process that establishes the order in which the operations are executed within concurrent transactions.
Scheduler
95
L allows the user to create and restructure database objects
Data Definition Language (DDL)
96
* It determines the most efficient way to execute a SQL statement.
Query Optimizer
97
is the standard language used to define, query, update and maintain relational databases.
Structured Query Language (SQL)
98
SELECT statement embedded within another SELECT statement.
Subqueries
99
retains each record—even if no other matching record exists.
Joined Table
100
indicates the permanence of the database’s consistent state.
Consistency
101
a condition in which only one transaction at a time can own an
exclusive lock on the same object.
mutual exclusive rule
102
Raw facts, or facts that have yet
been processed to reveal their
meaning to the end user
DATA
103
An --- is issued when a transaction requests permission to update a data item and no locks are held on that data item by any other transaction. An --- does not allow other transactions to access the database.
exclusive lock
104
used to improve the efficiency of searches and to avoid duplicate column values.
SQL Indexes
105
divided into logical units called table which is composed of rows and columns of data.
Relational Database
106
Level of Abstraction
- Physical or Internal Leve
- Logical or Conceptual Level
- View or External Level
107
returns a single column and a single row, that is, a single value.
Scalar subquery
108
Types of Subqueries
1. Scalar subquery
2. Row subquery
3. Table subquery
109
Four important concepts that affects the recovery process:
- write-ahead-log protocol
- Redundant transaction logs
- Database buffers
- Database checkpoints
110
an iterative process based on many activities
ER Modeling
111
SQL includes commands to create database objects such as tables, indexes, and views, as well as commands to define access rights to those database objects.
Data Definition Language (DDL)
112
A lock that is issued when a transaction requests permission to read data from a database and no exclusive locks are held on the data by another transaction. A ---- allows other read only transactions to access the database.
shared lock
113
The --- requires neither locking nor time stamping techniques. Instead, a transaction is executed without restrictions until it is committed.
optimistic approach
114
The DML commands in SQL are executed within the context of a transaction, which is a logical unit of work composed of one or more
SQL statements, as defined by business rules. SQL provides commands to control the processing of these statements an indivisible unit of work.
Transaction Control Language (TCL)
115
The end of a program is successfully reached, in which case all changes are
permanently recorded within the database. This action is equivalent to ----.
COMMIT
116
Three Types of Relationships
One-to-One (1:1) relationship
One-to-Many (1:*) relationship
Many-to-Many (*:*) relationship
117
What are the types of integrity constraints?
* Primary key Constraints
* Unique Constraints
* Foreign Key Constraints
* Not Null Constraints
* Check Constraints
* Default Constraints
118
both tables are secondary (or optional), such that if rows are being matched in table A and table B, then all rows from table A are displayed even if there is no matching row in table B, and vice versa
FULL JOIN
119
is based on the assumption that the majority of database
operations do not conflict.
optimistic approach
120
returns one or more columns and multiple rows.
Table subquery
121
the transaction reads the database, executes the needed computations, and makes the updates to a private copy of the database values.
read phase
122
—that is, raw facts of interest to the end user
End-user data
123
a transaction reads a given row at time t1, and then it reads the same row at time t2, yielding different results. The original row may have been updated or deleted
Nonrepeatable read
124
is the second stage in the database design process.
Logical Design
125
a transaction can read data that is not yet committed.
Dirty read
126
The result of processing raw data
to reveal its meaning.
INFORMATION
127
A DBMS uses a ---- to keep track of all transactions that update the database.
The DBMS uses the information stored in this log for a recovery requirement triggered by a ROLLBACK statement, a program’s abnormal termination, or a system failure such as a network discrepancy or a disk crash.
transaction log
128
are operations in which the DBMS writes all of its updated buffers
in memory (also known as dirty buffers) to disk
Database checkpoints
129
A concurrency control scheme in which an older transaction must wait for the younger transaction to complete and release the locks before requesting the locks itself. Otherwise, the newer transaction dies and is rescheduled.
Wait/Die Scheme
130
is to create complete, normalized, no redundancy (to the greatest extent possible), and fully integrated conceptual, logical, and physical database models.
Database Design
131
is composed of rows and columns of data.
Relation
132
requires that all operations (SQL requests) of a transaction be completed; if not, the transaction is aborted.
Atomicity
133
allow the user to manage database transactions.
Transactional Control Commands
134
is the foundation of information, which is the bedrock of knowledge—that is, the body of information and facts about a specific subject.
Data
135
restores a database from a given state (usually inconsistent) to a previously consistent state.
Database recovery
136
is an extension of INNER JOIN. But it does not require each record in
the two joined tables to have a matching record
OUTER JOIN
137
On the client side, the objective is to generate a SQL query that returns the correct
answer in the least amount of time, using the minimum amount of resources at the
server end. The activities required to achieve that goal are commonly referred to as
SQL performance tuning
138
is a collection of programs that
manages the database structure and controls access to the data stored in the database
DATABASE MANAGEMENT
SYSTEM
139
is any action that reads from or writes to a database.
transaction
140
It is the first stage in the database design process.
Conceptual Design
141
refers to a set of valid atomic values for a given attribute.
Domain
142
yields a general overview of the company and its objectives
PLANNING
143
returns multiple columns, but only a single row .
Row subquery
144
Collection of application programs that perform services for the end users (e.g. reports)
FILE SYSTEMS
145
it groups the data from the SELECT table(s) and produces a single summary row for
each group
Grouping Results (GROUP BY Clause)
146
