Cover page

Table of Contents

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)

Title page

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:

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.

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.
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.
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 .

 Two study levels in the book.

c01f001

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 . 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:

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 , as shown in .

 Book-related materials on website.

c01f002

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: . 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 , and those instructor materials are available upon request from the book’s listing on .

A selected homework solution is also available upon request from the book’s listing on .

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

Please send all questions to the e-mail address: .

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 .

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:

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. show sample data tables stored in this database.

 LogIn table

c02t001

 Faculty table

c02t002

 Course table

c02t003

 Student table

c02t004

 StudentCourse table

c02t005

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 (). 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.

 Records and fields in a table.

c02f001

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 courses
  • Departments offer certain courses
  • Employees are assigned to departments
The 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 .

 Faculty table

c02t006

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 ().

 Course (Partial data shown) Faculty (Partial data shown)

c02t007

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.