Lesson 13

 

Lesson 13

Basic steps designing databases in Microsoft Access. Creating tables.

Development of the Relational Databases

Basic questions

1.     What is the database?

2.     Types of the Databases

3.     Common Database Models

4.     Overview of the Relational Database Model

5.     Data Structure and Terminology in the Relational Databases

6.     Properties of Relational Tables

7.     Data Integrity in the Relational Databases

8.     Relational Data Manipulation

9.     What is the normalization?

Enter the database.

Simply put, a database is a computerized record keeping system. More completely, it is a system involving data, the hardware that physically stores that data, the software that utilizes the hardware’s file system in order to 1) store the data and 2) provide a standardized method for retrieving or changing the data, and finally, the users who turn the data into information.

Databases, another creature of the 60s, were created to solve the problems with file-oriented systems in that they were compact, fast, easy to use, current, accurate, allowed the easy sharing of data between multiple users, and were secure.

A database might be as complex and demanding as an account tracking system used by a bank to manage the constantly changing accounts of thousands of bank customers, or it could be as simple as a collection of electronic business cards on your laptop.

The important thing is that a database allows you to store data and get it or modify it when you need to easily and efficiently regardless of the amount of data being manipulated. What the data is and how demanding you will be when retrieving and modifying that data is simply a matter of scale.

Traditionally, databases ran on large, powerful mainframes for business applications. You will probably have heard of such packages as Oracle 8 or Sybase SQL Server for example.

However with the advent of small, powerful personal computers, databases have become more readily usable by the average computer user. Microsoft’s Access is a popular PC-based engine. [21]

Types of Databases

These days, when you talk about databases in the wild, you are primarily talking about two types: analytical databases and operational databases. Let’s examine each type.

Analytic Databases

Analytic databases (a.k.a. OLAP- On Line Analytical Processing) are primarily static, read-only databases which store archived, historical data used for analysis. For example, a company might store sales records over the last ten years in an analytic database and use that database to analyze marketing strategies in relationship to demographics.

Operational Databases

Operational databases (a.k.a. OLTP On Line Transaction Processing), on the other hand, are used to manage more dynamic bits of data. These types of databases allow you to do more than simply view archived data. Operational databases allow you to modify that data (add, change or delete data).

These types of databases are usually used to track real-time information. For example, a company might have an operational database used to track warehouse/stock quantities. As customers order products from an online web store, an operational database can be used to keep track of how many items have been sold and when the company will need to reorder stock.

Database Models

Besides differentiating databases according to function, databases can also be differentiated according to how they model the data. What is a data model?

Well, essentially a data model is a “description” of both a container for data and a methodology for storing and retrieving data from that container. Actually, there isn’t really a data model “thing”. Data models are abstractions, oftentimes mathematical algorithms and concepts. You cannot really touch a data model. But nevertheless, they are very useful. The analysis and design of data models has been the cornerstone of the evolution of databases. As models have advanced so has database efficiency.

Before the 1980’s, the two most commonly used Database Models were the hierarchical and network systems. Let’s take a quick look at these two models and then move on to the more current models. [22]

Hierarchical Databases

As its name implies, the Hierarchical Database Model defines hierarchically-arranged data.

Perhaps the most intuitive way to visualize this type of relationship is by visualizing an upside down tree of data. In this tree, a single table acts as the “root” of the database from which other tables “branch” out.

You will be instantly familiar with this relationship because that is how all windows-based directory management systems (like Windows Explorer) work these days.

Relationships in such a system are thought of in terms of children and parents such that a child may only have one parent but a parent can have multiple children. Parents and children are tied together by links called “pointers” (perhaps physical addresses inside the file system). A parent will have a list of pointers to each of their children (Figure 286).

Figure 286. Hierarchical Database Model

This child/parent rule assures that data is systematically accessible. To get to a low-level table, you start at the root and work your way down through the tree until you reach your target. Of course, as you might imagine, one problem with this system is that the user must know how the tree is structured in order to find anything!

The hierarchical model however, is much more efficient than the flat-file model we discussed earlier because there is not as much need for redundant data. If a change in the data is necessary, the change might only need to be processed once. Consider the student flatfile database example from our discussion of what databases are:

 

 

As we mentioned before, this flatfile database would store an excessive amount of redundant data. If we implemented this in a hierarchical database model, we would get much less redundant data. Consider the following hierarchical database scheme (Figure 287):

Figure 287. Hierarchical Database Example

However, as you can imagine, the hierarchical database model has some serious problems. For one, you cannot add a record to a child table until it has already been incorporated into the parent table. This might be troublesome if, for example, you wanted to add a student who had not yet signed up for any courses.

Worse, yet, the hierarchical database model still creates repetition of data within the database. You might imagine that in the database system shown above, there may be a higher level that includes multiple course. In this case, there could be redundancy because students would be enrolled in several courses and thus each “course tree” would have redundant student information (Figure 288).

Redundancy would occur because hierarchical databases handle one-to-many relationships well but do not handle many-to-many relationships well. This is because a child may only have one parent. However, in many cases you will want to have the child be related to more than one parent. For instance, the relationship between student and class is a “many-to-many”. Not only can a student take many subjects but a subject may also be taken by many students. How would you model this relationship simply and efficiently using a hierarchical database? The answer is that you wouldn’t.

Figure 288. Redundancy in the hierarchical database

Though this problem can be solved with multiple databases creating logical links between children, the fix is very kludgy and awkward. [22]

Faced with these serious problems, the computer brains of the world got together and came up with the network model.

Network Databases

In many ways, the Network Database model was designed to solve some of the more serious problems with the Hierarchical Database Model. Specifically, the Network model solves the problem of data redundancy by representing relationships in terms of sets rather than hierarchy. The model had its origins in the Conference on Data Systems Languages (CODASYL) which had created the Data Base Task Group to explore and design a method to replace the hierarchical model.

The network model is very similar to the hierarchical model actually. In fact, the hierarchical model is a subset of the network model. However, instead of using a single-parent tree hierarchy, the network model uses set theory to provide a tree-like hierarchy with the exception that child tables were allowed to have more than one parent. This allowed the network model to support many-to-many relationships. [Марценюк В.П. Медична інформатика. Проектування та використання баз даних]

Visually, a Network Database looks like a hierarchical Database in that you can see it as a type of tree. However, in the case of a Network Database, the look is more like several trees which share branches. Thus, children can have multiple parents and parents can have multiple children (Figure 289).

Figure 289. Network Database structure

Nevertheless, though it was a dramatic improvement, the network model was far from perfect. Most profoundly, the model was difficult to implement and maintain. Most implementations of the network model were used by computer programmers rather than real users. What was needed was a simple model which could be used by real end users to solve real problems.

Relational Databases

Of course in the 80’s the “Relational Database Model” became the rage. The Relational Model developed out of the work done by Dr. E. F. Codd at IBM in the late 1960s who was looking for ways to solve the problems with the existing models.

Because he was a mathematician, he naturally built the model on mathematical concepts which he expounded in the famous work called “A Relational Model of Data for Large Shared Databanks”.

At the core of the relational model is the concept of a table (also called a relation) in which all data is stored. Each table is made up of records (horizontal rows also known as tuples) and fields (vertical columns also known as attributes).

It is important to note that how or where the tables of data are stored makes no difference. Each table can be identified by a unique name and that name can be used by the database to find the table behind the scenes. As a user, all you need to know is the table name in order to use it. You do not need to worry about the complexities of how the data is stored on the hard drive.

This is quite a bit different from the hierarchical and network models in which the user had to have an understanding of how the data was structured within the database in order to retrieve, insert, update, or delete records from the database. [22]

Overview of the Relational Model

The relational model was formally introduced by Dr. E. F. Codd in 1970 and has evolved since then, through a series of writings. The model provides a simple, yet rigorously defined, concept of how users perceive data. The relational model represents data in the form of two-dimension tables. Each table represents some real-world person, place, thing, or event about which information is collected. A relational database is a collection of two-dimensional tables. The organization of data into relational tables is known as the logical view of the database. That is, the form in which a relational database presents data to the user and the programmer. The way the database software physically stores the data on a computer disk system is called the internal view. The internal view differs from product to product and does not concern us here. [22]

A basic understanding of the relational model is necessary to effectively use relational database software such as Oracle, Microsoft SQL Server, or even personal database systems such as Access or Fox, which are based on the relational model.

Data Structure and Terminology

Figure 290. Relational tables for a simple bibliographic database

In the relational model, a database is a collection of relational tables. A relational table is a flat file composed of a set of named columns and an arbitrary number of unnamed rows. The columns of the tables contain information about the table. The rows of the table represent occurrences of the “thing” represented by the table. A data value is stored in the intersection of a row and column. Each named column has a domain, which is the set of values that may appear in that column. Figure 290 shows the relational tables for a simple bibliographic database that stores information about book title, authors, and publishers.

There are alternate names used to describe relational tables. Some manuals use the terms tables, fields, and records to describe relational tables, columns, and rows, respectively. The formal literature tends to use the mathematical terms, relations, attributes, and tuples. Table 20 summarizes these naming conventions. [22]

Table 20. Relational Databases Terminology

Notation

 

Relational tables can be expressed concisely by eliminating the sample data and showing just the table name and the columames. For example, [21]

 

Properties of Relational Tables

Relational tables have six properties: [21]

1.     Values are atomic.

2.     Column values are of the same kind.

3.     Each row is unique.

4.     The sequence of columns is insignificant.

5.     The sequence of rows is insignificant.

6.     Each column must have a unique name.

Values Are Atomic

This property implies that columns in a relational table are not repeating group or arrays. Such tables are referred to as being in the “first normal form” (1NF). The atomic value property of relational tables is important because it is one of the cornerstones of the relational model.

The key benefit of the one value property is that it simplifies data manipulation logic.

Column Values Are of the Same Kind

In relational terms this means that all values in a column come from the same domain. A domain is a set of values which a column may have. For example, a Monthly_Salary column contains only specific monthly salaries. It never contains other information such as comments, status flags, or even weekly salary.

This property simplifies data access because developers and users can be certain of the type of data contained in a given column. It also simplifies data validation. Because all values are from the same domain, the domain can be defined and enforced with the Data Definition Language (DDL) of the database software.

Each Row is Unique

This property ensures that no two rows in a relational table are identical; there is at least one column, or set of columns, the values of which uniquely identify each row in the table. Such columns are called primary keys and are discussed in more detail in Relationships and Keys.

This property guarantees that every row in a relational table is meaningful and that a specific row can be identified by specifying the primary key value.

The Sequence of Columns is Insignificant

This property states that the ordering of the columns in the relational table has no meaning. Columns can be retrieved in any order and in various sequences. The benefit of this property is that it enables many users to share the same table without concern of how the table is organized. It also permits the physical structure of the database to change without affecting the relational tables.

The Sequence of Rows is Insignificant

This property is analogous the one above but applies to rows instead of columns. The main benefit is that the rows of a relational table can be retrieved in different order and sequences. Adding information to a relational table is simplified and does not affect existing queries.

Each Column Has a Unique Name

Because the sequence of columns is insignificant, columns must be referenced by name and not by position. In general, a columame need not be unique within an entire database but only within the table to which it belongs.

Relationships and Keys

A relationship is an association between two or more tables. Relationships are expressed in the data values of the primary and foreign keys.

A primary key is a column or columns in a table whose values uniquely identify each row in a table. A foreign key is a column or columns whose values are the same as the primary key of another table. You can think of a foreign key as a copy of primary key from another relational table. The relationship is made between two relational tables by matching the values of the foreign key in one table with the values of the primary key in another. [Марценюк В.П. Медична інформатика. Проектування та використання баз даних]

Keys are fundamental to the concept of relational databases because they enable tables in the database to be related with each other. Navigation around a relational database depends on the ability of the primary key to unambiguously identify specific rows of a table. Navigating between tables requires that the foreign key is able to correctly and consistently reference the values of the primary keys of a related table. For example, the picture below shows how the keys in the relational tables are used to navigate from AUTHOR to TITLE to PUBLISHER. AUTHOR_TITLE is an all key table used to link AUTHOR and TITLE. This relational table is required because AUTHOR and TITLE have a many-to-many relationship (Figure 291).

Data Integrity

Data integrity means, in part, that you can correctly and consistently navigate and manipulate the tables in the database. There are two basic rules to ensure data integrity; entity integrity and referential integrity.

The entity integrity rule states that the value of the primary key caever be a null value (a null value is one that has no value and is not the same as a blank). Because a primary key is used to identify a unique row in a relational table, its value must always be specified and should never be unknown. The integrity rule requires that insert, update, and delete operations maintain the uniqueness and existence of all primary keys.

The referential integrity rule states that if a relational table has a foreign key, then every value of the foreign key must either be null or match the values in the relational table in which that foreign key is a primary key. [21]

Figure 291. Example working relational database

Relational Data Manipulation

Relational tables are sets. The rows of the tables can be considered as elements of the set. Operations that can be performed on sets can be done on relational tables. The eight relational operations are:

Union

The union operation of two relational tables is formed by appending rows from one table to those of a second table to produce a third. Duplicate rows are eliminated. The notation for the union of Tables A and B is A UNION B.

The relational tables used in the union operation must be union compatible. Tables that are union compatible must have the same number of columns and corresponding columns must come from the same domain. Figure 292 shows the union of A and B. [21]

Note that the duplicate row [1, A, 2] has been removed.

Figure 292. A UNION B

Difference

The difference of two relational tables is a third that contains those rows that occur in the first table but not in the second. The Difference operation requires that the tables be union compatible. As with arithmetic, the order of subtraction matters. That is, A – B is not the same as B – A. Figure 293 shows the different results. [21]

Figure 293. The Difference Operator

Intersection

The intersection of two relational tables is a third table that contains common rows. Both tables must be union compatible. The notation for the intersection of A and B is A [intersection] B = C or A INTERSECT B. Figure 294 shows the single row [1, A, 2] appears in both A and B.

Figure 294. Intersection

Product

The product of two relational tables, also called the Cartesian Product, is the concatenation of every row in one table with every row in the second. The product of table A (having m rows) and table B (havingrows) is the table C (having m xrows). The product is denoted as A X B or A TIMES B (Figure 295). [21]

The product operation is by itself not very useful. However, it is often used as an intermediate process in a Join.

Figure 295. Product

Projection

The project operator retrieves a subset of columns from a table, removing duplicate rows from the result.

Selection

The select operator, sometimes called restrict to prevent confusion with the SQL SELECT command, retrieves subsets of rows from a relational table based on a value(s) in a column or columns.

Join

A join operation combines the product, selection, and, possibly, projection. The join operator horizontally combines (concatenates) data from one row of a table with rows from another or the same table when certain criteria are met. The criteria involve a relationship among the columns in the join relational table. If the join criterion is based on equality of column value, the result is called an equijoin. A natural join is an equijoin with redundant columns removed.

Figure 296 illustrates a join operation. Tables D and E are joined based on the equality of k in both tables. The first result is an equijoin. Note that there are two columns named k; the second result is a natural join with the redundant column removed. [21]

Joins can also be done on criteria other than equality.

Figure 296. Join

Division

The division operator results in columns values in one table for which there are other matching column values corresponding to every row in another table (Figure 297).

Figure 297. Division

Normalization

Normalization is a design technique that is widely used as a guide in designing relational databases. Normalization is essentially a two step process that puts data into tabular form by removing repeating groups and then removes duplicated data from the relational tables.

Normalization theory is based on the concepts of normal forms. A relational table is said to be a particular normal form if it satisfied a certain set of constraints. There are currently five normal forms that have been defined. In this section, we will cover the first three normal forms that were defined by E. F. Codd. [Марценюк В.П. Медична інформатика. Проектування та використання баз даних]

Basic Concepts

The goal of normalization is to create a set of relational tables that are free of redundant data and that can be consistently and correctly modified. This means that all tables in a relational database should be in the third normal form (3NF). A relational table is in 3NF if and only if all non-key columns are (a) mutually independent and (b) fully dependent upon the primary key. Mutual independence means that no non-key column is dependent upon any combination of the other columns. The first two normal forms are intermediate steps to achieve the goal of having all tables in 3NF. In order to better understand the 2NF and higher forms, it is necessary to understand the concepts of functional dependencies and lossless decomposition. [Марценюк В.П. Медична інформатика. Проектування та використання баз даних]

Functional Dependencies

The concept of functional dependencies is the basis for the first three normal forms. A column, Y, of the relational table R is said to be functionally dependent upon column X of R if and only if each value of X in R is associated with precisely one value of Y at any given time. X and Y may be composite. Saying that column Y is functionally dependent upon X is the same as saying the values of column X identify the values of column Y. If column X is a primary key, then all columns in the relational table R must be functionally dependent upon X.

A short-hand notation for describing a functional dependency is:

R.x —>; R.y

which can be read as in the relational table named R, column x functionally determines (identifies) column y.

Full functional dependence applies to tables with composite keys. Column Y in relational table R is fully functional on X of R if it is functionally dependent on X and not functionally dependent upon any subset of X. Full functional dependence means that when a primary key is composite, made of two or more columns, then the other columns must be identified by the entire key and not just some of the columns that make up the key. [21]

Overview

Simply stated, normalization is the process of removing redundant data from relational tables by decomposing (splitting) a relational table into smaller tables by projection. The goal is to have only primary keys on the left hand side of a functional dependency. In order to be correct, decomposition must be lossless. That is, the new tables can be recombined by a natural join to recreate the original table without creating any spurious or redundant data.

Sample Data

Data taken from Date [Date90] is used to illustrate the process of normalization. A company obtains parts from a number of suppliers. Each supplier is located in one city. A city can have more than one supplier located there and each city has a status code associated with it. Each supplier may provide many parts. The company creates a simple relational table to store this information that can be expressed in relational notation as:

FIRST (s#, status, city, p#, qty)

Where

 

 

In order to uniquely associate quantity supplied (qty) with part (p#) and supplier (s#), a composite primary key composed of s# and p# is used.

First Normal Form

A relational table, by definition, is in first normal form. All values of the columns are atomic. That is, they contaio repeating values. Figure 298 shows the table FIRST in 1NF. [21]

Figure 298. Table in 1NF

Although the table FIRST is in 1NF it contains redundant data. For example, information about the supplier’s location and the location’s status have to be repeated for every part supplied. Redundancy causes what are called update anomalies. Update anomalies are problems that arise when information is inserted, deleted, or updated. For example, the following anomalies could occur in FIRST:

·        INSERT. The fact that a certain supplier (s5) is located in a particular city (Athens) cannot be added until they supplied a part.

·        DELETE. If a row is deleted, theot only is the information about quantity and part lost but also information about the supplier.

·        UPDATE. If supplier s1 moved from London to New York, then six rows would have to be updated with this new information.

Second Normal Form

The definition of second normal form states that only tables with composite primary keys can be in 1NF but not in 2NF.

·        A relational table is in second normal form 2NF if it is in 1NF and every non-key column is fully dependent upon the primary key.

That is, every non-key column must be dependent upon the entire primary key. FIRST is in 1NF but not in 2NF because status and city are functionally dependent upon only on the column s# of the composite key (s#, p#). This can be illustrated by listing the functional dependencies in the table:

 

 

The process for transforming a 1NF table to 2NF is: [21]

1.     Identify any determinants other than the composite key, and the columns they determine.

2.     Create and name a new table for each determinant and the unique columns it determines.

3.     Move the determined columns from the original table to the new table. The determinate becomes the primary key of the new table.

4.     Delete the columns you just moved from the original table except for the determinate which will serve as a foreign key.

5.     The original table may be renamed to maintain semantic meaning.

To transform FIRST into 2NF we move the columns s#, status, and city to a new table called SECOND. The column s# becomes the primary key of this new table. The results are shown below in Figure 299.

Figure 299. Tables in 2NF

Tables in 2NF but not in 3NF still contain modification anomalies. In the example of SECOND, they are:

·        INSERT. The fact that a particular city has a certain status (Rome has a status of 50) cannot be inserted until there is a supplier in the city.

·        DELETE. Deleting any row in SUPPLIER destroys the status information about the city as well as the association between supplier and city.

Third Normal Form

The third normal form requires that all columns in a relational table are dependent only upon the primary key. A more formal definition is:

·        A relational table is in third normal form (3NF) if it is already in 2NF and every non-key column is non transitively dependent upon its primary key. In other words, all nonkey attributes are functionally dependent only upon the primary key.

Table PARTS is already in 3NF. The non-key column, qty, is fully dependent upon the primary key (s#, p#). SUPPLIER is in 2NF but not in 3NF because it contains a transitive dependency. A transitive dependency is occurs when a non-key column that is a determinant of the primary key is the determinate of other columns. The concept of a transitive dependency can be illustrated by showing the functional dependencies in SUPPLIER:

 

 

Note that SUPPLIER.status is determined both by the primary key s# and the non-key column city. The process of transforming a table into 3NF is: [21]

1.     Identify any determinants, other the primary key, and the columns they determine.

2.     Create and name a new table for each determinant and the unique columns it determines.

3.     Move the determined columns from the original table to the new table. The determinate becomes the primary key of the new table.

4.     Delete the columns you just moved from the original table except for the determinate which will serve as a foreign key.

5.     The original table may be renamed to maintain semantic meaning.

To transform SUPPLIER into 3NF, we create a new table called CITY_STATUS and move the columns city and status into it. Status is deleted from the original table, city is left behind to serve as a foreign key to CITY_STATUS, and the original table is renamed to SUPPLIER_CITY to reflect its semantic meaning. The results are shown in Figure 300 below.

Figure 300. Tables in 3NF

The results of putting the original table into 3NF has created three tables. These can be represented in “psuedo-SQL” as:

PARTS (#s, p#, qty)
Primary Key (s#,#p)
Foreign Key (s#) references SUPPLIER_CITY.s#

SUPPLIER_CITY(s#, city)
Primary Key (s#)
Foreign Key (city) references CITY_STATUS.city

CITY_STATUS (city, status)
Primary Key (city)

Advantages of Third Normal Form

The advantage of having relational tables in 3NF is that it eliminates redundant data which in turn saves space and reduces manipulation anomalies. For example, the improvements to our sample database are:

·        INSERT. Facts about the status of a city, Rome has a status of 50, can be added even though there is not supplier in that city. Likewise, facts about new suppliers can be added even though they have not yet supplied parts.

·        DELETE. Information about parts supplied can be deleted without destroying information about a supplier or a city. UPDATE. Changing the location of a supplier or the status of a city requires modifying only one row.

Designing a database

Basic questions

1.     Steps in designing a database

2.     Determining purpose of your database

3.     Determining the tables you need in the database

4.     Determining the fields you need in the database

5.     The field or fields with unique values in each record identifycation

6.     Determining the relationships between tables

7.     Design refinement.

8.     Data entering and other database objects creating

About designing a database

A database is a collection of data related to a particular topic or purpose (e.g., a phone book or an address file). Microsoft Access is an electronic database management system.

Before you use Microsoft Access Database Management System to actually build the tables, forms, and other objects that will make up your database, it is important to take time to design your database. Whether you are using a Microsoft Access database or a Microsoft Access project, good database design is the keystone to creating a database that does what you want it to do effectively, accurately, and efficiently.

Steps in designing a database

This topic provides reference information about these basic steps in designing a database:

1.     Determine the purpose of your database

2.     Determine the tables you need in the database

3.     Determine the fields you need in the tables

4.     Identify fields with unique values in each record

5.     Determine the relationships between tables

6.     Refine your design

7.     Enter data and create other database objects

8.     Use Microsoft Access analysis tools

Determine the purpose of your database

The first step in designing a database is to determine its purpose and how it’s to be used. You need to know what information you want from the database. From that, you can determine what subjects you need to store facts about (the tables) and what facts you need to store about each subject (the fields in the tables).

Talk to people who will use the database. Brainstorm about the questions you and they would like the database to answer. Sketch out the reports you’d like it to produce. Gather the forms you currently use to record your data. Examine well-designed databases similar to the one you are designing.

Determine the tables you need

Determining the tables can be the trickiest step in the database design process. That’s because the results you want from your database — the reports you want to print, the forms you want to use, the questions you want answered — don’t necessarily provide clues about the structure of the tables that produce them.

You don’t need to design your tables using Microsoft Access. In fact, it may be better to sketch out and rework your design on paper first. When you design your tables, divide up pieces of information by keeping these fundamental design principles in mind [19]:

·        A table should not contain duplicate information, and information should not be duplicated between tables. In this respect, a table in a relational database differs from a table in a flat-file application such as a spreadsheet.

When each piece of information is stored in only one table, you update it in one place. This is more efficient, and it also eliminates the possibility of duplicate entries that contain different information. For example, you would want to store each customer address and phone number only once, in one table.

·        Each table should contain information about one subject.

When each table contains facts about only one subject, you can maintain information about each subject independently from other subjects. For example, you would store customer addresses in a different table from the customers’ orders, so that you could delete one order and still maintain the customer information.

Determine the fields you need

Each table contains information about the same subject, and each field in a table contains individual facts about the table’s subject. For example, a customer table may include company name, address, city, state, and phone number fields. When sketching out the fields for each table, keep these tips in mind [19]:

·        Relate each field directly to the subject of the table.

·        Don’t include derived or calculated data (data that is the result of an expression).

·        Include all the information you need.

·        Store information in its smallest logical parts (for example, First Name and Last Name, rather than Name).

Identify the field or fields with unique values in each record

In order for Microsoft Access to connect information stored in separate tables — for example, to connect a customer with all the customer’s orders — each table in your database must include a field or set of fields that uniquely identifies each individual record in the table. Such a field or set of fields is called a primary key.

Determine the relationships between tables

Now that you’ve divided your information into tables and identified primary key fields, you need a way to tell Microsoft Access how to bring related information back together again in meaningful ways. To do this, you define relationships between tables in a Microsoft Access database.

You may find it useful to view the relationships in an existing well-designed database. For example, open the Northwind sample database and click Relationships on the Tools menu to see the relationships between its tables.

Refine your design

After you have designed the tables, fields, and relationships you need, it’s time to study the design and detect any flaws that might remain. It is easier to change your database desigow than it will be after you have filled the tables with data.

Use Microsoft Access to create your tables, specify relationships between the tables, and enter enough sample data in your tables so you can test your design. To test the relationships in your database, see if you can create queries to get the answers you want. Create rough drafts of your forms and reports and see if they show the data you expect. Look for unnecessary duplications of data and eliminate them. If you find problems, refine the design.

Enter data and create other database objects

When you are satisfied that the table structures meet the design principles described here, then it’s time to go ahead and add all your existing data to the tables. You can then create any queries, forms, reports, data access pages, macros, and modules that you may want.

Use Microsoft Access analysis tools

Microsoft Access includes two tools that can help you to refine the design of your Microsoft Access database. The Table Analyzer Wizard can analyze the design of one table at a time, can propose new table structures and relationships if appropriate, and can divide a table into new related tables if that makes sense.

The Performance Analyzer can analyze your entire database and make recommendations and suggestions for improving it. The wizard can also implement these recommendations and suggestions.

For additional ideas on designing a Microsoft Access database, you may want to look at the design of the Northwind sample database or of one of the databases that you can create with the Database Wizard.

Example 1

Task. On the families doctor allotted work inhabitation 15 men and 15 women. You must create the database for storage the people’s passport data and, in accordance, its results of the blood test.

Solution. After analyzing of the task, will arrive at a conclusion, that two tables will be containing in the database:

·        The first table, which contain passport data of the patient (named Pasport),

·        The second table, which contain blood test data (named Result).

Structure of the database more suitable described as following drawing (Figure 301).

After the tables amount determining, the attributes selecting will necessary, which will be stored in this tables. At the same time, the attributes data type must will be determined.

 

For the passport data it is possible choose following attributes:

 

Similarly, for the blood test data it is possible choose following attributes:

 

Remarks: 1 – primary key. This field that uniquely identifies each record stored in the table. 2- foreign key. This field using for creating the relationships between tables.

Figure 301. Structure of the database “Patient”.

Creating a database file

Basic questions

1.     Parts of the Access Window

2.     Choosing How to Create Your Database

3.     Main Database Window

Launch MS Acces applicatopn

For the Microsoft Access run you must in Microsoft windows press button“Strart” on the taskbar, then in menu select item “Programs” then – item “Microsoft Access”. After loading this application you can create a new database file. There are two ways to create new database: using Database Wizard, and without it.

Parts of the Access Window

Access is much like any other Office application: It contains menus, toolbars, a status bar, the Ask a Question box, and so on. Figure 302 provides a look at these different areas of the Access window. This view assumes that you have either created a new database or opened an existing database in the Access workspace [19].

Figure 302. Access provides the typical tools provided by the members of the Microsoft Office suite of applications.

Notice that in Figure 302 the Database window provides a list of icons on the left side for items such as Tables, Queries, Forms, and so on. It is these different items, called Access objects, that will make up your database. We will describe how each of these objects fits into the overall database later in the lesson.

Note. Access objects are the different items that make up a database such as tables, forms, queries, and reports.

You probably have noticed that most of the buttons on the toolbar are unavailable if you have opened Access and have not created or opened a database. That’s because you haven’t created any database objects, such as tables or forms, for the new database. The toolbar currently displayed in the Access window is the Database toolbar. Access differs from the other Office applications in that it has a different toolbar for each database object. In some cases, multiple toolbars exist for an object, depending on whether you are entering data into the object or changing the design parameters of the object [19].

For example, Access tables have two toolbars. The Table Datasheet toolbar provides you with tools that help you enter and manipulate the data in the table when you work with it in the Datasheet view. If you switch to the Design view of the table, a Table Design toolbar helps you manipulate the design settings for the table.

Because you will be working with each Access object type, you will also become familiar with each object toolbar. As you work with the various buttons on the toolbars, remember that you can place the mouse pointer on any toolbar button to see a ToolTip. The ToolTip shows the name of the button, which usually indicates what the particular tool is used for.

One other thing that should be mentioned related to the Access window is that only one database at a time can be open in the Access window. It doesn’t enable you to work on multiple databases at the same time, as you could work with multiple documents in Word, or multiple workbooks in Excel.

Note. As you work in Access on the various objects, right-click any toolbar to view a shortcut menu that provides a list of available toolbars. Typically, you are limited to the toolbar specific to the object that you are working on.

Choosing How to Create Your Database

Before you can create your database tables and actually enter data, you must create a database file. The database is really just a container file that holds all the database objects, such as the tables, forms, and reports. You have three options for creating a new database: You can create a blank database from scratch, create a new database based on a database template, or you can create a new database based on the structure of an existing database file. This option actually creates a copy of the existing database file including the database’s structure and the objects contained in the database (minus the data it contains). The third alternative would be great in situations where you want to share the structure for a database with a colleague and also show them how your data is organized in the database [19].

Note. What Are Projects? Another option when you use the New File Task Pane is a project. Projects are Access front-ends or conduits to powerful SQL databases maintained on a server running Microsoft SQL Server. Access allows you to access this remote data just as you would a database that you created that you have saved on your computer.

Creating a new database based on a template (a template other than the Blank Database template) means that you take advantage of a Database Wizard, which not only creates your new database file but also helps you quickly create tables, forms, and other objects for the database.

Note. Access provides several templates for creating new database files, and the Database Wizard walks you through the process of creating objects, such as tables, for the new database.

Whether you create your new database from scratch or use one of the database templates depends on how closely one of the Access templates meets your database needs. If one of the templates provides you with the type of tables and other objects necessary for your database, it makes sense to use a template. For example, if you want to create a database that helps you manage your company’s inventory, you can take advantage of the Inventory Control template that Access provides. This template provides you with the basic tables and other objects to start the process of getting a handle on your inventory database.

In some cases, the templates might not meet your needs. For example, if you want to create a complex database that allows you to track sales, customers, and employee performance, it might be easier to create a blank database and then create each table for the database as needed. Let’s start the overview of database creation with creating a blank database.

Selecting a Database File Type

One thing to discuss before you look at creating a new database is the database file format. By default, new databases created in Access are created in the Access 2000 file format. This makes your database files compatible with earlier versions of Access, such as Access 2000 and Access 97.

Saving the database in the Access 2000 file format does not prevent you from using any of the tools or features available in Access 2003. If you use your database files only in Access 2002 or 2003, you can set the default file format for new databases to Access 2002-2003. You must have a database (blank or otherwise) open to access the Options dialog box. Select the Tools menu, and then select Options. The Options dialog box opens [19].

Select the Advanced tab on the Options dialog box. Click the Default File Format drop-down box and select Access 2002-2003. Now let’s take a look at creating new databases.

Creating a Blank Database

Creating a blank database is very straightforward. As mentioned previously, you are just creating the container file that holds all the objects that actually make up the database. To create a blank database, follow these steps:

1.      In the Access window select the New button on the Database toolbar or select File, then New. The New File Task Pane will appear.

2.     Select Blank Database in the task pane. The File New Database dialog box (Figure 303) appears.

3.     Use the Save In drop-down box to locate the folder in which you want to save the new database. Type a name for the new file into the File Name text box.

4.     When you are ready to create the database file, click Create. The new database window appears in the Access workspace.

Figure 303. Provide a location and a name for the new database file.

Creating a Database from a Template

Another option for creating a new database is using one of the Access database templates. Templates are available for asset tracking, contact management, inventory control, and other database types. Another perk of using an Access template to create a new database is that a Database Wizard creates tables and other objects, such as forms and reports, for the new database. The wizard also sets up the relationships between the various tables (making your database relational).

Your interaction with the Database Wizard is somewhat limited; the wizard allows you to select the fields that will be used in the tables that it creates for the database. However, you don’t have a say about which tables are initially created (tables can always be deleted later if you don’t need them). You are, however, given the opportunity to select the format for screen displays (for forms and reports) and select the format for printed reports.

To create a database from a template, follow these steps [19]:

1.     In the Access window, open the New File task pane: Select File, New. In the Templates area of the New File task pane, click the On My Computer link.

2.     The Templates dialog box appears. If necessary, click the Databases tab on the dialog box to view the database templates (Figure 304).

3.     Click the database template you want to use (for example, the Contact Management template) and then click OK. The File New Database dialog box appears (refer toFigure 303).

4.     Specify a location for the database using the Save In drop-down list, type a name for the database, and then click Create to continue. A new database file is created, and then the Database Wizard associated with the template starts. For example, if you chose the Contact Management template, the wizard appears and explains the type of information that the database holds.

5.     To move past the wizard’s opening screen, click Next. On the next screen, a list of the tables that will be created appears (Figure 305). The tables in the database are listed on the left of the screen and the selected table’s fields appear on the right.

6.     Select a table to examine its fields. If you do not want to include a field in the table, clear the check box next to the field name. Optional fields are also listed for each field and are shown in italics. To include an optional field, click it to place a check mark next to it. When you have finished viewing the tables and their fields, click Next to continue.

7.     The next screen asks you to select the screen display style you want to use. This affects how forms appear on the screen. Click a display style in the list to preview the style; after selecting the style you want to use, click Next.

8.     On the next screen, the wizard asks you to choose a visual style for your printed reports. Click a report style and examine the preview of it. When you decide on a style, click it, and then click Next.

9.     On the next wizard screen, you are asked to provide a title for the database. This title appears on reports and can be different from the filename. Enter a title as shown in Figure 306.

10. (Optional) To include a picture on your forms and reports (for example, your company’s logo), click the Yes, I’d Like to Include a Picture check box. Then click the Picture button, choose a picture file from your hard drive (or other source), and click OK to return to the wizard.

11. Click Next to continue. You are taken to the last wizard screen. On this screen there is a checkbox that says “Yes, start the database.” Make sure that this is selected so that the database will open when you complete the process. Click Finish to open the new database. The wizard goes to work creating your database and its database objects.

Figure 304. Access provides several database templates.

Figure 305. You can examine and deselect (or select) the fields that will be contained in each table.

Figure 306. Enter a title for the database, and as an option, choose a graphic to use for a logo.

Note. Be Careful Deselecting Fields! Because you are stuck with the tables that the Database Wizard creates, you must be very careful removing fields from the tables. This is especially true of fields that uniquely identify the records in a table, such as Contact ID. These fields are often used to relate the tables in the database. You might want to leave all the fields alone initially when you use the wizard [19].

When the wizard has finished creating the database, the database’s Main Switchboard window appears (Figure 307). The Main Switchboard opens automatically whenever you open the database.

Figure 307. The Switchboard window is a database navigation tool provided by the Database Wizard.

All the databases created using one of the Access templates (other than the Blank Database template) include a Main Switchboard. The Switchboard is actually a form with some programming built into it. It enables you to perform common tasks related to database management by clicking a button. It is very useful when a person is unfamiliar with how to manipulate the various objects in a database.

For example, to enter or view contacts in the database shown in Figure 307, you would click Enter/View Contacts. This action opens a form (which is used to view and edit data into a database table) that allows you to view and enter contact information. If you click the Preview Reports button, a second Switchboard opens and you are provided with a list of ready-made reports that are available for you to view. Again, these reports were created by virtue of the fact that you used a template to create your new database.

Using the Main Switchboard for a database is a quick and straightforward way of quickly getting data into a database and taking advantage of a number of ready-made objects that were created for you. You will find, however, that as you become more familiar with Access, you will probably want to work with your database objects directly (such as tables, forms, and reports) and will no longer use the Main Switchboard. To close the Switchboard, click its Close (X) button [19].

After you close the Switchboard window, you will find that the database window has been minimized in the Access workspace. Just double-click its title bar (at the bottom-left corner of the screen) to open it. To see the tables that the wizard created, click the Tables object type. Click the other object types (such as forms) to see the other objects that were created by the wizard.

The tables that the wizard creates are, of course, empty. After you fill them with data (either inputting the data directly into the table or using a form), you will be able to run queries and create reports.

Main Database Window

The database window (Figure 308) provides you with a set of icons that enable you to select a particular object type. For example, the Tables icon is selected by default after you create the new database (which makes sense, because you need to create at least one table before you can create any of the other object types, such as a form or a report).

Figure 308. Main Database window.

Shortcuts for different methods of creating tables are provided at the top of the Object pane. After you create a new table for the database, it is listed in this pane.

The database window enables you to view the different objects that you’ve created for a particular database (or those that were created when you used the Database Wizard). When you want to switch the database window’s focus to a different Access object, all you have to do is click the appropriate icon in the Objects list [19].

Note. The toolbar on the database window provides buttons for opening or creating a particular database object, such as a table or a form. The toolbar also provides buttons that can be used to change the view in the Object pane: Large Icons, Small Icons, List (the default view) and Details (which provides information such as when the object was last modified). To collapse the icons shown on the left side of the Database window to categories such as Objects, click the Groups button.

Example 2

Task. For database, designed in example 1, create its Access realise.

Solution. You must execute following steps:

1.     In Microsoft Windows press button“Strart” on the taskbar, then in menu select item “Programs” then – item “Microsoft Access”

2.     When Microsoft Access starts up, a dialog box is automatically displayed with options to create a new database or open an existing one. Select option “Create new database” and press “OK” button.

3.     In dialog “Save database” you may select disk and folder where database will be saved, and type file name for its. Then press button “Save”.

4.     After that you will see the Database window. You can press F11 to switch to the Database window from any other window.

Creating a database tables

Basic questions

1.     Ways to the table creation

2.     How choose fields for a new table from existing tables using the Table Wizard?

3.     How create tables automatically from your data by entering its in a datasheet?

4.     How create a new table from existing data?

5.     How create a table on your own design?

Ways to the table creation

Microsoft Access provides two general ways to create a table. You can create a blank (empty) table for entering your own data, or you can create a table using existing data from another source.

Use the Database Wizard to create in one operation all the tables, forms, and reports required for an entire database. The Database Wizard creates a new database; it can’t be used to add new tables, forms, or reports to an existing database.

Choose fields for a new table from existing tables using the Table Wizard

Use the Table Wizard to choose the fields for your table from a variety of predefined tables such as business contacts, household inventory, or medical records [19].

1.     If you haven’t already done so, switch to the Database window. You can press F11 to switch to the Database window from any other window.

2.     Click Tables  under Objects, and then click New on the Database window toolbar.

3.     Double-click Table Wizard.

4.     Follow the directions in the Table Wizard dialog boxes.

Note   If you want to modify or extend the resulting table, you can do so in Design view when you have finished using the Table Wizard.

Create tables automatically from your data by entering its in a datasheet

Enter data directly into a blank datasheet. When you save the new datasheet, Microsoft Access will analyze your data and automatically assign the appropriate data type and format for each field.

1.     Click Tables  under Objects, and then click New on the Database window toolbar.

2.     Double-click Datasheet View. A blank datasheet is displayed. The default columames are Field1, Field2, and so on.

3.     Rename each column you will use: double-click the columame, type a name for the column following Microsoft Access object-naming rules and then press ENTER.

4.     You can insert additional columns at any time: click in the column to the right of where you want to insert a new column, and then on the Insert menu, click Column. Rename the column as described in step 4.

5.     Enter your data in the datasheet.

Enter each kind of data in its own column (each column is called a field in Microsoft Access). For example, if you are entering names, enter the first name in its own column and the last name in a separate column. If you are entering dates, times, or numbers, enter them in a consistent format so that Microsoft Access can create an appropriate data type and display format for the column. Any columns you leave empty will be deleted when you save the datasheet [19].

1.     When you’ve added data to all the columns you want to use, click Save  on the toolbar to save your datasheet.

2.     Microsoft Access asks you if you want to create a primary key. If you haven’t entered data that can be used to uniquely identify each row in your table, such as part numbers or ID numbers, it’s recommended that you click Yes. If you have entered data that can uniquely identify each row, you can specify this field as your primary key.

Microsoft Access will assign data types to each field (column) based on the kind of data you entered. If you want to customize a field’s definition further — for example, to change its data type, or define a validation rule, use Design view.

Note   In addition to renaming and inserting columns, you can delete or reorder columns at any time, before or after saving your new datasheet.

Create a new table from existing data

Microsoft Access provides two ways to create a table from existing data:

·        You can import or link data from another Microsoft Access database or data in a variety of file formats from other programs.

·        You can perform a make-table query to create a table based on data in a current table. For example, you can use make-table queries to archive old records, to make backup copies of your tables, to select a group of records to export to another database, or to use as a basis for reports that display data from a specific time.

Create a table on my own

You can use the steps in this topic to guide you through creating a table from start to finish. You’ll use many of these steps in creating every table. The others you’ll use when you design a table to contain a particular type of data or define how a field appears. You need to use only the steps that are relevant to the type of data or field definitions specific to the table that you’re creating.

Create a table from scratch in Design view

1.     Click Tables  under Objects, and then click New on the Database window toolbar.

2.     Double-click Design View.

3.     Define each of the fields in your table.

4.     Define a primary key field before saving your table.

Note   You don’t have to define a primary key, but it’s usually a good idea. If you don’t define a primary key, Microsoft Access asks if you want it to create one for you when you save the table.

5.     When you are ready to save your table, click Save on the toolbar, and then type a name for the table following Microsoft Access object-naming rules.

What data type should I use for a field in my table?

Decide what kind of data type to use for a field based on these considerations [19]:

·        What kind of values do you want to allow in the field? For example, you can’t store text in a field with a Number data type.

·        How much storage space do you want to use for values in the field?

·        What types of operations do you want to perform on the values in the field? For example, Microsoft Access can sum values in Number or Currency fields, but not values in Text or OLE Object fields.

·        Do you want to sort or index a field? OLE Object fields can’t be sorted or indexed.

·        Do you want to use a field to group records in queries or reports? OLE Object fields can’t be used to group records.

·        How do you want to sort values in a field? In a Text field, numbers sort as strings of characters (1, 10, 100, 2, 20, 200, and so on), not as numeric values. Use a Number or Currency field to sort numbers as numeric values. Also, many date formats will not sort properly if entered in a Text field. Use a Date/Time field to ensure proper sorting.

Field data types available in Microsoft Access

The following table (Table 21) summarizes all the field data types available in Microsoft Access, their uses, and their storage sizes [19].

Table 21. Microsoft Access data types

 

Note. Number, Date/Time, Currency, and Yes/No data types provide predefined display formats. Set the Format property to choose from the formats available for each data type. You can also create a custom display format for all data types except the OLE Object data type.

Open a table and switch between views of a table

Tables have two views: Design view (Figure 309) and Datasheet view. You use Design view to create and modify the structure of a table. You use Datasheet view to view, add, delete, and edit data in a table [19].

1.     In the Database window, click Tables  under Objects.

2.     Click the name of the table you want to open.

3.     To open the table in Design view, click Design on the Database window toolbar. To open the table in Datasheet view, click Open on the Database window toolbar.

Note. After you’ve opened a table, you can easily switch between the two views by clicking the View button on the toolbar.

Figure 309. Table in the design view.

Add a field to a table in Design view

1.     Open the table in Design view.

2.     To insert the field within the table, click in the row below where you want to add the field, and then click Insert Rows  on the toolbar.

3.     To add the field to the end of the table, click in the first blank row.

4.     Click in the Field Name column and type the name for the field, following Microsoft Access object-naming rules.

5.     In the Data Type column, keep the default (Text); or click in the Data Type column, click the arrow, and select the data type you want.

6.     In the Description column, type a description of the information this field will contain. This description is displayed on the status bar when adding data to the field and is included in the Object Definition of the table. The description is optional.

7.     If you want, set field properties for the field in the bottom part of the window.

Note. If this is a linked table, you can’t add a new field in the current database. If the linked table is a Microsoft Access table, you must open its source database to add a field. If the linked table is from another application, you must open the source file with that application to add a field [19].

Create a field for text or memos

1.     Add new field into table.

2.     In the Data Type column, click the arrow and select either Text or Memo.

Note   The default field size for Text fields is 50 characters. You can change this for a particular field by setting the FieldSize property. You can change the default field size itself by clicking the Options command on the Tools menu, and then by clicking the Tables/Queries tab.

Create a field for Yes/No, True/False, and On/Off data

1.     Add new field into table.

2.     In the Data Type column, click the arrow and select Yes/No.

3.     To display True/False or On/Off instead of Yes/No (the default) in the field, in the bottom part of the window on the General tab, click in the Format box and select the format you want.

Create a field that automatically generates numbers

1.     Add new field into table.

2.     In the Data Type column, click the arrow and select AutoNumber.

3.     To create an incrementing AutoNumber, leave the property settings in the bottom part of the window as they are (the FieldSize property is set to Long Integer and the NewValues property is set to Increment by default). To create a random AutoNumber, in the bottom part of the window on the General tab, set the NewValues property to Random.

Create a field to store OLE objects (pictures, and so on)

1.     Add new field into table.

2.     In the Data Type column, click the arrow and select OLE Object.

Note OLE Object fields are used to store data such as Microsoft Word or Microsoft Excel documents, pictures, sound, and other types of binary data created in other programs. OLE objects can be linked to or embedded in a field in a Microsoft Access table. You must use a control in a form or report to display the OLE object.

Create a field for numbers or currency

1.     Add new field into table.

2.     In the Data Type column, click the arrow and select either Number or Currency.

3.     If you are creating a Number field, in the bottom part of the window set the FieldSize property to the size you want.

4.     To set a display format for your field, in the bottom part of the window click in the Format box, click the arrow, and select the format you want. You can also create a custom display format, if the predefined formats don’t meet your needs.

Note Changes made to the currency formats that are specified by double-clicking Regional Settings in Windows Control Panel will be automatically reflected in your database for Currency fields. However, this only affects the format; no conversion of currency values will be made.

Create a field for dates or times

1.     Add new field into table.

2.     In the Data Type column, click the arrow and select Date/Time.

Note Storing dates and times in a Date/Time field ensures that dates and times will be sorted properly. Also, changes made to the date or time formats that are specified by double-clicking Regional Settings in Windows Control Panel will be automatically reflected in Date/Time fields.

Move a field in table Design view

1.     Open the table in Design view.

2.     Select the field(s) you want to move. To select one field, click that field’s row selector. To select a group of fields, drag through the row selectors of those fields.

3.     Click and hold down the mouse button in the row selector again. Microsoft Access displays a thin horizontal bar just above the last selected row.

4.     Drag the horizontal bar to the row just below where you want to move the fields.

Note Changing field order in table Design view changes the order in which fields are stored in the table and also changes the column order in the table’s datasheet [19].

Delete a field from a table in Design view

1.     Open the table in Design view.

2.     Select the field(s) you want to delete. To select one field, click that field’s row selector. To select a group of fields, drag through the row selectors of those fields.

3.     Click Delete Row  on the toolbar.

Notes

·        If other database objects contain references to a deleted field, you need to delete those references as well. For example, if a report includes a control bound to a deleted field, Microsoft Access won’t be able to find the data from the field and will generate a message.

·        You can’t delete a field that’s part of a relationship. You must delete the relationship first.

Optimize general table performance

There are several things you can do to optimize your tables. In addition to the following tips, you can use the Performance Analyzer to analyze specific tables in your database.

Design tables without redundant data. A well-designed database is a prerequisite for fast data retrieval and updates. If existing tables contain redundant data, you can use the Table Analyzer Wizard to split your tables into related tables to store your data more efficiently.

Choose appropriate data types for fields. You can save space in your database and improve join operations by choosing appropriate data types for fields. When defining a field, choose the smallest data type or field size that’s appropriate for the data in the field.

Create indexes for fields you sort, join, or set criteria for. You can make dramatic improvements in the speed of queries by indexing fields on both sides of joins, or by creating a relationship between those fields and indexing any field used to set criteria for the query. Finding records through the Find dialog box is also much faster when searching an indexed field [19].

Indexes aren’t appropriate in all cases, however. Indexes add to the size of the .mdb file, reduce concurrency (the ability of more than one user to modify a page at the same time) in multiuser applications, and decrease performance when you update data in fields that are indexed, or when you add or delete records. It’s a good idea to experiment to determine which fields should be indexed. Adding an index may speed up a query one second, but slow down adding a row of data by two seconds and cause locking problems. Or it may add negligible gains depending on which other fields are indexed. For example, adding an index to a PostalCode field may provide very little performance gain if a CompanyName field and LastName field in the table are already indexed. Regardless of the types of queries you create, you should only index fields that have mostly unique values.

Keys and indexes in the tables

Basic questions

1.     What kind of primary key should I use?

2.     AutoNumber primary keys

3.     Single-field and Multiple-field primary keys

4.     Set or change the primary key

5.     Remove the primary key

6.     Create a Single-field or Multiple-field index

7.     Delete an index

What kind of primary key should I use?

The power of a relational database system such as Microsoft Access comes from its ability to quickly find and bring together information stored in separate tables using queries, forms, and reports. In order to do this, each table should include a field or set of fields that uniquely identifies each record stored in the table. This information is called the primary key of the table. Once you designate a primary key for a table, to ensure uniqueness, Microsoft Access will prevent any duplicate or Null values from being entered in the primary key fields.

There are three kinds of primary keys that can be defined in Microsoft Access: AutoNumber, single-field, and multiple-field.

AutoNumber primary keys

An AutoNumber field can be set to automatically enter a sequential number as each record is added to the table. Designating such a field as the primary key for a table is the simplest way to create a primary key. If you don’t set a primary key before saving a newly created table, Microsoft Access will ask if you want it to create a primary key for you. If you answer Yes, Microsoft Access will create an AutoNumber primary key [19].

Single-field primary keys

If you have a field that contains unique values such as ID numbers or part numbers, you can designate that field as the primary key. If the field you select as primary key does have duplicate or Null values, Microsoft Access won’t set the primary key. You can run a Find Duplicates query to determine which records contain duplicate data. If you can’t readily eliminate duplicate entries by editing your data, you can either add an AutoNumber field and set it as the primary key or define a multiple-field primary key.

Multiple-field primary keys

In situations where you can’t guarantee the uniqueness of any single field, you may be able to designate two or more fields as the primary key. The most common situation where this arises is in the table used to relate two other tables in a many-to-many relationship. The Order Details table in the Northwind sample database is such a table, relating the Orders and Products tables. Its primary key consists of two fields: OrderID and ProductID (Figure 310). The Order Details table can list many products and many orders, but each product can only be listed once per order, so combining the OrderID and ProductID fields produces an appropriate primary key [19].

Figure 310. A multiple-field primary keys

Note. If you are in doubt about whether you can select an appropriate combination of fields for a multiple-field primary key, you should probably add an AutoNumber field and designate it as the primary key instead. For example, combining FirstName and LastName fields to produce a primary key is not a good choice, since you may eventually encounter duplication in the combination of these two fields.

Set or change the primary key

1.     Open a table in Design view.

2.     Select the field or fields you want to define as the primary key. To select one field, click the row selector for the desired field. To select multiple fields, hold down the CTRL key and then click the row selector for each field.

3.     Click Primary Key on the toolbar.

Notes

·        You can specify a primary key for a field that already contains data, but Microsoft Access generates a message when you save the table if it finds duplicate values or Null values in the field. If you encounter this message, you have three choices: use a Find Duplicates query to locate records with duplicate values or Null values and then edit the field to remove them; choose a different field; or add an AutoNumber field and set it as the primary key.

·        In a multiple-field primary key, field order may be important to you. The fields in a multiple-field primary key are sorted according to their order in table Design view. If you want a different order, first specify the fields for the primary key as described in the preceding procedure, and then click Indexes  on the toolbar to display the Indexes window and reorder the field names for the index named PrimaryKey.

Remove the primary key

1.     If the primary key is used in a relationship, you must delete the relationship before you can remove the primary key.

2.     Open the table in Design view.

3.     Click the row selector for the current primary key and then click Primary Key on the toolbar.

Note   This procedure doesn’t delete the field or fields that are designated as the primary key; it simply removes the primary key features from the table. In some situations, you may need to temporarily remove the primary key. For example, importing records into a table may result in some duplicate records. Until you eliminate the duplicates, you should remove the primary key.

Create a single-field index

Indexes speed up searches for data in the table of databases. To create a single-field index you may [19]:

1.     Open a table in Design view.

2.     In the upper portion of the window, click the field that you want to create an index for.

3.     In the lower portion of the window on the General tab, click in the Indexed property box, and then click Yes (Duplicates OK) or Yes (No Duplicates). Click Yes (No Duplicates) if you want to ensure that no two records have the same data in this field.

Create a multiple-field index

1.     Open the table in Design view.

2.     Click Indexes on the toolbar.

3.     In the first blank row in the Index Name column, type a name for the index. You caame the index after one of the index fields, or use some other appropriate name.

4.     In the Field Name column, click the arrow and select the first field for the index.

5.     In the next row in the Field Name column, select the second field for the index. (Leave the Index Name column blank in that row.) Repeat this step until you have selected all the fields you want to include in this index. You can use up to 10 fields.

Note. The default sort order is Ascending. Select Descending in the Sort Order column of the Indexes window to sort the corresponding field’s data in descending order.

Delete an index

Indexes speed up searches, but they can also slow bulk record updates such as append queries. If you have indexed a field or fields that you don’t use to search, sort, or join, and you perform operations that perform bulk record updates that you want to speed up, delete the index. Deleting an index doesn’t delete the field or the data in the field.

To delete a single-field index

1.     Open the table in Design view.

2.     In the upper portion of the window, click the field whose index you want to delete.

3.     In the lower portion of the window on the General tab click in the Indexed property box, and then click No.

To delete a multiple-field index

1.     Open the table in Design view.

2.     Click Indexes  on the toolbar.

3.     In the Indexes window, select the rows containing the index you want to delete and press the DELETE key. This removes only the index, not the field itself.

Example 3.

Task. Create the tables for database, created in example 2.

Solution. Create table for store the passport data:

1.     Click Tables  under Objects, and then click New on the Database window toolbar.

2.     Double-click Design View (fig. 4).

3.     Define each of the fields in table:

a.      Add new fields into table as in 3.5.6. and type names of the each fields.

b.     For each fields in the Data Type column, click the arrow and select either data types, as defined iext table:

 

 

2.     Define a primary key field: select field named “IDPerson” and Click Primary Key  on the toolbar.

3.     When you are ready to save your table, click Save on the toolbar, and then type a name “Passport” for this table.

Create table for store the blood test data:

1.     Click Tables  under Objects, and then click New on the Database window toolbar.

2.     Double-click Design View (fig. 4)..

3.     Define each of the fields in table:

a.      Add new fields into table as in 3.5.6. and type names of the each fields.

b.     For each fields in the Data Type column, click the arrow and select either data types, as defined iext table:

 

 

4.     Define a primary key field: select field named “IDTest” and Click Primary Key  on the toolbar.

5.     When you are ready to save your table, click Save on the toolbar, and then type a name “Result” for this table following Microsoft Access object-naming rules.

About Relationships in an Access databases

Basic questions

1.     Why define relationships?

2.     How do relationships work?

3.     A one-to-many relationship

4.     A many-to-many relationship

5.     A one-to-one relationship

6.     Defining relationships in MS Access databases

7.     Define the default join type for a relationship between two tables

8.     Define a many-to-many relationship between tables

9.     Delete a relationship

10. Edit an existing relationship

11. View existing relationships

Why define relationships?

After you’ve set up different tables for each subject in your Microsoft Access database, you need a way of telling Microsoft Access how to bring that information back together again. The first step in this process is to define relationships between your tables. After you’ve done that, you can create queries, forms, and reports to display information from several tables at once. For example, this form includes information from five tables (Figure 311) [19]:

Figure 311. Form that include information from five tables

How do relationships work?

In the previous example, the fields in five tables must be coordinated so that they show information about the same order. This coordination is accomplished with relationships between tables. A relationship works by matching data in key fields — usually a field with the same name in both tables. In most cases, these matching fields are the primary key from one table, which provides a unique identifier for each record, and a foreign key in the other table. For example, employees can be associated with orders they’re responsible for by creating a relationship between the Employees table and the Orders table using the EmployeeID fields (Figure 312).

A one-to-many relationship

A one-to-many relationship is the most common type of relationship. In a one-to-many relationship, a record in Table A can have many matching records in Table B, but a record in Table B has only one matching record in Table A (Figure 313).

Figure 312. How do relationships work

Figure 313. A one-to-many relationship

A many-to-many relationship

In a many-to-many relationship, a record in Table A can have many matching records in Table B, and a record in Table B can have many matching records in Table A. This type of relationship is only possible by defining a third table (called a junction table) whose primary key consists of two fields — the foreign keys from both Tables A and B. A many-to-many relationship is really two one-to-many relationships with a third table. For example (Figure 314), the Orders table and the Products table have a many-to-many relationship that’s defined by creating two one-to-many relationships to the Order Details table [22].

A one-to-one relationship

In a one-to-one relationship, each record in Table A can have only one matching record in Table B, and each record in Table B can have only one matching record in Table A. This type of relationship is not common, because most information related in this way would be in one table. You might use a one-to-one relationship to divide a table with many fields, to isolate part of a table for security reasons, or to store information that applies only to a subset of the main table. For example, you might want to create a table to track employees participating in a fundraising soccer game (Figure 315).

Figure 314. A many-to-many relationship

Figure 315. A one-to-one relationship

Defining relationships in MS Access databases

You define a relationship by adding the tables that you want to relate to the Relationships window, and then dragging the key field from one table and dropping it on the key field in the other table.

The kind of relationship that Microsoft Access creates depends on how the related fields are defined (Figure 316) [19]:

1.     A one-to-many relationship is created if only one of the related fields is a primary key or has a unique index.

2.     A one-to-one relationship is created if both of the related fields are primary keys or have unique indexes.

3.     A many-to-many relationship is really two one-to-many relationships with a third table whose primary key consists of two fields — the foreign keys from the two other tables.

Note. If you drag a field that isn’t a primary key and doesn’t have a unique index to another field that isn’t a primary key and doesn’t have a unique index, an indeterminate relationship is created. In queries containing tables with an indeterminate relationship, Microsoft Access displays a default join line between the tables, but referential integrity won’t be enforced, and there’s no guarantee that records are unique in either table.

Figure 316. Defining relationships in MS Access databases

Define relationships between tables

1.     Close any tables you have open. You can’t create or modify relationships between open tables.

2.     Click Relationships  on the toolbar.

3.     If your database doesn’t have any relationships defined, the Show Table dialog box will automatically be displayed. If you need to add the tables you want to relate and the Show Table dialog box isn’t displayed, click Show Table on the toolbar. If the tables you want to relate are already displayed, skip to step 6.

4.     Double-click the names of the tables you want to relate, and then close the Show Table dialog box.

5.     Drag the field that you want to relate from one table to the related field in the other table. To drag multiple fields, press the CTRL key and click each field before dragging them. In most cases, you drag the primary key field (which is displayed in bold text) from one table to a similar field (often with the same name) called the foreign key in the other table. The related fields don’t have to have the same names, but they must have the same data type (with two exceptions) and contain the same kind of information. In addition, when the matching fields are Number fields, they must have the same FieldSize property setting. The two exceptions to matching data types are that you can match an AutoNumber field with a Number field whose FieldSize property is set to Long Integer; and you can match an AutoNumber field with a Number field if both fields have their FieldSize property set to Replication ID [19].

6.     The Edit Relationships dialog box is displayed. Check the field names displayed in the two columns to ensure they are correct. You can change them if necessary. Set the relationship options if necessary. For information about a specific item in the Relationships dialog box, click the question mark button , and then click the item.

7.     Click the Create button to create the relationship.

8.     Repeat steps 5 through 8 for each pair of tables you want to relate. When you close the Relationships window, Microsoft Access asks if you want to save the layout. Whether you save the layout or not, the relationships you create are saved in the database.

Notes  

1.     If you need to view all the relationships defined in the database, click Show All Relationships  on the toolbar. To view only the relationships defined for a particular table, click the table, and then click Show Direct Relationships  on the toolbar.

2.     If you need to make a change to the design of a table, you can right-click the table you want to change, and then click Table Design.

3.     You can create relationships using queries as well as tables. However, referential integrity isn’t enforced with queries.

4.     To create a relationship between a table and itself, add that table twice. This is useful in situations where you need to perform a lookup within the same table. For example, in the Employees table in the Northwind sample database, a relationship has been defined between the EmployeeID and ReportsTo fields, so that the ReportsTo field can display employee data from a matching EmployeeID.

Define the default join type for a relationship between two tables

1.     Click Relationships  on the toolbar to open the Relationships window.

2.     Double-click the middle section of a join line between two tables to open the Relationships dialog box.

3.     Click the Join Type button, and then click the desired join type.

a.      Option 1 defines an inner join. This is the default.

b.     Option 2 defines a left outer join.

c.     Option 3 defines a right outer join.

Note   The Join Type button won’t be enabled if the tables are linked tables. If the tables are in Microsoft Access format, you can open the database in which they are stored to set the join type.

Defining the join type for a relationship in the Relationships window doesn’t affect the relationship itself; it sets the kind of join that will be used by default when creating queries based on the related tables. You can always override the default join type later when defining a query.

Define a many-to-many relationship between tables

1.     Create the two tables that will have a many-to-many relationship.

2.     Create a third table, called a junction table, and add fields with the same definitions as the primary key fields from each of the other two tables to this table. In the junction table, the primary key fields function as foreign keys. You can add other fields to the junction table, just as you can to any other table [19].

3.     In the junction table, set the primary key to include the primary key fields from the other two tables. For example, in an Order Details junction table, the primary key would be made up of the OrderID and ProductID fields.

4.     Define a one-to-many relationship between each of the two primary tables and the junction table.

5.     To add data to the tables, do one of the following:

a.      Create a query that works with more than one table.

b.     Create a form that works with more than one table.

Delete a relationship

1.     Close any tables you have open. Click Relationships on the toolbar to open the Relationships window.

2.     If the tables whose relationship you want to delete aren’t displayed, click Show Table on the toolbar and double-click each table you want to add. Then click Close.

3.     Click the relationship line for the relationship you want to delete (the line will turn bold when it’s selected), and then press the DELETE key.

Edit an existing relationship

1.     Click Relationships on the toolbar to open the Relationships window.

2.     If the tables whose relationship you want to edit aren’t displayed, click Show Table on the toolbar and double-click each table you want to add.

3.     Double-click the relationship line for the relationship you want to edit.

4.     Set the relationship options.

Remove a table from the Relationships window

Click the table you want to remove, and then press the DELETE key. Microsoft Access removes the table and its relationship lines from the Relationships window. This action affects only the display of the Relationships window. The table and relationships remain in the database.

View existing relationships

1.     Click Relationships on the toolbar to open the Relationships window.

2.     To view all the relationships defined in the database, click Show All Relationships on the toolbar.

3.     To view only the relationships defined for a particular table, click the table, and then click Show Direct Relationships on the toolbar.

Note If all of the tables related to the selected table are already displayed in the Relationships window, clicking the Show Direct Relationships button will have no effect because it only adds tables related to the current table. If you want to view only the direct relationships for one table, click Clear Layout on the toolbar to remove all tables from the Relationships window (this won’t delete the tables or relationships, it just removes them from the Relationships window), add the table back, and then click the Show Direct Relationships button.

Example 1.

Task. the relationship for the database, created in lection “Database development using Microsoft Access: Design database and create table”, example 2.

Solution. Create relationships between tables “Passport” and “Result”:

1.     Close any tables you have open. You can’t create or modify relationships between open tables.

2.     Click Relationships  on the toolbar.

3.     the Show Table dialog box will automatically be displayed because your database doesn’t have any relationships defined.

4.     Double-click the names of the tables you want to relate: “Passport” and “Result”, and then close the Show Table dialog box.

5.     Drag the field “IDPerson” from table “Passport” to the related field “IDPerson” in the table “Result”.

6.     The Edit Relationships dialog box is displayed. Check the field names displayed in the two columns to ensure they are correct. You can change them if necessary. Set the relationship options if necessary.

7.     Click the Create button to create the relationship. Results must be as you can see on the following picture: Figure 317

Figure 317. Relationships between table.