Chapter 1 Database Introduction 3

1. 1 reasons for using database 4

1. 1. 1 Question 4 on the list

1. 1.2 using relational database 7

Treatment of 1. 1.3 relation table 13

1.2 concept of database system 14

1.2. 1 database 15

1.2.2DBMS 16

1.2.3 Application 18

1.2.4 Comparison between personal database system and enterprise database system 20

1.3Access workbench: parts 1- Familiar with MicrosoftAccess22.

1.3. 1 Create Access Database23

1.3.2 Create database table 26

1.3.3 Insert data in the table-Datasheet View 34

1.3.4 Modify the data in the table-Datasheet View 37

1.3.5 Delete rows in the table-Datasheet View 38

1.3.6 Inserting Data into a Table-Using Form 40

1.3.7 modifying data and deleting records-using table 44

1.3.8 Create an access report for table 44.

1.3.9 Close the database and exit Access47.

1.4 Summary 48

1.5 review questions 49

1.6 Exercise 50

1.7 Visit Workbench Exercise 5 1

1.8GardenGlory project problem 52

1.9 James River Jewelry Store Project

Question 52

1. 10 queens safe storage project problem 53

Chapter II Relationship Model 55

2. 1 relationship 56

One relational example and two non-relational examples 57

2. 1.2 display relationship structure description 58

2. 1.3 Terminology 59

2.2 Type of key 59

2.2. 1 compound key 60

2.2.2 Candidate keys and primary keys 60

Proxy key 63

2.2.4 Foreign keys and referential integrity constraints 64

2.3 null problem 68

2.4 Functional Dependence and Standardization 68

2.4. 1 functional dependency 69

2.4.2 Re-discussing Primary Keys and Candidate Keys 70

2.4.3 Normalized 7 1

2.4.4 Relationship design principles

2.4.5 Standardization process 72

2.4.6 standardization example 74

2.5Access Workbench: Part II-Operating Multiple Tables in MicrosoftAccess 79

2.5. Possible modification problems in1wmcrm database 80

2.5.2 Manipulating Multiple Tables 84

2.5.3 Creating relationships between tables 86

2.5.4 Use Form 90, including two forms.

2.5.5 Create a report containing data from two tables 9 1.

2.5.6 Close the database and exit Access92.

2.6 Summary 93

2.7 Review questions 94

2.8 Exercise 95

2.9 Visit the workbench to practice 96

2. 10GardenGlory Project Question 99

2.11JamesRiverjeway Jewelry Project Problems 100

2.12 queenan security store project problem 10 1

Chapter 3 Structured Query Language 105

3. 1 sample database 106

3.2 SQL statement for data definition 1 10

3.2. 1 Define primary key with table constraints 1 15.

3.2.2 Use table constraints to define foreign keys 1 16.

3.2.3 Submit the SQL statement 1 18 to the DBMS.

3.3 SQL statement for inserting relational data 12 1

3.4SQL relational query statement 124

3.4.1sqlselect/from/where schema 124

3.4.2 Read the specified column 125 from a single table.

3.4.3 Read the specified line 127 from a single table.

3.4.4 Read the specified row and column 129 from a single table.

3.4.5 Specify the range, and use wildcards and null values 13 1 in the WHERE clause.

3.4.6 Sorting result 134

3.4.7SQL built-in functions and calculations 135

3.4.8 Built-in functions and grouping 138

3.4.9 Using subquery to process multiple tables 139

3. 4. 10 uses joins to query multiple tables 14 1

3.4. 1 1SQLJOIN…ON syntax 145

External connection 148

3.5 SQL statement for modifying and deleting relational data 150

3.5. 1 modified data 150

3.5.2 Delete data 1523.6 Modify and delete SQL statements of tables and constraints 153.

3. 6. 1 Deleteable and modifiable statements 153

Check constraints 154

3.7SQL view 155

3.8Access Workbench: Part III-Using Query 155 in MicrosoftAccess

3.8. 1 Use MicrosoftAccessSQL 156.

3.8.2 using MicrosoftAccessQBE 160

3.8.3 Query 165 with MicrosoftAccess parameters.

Using MicrosoftAccess

SQL Create Table 166

3.8.5 Modify the access table to add access rights.

SQL does not support data requirement 169.

3.8.6 insert data with MicrosoftAccessSQL 174.

3.8.7 Add referential integrity constraint 177 using AccessSQL.

3.8.8 Modify the Access database and add the constraint 178 that AccessSQL does not support.

3.8.9 Close the database and exit Access 180.

3.9 Summary 18 1

3. 10 review questions 182

3. 1 1 practice 184

3. 12Access workbench exercise 186

3. 13GardenGlory Project Problems 189

3. 14 James River Jewellery Project 190

3. 15 Kun 'an Safe Store Project 192

The second part is database design and management

Chapter 4 Data Modeling and Entity Relationship Model 197

4. 1 requirement analysis stage 198

4.2 Entity Relationship Data Model 199

4.2. 1 entity 199

Attribute 200

4.2.3 Identifier 200

4.2.4 Relationship 20 1

4.3 Entity Relationship Diagram 204

4. 3. 65438+ different versions of 0e-r model 205

4.3.2 Changes of E-R model in data modeling products 205

4.3.3 Vulnerable entities 207

4.3.4ID Dependent Entity 207

4.3.5 Weak Entity 209 Independent of Identifier

4.3.6 Subclass Entity 2 12

4.3.7 Recursive Relation 2 13

4.4 example of developing E-R diagram 2 14

4.4. 1 database of Heather Winnie Design Company 2 14

4.4.2 Customer list of training courses 2 14

4.4.3 Customer Letter Template 2 16

4.4.4 Sales Invoice 2 19

Attribute description 222

Business rules 224

4.4.7 Validating the Data Model 224

4.5Access Workbench: Part IV-Using MicrosoftAccess to Develop Prototype 225

4.5. 1 Create a form model for the original data model 226.

4.5.2 Create a form prototype for the modified data model 228.

4.5.3 Bandwidth Format of Access and Report Editor 230

4.5.4 Close the database and exit Access23 1.

4.6 Summary 23 1

4.7 Review Question 232

4.8 Exercise 234

4.9 Visit the workbench for exercise 234

4. 10 garden glory project problem 235

4.11jamesriverway project problem 235

4. 12 kunnan antique shop project problem 236

The fifth chapter database design 239

5. 1 Design scheme of transforming data model into database 240

5.2 Representing Entity 24 1

5.2. 1 Representation of project entity 24 1

5.2.2 Representation of Customer Entities 243

5.2.3 Relationship Design of Sales Commission Entities 246

5.2.4 Representation of vulnerable entities 247

5.3 Representation of Relationships 25 1

5.3. 1 Relationships in Strong Entities 25 1

5.3.2 Relationships using weak entities 258

5.3.3 Representation of the relationship between fruiting bodies 259

5.3.4 Representation of Recursive Relation 260

5.4 database design of Heather Sweeney Designs Company 264

5.4. 1 weak entity 265

Relationship 265

5.4.3 Mandatory referential integrity 266

5.5Access Workbench: Part V-Relationships in Microsoft Access 268

5.5. Many-to-many relationship in1access268

5.5.2 One-to-one relationship in interviews 268

5.5.3 Close the database and exit Access273.

5.6 Summary 273

5.7 Review Questions 274

5.8 Exercise 276

5.9 Visit the workbench for exercise 276

5. 10GardenGlory project problem 277

5. 1 1 james river jewelry store project problem 277

5.12 queenannecurity store project problem 278

Chapter VI Database Management 279

6. 1 heather weeney designs company database 280

6.2 Concurrency Control 287

6.2. 1 Necessity of using atomic transactions

6.2.2 Concurrent Transaction Processing 288

6.2.3 Lack of update problem 289

6.2.4 Concurrent problems: dirty reading, unrepeatable reading and phantom reading 290

Resource lock 290

6.2.6 Serializing Transactions 292

Deadlock 292

6.2.8 Optimistic Locking and Pessimistic Locking 293

6.2.9 Declaring locking features 294

6.2. 10 consistent transaction 295

6.2. 1 1 transaction isolation level 296

6.3 cursor type 297

6.4 Database Security 298

6.4. 1 user account 299

6.4.2 Handling Authority and Responsibility 300

6.4.3DBMS level security 304

6.4.4 Application-level security 305

6.5 Database Backup and Recovery 306

6.5. 1 Recovered by reprocessing 306

6.5.2 Recovery by Rollback and Rollforward 307

6.5.3 Other responsibilities of DBA 3 10

6.6 Processing of Distributed Database 3 10

6.6. 1 distributed database310 type

6.6.2 Challenges Faced by Distributed Databases 3 12

6.7 Object Relational Database 3 13

6.8Access Workbench: Part VI-Database Management in Microsoft Access 313

6.8. Database Security in1Access 3 14

6.8.2 Use of Protected Databases 322

6.8.3 Management of Protected Databases 324

6.8.4 Close the database and exit Access324.

6.9 Summary 324

6 10 review questions 326

6. 1 1 Exercise 328

6. 12 Visit the workbench for exercise 329

6. 13GardenGlory project problem 330

6. 14 James river jewelry store project problem 33 1

6.15 queenanne museum store project problem 332

Chapter 7 Database Processing Applications and Business Intelligence 335

7. 1 database processing environment 336

7. 1. 1 Queries, forms and reports 337

7. 1.2 Client/Server and Traditional Application Processing 339

7. 1.3 stored procedures and triggers 339

7.2 Network Database Processing 340

7.2. 1ODBC342

7.2.2 Web processing using IIS 345

7.2.3ActiveServerPages(ASP)349

Active data object (ADO)352

7. 2. 5 Network Challenges Faced by Network Database Processing 358

7.3 database processing and XML358

7.3. 1XML schema file 359

7.3.2XML and database processing 360

XMLWebServices362

7.4 Business Intelligence System 363

7.5Access Workbench: Part VII: Processing Web Database with MicrosoftAccess 367

7. 5. 1 Ford Motor Company Website Home Page 367

7.5.2 Selecting Database File 369

7.5.3 Creating ODBC Data Source 370

7.5.4 Create customer contact information view 37 1

7.5.5 Creating ASP Page 372

7.5.6 Running ASP Page 374

Close 374

7.6 Summary 375

7.7 review questions 376

7.8 Exercise 378

7.9 Visit the workbench to practice 380

7. 10GardenGlory Project Problem 38 1

7.11JamesRiverjeway Jewelry Project Question 38 1

7.12 queenannecurity store project problem 382

Appendix Microsoft SqlServer 2005 Expression Introduction 383

Appendix b introduction to bmysql 395

Appendix CSQL view 409

Vocabulary 427

……