Practical Database Programming with Visual Basic.NET E-Book

Ying Bai

• Herausgeber: John Wiley & Sons
• Kategorie: Wissenschaft und neue Technologien
• Sprache: Englisch

Seitenzahl: 1379

Veröffentlichungsjahr: 2012

Beschreibung

Practical Database Programming with Visual Basic.NET The most up-to-date Visual Basic.NET programming textbook--covering both fundamentals and advanced-level programming techniques--complete with examples and solutions Visual Basic.NET (VB.NET) is an object-oriented computer programming language that can be viewed as an evolution of the classic Visual Basic (VB), which is implemented on the .NET Framework. Microsoft currently supplies two major implementations of Visual Basic: Microsoft Visual Studio (which is commercial software) and Microsoft Visual Studio Express (which is free of charge). Forgoing the large amounts of programming codes found in most database programming books, Practical Database Programming with Visual Basic.NET shows students and professionals both how to develop professional and practical database programs in a Visual Basic.NET environment by using Visual Studio.NET Data Tools and Wizards related to ADO.NET 4.0, and how to apply codes that are auto-generated by solely using Wizards. The fully updated Second Edition: * Covers both fundamentals and advanced database programming techniques * Introduces three popular database systems with practical examples including MS Access, SQL Server 2008, and Oracle * Features more than fifty sample projects with detailed illustrations and explanations to help students understand key techniques and programming technologies * Includes downloadable programming codes and exercise questions This book provides undergraduate and graduate students as well as database programmers and software engineers with the necessary tools to handle the database programming issues in the Visual Studio.NET environment.

Leseprobe

Table of Contents

Cover

IEEE Press

Tilte page

Copyright page

Dedication

Preface

Acknowledgments

Chapter 1 Introduction

OUTSTANDING FEATURES ABOUT THIS BOOK

WHO THIS BOOK IS FOR

WHAT THIS BOOK COVERS

HOW THIS BOOK IS ORGANIZED AND HOW TO USE THIS BOOK

HOW TO USE THE SOURCE CODE AND SAMPLE DATABASES

INSTRUCTORS AND CUSTOMERS SUPPORTS

Chapter 2 Introduction to Databases

2.1 WHAT ARE DATABASES AND DATABASE PROGRAMS?

2.2 DEVELOP A DATABASE

2.3 SAMPLE DATABASE

2.4 IDENTIFYING KEYS

2.5 DEFINE RELATIONSHIPS

2.6 ER NOTATION

2.7 DATA NORMALIZATION

2.8 DATABASE COMPONENTS IN SOME POPULAR DATABASES

2.9 CREATE MICROSOFT ACCESS SAMPLE DATABASE

2.10 CREATE MICROSOFT SQL SERVER 2008 SAMPLE DATABASE

2.11 CREATE ORACLE 11G XE SAMPLE DATABASE

2.12 CHAPTER SUMMARY

Chapter 3 Introduction to ADO.NET

3.1 THE ADO AND ADO.NET

3.2 OVERVIEW OF ADO.NET

3.3 THE ARCHITECTURE OF ADO.NET

3.4 THE COMPONENTS OF ADO.NET

3.5 CHAPTER SUMMARY

Chapter 4 Introduction to Language Integrated Query (LINQ)

4.1 OVERVIEW OF LANGUAGE INTEGRATED QUERY

4.2 INTRODUCTION TO LINQ QUERY

4.3 THE ARCHITECTURE AND COMPONENTS OF LINQ

4.4 LINQ TO OBJECTS

4.5 LINQ TO DATASET

4.6 LINQ TO SQL

4.7 LINQ TO ENTITIES

4.8 LINQ TO XML

4.9 VISUAL BASIC.NET LANGUAGE ENHANCEMENT FOR LINQ

4.10 CHAPTER SUMMARY

Chapter 5 Data Selection Query with Visual Basic.NET

PART I DATA QUERY WITH VISUAL STUDIO.NET DESIGN TOOLS AND WIZARDS

5.1 A COMPLETED SAMPLE DATABASE APPLICATION EXAMPLE

5.2 VISUAL STUDIO.NET DESIGN TOOLS AND WIZARDS

5.3 QUERY DATA FROM SQL SERVER DATABASE USING DESIGN TOOLS AND WIZARDS

5.4 ADD AND UTILIZE VISUAL STUDIO WIZARDS AND DESIGN TOOLS

5.5 QUERY AND DISPLAY DATA USING THE DATAGRIDVIEW CONTROL

5.6 USE DATASET DESIGNER TO EDIT THE STRUCTURE OF THE DATASET

5.7 BIND DATA TO THE ASSOCIATED CONTROLS IN LOGIN FORM

5.8 DEVELOP CODES TO QUERY DATA USING THE FILL() METHOD

5.9 USE RETURN A SINGLE VALUE TO QUERY DATA FOR LOGIN FORM

5.10 DEVELOP THE CODES FOR THE SELECTION FORM

5.11 QUERY DATA FROM THE FACULTY TABLE FOR THE FACULTY FORM

5.12 DEVELOP CODES TO QUERY DATA FROM THE FACULTY TABLE

5.13 DISPLAY A PICTURE FOR THE SELECTED FACULTY

5.14 QUERY DATA FROM THE COURSE TABLE FOR THE COURSE FORM

5.15 DEVELOP CODES TO QUERY DATA FOR THE COURSE FORM

5.16 QUERY DATA FROM ORACLE DATABASE USING DESIGN TOOLS AND WIZARDS

PART II DATA QUERY WITH RUNTIME OBJECTS

5.17 INTRODUCTION TO RUNTIME OBJECTS

5.18 QUERY DATA FROM MICROSOFT ACCESS DATABASE USING RUNTIME OBJECT

5.19 QUERY DATA FROM SQL SERVER DATABASE USING RUNTIME OBJECT

5.20 QUERY DATA FROM ORACLE DATABASE USING RUNTIME OBJECT

5.21 CHAPTER SUMMARY

Chapter 6 Data Inserting with Visual Basic.NET

PART I DATA INSERTING WITH VISUAL STUDIO.NET DESIGN TOOLS AND WIZARDS

6.1 INSERT DATA INTO A DATABASE

6.2 INSERT DATA INTO THE SQL SERVER DATABASE USING A SAMPLE PROJECT INSERTWIZARD

6.3 INSERT DATA INTO THE ORACLE DATABASE USING A SAMPLE PROJECT INSERTWIZARDORACLE

PART II DATA INSERTION WITH RUNTIME OBJECTS

6.4 THE GENERAL RUNTIME OBJECTS METHOD

6.5 INSERT DATA INTO THE SQL SERVER DATABASE USING THE RUNTIME OBJECT METHOD

6.6 INSERT DATA INTO THE MICROSOFT ACCESS DATABASE USING THE RUNTIME OBJECTS

6.7 INSERT DATA INTO THE ORACLE DATABASE USING THE RUNTIME OBJECTS

6.8 INSERT DATA INTO THE DATABASE USING STORED PROCEDURES

6.9 INSERT DATA INTO THE DATABASE USING THE LINQ TO DATASET METHOD

6.10 CHAPTER SUMMARY

Chapter 7 Data Updating and Deleting with Visual Basic.NET

PART I DATA UPDATING AND DELETING WITH VISUAL STUDIO.NET DESIGN TOOLS AND WIZARDS

7.1 UPDATE OR DELETE DATA AGAINST DATABASES

7.2 UPDATE AND DELETE DATA FOR MICROSOFT SQL SERVER DATABASE

7.3 UPDATE AND DELETE DATA FOR ORACLE DATABASE

7.4 UPDATE AND DELETE DATA FOR MICROSOFT ACCESS DATABASE

PART II DATA UPDATING AND DELETING WITH RUNTIME OBJECTS

7.5 THE RUNTIME OBJECTS METHOD

7.6 UPDATE AND DELETE DATA FOR SQL SERVER DATABASE USING THE RUNTIME OBJECTS

7.7 UPDATE AND DELETE DATA FOR ORACLE DATABASE USING THE RUNTIME OBJECTS

7.8 UPDATE AND DELETE DATA AGAINST DATABASE USING STORED PROCEDURES

7.9 CHAPTER SUMMARY

Chapter 8 Accessing Data in ASP.NET

8.1 WHAT IS THE .NET FRAMEWORK?

8.2 WHAT IS ASP.NET?

8.3 DEVELOP ASP.NET WEB APPLICATION TO SELECT DATA FROM SQL SERVER DATABASES

8.4 DEVELOP ASP.NET WEB APPLICATION TO INSERT DATA INTO SQL SERVER DATABASES

8.5 DEVELOP WEB APPLICATIONS TO UPDATE AND DELETE DATA IN SQL SERVER DATABASES

8.6 DEVELOP ASP.NET WEB APPLICATIONS WITH LINQ TO SQL QUERY

8.7 DEVELOP ASP.NET WEB APPLICATION TO SELECT DATA FROM ORACLE DATABASES

8.8 DEVELOP ASP.NET WEB APPLICATION TO INSERT DATA INTO ORACLE DATABASES

8.9 DEVELOP ASP.NET WEB APPLICATION TO UPDATE AND DELETE DATA IN ORACLE DATABASES

8.10 CHAPTER SUMMARY

Chapter 9 ASP.NET Web Services

9.1 WHAT ARE WEB SERVICES AND THEIR COMPONENTS?

9.2 PROCEDURES TO BUILD A WEB SERVICE

9.3 BUILD ASP.NET WEB SERVICE PROJECT TO ACCESS SQL SERVER DATABASE

9.4 BUILD ASP.NET WEB SERVICE PROJECT TO INSERT DATA INTO SQL SERVER DATABASE

9.5 BUILD ASP.NET WEB SERVICE TO UPDATE AND DELETE DATA FOR SQL SERVER DATABASE

9.6 BUILD WINDOWS-BASED WEB SERVICE CLIENTS TO CONSUME THE WEB SERVICES

9.7 BUILD WEB-BASED WEB SERVICE CLIENTS TO CONSUME THE WEB SERVICES

9.8 BUILD ASP.NET WEB SERVICE PROJECT TO ACCESS ORACLE DATABASE

9.9 BUILD WEB SERVICE CLIENT PROJECTS TO CONSUME THE WEB SERVICE

9.10 BUILD ASP.NET WEB SERVICE PROJECT TO INSERT DATA INTO ORACLE DATABASE

9.11 BUILD WEB SERVICE CLIENT PROJECTS TO CONSUME THE WEB SERVICE

9.12 BUILD ASP.NET WEB SERVICE TO UPDATE AND DELETE DATA FOR THE ORACLE DATABASE

9.13 BUILD WEB SERVICE CLIENT PROJECTS TO CONSUME THE WEB SERVICE

9.14 CHAPTER SUMMARY

Index

About the Author

IEEE Press

445 Hoes Lane

Piscataway, NJ 08854

IEEE Press Editorial Board

Lajos Hanzo, Editor in Chief

R. Abari

M. El-Hawary

S. Nahavandi

J. Anderson

B. M. Hammerli

W. Reeve

F. Canavero

M. Lanzerotti

T. Samad

T. G. Croda

O. Malik

G. Zobrist

Kenneth Moore, Director of IEEE Book and Information Services (BIS)

Copyright © 2012 by the Institute of Electrical and Electronics Engineers, Inc.

Published by John Wiley & Sons, Inc., Hoboken, New Jersey. All rights reserved.

Published simultaneously in Canada.

No part of this publication may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, scanning, or otherwise, except as permitted under Section 107 or 108 of the 1976 United States Copyright Act, without either the prior written permission of the Publisher, or authorization through payment of the appropriate per-copy fee to the Copyright Clearance Center, Inc., 222 Rosewood Drive, Danvers, MA 01923, [phone], fax [phone], or on the web at www.copyright.com. Requests to the Publisher for permission should be addressed to the Permissions Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030, [phone], fax [phone], or online at http://www.wiley.com/go/permissions.

Limit of Liability/Disclaimer of Warranty: While the publisher and author have used their best efforts in preparing this book, they make no representations or warranties with respect to the accuracy or completeness of the contents of this book and specifically disclaim any implied warranties of merchantability or fitness for a particular purpose. No warranty may be created or extended by sales representatives or written sales materials. The advice and strategies contained herein may not be suitable for your situation. You should consult with a professional where appropriate. Neither the publisher nor author shall be liable for any loss of profit or any other commercial damages, including but not limited to special, incidental, consequential, or other damages.

For general information on our other products and services or for technical support, please contact our Customer Care Department within the United States at [phone], outside the United States at [phone] or fax [phone].

Wiley also publishes its books in a variety of electronic formats. Some content that appears in print may not be available in electronic formats. For more information about Wiley products, visit our web site at www.wiley.com.

Library of Congress Cataloging-in-Publication Data:

Bai, Ying, 1956–

 Practical database programming with Visual Basic.NET / Ying Bai. – 2nd ed.

p. cm.

 ISBN 978-1-118-16205-7 (pbk.)

 1. Microsoft Visual BASIC. 2. BASIC (Computer program language) 3. Microsoft .NET. 4. Database design. I. Title.

 QA76.73.B3B335 2012

 005.2'768–dc23

2011039947

This book is dedicated to my wife, Yan Wang,

and my daughter, Xue Bai.

Preface

Databases have become an integral part of our modern day life. We are an information-driven society. Database technology has a direct impact on our daily lives. Decisions are routinely made by organizations based on the information collected and stored in databases. A record company may decide to market certain albums in selected regions based on the music preference of teenagers. Grocery stores display more popular items at the eye level, and reorders are based on the inventories taken at regular intervals. Other examples include patients’ records in hospitals, customers’ account information in banks, book orders by the libraries, club memberships, auto part orders, winter cloth stock by department stores, and many others.

In addition to database management systems, in order to effectively apply and implement databases in real industrial or commercial systems, a good graphic user interface (GUI) is needed to allow users to access and manipulate their records or data in databases. Visual Basic.NET is an ideal candidate to be selected to provide this GUI functionality. Unlike other programming languages, Visual Basic.NET is a kind of language that has advantages, such as easy-to-learn and easy-to-be-understood with little learning curves. Beginning of Visual Studio.NET 2005, Microsoft integrated a few programming languages such as Visual C++, Visual Basic, C#, and Visual J# into a dynamic model called .NET Framework that makes Internet and Web programming easy and simple, and any language integrated in this model can be used to develop professional and efficient Web applications that can be used to communicate with others via the Internet. ADO.NET and ASP.NET are two important submodels of .NET Framework. The former provides all components, including the Data Providers, DataSet, and DataTable, to access and manipulate data against different databases. The latter provides support to develop Web applications and Web services in the ASP.NET environment to allow users to exchange information between clients and servers easily and conveniently.

This book is mainly designed for college students and software programmers who want to develop practical and commercial database programming with Visual Basic.NET and relational databases, such as Microsoft Access, SQL Server 2008, and Oracle Database 11 g XE. The book provides a detailed description about the practical considerations and applications in database programming with Visual Basic.NET 2010 with authentic examples and detailed explanations. More important, a new writing style is developed and implemented in this book, combined with real examples, to provide readers with a clear picture as how to handle the database programming issues in Visual Basic.NET 2010 environment.

The outstanding features of this book include, but not limited to:

1. A novel writing style is adopted to try to attract students’ or beginning programmers’ interesting in learning and developing practical database programs, and to avoid the headache caused by using huge blocks of codes in the traditional database programming books.

2. Updated database programming tools and components are covered in the book, such as .NET Framework 4.0, LINQ, ADO.NET 4.0, and ASP.NET 4.0, to enable readers to easily and quickly learn and master advanced techniques in database programming and develop professional and practical database applications.

3. A real completed sample database CSE_DEPT with three versions, Microsoft Access 2007, SQL Server 2008, and Oracle Database 11 g XE, is provided and used for entire book. Step by step, a detailed illustration and description about how to design and build a practical relational database is provided.

4. Covered both fundamental and advanced database programming techniques to convenience both beginning students and experienced programmers.

5. Various actual data providers are discussed and implemented in the sample projects, such as the SQL Server and Oracle data providers. Instead of using the OleDb to access the SQL Server or Oracle databases, the real SQL Server and Oracle data providers are utilized to connect to the Visual Basic.NET 2010 directly to perform data operations.

6. Good textbook for college students, and good reference book for programmers, software engineers, and academic researchers.

I sincerely hope that this book can provide useful and practical helps and guides to all readers or users who adopted this book, and I will be more than happy to know that you can develop and build professional and practical database applications with the help of this book.

YING BAI

Acknowledgments

The first and most special thanks to my wife, Yan Wang. I could not finish this book without her sincere encouragement and support.

Special thanks to Dr. Satish Bhalla, who is the chapter contributor for this book. Dr. Bhalla is a specialist in database programming and management, especially in SQL Server, Oracle, and DB2. Dr. Bhalla spent a lot of time to prepare materials for Chapter 2, and he is deserving of thanks.

Many thanks to Senior Editor Taisuke Soda and Associate Editor Mary Hatcher who made this book available to the public. You would not find this book in the market without their deep perspective and hard work. The same thanks are extended to the editorial team of this book. Without their contributions, it is impossible for this book to be published.

These thanks should also be extended to the following book reviewers for their precious opinions to this book:

Dr. Xintao Wu, Associate Professor, Department of Information and Systems, University of North Carolina at Charlotte

Dr. Xiaohong Yuan, Associate Professor, Department of Computer Science, North Carolina A&T State University

Dr. Daoxi Xiu, Application Analyst Programmer, North Carolina Administrative Office of the Courts

Dr. Dali Wang, Associate Professor, Department of Physics and Computer Science, Christopher Newport University

Last but not least, thanks should be forwarded to all people who supported me to finish this book.

Y. B.

Chapter 1

Introduction

For years, during my teaching database programming and Visual Basic.NET programming in my college, I found that it was too difficult to find a good textbook for this topic, so I had to combine a few different professional books together as references to teach this course. Most of those books are specially designed for programmers or software engineers, which cover a lot of programming strategies and huge blocks of codes, which is a terrible headache to college students or beginning programmers who are new to the Visual Basic.NET and database programming. I have to prepare my class presentations and figure out all homework and exercises for my students. I dream that one day I could find a good textbook that is suitable for college students or beginning programmers and help them to learn and master database programming with Visual Basic.NET easily and conveniently. Finally, I decided that I needed to do something for this dream myself after waiting for a long time.

Another reason for me to have this idea is the job market. As you know, most industrial and commercial companies in United States belong to database applications businesses, such as manufactures, banks, hospitals, and retails. Majority of them need professional people to develop and build database-related applications, but not database management and design systems. To enable our students to become good candidates for those companies, we need to create a book like this one.

Unlike most database programming books in the current market, which discuss and present database programming techniques with huge blocks of programming codes from the first page to the last page, this book tries to use a new writing style to show readers, especially to college students, how to develop professional and practical database programs in Visual Basic.NET 2010 by using Visual Studio.NET Design Tools and Wizards related to ADO.NET 4.0, and to apply codes that are autogenerated by using Wizards. By using this new style, the headache caused by using those huge blocks of programming codes can be removed; instead, a simple and easy way to create database programs using the Design Tools can be developed to attract students’ learning interest, and furthermore to enable students to build professional and practical database programming in more efficient and interesting ways.

There are so many different database programming books available on the market, but rarely can you find a book like this one, which implemented a novel writing style to attract the students’ learning interests in this topic. To meet the needs of some experienced or advanced students or software engineers, the book contains two programming methods: the interesting and easy-to-learn fundamental database programming method—Visual Studio.NET Design Tools and Wizards, and advanced database programming method—runtime object method. In the second method, all database-related objects are created and applied during or when your project is running by utilizing quite a few blocks of codes.

OUTSTANDING FEATURES ABOUT THIS BOOK

1. A novel writing style is adopted to try to attract students’ or beginning programmers’ interests in learning and developing practical database programs, and to avoid the headache caused by using huge blocks of codes in the traditional database programming books.

2. Updated database programming tools and components are covered in the book, such as .NET Framework 4.0, LINQ, ADO.NET 4.0, and ASP.NET 4.0, to enable readers to easily and quickly learn and master advanced techniques in database programming and develop professional and practical database applications.

3. A real completed sample database CSE_DEPT with three versions, Microsoft Access 2007, SQL Server 2008, and Oracle Database 11g XE, is provided and used for the entire book. Step by step, a detailed illustration and description about how to design and build a practical relational database are provided.

4. Covered both fundamental and advanced database programming techniques to convenience both beginning students and experienced programmers.

5. Various actual data providers are discussed and implemented in the sample projects, such as the SQL Server and Oracle data providers. Instead of using the OleDb to access the SQL Server or Oracle databases, the real SQL Server and Oracle data providers are utilized to connect to the Visual Basic.NET 2010 directly to perform data operations.

6. Provides homework and teaching materials, and these allow instructors to organize and prepare their courses easily and rapidly, and enable students to understand what they learned better by doing something themselves.

7. Good textbook for college students and good reference book for programmers, software engineers, and academic researchers.

WHO THIS BOOK IS FOR

This book is designed for college students and software programmers who want to develop practical and commercial database programming with Visual Basic.NET and relational databases, such as Microsoft Access, SQL Server 2008, and Oracle Database 11g XE. Fundamental knowledge and understanding on Visual Basic.NET and Visual Studio.NET IDE is assumed.

WHAT THIS BOOK COVERS

Nine chapters are included in this book. The contents of each chapter can be summarized as below.

Chapter 1 provides an introduction and summarization to the whole book.

Chapter 2 provides a detailed discussion and analysis of the structure and components about relational databases. Some key technologies in developing and designing database are also given and discussed in this part. The procedure and components used to develop a practical relational database with three database versions, such as Microsoft Access 2007, SQL Server 2008, and Oracle Database 11g XE, are analyzed in detail with some real data tables in our sample database CSE_DEPT.

Chapter 3 provides an introduction to the ADO.NET, which includes the architectures, organizations, and components of the ADO.NET. Detailed discussions and descriptions are provided in this chapter to give readers both fundamental and practical ideas and pictures in how to use components in ADO.NET to develop professional data-driven applications. Two ADO.NET architectures are discussed to enable users to follow the directions to design and build their preferred projects based on the different organizations of the ADO.NET. Four popular Data Provides, such as OleDb, ODBC, SQL Server, and Oracle, are discussed in detail. The basic ideas and implementation examples of DataTable and DataSet are also analyzed and described with some real coding examples.

Chapter 4 provides a detailed discussion and analysis about the Language Integrated Query (LINQ), which includes LINQ to Objects, LINQ to DataSet, LINQ to SQL, LINQ to Entities, and LINQ to XML. An introduction to the LINQ general programming guide is provided at the first part in this chapter. Some popular interfaces widely used in LINQ, such as IEnumerable, IEnumerable(Of T), IQueryable and IQueryable(Of T), and Standard Query Operators (SQO), including the deferred and nondeferred SQO, are discussed in that part. An introduction to LINQ Query is given in the second section in this chapter. Following this introduction, a detailed discussion and analysis about the LINQ queries that were implemented for different data sources is provided in detail.

Starting from Chapter 5, the real database programming techniques with Visual Basic.NET, such as data selection queries, are provided and discussed. Two parts are covered in this chapter: Part I contains the detailed descriptions in how to develop professional data-driven applications with the help of the Visual Studio.NET design tools and wizards with some real projects, and this part contains a lot of hiding codes that are created by Visual Basic.NET automatically when using those design tools and wizards. Therefore, the coding for this part is very simple and easy. Part II covers an advanced technique, the runtime object method, in developing and building professional data-driven applications. Detailed discussions and descriptions about how to build professional and practical database applications using this runtime method are provided combined with four real projects.

Chapter 6 provides detailed discussions and analyses about three popular data insertion methods with three different databases—Microsoft Access 2007, SQL Server 2008, and Oracle:

1. Using TableAdapter’s DBDirect methods TableAdapter.Insert() method.

2. Using the TableAdapter’s Update() method to insert new records that have already been added into the DataTable in the DataSet.

3. Using the Command object’s ExecuteNonQuery() method.This chapter is also divided into two parts: Methods 1 and 2 are related to Visual Studio.NET design tools and wizards, and therefore are covered in Part I. The third method is related to runtime object and therefore it is covered in Part II. Nine real projects are used to illustrate how to perform the data insertion into three different databases: Microsoft Access 2007, SQL Server 2008, and Oracle Database 11g XE. Some professional and practical data validation methods are also discussed in this chapter to confirm the data insertion.

Chapter 7 provides discussions and analyses on three popular data updating and deleting methods with seven real project examples:

1. Using TableAdapter DBDirect methods, such as TableAdapter.Update() and TableAdapter.Delete(), to update and delete data directly again the databases.

2. Using TableAdapter.Update() method to update and execute the associated Table­Adapter’s properties, such as UpdateCommand or DeleteCommand, to save changes made for the table in the DataSet to the table in the database.

3. Using the run time object method to develop and execute the Command’s method ExecuteNonQuery() to update or delete data again the database directly.This chapter is also divided into two parts: Methods 1 and 2 are related to Visual Studio.NET design tools and wizards and therefore are covered in Part I. The third method is related to runtime object and it is covered in Part II. Seven real projects are used to illustrate how to perform the data updating and deleting against three different databases: Microsoft Access, SQL Server 2008, and Oracle Database 11g XE. Some professional and practical data validation methods are also discussed in this chapter to confirm the data updating and deleting actions. The key points in performing the data updating and deleting actions against a relational database, such as the order to execute data updating and deleting between the parent and child tables, are also discussed and analyzed.

Chapter 8 provides introductions and discussions about the developments and implementations of ASP.NET Web applications in Visual Basic.NET 2010 environment. At the beginning of Chapter 8, a detailed and complete description about the ASP.NET and the .NET Framework is provided, and this part is especially useful and important to students or programmers who do not have any knowledge or background in the Web application developments and implementations. Following the introduction section, a detailed discussion on how to install and configure the environment to develop the ASP.NET Web applications is provided. Some essential tools, such as the Web server, IIS, and FrontPage Server Extension 2000, as well as the installation process of these tools, are introduced and discussed in detail. Starting from Section 8.3, the detailed development and building process of ASP.NET Web applications to access databases are discussed with six real Web application projects. Two popular databases, SQL Server and Oracle, are utilized as the target databases for those development and building processes.

Chapter 9 provides introductions and discussions about the developments and implementations of ASP.NET Web services in Visual Basic.NET 2010 environment. A detailed discussion and analysis about the structure and components of the Web services is provided at the beginning of this chapter. Two popular databases, SQL Server and Oracle, are discussed and used for three pairs of example Web service projects, which include:

1. WebServiceSQLSelect and WebServiceOracleSelect

2. WebServiceSQLInsert and WebServiceOracleInsert

3. WebServiceSQLUpdateDelete and WebServiceOracleUpdateDelete

Each Web service contains different Web methods that can be used to access different databases and perform the desired data actions, such as Select, Insert, Update, and Delete, via the Internet. To consume those Web services, different Web service client projects are also developed in this chapter. Both Windows-based and Web-based Web service client projects are discussed and built for each kind of Web service listed above. A total of 18 projects, including the Web service projects and the associated Web service client projects, are developed in this chapter. All projects have been debugged and tested and can be run in any Windows operating system, such as Windows 2000, XP, Vista, and Windows 7.

HOW THIS BOOK IS ORGANIZED AND HOW TO USE THIS BOOK

This book is designed for both college students who are new to database programming with Visual Basic.NET and professional database programmers who has professional experience on this topic.

Chapters 2, 3, and 4 provide the fundamentals on database structures and components, ADO.NET and LINQ components. Starting from Chapter 5, and then to Chapters 6 and 7, each chapter is divided into two parts: fundamental part and advanced part. The data driven applications developed with design tools and wizards provided by Visual Studio.NET, which can be considered as the fundamental part, have less coding loads, and, therefore, they are more suitable to students or programmers who are new to the database programming with Visual Basic.NET. Part II contains the runtime object method, and it covers a lot of coding developments to perform the different data actions against the database, and this method is more flexible and convenient to experienced programmers event a lot of coding jobs is concerned.

Chapters 8 and 9 give a full discussion and analysis about the developments and implementations of ASP.NET Web applications and Web services. These technologies are necessary to students and programmers who want to develop and build Web applications and Web services to access and manipulate data via Internet.

Based on the organization of this book we described above, this book can be used as two categories, such as Level I and Level II, which is shown in Figure 1.1.

Figure 1.1. Two study levels in the book.

For undergraduate college students or beginning software programmers, it is highly recommended to learn and understand the contents of Chapters 2, 3, and 4 and Part I of Chapters 5, 6, and 7 since those are fundamental knowledge and techniques in database programming with Visual Basic.NET 2010. For Chapters 8 and 9, it is optional to instructors, and it depends on the time and schedule.

For experienced college students or software programmers who have already some knowledge and techniques in database programming, it is recommended to learn and understand the contents of Part II of Chapters 5–7, as well as Chapters 8 and 9, since the runtime data objects method and some sophisticated database programming techniques, such as joined-table query, nested stored procedures, and Oracle Package, are discussed and illustrated in those chapters with real examples. Also, the ASP.NET Web applications and ASP.NET Web services are discussed and analyzed with 24 real database program examples for SQL Server 2008 and Oracle Database 11g XE.

HOW TO USE THE SOURCE CODE AND SAMPLE DATABASES

All source codes in each real project developed in this book are available. All projects are categorized into the associated chapters that are located at the folder DBProjects that is located at the site ftp://ftp.wiley.com/public/sci_tech_med/practical_database_vb. You can copy or download those codes into your computer and run each project as you like. To successfully run those projects on your computer, the following conditions must be met:

Visual Studio.NET 2010 or higher must be installed in your computer.

Three databases’ management systems, Microsoft Access 2007 (Microsoft Office 2007), Microsoft SQL Server 2008 Management Studio Express, and Oracle Database 11g Express Edition (XE) must be installed in your computer.

Three versions of sample database, CSE_DEPT.accdb, CSE_DEPT.mdf, and Oracle version of CSE_DEPT, must be installed in your computer in the appropriate folders.

To run projects developed in Chapters 8 and 9, in addition to conditions listed above, an Internet Information Services (IIS), such as FrontPage Server Extension 2000 or 2002, must be installed in your computer, and it works as a pseudoserver for those projects.

All book related teaching and learning materials, including the sample databases, example projects, appendices, faculty and student images, as well as sample Win­dows forms and Web pages, can be found from the associated folders located at the Wiley ftp site ftp://ftp.wiley.com/public/sci_tech_med/practical_database_vb-net-2e, as shown in Figure 1.2.

Figure 1.2. Book-related materials on website.

These materials are categorized and stored at different folders in two different sites based on the teaching purpose (for instructors) and learning purpose (for students):

1. Appendix Folder: Contains all appendices that provide useful references and practical knowledge to download and install database, database server and management systems and develop actual database application projects.Appendix A: Provides detailed descriptions about the download and installation of Microsoft SQL Server 2008 R2 Express.Appendix B: Provides detailed descriptions about download and installation of Oracle Database 11g Express Edition (XE).Appendix C: Provides detailed discussions in how to use three sample databases: CSE_DEPT.accdb, CSE_DEPT.mdf, and Oracle version of CSE_DEPT.Appendix D: Provides detailed discussions in how to create a user database in Oracle Database 11g XE using Unload and Load methods.Appendix E: Provides detailed discussions in how to add Existing Oracle Stored Procedures into the VB Project Using the DataSet Configuration Wizard.Appendix F: Provides detailed discussions in how to download and install a third-party Oracle Database driver dotConnect 6.30.

2. Database Folder: Contains three types of sample databases, CSE_DEPT, such as CSE_DEPT.accdb (Microsoft Access 2007), CSE_DEPT.mdf (SQL Server 2008), and Oracle version of CSE_DEPT. These sample databases are located at three subfolders, Access, SQLServer, and Oracle. Refer to Appendix F to get details in how to use these databases for your applications or sample projects.

3. DBProjects Folder: Contains all sample projects developed in the book. Projects are categorized and stored at different chapter subfolder based on the book chapter sequence. Readers can directly use the codes and GUIs of those projects by downloading them from the DBProjects folder at the Wiley ftp site.

4. Images Folder: Contains all sample faculty and student image files used in all sample projects in the book. Readers can copy and paste those image files to their projects to use them.

5. VB Forms Folder: Contains all sample Windows-based forms and Web-based pages developed and implemented in all sample projects in the book. All Windows-based Forms are located at the Window subfolder, and all Web-based Pages are located at the Web subfolder. Readers can use those Forms or Pages by copying and pasting them into their real projects.

6. TeachingPPT Folder: Contains all MS-PPT teaching slides for each chapter.

7. HWSolutions Folder: Contains selected solutions for the homeworks developed and used in the book. The solutions are categorized and stored at the different chapter subfolder based on the book chapter sequence.

Folders 1∼5 belong to learning materials for students; therefore they are located at the student site: ftp://ftp.wiley.com/public/sci_tech_med/practical_database_vb. Folders 1∼7 belong to teaching materials for instructors; they are located at the Wiley teaching site and available upon requests by instructors.

INSTRUCTORS AND CUSTOMERS SUPPORTS

The teaching materials for all chapters have been extracted and represented by a sequence of Microsoft Power Point files, each file for one chapter. The interested instructors can find those teaching materials from the folder TeachingPPT that is located at the site http://www.wiley.com, and those instructor materials are available upon request from the book’s listing on http://www.wiley.com.

A selected homework solution is also available upon request from the book’s listing on http://www.wiley.com.

E-mail support is available to readers of this book. When you send an e-mail to us, please provide the following information:

The detailed description about your problems, including the error message and debug message, as well as the error or debug number if it is provided.

Your name, job title, and company name.

How long you expect to get the answer to your questions.

Please send all questions to the e-mail address: [email protected].

Detailed structure and distribution of all book-related materials in the Wiley site, including the teaching materials for instructors and learning materials for students, are shown in Figure 1.2.

Chapter 2

Introduction to Databases

SATISH BHALLA AND YING BAI

Databases have become an integral part of our modern-day life. We are an information-driven society. We generate large amounts of data that is analyzed and converted into information. A recent example of biological data generation is the Human Genome Project that was jointly sponsored by the Department of Energy and the National Institute of Health. Many countries in the world participated in this venture for 10 years. The project was a tremendous success. It was completed in 2003 and resulted in generation of huge amount of genome data, currently stored in databases around the world. The scientists will be analyzing this data in years to come.

Database technology has a direct impact on our daily lives. Decisions are routinely made by organizations based on the information collected and stored in the databases. A record company may decide to market certain albums in selected regions based on the music preference of teenagers. Grocery stores display more popular items at the eye level, and reorders are based on the inventories taken at regular intervals. Other examples include book orders by libraries, club memberships, auto part orders, winter cloth stock by department stores, and many others.

Database management programs have been in existence since the 1960s. However, it was not until the 1970s when E. F. Codd proposed the then revolutionary relational data model that database technology really took off. In the early 1980s, it received a further boost with the arrival of personal computers and microcomputer-based data management programs, like dBase II (later followed by dBase III and IV). Today, we have a plethora of vastly improved programs for PCs and mainframe computers, including Microsoft Access, IBM DB2, Oracle, Sequel Server, My SQL, and others.

This chapter covers the basic concepts of database design followed by implementation of a specific relational database to illustrate the concepts discussed here. The sample database, CSE_DEPT, is used as a running example. The database creation is shown in detail using Microsoft Access, SQL Server, and Oracle. The topics discussed in this chapter include:

What are databases and database programs?

File processing system

Integrated databases

Various approaches to developing a database

Relational data model and entity-relationship model (ER)

Identifying keys

Primary keys, foreign keys, and referential integrity

Defining relationships

Normalizing the data

Implementing the relational database

Create Microsoft Access sample database

Create Microsoft SQL Server 2008 sample database

Create Oracle sample database

2.1 WHAT ARE DATABASES AND DATABASE PROGRAMS?

A modern-day database is a structured collection of data stored in a computer. The term structured implies that each record in the database is stored in a certain format. For example, all entries in a phone book are arranged in a similar fashion. Each entry contains a name, an address, and a telephone number of a subscriber. This information can be queried and manipulated by database programs. The data retrieved in answer to queries become information that can be used to make decisions. The databases may consist of a single table or related multiple tables. The computer programs used to create, manage, and query databases are known as a database management systems (DBMS). Just like the databases, the DBMS’ vary in complexity. Depending on the need of a user one can use either a simple application or a robust program. Some examples of these programs were given earlier.

2.1.1 File Processing System

The file processing system is a precursor of the integrated database approach. The records for a particular application are stored in a file. An application program is needed to retrieve or manipulate data in this file. Thus, various departments in an organization will have their own file processing systems with their individual programs to store and retrieve data. The data in various files may be duplicated and not available to other applications. This causes redundancy and may lead to inconsistency, meaning that various files that supposedly contain the same information may actually contain different data values. Thus duplication of data creates problems with data integrity. Moreover, it is difficult to provide access to multiple users with the file processing systems without granting them access to the respective application programs, which manipulate the data in those files.

The file processing system may be advantageous under certain circumstances. For example, if data are static and a simple application will solve the problem, a more expensive DBMS is not needed. For example, in a small business environment, you want to keep track of the inventory of the office equipment purchased only once or twice a year. The data can be kept in an Excel spreadsheet and manipulated with ease from time to time. This avoids the need to purchase an expensive database program, and hiring a knowledgeable database administrator. Before the DBMS’s became popular, the data were kept in files, and application programs were developed to delete, insert, or modify records in the files. Since specific application programs were developed for specific data, these programs lasted for months or years before modifications were necessitated by business needs.

2.1.2 Integrated Databases

A better alternative to a file processing system is an integrated database approach. In this environment, all data belonging to an organization is stored in a single database. The database is not a mere collection of files; there is a relation between the files. Integration implies a logical relationship, usually provided through a common column in the tables. The relationships are also stored within the database. A set of sophisticated programs known as DBMS is used to store, access, and manipulate the data in the database. Details of data storage and maintenance are hidden from the user. The user interacts with the database through the DBMS. A user may interact either directly with the DBMS or via a program written in a programming language, such as C++, Java, or Visual Basic. Only the DBMS can access the database. Large organizations employ database administrators (DBAs) to design and maintain large databases.

There are many advantages to using an integrated database approach over that of a file processing approach:

1. Data Sharing: The data in the database are available to a large numbers of users who can access the data simultaneously and create reports and manipulate the data given proper authorization and rights.

2. Minimizing Data Redundancy: Since all the related data exist in a single database, there is a minimal need of data duplication. The duplication is needed to maintain relationship between various data items.

3. Data Consistency and Data Integrity: Reducing data redundancy will lead to data consistency. Since data are stored in a single database, enforcing data integrity becomes much easier. Furthermore, the inherent functions of the DBMS can be used to enforce the integrity with minimum programming.

4. Enforcing Standards: DBAs are charged with enforcing standards in an organization. DBA takes into account the needs of various departments and balances it against the overall need of the organization. DBA defines various rules, such as documentation standards, naming conventions, update and recovery procedures, and so on. It is relatively easy to enforce these rules in a Database System, since it is a single set of programs that is always interacting with the data files.

5. Improving Security: Security is achieved through various means, such as controlling access to the database through passwords, providing various levels of authorizations, data encryption, providing access to restricted views of the database, and so on.

6. Data Independence: Providing data independence is a major objective for any database system. Data independence implies that even if the physical structure of a database changes, the applications are allowed to access the database as before the changes were implemented. In other words, the applications are immune to the changes in the physical representation and access techniques.

The downside of using an integrated database approach has mainly to do with exorbitant costs associated with it. The hardware, the software, and maintenance are expensive. Providing security, concurrency, integrity, and recovery may add further to this cost. Further more, since DBMS consists of a complex set of programs, trained personnel are needed to maintain it.

2.2 DEVELOP A DATABASE

Database development process may follow a classical Systems Development Life Cycle.

1. Problem Identification: Interview the user, identify user requirements. Perform preliminary analysis of user needs.

2. Project Planning: Identify alternative approaches to solving the problem. Does the project need a database? If so, define the problem. Establish scope of the project.

3. Problem Analysis: Identify specifications for the problem. Confirm the feasibility of the project. Specify detailed requirements

4. Logical Design: Delineate detailed functional specifications. Determine screen designs, report layout designs, data models, and so on.

5. Physical Design: Develop physical data structures.

6. Implementation: Select DBMS. Convert data to conform to DBMS requirements. Code programs; perform testing.

7. Maintenance: Continue program modification until desired results are achieved.

An alternative approach to developing a database is through a phased process which will include designing a conceptual model of the system that will imitate the real world operation. It should be flexible and change when the information in the database changes. Furthermore, it should not be dependent upon the physical implementation. This process follows following phases:

1. Planning and Analysis: This phase is roughly equivalent to the first three steps mentioned above in the Systems Development Life Cycle. This includes requirement specifications, evaluating alternatives, determining input, output, and reports to be generated.

2. Conceptual Design: Choose a data model and develop a conceptual schema based on the requirement specification that was laid out in the planning and analysis phase. This conceptual design focuses on how the data will be organized without having to worry about the specifics of the tables, keys, and attributes. Identify the entities that will represent tables in the database; identify attributes that will represent fields in a table; and identify each entity attribute relationship. Entity-relationship diagrams (ERDs) provide a good representation of the conceptual design.

3. Logical Design: Conceptual design is transformed into a logical design by creating a roadmap of how the database will look before actually creating the database. Data model is identified; usually it is the relational model. Define the tables (entities) and fields (attributes). Identify primary and foreign key for each table. Define relationships between the tables.

4. Physical Design: Develop physical data structures; specify file organization, and data storage, and so on. Take into consideration the availability of various resources, including hardware and software. This phase overlaps with the implementation phase. It involves the programming of the database taking into account the limitations of the DBMS used.

5. Implementation: Choose the DBMS that will fulfill the user needs. Implement the physical design. Perform testing. Modify if necessary or until the database functions satisfactorily.

2.3 SAMPLE DATABASE

We will use the CSE_DEPT database to illustrate some essential database concepts. Tables 2.1–2.5 show sample data tables stored in this database.

Table 2.1. LogIn table

Table 2.2. Faculty table

Table 2.3. Course table

Table 2.4. Student table

Table 2.5. StudentCourse table

The data in the CSE_DEPT database are stored in five tables—LogIn, Faculty, Course, Student, and StudentCourse. A table consists of row and columns (Fig. 2.1). A row represents a record, and the column represents a field. Row is called a tuple, and a column is called an attribute. For example, the Student table has seven columns or fields—student_id, name, gpa, major, schoolYear, and email. It has five records or rows.

Figure 2.1. Records and fields in a table.

2.3.1 Relational Data Model

Data model is like a blue print for developing a database. It describes the structure of the database and various data relationships and constraints on the data. This information is used in building tables, keys, and defining relationships. Relational model implies that a user perceives the database as made up of relations, a database jargon for tables. It is imperative that all data elements in the tables are represented correctly. In order to achieve these goals, designers use various tools. The most commonly used tool is the ER. A well-planned model will give consistent results and will allow changes if needed later on. The following section further elaborates on the ER.

2.3.2 Entity-Relationship Model

The ER was first proposed and developed by Peter Chen in 1976. Since then, Charles Bachman and James Martin have added some refinements; the model was designed to communicate the database design in the form of a conceptual schema. The ER is based on the perception that the real world is made up of entities, their attributes, and relationships. The ER is graphically depicted as ERDs. ERDs are a major modeling tool; they graphically describe the logical structure of the database. ER diagrams can be used with ease to construct the relational tables and are a good vehicle for communicating the database design to the end user or a developer. The three major components of ERD are entities, relationships, and the attributes.

Entities: An entity is a data object, either real or abstract, about which we want to collect information. For example, we may want to collect information about a person, a place, or a thing. An entity in an ER diagram translates into a table. It should preferably be referred to as an entity set. Some common examples are departments, courses, and students. A single occurrence of an entity is an instance. There are four entities in the CSE_Dept database, LogIn, Faculty, Course, and Student. Each entity is translated into a table with the same name. An instance of the Faculty entity will be Alice Brown and her attributes.

Relationships: A database is made up of related entities. There is a natural association between the entities; it is referred to as relationship. For example,Students take coursesDepartments offer certain coursesEmployees are assigned to departmentsThe number of occurrences of one entity associated with single occurrence of a related entity is referred to as cardinality.

Attributes: Each entity has properties or values called attributes associated with it. The attributes of an entity map into fields in a table. Database Processing is one attribute of an entity called Courses. The domain of an attribute is a set of all possible values from which an attribute can derive its value.

2.4 IDENTIFYING KEYS

2.4.1 Primary Key and Entity Integrity

An attribute that uniquely identifies one and only one instance of an entity is called a primary key. Sometimes, a primary key consists of a combination of attributes. It is referred to as a composite key. Entity integrity rule states that no attribute that is a member of the primary (composite) key may accept a null value.

A FacultyID may serve as a primary key for the Faculty entity, assuming that all faculty members have been assigned a unique FaultyID. However, caution must be exercised when picking an attribute as a primary key. Last Name may not make a good primary key because a department is likely to have more than one person with the same last name. Primary keys for the CSE_DEPT database are shown in Table 2.6.

Table 2.6. Faculty table

Primary keys provide a tuple level addressing mechanism in the relational databases. Once you define an attribute as a primary key for an entity, the DBMS will enforce the uniqueness of the primary key. Inserting a duplicate value for primary key field will fail.

2.4.2 Candidate Key

There can be more than one attribute that uniquely identifies an instance of an entity. These are referred to as candidate keys. Any one of them can serve as a primary key. For example, ID Number as well as Social Security Number may make a suitable primary key. Candidate keys that are not used as primary key are called alternate keys.

2.4.3 Foreign Keys and Referential Integrity

Foreign keys are used to create relationships between tables. It is an attribute in one table whose values are required to match those of primary key in another table. Foreign keys are created to enforce referential integrity, which states that you may not add a record to a table containing a foreign key unless there is a corresponding record in the related table to which it is logically linked. Furthermore, the referential integrity rule also implies that every value of a foreign key in a table must match the primary key of a related table or be null. MS Access also makes provision for cascade update and cascade delete, which imply that changes made in one of the related tables will be reflected in the other of the two related tables.

Consider two tables, Course and Faculty, in the sample database, CSE_DEPT. The Course table has a foreign key, entitled faculty_id, which is primary key in the Faculty table. The two tables are logically related through the faculty_id link. Referential integrity rules imply that we may not add a record to the Course table with a faculty_id, which is not listed in the Faculty table. In other words, there must be a logical link between the two related tables. Second, if we change or delete a faculty_id in the Faculty table, it must reflect in the Course table, meaning that all records in the Course table must be modified using a cascade update or cascade delete (Table 2.7).

Table 2.7. Course (Partial data shown) Faculty (Partial data shown)

2.5 DEFINE RELATIONSHIPS

2.5.1 Connectivity

Connectivity refers to the types of relationships that entities can have. Basically it can be one-to-one, one-to-many, and many-to-many. In ERDs, these are indicated by placing 1, M, or N at one of the two ends of the relationship diagram. Figures illustrate the use of this notation.

A

one-to-one (1:1)

relationship occurs when one instance of entity A is related to only one instance of entity B. For example,

user_name

in the LogIn table and

user_name

in the Student table (

Fig. 2.2

).

A

one-to-many (1:M)

relationship occurs when one instance of entity A is associated with zero, one, or many instances of entity B. However, entity B is associated with only one instance of entity A. For example, one department can have many faculty members; each faculty member is assigned to only one department. In the CSE_DEPT database, one-to-many relationship is represented by

faculty_id

in the Faculty table and

faculty_id

in the Course table,

student_id

in the Student table and

student_id

in the StudentCourse table,

course_id

in the Course table and

course_id

in the StudentCourse table (

Fig. 2.3

).

A

many-to-many (M:N)

relationship occurs when one instance of entity A is associated with zero, one, or many instances of entity B. And one instance of entity B is associated with zero, one, or many instance of entity A. For example, a student may take many courses and one course may be taken by more than one student (

Fig. 2.4

).

Figure 2.2. One-to-one relationship in the LogIn and the Student tables.

Figure 2.3. One-to-many relationship between Faculty and Course tables.

Figure 2.4. Many-to-many relationship between Student and Course tables.

In the CSE_DEPT database, a many-to-many relationship can be realized by using the third table. For example, in this case, the StudentCourse that works as the third table, set a many-to-many relationship between the Student and the Course tables.

This database design assumes that the course table only contains courses taught by all faculty members in this department for one semester. Therefore, each course can only be taught by a unique faculty. If one wants to develop a Course table that contains courses taught by all faculty in more than one semester, the third table, say FacultyCourse table, should be created to set up a many-to-many relationship between the Faculty and the Course table, since one course may be taught by the different faculty for the different semester.

The relationships in CSE_DEPT database are summarized in Figure 2.5.

Figure 2.5. Relationships in CSE_DEPT database.

Database name: CSE_DEPT

The five entities are:

LogIn

Faculty

Course

Student

StudentCourse

The relationships between these entities are shown in Figure 2.5. P.K. and F.K. represent the primary key and the foreign key, respectively.

Figure 2.6 displays the Microsoft Access relationships diagram among various tables in the CSE_Dept database. One-to-many relationships is indicated by placing 1 at one end of the link and ∞ at the other. The many-to-many relationship between the Student and the Course table was broken down to two one-to-many relationships by creating a new StudentCourse table.

Figure 2.6. Relationships are illustrated using MS Access in the CSE_DEPT database.

2.6 ER NOTATION

There are a number of ER notations available, including Chen’s, Bachman, Crow’s foot, and a few others. There is no consensus on the symbols and the styles used to draw ERDs. A number of drawing tools are available to draw ERDs. These include ER Assistant, Microsoft Visio, and Smart Draw, among others. Commonly used notations are shown in Figure 2.7.

Figure 2.7. Commonly used symbols for ER notation.

2.7 DATA NORMALIZATION

After identifying tables, attributes, and relationships, the next logical step in database design is to make sure that the database structure is optimum. Optimum structure is achieved by eliminating redundancies, various inefficiencies, update, and deletion anomalies that usually occur in the unnormalized or partially normalized databases. Data normalization is a progressive process. The steps in the normalization process are called normal forms. Each normal form progressively improves the database and makes it more efficient. In other words, a database that is in second normal form is better than the one in the first normal form (1NF), and the one in third normal form (3NF) is better than the one in second normal form (2NF). To be in 3NF, a database has to be in the first and second normal form. There are fourth and fifth normal forms, but for most practical purposes, a database meeting the criteria of 3NF is considered to be of good design.

2.7.1 First Normal Form (1NF)

A table is in 1NF if values in each column are atomic, that is, there are no repeating groups of data.

The following Faculty table (Table 2.8) is not normalized. Some faculty members have more than one telephone number listed in the phone column. These are called repeating groups.

Table 2.8. Unnormalized Faculty table with repeating groups

In order to convert this table to 1NF, the data must be atomic. In other words, the repeating rows must be broken into two or more atomic rows. Table 2.9 illustrates the Faculty table in 1NF, where repeating groups have been removed. Now it is in 1NF.

Table 2.9. Normalized Faculty table

2.7.2 Second Normal Form (2NF)

A table is in 2NF if it is already in 1NF and every nonkey column is fully dependent upon the primary key.

This implies that if the primary key consists of a single column then the table in 1NF is automatically in 2NF. The second part of the definition implies that if the key is composite, then none of the nonkey columns will depend upon just one of the columns that participates in the composite key.

The Faculty table in Table 2.9 is in 1NF. However, it has a composite primary key, made up of faculty_id and office. The phone number depends on a part of the primary key, the office, and not on the whole primary key. This can lead to update and deletion anomalies mentioned above.

By splitting the old Faculty table (Fig. 2.8) into two new tables, Faculty and Office, we can remove the dependencies mentioned earlier. Now the faculty table has a primary key, faculty_id, and the Office table has a primary key, office. The nonkey columns in both tables now depend only on the primary keys only.

Figure 2.8. Converting faulty table into 2NF by decomposing the old table in two, Faculty and Office.

2.7.3 Third Normal Form (3NF)

A table is in 3NF if it is already in 2NF, and every nonkey column is nontransitively dependent upon the primary key. In other words, all nonkey columns are mutually independent, but at the same time, they are fully dependent upon the primary key only.

Another way of stating this is that in order to achieve 3NF, no column should depend upon any nonkey column. If column B depends on column A, then A is said to functionally determine column B; hence the term determinant. Another definition of 3NF says that the table should be in 2NF, and only determinants it contains are candidate keys.

For the Course table in Table 2.10, all nonkey columns depend on the primary key—course_id. In addition, name and phone columns also depend on faculty_id. This table is in 2NF, but it suffers from update, addition, and deletion anomalies because of transitive dependencies. In order to conform to 3NF, we can split this table into two tables, Course and Instructor (Tables 2.11 and 2.12). Now we have eliminated the transitive dependencies that are apparent in the Course table in Table 2.10.

Table 2.10. The old Course table

Table 2.11. The new Course table

Table 2.12. The new Instructor table

2.8 DATABASE COMPONENTS IN SOME POPULAR DATABASES

All databases allow for storage, retrieval, and management of data. Simple databases provide basic services to accomplish these tasks. Many database providers, like Microsoft SQL Server and Oracle, provide additional services, which necessitates storing many components in the database other than data. These components, such as views, stored procedures, and so on, are collectively called database objects. In this section, we will discuss various objects that make up MS Access, SQL Server, and Oracle databases.

There are two major types of databases, File Server and Client Server.

In a File Server database, data are stored in a file and each user of the database retrieves the data, displays the data, or modifies the data directly from or to the file. In a Client Server database, data are also stored in a file; however, all these operations are mediated through a master program, called a server. MS Access is a File Server database, whereas Microsoft SQL Server and Oracle are Client Server databases. The Client Server databases have several advantages over the File Server databases. These include minimizing chances of crashes, provision of features for recovery, enforcement of security, better performance, and more efficient use of the network compared to the file server databases.

2.8.1 Microsoft Access Databases

Microsoft Access Database Engine is a collection of information stored in a systematic way that forms the underlying component of a database. Also called a Jet (Joint Engine Technology), it allows the manipulation of relational database. It offers a single interface that other software may use to access Microsoft databases. The supporting software is developed to provide security, integrity, indexing, record locking, and so on. By executing the MS Access program, MSACCESS.EXE, you can see the database engine at work and the user interface it provides. Figure 2.9 shows how a Java application accesses the MS Access database via ACE OLE database provider.

Figure 2.9. Microsoft Access database illustration.

2.8.1.1 Database File

Access database is made up of a number of components called objects that are stored in a single file referred to as database file