A
- action methods, in Neo4j AuraDB, 405–409, 419–423
- actors, in use cases, 102
- address constraints, 305–306
- algorithms, 56
ALTER
command, 496
- alternate key, 36, 671
- alternative flow, in use cases, 102
- Amazon Web Services (AWS), 435
- American National Standards Institute (ANSI), 492
- Anaconda, 329
- Apache Ignite
- about, 328
- in C#
- about, 477, 483–484
building
class, 480
- creating programs, 477–483
- demonstrating persistent data, 483
- demonstrating volatile data, 483
- exercise solutions, 633–635
- exercises, 484–485
- installing Ignite database adapter, 478–479
- main program, 479–480
ReadData
method, 482–483
WriteData
method, 480–481
- defined, 671
- in Python
- about, 467–468, 474
- creating programs, 470–474
- defining
Building
class, 471
- demonstrating persistent data, 474
- demonstrating volatile data, 473–474
- exercise solutions, 631–633
- exercises, 474–475
- installing Apache Ignite, 468
- installing pyignite database adapter, 471
- with persistence, 470
- reading data, 473
- saving data, 471–472
- starting nodes, 468–470
- without persistence, 469
- Apache Subversion, 521
- application programming interface (API), 43
- applications
- multi-tier, 154–158
- social media, 55
- architecture, multi-tier, 205
- as a service (aaS), 23
- association objects, 126–128
- associations
- about, 216
- many-to-many, 216
- multiple many-to-many, 216–218
- multiple-object, 218–221
- reflexive, 222–231
- repeated attribute, 221–222
- atom, 671
- atomic objects, 124
- atomic transactions
- defined, 8, 671
- as a desirable feature of databases, 12–13, 25
- atomicity
- defined, 671
- lack of, in NoSQL databases, 198
- Atomicity, Consistency, Isolation, ad Durability (ACID)
- about, 561
- defined, 671
- as a desirable feature of databases, 13–16
- attributes
- defined, 671
- entity-relationship models (ER diagram/ERD) and, 131–132
- in relational databases, 34
- audit trails, 236
- availability, in CAP theorem, 22
AVERAGE
function, 511
B
- background, of Structured Query Language (SQL), 491
- backups
- for database maintenance, 533–537
- as a desirable feature of databases, 17–18, 25
- basic initialization scripts, 520
- basic syntax, for Structured Query Language (SQL), 495
- Basically Available, Soft state, and Eventually consistent (BASE)
- about, 561–562
- as a desirable feature of databases, 16
BEGIN
command, 498
- Beginning Software Engineering, Second Edition (Stephens), 99, 204
- Binary JavaScript Object Notation (BSON), in MongoDB Atlas, 432–434
- Binary Large Object (BLOB) data type, 52, 499, 672
- Boeing, 554
- books, database design for, 650–652
- Boolean data type, 499
- Boyce-Codd Normal Form (BCNF), 181–185, 672
- brainstorming
- about, 94–95
- with customers, 265–275
- Brewer, Eric (computer scientist), 22
- Brewer's theorem, 22
- b-tree, 672
- b+tree, 672
- bugs, 249
- building
- combined ER diagrams, 291–292
- data models, 283–301
- databases with SQL scripts, 519–532
- ER diagrams, 289–290
- initial semantic object models, 283–286
- multiple-object associations, 220–221
- tables, 500–503
Building
class, in Apache Ignite, 471, 480
- Building the Data Warehouse 4th Edition (Inmon), 538
build_org_chart
method, 410
BuildOrgChart
method, 424
- business rules
- about, 145–147, 158–159, 303, 310–311
- defined, 672
- drawing relational models, 310
- exercise solutions, 581–587, 608–610
- exercises, 159–161, 311
- extracting, 152–154, 303–311
- identifying, 147–152, 303–309
- multi-tier applications, 154–158
- business tier/layer, 154
C
- C#
- about, 327
- Apache Ignite in
- about, 477, 483–484
building
class, 480
- creating programs, 477–483
- demonstrating persistent data, 483
- demonstrating volatile data, 483
- exercises, 484–485
- installing Ignite database adapter, 478–479
- main program, 479–480
ReadData
method, 482–483
WriteData
method, 480–481
- MariaDB in
- about, 355, 366
- creating data, 360–363
- creating databases, 356–358
- creating programs, 355–365
- defining tables, 358–360
- exercises, 366–367
- fetching data, 364–365
- installing MySqlConnector, 356
- MongoDB Atlas in
- about, 453–454, 465
- creating programs, 454–465
- exercises, 466
- helper methods, 454–462
- installing MongoDb database adapter, 454
- main program, 462–465
- Neo4j AuraDB in
- about, 417–418, 428
- action methods, 419–423
- creating programs, 418–427
- exercises, 428–429
- installing Neo4j driver, 418–419
- main program, 426–427
- org chart methods, 423–425
- PostgreSQL in
- about, 389, 399
- connecting to database, 390
- creating customer data, 392–393
- creating order data, 393–395
- creating order item data, 395–396
- creating programs, 389–399
- deleting old data, 391–392
- displaying orders, 396–399
- exercises, 399–400
- installing Npgsql, 389–390
- C++, 327
- calculation parameters, business rules for, 148
- camel case, 131
- candidate key, 35, 36, 181, 672
- CAP theorem
- about, 562
- as a desirable feature of databases, 22
- cardinality
- defined, 672
- entity-relationship models (ER diagram/ERD) and, 133–134
- semantic object models (SOMs) and, 120
- Cartesian product, as a database operation, 40
- case sensitivity
- in entity-relationship models (ER diagram/ERD), 131
- in PostgreSQL, 376
- in SQL, 495
- catalog, 672
- categories, of SQL scripts, 520–522
cd
command, 469, 470
- changeable data models, 198
- CHAR data type, 499
- check constraints, 37–38, 672
- CIH virus, 535
- classes, semantic object models (SOMs) and, 119–120, 124–129
- cleanup scripts, 521
CLOSE
command, 497
- closing connections, in PostgreSQL, 384
- cloud
- considerations, as a desirable feature of databases, 22–23
- defined, 672
- NoSQL databases and, 47–49
- Codd, Edgar (researcher), 675
- column family databases. See column-oriented databases
- column-oriented databases
- about, 53
- defined, 672
- uses for, 75
- columns, in relational databases, 32–34
- commands, in Structured Query Language (SQL), 495–498
- commas, in tuples, 346
- comma-separated value (CSV) files, 673
- comments
- entity-relationship models (ER diagram/ERD) and, 137
- semantic object models (SOMs) and, 129–130
- commit, 672
COMMIT
command, 498
Commit
method, 363
- communication networks, 55
- compacting databases, 538
- complex calculations, ability to perform, as a desirable feature of databases, 21–22, 26
- complex object, 124
- complicated checks, business rules for, 148–149
- composite index. See concatenated index
- composite key, 35, 36, 672, 673
- composite objects, 124–125, 673
- compound index. See concatenated index
- compound key. See composite key
- compound objects, 125, 673
- concatenated index, 672, 673
- concatenated key. See composite key
- Concurrent Versions System (CVS), 521, 655
- configuration, initial, privileges and, 553
- connections
- closing in PostgreSQL, 384
- to databases in PostgreSQL, 379–380, 390
- finding in MongoDB Atlas, 436–439
connect_to_db
method, 440, 441–442
- consistency
- in CAP theorem, 22
- defined, 673
- as a desirable feature of databases, 10, 25
- importance of, 48–49
- in NoSQL databases, 198
- constraints
- about, 37
- address, 305–306
- check, 37–38
- domain, 37
- foreign key, 38–39
- not enforcing, as a design pitfall, 253
- primary key, 38
- unique, 38
- contacts, database design for, 665–666
- continuous shadowing, 16
- conventions, entity-relationship models (ER diagram/ERD) and, 136–137
- Convert, 90
- converting
- domains into tables, 205–206
- entity-relationship models, 140–141
- semantic object models, 138–140
- Coordinated Universal Time (UCT), 68
- cost, as a desirable feature of databases, 18–19, 26
- Create, Read, Update, and Delete (CRUD)
- about, 497
- defined, 673
- as a desirable feature of databases, 9, 25
CREATE
command, 407, 408, 420, 421, 496
CREATE DATABASE
statement, 519
CREATE INDEX
statement, in Structured Query Language (SQL), 503–504
CREATE TABLE
statement
- about, 348, 360, 500–503
- in Structured Query Language (SQL), 498–503
CreateCustomerRecords
method, 392, 396
create_data
method, 440, 442–444, 448
CreateData
method, 360–362, 455, 457–458, 464
CreateDatabase
method, 356–357
CreateOrderItemRecords
method, 395–396
CreateOrderRecords
method, 393–394, 396
CreateTables
method, 358–359
- creating
- backup plans, 536–537
- customer data in PostgreSQL, 380–381, 392–393
- data in MariaDB, 348–350, 360–363
- databases
- in MariaDB, 356–358
- in PostgreSQL, 373–374
- MariaDB databases, 344–346
- order data in PostgreSQL, 382–383, 393–395
- order item data in PostgreSQL, 383–384, 395–396
- programs
- in Apache Ignite, 470–474, 477–483
- in MariaDB, 343–352, 355–365
- in MongoDB Atlas, 439–450, 454–465
- in Neo4j AuraDB, 405–413, 418–427
- in PostgreSQL, 378–386, 389–399
- requirements document, 101
- use cases, 102–105
- users in PostgreSQL, 371–372
- Crockford, Douglas, 433
- cross-record checks, business rules for, 148
- cross-table checks, business rules for, 148
- crow's foot symbol, 133
- current operations, studying, 94
- cursor, 673
- Customer Champion, 89
- customer data, creating in PostgreSQL, 380–381, 392–393
- customer orders, database design for, 656
- Customer Representative, 89
- customers
- brainstorming with, 265–275
- meeting, 88–89, 263–265
- picking the brains of, 93
- understanding reasoning of, 96–97
- cyclic dependency, 673
- Cypher, in Neo4j AuraDB, 404–405
D
- data. See also temporal data
- arranging
- in BCNF, 184–185
- in domain/key normal form (DKNF), 194–195
- in fifth normal form, 192–193
- in first normal form (1NF), 167–172
- in fourth normal form, 188–189
- in second normal form (2NF), 175–177
- in third normal form (3NF), 179–181
- creating in MariaDB, 348–350, 360–363
- deleting in PostgreSQL, 380, 391–392
- determining
- how pieces of data are related, 269–271
- needs for user interface, 268–269
- origin of, 269
- fetching in MariaDB, 350–352, 364–365
- reading in Apache Ignite, 473
- redundant, 210–211
- saving in Apache Ignite, 471–472
- data anomalies, in NoSQL databases, 198
- Data Control Language (DCL), 495, 497–498, 673
- Data Definition Language (DDL), 495–496, 673
- data dictionary, 673
- data disasters, 20
- data elements. See attributes
- data initialization scripts, 520–521
- data integrity
- determining needs for, 273–275
- user needs and, 86–87
- Data Manipulation Language (DML), 495, 497, 673
- data mart, 673
- data mining, 673
- data models/modeling
- about, 111–114, 142, 283, 298–300
- building, 283–301
- entity-relationship modeling, 130–137, 289–294
- exercise solutions, 573–580, 605–607
- exercises, 142–143, 300–301
- relational modeling, 137–141, 294–298
- semantic object modeling, 118–130, 283–288
- translating user needs into, 111–143
- user interface models, 114–118
- data needs, user needs and, 86
- data normalization
- about, 163–164, 199, 311, 323–324
- best level of, 197
- Boyce-Codd Normal Form (BCNF), 181–185
- defined, 678
- domain/key normal form (DKNF), 193–195
- essential redundancy, 195–197
- exercise solutions, 587–592, 610–613
- exercises, 199–201, 324
- fifth normal form (5NF), 190–193
- first normal form (1NF), 164–172
- fourth normal form (4NF), 185–189
- improving flexibility, 311–315
- insufficient, as a design pitfall, 248–249
- normal vs. abnormal, 432
- NoSQL, 197–198
- second normal form (2NF), 173–177
- stopping at third normal form, 181
- third normal form (3NF), 177–181
- too much, as a design pitfall, 248
- verifying
- first normal form, 315–318
- second normal form, 318–321
- third normal form, 321–323
- data residency, 19
- data scrubbing, 674
- data sovereignty, 19
- data tier, 154
- data types
- about, 499
- business rules for, 149
- of columns, 32–33
- defined, 674
- data warehouse
- for database maintenance, 537–538
- defined, 674
- database adapters, 332–333
- database administrator (DBA), 30, 674
- database connector. See database adapters
- database creation scripts, 520
- database design
- about, 26–27, 212–213, 241, 257
- allowing redundant data, 210–211
- avoiding pitfalls of, 241–259
- consequences of good and bad, 24–26
- converting domains into tables, 205–206
- of databases for software support, 203–214
- desirable features, 9–26
- documenting everything, 204–205
- exercise solutions, 557–562, 592–593, 598–600
- exercises, 27–28, 213–214, 257–259
- focused tables, 206–207
- goals for, 3–28
- importance of, 4–5
- information containers, 6–8
- insufficient normalization for, 248–249
- insufficient testing for, 249
- lack of preparation for, 241–242
- mishmash tables for, 250–252
- multi-tier architecture, 205
- naming conventions, 209–210
- not defining natural keys with, 256–257
- not enforcing constraints for, 253
- not planning for change with, 245–247
- not squeezing in everything, 211–212
- obsession with IDs for, 253–256
- performance anxiety for, 249–250
- planning ahead, 204
- poor documentation for, 242
- poor naming standards for, 242–244
- in PostgreSQL, 371
- samples, 649–669
- strengths and weaknesses of information containers, 8–9
- thinking too small for, 244–245
- too much normalization with, 248
- types of tables, 207–209
- database design patterns
- about, 215, 238
- associations, 216–231
- exercise solutions, 594–597
- exercises, 238–240
- locking, 236–238
- logging, 236–238
- temporal data, 232–236
- database engine, 332
- database maintenance
- about, 533, 542
- backups, 533–537
- compacting databases, 538
- data warehousing, 537–538
- exercise solutions, 640–641
- exercises, 542–543
- performance tuning, 538–542
- repairing databases, 538
- database management systems (DBMSs), 30
- database operations, 40–41
- database security
- about, 545, 555–556
- exercise solutions, 642–648
- exercises, 556
- initial configuration and privileges, 553
- levels of, 545
- passwords, 546–548
- physical security, 554–555
- privileges, 548–553
- too much, 553–554
- database tier/layer, 154
- database transaction units (DTUs), 18–19
- databases
- building with SQL scripts, 519–532
- compacting, 538
- connecting to, in PostgreSQL, 379–380, 390
- creating
- in MariaDB, 356–358
- in PostgreSQL, 373–374
- deductive, 70
- defined, 6, 674
- designing
- in PostgreSQL, 371
- for software support, 203–214
- desirable features of, 9–26
- dimensional, 70–71
- exercise solutions, 563–564
- MariaDB, creating, 344–346
- object, 70
- object-oriented, 70
- repairing, 538
- semi-structured, 64
- temporal, 71–72
- DATE data type, 499
- DATETIME data type, 499
- DECIMAL data type, 499
DECLARE
command, 497
- dedicated service, 434
- deductive database, 70
- defining
Building
class in Apache Ignite, 471
- tables
- about, 346–348
- in MariaDB, 358–360
- in PostgreSQL, 374–378
- The Definitive Guide to Dimensional Modeling, 3rd Edition (Kimball), 538
DELETE
command, 380, 497
DELETE FROM
statement, 350, 380, 392
DELETE
statement
- about, 391, 392
- in Structured Query Language (SQL), 514–515
delete_all_nodes
method, 406–407, 412
DeleteAllNodes
method, 419–420
- deleted objects, temporal data and, 233–234
DeleteMany
method, 456
delete_old_data
method, 440, 442
DeleteOldData
method, 455, 456
DeleteRecords
method, 391
- deleting
- data in PostgreSQL, 380
- defined, 674
- old data in PostgreSQL, 391–392
- deletion anomaly, 174, 674
- deliverables, 101
- demanding feedback, 280–281
- demonstrating
- persistent data in Apache Ignite, 474, 483
- volatile data in Apache Ignite, 473–474, 483
- determinant, 182, 674
- development, methodologies for, 99
- Devil's Advocate, 90
- difference, as a database operation, 40
- differential backup, 534
- dimensional database, 70–71, 674
- directed relationships, 404
- displaying orders, in PostgreSQL, 396–399
DisplayOrders
method, 396–397
Dispose
method, 357, 363
- distinct object rental, database design for, 660–661
- distributed database, 674
- divide, as a database operation, 40
- document databases
- document management, database design for, 655–656
- document stores. See document databases
- documentation
- of everything, 204–205
- poor, as a design pitfall, 242
- document-oriented databases. See document databases
- domain constraints, 37
- domain/key normal form (DKNF), 193–195, 674
- domains
- of columns, 32
- converting into tables, 205–206
- defined, 674
- double quotes, 505
- drawing relational models, 310
DROP DATABASE
statement, 519
DROP
statement, 496, 504
DROP TABLE
statement, 347, 357, 359–360, 494, 504
- durability, 15, 674
E
- ease of use, as a desirable feature of databases, 19, 26
- Easter egg, 675
- e-commerce programs, 55
- edges, 54, 675
- broken, 293–294
- defined, 680
- entity-relationship models (ER diagram/ERD) and, 132
- in Neo4j AuraDB, 404
- effective dates, temporal data and, 232–233
- employee shifts and timesheets, database design for, 656
- employee skills and qualifications, database design for, 657–658
- employees, projects, and departments, database design for, 657
- enrollment use cases, 104–105
- entities
- defined, 675
- entity-relationship models (ER diagram/ERD) and, 131–132
- entity classes, 131
- entity integrity, 675
- entity sets, 131
- entity-relationship diagram (ER diagram), 675
- entity-relationship models (ER diagram/ERD)
- about, 289
- additional conventions, 136–137
- attributes, 131–132
- building combined ER diagrams, 291–292
- building ER diagrams, 289–290
- cardinality, 133–134
- case, 131
- comments, 137
- converting, 140–141
- defined, 113
- entities, 131–132
- identifiers, 131–132
- improving entity-relationship diagrams, 293–294
- inheritance, 134–136
- notes, 137
- relationships, 132
- translating user needs into, 130–137
- enumerated values, business rules for, 148, 149
- environment, user needs and, 88
- Error Between Chair and Keyboard (EBCAK) problems, 535
- error correction, as a desirable feature of databases, 11, 25
- Essential Algorithms: A Practical Approach to Computer Algorithms Using Python and C# (Stephens), 525
- essential redundancy, 195–197
- eventual consistency, 49
- example programs
- about, 336–337
- database adapters, 332–336
- exercise solutions, 613–615
- exercises, 337
- Jupyter Notebook, 329–331
- program passwords, 336
- tool choices, 327–328
- Visual Studio, 331–332
EXECUTE
statement, 382–384
execute_node_query
method, 406, 408, 411
ExecuteNodeQuery
method, 419, 422, 425
ExecuteNonQuery
method, 357
ExecuteScalar
method, 393, 394
- Executive Champion, 89
- executive summary, 280
- exercise solutions
- Apache Ignite
- in C#, 633–635
- in Python, 631–633
- business rules, 581–587, 608–610
- data models, 573–580, 605–607
- data normalization, 587–592, 610–613
- database design, 557–562, 592–593, 594–597, 598–600
- database maintenance, 640–641
- database security, 642–648
- databases, 563–564
- examples, 613–615
- MariaDB
- in C#, 617–619
- in Python, 615–617
- MongoDB Atlas
- in C#, 630–631
- in Python, 629–630
- Neo4j AuraDB
- NoSQL, 564–568
- PostgreSQL
- in C#, 622–626
- in Python, 619–622
- Structured Query Language (SQL), 635–640
- user needs, 568–573, 600–605
- exercises
- Apache Ignite
- in C#, 484–485
- in Python, 474–475
- business rules, 159–161, 311
- data models, 142–143, 300–301
- data normalization, 199–201, 324
- database design, 27–28, 213–214, 257–259
- database maintenance, 542–543
- database security, 556
- design patterns, 238–240
- example programs, 337
- MariaDB, 366–367
- MongoDB Atlas
- Neo4j AuraDB
- in C#, 428–429
- in Python, 414–415
- NoSQL databases, 78–79
- PostgreSQL, 399–400
- relational databases (RDBs), 44–46
- Structured Query Language (SQL), 515–517
- user needs, 107–109, 281–282
- extending the partial ordering, 525
- extensibility, as a desirable feature of databases, 18–19, 26
- Extensible Markup Language (XML)
- defined, 675
- in document databases, 51
- hierarchical nature of, 229
- in MongoDB Atlas, 432–434
- extracting business rules, 152–154, 303–311
F
- feasibility, 106
- feedback, demanding, 280–281
FETCH
command, 497
fetchall
method, 351, 385, 494
FetchData
method, 364–365
- fetching data, in MariaDB, 350–352, 364–365
- field, 675
- field-level check constraint, 38
- fifth normal form (5NF), 190–193, 675
- files
- flat, 59–60
- JSON, 67–69
- schema, 64
- final deliverables, 101
Find
method, 459–460
find
statement, 449
- finding
- connection code in MongoDB Atlas, 436–439
- more information for SQL, 491–492
find_path
method, 406, 409, 411
FindPath
method, 419, 422–423
- first normal form (1NF)
- fixed values, business rules for, 149
- flat files
- about, 59–60
- defined, 675
- uses for, 75
- flexibility, improving, 311–315
- flexible data models, 198
- focusing tables, 206–207
- foreign key, 675
- foreign key constraints, 38–39
- fourth normal form (4NF), 185–189, 675
FROM
clause, in Structured Query Language (SQL), 507–511
- full backup, 534
- functionality, user needs and, 85
G
- garbage-in, garbage-out (GIGO) principle, 28
- General Data Protection Regulation (GDPR), 20
- generalizable constraints, business rules for, 148
- Generic Villain, 90
GetInt32
method, 398
get_or_create_cache
method, 472, 473
GetOrCreateCache
method, 481
- Git, 521, 655
- GitHub, 521
global
statement, 442
- globally unique identifier (GUID), 254
- goals, in use cases, 102
- Google Cloud, 235
- Google Sheets, 43
GRANT
command, 497
- graph databases
- Graph Query Language (GQL), 675
- Gravina Island Bridge, 538
- group attribute, 119
GROUP BY
clause, in Structured Query Language (SQL), 511
H
- Health Insurance Portability and Accountability Act (HIPAA), 23
- HeidiSQL, running, 340–343
- helper methods, in MongoDB Atlas, 440–449, 454–462
- hierarchical data
- about, 225–226
- with NoSQL, 228–229
- working with, 226–228
- hierarchical databases, 56–59
- hybrid objects, 125–126, 676
- hybrid online analytical processing (HOLAP), 676
- hypercube, 676
I
- IBM Db2, 42–43
- identical object rental, database design for, 659
- identifiers
- defined, 676
- entity-relationship models (ER diagram/ERD) and, 131–132
- semantic object models (SOMs) and, 120–121
- identifying
- business rules, 303–309
- IDs, 297–298
- IDs
- identifying, 297–298
- obsession with, as a design pitfall, 253–256
- Ignite database adapter, installing, 478–479
- implicit relationships, 404
import
statement, 406, 472
- improving
- entity-relationship diagrams, 293–294
- flexibility, 311–315
- semantic object models, 286–288
- incremental backup, 534
- indexes, 36–37, 676
- individual passwords, 546–547
- individual sports, database design for, 663
- information containers
- about, 6–8
- strengths and weaknesses of, 8–9
- inheritance, entity-relationship models (ER diagram/ERD) and, 134–136
- inherited objects, 128–129
- Inmon, W. H. (author)
- Building the Data Warehouse 4th Edition, 538
INNER JOIN
clause, 508
- insert, 676
INSERT
command, 497
INSERT INTO
statement, 350
INSERT
statement, 363, 381, 382, 383, 392, 394, 494, 504–506
- insertion anomaly, 174, 676
- installing
- Apache Ignite, 468
- Ignite database adapter, 478–479
- MariaDB, 340
- MongoDB Atlas, 434–436
- MongoDB database adapter, 454
- MySqlConnector, 356
- Neo4j AuraDB, 402–403
- Neo4j database adapter, 405
- Neo4j driver, 418–419
- Npgsql in PostgreSQL, 389–390
- PostgreSQL, 370–371
- Psycopg, 379
- pyignite database adapter, 471
- PyMongo database adapter, 439–440
pymysql,
344
- instance, 676
- INT data type, 499
- International Organization for Standardization (ISO), 492
- intersection, as a database operation, 40
- isolation, 15, 676
IssonArray
function, 456
J
- Java, 327
- Java Online Analytical Processing (JOLAP), 676
- JavaScript Object Notation (JSON)
- defined, 676
- in document databases, 51
- files, 67–69
- hierarchical nature of, 229
- in MongoDB Atlas, 432–434
- uses for files, 76
- join
- as a database operation, 40
- defined, 676
- Jupyter Notebook
- about, 329–331
- packages in, 333–334
K
- Kanban manufacturing, 152
- kebab case, 131
- key constraint, 136, 677
- keys
- defined, 676
- in relational databases, 34–36
- key-value databases
- about, 52
- defined, 677
- uses for, 75
- Kimball, Ralph (author)
- The Definitive Guide to Dimensional Modeling, 3rd Edition, 538
L
LEFT JOIN
clause, 509
- legal considerations, as a desirable feature of databases, 23–24
- line symbol, 133
- links. See edges
- LINQ queries, 459
- Linux, 370
- locking. See logging and locking
- logging and locking
- audit trails, 236
- defined, 677
- turnkey records, 237–238
- lookup tables, 206
M
main
method, 355, 479
- main program
- in Apache Ignite, 479–480
- in MongoDB Atlas, 449–450, 462–465
- in Neo4j AuraDB, 412–413, 426–427
- maintenance, of databases. See database maintenance
make_link
method, 406, 407–408, 410
MakeLink
method, 419, 421, 424
make_node
method, 406, 407, 410
MakeNode
method, 419, 420–421, 424
- many-to-many associations, 216–218
- many-to-many relationship, 677
- MariaDB
- about, 42–43, 328
- in C#
- about, 355, 366
- creating data, 360–363
- creating databases, 356–358
- creating programs, 355–365
- defining tables, 358–360
- exercise solutions, 617–619
- exercises, 366–367
- fetching data, 364–365
- installing MySqlConnector, 356
- defined, 677
- in Python
- about, 339–340
- creating data, 348–350
- creating databases, 344–346
- creating programs, 343–352
- defining tables, 346–348
- exercise solutions, 615–617
- fetching data, 350–352
- installing, 340
- installing
pymysql,
344
- running HeidiSQL, 340–343
- website, 491
MATCH
command, 420
MATCH
statement, 408, 421, 422
- MaxDB, 677
- meeting customers, 263–265
- memo, 677
- metabase, 677
- metadata, 677
- methodologies, for development, 99
- Microsoft Access, 42–43
- Microsoft Azure SQL Database, 42–43
- Microsoft Excel, 43
- Microsoft SQL Server, 42–43
- Microsoft Transact-SQL (website), 491
- milestones. See deliverables
- Minnesota Department of Transportation, 267
- mishmash tables, as a design pitfall, 250–252
- mission statement, 279
- MongoDB Atlas
- about, 328
- in C#
- about, 453–454, 465
- creating programs, 454–465
- exercise solutions, 630–631
- exercises, 466
- helper methods, 454–462
- installing MongoDb database adapter, 454
- main program, 462–465
- defined, 677
- in Python
- about, 431, 450
- Binary JavaScript Object Notation (BSON), 432–434
- creating programs, 439–450
- exercise solutions, 629–630
- exercises, 450–451
- Extensible Markup Language (XML), 432–434
- finding connection code, 436–439
- helper methods, 440–449
- installing MongoDB Atlas, 434–436
- installing PyMongo database adapter, 439–440
- JavaScript Object Notation (JSON), 432–434
- main program, 449–450
- normal vs. abnormal, 432
- MongoDB database adapter, installing, 454
- movies, database design for, 653–654
- Mueller, John (tech editor), 370
- multidimensional analytical processing (MOLAP), 677
- multidimensional array, 677
- multidimensional database. See dimensional database
- multiple many-to-many associations, 216–218
- multiple-object associations, 218–221
- multistatement commands, in Structured Query Language (SQL), 493–494
- multi-tier applications, 154–158
- multi-tier architecture, 205
- multivalued dependency, 678
- music, database design for, 654–655
- Must, Should, Could, Won't (MOSCOW), 98
- MySQL
- MySqlConnector, installing, 356
N
- naming conventions
- about, 209–210
- as a design pitfall, 242–244
- natural keys, not defining, as a design pitfall, 256–257
- nearline, 235
- Neo4j AuraDB
- about, 328
- in C#
- about, 417–418, 428
- action methods, 419–423
- creating programs, 418–427
- exercise solutions, 627–628
- exercises, 428–429
- installing Neo4j driver, 418–419
- main program, 426–427
- org chart methods, 423–425
- defined, 678
- installing, 402–403
- in Python
- about, 401, 414
- action methods, 405–409
- creating programs, 405–413
- Cypher, 404–405
- exercise solutions, 627
- exercises, 414–415
- installing Neo4j AuraDB, 402–403
- installing Neo4j database adapter, 405
- main program, 412–413
- nodes, 404
- org chart methods, 410–412
- relationships, 404
- Neo4j database adapter, installing, 405
- Neo4j driver, installing, 418–419
- network data
- about, 229–231
- with NoSQL, 231
- networks
- communication, 55
- street, 54–55
- NewSQL, as a desirable feature of databases, 17
- nodes
- defined, 53, 678
- in Neo4j AuraDB, 404
- starting in Apache Ignite, 468–470
- normal flow, in use cases, 102
- normalization. See data normalization
- NoSQL databases
- about, 47, 76–77, 114
- cloud, 47–49
- column-oriented databases, 53
- deductive databases, 70
- defined, 678
- dimensional databases, 70–71
- document databases, 51–52
- exercise solutions, 564–568
- exercises, 78–79
- flat files, 59–60
- graph databases, 53–56
- hierarchical data with, 228–229
- hierarchical databases, 56–59
- JSON files, 67–68
- key-value databases, 52
- network data with, 231
- object databases, 70
- philosophy, 50
- pros and cons of, 72–76
- selecting, 50
- spreadsheets, 69
- SQL and, 489
- temporal databases, 71–72
- XML files, 60–67
- NoSQL normalization, 197–198
- notes
- entity-relationship models (ER diagram/ERD) and, 137
- semantic object models (SOMs) and, 129–130
- in use cases, 102
- Npgsql, installing in PostgreSQL, 389–390
- NuGet package, 335
- null, 678
- NUMBER data type, 499
O
- object attribute, 120
- object database management system (ODBMS), 70, 678
- object databases, 70, 76
- object identifier, 120–121
- object-oriented databases, 70, 678
- object-relational database (ORD), 30, 678
- object-relational database management system (ORDBMS), 30
- object-relational mapping, 678
- objects
- defined, 678
- semantic object models (SOMs) and, 119–120
- one-to-many relationship, 224–225, 679
- one-to-one relationship, 223–224, 679
- online analytical processing (OLAP), 679
- online transaction processing (OLTP) systems, 17
- openCypher, 404–405
- operating system passwords, 547
- operations, database, 40–41
- Oracle, 42–43, 679
- Oracle SQL (website), 491
ORDER BY
clause
- about, 397
- in Structured Query Language (SQL), 512–513
- order data, creating in PostgreSQL, 382–383, 393–395
- order item data, creating in PostgreSQL, 383–384, 395–396
- ordering
- SQL commands, 522–531
- tables, 524–531
- orders, displaying in PostgreSQL, 396–399
- org chart methods, in Neo4j AuraDB, 410–412, 423–425
- outer joins, 510
P
- packages
- in Jupyter Notebook, 333–334
- in Visual Studio, 334–336
- participation constraint, 136, 679
- partition tolerance, in CAP theorem, 22
- Pascal case, 131
- passengers, database design for, 667–668
- passwords
- about, 546
- individual, 546–547
- operating system, 547
- for programs, 336
- quality of, 547–548
- single-password databases, 546
pause
command, 469
- performance
- determining needs for, 271–272
- performance anxiety as a design pitfall, 249–250
- tuning for database maintenance, 538–542
- performing queries, in PostgreSQL, 384–386
- persistence
- Apache Ignite and, 469, 470
- as a desirable feature of databases, 17–18, 25
- persistent data, demonstrating in Apache Ignite, 474, 483
- personally identifiable information (PII), 23–24
person_string
method, 440–441
PersonString
method, 455–456, 465
- pgAdmin, running, 371
- philosophy, NoSQL, 50
- physical security, 554–555
- Pip, 333
pip
commands, 333
- planning
- for change (not), as a design pitfall, 245–247
- for user needs, 84–85
- plus sign (+), 377
- points of view, relational, 31–32
- portability, as a desirable feature of databases, 19–20, 26
- post-conditions, in use cases, 102
- PostgreSQL
- about, 42–43, 328
- in C#
- about, 389, 399
- connecting to database, 390
- creating customer data, 392–393
- creating order data, 393–395
- creating order item data, 395–396
- creating programs, 389–399
- deleting old data, 391–392
- displaying orders, 396–399
- exercise solutions, 622–626
- exercises, 399–400
- installing Npgsql, 389–390
- case sensitivity in, 376
- defined, 679
- installing, 370–371
- in Python
- about, 369–370
- closing connections, 384
- connecting to databases, 379–380
- creating customer data, 380–381
- creating databases, 373–374
- creating order data, 382–383
- creating order item data, 383–384
- creating programs, 378–386
- creating users, 371–372
- defining tables, 374–378
- deleting old data, 380
- designing databases, 371
- exercise solutions, 619–622
- installing PostgreSQL, 370–371
- installing Psycopg, 379
- performing queries, 384–386
- running
pgAdmin,
371
- website, 491
- practices, XML, 64–66
- pre-conditions, in use cases, 102
- predecessor list, 525
- preparation, lack of, as a design pitfall, 241–242
Prepare
method, 394, 396
PREPARE
statement, 382, 383
- primary key
- primary key constraints, 38, 679
PrintReader
method, 364–365
- prioritizing, 98–99
- privileges
- about, 548–553
- initial configuration and, 553
- Procedural Language/Structured Query Language (PL/SQL), 679
- product requirements document (PRD), 101
- programs
- creating
- in Apache Ignite, 470–474, 477–483
- in MariaDB, 343–352, 355–365
- in MongoDB Atlas, 439–450, 454–465
- in Neo4j AuraDB, 405–413, 418–427
- in PostgreSQL, 378–386, 389–399
- passwords for, 336
- projection, as a database operation, 40
- project-join normal form. See fifth normal form (5NF)
- projects, determining how they should look, 267–268
- property, 54
- Psycopg, installing, 379
- pyignite database adapter, installing, 471
- pyinstall, 333
- PyMongo database adapter, installing, 439–440
pymysql,
installing, 344
- Python
- about, 327
- Apache Ignite in
- about, 467–468, 474
- creating programs, 470–474
- defining
Building
class, 471
- demonstrating persistent data, 474
- demonstrating volatile data, 473–474
- exercises, 474–475
- installing Apache Ignite, 468
- installing pyignite database adapter, 471
- with persistence, 470
- reading data, 473
- saving data, 471–472
- starting nodes, 468–470
- without persistence, 469
- MariaDB in
- about, 339–340
- creating data, 348–350
- creating databases, 344–346
- creating programs, 343–352
- defining tables, 346–348
- fetching data, 350–352
- installing, 340
- installing
pymysql,
344
- running HeidiSQL, 340–343
- MongoDB Atlas in
- about, 431, 450
- Binary JavaScript Object Notation (BSON), 432–434
- creating programs, 439–450
- exercises, 450–451
- Extensible Markup Language (XML), 432–434
- finding connection code, 436–439
- helper methods, 440–449
- installing MongoDB Atlas, 434–436
- installing PyMongo database adapter, 439–440
- JavaScript Object Notation (JSON), 432–434
- main program, 449–450
- normal vs. abnormal, 432
- Neo4j AuraDB in
- about, 401, 414
- action methods, 405–409
- creating programs, 405–413
- Cypher, 404–405
- exercises, 414–415
- installing Neo4j AuraDB, 402–403
- installing Neo4j database adapter, 405
- main program, 412–413
- nodes, 404
- org chart methods, 410–412
- relationships, 404
- PostgreSQL in
- about, 369–370
- closing connections, 384
- connecting to databases, 379–380
- creating customer data, 380–381
- creating databases, 373–374
- creating order data, 382–383
- creating order item data, 383–384
- creating programs, 378–386
- creating users, 371–372
- defining tables, 374–378
- deleting old data, 380
- designing databases, 371
- installing PostgreSQL, 370–371
- installing Psycopg, 379
- performing queries, 384–386
- running
pgAdmin,
371
Q
- quality, of passwords, 547–548
- queries
- defined, 679
- performing in PostgreSQL, 384–386
- query results, 41
query_data
method, 440, 444–449
QueryData
method, 455, 458–462
query_org_chart
method, 411–412
QueryOrgChart
method, 424–425
- quotes, 505
R
Read
method, 398
ReadData
method, in Apache Ignite, 482–483
- reading data, in Apache Ignite, 473
read_transaction
method, 405, 412
- recipes, database design for, 668–669
- records, 33, 679
- recursive associations. See reflexive associations
- redundant data, 210–211
- referential integrity, 680
- referential integrity constraint, 38
- refining. See data normalization
- reflexive associations
- about, 222–223
- hierarchical, 225–229
- hierarchical data with NoSQL, 228–229
- network data, 229–231
- network data with NoSQL, 231
- one-to-many, 224–225
- one-to-one, 223–224
- regret, 680
- relational database management system (RDBMS), 30, 680
- relational databases (RDBs)
- about, 29–30, 44
- attributes, 34
- columns, 32–34
- constraints, 37–39
- database operations, 40–41
- defined, 680
- exercises, 44–46
- indexes, 36–37
- keys, 34–36
- points of view, 31–32
- popular, 41–43
- pros and cons of, 71–72
- relations, 34
- rows, 32–34
- selecting, 30–31
- spreadsheets, 43
- tables, 32–34
- tuples, 34
- uses for, 74
- relational models/modeling
- about, 294–298
- defined, 112
- drawing, 310
- identifying IDs, 297–298
- translating user needs into, 137–141
- relational online analytical processing (ROLAP), 680
- relations
- defined, 680
- in relational databases, 34
- relationships. See edges
- repairing databases, 538
- repeated attribute associations, 221–222
- replication, 680
- report, 680
- required values, business rules for, 149
- requirements documents
- creating, 101
- writing, 279–280
- retrieval, as a desirable feature of databases, 9–10, 25
- return merchandise authorization (RMA), 100
REVOKE
command, 497
RIGHT JOIN
clause, 509
- ring symbol, 133
- roles and responsibilities, user needs and, 89–92
- roll back, 680
ROLLBACK
command, 498
- rows
- defined, 680
- in relational databases, 32–34
- running
- HeidiSQL, 340–343
- pgAdmin, 371
S
- samples, of database design, 649–669
- sanity check constraints, 303
- sanity checks, business rules for, 149
SAVE
command, 498
- savepoint, 680
- saving
- data in Apache Ignite, 471–472
- scripts, 521–522
- scalable data models, 198
- scalar, 393
- schema files, 64
- schemas, 73
- scripts, SQL. See Structured Query Language (SQL), scripts
- second normal form (2NF)
- about, 173–177
- defined, 680
- verifying, 318–321
- security
- considerations, as a desirable feature of databases, 23–24
- database (See database security)
- as a desirable feature of databases, 20–21, 26
- determining needs for, 272–273
- user needs and, 87–88
SELECT
clause, 41, 506–507, 511, 512–513
SELECT
command, 497, 506
SELECT
statement, 491, 494, 512–513
- selecting
- as a database operation, 40
- defined, 680
- NoSQL databases, 50
- relational databases, 30–31
- semantic attribute, 680
- semantic class, 680
- semantic object (SO), 681
- semantic object models/modeling (SOMs)
- about, 121–122, 283
- building initial semantic object models, 283–286
- cardinality, 120
- class types, 124–129
- classes, 119–120
- comments, 129–130
- converting, 138–140
- defined, 113, 681
- identifiers, 120–121
- improving semantic object models, 286–288
- notes, 129–130
- objects, 119–120
- semantic views, 122–124
- translating user needs into, 118–130
- semantic views, semantic object models (SOMs) and, 122–124
- semi-structured database, 64
- serverless service, 434
- shadowing, 16
- shared service, 434
- sharing, as a desirable feature of databases, 21, 26
shortestPath
method, 409, 423
- Short-Timer, 90
SHOW DATABASES
command, 358
- Sidekick/Gopher, 90
- simple attribute, 119
- simple objects, 124, 681
single
method, 409
- single quotes, 505
- single-password databases, 546
- snake case, 131
- Snowflake, 42–43
- social media apps, 55
- software as a service (SaaS), 23
- software support, designing databases for, 203–214
- speed, as a desirable feature of databases, 12, 25
- spilled soda, 535
- splay tree, 681
- spreadsheets
- SQL server, 681
- SQLite, 42–43
- Stakeholder, 89–90
- standardization, in NoSQL databases, 198
- standards, in Structured Query Language (SQL), 492–493
- starting nodes, in Apache Ignite, 468–470
- Stephens, Rod (author)
- Beginning Software Engineering, Second Edition, 99, 204
- Essential Algorithms: A Practical Approach to Computer Algorithms Using Python and C#, 525
- stopping, at third normal form (3NF), 181
- stored efficiencies, 153
- stored procedure, 681
- street networks, 54–55
- strong consistency, 49
- Structured Query Language (SQL)
- about, 41, 489–490, 515
- background, 491
- basic syntax, 495
- case sensitivity of, 495
FROM
clause, 507–511
- commands, 495–498
CREATE INDEX
statement, 503–504
CREATE TABLE
statement, 498–503
- defined, 681
DELETE
statement, 514–515
DROP
statement, 504
- exercise solutions, 635–637
- exercises, 515–517
- finding more information, 491–492
GROUP BY
clause, 511
INSERT
statement, 504–506
- multistatement commands, 493–494
ORDER BY
clause, 512–513
- scripts
- about, 519, 531–532
- building databases with, 519–532
- categories of scripts, 520–522
- exercise solutions, 637–640
- exercises, 532
- importance of, 519–520
- ordering SQL commands, 522–531
SELECT
clause, 506–507
SELECT
command, 506
- standards, 492–493
- tutorials, 42, 491
UPDATE
statement, 513–514
- versions, 491–492
WHERE
clause, 511
- students, courses, and grades, database design for, 661–662
- studying current operations, 94
- subclass object, 681
SUM
function, 511
- summary, in use cases, 102
- superkey, 35, 36, 181, 681
- systems, determining requirements for, 265–267
T
- table-level check constraint, 38
- tables
- building, 500–503
- converting domains into, 205–206
- defined, 681
- defining
- about, 346–348
- in MariaDB, 358–360
- in PostgreSQL, 374–378
- focusing, 206–207
- lookup, 206
- ordering, 524–531
- in relational databases, 32–34
- types of, 207–209
- teams, database design for, 662–663
Temp
directory, 56
- temporal data
- about, 232
- deleted objects, 233–234
- determining what to temporalize, 234–236
- effective dates, 232–233
- temporal database, 71–72, 681
- testing, insufficient, as a design pitfall, 249
- thinking small, as a design pitfall, 244–245, 250
- third normal form (3NF)
- about, 177–181
- defined, 681
- stopping at, 181
- verifying, 321–323
- 3-2-1 backup strategy, 535
- TIME data type, 499
- TIMESTAMP data type, 499
- tools, choices for, 327–328
- transaction, 681
- Transaction Control Language (TCL), 495, 498, 681
- Transact-SQL (TSQL), 681
- transitive dependency, 177, 681–682
- transitory transactions, 393
- trigger, 682
TRUNCATE
command, 497
- tuples
- commas in, 346
- defined, 682
- in relational databases, 34
- turnkey records, 237–238
U
- understanding, verifying, 99–100
- Unified Modeling Language (UML), 103
- uniform resource identifier (URI), 412, 426
- union, as a database operation, 40
- unique constraints, 38, 682
- unique key, 35, 36
- universally unique identifier (UUID), 254
- unrelated multivalued dependency, 186
- update, 682
- update anomaly, 173–174, 682
UPDATE
statement, 497, 513–514
UPDATE STATISTICS
statement, 539
- use cases
- creating, 102–105
- enrollment, 104–105
- writing, 275–278
- user interface models
- defined, 113
- determining what data is needed for, 268–269
- translating user needs into, 114–118
- user interface tier/layer, 154
- user needs
- about, 83–84, 97–98, 106–107, 263, 281
- brainstorming
- about, 94–95
- with customers, 265–275
- creating requirements document, 101
- customer reasoning, 96–97
- customers, 88–89
- data integrity questions, 86–87
- data needs questions, 86
- deciding on feasibility, 106
- defining, 263–282
- demanding feedback, 280–281
- environment questions, 88
- exercise solutions, 568–573, 600–605
- exercises, 107–109, 281–282
- following customers, 93–94
- functionality questions, 85
- future predictions, 95–96
- making use cases, 102–105
- meeting customers, 263–265
- planning for, 84–85
- prioritizing, 98–99
- roles and responsibilities, 89–92
- security questions, 87–88
- studying current operations, 94
- talking to customers, 93
- translating into data models, 111–143
- verifying understanding, 99–100
- writing
- requirements document, 279–280
- use cases, 275–278
- user requirements, defining, 263–282
- users, creating in PostgreSQL, 371–372
V
- valid time, 71, 682
- validity
- business rules for, 148
- as a desirable feature of databases, 10–11, 25
- VARCHAR data type, 499
- vehicle fleets, database design for, 664–665
- verifying
- first normal form (1nf), 315–318
- second normal form (2NF), 318–321
- third normal form (3NF), 321–323
- understanding, 99–100
- view, 682
- view maps, 123–124
- virtual machines (VMs), 22–23
- virtual tables, 41
- Visual Basic, 266
- Visual Basic for Applications (VBA), 43
- Visual Studio
- about, 331–332
- packages in, 334–336
- volatile data, demonstrating in Apache Ignite, 473–474, 483
W
- W3School (website), 67
- weak entity, 137, 682
- websites
- Apache Ignite, 468
- Apache Subversion, 521
- AuraDB, 402
- CIH virus, 535
- Concurrent Versions System (CVS), 521, 655
- Cypher, 404
- database naming conventions, 243
- Git, 521, 655
- globally unique identifier (GUID), 254
- Google Cloud, 235
- Gravina Island Bridge, 538
- Jupyter Notebook, 329
- MariaDB, 491
- Microsoft Transact-SQL, 491
- MongoDB Atlas, 434
- MySQL, 491
- openCypher, 404
- Oracle SQL, 491
- PostgreSQL, 370, 491
- SQL tutorials, 491
- SQL versions, 491–492
- Visual Basic, 264
- Visual Studio, 331
- W3School, 67
WHERE
clause, 350, 363, 397, 511, 513
- wide-column databases. See column-oriented databases
- Widenius, Michael (author), 677
Windows
directory, 57
WriteData
method, 480–481, 482–483
write_transaction
method, 405, 412
- writing
- requirements documents, 279–280
- use cases, 275–278
X
- XML files
- about, 60–61, 66
- basics of, 61–64
- practices, 64–66
- uses for, 75–76
..................Content has been hidden....................
You can't read the all page of ebook, please click
here login for view all page.