Numbers

2VL (2-valued logic), 262

3VL (3-valued logic), 262, 340–346

4VL (4-valued logic), 341, 345

symbols

/* ... */ comments, 144, 315

A

abbreviating consecutive nested quantifiers, 56

absorption, 50

admissible tuples, 82

after row triggers, 249

after statement triggers, 249, 268, 274

aggregate operators, 111

algebraic properties, 47–51

alter table add constraint command, 248, 259, 262

alter table command, 248, 292

always execution model, 268

application locks, 286–289

associativity, 11, 19, 48

attribute constraints, 142, 222, 224, 306

data integrity code and, 246

implementing, 259–262

attribute-value pairs, 81

attribute-value sets, 73, 78, 314, 339

attributes, 78

applicability of values and, 337, 341

renaming, 82, 108

autonomous transactions, 286

AVG (average operator), 111, 235

B

before row triggers, 249

before statement triggers, 249, 268

begin state, 186, 226, 229, 296

binary operators, 14

binary relations, 68–70

BL code (business logic code), 244

Boole, George, 4

Boolean algebra, 4

Borkowski, Ludwik Stefan, 341

bottom-up approach to database universe specification, 141

bound variables, 8

broken promise, 21

business applications, 243–247

business logic code (BL code), 244

business rules, 139

C

canonical forms, 59

cardinality of sets, 29, 81

Cartesian joins, 108

Cartesian product, 39, 68, 191

characterizations, 78, 139, 141

sample database design and, 147–151, 314

set of admissible tuples and, 142

check constraints, 265, 338, 344

attribute constraints and, 259

tuple constraints and, 262

choose operator, 30

classification schema, for data integrity constraints, 142

closed world assumption, 81

closure property, of operators, 75, 102

CNF. See conjunctive normal form

Codd, E. F., 5, 68, 74, 345

comments (/* ... */), 144, 315

COMMIT statement, 266

commit triggers, 300

commutativity, 19, 48

compatible functions, 75

complexity of compound predicates, 9

components of predicates, 9

compound logical expressions, truth tables and, 11

compound predicates, 9, 10, 53

conceptual skeleton. See database skeleton

concurrent transactions, 266, 284, 298

conjunction members (conjuncts), 13, 59

conjunctions, 8, 12, 341

conjunctive normal form (CNF), 59, 63

tuple constraints and, 264

tuple predicates and, 120

connectives, 8–11, 340

constraint specification, 81

constraint validation queries, 245, 268, 282, 289

constraints

deferred checking for, 299–303

RuleGen framework and, 303

contradictions, 17

coordinates of ordered pairs, 38, 68, 305

count operator, 111, 209

courses (CRS), 312

attribute constraints for, 261

table universe for, 321

tuple constraints for, 264

CREATE ASSERTION command, 242

create table command, 248, 255–259

create trigger command, 269

D

data integrity predicates, 185–190

data integrity code (DI code), 245, 247, 308

efficient, 248, 250, 254

execution models for, 265–284

implementing, 247–255

data integrity constraints, 224, 306–307

vs. business rules, 139

classification schema for, 142

data integrity code and, 245

deferred checking for, 299–303

documenting, 140

sample database design and, 158

data integrity predicates, 117–138, 306

data management, relational model for, 5, 68, 81

data manipulation, 221–237, 306

data manipulation code, 243

data retrieval, 199–220, 306

data retrieval code, 243

data values, applicability and, 337, 341

database characterizations, 170, 324

database constraints, 143, 163, 306, 338

data integrity code and, 246

implementing, 291–296

sample database design and violations and, 299

database design (sample), 143–181, 199, 222

database constraints and, 170–181

database predicates and, 170, 175–181

database skeleton and, 144–147

database universe and, 167–181

formal specification of, 311–331

state transition constraints for, 186, 192–196

database designs

defined, 92

implementing, 241–304

specifying formally, 139–184

database management system (DBMS), 93. See also SQL DBMS

database predicates, 117–136, 306

patterns of, 127–136

sample database design and, 170, 175–181

database skeleton, 100, 139, 141

sample database design and, 144–147

specification of, 313

database state spaces, 127

database states, 79, 98, 306

constraints and, 91–93, 266

data integrity constraints and, 143

defined, 93

queries and, 199–201

specifying formally, 98

top-down view of, 141

transactions and, 221–233

database universe, 36, 77, 139, 306

defined, 92

queries and, 199–216

sample database design and, 167–181

specification of, 325–330

specifying formally, 141

transactions and, 221–236

database variable, defined, 92

databases

defined, 93

documenting, 140

Date, Chris, 7

DBMS (database management system), 93. See also SQL DBMS

dbms_lock package, 286

De Brock, Bert, 142, 229

De Morgan Laws, 19, 50

declarative sentences, 5

declarative strategy, for DI code execution, 248, 254

deferrable constraints, 300

deferred checking for data integrity constraints, 299–303

definitions

attribute renaming operator, 109

average operator (AVG), 112

binary relations, 69

cardinality of sets, 29

Cartesian product, 39

compatible functions, 77

database, 93

database design, 92

database management system (DBMS), 93

database state, 93

database universe, 92

database variable, 92

disjoint sets, 34

function composition, 83

functions, 71

generalized product of set functions, 79

headings, 96

join operator, 108

joinable functions, 77

limitations of functions, 76

maximum operator (MAX), 112

minimum operator (MIN), 112

monadic union operator, 36

ordered pairs, 38

partitions, 37

powersets, 35

projection operator, 104

set functions, 78

sets, 24

singletons, 29

specialization predicates, 133

subset requirement predicates, 129

subsets, 30

sum operator, 40

table, 94, 95

table design, 93

table structure, 93

tuple-in-join predicates, 136

unique identification predicates, 128

DELETE statement, 229, 235, 243, 266

delete TE views, 275

delete triggers, 249

on-involved column(s) execution model and, 271

on-involved column(s) plus polarity-of-involved-tables execution model and, 274

on-transition-effect-property execution model and, 277

on-transition-effect-property plus optimized-query execution model and, 282

departments (DEPT), 312

attribute constraints for, 261

table universe for, 319

DI code. See data integrity code

diagonal stroke, 30

difference operators, 101, 103, 305

functions and, 75

sets and, 31–35

SQL and, 213

direct reasoning (modus ponens), 18

directed graphs, 190

disjoint sets, 34

disjunction members (disjuncts), 13, 61

disjunctions, 8, 13, 341

disjunctive normal form (DNF), 60

distributive properties of quantifiers, 57

distributivity, 19, 49

DML statements, 243, 266

deferred checking and, 299

triggers for, 249

DNF (disjunctive normal form), 60

documenting databases/data integrity constraints, 140

domain, of functions, 71

double negation (involution), 19, 50

DUAL table, 268

dyadic operators, 14, 36

dynamic constraints. See state transition constraints

E

elements, 24

embedded procedural strategy, for DI code execution, 251, 254

employee histories (HIST), 312

attribute constraints for, 260

table universe for, 323

employees (EMP), 312

attribute constraints for, 259

table universe for, 315

empty sets, 34–35, 54, 95

end state, 186, 226, 229, 296

entity-relationship diagrams (ERDs), 68

enumerative methods, 25

equality axiom, 39

equivalence, 8, 14

ERDs (entity-relationship diagrams), 68

execution models

for data integrity code, 265–284

deferred checking and, 300

exercises

answers to, 347–365

binary relations, 85

data integrity predicates, 138

database design specification, 183

functions, 85

logic, 22, 64

queries, 219

sets, 42

state transition constraints, 197

tables, 114

transactions, 236

existential quantification, 47, 51–59, 305

aggregation and, 112

nested, 205

expressions, ill-formed, 8

extension operator, 109, 306

external predicates, 79, 139, 146, 314

F

FALSE value, 340, 344

finite sets, 29, 41

flashback queries, 296

foreign key constraints, 312

foreign keys, 291

free variables, 7

Frege, Gottlob, 4

function composition, 82

functional completeness, 16, 62, 262

functions, 67–87, 305

domain/range of, 71

into sets, 72

limitations of, 76

operations on, 75–77

over sets, 70

subsets of, 74

symbols for, 333

G

generalization predicates, 134, 306

generalized product of set functions, 36, 79–81, 97

Gödel, Kurt, 4

H

headings, 96

Hilbert, David, 4

human reasoning, 3, 17

hybrid methods, 27

I

idempotence, 19, 50

identity, 48

identity function, 74

ill-formed expressions, 8

implication, 8, 13, 14

Implication rewrite rules, 19

indirect reasoning (modus tollens), 18

infinite sets, 29

INSERT statement, 228, 232, 243, 266

insert TE views, 274

insert triggers, 249

on-involved column(s) execution model and, 271

on-involved column(s) plus polarity-of-involved-tables execution model and, 274

on-transition-effect-property execution model and, 277

on-transition-effect-property plus optimized-query execution model and, 282

inspection attribute, 134

instantiating predicates, 7

intersection operators, 101–102, 305

functions and, 75

sets and, 31–35

SQL and, 213

involution (double negation), 50

irreducible subsets, 128

J

join attributes, 108

join operator, 106, 203, 306

joinable functions, 75

L

layered approach, 118–119, 141

Leibnitz, Gottfried, 4

limitations of functions, 76

logic, 3–22, 47–65, 305

history of, 4

mathematical symbols and, 334

logical AND, 8

logical connectives, 8–11, 47– 51, 305

functional completeness and, 16

precedence rules for, 10

symbols of, 8

truth tables and, 11–16

logical equivalences, 14, 18–21

logical NOT, 8

logical operators, 9

logical OR, 8

Łukasiewicz, Jan, 340

M

managed employees (MEMP), 312

attribute constraints for, 260

table universe for, 317

tuple constraints for, 264

many-to-one relationships, 144, 312

mathematical symbols, 333

MAX (maximum operator), 112

methods, 25–27

MIN (minimum operator), 112

mnemonic device for operator precedence, “Please Excuse My Dear Aunt Sally,” 10

modus ponens (direct reasoning), 18

modus tollens (indirect reasoning), 18

monadic operators, 14

monadic union operator, 36

multi-tuple constraints, 286

data integrity code and, 245

support for, 274, 279, 295

multiple assignments, SQL and, 303

mutating table error, 267

N

n-ary relations, 68, 74

n-place predicates, 7

names

renaming attributes and, 82, 108

table structures and, 100

naming conventions

for sets/elements, 25

table universe definitions and, 314

NAND operator, 16

negations, 8, 216

combined with universal quantifier, 57

truth tables for, 12, 341

nesting quantifiers, 55

nondeterministic behavior, 267

normal forms, 59–63

NOT operator, NAND operator and, 16

nullable attributes, 337

nullable columns, 257

NULLs, 337–340

number valued operators, 112

O

offerings (OFFR), 312

attribute constraints for, 261

table universe for, 321

tuple constraints for, 264

on-involved column(s) execution model, 271

on-involved column(s) plus polarity-of-involved-tables execution model, 273

on-involved-table execution model, 268

on-transition-effect-property execution model, 274–281

on-transition-effect-property plus optimized-query execution model, 281

deferred checking and, 300

RuleGen framework and, 303

open transactions, 284

operands, 9

operations on tables, 101–113

optional columns, 257

Oracle

data integrity code and, 247–255

dbms_lock package and, 286

implementing database designs in, 241–304

ordered pairs, 38, 68, 305

ordering of predicates, 15

P

parentheses, 10

partial ordering, 15

partitions, 35, 37

parts tables, 95

“Please Excuse My Dear Aunt Sally” mnemonic device, for operator precedence, 10

polarity, table structures and, 273

Polish notation, 341

powerset operator, 157

powersets, 35–38, 305

binary relations and, 69

properties of, 36

precedence rules, 10

predicates, 5, 51–58, 59, 305

compound, 9, 53, 60

data integrity, 117–138

external, 79

instantiating, 7

nesting quantifiers and, 55

normal forms and, 59–63

simple, 9, 59, 61

special categories of, 17

strength/weakness of, 15, 281, 284

predictive methods, 26

projection operator, 104, 306

propositional forms, 10, 19

propositional variables, 10

propositions, 5, 51–58, 59, 305

Q

quantification, 51–59. See also existential quantification; universal quantification

over empty sets, 54

syntax for, 51

quantifiers, 8, 51–59

distributive properties of, 57

negation of, 57

nesting, 55

queries, 199–220, 306

business logic code and, 244

examples of, 201–216

formally specified, 199

R

range, of functions, 71

rBL code (read BL code), 244, 247

reflexivity, 49

registrations (REG), 312

attribute constraints for, 261

table universe for, 322

relational model for data management, 5, 68, 81

relations, 68–70

Remmen, Frans, 142

resources for further reading, 335

database data type definition methodology, 142

formal shorthand expressions, 229

history of mathematics, 5

PL/SQL programming language, 241

RuleGen framework, 304

restriction operator, 104, 306

Reverse Polish Notation (RPN), 341

rewrite rules, 19, 305

2-valued/3-valued logic and, 343

negations and, 217

quantification and, 54, 57–58

row triggers, 249, 267, 275

RPN (Reverse Polish Notation), 341

RuleGen framework, 303

Russell’s paradox, 4

Russell, Bertrand, 4

S

salary grades (GRD), 312

attribute constraints for, 260

table universe for, 319

tuple constraints for, 264

sales representatives (SREP), 312

attribute constraints for, 260

table universe for, 317

SELECT statement, 269

queries and, 201

transactions and, 228

self-referential sentences, 8

semantic optimization, 230

sentences, 5, 8

serializability, 284–290

serialization locks, 290, 298

serialization operator, 38

set functions, 36, 77–82, 305

set operators, 31–35

properties of, 33

SQL and, 213

set specification shorthand, 41

set-theory symbols, 24

sets, 24–28

finite, 29, 41

infinite, 29

methods for, 26

sample database design and, 315

symbols for, 333

symmetric difference operators and, 31–35

theory of, 23–45, 305

shorthand notations, 41, 96

simple predicates, 9, 59, 61

singletons, 29

special case rewrite rules, 19

specialization predicates, 132, 306

SQL DBMS, 227, 232

data integrity code implementation in, 247–255

data integrity constraints and, 221

database variable and, 92

implementing database designs in, 241–304

table structure implementation and, 255–259

transition constraints and, 296

SQL DML statements, transactions and, 221, 227–236

SQL expressions, 199–220

SQL language, multiple assignments and, 303

SQL queries, 243

state transition constraints, 143, 185, 190–196, 306

formally specifying transactions and, 225

specifying for sample database design, 186, 192–196

state transition predicates, 185, 188

state transition universes, 185, 190, 225, 330

statement triggers, 249, 268, 274

static constraints, 143, 223–227

subqueries, 228

subset of operator, SQL and, 213

subset requirement predicates, 129

subset requirements, 291, 306

subsets, 30, 74, 292, 305

substitutive methods, 27

sum operator, 40, 111, 209

supersets, 128

symbols, 333

choose operator, 30

diagonal stroke, 30

of logical connectives, 8

of sets, 24

symmetric difference operators

SQL and, 213

sets and, 31–35

system change number, 296

T

table constraints, 142, 157, 159–167, 306

CRS table structure and, 222, 224

data integrity code and, 246

deferred checking and, 300

implementation issues and, 265–290

implementing, 290

sample database universe specification and, 339

table data structures, 92

table design, defined, 93

table extensions, 108

table predicates, 117–136, 157, 306

patterns of, 127–136

sample database design and, 158

table structures

defined, 93

DI code execution models and, 266–284

implementing, 255–259

mutating table error and, 267

naming, 100

polarity and, 273

table universe, 129, 142

sample database design and, 156–167, 314

specification of, 314–324

tables, 79, 91–115, 306

constructing, 97

defined, 94

examples of, 211

join of, 106

operations on, 101–113

parts tables and, 95

shorthand notation for, 96

specifying formally, 94

Tarski, Alfred, 4

tautologies, 17

2-valued/3-valued logic and, 343

rewrite rules and, 19

TE (transition effect) views, 274

TE (transition effect) queries, 279

terminated employees (TERM), 312

attribute constraints for, 260

table universe for, 318

terminology. See definitions

ternary operators, 14

tertium non datur, 341

top-down view of databases, 141

transactions, 221–237, 284, 306

autonomous, 286

business logic code and, 244

concurrent, 266, 284, 298

data integrity code execution models for, 265–284

examples of, 226–236

formally specifying, 221–226

transition constraints

data integrity code and, 246

implementing, 296

transition effect (TE) views, 274

transition effect (TE) queries, 279

transition effects, 274–282, 302

transitions, constraint classes and, 143

transitivity, 49

triadic operators, 14

triggered procedural strategy, for DI code execution, 249

order of preference and, 254

table constraints and, 265, 268–284

triggers, 249, 267, 300

TRUE value, 340, 344

truth tables, 4, 11–16

3-valued logic and, 341

4-valued logic and, 345

purposes of, 11

truth-valued operators, 112

tuple constraints, 139, 142, 306

CRS table structure and, 222, 224

data integrity code and, 246

implementing, 262–265

sample database universe specification and, 339

specifying, 153, 163

tuple-in-join predicates, 135, 293, 306

tuple predicates, 117–122, 306

over sets, 119

sample database design and, 153

tuple universe, 139

sample database design and, 151–156, 314

table universe and, 142

tuples, 38, 79, 81

admissible, 82

combining, 106

deleting, 228

formal table specification and, 94

function composition and, 82

table predicates and, 120

updating, 230

types, variables and, 5

U

UI code (user interface code), 246

unary operators, 14

union of a set of sets, 36

union operators, 101–102, 305

functions and, 75

sets and, 31–35, 36

SQL and, 213

unique identification predicates, 127, 306

universal quantification, 51–59, 112, 305

negation connective and, 57

rewriting into existential, 207

universal quantifier, 47

UNKNOWN value, 340–341, 344

UPDATE statement, 232, 235, 243, 266

update statements, 244, 266

update TE views, 274

update triggers, 249

on-involved column(s) execution model and, 272

on-transition-effect-property execution model and, 278

on-transition-effect-property plus optimized-query execution model and, 282

user interface code (UI code), 246

V

value sets, 79

value spaces, 117

values, 5

variables, 5

bound, 8

free, 7

propositional, 10

Venn diagrams, 28

of binary relations, 70

of partitions, 37

of set operators, 31

Venn, John, 28

von Neumann, John, 4

W

wBL code (write BL code), 244, 247

WHERE statement, 268, 344

Whitehead, Alfred, 4

window-on-data (WoD) applications, 243–247

Wittgenstein, Ludwig, 4

WoD (window-on-data) applications, 243–247

write BL code (wBL code), 244, 247