A
access-arm mechanism, 203
access methods, 207–218. See also index
file organizations and, 207–218
sequential, 207, 210, 213, 217
access path plan, 70
accessing data, problems in, 12–13
active data dictionaries, 284–286. See also passive dictionaries
definitions, 284
distinctions, 284
relationships, 286
uses and users, 286
Advance Auto Parts, 69
aggregated data, 340
alternate key, 110
Amazon.com, 3–4
Analytical Engine, 7
anomalies data, 55
anti-virus software, 301
application characteristics, 218, 220
Application Program Interface (API), 373
application servers, 318
arbitration, 288
associative entity, in M-M binary relationship, 27
asymmetric data encryption, 300
columns, 108
creating uniqueness with, 20, 28
data normalization and, 157–158, 174
data normalization examples, 185–189
domain of values, 112, 142, 144
keys and, 109
physical database design, 97, 199–237
unique, 20
ATTRIBUTES table, 283
audio clips, 373
availability, database, 374, 375–376
AVG operator, 81
B
Babbage, Charles, 7
backup copies and journals, 303
importance, 303
balance sheet, 6
Baptist Memorial Health care, 378–379
bartering, 5
base table, 70
basic SELECT format, 70
before and after image log, 303
bill of materials, 29, 143–144, 165
Binary File (BFILE), 374
binary large objects (BLOBs), 263, 374
Binary LOB (BLOB), 374
converting entities in, 160–164
E-R diagram, 22
many-to-many (M-M) binary relationship, 23–28
one-to-many (1-M) binary relationship, 23–25
one-to-one (1-1) binary relationship, 23, 25
biometric systems, 297
Black & Decker, 107
block of logical records, 206
breaches, data security, 294
types, 294
browsers, 369
C
calculating devices, 9
cardinality, in binary relationships, 23–24
cascade delete rule, 152
case-based learning, 358
census, 8
centralized database, 322
change log, 303
Character LOB (CLOB), 374
checkpoint, 306
class, 251
class diagram, 251
client/server database, 315–321
application servers, 318
database server, 318
file server approach, 318
two-tiered client/server arrangement, 318–319
client/server system, 368
clustering, 376
clustering files, 225
Codd, Edgar F. “Ted”, 105
cold sites, 307
collision, 216
column (field), 108
Common Gateway Interface (CGI), 373
compact disk (CD), 11
comparisons, 98
competitive advantage, 12
complex relationships, 251–260
General Hardware Co. Class Diagram, 256
Good Reading Bookstores Class Diagram, 256–259
inheritance of attributes, 253–254
inheritance of operations, 254–255
Lucky Rent-A-Vehicle Class Diagram, 260–261
World Music Association class diagram, 259
Computer-Aided Restoration of Electric Service (CARES), 44
Computer-Aided Software Engineering (CASE), 287
computer security issue, 59
computer viruses, 296
concurrency control, 291, 308–311
in distributed databases, 325–327
importance of, 308
resource usage matrix, 310
concurrency problem, 59
Contact Management and lead Tracking System, 249–50
controlled access (passwords and privileges), 297–299
COUNT operator, 82
CREATE TABLE command, 191
CREATE VIEW command, 192
Customer Information System, 44
customer relationship management systems (CRMs), 292–293
D
data access, unauthorized, 294
decentralized environment, managing data in, 274
externally acquired databases, managing, 273
operational management of data, 273
data analyst, 274
data before database management, 43–48
attribute, 45
entity, 45
entity set, 45
field, 45
record, 45
storing and retrieving data, basic concepts in, 46–48
apparently incorrect data, 356
impossible/out-of-range data, 356
missing data, 353
possible misspelling, 355
data communications, intercepting, 295
computer security, 59
concurrency problem, 59
data independence, 60
data coordination, 274–275, 288
data definition language (DDL), 68
data dependence, 60
data dictionaries. See dictionaries, data
data encryption 299
data enrichment 353
into XML, 379–381. See also under Extensible Markup Language (XML)
data independence 60
data integration 49–56, 127–129
data integrity 50–52, 248, 260
data management. See also Structured Query Language (SQL)
data manipulation, 68, 192–194
in decentralized environment, 274, 288
documenting data environment, 277
responsibility for, 252
data manipulation languages (DMLs) 68
case-based learning, 358
decision trees, 358
genetic algorithm, 358
neural networks, 358
attribute, 20
entity, 20
generalization/specialization, 248, 251–253, 260–262
relationships, 20. See also binary relationships; ternary relationships; unary relationships
unique identifier, 20
data normalization process 158, 174–189
Boyce-Codd normal form, 177
fifth normal form, 177
fourth normal form, 177
Good Reading Bookstores, 186–188
second normal form, 177, 180–182
steps in, 177
third normal form, 177, 182–185
unnormalized data, 178
World Music Association, 188
data ownership 277
data planning 275
physical design techniques and, 218–37
data retrieval 124–129. See also under relational database model
disk storage considerations, 202–6
breaches, 294–296. See also breaches, data security
as operational requirement, 220–221
data storage 7. See also data security
clustering files, 222, 225–227
data relationships, 56–58, 111–124
data repositories, 287
DBMS and, 14–15, 56, 60–63, 68–70, 106, 124, 127, 129, 150–151, 201, 218, 221
derived, 221
hashed files and, 217
Internet security and privacy, 376–378
data structure building with SQL 157, 191–192
data types 373
data volatility 220
data volume 223
data cleaning, 344, 352, 354–356, 361
Good Reading Bookstores, 348–350
World Music Association, question of, 351
database 2
database administration 269–290
database concept 48–60. See also database management system (DBMS)
data integration, 48
data redundancy, 48
datacentric environment, 48
principles of, 48
database connectivity issues 367–373
basic client/server system, 368
stand-alone PC, 368
database control issues 291–313, 374–379. See also backup; concurrency control; data security; disaster recovery; recovery
database management system (DBMS) 2, 14–15, 41–66
definition of, 43
externally-acquired databases, 273
relational catalogs, 98, 287, 298
database performance 200
factors affecting, 200
database persistence 375
database server 318
databases and internet 365–383
database connectivity issues, 367–373. See also individual entry
database control issues, 374–379
expanded set of data types, 373–374
Good Reading Bookstores relational database, 371
data-centric environments 48
decentralized environment, managing data in 274
decision support systems (DSS) 336
decision trees 358
declarative SQL SELECT statement 70
defining associations 175–177, 179–181, 189–190
Cascade, 152
Restrict, 152
deletion anomaly 55
dependent entities 33, 36, 169, 172
functional, 148, 149, 151–155, 157–161
derived data 221
designing databases. See database design
development of data 10
dictionaries, data 281–287. See also active data dictionaries; passive dictionaries
ATTRIBUTES table, 283
relational DBMS catalogs, 287
TABLES table, 283
dimension tables 338, 344–346, 322–325, 349, 359
dimensions 343
directories 296
hot sites, 307
cold sites, 307
disk drives, 11
disk-pack philosophy, 11
disk storage, 202–206. See also under physical database design
structure of, 203
dispersing tables on the LAN 331
DISTINCT operator 79
distributed database/distributed DBMS 321–334. See also distributed joins
centralized database, 322
concurrency control in, 325–327
distributed directory management, 330–331
location transparency, 321
two-phase commit, 327
with maximum data replication, 324
with no data replication, 323
with one complete copy in one city, 325
with targeted data replication, 326
distributed directory management 330–331
division-remainder method 216
documentation 277
domain of values 112
double-entry bookkeeping 6
Drill-Down 357
Driver's License System (Tennessee Department of Safety) 336
DROP TABLE command 191
DROP VIEW command 192
Ducks Unlimited (DU) 201
duplicate databases 306
duplicating tables 233
dynamic backout 306
E
early data problems spawn calculating devices, 7–8
Ecolab, 159
electric-eye devices, 298
electromechanical equipment, 9
electronic commerce, 366
electronic computers, 9
electronic data interchange (EDI), 380
embedded mode, 70
enriched data, 359
enterprise data warehouse (EDW), 341–343
enterprise resource planning (ERP) systems, 49
entity identifier, 118
entity occurrences, 140
entity-relationship diagram. See E-R diagram
entity set 45
equijoin 128
conversions, 158. See also under binary relationships; data normalization process; logical database design
with data normalization, testing tables converted from, 189–191
expanded set of data types 373–374
audio clips, 373
binary file (BFILE), 374
binary LOB (BLOB), 374
character LOB (CLOB), 374
graphic images, 373
National Character LOB (NCLOB), 374
video clips, 373
Extensible Markup Language (XML), data extraction into 379–381
as an independent layer of data definition, 381
Document Type Definition (DTD), 380
for Good Reading Bookstores book, 380
external features, adding 221–222
externally acquired databases, managing 273
F
facts, 45
field, 45
file organizations, 207–218. See also hashed files
file server approach, 318
data redundancy and integration, 48–56
loss or corruption of, 59
terminology of, 106, 108, 250–251
filtering, 79
firewalls, 301
flash drive, 9
foreign keys, 111
substituting, 228
functional dependencies, 175, 177, 190
G
Garment Sortation System, 61–62
Garment Utilization System (GUS), 21
gateway computer, 316
genetic algorithm, 358
geographic information systems (GIS), 373
GRANT command, 298
graphic images, 373
Guest Profile Manager (GPM), 292
H
hacking, 295
hard disk drives, 203
hardware, 13–15, 29, 31, 307, 367
Hasbro, 317
hashing method, 207
HAVING clause, 84
head switching, 206
hierarchical DBMS approach, 60
Analytical Engine, 7
bartering, 5
Census, 8
‘Code of Commerce’, 6
commercial data processing, 9
compact disk (CD), 11
data storage means, 7
disk drives, 11
double-entry bookkeeping, 6
early data problems spawn calculating devices, 7–8
effect of Crusades, 6
electronic computers, 9
fourteenth century, 6
late 1800s, 8
late thirteenth centuries, 6
magnetic tape concept, 10
modern data storage media, 9–11
punched cards, 7
punched paper tape, 9
seventeenth century, 7
Hnedak Bobo Group (HBG), 249
home page, 370
horizontal partitioning, 226
hot sites, 307
HyperText Markup Language (HTML), 379
Hypertext Transfer Protocol (HTTP), 372
I
IMAGE data type, 303
as a competitive weapon, 12
as new corporate resource, 13–14
creating an index with SQL, 215
indexed-sequential file, 210
Information Management System (IMS), 62
information processing, 8
information systems environment, today's data in, 12–15
accessing data, problems in, 12–13
data for competitive advantage, 12
challenging factors, 13
storing data, problems in, 12–13
information theft, 13, 42, 59, 220
Informix Universal Server, 374
inheritance
insert rules 151
insertion anomaly 55
Integrated Data Management Store (IDMS) 62
integrated queries 225
integrated software 273
integrated, data as 339
International Business Machines Corporation (IBM) 8
internet 365–383. See also databases and internet
Internet Service Provider (ISP) 370
in binary relationships, 25–31
data normalization and, 158
in M-M binary relationship, 25–26
nonkey attributes and, 175, 179, 180
in ternary relationships, 31–37
J
Java Database Connectivity (JDBC), 373
Join operator, 127
JPEG data type, 374
K
key fields, 45
keys. See candidate keys; foreign keys; primary keys
L
large object (LOB) data types, 374
load balancing, 376
local-area network (LAN), 316
local autonomy, 322
location transparency, 321
logical database design, 157–198
converting E-R diagrams into relational tables, 158–174
data normalization process, 174–189
E-R diagram conversion logical design technique, 172
General Hardware Co. Database, designing, 166–170
Good Reading Bookstores database, designing, 170–171
Lucky Rent-A-Car Database, designing, 173–174
manipulating the data with SQL, 192–193
testing tables converted
World Music Association database, designing, 171–173
logical design technique, for E-R diagram conversion, 172
logical records, 206
logical sequential access, 47
logical view, 223
logs, database, 303
change log, 303
transaction log, 303
M
magnetic disk, 11
malicious mischief, 294
manageable resource, data as, 48–49
corporate resource, 49
software utility, 49
manugistics, 107
many-to-many (M-M) binary relationship, 23–28, 113, 163–166
associative entity, 27
associative entity SALES, 27
associative entity with intersection data, 27
E-R diagram conversion, 158–174
record deletion and, 150
unary, 29–31, 143–145, 165–166
unique identifiers in, 28, 116
market basket analysis, 358
MAX operator, 82
memory, primary and secondary, 202–203, 206–210
merge-scan join algorithm, 98
message, 262
metadata, 281
data planning issues, 275
data repositories, 287
documentation of, 277
Microsoft Active Server Pages (ASP), 373
middleware, 373
MIN operator, 82
mirrored databases, 306
Mobile Dispatching System (MDS), 44
modality, in binary relationships, 24–25
modern data storage media, 9–11
multidimensional databases, 343
N
National Character LOB (NCLOB), 374
natural join, 128
navigational DBMSs, 62
Neolithic means of record keeping, 5
nested-loop join, 98
Network Cable System (NCS), 270
network DBMS approach, 60, 158
neural networks, 358
non-redundant data, 127
non-volatile, data as, 339
normal forms, 177, 180–181, 183
O
object class, 251
Object Management Group (OMG), 251
object, 250
object/relational database, 263–264
object-oriented database management systems (OODBMS), 60, 247–267. See also complex relationships; encapsulation
encapsulation, 262
object/relational database, 263–264
object-oriented data modeling, 250
relational databases vs., 263–264
occurrence vs. type, 45
one-to-many (1-M) binary relationships, 111, 162–163
E-R diagram conversion, 158–164
record deletion and, 150
one-to-one (1-1) binary relationship, 23, 120–124, 160–162, 164–165
combining tables in, 222, 230–231
E-R diagram conversion, 23, 158–164
unary relationship, 28–29, 164–165
on-line analytic processing (OLAP), 357
drill-down, 357
pivot or rotation, 357
slice, 357
Open Database Connectivity (ODBC), 373
operational management of data, 273
order pipeline system (Amazon.com), 3
ancient Middle East, 4
clay tokens or counters, 4
Neolithic means of record keeping, 5
Susa culture, 5
overflow records, 216
P
Pacioli, Luca, 6
partitioning/fragmentation, 329–330
Parts Delivered Quickly (PDQ) system, 69
Pascal, Blaise, 7
passive dictionaries, 284–286. See also active data dictionaries
definitions, 284
distinctions, 284
relationships, 286
uses and users, 286
passwords, 298
PeopleSoft, 273
performance monitoring, 278
performance, database, 374–375
personal computer (PC), 106
physical database design, 199–245. See also file organizations
examples
finding and transferring data, steps in, 206
techniques that DO change the logical design, 227–233
techniques that DO NOT change the logical design, 222–227
physical sequential access, 47
pivot or rotation, 357
Plant Planning System, 107
‘platter’, 203
Powers Tabulating Machine Company, 8
Powers, James, 8
data normalization and, 218, 222
primary memory, 202
priorities, application, 218, 220
private-key technique, 300
privileges, 299
procedures, 250
program modification, unauthorized, 294
proxy server, 301
publicity, 277
public-key technique, 300
punched cards, 7
punched paper tape, 9
pure tables, 219
Q
queries
filtering results of, 79
integrated, 54, 62–63, 225. 339
multiple limiting conditions in, 56–57, 90
nonunique search argument, 73, 125–26
optimizers and indexes, 98, 206–15
query cache 375
query mode 70
R
Random Access Memory Accounting Machine (RAMAC), 11
RAW, for multimedia data, 374
reciprocal agreement, 307
record deletion, 150
record keeping, 5
importance, 303
redundant data. See data redundancy reengineering 49
relational algebra 125
relational catalogs 223, 265–266, 276
relational data retrieval 67–103. See also Structured Query Language (SQL)
relational database model 105–156
examples
foreign keys, 111
many-to-many binary relationship, 113–124
one-to-many binary relationship, 111
referential integrity, 150–153
relational terminology, 106–108
relational DBMS approach 60, 62, 287
relational DBMS performance 97
relational OLAP (ROLAP) 357
relational Project Operator 125–127
relational query optimizer 97–99
comparisons, 98
merge-scan join algorithm, 98
nested-loop join, 98
relational DBMS performance, 97
relational query processing, streamlining 129
relational Select operator 125–127
relational tables, E-R diagrams conversion into 158–174
relational terminology 106–108
relations 108
relationships 20
extracting data from, 42, 124–125
splitting tables, 222, 226–227
tables or files as, 108
reorganization 37
repeating groups 231
resource usage matrix 310
response time 219
restrict delete rule 152
sequential access, 47
rollback 305
roll-forward recovery 304
rotation or pivot 357
rotational delay 206
row (record) 108
S
SAS software, 293
scalability, database, 374, 376
screen scrapping technology, 160
search argument, 73
search attributes, 222
second normal form, 177, 180–182
secondary memory, 202–203, 206
Secure Socket Layer (SSL) technology, 300
security and privacy, database, 376–379
security monitoring, 288
seek time, 206
SELECT operator, 85–86, 125–127. See also Structured Query Language (SQL)
access privileges, 299
basic format, 71
command writing strategy, 89–90
relational algebra, 125
sequential access, 47
logical sequential access, 47
physical sequential access, 47
server, 316
server approach, 318
server side, 371
Set-to-Null delete rule, 152–153
shared corporate resource, data as, 271–272
signatures, 301
slice, 357
‘snowflake’ design, 349
software components, Web-to-database connection, 372
software utility, 49
splitting off large text attributes, 227
stand-alone PC, 368
Standard Generalized Markup Language (SGML), 379
star schema, 344
Store Inventory Management System, 380
stored data, reorganizing, 224–226
storing data, problems in, 12–13
Structured Query Language (SQL), 67–103
data structure building with, 191–192
examples
index creation with, 215
SQL query, filtering the results of, 79
SQL select command, data retrieval with, 68–90
SQL SELECT commands, writing strategies, 89–90
subject oriented, data as, 338–339
as alternatives to joins, 87
requirement, 88
SUM operator, 81
supply-chains, 380
symmetric data encryption, 300
synonym pointer, 217
‘synonyms’, 216
System Reliability Monitoring database, 44
T
table splitting into multiple tables, 226–227
TABLES table, 283
Tennessee Department of Safety, 366–367
terminology, relational vs. file, 108
ternary relationships, 31
converting entities in, 166
relational structures for, 146–150
testing tables converted from E-R diagrams with data normalization, 189–191
text attributes, 227
third normal form, 177, 182–185
three-tier approach, 318
TIFF data type, 374
tracks, 204
training personnel, 60
transaction log, 303
transaction processing systems (TPS), 336
transfer time, 206
transitive dependencies, 182, 190–191
Transmission Control Protocol/Internet Protocol (TCP/IP), 372
tuple, 108
two-phase commit, 327
two-tiered client/server arrangement, 318
type vs. occurrence, 45
U
converting entities in, 164–166
E-R diagram conversion examples, 158, 194
one-to-many, 29
relational structures for, 139–150
unauthorized computer access, 295
unauthorized data access, 294
unauthorized data or program modification, 294
Unified Modeling Language (UML), 251
unique attribute, 113
unique identifier, 20
Unisys Corporation, 9
unnormalized data, 178
update anomalies, 55
update rules, 151
usage monitoring, 279
V
Vehicle Service Center (Memphis, TN), 138–139
vertical partitioning, 227
video clips, 373
view, 223
viruses (computer), 59, 296. 301, 376
W
well integrated file, 54
wiretapping, 295
World Wide Web, 369
as a client/server system, 369
X
XML. See under Extensible Markup Language (XML)
18.191.139.42