Absolute Beginner’s Guide to Databases

Absolute Beginner’s Guide to Databases
اسم المؤلف
John V. Petersen
التاريخ
14 يونيو 2021
المشاهدات
التقييم
(لا توجد تقييمات)
Loading...

Absolute Beginner’s Guide to Databases
John V. Petersen
Contents at a Glance
Introduction, 1
Part I Database Basics, 5
1 An Introduction to Databases, 7
2 The Anatomy of a Real
Database, 19
Part II Designing and Building Your
First Database, 43
3 An Introduction to Database
Design, 45
4 Database Design Continued: An
Introduction to Normalization, 69
5 Using Access to Build the Time
Entry and Billing Database, 95
Part III Maintaining Your Database—
An Introduction to Database
Integrity, 119
6 Maintaining Integrity Through
Data Validation Rules, 121
7 The Basics of Referential
Integrity, 147
Part IV Using Your Database to Provide
Information—An Introduction
to SQL, 157
8 SQL Basics, 159
9 SQL—Going Beyond the
Basics, 187
10 Making SQL Queries a Part of Your
Database, 217
Part V Putting Your Database to
Work—Building a Simple Access
Database Application, 235
11 Building the User Interface
Components, 237
12 Providing Useful Output: An
Introduction to Creating and
Designing Reports, 263
Appendixes
A The Structure of the TEB
Database, 277
B TEB Referential Integrity Rules, 283
C Glossary, 285
D Database-Oriented Periodicals, 295
E Web-Based Resources, 297
Index, 299
Table of Contents
Introduction 1
I Database Basics
1 An Introduction to Databases 7
What Is a Database? 8
How Databases Are Used 9
Typical Database Management Systems Used
Today 10
Key Database Terminology 11
Database 12
Tables and Entities 12
Fields, Columns, and Attributes 12
Normalization 12
Rows and Records 12
Primary Key 13
The Relational Database Model:
An Overview 13
Database-Related Careers 17
Database Administrator 17
Database Applications Programmer 17
Database Analyst 18
What You Have Learned 18
2 The Anatomy of a Real Database 19
An Introduction to Microsoft Access 20
The Sample Northwind Traders Database 22
The Database’s Relationships 22
Overview of the Database’s
Components 26
What You Have Learned 40
II Designing and Building Your First
Database
3 An Introduction to Database Design 45
Your Project: A Time Entry and Billing
Database 46
Determine the Requirements of the
Database 47
Interviews 47
Business Forms and Documents 48
Existing Systems 49
Introduction to Database Modeling 50
What Does a Database Model Look
Like? 51
Creating Your First Model 52
Creating a Physical Database from the
Logical Database Model 60
Creating a Logical Database Model from a
Physical Database 61
Model the Time Entry and Billing
Database 62
Editing the Text 63
Refining the Design 63
What You Have Learned 66
4 Database Design Continued: An
Introduction to Normalization 69
What Does It Mean to Normalize a
Database? 70
Steps to Normalize Your Data Model 71
First Normal Form: Eliminating Repeating
Groups 72
Second Normal Form: Eliminating
Redundant Data 73vi ABSOLUTE BEGINNER’S GUIDE TO DATABASES
Third Normal Form: Eliminating Columns
Not Dependent on Keys 79
Fourth and Fifth Normal Forms 81
Normalization: Some Final Thoughts 83
Denormalize Data—When Does It Make
Sense to Break the Rules? 83
Normalization Applied—Review the TEB
Database and Refine the Design 84
Identifying New Columns and Tables 85
Reviewing the TEB Model 91
What You Have Learned 93
5 Using Access to Build the Time Entry
and Billing Database 95
Automatically Generate the Database from
the TEB Model 96
A Brief Review of the Access Database
Designer 99
Main Database Window 100
Designer Window 102
Use the Design to Create the Time Entry and
Billing Database 103
Creating the Database 103
Creating Tables and Columns 106
Creating Relationships 112
What You Have Learned 117
III Maintaining Your Database—An
Introduction to Database Integrity
6 Maintaining Integrity Through Data
Validation Rules 121
An Overview of How Validation Rules Work
in Databases 122
Implement Field-Level Rules 123
Field-Level Properties 123
Implement Row-Level Rules 143
Row-Level Properties 143
What You Have Learned 145
7 The Basics of Referential Integrity 147
What Is Referential Integrity? 148
Why Maintaining Referential Integrity Is
Important 148
How Referential Integrity Works 149
Establishing Referential Integrity Rules for
the TEB Database 150
Restricted Deletes 151
Restricted Inserts 152
Cascading Deletes 153
Cascading Updates 153
What You Have Learned 154
IV Using Your Database to Provide
Information—An Introduction to SQL
8 SQL Basics 159
SQL Overview 160
Interactively Working with SQL in
Access 161
The DDL and DCL SQL
Sublanguages 162
Select Statement 164
Selecting Specific Fields 165
Renaming Fields with the As Clause 166
Combining Field Data 167
Top n Select Statements 169
Filter Results I: Use the Where Clause 170
Organize Output I: Use the Order
By Clause 172
Compound Sort Orders 173Combine Multiple Tables: Use the Join
Statement 176
Inner Joins 177
Left Outer Joins 180
Right and Full Outer Joins 182
Combine Multiple Resultsets: Use the Union
Clause 184
What You Have Learned 186
9 SQL—Going Beyond the Basics 187
Organize Output II: Use the Group
By Clause 188
Use Aggregate Functions 190
The Count() Function 190
Advanced Operations: A Methodology for
Putting It All Together 192
The Sum() Function 196
The Avg() Function 204
The Min() and Max() Functions 205
Filter Results II: Use the Having
Clause 206
Filter Results III: Create Parameterized SQL
Statements 207
Modify Data with the Insert, Update, and
Delete Statements 210
The Insert Statement 210
The Update Statement 212
The Delete Statement 213
What You Have Learned 215
10 Making SQL Queries a Part of Your
Database 217
An Overview of the Access Query
Designer 218
Adding Select Fields 220
Adding Selection Criteria 221
CONTENTS vii
Suppressing Columns 223
Adding a Sort Order 225
Sorting on Multiple Fields 226
Saving Your Query 227
Create Advanced Queries 228
Implementing Aggregate Functions and
Groupings 230
Parameterized Queries 232
What You Have Learned 234
V Putting Your Database to Work—
Building a Simple Access Database
Application
11 Building the User Interface Components
237
Overview of the Access Form Designer 238
Create Forms for the TEB Application 239
Client 239
Employee Form 256
Create a Main Menu Form 258
What You Have Learned 262
12 Providing Useful Output: An Introduction
to Creating and Designing Reports 263
Overview of the Access Report Designer 264
Create Reports for the TEB Application 265
Client Listing 265
Modifying the ClientListing Report in the
Report Designer 271
Add Report Items to the Main Menu
Form 272
What You Have Learned 275A The Structure of the TEB Database 277
B TEB Referential Integrity Rules 283
C Glossary 285
D Database-Oriented Periodicals 295
E Web-Based Resources 297
Index 299
A
Access, 11
buttons, Open/View, 27
Easy Microsoft Access 2000, 20
main database dialog box, 22
Microsoft Office Pro Magazine, 20
Northwind Traders Database, 20-26
normalization, 70
queries, 31
SQL (Structured Query Language) windows, 161
aggregate functions, 190
As clause, 198
Avg() functions, 204
combining field data, 167-169
combining multiple tables, 176
compound sort orders, 173-175
Count() functions, 190-191, 195
Delete statements, 213-214
Distinct clause, 178
From clause, 192-193
full outer joins, 182-184
Group By clause, 188-189, 196, 202
Having clause, 206
inner joins, 177-178
Insert statements, 210-211
Join clause, 193
left outer joins, 180-181
limiting results, 181
Min() and Max() functions, 205, 211
Order By Clause, 172-173, 202
parameterized statements, 207-208
renaming fields, 166, 198
returning data from multiple tables, 179-181
right joins, 182-184
Select statement, 164-165
selecting specific fields, 165
Sum() functions, 196-197
Top n Select statement, 169-170
Union clause, 184-185
Update statements, 212
Where Clause, 170-171, 204-206, 212-214
starting, 20-22
startup dialog box, 21
tables, 26
Unicode Compression, 30
Using Microsoft Access 2000, 20
views (Datasheet), 151
Web sites, 20
Access Database Designer
columns, creating, 106-108
components, 99-102
databases, creating, 103-112
dialog boxes
Save As, 108
Show Table, 112-113
fields, creating, 107-112
Primary Key button, 107
Relationships Menu, 112-114
tables, creating/saving, 106-107
Tools Menu, 112, 116
Index300 ACCESS DATABASE DESIGNER
Alphabetical List of Products Query, 35
ANSI (American National Standards Institute),
160
ANSI-SQL 92, 160
As clause
renaming fields, 166
SQL (Structured Query Language), 198
Attributes tab, 56
automatic method database creation (TEB
database model), 96-99
Autonumber data types, 29
Avg() functions (SQL), 204
B
Bound Column (Lookup tab), 41
Boyce-Codd Normal Form, 71
business forms/documents, determining
database requirements, 48
BusinessClient
forms, 246
queries, 242
buttons
Create (Access Database Designer), 104
Create New (Access Database Relationship
Builder), 114
custom, adding, 249, 252
Open, 27, 31
Primary Key, 107
Relationship View, 22
View, 27, 33, 41
views
Datasheet, 106
Design, 106-107
wizards
Import Table, 106
Link Table, 107
Table, 106
Access Database Relationship Builder
check boxes
Enforce Referential Integrity, 114
Referential Integrity, 115
Create New button, 114
dialog boxes
Create New Relationships, 114
Documenter, 116
Edit Relationships, 114, 150-151
New Relationships, 114
Access Query Designer, 218
aggregate functions/groupings, 230-231
columns, suppressing, 223
fields
adding, 220
sorting multiple fields, 226
queries
creating advanced queries, 228-229
parameterized, 232-233
saving, 227
selection criteria, adding, 221
sort orders, adding, 225
adding
columns, 108
custom buttons, 249, 252
report items, main menu forms, 272-274
advanced queries, creating, 228-229
aggregate functions (SQL), 190
Allow Zero Length property, 30, 127-129CREATING 301
C
Caption property, 30
careers, database-related, 17-18
cascading deletes (referential integrity constraints), 150, 153
cascading updates (referential integrity constraints), 150, 153-154
Case tables, 88
changing database object names, 108
characters (Unicode Compression), 30
check boxes
Enforce Referential Integrity (Access Database
Relationship Builder), 114
Referential Integrity (Access Database
Relationship Builder), 115
child tables, 23
clauses (SQL)
As, 198
From, 192-193
Group By, 188-189, 196, 202
Having, 206
Join, 193
Order By, 202
Where, 204-206, 212-214
client
forms, 239
BusinessClient forms/queries, 242, 246
custom buttons, 249, 252
IndividualClient forms/queries, 240, 244,
253-254
layouts, modifying, 248
listing reports
creating, 265-269
modifying, 271
Client tables, 86
clipboards (Windows), 55
Codd, Dr. E. F., 13, 18
Column Count (Lookup tab), 41
Column Heads (Lookup tab), 41
Column Widths (Lookup tab), 41
columns, 8
adding, 108
creating, 106
suppressing, 223
combining field data (SQL), 167-169
Command Button Wizard, 274
compound
primary keys, 76
sort orders (SQL), 173-175
computer programming languages
DCL (Data Control Language), 160-163
DDL (Data Definition Language), 160-163
DML (Data Manipulation Language), 160-163
SQL (Structured Query Language), 160
constraints (referential integrity), 150, 153-154
Contact tables, 87-88
Count() functions (SQL), 190-191, 195
Court tables, 89
Create button (Access Database Designer), 104
Create Database Wizard (Visio), 60, 96-97
Create New button (Access Database
Relationship Builder), 114
Create New Relationships dialog box (Access
Database Relationship Builder), 114
creating
client forms, 239
adding custom buttons, 249, 252
BusinessClient forms, 246302 CREATING
BusinessClient queries, 242
employee forms, 256-257
IndividualClient forms, 244, 253-254
IndividualClient queries, 240
modifying layout, 248
columns, 106
database models, 52-61
databases, 59, 103-112
entities (Visio), 55-56
fields, 107-112
main menu forms, 258, 261
physical databases, 96-99
relationships, 112-114
reports, 265-271
tables, 106
“Crow’s Foot”
database models, 59
diagrams, 61
CRUD (Create, Update, and Delete) operations,
160
Currency data types, 29
Current Product List Query, 31-33
D
data
concatenating, 167
dictionary, 14
entry forms, 39
fields, combining, 167-169
normalization, 12
types, 29
validation rule enforcement, 122
Data/Time data types, 29
Database Administrator (DBA), 17
Database Analyst, 18
Database Applications Programmer, 17
Database icon (Access Database Designer), 104
database management system (DBMS). See
DBMS (database management system)
database-related careers, 17-18
databases
Access, 11, 20-22
Access Database Designer, 96-112
Case, 88
Client, 86
Contact, 87-88
Court, 89
“crow’s foot” model, 59
defining, 8, 12
denormalizing, 83
Department, 86
design, 63
guidelines, 93
tools, 51
development, 116
Employee, 85-86
EmployeeClass, 86
empty, 103-104
fields, 8, 12
forms, 39
importing, 61
Input Mask wizard, 134
integrity, 122, 148-154
Invoices, 91
models, 51
creating, 52-61
“Crow’s Foot” Diagrams, 61
ErWin (Computer Associates), 52
normalizing, 71-78
Relationships View, 51
TEB (Time Entry and Billing), 84-85, 96
VADBE (Visible Analyst Database Engineer),
52DIAGRAMS 303
Visio, 51-52
xCase (Resolution), 51-52
normalization, 12
Boyce-Codd Normal Form, 71
Fifth Normal Form, 81-82
First Normal Form (1NF), 72-73
Fourth Normal Form, 81-82
Second Normal Form, 73-78
Third Normal Form, 79-80
objects, changing names, 108
relational, 13-16
relationships, 22-25, 112
reports, 36
requirements, determining, 47-48
rows, 8
rules (field-level), 122
Allow Zero Length, 127-129
Default Value, 138
field-level properties, 123
Format, 128-134
Input Mask, 134-138
Required, 124-128
Validation Rule, 139-140
Validation Text, 141-142
rules (row-level), 143
row-level properties, 143
Validation Rule, 144
Validation Text, 144-145
SQL (Structured Query Language), 160-163
tables, 8, 12, 26
TEB (Time Entry and Billing), 62-66
TimeEntryDetail, 89-91
title shape, 63
uses, 9-10
WorkCategory, 90-91
Databases tab (Access Database Designer), 103
Datalist View, 27, 30, 37
Datasheet View, 27, 106, 151
DataType property, 29
DBA (Database Administrator). See Database
Administrator (DBA)
DBMS (database management system), 10-11
DCL (Data Control Language), 160-163
DDL (Data Definition Language), 160-163
Default Value property, 30, 138
Define Entity dialog box, 56
Delete statements (SQL), 213-214
deletes, restricted (referential integrity), 151
deleting records, 151
denormalization, 83
Department tables, 86
Description property, 30
design guidelines, database, 93
Design Surface (Visio), 52-53
Design Symbol toolbox, 54-56, 63
design tools, database, 51
Design View, 27
Products by Category Report, 38
Products Form, 41
Design View (Access Database Designer),
106-107
Designer (Visio), 52-53
Designer Window (Access Database Designer),
102
designers (Access Database Designer), 99-112
designing databases, 63
diagrams
“Crow’s Foot”, 61
ERDs (entity relationship diagrams), 70
.vsd (Visio) files, 97304 DIALOG BOXES
dialog boxes
Create New Relationships (Access Database
Relationship Builder), 114
Define Entity, 56
Documenter (Access Database Relationship
Builder), 116
Edit Relationships (Access Database Relationship
Builder), 114, 150-151
main database, 22
New Relationships (Access Database Relationship
Builder), 114
New Table, 106-107
Save As (Access Database Designer), 108
Show Table (Access Database Designer), 112-113
startup, 21
Display Control (Lookup tab), 40
Distinct clause (SQL), 178
DML (Data Manipulation Language), 160-163
Documenter dialog box (Access Database
Relationship Builder), 116
documenting database development, 116
documents (business). See business
forms/documents
E
Easy Microsoft Access 2000, 20
Edit Relationships dialog box (Access Database
Relationship Builder), 114, 150-151
editing text, 63
employee forms, creating, 256-257
Employee tables, 85-86
EmployeeClass tables, 86
empty databases, creating, 103-104
Enforce Referential Integrity check box (Access
Database Relationship Builder), 114
entities, 8
connecting, 56
creating (Visio), 55-56
Define Entity dialog box, 56
TEB (Time Entry and Billing) database, 62
ERDs (entity relationship diagrams), 52, 55,
60, 70
ErWin (Computer Associates), 52
establishing referential integrity, 150
F
Field Size property, 30
field-level properties, 123
Allow Zero Length, 127-129
Default Value, 138
Format, 128-134
Input Mask, 134-138
Required, 124-128
Validation Rule, 139-140
Validation Text, 141-142
FieldName property, 29
fields, 8
adding, 220
attributes, 12
combining data (SQL), 167-169
creating, 107-112
defining, 12
multiple, sorting, 226
null values, 14
primary key, 107
properties, 29-30
renaming (SQL), 166, 198
selecting (SQL), 165
Fifth Normal Form, 81-82
File New Database dialog box (Access
Database Designer), 104INTEGRITY 305
files
TEB.MDB, 105
.vsd (Visio) diagram, 97
First Normal Form (1NF), 72-73
foreign keys, 59
Form Wizard
BusinessClient forms, 246
custom buttons, 249, 252
employee forms, 256-257
IndividualClient forms, 244, 253-254
layout, modifying, 248
Format property, 30, 128-134
forms
business. See business forms/documents
client, creating, 239
BusinessClient forms, 246
BusinessClient queries, 242
employee forms, 256-257
IndividualClient forms, 244, 253-254
IndividualClient queries, 240
custom buttons, 249, 252
data entry, 39
layout, modifying, 248
main menu
creating, 258, 261
report items, adding, 272, 274
Northwind Traders Database, 39
Products Form, 40-41
Fourth Normal Form, 81-82
From clause (SQL), 192-193
full outer joins (SQL), 182-184
functions (SQL) windows
aggregate, 190
Count(), 190-191, 195
G–H
General tab, 56, 103-104
Group By clause (SQL), 188-189, 196, 202
guidelines (database design), 93
Having clause (SQL), 206
highlighting text, 63
I
icons, database (Access Database Designer),
104
identifying entities (TEB database), 62-63
Import Table wizards (Access Database
Designer), 106
importing databases, 61
Indexed property, 30
IndividualClient
forms, 244, 253-254
queries, 240
inner joins (SQL), 177-178
Input Mask property, 30, 134-138
Input Mask wizard, 134
Insert statements (SQL), 210-211
inserts, restricted (referential integrity), 152
integrity (database)
data validation rule enforcement, 122
referential, 122, 148
constraints, cascading deletes, 150, 153
constraints, cascading updates, 150,
153-154
establishing, 150
maintaining, 148-149
restricted deletes, 151
restricted inserts, 152306 INTERVIEWS
interviews, determining database requirements, 47-48
Invoice tables, 91
J–K
Join clause (SQL), 193
joins (SQL)
full outer, 182-184
inner, 177-178
left outer, 180-181
right, 182-184
statements, 176
junction tables, 24
keys
F2 key, highlighting text, 63
foreign, 59
multivalued, 76
primary, 13, 24, 75-76, 107
surrogate, 76
Update Foreign Keys, 59
L
languages (SQL)
ANSI (American National Standards Institute),
160
DCL (Data Control Language), 160-163
DDL (Data Definition Language), 160-163
DML (Data Manipulation Language), 160-163
layout, modifying forms, 248
left outer joins (SQL), 180-181
Limit To List (Lookup tab), 41
lines, relationship, 56
Link Table wizards (Access Database Designer),
107
List Rows (Lookup tab), 41
List Width (Lookup tab), 41
Lookup tab, 28, 40-41
M
main database dialog box (Access), 22
Main Database Window (Access Database
Designer), 100-101
main menu forms, 258, 261, 272-274
maintaining referential integrity, 148-149
Many-to-Many relationships, 23-25
many-to-many resolver tables, 85
Map Database Wizard, 61-62
masks (Input Mask), 30
meaningful keys. See multivalued keys
meaningless keys. See surrogate keys
Memo data types, 29
menus
Relationships (Access Database Designer),
112-114
Shape (Visio), 59
Tools (Access Database Designer), 112-116
metadata. See data dictionary
methods, database creation, 96-99
Microsoft Developer Network Knowledge
Base. See MSDN KB (Microsoft Developer
Network Knowledge Base)
Microsoft Knowledge Base Web site, 20
Microsoft Office Pro Magazine, 20
Microsoft Support newsgroup, 20
Min() and Max() functions (SQL), 205, 211PRODUCTS BY CATEGORY REPORT 307
models, databases
creating, 52-61
“Crow’s Foot” Diagrams, 61
ErWin (Computer Associates), 52
normalizing, 71, 73-82
relational databases, 13
Relationships View, 51
TEB (Time Entry and Billing), 62-66, 84-85, 96
VADBE (Visible Analyst Database Engineer), 52
Visio, 51-52
xCase (Resolution), 51-52
modifying
ClientListing reports, 271
form layouts, 248
MSDN KB (Microsoft Developer Network
Knowledge Base) Web sites, 105
multitable queries, 35
multivalued keys, 76
N
names, database object, 108
New Relationships dialog box (Access Database
Relationship Builder), 114
New Table dialog box, 106-107
newsgroups (Microsoft Support), 20
normalization, 12
Boyce-Dodd Normal Form, 71
data, 12
databases, 12
Fifth Normal Form, 81-82
First Normal Form (1NF), 72-73
Fourth Normal Form, 81-82
Northwind Traders Database, 70
Second Normal Form, 73-78
tables, 12
TEB (Time Entry and Billing) database models,
84-85
Third Normal Form, 79-80
Northwind Traders Database, 20-26
forms, 39
normalization, 70
queries, 31
reports, 37
null values, 14
Number data types, 29
O
One-to-Many relationships, 23, 112
One-to-One relationships, 23
Open button, 27, 31
opening Access, 20-22
Order By Clause (SQL), 172-173, 202
orphaned records, 183
P
parameterized SQL (Structured Query
Language) statements, 207-208
parent tables, 23
physical databases, creating, 96-99
Primary Key button, 107
primary keys, 13, 24, 75-76, 107
Print Preview View (Products by Category
Report), 38
Product Form, 40
Products by Category Query, 37
Products by Category Report, 38308 PRODUCTS FORM
adding sort orders, 225
creating advanced queries, 228-229
implementing aggregate functions/
groupings, 230-231
parameterized queries, 232-233
saving queries, 227
sorting multiple fields, 226
suppressing columns, 223
Alphabetical List of Products, 35
BusinessClient, 242
Current Product List, 31-33
IndividualClient, 240
multitable, 35
Products by Category, 37
saving, 227
SQL (Structured Query Language) window
aggregate functions, 190
As clause, 198
Avg() functions, 204
Count() functions, 190-191, 195
Delete statements, 213-214
From clause, 192-193
Group By clause, 188-189, 196, 202
Having clause, 206
Insert statements, 210-211
Join clause, 193
Min() and Max() functions, 205, 211
Order By clause, 202
Sum() functions, 196-197
Update statements, 212
Where clause, 204-206, 212-214
structure, 31
Query Wizard
BusinessClient queries, 242
IndividualClient queries, 240
Products Form (Design View), 41
programming languages (SQL), 160
DCL (Data Control Language), 160-163
DDL (Data Definition Language), 160-163
DML (Data Manipulation Language), 160-163
properties
Allow Zero Length, 30
Caption, 30
DataType, 29
Default Value, 30
Description, 30
Field Size, 30
field-level, 123
Allow Zero Length, 127-129
Default Value, 138
Format, 128-134
Input Mask, 134-138
Required, 124-128
Validation Rule, 139-140
Validation Text, 141-142
FieldName, 29
Format, 30
Indexed, 30
Input Mask, 30
Required, 30
row-level, 143
Validation Rule, 144
Validation Text, 144-145
Validation Rule, 30
Validation Text, 30
Q
queries, 31
Access Query Designer, 218
adding fields, 219-220
adding selection criteria, 221
TEAMFLYRULES 309
R
RDBMs (Relational Database Management
Systems), 122
records, 8, 12
deleting, 151
orphaned, 183
referential integrity, 122, 148
constraints
cascading deletes, 150, 153
cascading updates, 150, 153-154
establishing, 150
maintaining, 148-149
restricted
deletes, 151
inserts, 152
Referential Integrity check box (Access
Database Relationship Builder), 115
Relational Database Management Systems. See
RDBMs (Relational Database Management
Systems)
relational databases, 13-16
Relational Model of Data for Large Shared
Data Banks, A (Codd, Dr. E. F.), 13
Relationship View button, 22
relationships
Access Database Relationship Builder, 114
creating, 112-114
databases, 22-24
lines, 56
Many-to-Many, 23-25
One-to-Many, 23, 112
One-to-One, 23
symbols, 56
TEB (Time Entry and Billing) database, 62-63
views, 22
Relationships menu (Access Database
Designer), 112-114
Relationships View, 51
renaming fields (SQL window), 166, 198
report items (main menu forms), 272-274
Report Wizard, 267-269
reports, 36
ClientListing report, 271
creating, 265-269
Products by Category, 38
Required property, 30, 124-128
requirements (databases), determining, 47-49
resolver tables (many-to-many), 85
restricted deletes (referential integrity), 151
restricted inserts (referential integrity), 152
results, limiting (SQL), 181
right joins (SQL), 182-184
Row Source (Lookup tab), 41
Row Source Type (Lookup tab), 40
row-level properties, 143-145
rows, 8, 12
rules
data validation rule enforcement, 122
field-level properties, 122
Allow Zero Length, 127-129
Default Value, 138
field-level, 123
Format, 128-134
Input Mask, 134-138
Required, 124-128
Validation Rule, 139-140
Validation Text, 141-142
relational databases, 13-16
row-level properties, 143310 RULES
Validation Rule, 144
Validation Text, 144-145
S
Save As dialog box (Access Database
Designer), 108
saving
queries, 227
tables, 107
Second Normal Form, 73-78
Select statements (SQL), 164-165
Shape menu (Visio), 59
shapes (database title), 63
Show Table dialog box (Access Database
Designer), 112-113
sort orders, 225
sorting multiple fields, 226
SQL (Structured Query Language) window
ANSI-SQL 92, 160
clauses
As, 198
From, 192-193
Group By, 188-189, 196, 202
Having, 206
Join, 193
Order By, 202
Where, 204, 206, 212-214
compound sort orders, 173, 175
CRUD (Create, Update, and Delete) operations,
160
Distinct clause, 178
fields
combining data, 167-169
renaming, 166, 198
selecting, 165
full outer joins, 182-184
functions
aggregate, 190
Avg(), 204
Count(), 190-191, 195
Min() and Max(), 205, 211
Sum(), 196-197
joins
full outer, 182-184
inner, 177-178
left outer, 180-181
right, 182-184
limiting results, 181
Order By Clause, 172-173
statements
Delete, 213-214
Insert, 210-211
parameterized, 207-208
Select, 164-165
Update, 212
sublanguages
DCL (Data Control Language), 160-163
DDL (Data Definition Language), 160-163
DML (Data Manipulation Language),
160-163
tables
combining, 176
returning data, 179-181
Top n Select statements, 169-170
Union clause, 184-185
Where Clause, 170-171
starting Access, 20-22
Startup dialog boxes (Access), 21
statements (SQL window)
Delete, 213-214
Insert, 210-211
parameterized, 207-208
Update, 212TEB 311
strategic decisions, determining database
requirements, 48
Structured Query Language. See SQL
(Structured Query Language)
structures
queries, 31
tables
Case, 88
Contact, 87-88
Employee tables, 86
Invoice, 91
TimeEntryDetail tables, 90
WorkCategory, 91
Sum() functions (SQL), 196-197
surrogate keys, 76
symbols
Design Symbol Toolbox (Visio), 54-56
relationships, 56
systems
DBMS (database management system), 10-11
existing, 49
T
Table Datalist View, 39
Table Designer (Lookup tab), 40
Table wizards (Access Database Designer), 106
tables, 8, 26
Case, 88
child, 23
Client, 86
combining (SQL), 176
Contact, 87-88
Court, 89
creating, 106
defining, 12
Department, 86
dialog boxes (New Table), 106-107
Employee, 85-86
EmployeeClass, 86
FieldName property, 29
Invoice, 91
junction, 24
many-to-many resolver, 85
multitable queries, 35
normalization, 12
parent, 23
relationships
Access Database Relationship Builder, 114
creating, 112-114
Relationships View, 51
returning data (SQL), 179-181
rows, 12
saving, 107
Table Datalist View, 39
TimeEntryDetail, 89-91
views, 15
WorkCategory, 90-91
tabs
Attributes, 56
Databases (Access Database Designer), 103
General, 56
General (Access Database Designer), 103-104
Lookup, 28, 40-41
tactical decisions, determining database
requirements, 48
TEB (Time Entry and Billing) databases
models, 84-85, 96
tables
Case, 88
Client, 86
Contact, 87-88
Court, 89
Department, 86
Employee, 85-86312 TEB
EmployeeClass, 86
Invoice, 91
TimeEntryDetail, 89-91
WorkCategory, 90-91
TEB.LDB files, 105
TEB.MDB files, 105
text
editing, 63
highlighting, 63
Text data types, 29
Third Normal Form, 79-80
TimeEntryDetail tables, 89-91
toolboxes
Design Symbol (Visio), 54-56
Design Symbol toolbox, 63
tools, database design, 51
Tools menu (Access Database Designer),
112, 116
Top n Select statements (SQL), 169-170
U–V
understanding databases, 8
Unicode Compression, 30
Union clause (SQL), 184-185
Update Foreign Keys, 59
Update statements (SQL), 212
uses of databases, 9-10
Using Microsoft Access 2000, 20
VADBE (Visible Analyst Database Engineer), 52
Validation Rule property, 30, 139-140, 144
Validation Text Property, 30, 141-145
validations. See rules
View button, 27, 33, 41
views, 15
Datalist, 27, 30, 37
Datasheet (Access Database Designer), 27, 106,
151
Design (Access Database Designer), 27, 106-107
Products by Category Report, 38
Products Form, 41
Print Preview, 38
relationship, 22
Relationships, 51
Table Datalist, 39
Visible Analyst Database Engineer. See VADBE
(Visible Analyst Database Engineer)
Visio, 51-52
Create Database Wizard, 60, 96-97
Design Surface, 52-53
Design Symbol Toolbox, 55-56
Designer, 52-53
Diagram files, 97
entities, creating, 55-56
Map Database Wizard, 61
Shape menu, 59
Web site, 51
.vsd diagram files (Visio), 97
W
Web sites
Access, 20
ErWin (Computer Associates), 52
Microsoft Knowledge Base, 20
MSDN KB (Microsoft Developer Network
Knowledge Base), 105
VADBE (Visible Analyst Database Engineer), 52
Visio, 51
xCase (Resolution), 51-52
كلمة سر فك الضغط : books-world.net
The Unzip Password : books-world.net

تحميل

يجب عليك التسجيل في الموقع لكي تتمكن من التحميل
تسجيل | تسجيل الدخول

التعليقات

اترك تعليقاً