Index
Symbols and Numerics
* (asterisk), 325
: (colon), 470
|| (concatenation operator), 238
= (equal), 242
[] (square brackets), 505
_ (underscore), 257
1:1 (one-to-one) relationship, 36
1:N (one-to-many) relationship, 176
1NF (First Normal Form),
2NF (Second Normal Form), 161
3NF (Third Normal Form), 161
4NF (Fourth Normal Form),
5NF (Fifth Normal Form),
A
ABS function, 234
absolute fetches, 355
Access Database Engine, 451
access modes, 398
access path, checking,
access privileges
about, 419
classifying users, 422
Data Control Language (DCL) and,
granting
about,
adding data, 424
changing data,
defining new data types, 427
delegation, 428
deleting data, 424
executing SQL statements, 427
GRANT ALL statement,
referencing data in other tables,
responding to events, 427
roles, 430
using database facilities, 426
viewing data,
identifying authorized users
about, 420
roles,
user identifiers, 420
revoking,
revoking roles, 430
ACID test,
ACM Transactions on Database Systems (journal), 33
ACOS function, 234
ActiveX control, 721
ActiveX Data Objects (ADO), 450
actual cardinality, 241
ad hoc query, 87
adding
blocks of rows to tables, 506
constraints
about,
to existing tables, 444
data
for access privileges, 424
with forms,
to node and edge tables,
to selected columns,
to tables,
hardware,
ADO (ActiveX Data Objects), 450
ADO Extensions for DDL and Security (ADOX), 457
ADOdb library,
advanced concepts, for entity-relationship models,
AFTER keyword, 526
aggregate functions. functions
alertness, for protection, 389
alias, 721
ALL predicate,
ALTER statement, 495
ALTER TABLE statement, 85
American National Standards Institute (ANSI), 55
analyzing
query efficiency,
workload,
AND condition,
anomalies
about,
defined, 186
eliminating,
higher normal forms,
ANSI (American National Standards Institute), 190
ANSI/ISO standard, indexes and, 190
antivirus software, 388
ANY predicate,
ANY_VALUE function, 226
APPENDCHILDXML function,
applet, 721
application programming interface (API), 721
application requests, to remote databases,
application/database interaction, 688
application-layer firewalls, 388
applications
about, 477
approaching client problem
about, 478
drafting detailed statement of requirements, 479
interviewing stakeholders,
proposal,
building
about, 497
from bottom up,
Entity-Relationship (ER) models,
with SQL and procedural language,
tables,
testing,
from top down,
combining SQL with procedural languages
about, 464
classic procedural languages,
object-oriented procedural languages,
comparing SQL with procedural languages
about, 462
classic procedural languages,
nonprocedural languages,
object-oriented procedural languages, 463
designing
sample,
user interface, 496
determining deliverables,
development tools for,
embedding SQL in
about,
Java applications, 470
Oracle Pro*C applications,
Perl applications, 470
PHP applications, 471
using SQL with other .NET languages, 472
using SQL with Visual Basic .NET applications, 471
modifying table structure, 495
in ODBC,
removing tables, 495
transforming models,
understanding client problem, 478
using SQL modules with
about,
module declarations,
module procedures, 474
modules in Oracle,
applying constraints
about, 126
assertions,
column,
foreign key,
table,
approximate numerics
about,
DOUBLE 221
221
221
ARRAY type,
array value expressions, 241
arrays
defined,
RAID, 679
in relational database model, 66
ASIN function, 234
assertions, 721
assignment statements, 517
asterisk (*), 325
ATAN function, 234
atomic, 721
ATOMIC keyword, 516
atomic value, 72
atomicity
in ACID test, 396
compound statements and,
attributecentric mapping,
attributes
about, 157
defined, 721
in Entity-Relationship (ER) models, 34
XML documents, 551
AUTHORIZATION clause, 473
AVG function, 225
avoiding
data corruption,
direct user interaction,
B
B+ tree indexes
about, 194
problems with, 683
back end, defined, 721
backups
about, 368
frequency, 369
full versus incremental, 369
importance of,
maintenance,
Backus-Naur Form (BNF), 621
batch transactions, optimizing, 670
BCNF (Boyce-Codd Normal Form),
BEFORE keyword, 526
beta testing, 511
BETWEEN predicate,
BIGINT type, 221
bill of materials (BOM), 15
BINARY LARGE OBJECT (BLOB) type, 221
binary relationships, handling,
binary strings
about, 112
221
BINARY LARGE OBJECT 221
BINARY 124
BINARY type, 221
BINARY VARYING type, 124
BLOB (BINARY LARGE OBJECT) type, 221
BNF (Backus-Naur Form), 621
BOM (bill of materials), 15
Boolean value expressions, 240
Booleans, 392
bottlenecks
about, 681
analyzing query efficiency,
application/database interaction, 688
avoiding direct user interaction,
columns and,
communication problems
application development tools,
locking and client performance, 686
ODBC/JDBC versus native drivers,
cursors and, 689
finding problem queries,
hardware and, 687
index problems
B+ tree indexes, 683
full table scans versus indexed table access,
index pluses and minuses,
index-only queries, 684
isolating performance problems, 690
locating hotspots, 693
managing resources
database buffer manager,
disk subsystem,
locking subsystem,
logging subsystem, 707
partitioning,
performing top-down analysis,
precompiled queries and, 689
slow query, 682
slow update,
using query analyzers,
bottom up design,
Boyce-Codd Normal Form (BCNF),
bugs, fixing, 511
business rules,
Buxton, Stephen (author)
Querying 116
C
C#, MySQL and, 458
CALL statement, 523
candidate key, 207
cardinality
about, 241
actual, 241
defined, 38
maximum, 485
minimum,
CARDINALITY function, 241
Cartesian product/cross join,
CASCADE keyword, 430
cascading delete, 311
CASE statement,
CASE…END CASE statement,
CAST expression, converting data types with,
catalogs
defined, 721
in relational database model, 72
CEIL/CEILING function, 235
CHARACTER LARGE OBJECT (CLOB) type, 221
character sets, mapping to XML, 557
character strings
about, 110
221
CHARACTER LARGE OBJECT 221
CHARACTER 111
NATIONAL 221
NATIONAL CHARACTER LARGE 112
NATIONAL CHARACTER 221
CHARACTER type, 221
CHARACTER VARYING type, 111
CHARACTER_LENGTH function, 232
Cheat Sheet (website), 3
CHECK constraint, 392
checking access path,
checkpoints
about, 404
setting,
CIL (Common Intermediate Language), 458
classic procedural languages,
classifying users, 422
client, defined, 721
client/server system, defined, 721
CLOB (CHARACTER LARGE OBJECT) type, 221
CLOSE statement,
closing cursors, 356
cloud computing, 25
CLR (Common Language Runtime), 457
cluster, defined, 721
clustered index scan, 279
clustering indexes
about,
relational operators and,
C++.NET, MySQL and, 458
COALESCE expression, 244
CODASYL DBTG database model, defined, 721
Codd, Edgar F. (doctor), 547
Codd's rules,
COLLATE BY clause, 348
collating sequence, defined, 722
collation, 348
collection types
about, 118
defined, 722
multiset, 119
colon (:), 470
column constraints
about, 492
392
NOT
column values, 220
column-name join,
columns
adding data to selected,
bottlenecks and,
defined, 722
in relational database model, 71
selecting to index, 651
specifying,
transferring between tables,
combining
SQL statements with host language, 101
SQL with procedural languages
about, 464
classic procedural languages,
object-oriented procedural languages,
COMMIT statement, 408
committing transactions, 401
Common Intermediate Language (CIL), 458
Common Language Runtime (CLR), 457
communication problems
application development tools,
locking and client performance, 686
ODBC/JDBC versus native drivers,
comparing SQL, with procedural languages
about, 462
classic procedural languages,
nonprocedural languages,
object-oriented procedural languages, 463
comparison operators, correlated subqueries and,
comparison predicates, 253
complex relationships
about,
for Entity-Relationship (ER) models,
components, of database management system (DBMS)
about, 136
database, 136
database application, 137
database engine, 137
front end, 137
user, 138
composite identifier, 35
composite indexes,
composite key, 722
compound SQL statement, 445
compound statements
about,
assignment, 517
atomicity,
cursors,
variables, 516
concatenation operator (||), 238
conceptual view, defined, 722
concurrent access, defined, 722
concurrent-operation conflicts,
condition join, 335
conditional value expressions, 722
conditions, handling
about,
handler actions/effects,
handler declarations, 435
nonhandled conditions, 437
CONNECT statement, 72
connecting
nodes by multiple edges,
nodes to itself, 635
in relational database model, 72
user interfaces to databases,
connection handle, 538
consensus, building for DBMS,
Consistency, in ACID test, 396
constraints
about, 81
adding
about,
to existing tables, 444
applying
about, 126
assertions,
column,
foreign key,
table,
data-entry errors and,
defined, 722
setting
about, 209
table constraints,
constructor functions,
containment hierarchy,
CONTENT predicate, 572
context switching, 672
CONVERT function, 230
converting
data types
about,
with CAST expression,
ER models to relational models, 172
cooling hot spots, 415
copying, from data files,
correctness,
correlated subqueries
defined, 296
role of,
tuning,
COS function, 234
COSH function, 234
cost
of index maintenance, 654
of indexes, 191
CouchDB, 25
COUNT function,
CPU registers, 663
crackers, 370
CREATE command, 45
CREATE DATABASE statement, 100
CREATE DOMAIN statement,
CREATE SCHEMA statement, 84
CREATE statement, 503
CREATE TABLE statement,
CREATE TRIGGER statement, 526
CREATE TYPE statement,
CREATE VIEW statement, 79
creating
access privilege roles, 421
applications
about, 497
from bottom up,
with SQL and procedural language,
testing,
from top down,
consensus for DBMS,
database application, 147
database structure, 147
databases,
databases with Data Definition Language (DDL)
about,
containment hierarchy,
creating other objects,
creating tables, 77
modifying tables, 85
removing tables, 85
specifying columns,
databases with SQL
about, 203
building tables,
deleting tables, 215
ensuring data validity with domains,
establishing relationships between tables,
indexes and, 210
keys and, 210
modifying table structure, 215
planning,
setting constraints,
domains,
Entity-Relationship (ER) models,
indexes, 190
maintenance programmer documentation, 147
property graph tables,
relational models
about,
database traditions,
functional dependencies,
keys, 159
relations, 158
reports, 509
schemas,
screen forms, 509
SQL tables to hold XML data,
Statement of Requirements, 479
tables
about, 502
finding table rows with keys,
using CREATE TABLE statement,
user documentation, 147
views,
XML schema for SQL table,
current user identifier, 420
CURRENT_USER variable, 223
cursors
about,
bottlenecks and, 689
closing, 356
compound statements and,
declaring
about,
holding, 351
ordering query result set,
query expression, 347
result set,
scrolling, 351
sensitive versus insensitive,
updating table rows, 349
defined, 722
opening,
operating on single rows
about,
absolute versus relative fetches, 355
deleting rows, 355
FETCH syntax, 354
updating rows, 355
D
DAO interface, 450
data
adding
with forms,
to node and edge tables,
to selected columns,
to tables,
deleting from tables,
entering with SQL, 505
managing
with complicated programs,
with simple programs, 11
operating on with Data Manipulation Language (DML)
about, 86
adding data to tables,
deleting data from tables,
retrieving data from databases,
updating data in tables,
updating views,
querying in graph tables,
retrieving from databases,
typing, 88
updating in tables,
Data Control Language (DCL)
access privileges and,
defined, 722
for security
about, 95
granting access privileges,
preserving database integrity with transactions,
revoking access privileges, 96
data corruption, avoiding,
Data Definition Language (DDL)
creating databases with
about,
containment hierarchy,
creating other objects,
creating tables, 77
modifying tables, 85
removing tables, 85
specifying columns,
defined, 722
scheduling statements, 414
data entry clerks, 88
data files, copying from,
Data Manipulation Language (DML)
defined, 722
operating on data with
about, 86
adding data to tables,
deleting data from tables,
retrieving data from databases,
updating data in tables,
updating views,
data models
about,
Entity-Relationship (ER) models
about,
advanced concepts,
complex relationships,
drawing diagrams,
examples,
simplifying relationships using normalization, 51
techniques for,
translating into relational models, 51
identifying stakeholders, 30
interviewing stakeholders, 30
obtaining stakeholder buy-in, 31
reconciling conflicting requirements, 31
for users, 141
data redundancy, defined, 722
data retrievals
about, 187
full table scans, 189
hierarchical storage,
from XML documents,
data sources
defined, 722
in ODBC, 537
data structures, used for indexes,
data sublanguage, 722
data types
about,
approximate numerics
about,
DOUBLE 221
221
221
binary strings
about, 112
221
BINARY LARGE OBJECT 221
BINARY 124
Booleans, 124
character strings
about, 110
221
CHARACTER LARGE OBJECT 221
CHARACTER 111
NATIONAL 221
NATIONAL CHARACTER LARGE 112
NATIONAL CHARACTER 221
collection types
about, 118
multiset, 124
converting
about,
with CAST expression,
data-entry errors and, 392
datetimes
about, 113
221
TIME WITH TIME 221
TIME WITHOUT TIME 124
TIMESTAMP WITHOUT TIME 124
TIMSETAMP WITH TIME 221
defined, 722
exact numerics
about, 106
221
221
221
502
393
221
incompatibilities with, 465
intervals, 115
JSON types,
mapping to XML, 558
REF types, 124
ROW type,
summary of, 124
user-defined types
about, 124
distinct types,
structured types,
XML type,
XQuery compared with SQL,
data validity, ensuring with domains,
data values.
database administrator (DBA), 722
database application
building, 147
as component of DBMS, 146
defined, 57
database buffer manager,
Database Development For Dummies (Taylor), 363
database dumps,
database engine
as component of DBMS, 137
defined, 722
Database Engine Tuning Advisor,
database facilities, 426
database locks, 410
database management system (DBMS)
about,
anomalies
about,
eliminating,
higher normal forms,
building consensus,
building relational models
about,
database traditions,
functional dependencies,
keys, 159
relations, 158
components of
about, 136
database, 136
database application, 137
database engine, 137
front end, 137
user, 138
database integrity versus performance tradeoff,
defined, 723
finding people
about, 152
immediate supervisor, 152
standards organization,
upper management, 154
users, 153
System Development Life Cycle (SDLC)
about,
Definition phase, 139
Design phase,
Evaluation phase,
Final Documentation and Testing phase,
Implementation phase, 147
Maintenance phase, 150
Requirements phase,
database management systems (DBMS)
about, 56
IBM DB2, 61
Microsoft Access,
Microsoft SQL Server, 61
MySQL,
operations, 690
Oracle Database, 62
PostgreSQL, 63
Sybase SQL Anywhere, 62
database object owner, 422
database page buffer,
database publishing, defined, 723
database server, defined, 723
database traditions, in relational models,
databases. See databases
about, 57
as component of DBMS, 136
connecting user interfaces to,
creating
about,
structure of, 147
creating with Data Definition Language (DDL)
about,
containment hierarchy,
creating other objects,
creating tables, 77
modifying tables, 85
removing tables, 85
specifying columns,
creating with SQL
about, 203
building tables,
deleting tables, 215
ensuring data validity with domains,
establishing relationships between tables,
indexes and, 210
keys and, 210
modifying table structure, 215
planning,
setting constraints,
defined, 722
design errors/flaws, 393
designing, 145
designing samples
about, 170
converting ER models to relational models, 172
Entity-Relationship (ER) model,
handling binary relationships,
normalizing relational models,
sample conversion,
determining buffer size,
elements of,
evolution of,
integrity of,
models
about, 14
hierarchical,
historical background of,
network,
nonrelational NoSQL, 25
object-oriented, 25
object-relational, 25
relational,
performance of,
retrieving
about,
data from,
tuning
about,
analyzing workload,
minimizing traffic between application and server, 658
physical design considerations,
precompiling frequently used queries, 658
selecting indexes,
separating user transactions from transactions,
tuning indexes,
tuning queries,
tuning transactions, 657
using, 12
data-entry errors
adding constraints to prevent,
reducing
about, 392
constraints,
data types, 392
humans, 393
as a threat, 363
DATE type, 221
datetime value expressions
about,
cursors and,
defined, 723
datetime value functions
about,
221
TIME WITH TIME 221
TIME WITHOUT TIME 124
TIMESTAMP WITHOUT TIME 124
TIMSETAMP WITH TIME 221
DB2
about, 452
defined, 723
Java and, 460
DCL.
DDL.
deadlocks, 674
DECFLOAT type, 221
DECIMAL type, 221
declarations
about, 460
module,
XML,
DECLARE CURSOR statement, 354
DECLARE statement, 446
declaring
cursors
about,
holding, 351
ordering query result set,
query expression, 347
result set,
scrolling, 351
sensitive versus insensitive,
updating table rows, 349
host variables, 469
deferred constraint, defined, 723
defining new data types, 427
Definition phase, in Software Development Life Cycle (SDLC), 139
degree-three relationships,
degree-two relationships,
delegation, for access privileges, 428
DELETE CASCADE clause, 494
DELETE statement,
DELETEXML function,
deleting
data
for access privileges, 424
from tables,
rows, 355
tables, 215
deletion anomaly, 723
deliverables, determining,
delivering results,
denial-of-service attacks, 723
dense index,
descriptor, defined, 723
Design phase, in Software Development Life Cycle (SDLC),
designing
databases, 145
Entity-Relationship (ER) models,
sample applications,
sample databases
about, 170
converting ER models to relational models, 172
Entity-Relationship (ER) model,
handling binary relationships,
normalizing relational models,
sample conversion,
user interface,
destroying access privilege roles, 421
detail area,
determinants, in relational database model, 67
determining
database buffer size,
deliverables,
entities, 484
project scope,
development environments
about, 449
IBM Db2, 452
Microsoft Access
about,
Access Database Engine, 451
ActiveX Data Objects (ADO), 450
DAO interface, 450
files with .mdb extension, 451
Jet engine, 450
Object Linking and Embedding Database (OLE DB), 451
Open Database Connectivity (ODBC), 451
Microsoft SQL Server,
MySQL, 454
Oracle 23c, 453
PostgreSQL, 453
SQL Anywhere, 453
tuning
about,
adding hardware,
handling failures,
maximizing hardware,
multiprocessor environments, 680
tuning operating system,
tuning recovery system,
diagnostics areas
about,
defined, 723
detail area,
header area,
dirty read, 399
disk controller, 691
disk controller cache, 677
disk mirroring, 367
disk subsystem,
DISTINCT predicate, 263
distinct types,
distributed data processing, defined, 723
DKNF (Domain/Key Normal Form),
DLL (Dynamic Link Library), 450
DML.
document components (XML)
about, 549
attributes, 551
elements,
entity references,
numeric character references, 553
XML declarations,
document element, 550
Document Object Model (DOM), 579
DOCUMENT predicate,
documentation, finalizing, 149
documenting
Design phase in SDLC,
Evaluation phase in SDLC, 144
DOM (Document Object Model), 579
domain integrity, 723
Domain/Key Normal Form (DKNF),
domains
creating,
defined, 723
ensuring data validity with,
DOUBLE PRECISION type, 221
drawing Entity-Relationship (ER) model diagrams,
driver manager
defined, 724
in ODBC, 535
drivers
defined, 723
in ODBC,
DROP statement, 495
dumb terminal, defined, 724
Durability, in ACID test,
dynamic cache sizing, 62
Dynamic Link Library (DLL), 450
dynamic SQL, defined, 724
dynamic string building,
E
edge properties, 634
edges.
education, for protection, 389
elementcentric mapping,
elements, in XML documents,
eliminating
anomalies,
many-to-many relationships,
unneeded locks, 412
Ellisonn, Larry, 54
ELSE clause, 519
embedded Structured Query Language (SQL), 724
embedding
SQL in applications
about,
Java applications, 470
Oracle Pro*C applications,
Perl applications, 470
PHP applications, 471
using SQL with other .NET languages, 472
using SQL with Visual Basic .NET applications, 471
SQL statements, 514
empty elements,
ensuring data validity, with domains,
entering
data with SQL, 505
values
about,
column values, 220
literal values,
row values, 220
special variables,
variables, 222
enterprise database, defined, 724
entities
determining, 484
in Entity-Relationship (ER) models,
ID-dependent, 43
relationships and,
strong,
subtype,
supertype,
weak,
entity class, 33
entity instance, 33
entity integrity, 724
entity references, in XML documents,
Entity-Relationship (ER) model
about,
advanced concepts,
building,
complex relationships,
converting to relational models, 172
designing,
drawing diagrams,
examples,
normalizing,
simplifying relationships using normalization, 51
techniques for
about, 33
attributes, 34
entities,
identifiers,
relationships,
transforming,
translating into relational models, 51
environment handle, 538
equal (=), 242
equi-join,
equi-join query, 193
equipment failure, as a threat,
ER model. model
error handling
about,
adding constraints to existing tables, 444
conditions
about,
handler actions/effects,
handler declarations, 435
nonhandled conditions, 437
diagnostics areas
about,
detail area,
header area,
example constraint violation,
exceptions,
identifying error conditions, 432
interpreting SQLSTATE information,
SQLSTATE statement,
WHENEVER clause,
error messages,
escape characters, mishandling,
establishing relationships, between tables,
Evaluation phase, in System Development Life Cycle (SDLC),
exact numerics
about, 106
221
221
221
502
393
221
examples
constraint violation,
of entity-relationship models,
EXCEPT operator, 329
EXCEPTION statement, 446
exceptions, handling,
exclusive locks, 409
executable statements, 460
EXECUTE privilege, 528
executing SQL statements
about, 427
combining with host language, 101
embedded SQL,
interactive SQL, 100
module language,
execution plans, reading SQL Server,
existence, testing for,
existence-dependent, 43
EXISTS predicate,
EXP function, 234
explicit mapping,
exploits, 724
expressions
about, 237
array value, 241
Boolean value, 240
conditional value,
converting data types with CAST expression,
datetime value,
interval value,
numeric value,
row value,
string value, 238
Extensible Markup Language (XML)
about,
data type,
defined, 724
document components
about, 549
attributes, 551
elements,
entity references,
numeric character references, 553
XML declarations,
FLWOR expressions
about,
for clause,
let clause,
order by clause, 611
return clause,
where clause, 610
mapping SQL to
about, 557
character sets, 557
creating XML schema for SQL table,
data types, 558
handling null values,
identifiers,
nonpredefined data types,
tables, 564
predicates
572
572
relating SQL to, 554
retrieving data from documents,
schema,
SQL functions
about, 566
571
569
568
567
567
570
SQL tables
about, 575
creating to hold XML data,
inserting XML data into,
Microsoft's tools for updating XML data in,
Oracle's tools for updating XML data in,
updating XML documents, 578
updating documents, 578
XQuery
about, 600
functionality,
origin of, 600
requirements for, 601
SQL
usage scenarios,
external routine, 73
externally invoked routine, 73
EXTRACT function, 232
extremal query, 192
F
failures, handling,
feasibility analysis, 144
feature creep, 480
FETCH statement, 354
Fifth Normal Form (5NF),
file server, defined, 724
files.
filter ratio, 198
filtering, selective, 703
Final Documentation and Testing phase, in System Development Life Cycle (SDLC),
finalizing documentation, 149
finding
hotspots, 693
people for database management system (DBMS)
about, 152
immediate supervisor, 152
standards organization,
upper management, 154
users, 153
problem queries,
table rows with keys,
vulnerabilities,
firewall, defined, 724
First Normal Form (1NF),
fixing bugs, 511
flash memory, 662
flat files
data structure for,
defined, 724
FLOAT type, 221
floating-point number, 109
FLOOR function, 235
flow of control statements
about, 517
CASE…END
FOR…DO…END 522
IF…THEN…ELSE…END 518
LOOP…END 520
REPEAT…UNTIL…END
WHILE…DO…END 521
FLWOR expressions
about,
for clause,
let clause,
order by clause, 611
return clause,
where clause, 610
following paths, with SQL queries, 635
for clause,
FOR…DO…END FOR statement, 522
foreign key
constraints,
defined, 724
forest, defined, 724
forms, adding data with,
Fourth Normal Form (4NF),
frequency, of backups, 369
FROM clause,
front end
as component of DBMS, 137
defined, 724
full backups, 369
full outer join,
full table scans
about, 189
indexed table access compared with,
indexes compared with, 197
functional dependencies
defined, 724
in relational database model, 67
in relational models,
functionality (XQuery),
functions
about, 223
aggregate
constructor, 121
datetime value functions,
mutator, 121
numeric value functions
about, 231
234
235
232
234
232
235
235
236
logarithmic functions, 234
234
235
235
trigonometric functions, 234
236
polymorphic table functions, 237
set functions
about,
226
225
226
225
225
226
SQL
about, 566
571
569
568
567
567
570
string value functions
about, 227
230
229
230
230
SUBSTRING 228
SUBSTRING 228
230
229
G
GET DIAGNOSTICS statement, 444
GET parameter,
GRANT ALL statement,
GRANT DELETE statement, 424
GRANT INSERT statement, 424
GRANT OPTION FOR clause, 429
GRANT SELECT statement, 424
GRANT statement, 429
GRANT UPDATE statement, 428
GRANTED BY clause, 430
grantee, 422
granting
access privilege roles, 430
access privileges
about,
adding data, 424
changing data,
defining new data types, 427
delegation, 428
deleting data, 424
executing SQL statements, 427
GRANT ALL statement,
referencing data in other tables,
responding to events, 427
using database facilities, 426
viewing data,
granularity, locking and, 414
graph databases. graph databases
graphical user interface (GUI), defined, 724
GREATEST function, 235
GROUP BY clause
about,
correlated subqueries in,
grouping query, 193
groups, in relational database model, 66
H
hackers, 511
handler actions/effects, handling,
handler declarations, handling, 435
handlers, 538
hard disk,
hard disk drives, 691
hardware
adding,
bottlenecks and, 687
maximizing,
hash join, 704
hash structures, 194
HAVING clause
about,
correlated subqueries in,
in queries,
header area,
hierarchical database model, 725
hierarchical storage,
hierarchy, of relational databases, 71
higher normal forms,
holding cursors, 351
host variables
declaring, 469
defined, 725
hot spots
cooling, 415
locating, 693
HP Scrwalr, 385
HP WebInspect, 386
humans, data-entry errors and, 393
Hypertext Markup Language (HTML), defined, 725
I
IBM, 21
IBM Security AppScan, 386
icons, explained, 3
ID-dependent entities, 43
identifiers
about, 172
in Entity-Relationship (ER) models,
mapping to XML,
identifying
authorized users
about, 420
roles,
user identifiers, 420
error conditions, 432
stakeholders, 30
IF…THEN…ELSE…END IF statement, 518
immediate supervisor, for DBMS, 152
implementation
of deferrable constraints,
defined, 725
Implementation phase, in System Development Life Cycle (SDLC), 147
implicit mapping,
IMS (Information Management System),
IN predicate
about,
correlated subqueries and,
incremental backups, 369
index pluses and minuses,
index problems
B+ tree indexes, 683
full table scans versus indexed table access,
index pluses and minuses,
index-only queries, 684
Indexed Sequential Access Method (ISAM), 450
indexes
about,
ANSI/ISO standard and, 190
clustering,
composite,
cost of, 654
creating, 190
data structures used for,
defined, 725
effect of on join performance, 196
full table scans compared with, 197
keys and, 210
multicolumn, 652
query type and,
selecting,
selecting type, 654
sparse vs. dense,
table size and, 196
tuning,
index-only queries, 684
inference, testing by, 384
inflexibility, of relational database models, 24
Information Management System (IMS),
information schema, defined, 725
inheritance, 44
inner join, 337
insensitive cursors,
INSERT statement, 641
INSERTCHILDXML function,
inserting XML data, into SQL pseudotables,
insertion anomaly, 725
insertions, partitioning, 414
INSERTXMLBEFORE function,
installing layers of protection
about, 388
alertness, 389
antivirus software, 388
application-layer firewalls, 388
education, 389
exploits,
network-layer firewalls, 388
patches,
vulnerabilities,
INTEGER type, 502
integrity
of databases,
maintaining
about, 182
domain integrity,
entity integrity,
referential integrity,
interactive SQL, 100
interactive Structured Query Language (SQL), 725
interfacing SQL, with procedural language,
International Organization for Standardization (ISO), 190
Internet
about, 370
defined, 725
denial-of-service attacks, 374
phishing scams, 387
ransomware, 374
SQL injection attacks,
Trojan horses,
viruses,
worms, 373
zombie spambots, 387
Internetwork Packet Exchange/Sequenced Packet Exchange (IPX/SPX), defined, 725
interpreting SQLSTATE information,
INTERSECT operator,
intersection relation, 178
interval value expressions,
intervals, 115
interviewing stakeholders,
intranet, defined, 725
IPX/SPX (Internetwork Packet Exchange/Sequenced Packet Exchange), defined, 725
IS JSON predicate,
ISAM (Indexed Sequential Access Method), 450
ISO (International Organization for Standardization), 190
isolation
in ACID test, 396
levels of,
of performance problems, 690
weakening levels of,
items, in SQL/JSON data model,
ITERATE statement,
J
Java
application programming interface (API), 579
applications, 470
Db2 and, 460
defined, 725
MySQL and, 459
Oracle SQL and,
Java DataBase Connectivity (JDBC), 725
Java-based embedded SQL (SQLI), 459
JavaScript, defined, 725
JavaScript Object Notation (JSON)
about, 617
defined, 725
JSON types,
nulls, 629
output clause, 622
SQL:2023 JSON enhancements
about, 629
additional functions for JSON data type,
JSON data type,
SQL/JSON data model
about, 618
items,
parsing JSON, 620
sequences, 619
serializing JSON, 620
SQL/JSON functions
about, 620
constructor functions,
IS JSON predicate,
JSON nulls, 629
query functions,
SQL nulls, 629
SQL/JSON path language, 629
using with SQL,
value expression, 621
JDBC (Java DataBase Connectivity), 725
Jet engine, 450
join conditions, relational operators and,
joins
defined, 725
effect of indexes on performance of, 196
selecting types,
JOINS operator
about, 330
Cartesian product/cross join,
column-name join,
condition join, 335
equi-join,
inner join, 337
natural join,
outer join,
JSON. Object Notation (JSON)
JSON_ARRAY function,
JSON_ARRAYAGG function, 628
JSON_EXISTS function,
JSON_OBJECT function, 626
JSON_OBJECTTAG function, 627
JSON_QUERY function,
JSON_TABLE function, 625
JSON_VALUE function,
K
keys
about, 172
defined, 164
finding table rows with,
indexes and, 210
in relational database model,
in relational models, 159
L
L1 cache, in hierarchical storage, 188
L2 cache, in hierarchical storage, 188
LAN (local-area network), defined, 726
LEAST function, 236
least recently used (LRU) algorithm,
LEAVE statement,
left outer join,
let clause,
Level 1 cache, 663
Level 2 cache, 663
Level 3 cache, 663
levels, of RAID,
LIKE predicate,
limitations, of SQL,
LISTAGG function, 226
literal values,
LN function, 234
local-area network (LAN), defined, 726
locking
about, 409
client performance and, 686
deadlock,
eliminating unneeded locks, 412
granularity, 410
subsystem,
two-phase, 409
log files,
LOG function, 234
LOG10 function, 234
logarithmic functions, 234
logging subsystem, 707
logical connectives
about, 268
defined, 726
269
logical operations, 704
logical request, 11
logs, putting on different disks,
LOOP…END LOOP statement, 520
LOWER function, 229
LPAD function, 230
LRU (least recently used) algorithm,
LTRIM function,
M
magnetic tape, 663
main memory, 663
maintaining
backups,
integrity
about, 182
domain integrity,
entity integrity,
referential integrity,
Maintenance phase, in System Development Life Cycle (SDLC), 150
maintenance programmer documentation, creating, 147
malware, defined, 726
managing
binary relationships,
data with complicated programs,
failures,
lock granularity, 414
null values,
resources
database buffer manager,
disk subsystem,
locking subsystem,
logging subsystem, 707
many-to-many (N:M) relationship,
mapping
attributecentric,
defined, 726
elementcentric,
explicit,
implicit,
schemas,
SQL to XML
about, 557
character sets, 557
creating XML schema for SQL table,
data types, 558
handling null values,
identifiers,
nonpredefined data types,
tables, 564
MariaDB, 63
MATCH clause, 642
MAX function, 225
maximizing hardware,
maximum cardinality, 485
maximum project, 155
measuring performance, with throughput, 412
medium project, 155
Melton, Jim (author)
Querying 116
memory
flash, 662
system hierarchy,
volatile versus nonvolatile,
metadata, defined, 726
methods, 73
Microsoft Access
about,
Access Database Engine, 451
ActiveX Data Objects (ADO), 450
DAO interface, 450
files with .mdb extension, 451
Jet engine, 450
Object Linking and Embedding Database (OLE DB), 451
Open Database Connectivity (ODBC), 451
Visual Basic for Applications (VBA) and,
Microsoft SQL Server,
Microsoft tools, for updating XML data in SQL tables
about, 584
mapping schemas,
OPENXML function,
updategram namespace/keywords,
updategrams, 585
Microsoft Visio, 32
MIN function, 225
minimal project, 155
minimizing traffic, between application and server, 658
minimum cardinality,
MOD function, 234
modification anomaly, 726
modify() method, 584
modifying
data for access privileges,
table structure, 495
tables, 85
modifying clauses
about, 250
GROUP
ORDER
WHERE
about,
ALL predicate,
ANY predicate,
comparison predicates, 253
DISTINCT predicate, 263
EXISTS predicate,
BETWEEN keyword,
IN keyword,
LIKE keyword,
logical connectives,
MATCH predicate,
NOT IN keyword,
NOT LIKE keyword,
NULL predicate,
OVERLAPS predicate, 264
SOME predicate,
UNIQUE predicate, 263
module declarations,
module language, 726
module procedures, 474
modules
defined, 726
Oracle,
SQL
about,
module declarations,
module procedures, 474
modules in Oracle,
stored,
MongoDB, 25
Moore, Gordon (Intel founder), 26
Moore's law,
multicolumn indexes, 652
multipoint query, 191
multiprocessor environments, 680
MULTISET type, 563
multisets
about,
defined, 66
in relational database model, 66
multitable views,
mutator function, 726
MySQL
about, 454
C and, 458
C# and, 458
C++.NET and, 458
Java and, 459
Perl and, 459
PHP and, 459
Python and, 459
website, 63
N
NAMES ARE clause, 104
NASA, 13
NATIONAL CHARACTER LARGE OBJECT type, 112
NATIONAL CHARACTER type, 221
NATIONAL CHARACTER VARYING type, 221
native drivers, 532
natural join,
nested elements, 550
nested queries
defined, 726
tuning statements containing,
nested-loops join,
.NET languages
SQL Server and,
using SQL with other, 472
NetBEUI, defined, 726
network, 692
network database model, 726
network-layer firewalls, 388
N:M (many-to-many) relationship,
node properties, 634
nonexistence, testing for, 305
nonhandled conditions, 437
nonpredefined data types, mapping to XML,
nonprocedural, SQL as, 101
nonprocedural languages,
nonrelational NoSQL model, 25
nonrepeatable read,
nonvolatile memory,
normal forms
about, 161
defined, 726
higher,
normalization
defined, 726
Entity-Relationship (ER) model,
relational models,
simplifying relationships using, 51
NOT EXISTS predicate,
NOT IN predicate,
NOT LIKE predicate,
NOT NULL constraint,
NULL value,
null values
about,
handling,
managing, 125
NULLIF expression,
numeric character references, in XML documents, 553
NUMERIC type, 393
numeric value expressions, 726
numeric value functions
about, 231
234
235
232
234
232
235
235
236
logarithmic functions, 234
234
235
235
trigonometric functions, 234
236
O
object, defined, 726
Object Linking and Embedding Database (OLE DB), 543
object-oriented database model (OODBMS), 25
object-oriented procedural languages,
object-relational database model, 25
obtaining stakeholder buy-in, 31
OCTET_LENGTH function,
ODBC/JDBC versus native drivers,
offline storage, in hierarchical storage, 189
OLE DB (Object Linking and Embedding Database), 543
ON clause, relational operators and, 341
one-to-many (1:N) relationship, 176
one-to-one (1:1) relationship, 36
OODBMS (object-oriented database model), 25
Open Database Connectivity (ODBC)
about,
application,
data sources, 537
defined, 726
driver manager, 535
drivers,
open source, 62
OPEN statement, 353
opening cursors,
OPENXML function,
operating
cursors on single rows
about,
absolute versus relative fetches, 355
deleting rows, 355
FETCH syntax, 354
updating rows, 355
on data with Data Manipulation Language (DML)
about, 86
adding data to tables,
deleting data from tables,
retrieving data from databases,
updating data in tables,
updating views,
operating modes, contrasting, 465
operating system, tuning,
operator error, as a threat, 363
optimizing
batch transactions, 670
code placement on hard disks, 676
data placement on hard disks, 676
OR logical connective,
Oracle
about, 54
defined, 726
modules in,
tools for updating XML data in SQL tables
about, 579
website, 62
Oracle 23c, 453
Oracle Database, 62
Oracle Pro*C,
Oracle SQL, Java and,
Oracle Tuning Advisor, 701
order by clause, 611
ORDER BY clause
about,
ordering query result set using,
in queries,
ordering query,
ordering query result set cursors,
organization growth, planning for,
origins
of SQL,
of XQuery, 600
outer join
about, 337
full,
left,
right, 340
OVERLAPS predicate, 264
OVERLAY function,
P
page buffer, 660
page locks, 410
page replacement algorithm,
page usage factor, 675
pages, in indexes,
parameter, defined, 727
parsing JSON, 620
partitioning
about,
insertions, 414
passing clause, 621
patches,
path expression, 621
paths, in relational database model, 73
PCP (Priority Ceiling Protocol), 673
people, finding for database management system (DBMS)
about, 152
immediate supervisor, 152
standards organization,
upper management, 154
users, 153
performance
about,
bottlenecks
of databases,
isolating problems, 690
measuring with throughput, 412
of top-down analysis,
Perl
about, 470
MySQL and, 459
personal database, defined, 727
PGQL (Property Graph Query Language), See
phantom read, 400
phishing scams, 727
PHP
about, 471
MySQL and, 459
physical design considerations,
physical operations, 704
PIP (Priority Inheritance Protocol), 673
planning
databases,
for organization growth,
platform instability, as a threat, 362
point query, 191
polymorphic table functions, 237
POSITION function,
PostgreSQL, 453
POWER function, 235
precision, defined, 727
precompiled queries, bottlenecks and, 689
precompiling frequently used queries, 658
predicates
defined, 727
XML
572
572
prefix match query, 192
preserving database integrity, with transactions,
primary key, 727
PRIMARY KEY constraint, 183
Priority Ceiling Protocol (PCP), 673
Priority Inheritance Protocol (PIP), 673
priority inversion, 673
priority-based scheduling, 672
private attribute, 120
privileges. See privileges
procedural capabilities and, 528
in relational database model,
procedural capabilities
about,
compound statements
about,
assignment, 517
atomicity,
cursors,
variables, 516
embedding SQL statements, 514
flow of control statements
about, 517
CASE…END
FOR…DO…END 522
IF…THEN…ELSE…END 518
LOOP…END 520
REPEAT…UNTIL…END
WHILE…DO…END 521
privileges, 528
stored functions, 527
stored procedures,
triggers
about,
trigger action time, 526
trigger events,
triggered actions, 526
triggered SQL statement,
using stored modules,
procedural languages
combining SQL with
about, 464
classic procedural languages,
object-oriented procedural languages,
comparing SQL with
about, 462
classic procedural languages,
nonprocedural languages,
object-oriented procedural languages, 463
defined, 727
interfacing SQL with,
procedures (module), 474
processors
about,
speed of, 678
programming errors, 394
project scope
about,
determining,
property graph databases
about,
connecting nodes by multiple edges,
connecting nodes to itself, 635
defined, 727
edge properties, 634
following paths with SQL queries, 635
node properties, 634
SQL/PGQ
about,
adding data to node and edge tables,
building property graph tables,
querying data in graph tables,
Property Graph Query Language (PGQL), See
proposals,
protected attribute, 120
pseudo-random hash function, 194
public, 422
Python, MySQL and, 459
Q
quantified comparison operator, 298
quantified subqueries,
queries
about,
ad hoc, 87
analyzing efficiency of,
data in graph tables,
defined, 727
finding problems,
HAVING clause,
OR logical connective,
ORDER BY clause,
precompiling frequently used, 658
SELECT
temporary tables,
tuning,
query analyzers,
query expression cursors, 347
query functions,
query optimizer, 190
query type, indexes and,
Querying XML (Melton and Buxton), 116
R
RAD (rapid application development) tool, defined, 727
RAID. Array of Independent Disks (RAID)
RAID 0, 366
RAID 1, 367
RAID 5, 367
RAID 10, 367
RAID arrays, 679
RAM (read-only memory), 678
range query, 192
ransomware, 374
rapid application development (RAD) tool, defined, 727
RDBMS (relational database management system), 727
READ
read locks, 409
READ 399
reading SQL Server execution plans,
read-only memory (RAM), 678
REAL type, 221
reconciling conflicting requirements, 31
record, defined, 727
recovery system, tuning,
Redundant Array of Independent Disks (RAID)
about,
levels,
striping,
REF types, 124
reference type, defined, 727
REFERENCING clause, 525
referencing data, in other tables,
referential integrity
about,
defined, 727
MATCH predicate and,
registers, in hierarchical storage, 188
relational database management system (RDBMS), 727
relational database model
about, 65
building
about,
database traditions,
functional dependencies,
keys, 159
relations, 158
catalogs, 72
connections, 72
converting ER models to, 172
functional dependencies, 67
hierarchy of, 71
keys,
multisets, 66
normalizing,
paths, 73
privileges,
relations, 66
routines, 73
schemas, 71
sessions, 72
sets, 66
tables, 66
transactions, 72
users, 70
views,
relational operators
about, 323
ON clause, 341
clustering indexes,
329
join conditions,
JOINS
about, 330
Cartesian product/cross join,
column-name join,
condition join, 335
equi-join,
inner join, 337
natural join,
outer join,
UNION
about,
UNION 326
UNION
WHERE clause, 341
relations. See
about, 157
defined, 727
intersection, 178
with .mdb extension, 451
in relational database model, 66
in relational models, 158
SQL to XML, 554
relationships
binary,
complex,
degree-three,
degree-two,
entities and,
in Entity-Relationship (ER) models,
establishing between tables,
relative fetches, 355
Remember icon, 3
remote databases
about, 531
application requests,
native drivers, 532
Open Database Connectivity (ODBC)
about,
application,
data sources, 537
driver manager, 535
drivers,
removing tables, 495
REPEATABLE 400
repeating groups, 21
REPEAT…UNTIL…END REPEAT statement,
reports, developing, 509
requirements, for XQuery, 601
Requirements phase, in System Development Life Cycle (SDLC),
reserved words
about, 105
defined, 727
SQL:2023,
resources, managing
database buffer manager,
disk subsystem,
locking subsystem,
logging subsystem, 707
responding, to events, 427
RESTRICT keyword, 430
result set cursors,
retesting, 512
retrieving
data from databases,
databases,
return clause,
REVOKE statement, 429
revoking
access privilege roles, 430
access privileges,
right outer join, 340
Robie, Jonathan (coder), 600
robust execution plans, 197
roles
access privilege, 430
authorized users,
of SQL, 54
of subqueries
about, 296
correlated subqueries,
returning multiple values,
returning single values,
ROLLBACK statement, 408
rolling back transactions,
round-robin scheduling, 672
routines, in relational database model, 73
row locks, 410
row pattern recognition, defined, 728
ROW type,
row value expressions, 728
row values, 220
row-level trigger, 526
rows
adding blocks of, to tables, 506
defined, 728
deleting, 355
transferring between tables,
updating, 355
RPAD function, 230
RTRIM function,
Rule Zero, 24
S
sample data, testing systems with,
sample database,
Saturn V, 13
scale
defined, 728
of numbers, 108
scheduling
DDL statements, 414
threads,
SCHEMA clause, 473
schema owner, defined, 728
schemas
creating,
defined, 728
mapping,
in relational database model, 71
XML (XSD),
screen forms, developing, 509
script kiddies, 372
scrolling cursors, 351
Second Normal Form (2NF), 161
security, Data Control Language (DCL)
about, 95
granting access privileges,
preserving database integrity with transactions,
revoking access privileges, 96
SELECT in queries,
SELECT privilege, 564
SELECT statement, 641
selecting
columns to index, 651
index type, 654
indexes,
join types,
selective filtering, 703
sensitive cursors,
separating user transactions, from transactions,
SEQUEL, 728
sequences, in SQL/JSON data model, 619
serializability, enforcing with timestamps,
serializing JSON, 620
sessions, in relational database model, 72
SESSION_USER variable, 223
SET clause, for updating data in tables, 90
set functions
about,
226
225
defined, 728
226
225
225
subqueries and, 300
226
SET statement, 527
set theory, 66
SET TRANSACTION statement, 439
sets, in relational database model, 66
setting
checkpoints,
constraints
about, 209
column constraints,
table constraints, 210
shared locks, 409
shared-disk architecture, 680
shared-nothing architecture, 680
shortening transactions,
simplifying relationships, using normalization, 51
SIN function, 234
single-table views,
SINH function, 234
SIO/IEC SQL standard, 55
slow query, 682
slow update,
SMALLINT type, 221
social engineering, 372
solid state disk (SSD)
about, 679
in hierarchical storage,
SOME predicate,
sort-merge join, 704
source type, 120
sparse index,
special variables,
specifying columns,
speed
of hard disks,
of processors, 678
spoofing, 372
SQL Anywhere, 453
SQL Map, 385
SQL Server
.NET languages and,
reading execution plans,
SQL Server Profiler,
SQL:2023
JSON enhancements
about, 629
additional functions for JSON data type,
JSON data type,
reserved words,
SQL/DS (Structured Query Language/Data System), defined, 728
SQLI (Java-based embedded SQL), 459
SQL-invoked routine, 73
SQL/JSON
array, 619
data model
about, 618
items,
parsing JSON, 620
sequences, 619
serializing JSON, 620
functions
about, 620
constructor functions,
IS JSON predicate,
JSON nulls, 629
query functions,
SQL nulls, 629
null, 619
object, 619
path language, 629
scalar, 619
SQL/PGQ
about,
adding data to node and edge tables,
building property graph tables,
defined, 728
querying data in graph tables,
SQL-session user identifier, 420
SQLSTATE statement,
SQRT function, 235
square brackets ([]), 505
SSD. state disk (SSD)
stakeholders
defined,
identifying, 30
interviewing,
obtaining buy-in from, 31
standards organization, for DBMS,
starting transactions,
statement handle, 538
Statement of Requirements, 479
statement-level trigger, 526
storage engines, 62
stored functions, procedural capabilities and, 527
stored procedures, procedural capabilities and,
string value expressions, 728
string value functions
about, 227
230
229
230
230
SUBSTRING 228
SUBSTRING 228
230
229
striping,
structure
of databases, 147
modifying for tables, 215
Structured Query Language (SQL)
about, 53
creating databases with
about, 203
building tables,
deleting tables, 215
ensuring data validity with domains,
establishing relationships between tables,
indexes and, 210
keys and, 210
modifying table structure, 215
planning,
setting constraints,
DBMS implementations
about, 56
IBM DB2, 61
Microsoft Access,
Microsoft SQL Server, 61
MySQL,
Oracle Database, 62
PostgreSQL, 63
Sybase SQL Anywhere, 62
defined, 728
embedding in applications
about,
Java applications, 470
Oracle Pro*C applications,
Perl applications, 470
PHP applications, 471
using SQL with other .NET languages, 472
using SQL with Visual Basic .NET applications, 471
entering data with, 505
following paths with SQL queries, 635
injection attacks, 728
interfacing with procedural language,
limitations of,
mapping to XML
about, 557
character sets, 557
creating XML schema for SQL table,
data types, 558
handling null values,
identifiers,
nonpredefined data types,
tables, 564
nulls, 629
origins of,
relating to XML, 554
role of, 54
routine, 73
SIO/IEC SQL standard, 55
XQuery
Structured Query Language (SQL) functions
about, 566
571
569
568
567
567
570
Structured Query Language (SQL) modules
about,
module declarations,
module procedures, 474
modules in Oracle,
Structured Query Language (SQL) statements
embedding, 514
executing
about,
combining with host language, 101
embedded SQL,
interactive SQL, 100
module language,
Structured Query Language (SQL) tables
about, 575
creating to hold XML data,
inserting XML data into,
Microsoft's tools for updating XML data in
about, 584
mapping schemas,
updategram namespace/keywords,
updategrams, 585
Oracle's tools for updating XML data in
about, 579
updating XML documents, 578
Structured Query Language/Data System (SQL/DS), defined, 728
structured types, 728
subqueries
about, 295
defined, 728
in DELETE statement,
in INSERT statement,
role of
about, 296
correlated subqueries,
returning multiple values,
returning single values,
tuning
considerations for statements containing nested queries,
correlated subqueries,
in UPDATE statement,
SUBSTRING (FROM) function, 228
SUBSTRING (SIMILAR) function, 228
subtype entities, 728
subtypes, 122
SUM function, 307
supertype entities, 729
supertypes, 122
Sybase SQL Anywhere, 62
System Development Life Cycle (SDLC)
about,
Definition phase, 139
Design phase,
Evaluation phase,
Final Documentation and Testing phase,
Implementation phase, 147
Maintenance phase, 150
Requirements phase,
system failures, 96
SYSTEM_USER variable, 223
T
table constraints, 492
table locks, 410
tables. See
adding
blocks of rows to, 506
constraints to existing, 444
data to,
creating
about, 502
finding table rows with keys,
using CREATE TABLE statement,
defined, 729
deleting
about, 215
data from,
establishing relationships between,
finding row with keys,
indexes and size of, 196
modifying
about, 85
structure of, 495
in relational database model, 71
removing, 495
temporary,
transferring
columns between,
rows between,
updating
data in,
rows, 349
TAN function, 234
TANH function, 234
Taylor, Allen G. (author)
Database Development For 363
TCP/IP (Transmission Control Protocol/Internet Protocol), defined, 729
Technical Stuff icon, 3
techniques, for Entity-Relationship models,
teleprocessing system, defined, 729
temporary tables, in queries,
testing
ACID test,
applications,
beta, 511
for existence,
by inference, 384
for nonexistence, 305
retesting, 512
systems with sample data,
Third Normal Form (3NF), 161
threads, scheduling,
threats
about,
ACID test,
backups
about, 368
frequency, 369
full versus incremental, 369
importance of,
maintenance,
concurrent-operation conflicts,
database design errors, 393
database design flaws, 363
data-entry errors
about, 392
constraints,
data types, 392
humans, 393
enforcing serializability with timestamps,
equipment failure,
installing layers of protection
about, 388
alertness, 389
antivirus software, 388
application-layer firewalls, 388
education, 389
exploits,
network-layer firewalls, 388
patches,
vulnerabilities,
Internet
about, 370
denial-of-service attacks, 374
phishing scams, 387
ransomware, 374
SQL injection attacks,
Trojan horses,
viruses,
worms, 373
zombie spambots, 387
locking
about, 409
deadlock,
granularity, 410
two-phase, 409
operator error, 363
platform instability, 362
programming errors, 394
Redundant Array of Independent Disks (RAID)
about,
levels,
striping,
transactions
about, 397
committing, 401
implementing deferrable constraints,
rolling back,
SET TRANSACTION statement,
starting,
tuning locks
about,
controlling lock granularity, 414
cooling hot spots, 415
eliminating unneeded locks, 412
measuring performance with throughput, 412
partitioning insertions, 414
scheduling DDL statements, 414
shortening transactions,
tuning deadlock interval, 415
weakening isolation levels,
tuning recovery system, 418
three-option proposal, 155
throughput, measuring performance with, 412
tightly coupled architecture, 680
TIME WITH TIME ZONE type, 221
TIME WITHOUT TIME ZONE type, 124
TIMESTAMP WITHOUT TIME ZONE type, 124
timestamps, enforcing serializability with,
TIMSETAMP WITH TIME ZONE type, 221
Tip icon, 3
tools, for vulnerability testing,
top down design,
top-down analysis, performing,
traffic, minimizing between application and server, 658
transactions
defined, 729
operating with
about, 397
committing, 401
implementing deferrable constraints,
rolling back,
SET TRANSACTION statement,
starting,
preserving database integrity with,
putting on different disks,
in relational database model, 72
separating user transactions from,
shortening,
tuning, 657
Transact-SQL, 458
transferring
columns between tables,
rows between tables,
transforming models,
transitive dependency, defined, 729
TRANSLATE function, 230
translating Entity-Relationship models into relational models, 51
translation table, defined, 729
Transmission Control Protocol/Internet Protocol (TCP/IP), defined, 729
tree-structured organization, 15
trigger action time, 526
trigger events,
triggered actions, 526
triggered SQL statement,
triggers
about,
defined, 729
trigger action time, 526
trigger events,
triggered actions, 526
triggered SQL statement,
trigonometric functions, 234
TRIM function, 229
Trojan horse, 729
tuning
correlated subqueries,
databases
about,
analyzing workload,
minimizing traffic between application and server, 658
physical design considerations,
precompiling frequently used queries, 658
selecting indexes,
separating user transactions from transactions,
tuning indexes,
tuning queries,
tuning transactions, 657
deadlock interval, 415
environment
about,
adding hardware,
handling failures,
maximizing hardware,
multiprocessor environments, 680
tuning operating system,
tuning recovery system,
indexes,
locks
about,
controlling lock granularity, 414
cooling hot spots, 415
eliminating unneeded locks, 412
measuring performance with throughput, 412
partitioning insertions, 414
scheduling DDL statements, 414
shortening transactions,
tuning deadlock interval, 415
weakening isolation levels,
operating system,
queries,
recovery system,
statements containing nested queries,
transactions, 657
write operations,
tuple stream, 609
tuples, 157
Turing-complete,
two-phase locking, 409
typing data, 88
U
UDFs (user-defined functions), 460
UDTs. types (UDTs)
UNDER privilege, 427
underscore (_), 257
UNION ALL operator, 326
UNION CORRESPONDING operator,
UNION operator
about, 508
UNION 326
UNION
UNIQUE constraint,
universal Turing machine, 55
update anomaly, defined, 729
update functions, 579
UPDATE statement,
updategram
about, 585
namespace/keywords,
UPDATEXML function,
updating
data in tables,
rows, 355
table rows, 349
views,
XML documents, 578
UPPER function,
upper management, for DBMS, 154
USAGE privilege, 427
usage scenarios, in XQuery,
user documentation, creating, 147
user identifiers, 420
user interactions, 96
user interfaces
connecting to databases,
designing,
user-defined functions (UDFs), 460
user-defined types (UDTs)
about, 426
defined, 729
distinct types,
structured types,
users
classifying, 422
as component of DBMS, 138
data models for, 141
for DBMS, 153
in relational database model, 70
V
VALID predicate,
value expression, defined, 729
value functions, 729
values
entering
about,
column values, 220
literal values,
row values, 220
special variables,
variables, 222
subqueries
returning multiple,
returning single,
VAR CHAR type, 221
variables
about, 222
compound statements and, 516
VBA (Visual Basic for Applications),
views
creating,
data for access privileges,
defined, 729
multitable,
in relational database model,
single-table,
updating,
virtual table, defined, 729
viruses, 729
Visual Basic for Applications (VBA),
Visual Basic .NET applications, using SQL with, 471
volatile memory,
vulnerabilities,
W
Warning icon, 3
weakening isolation levels,
Web Developer,
websites
Cheat Sheet, 3
HP Scrwalr, 385
HP WebInspect, 385
IBM Security AppScan, 385
MariaDB, 63
MySQL, 63
Oracle, 62
SQL Map, 385
Web Developer, 384
World Wide Web Consortium, 602
WHENEVER clause,
where clause, 610
WHERE clause
about, 508
ALL predicate,
ANY predicate,
comparison predicates, 253
deleting data from tables using,
DISTINCT predicate, 263
EXISTS predicate,
indexes and,
LIKE predicate,
logical connectives,
MATCH predicate,
NOT IN predicate,
NOT LIKE predicate,
NULL predicate,
OVERLAPS predicate, 264
BETWEEN predicate,
IN predicate,
relational operators and, 341
SOME predicate,
UNIQUE predicate, 263
for updating data in tables,
WHILE…DO…END WHILE statement, 521
WIDTH_BUCKET function, 236
WITH ADMIN OPTION clause, 430
WITH GRANT OPTION clause, 430
word table, 66
words, reserved, 105
workgroup database, defined, 730
workload, analyzing,
World Wide Web, 730
worms, 730
write operations, tuning,
write-ahead log protocol,
write-back, 677
write-through, 677
X
XML Data Reduced (XDR),
XML Schema Definition (XSD),
XML type, 116
XMLAGG function,
XMLCAST function, 571
XMLCOMMENT function, 569
XMLCONCAT function, 568
XMLELEMENT function, 567
XMLEXISTS predicate, 572
XMLFOREST function, 567
XMLPARSE function,
XMLPI function, 570
XMLQUERY function,
XMLTABLE function,
XQuery
about, 600
functionality,
origin of, 600
requirements for, 601
SQL
usage scenarios,
XSD (XML Schema Definition),
Z
zombie spambots, 730
