DATABASE - FINALS Flashcards
(179 cards)
1
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 raw data into useful information.
A
Database Language
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
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
4
Q
SQL Major Components:
A
- Data Definition Language (DDL)
- Data Manipulation Language (DML)
- Transaction Control Language(TCL)
- Data Control Language(DCL
5
Q
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.
A
Data Definition Language (DDL)
6
Q
SQL includes commands to insert, update, delete and retrieve data within the database tables.
A
Data Manipulation Language (DML)
7
Q
DML commands in SQL are executed within the context of a transaction, which is logical unit of work composed of one or more SQL statements, as defined by business rules.
A
Transaction Control Language (TCL)
8
Q
SQL provides commands to control the processing of these statements an indivisible unit of work.
A
Transaction Control Language (TCL)
9
Q
commands 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.
A
Data Control Language (DCL)
10
Q
it groups the data from the SELECT table(s) and produces a single summary row for each group.
A
Group by Clause
11
Q
is used to combine records from two or more tables in a database.
A
SQL Joins
12
Q
based on a common field between them other keywords are combined with the SELECT statement, these keywords are:
A
INNER JOIN
OUTER JOIN
- Left Join
- Right Join
- Full Join
13
Q
Inner Join keyword
A
EQUIJOIN
14
Q
return rows when there is atleast one match in both tables
A
Inner Join or Equijoin
15
Q
is an extension of Inner Join. but it does not require each record in the two joined tables to have a matching record.
A
Outer Join
16
Q
the joined table retains each record - even if no other matching record exists.
A
Outer Join
17
Q
Outer joins subdivide further into:
A
- left outer joins
- right outer joins
- full outer joins
18
Q
returns all rows from the left table, even if there are no matches in the right table
A
Left Join
19
Q
returns all the rows from the right table, even if there are no matches in the left table
A
Right Join
20
Q
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
A
Outer Join
21
Q
returns all possible combinations of rows from the two tables
A
Cross Join
22
Q
SELECT statement embedded within another SELECT statement
A
Subqueries
23
Q
the results of this inner SELECT statement (or subselect) are used in the outer statement to help determine the contents of the final result.
A
Subqueries
24
Q
Types of Subqueries
A
- Scalar Subquery
- Row Subquery
- Table Subquery
25
returns a single column and a single row, that is, a single value
Scalar Subquery
26
returns multiple columns but only a single row
Row Subquery
27
returns one or more columns and multiple rows
Table Subquery
28
used to improve the efficiency of searches and to avoid duplicate column values. can be created on the basis of any selected attribute.
SQL Indexes
29
used to delete index
Drop Index
30
the syntax of Drop Index
DROP INDEX indexname;
31
a virtual table base on a SELECT query
View
32
the query can contain columns, computed columns, aliases and aggregate functions from one or more tables.
View
33
syntac for create view
CREATE VIEW viewname AS SELECT query
34
data definition command that stores the subquery specification
CREATE VIEW
35
used to generate the virtual table - in the data dictionary.
SELECT statement
36
is the keyword to modify a created view
ALTER VIEW
37
is used to delete a view that was previously created
DROP VIEW
38
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
39
An information system is composed of:
- hardware
-DBMS and other software
-people
-procedures
40
is the process that establishes the need for an information system and its extent
System Analysis
41
is the process of creating an information system
System Development
42
Generating Information for decision making:
1. Data
2. Appplication Code
3. Information
4. Decisions
43
The performance of an information system depends on three factors:
1. Database design and implementation
2. Application design and implementation
3. Administrative procedures
44
is the process of database design and implementation
Database Development
45
is to create complete, normalized, no redundancy (to the greatest extent possible), and fully integrated conceptual, logical and physical database models.
Database Design
46
this phase includes creating the database storage structure, loading data into the database and providing for data management.
Implementation Phase
47
is a cycle that traces the history of an information system.
System Development Life Cycle (SDLC)
48
provides the big picture within which database design and application development can be mapped out and evaluated
System Development Life Cycle (SDLC)
49
Phases of System Development Life Cycle (SDLC)
- Plannin
- Analysis
- Detailed Systems Design
- Implementation
- Maintenance
50
phase yields a general overview of the company and its objectives
Planning
51
problems defined during the planning phase are examined in greater detail during the ----
Analysis Phase
52
the designer completes the design of the system's processes
Detailed Systems Design
53
the design includes all the necessary technical specifications for the screens, menu, reports and other devices that might help make the system a more efficient information generator.
Detailed Systems Design
54
the hardware, the DBMS software and application programs are installed and the database design is implemented
Implementation
55
during the initial stages of ---, the system enters to a cycle of coding, testing and debugging until it is ready to be delivered
Implementation
56
the actual database is created, and the system is customized by the creation of table views, user authorization and so on.
Implementation
57
The system is in full operation at the end of this phase, but it will be continuously evaluated and fine-tuned
Implementation
58
almost as soon as the system is operational, end users begin to request changes in it.
Maintenance
59
Three types of Maintenance
1. Corrective Maintenance
2. Adaptive Maintenance
3. Perfective Maintenance
60
a cycle that traces the history of a database within an information system
Database Life Cycle
61
6 Phases of Database Life Cycle
1. Database Initial Study
2. Database Design
3. Implementation and loading
4. Testing and evaluation
5. Operation
6. Maintenance and evolution
62
the designers job is to make sure that his or her database system objectives correspond to those envisioned by the end user.
Database Initial Study
63
It focuses on the design of the database model that will support company operations and
objectives.
DATABASE DESIGN
64
The most critical DBLC phase since it will make sure that the final product meets the user
and system requirements.
DATABASE DESIGN
65
Two views of the data within the systems (Database Design)
1. Business View
2. Designer's View
66
This includes
creation of tables, attributes, domains, views, indexes, security constraints and storage
and performance guidelines
IMPLEMENTATION AND LOADING
67
is a technique that creates logical representations of computing resources that are independent of the underlying physical computing resources.
Virtualization
68
The DBA test and fine-tunes the database to ensure that is performed as expected.
TESTING AND EVALUATION
69
Database backups can be performed in different levels:
1. Full Backup
2. Differential Backup
3. Transaction Log Backup
70
dump, of the entire database. In this case, all database objects are
backed up in their entirety.
Full Backup
71
which only the objects that have been updated or modified since the last full backup are backed up.
Differential Backup
72
which backs up only the transaction log operations that are
not reflected in a previous backup copy of the database.
Transaction Log Bacup
73
The database, its management, its users, and its application program constitute a complete
information system.
OPERATION
74
The database administrator must be prepared to perform routine maintenance activities within the
database.
MAINTENANCE AND EVOLUTION
75
It is the first stage in the database design process.
Conceptual Design
76
A process that uses data
modelling techniques to create a model of a database structure that represents
real world objects as realistically as possible.
Conceptual Design
77
Conceptual Design Data Rule:
“All that is needed is there, and all that is there is needed.”
78
Conceptual Design Steps
1. Data Analysis and requirements
2. Entity relationship modeling and normalization
3. Data model verification
4. Distributed database design
79
The first step in conceptual design is to discover the characteristics of the data elements
DATA ANALYSIS AND REQUIREMENT
80
The process of defining business rules and developing the conceptual model
using ER diagrams can be described using the steps shown below:
Entity Relationship Modeling and Normalization
81
one of the last steps in the conceptual design stage, and it is one of the most
critical.
Data Model Verification
82
If the database data and processes will be distributed across the system, portions of a database, known as database fragments, may reside in several physical locations.
Distributed Database Design
83
is a subset of a database that is stored at a given location. The database fragment may be a subset of rows or columns from one or multiple tables.
Database Fragment
84
Factors that affect the purchasing decision vary from company to
company,
DBMS Software Selection
85
is the second stage in the database design process.
Logical Design
86
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.
Physical Design
87
is any action that reads from or writes to a database.
transaction
88
A sequence of database requests that accesses the database.
Transaction
89
is a logical unit of work; that is, it must be entirely completed or
aborted—no intermediate ending states are accepted.
Transaction
90
ACID
Atomicity
Consistency
Isolation
Durabilit
91
requires that all operations (SQL requests) of a transaction be
completed; if not, the transaction is aborted.
Atomicity
92
indicates the permanence of the database’s consistent state.
Consistency
93
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
94
ensures that once transaction changes are done and committed, they cannot be undone or lost, even in the event of a system failure.
Durability
95
Transaction support is provided by two SQL statements:
COMMIT and ROLLBACK
96
statement is reached, in which case all changes are permanently
recorded within the database. The COMMIT statement automatically ends the SQL transaction.
COMMIT statement
97
statement is reached, in which case all changes are aborted and the database is rolled back to its previous consistent state.
ROLLBACK statement
98
to keep track of all transactions that update the database.
transaction log
99
A DBMS feature that coordinates the simultaneous execution of transactions in a multiprocessing database system while preserving data integrity
Concurrency Control
100
is to ensure the serializability of transactions in a multiuser
database environment.
Concurrency Control
101
l is important because the simultaneous execution of transactions over a shared database can create several data integrity and consistency problems.
Concurrency Control
102
Three Main Problems of Concurrency Control
Lost Updates
Uncommitted Data
Inconsistent Retrieval
103
Problem occurs when two concurrent transactions, T1 and T2, are updating the same
data element and one of the updates is lost (overwritten by the other transaction).
Lost Updates
104
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.
Uncommitted Data
105
It occur when a transaction accesses data before and after one or more other transactions
finish working with such data.
Inconsistent Retrieval
106
is a special DBMS process that establishes the order in which the operations are
executed within concurrent transactions.
Scheduler
107
interleaves the execution of database operations to ensure serializability and isolation of
transactions
Scheduler
108
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
109
guarantees exclusive use of a data item to a current transaction.
lock
110
The use of locks based on the assumption that conflict between transactions is likely is
usually referred to as
Pessimistic Locking
111
All lock information is handled by a -----, which is responsible for assigning
and policing the locks used by the transactions.
Lock Manager
112
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.
Exclusive Lock
113
An ---- does not allow
other transactions to access the database.
Exclusive Lock
114
A --- lock allows
other read only transactions to access the database
Shared Lock
115
is a condition in which only one transaction at a time can own an
exclusive lock on the same object.
Mutual Exclusive Rule
116
approach to scheduling concurrent transactions assigns a global, unique time stamp to each transaction.
Time Stamping
117
Time stamps must have two properties:
Uniqueness
Monotonicity
118
ensures that no equal time stamp values can exist
Uniqueness
119
ensures that time stamp values always increase.
Monotonicity
120
Two Schemes for Time Stamping Method:
Wait/Die Scheme
Wound/Wait Scheme
121
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
122
A concurrency control scheme in which an older transaction can
request
Wound/Wait Scheme
123
is based on the assumption that the majority of database operations do not conflict.
Optimistic Approach
124
approach requires neither locking nor time stamping techniques.
Optimistic Approach
125
Using an optimistic approach, each transaction moves through two or three phases, referred to as ---, ----, and ----
read, validation, and write
126
During the --- phase, the transaction reads the database, executes the needed
computations, and makes the updates to a private copy of the database values.
Read Phase
127
During the --- phase, the transaction is validated to ensure that the changes made will not
affect the integrity and consistency of the database.
Validation Phase
128
During the --- phase, the changes are permanently applied to the database
Write Phase
129
defines transaction management based on transaction isolation levels.
ANSI SQL standard
130
refer to the degree to which transaction data is “protected
or isolated” from other concurrent transactions.
Transaction isolation levels
131
Types of Read Operation
Dirty read
Nonrepeatable read
Phantom read
132
a transaction can read data that is not yet committed
Dirty read
133
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
134
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
135
will read uncommitted data from other transactions.
Read Uncommitted
136
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
137
forces transactions to read only committed data.
Read Committed
138
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
Repeatable Read
139
restores a database from a given state (usually inconsistent) to a
previously consistent state.
Database recovery
140
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
141
Four important concepts that affects the recovery process:
1. Write-ahead-log Protocol
2. Redundant transaction logs
3. Database buffers
4. Database checkpoints
142
ensures that transaction logs are always written before any database data is actually updated.
Write-ahead-log Protocol
143
(several copies of the transaction log) ensure that a physical disk failure will not impair the DBMS’s ability to recover data
Redundant transaction logs
144
are temporary storage areas in primary memory used to speed up disk operations
Database buffers
145
are operations in which the DBMS writes all of its updated buffers in memory (also known as dirty buffers) to disk.
Database checkpoints
146
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
Database performance tuning
147
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.
Client Side
148
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
149
the DBMS environment must be properly configured to respond to
clients’ requests in the fastest way possible, while making optimum use of existing resources.
Server Side
150
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.
DBMS performance tuning
151
It is the component of a database management system that attempts to select the most efficient way to execute a query.
Query Optimizer
152
It determines the most efficient way to execute a SQL statement
Query Optimizer
153
The optimizer generates a set of potential plans for the SQL statement based on available access paths and hints.
Query Optimizer
154
refers to a set of data that displays the characteristics of volume, velocity, and variety (the 3 Vs) to an extent that makes the data unsuitable for management by a relational database management system
Big Data
155
Three Characteristics of Big Data:
1. Volume
2. Velocity
3. Variety
156
the quantity of data to be stored
Volume
157
the speed at which data is
entering the system
Velocity
158
the variations in the structure of
the data to be stored
Variety
159
According to --- 2018, The amount of data we produce every day is truly mind- boggling. There are 2.5 quintillion bytes of data created each day at our current pace, but that pace is only accelerating with the growth of the Internet of Things (IoT).
Forbes
160
According to Forbes 2018, The amount of data we produce every day is truly mind- boggling. There are ----- of data created each day at our current pace, but that pace is only accelerating with the growth of the Internet of Things (IoT).
2.5 quintillion bytes
161
According to Statista.com 2023 an
approximately ==== of data are created each day.
328.77 million terabytes
162
Two options for handling Big Data
Scale up
Scale out
163
meaning we keep the same number of systems to store and process data, but migrate each system to a larger system.
Scale up
164
meaning we increase the number
of systems, but do not migrate to larger systems
Scale Out
165
refers to the speed at which data is entered into a system and must be processed.
Velocity
166
refers to the vast array of formats and structures in which the data can be captured.
Variety
167
Types of Big Data Variety:
Structured Data
Semi-structured Data
168
It is organized, tagged and easily searchable, often stored in
traditional data
Structured Data
169
This type of data contains some structured elements but lacks a rigid structure.
Semi-structured Data
170
is a Java-based framework for distributing and processing very large data sets across clusters of computers.
Hadoop
171
If you have a huge file, the file will be broken down into smaller chunks and stored in various machines . In breaking the file, it will also make copies of it which goes in different nodes, this way big data is stored in a distributed way.
Hadoop Distributed File System.
172
Instead of using one large computer to store and process the data, Hadoop allows clustering multiple computers to analyze massive datasets in parallel more quickly.
Map Reduce
173
are non-tabular databases and store data differently than relational tables.
NoSQL
174
Four Category of NoSQL:
1.Key-value database
2.Document database
3.Column-oriented database
4.Graph database
175
It is the simplest of the NoSQL data models. It stores data as a collection of key-value pairs. The key acts as an identifier for the value.
Key-value Database
176
can refer to traditional, relational database technologies that use column centric storage instead of row-centric storage
Column-oriented Database
177
More efficient for optimizing read operations to store the data
in relational tables not per row, but per column.
Column-oriented Database
178
are based on graph theory and represent data through nodes, edges, and properties.
Graph Database
179
are conceptually similar to key-value databases, and they can
almost be considered a subtype of KV databases.
Document-oriented Database
