Index

Activity diagrams, 46–50
control flow icons, 46, 47
database design and, 50
decisions, 46, 47
defined, 34, 46
example, 49
flows, 46, 47
forks, 47–48
joins, 47, 48
nodes, 46, 47
notation description, 46–48
for workflow, 48–50
See also UML diagrams
Aggregate functions, 222–24
Aggregation, 25
composition vs., 41
defined, 25
ER model, 101
illustrated, 25
UML constructs, 41
UML model, 102
AllFusion ERwin Data Modeler, 188–89
advantages, 211
DBMS selection, 199
ER modeling, 194
modeling support, 209
one-to-many relationships, 195
schema generation, 198
See also CASE tools
Armstrong axioms, 122–24
Association rules, 179
Associations, 37–39
binary, 38–39
many-to-many, 39
many-to-many-to-many, 100
one-to-many, 39
one-to-many-to-many, 99
one-to-one, 39
one-to-one-to-many, 98
one-to-one-to-one, 97
reflexive, 37
ternary, 39
See also Relationships
Attributes, 15–16
assignment, 19
attachment, 57
classifying, 57
data types, 214
defined, 15
descriptor, 15
extraneous, elimination, 125
identifier, 15, 16
multivalued, 15, 57
of relationships, 17, 19
ternary relationships, 28
UML notation, 35, 36
Automatic summary tables (AST), 166
Binary associations, 38–39
Binary recursive relationships, 90–92
ER model, 90
many-to-many, 90, 91, 92
one-to-many, 90, 91
one-to-one, 90–91
UML model, 91
See also Relationships
Binary relationships, 85–89
many-to-many, 85, 87, 89, 104
one-to-many, 85, 87, 89
one-to-one, 85, 86, 88
See also Relationships
Binomial multifractal distribution tree, 172
Boyce-Codd normal form (BCNF), 107, 115–16, 118
defined, 115
strength, 115
tables, 132, 133, 144
See also Normal forms
Business intelligence, 147–86
data mining, 178–85
data warehousing, 148–66
defined, 147
OLAP, 166–78
summary, 185
Business system life cycle, 188
Candidate keys, 109, 110
Candidate tables
from ER diagram transformation, 121–22
normalization of, 118–22
primary FDs, 119
secondary FDs, 119
See also Tables
Cardenas’ formula, 170, 171
CASE tools, 1, 187–211
AllFusion ERwin Data Modeler, 188–89, 194, 195, 199, 211
application life cycle tooling
integration, 202–4
basics, 192–96
collaborative support, 200–201
database generation, 196–99
database support, 199–200
data warehouse modeling, 207–9
defined, 187
design compliance checking, 204–5
development cycle, 190
DeZign for Databases, 190
distribution development, 201–2
ER/Studio, 190
introduction, 188–91
key capabilities, 191–92
low-end, 192
PowerDesigner, 188, 200, 203, 206, 210, 211
QDesigner, 190
Rational Data Architect, 188, 189, 193, 195, 198, 210
reporting, 206–7
script generation, 196
summary, 211
transformation table types, 192–93
value, 190–91
Visible Analyst, 190
XML and, 209–10
Chen notation, 9, 10
Class diagrams, 34–46
constructs illustration, 36
for database design, 37–43
defined, 33–34
example, 43–46
notation, 35–37
packages, 43–46
See also UML diagrams
Classes
associations, 37
defined, 34
entities vs., 34–35
notation, 35
organization, 43
Clustering, 74–81
concepts, 75–76
defined, 74
grouping operations, 76–78
illustrated, 76
results, 81
techniques, 78–81
See also Entity-relationship (ER) model
Collaboration
capabilities, 200–201
concurrency control, 200
support, 200–201
Columns, 2
Comparison operators, 214
Compliance checkers, 204–5
Composition, 36–37
aggregation vs., 41
defined, 37
Computer-aided software engineering tools. See CASE tools
Conceptual data modeling, 3–4, 8–10, 55–66
alternative notations, 20–22
analysis results, 142
diagram for UML, 142
emphasis, 9
notations, 21–22
substeps, 55–56
transformation to SQL, 6, 83–106
See also Data modeling
Concurrency control, 200
Constraints
foreign, 217
not null, 216
primary, 217
unique, 216–17
Data
independence, 2
items, 2
semi-structured, 209–10
summarizing, 165–66
XML, 209–10
Database design
class diagrams for, 37–43
compliance checking, 204–5
knowledge of, 11
logical, 3–6, 53–54, 139–45
physical, 1, 6–8
Database life cycle, 3–8
illustrated, 4
implementation, 8
logical design, 3–6
physical design, 6–8
requirements analysis, 3
step-by-step results, 5, 7
Database management system (DBMS)
DDL, 8
defined, 2
relational, 2
Databases
CASE tools support, 199–200
defined, 2
generating from designs, 196–99
Data definition language (DDL), 8, 190, 196, 213, 215–18
constraints, 216–17
referential trigger actions, 217–18
Data manipulation language (DML), 8, 214, 218–29
aggregate functions, 222–24
basic commands, 220–21
intersection command, 221–22
referential integrity, 227–28
select command, 219
union command, 221–22
update commands, 226–27
usage, 218
views, 228–29
Data mining, 178–85
algorithms, 147, 178
forecasting, 179–81
text mining, 181–85
Data modeling
conceptual, 3–4, 8–10, 55–66
example, 61–66
example illustration, 62–63, 65
knowledge of, 11
Data warehouses, 148–66
architecture, 148
architecture illustration, 150
bus, 164
CASE tool modeling, 207–9
core requirements, 149–51
defined, 148
dimensional data modeling, 152–54
dimensional design process, 156
dimensional modeling example, 156–65
fact tables, 154, 155
integration capabilities, 149–50
life cycle, 151–52
life cycle illustration, 153
logical design, 152–66
OLTP database comparison, 149
overview, 148–52
purpose, 155
snowflake schema, 156, 157
star schema, 154–56
subject areas, 149
DB2 Cube Views, 207
Decisions, 46, 47
Decision support systems (DSSs), 148
Denormalization, 8
Descriptors, 15
Design patterns, 204
DeZign for Databases, 190
Dimensional data modeling, 152–54
example, 156–65
process, 156
See also Data modeling
Dimensions
degenerate, 154
determining, 159
Dimension tables, 154, 160
Double exponential smoothing, 179, 181
Drill-down, 155
Entities, 13–14
classes vs., 34–35
classifying, 56–57
clustering, 74–81
contents, 56–57
defined, 13
dominant, 78
existence, 19–20
intersection, 20
in ternary relationships, 25–26
transformation, 104
weak, 16, 103
Entity clusters
defined, 75
forming, 80
higher-level, 80
root, 75
See also Clustering
Entity instances, 13
Entity keys, 54
Entity-relationship diagram (ERD) notation, 194
Entity-relationship (ER) model, 9, 13–31
advanced constructs, 23–30
aggregation, 25
AllFusion Data Modeler, 194
application to relational database
design, 64–66
with Chen notation, 9, 10
conceptual data model diagram, 141
constructs, 13–22
definition levels, 9
entities, 13–14
entity clustering, 74–81
generalization and aggregation, 101
global schema, 64–66
illustrated, 14
many-to-many binary relationship, 87
one-to-many binary relationship, 87
one-to-one binary relationship, 86
Rational Data Architect, 193
relationships, 14–15, 16–20, 25–29
simple form example, 9–10
subtypes, 23–24
summary, 30
supertypes, 23–24
of views based on requirements, 61–63
ER/Studio, 190
Exclusion constraint, 29
Exclusive OR, 29
Executive information systems (EIS), 148
Existence
defined, 19
mandatory, 20
optional, 20, 64
Exponential smoothing, 179–80
defined, 179
double, 179, 181
triple, 180, 182
See also Forecasting
Extensible Markup Language. See XML
Fact tables
defined, 154
granularity, 155
See also Data warehouses
Fifth (5NF) normal form, 127, 133–37
defined, 133
membership algorithm satisfaction, 134
See also Normal forms
Files, 2
First normal form (1NF), 109
Flows, 46, 47
defined, 46
guard condition, 46
illustrated, 47
Forecasting, 179–81
defined, 179
double exponential smoothing, 179, 181
exponential smoothing, 179–80
least squares line approach, 179
reliability, 180
triple exponential smoothing, 180, 182
See also Data mining
Foreign key constraints, 217
Forks, 46, 47
defined, 47–48
example, 48–49
illustrated, 47
Fourth (4NF) normal form, 127, 129–33
goal, 129
tables, 131
tables, decomposing, 132–33
See also Normal forms
Functional dependence, 111
Functional dependencies (FDs)
defined, 6
in n-ary relationships, 29
primary, 118, 119
secondary, 118, 119
Generalization, 23
completeness constraint, 24
defined, 37
disjointness constraint, 24
ER model, 101
hierarchies, 24, 57–58
UML constructs, 40
UML model, 102
Global schema, 3
Grouping operations, 76–78
application, 77–78
defined, 76
illustrated, 77
types, 77
Guard condition, 46–47
HRU, 173–75
Hypercube lattice structure, 173
IDEFIX notation, 21
Identifiers
defined, 15
internal, 16
Inclusive OR, 29
Industry Warehouse Studio (IWS), 207, 208
Information Engineering Workbench (IEW), 20
Informix Data Server (IDS), 211
Intersection command, 221–22
Intersection entities, 20
Join dependencies (JDs), 133, 136
Joins, 46, 47
defined, 48
example, 49–50
illustrated, 47
SQL, 224–26
Keys
candidate, 109, 110
entity, 54
equivalent, merge, 126
primary, 42, 109
superkeys, 109, 123–24
Logical design, 3–6, 53–54
CASE tools for, 187–211
conceptual data model diagram, 141, 142
conversion, 197–98
data warehouses, 152–66
example, 139–45
problems, 140
requirements specification, 139–40
Logical model, 6
Logical operators, 214
Mandatory existence, 20
Many-to-many relationships, 14
binary, 85, 87, 89
binary recursive, 90, 91, 92
defined, 18
transformation, 104
See also Relationships
Many-to-many-to-many ternary associations, 100
Many-to-many-to-many ternary relationships, 96, 137
Materialized views, 167
metadata, 177
selection of, 173–76
Measures, 159
Multidimensional databases (MDDs), 151
Multiplicities
illustrated, 38
many-to-many, 39
one-to-many, 39
one-to-one, 39
Multivalued attributes, 15, 57
Multivalued dependencies (MVDs), 127–29
defined, 127–28
inference rules, 129
nontrivial, elimination, 129
N-ary relationships, 28–29, 92–101
ER model, 93–96
UML, 42, 97–100
variations, 92
Nodes, 46, 47
Nonredundant cover
partitioning of, 125–26
search for, 125
Normal forms
BCNF, 107, 115–16, 118
defined, 108
fifth (5NF), 127, 133–37
first (1NF), 109
fourth (4NF), 127, 129–33
second (2NF), 111–13
third (3NF), 113–15, 118
Normalization, 6, 107–38
accomplishment, 108–9
candidate tables derived from ER
diagrams, 118–22
defined, 108
denormalization, 8
fundamentals, 107–16
Normalized tables
design, 116–18
minimum set, 127
Not null constraints, 216
Objects
constraint-related, 78
defined, 34
dominant, 78
One-to-many relationships, 14
binary, 85, 87, 89
binary recursive, 90, 91
defined, 18
See also Relationships
One-to-many-to-many ternary associations, 99
One-to-many-to-many ternary relationships, 95
One-to-one relationships, 14
binary, 85, 86, 88
binary recursive, 90–91
defined, 18
See also Relationships
One-to-one-to-many ternary associations, 98
One-to-one-to-many ternary relationships, 94
One-to-one-to-one ternary associations, 97
One-to-one-to-one ternary relationships, 93
Online Analytical Processing (OLAP), 147, 166–78
applications, 8
defined, 166
materialized view selection, 173–76
optimization, 169, 170
overview, 169–70
query optimization, 177–78
view maintenance, 176–77
views, 166, 170–72, 178
Operations
defined, 35
notation, 35, 36
Optional existence, 20, 64
Packages, 43–46
contents, expanding, 45
defined, 43
relationships, 44
Physical design, 6–8
Physical model, 6
Polynomial Greedy Algorithm (PGA), 174–75
PowerBuilder, 203
PowerDesigner, 188, 211
merge process, 200
plug-in to Sybase PowerBuilder, 203
reporting features, 206
XML adoption, 210
Preintegration analysis, 67–68
Primary FDs, 118, 119
candidate table, 119
from ER diagram, 120
See also Functional dependencies (FDs)
Primary keys, 109
constraints, 217
UML constructs, 42
QDesigner, 190
Query optimization, 177–78
Rational Data Architect, 188, 189, 211
automatic computing linkages, 198
ER modeling, 193
property editing, 195
XML adoption, 210
See also CASE tools
Rational MultiSite software, 201, 202
Redundant relationships, 58–60
analyzing, 58
illustrated, 59
See also Relationships
Referential integrity, 30, 227–28
Reflexive associations, 37
Relational databases (RDBs), 150, 151
Relationships, 14–15
attributes, 17, 19
binary, 85–89
binary recursive, 90–92
cardinality, 18
connectivity, 17, 18–19
defined, 14
defining, 58–61
degree, 16–17
entity existence in, 19–20
many-to-many, 14, 18, 39
many-to-many-to-many, 96, 137
multiple, 103
names, 15
n-ary, 28–29, 92–100
one-to-many, 14, 18, 39
one-to-many-to-many, 95
one-to-one, 14, 18, 39
one-to-one-to-many, 94
one-to-one-to-one, 93
packages, 44
redundant, 58–60
roles, 15
ternary, 16, 25–28, 60–61, 92–100
Reporting, 206–7
elements, 206–7
PowerDesigner, 206
See also CASE tools
Requirements analysis, 3, 54–55
defined, 54
objectives, 55
results, 140
Requirements specification, 139–40
Reverse engineering, 6
Roles, 15
Rows, 2
Schemas
commonality, 72
comparison, 68
conceptual integration, 67
conformation, 68–69
diversity, 66
merging, 69
restructuring, 69
structural conflicts, 68, 71
Secondary FDs, 118, 119
candidate table, 119
determining, 120
from requirements specification, 121
See also Functional dependencies (FDs)
Second normal form (2NF), 111–13
functional dependence, 111
tables, 112, 113
See also Normal forms
Select command, 219
Semi-structured data, 209–10
Set operators, 214
Snowflake schema, 156, 157
Software Development Platform, 204
Specialization, 24
SQL, 213–29
advanced value expressions, 214–15
aggregate functions, 222–24
basics, 213–29
comparison operators, 214
conceptual data model
transformation to, 6, 83–106
constructs, 83–85
data types, 214
DDL, 215–18
defined, 213
DML, 218–29
joins, 224–26
logical operators, 214
names, 214
null values, 84–85
object definitions, 215
referential integrity, 227–28
set functions, 214
set operators, 214
subqueries, 224–26
update commands, 226–77
SQL tables, 83, 84
with embedded foreign key, 84
from relationships, 84
with same information content, 84
Star schema, 154–56
defined, 154
dimensions, 162
for estimating process, 160
example, 154
for job costing daily snapshot, 166
for job costing process, 165
for productivity tracking process, 163
queries, 154–55
for scheduling process, 162
See also Data warehouses
Stereotypes, 43
Subqueries, 224–26
Subtypes, 23–24
defined, 23
entities, 24
illustrated, 23
Superkeys
defined, 109
rules, 123–24
Supertypes, 23–24
defined, 23
illustrated, 23
Tables
Boyce-Codd normal form (BCNF), 132, 133, 144
candidate, 118–22
decomposition of, 145
fourth (4NF) normal form, 131, 132–33
merge of, 126
normalized, 6, 116–18
reduction of, 145
second normal form (2NF), 112, 113
third (3NF) normal form, 114, 122–27
Ternary associations, 39
Ternary relationships, 16, 25–28, 92–100
attributes, 28
connectivity, 61
defining, 60
entities in, 25–26
ER model, 93–96
foreign key constraints and, 92
forms, 28
illustrated, 26–27, 60
many-to-many-to-many, 96, 100
with multiple interpretations, 130
one-to-many-to-many, 95, 99
one-to-one-to-many, 94, 98
one-to-one-to-one, 93, 97
requirement, 25
transformation, 105
UML, 97–100
varieties, 92
See also Relationships
Text mining, 181–85
verbatim description, 184
verbatim description information, 184
word mapping, 184–85
See also Data mining
Third (3NF) normal form, 113–15, 118
defined, 114
synthesis algorithm, 124–25
tables, 114
tables, minimum set, 122–27
See also Normal forms
Transformation, 6, 83–106
entity, 104
ER-to-SQL example, 105
many-to-many binary relationship, 104
rules, 83–85
steps, 103–5
summary, 106
ternary relationship, 105
Triple exponential smoothing, 180, 182
UML diagrams
activity, 34, 46–50
class, 33–46
conceptual data model, 142
ER models vs., 33
generalization and aggregation, 102
many-to-many binary relationship, 89
one-to-many binary relationship, 89
one-to-one binary relationship, 88
organization, 51
size, 50
textual descriptions, 50–51
type, 33
Unified Modeling Language (UML), 9, 33–51
aggregation constructs, 41
defined, 33
generalization constructs, 40
n-ary relationship, 42
primary key constructs, 42
relationship types, 38
stereotypes, 43
summary, 51
usage rules, 50–51
See also UML diagrams
Union command, 221–22
Unique constraints, 216–17
Update anomaly, 112
Update commands, 226–27
View integration, 5–6, 66–74
defined, 66
example, 69–74
illustrated, 70, 71, 72–73
merged schema, 72–73
preintegration analysis, 67–68
process, 74
schema comparison, 68
schema conformation, 68–69
schema merge/restructure, 69
techniques, 69
type conflict, 71
Views, 166
coordinates of, 178
creating, 229
dynamic selection, 176
ER modeling based on
requirements, 61–63
exponential explosion, 167–69
size estimation, 170–72
SQL, 228–29
state estimation, 170–72
uses, 228
Visible Analyst, 190
Weak entities, 16, 103
XML, 207, 209–10
defined, 209
documents, 210
schema, 209
standards, 209
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset