| Free Tutorials |
| HTML |
 Learn HTML |
| Learn CSS |
| Learn XML |
| Learn WML |
| Database |
| Learn Access |
| Learn Data-VB |
| Learn Oracle |
| Learn SQL |
| Programming |
| Learn C++ |
| Learn JavaScript |
| Learn Vbscript |
| Learn VisualBasic |
Each record in the Skills table must contain a valid value in the EmpID field or it should be rejected. In other words, each time a record is added to the Skills table, the value in the EmpID field should be checked against values in the EmpID field of the Employees table. If no match is found, the Skills record must be corrected before it is written to the database. You remember from the discussion of SQL Data Definition Language statements on Day 13 that this is a FOREIGN KEY CONSTRAINT. The field EmpID in the Skills table is a foreign key that references the field EmpID in the Employees table. Also, the EmpID field in the Employees table should be a primary field to make sure that each record in the Employee table has a unique EmpID value. Now that you know the fields and index constraints you need, you can use SQL DDL to create two new tables. If you have not already done so, start the Visdata application and open the NORMDAT1.MDB database. Now you create two new tables that bring the database into compliance with the first rule of data normalization. First, create the table that holds all the basic employee data. This table has all the fields that were in the Table1 table, minus the skill fields. Using the information in Table 16.1 as a guide, enter an SQL DDL statement in the SQL window of Visdata that creates the Employees data table. Your SQL statement should resemble Listing 16.1. Listing 16.1. Creating the Employees table.CREATE TABLE Employees
(EmpID TEXT(5),
EmpName TEXT(30),
DeptID TEXT(5),
DeptName TEXT(20),
DeptLocation TEXT(20),
CONSTRAINT PKEmpID PRIMARY KEY (EmpID)); The EmpID field has been designated as a primary key field. This guarantees that no two records in the Employees data table can have the same EmpID value. You can use the EmpID field in the next table you create (the Skills table) as the reference field that links the two tables. Because you are using the EmpID field as a link, it must be a unique value in the Employees table in order to maintain database integrity. What you are doing here is setting up a one-to-many relationship between the Employees table (the one-side) and the Skills table (the many-side). Any time you establish a one-to-many relationship, you must make sure that the reference field (in this case, the EmpID field) is unique on the one-side of the relationship. Now that you have built the Employees table, you can create the table that holds all the skills data. Use the information in Table 16.1 to write an SQL DDL statement that creates a table called Skills. Make sure the new table has the field EmpID and that the EmpID field is built with the correct index constraint to enforce one-to-many database integrity. Your SQL statement should look like the one in Listing 16.2. Listing 16.2. Creating the Skills table.
CONSTRAINT FKEmpID FOREIGN KEY (EmpID) REFERENCES Employees(EmpID)); You can see in Listing 16.2 that you have used the FOREIGN KEY_REFERENCES syntax to establish and maintain the table relationship. As you remember from the SQL lessons on Day 13 and Day 15, the FOREIGN KEY_REFERENCES syntax makes sure that any entry in the Skills.EmpID field can be found in the related Employees.EmpID field. If users enter a value in the Skills.EmpID field that cannot be found in any Employees.EmpID field, Visual Basic automatically issues a database error message. This message is generated by Visual Basic, not by your program. That is how you build tables that adhere to the
first rule of data normalization. To see how these tables look when they have live data in
them, use Visdata to load the TYSDBVB5\ SOURCE\DATA\NORMDAT2.MDB database. This
database contains the Employees and Skills tables with data already loaded into them.
Figure 16.3 shows how Visdata displays the two new tables that have live data.
Rule 2: Eliminate Redundant DataAnother aspect of the Skills table also needs attention. Although moving the repeating skills fields into a separate table improves the database, you still have work to do. The Skills table contains redundant data. That is, data is stored in several places in the database. Redundant data in your database can lead to serious database integrity problems. It's best to eliminate as many occurrences of redundant data as possible.
For example, the Skills table includes a field called SkillCode. This field contains a code that identifies the specific skill (or skills) each employee has acquired. If two employees have gained the same skill, that skill appears twice in the Skills file. The same table also includes a field called SkillName. This field contains a meaningful name for the skill represented by the value in the SkillCode field. This name is much more readable and informative than the SkillCode value. In essence, these two fields contain the same data, represented slightly differently. This is the dreaded redundant data you have to eliminate! Before you jump into fixing things, first review the details regarding redundant data and how it can adversely affect the integrity of your database. Update Integrity ProblemsWhen you keep copies of data elements in several rows in the same table or in several different tables (such as job names to go with job ID codes), you have a lot of work ahead of you when you want to modify the copied data. If you fail to update one or more of these copies, you can ruin the integrity of your database. Redundant data can lead to what are known as update integrity problems. Imagine that you have built a huge database of employee skills using the tables you built in the preceding section. All is going great when, suddenly, the Human Resources Department informs you that it has designed a new set of names for the existing skill codes. You now have to go through the entire database and update all the records in the Skills table, searching out the old skill name and updating the SkillName field with the new skill name. Because this is an update for the entire data table, you have to shut down the database until the job is complete in order to make sure no one is editing records while you're performing this update. Also, you probably have to change some Visual Basic code that you built to verify the data entry. All in all, it's a nasty job. If that isn't enough, how about a little power outage in the middle of your update run? Now you have some records with the old names, and some with the new names. Things are really messed up! Delete Integrity ProblemsAlthough the update integrity problem is annoying, you can suffer through most of those problems. In fact, almost all database programmers have had to face similar problems before. The more troublesome integrity problem resulting from redundant data comes not during updates, but during deletes. Let's assume you have properly handled the mass update required by the Human Resources Department. Then you discover that there is only one employee in the entire database that has the SkillCode S099 (Advanced Customer Service course). No other employee has attained this high level of training. Now, that employee is leaving the organization. When you delete the employee record from the file, you delete the only reference to the Advanced Customer Service course! There is no longer any record of the Advanced Customer Service course in your entire database, which is a real problem. The Normalization SolutionThe way to reduce these kinds of data integrity problems is to pull out the redundant data and place it in a separate table. You need a single table, called SkillMaster, that contains only the SkillCode and the SkillName data fields. This table is linked to the Skills table through the SkillCode field. Now, when the Human Resources department changes the skill names, you only need to update a single record--the one in the SkillMaster table. Because the Skills table is linked to the SkillMaster table, when you delete the employee with the certification for SkillCode S099, you don't delete the last reference to the skill. It's still in the SkillMaster table.
Also, you now have a single table that lists all the unique skills that can be acquired by your employees. You can produce a Skills list for employees and managers to review. If you add fields that group the skills by department, you can even produce a report that shows all the skills by department. This would be very difficult if you were stuck with the file structure you developed in the preceding section. So let's redefine the Skills table and the
SkillMaster table to conform to the second rule of data normalization. Table 16.2 shows
the fields you need for the two tables.
Listing 16.3 shows the two SQL DDL statements that create the EmpSkills and the SkillMaster data tables. Note the use of FOREIGN KEY constraints in the EmpSkills table. Listing 16.3. Creating the SkillMaster and EmpSkills tables.
You now have a database that conforms to the first
two rules of data normalization. You have eliminated repeating data and redundant data.
You have one more type of data to eliminate from your tables. You handle that in the
following section.
Rule 3: Eliminate Columns Not Dependent on the Primary KeyBy now, you're probably getting the idea. You are looking for hints in the table structure that lead you into traps further down the road. Will this table be easy to update? What happens if you delete records from this table? Is it easy to get a comprehensive list of all the unique records in this table? Asking questions like these can uncover problems that are not so apparent when you first build a table. When you are building a data table, you should also be concerned about whether a field describes additional information about the key field. In other words, is the field you are about to add to this table truly related to the key field? If not, the field in question should not be added to the table. It probably needs to be in its own table. This process of removing fields that do not describe the key field is how you make your data tables conform to the third rule of data normalization--eliminate columns not dependent on keys.
In these database examples, you have data describing
the various departments in the company stored in the Employees table. Although the DeptID
field is important to the Employees description (it describes the department to which the
employee belongs), the department-specific data should not be stored with the employee
data. Yes, you need another table. This table should contain only department-specific data
and be linked to the Employees table through the DeptID field. Table 16.3 lists the
modified Employees table and the new Departments table.
Listing 16.4. Creating the Departments table.
Listing 16.5 contains the SQL DDL statements to create the modified Employees table. Listing 16.5. Creating the new Employees table.
Listing 16.6. Creating the new EmpSkills table.
The first three rules of data
normalization involve the elimination of repeating, redundant, or unrelated data fields.
The last two rules involve isolating multiple relationships to improve overall database
integrity. The first three rules are usually all that you need to produce well-designed
databases. However, there are times when additional normalization can improve the quality
of your database design. In the next two sections, you learn rules 4 and 5 of data
normalization.
|
| EmpID Degree YearCompleted InstitutionName |
Listing 16.7 is a sample SQL DDL statement that creates the Training data table with the
proper relationship constraint. Enter this statement in the SQL window of Visdata while
you have the NORMDAT4.MDB database open. Check your results against Figure 16.6.
Listing 16.7. Creating the Training table.
| CREATE TABLE Training (EmpID TEXT(5), Degree TEXT(20), YearCompleted INTEGER, InstitutionName TEXT(30), CONSTRAINT PKTraining PRIMARY KEY (EmpID,Degree), CONSTRAINT FKEmpTrn FOREIGN KEY (EmpID) REFERENCES Employees (EmpID)) |
The database NORMDAT5.MDB contains a complete version of the database normalized
up to the fourth rule of data normalization. Use Visdata to open the database and review
the table structure.
NOTE: Before continuing with the lesson, load the NORMDAT5.MDB database into Visdata.
Figure 16.6. The Training
table shows the degree achievements for the Employees table.
Rule 5: Isolate Related Multiple Relationships
The last remaining rule of data normalization covers the handling of related multiple relationships in a database. Unlike the fourth rule, which deals with independent, one-to-many, multiple relationships, the fifth rule is used to normalize related, many-to-many multiple relationships. Related, many-to-many multiple relationships do not occur frequently in databases. However, when they do come up, these types of data relations can cause a great deal of confusion and hassle when you're normalizing your database. You won't invoke this rule often, but when you do it pays off!
Imagine that the Maintenance Department decides it wants to keep track of all the large equipment used on the shop floor by various departments. It uses this data to keep track of where the equipment is located. The Maintenance Department also wants to keep a list of suppliers for the equipment in cases of repair or replacement. When you were a novice, you might have decided to design a single table that held the department ID, equipment name, and supplier name. But, as I'm sure you have guessed by now, that is not the correct response. What if the Maintenance Department has more than one supplier for the same type of equipment? What if a single supplier provides more than one of the types of equipment used in the plant? What if some departments are restricted in the suppliers they can use to repair or replace their equipment?
NOTE: The fifth rule of data normalization dictates that you should isolate related multiple relationships within a database. In other words, if several complex relationships exist in your database, separate each of the relationships into its own table. Databases that adhere to this rule are known to be in the Fifth Normal Form.
The following list shows the relationships that have been exposed in this example:
Although each of the preceding business rules are simple, putting them all together in the database design is tough. It's the last item that really complicates things. There is more than one way to solve this kind of puzzle. The one suggested here is just one of the many possibilities.
First, you need to expose all the tables that you
need to contain the data. The preceding list describes two one-to-many relationships
(department to equipment, and department to supplier, with restrictions) and one
many-to-many relationship (equipment to supplier, supplier to equipment). Each of those
relationships can be expressed in simple tables. Two additional tables not mentioned, but
certainly needed, are a table of all the equipment in the building (regardless of its
location) and a table of all the suppliers (regardless of their department affiliation).
Table 16.5 shows sample field layouts for the required tables. The Equipment and Supplier
tables are shortened in this example. If you were designing these tables for a real
database project, you would add several other fields.
Table 16.5. The Fifth
Rule sample data tables.
| Equipment | Supplier |
| EquipID | SupplierID |
| EquipName | SupplierName |
| DatePurchased | SupplierAddress |
Listing 16.8 contains the SQL DDL statements to create these tables. Figure 16.7 shows the
results of executing these statements.
Listing 16.8. Creating the Equipment and the Supplier tables.
| CREATE TABLE Equipment (EquipID TEXT (10), EquipName TEXT(30), DatePurchased DATE, CONSTRAINT PKEquipID PRIMARY KEY (EquipID)) CREATE TABLE Supplier (SupplierID TEXT (10), SupplierName TEXT(30), SupplierAddress MEMO, CONSTRAINT PKSupplier PRIMARY KEY (SupplierID)) |
Figure 16.7. Supplier and
Equipment tables in NORMDAT6.MDB.
The next two data tables describe the relationships between Supplier and Equipment and
between Supplier and Departments. You remember that departments can be restricted to
certain suppliers when repairing or replacing equipment. By setting up a table such as the
DeptSupplier table described next, you can easily maintain a list of valid suppliers for
each department. Similarly, as new suppliers are discovered for equipment, they can be
added to the EquipSupplier table. Refer to Table 16.6 for a sample list of fields.
Table 16.6.
EquipSupplier and DeptSupplier tables.
| EquipSupplier | DeptSupplier |
| EquipID | DeptID |
| SupplierID | SupplierID |
These two tables are short because they are only needed to enforce expressed simple
relationships between existing data tables. Creating small tables such as these is a handy
way to reduce complex relationships to more straightforward ones. It is easier to create
meaningful CONSTRAINT clauses when the tables are kept simple, too. The SQL DDL
statements for these two tables appear in Listing 16.9. The result of executing these
statements in Visdata appears in Figure 16.8.
Figure 16.8. EquipSupplier
and DeptSupplier tables.
Listing 16.9. Creating the EquipSupplier and DeptSupplier
tables.
| CREATE TABLE EquipSupplier (EquipID TEXT(10), SupplierID TEXT(10), CONSTRAINT PKEqSpl PRIMARY KEY (EquipID,SupplierID), CONSTRAINT FKEqSplEquip FOREIGN KEY (EquipID) REFERENCES Equipment(EquipID), CONSTRAINT FKEqSplSupplier FOREIGN KEY (SupplierID) REFERENCES Supplier(SupplierID)) CREATE TABLE DeptSupplier (DeptID TEXT(5), SupplierID TEXT(10), CONSTRAINT PKDeptSpl PRIMARY KEY (DeptID,SupplierID), CONSTRAINT FKDptSplDept FOREIGN KEY (DeptID) REFERENCES Departments(DeptID), CONSTRAINT FKDptSplSupplier FOREIGN KEY (SupplierID) REFERENCES Supplier(SupplierID)) |
Notice that, in these two tables, the CONSTRAINT definitions are longer than the
field de-finitions. This is common when you begin to use the power database integrity
aspects of SQL databases.
Finally, you need a single table that expresses the
Equipment-Supplier-Department relationship. This table shows which department has which
equipment supplied by which supplier. More importantly, you can build this final table
with tight constraints that enforce all these business rules. Both the Department-Supplier
relationship and the Equipment-Supplier relationship are validated before the record is
saved to the database. This is a powerful data validation tool--all without writing any
Visual Basic code! Table 16.7 and the SQL DDL statement in Listing 16.10 show how this
table can be constructed. See Figure 16.9 to review the results of executing these
statements.
Table 16.7. The
Department-Equipment-Supplier data table.
| DeptID EquipID SupplierID |
Listing 16.10. Creating the DeptEqpSuplr table.
| CREATE TABLE DeptEqpSuplr (DeptID TEXT(5), EquipID TEXT(10), SupplierID TEXT(10), CONSTRAINT PFDeptEq PRIMARY KEY (DeptID, EquipID), CONSTRAINT FKEqSupl FOREIGN KEY (EquipID,SupplierID) REFERENCES EquipSupplier(EquipID,SupplierID), CONSTRAINT FKDeptSupl FOREIGN KEY (DeptID,SupplierID) REFERENCES DeptSupplier(DeptID,SupplierID)) |
The Microsoft Access database NORMDAT6.MDB contains a set of live data for the
tables described in this section. Use Visdata to open the database and review the table
structure. Try adding or deleting records in ways that would break integrity rules. Notice
that none of the last three tables defined (EquipSupplier, DeptSupplier, and DeptEqpSuplr)
allow edits on any existing record. This is because you defined the primary key as having
all the fields in a record. Because you cannot edit a primary key value, you must first
delete the record, and then add the modified version to the data table.
Figure 16.9. The
EquipSupplier, DeptSupplier, and DeptEqpSuplr tables.
Summary
In today's lesson, you learned how to improve database integrity and access speed using the five rules of data normalization. You learned the following five rules:
Quiz
Exercises
 |
 |