Understanding Database Normalization and Table Structures

1. Process of designing “good” databases, and table structures is called NORMALIZATION. This term is based on the mathematical concepts of NORMAL FORMS (First, Second, Third, Boyce-Codd, Fourth, Fifth Normal Forms). We won’t worry about the formal wording of normal forms, but we will use the concepts they embody to normalize our databases. Example definition of Third Normal Form (3NF): “A relation is in third normal form if and only if, for all time, each tuple consists of a primary key value that identifies some entity, together with a set of zero or more mutually independent attribute values that describe that entity in some way”

2. “Keys” • There is a way to uniquely identify each entry in a table. This is called the PRIMARY KEY.

3. To begin, examine each table, and highlight the field or fields that uniquely identify each unit of observation. This is known as the PRIMARY KEY (PK). A critical concept is that each record in a table does not have to have a unique identifier contained in a single field (i.e., each individual doesn’t necessarily have to have a Social Security Number or ID number). A COMPOSITE PRIMARY KEY (CPK) is a unique identifier composed of two or more fields. Shocking Event Table Stream ID Position (above/below) Segment Date Crew Segment Length Conductivity Water Temperature Weather Water Conditions

4. Often, it is convenient to have fewer (or even a single) fields represent the primary key. This creates some redundancy in the data, but can make it easier to use the database. This is one place where there are no hard and fast rules. Shocking Event Table Stream ID Position (above/below) Segment Date Crew Segment Length Conductivity Water Temperature Weather Water Conditions Shocking Event Table Shocking Event ID Stream ID Position (above/below) Segment Date Crew Segment Length Conductivity Water Temperature Weather Water Conditions

5. Note that there may be more than set of possible primary keys. In this case, each possible set is a candidate key. Good candidates for primary keys should 1. Uniquely identify each record in the table 2. It can not be null (i.e., contain missing data) 3. It can not be a multipart field 4. Generally fewer fields are better 5. Once set, the value in any of the key fields should rarely if ever be changed 6. If possible, select fields for primary keys that relate directly to the table name

6. Creating Tables in Access • Design view • type in Field Names • Determine Data Type -Text -Memo (won’t discuss) -Number -Byte, Long Integer, Integer, Single, Double, Replication ID, Decimal -Date/time -Currency -Auto Number (often used for primary keys) -Yes/No -OLE Object (won’t discuss) -Hyperlink -Lookup Wizard (special - discuss use) -Define Primary Key -Define validation rules - use expression builder

7. Table Relationships • Establishing table relationships is the way we draw together data from separate tables. This minimizes redundant data and maintains higher levels of database reliability. • Types of relationships • One-to-One • One-to-Many • Many-to-Many

8. Table RelationshipsOne-to-One

9. Table Relationships • One-to-one relationships are relatively rare in most database structures. Further, one-to-one relationships can be thought of as a specialized case of one-to-many. • The most common type of “true” one-to-one relationship is when one table is a SUBSET TABLE of another. Example- faculty are a subset of all university staff members. Graduate students are also a subset of all university staff. Thus, one way of implementing a personnel database would be to have a staff table (with information common to all staff members such as social security number), and separate faculty and graduate student tables to hold data that applies only to each of these specialized type of staff members. • When you have a one-to-one relationship, the linkage is achieved by having the same primary key in both tables (e.g., social security number in the staff table, and in the faculty or graduate student table).

10. Table RelationshipsOne-to-Many

11. Table Relationships One-to-Many Example-Many fish are caught at each site Site TableIndividual Fish Table Stream Name (CPK) Stream Name (CPK/FK) Position (CPK) Position (CPK/FK) Site (CPK) Site (CPK/FK) Year (CPK) Year (CPK/FK) Month (CPK) Month (CPK/FK) Day (CPK) Day (CPK/FK) Width Fish_ID (CPK) Depth Species Code Temperature Length Conductivity Age Linkage is established by taking primary key (PK, or composite primary key CPK) from “One” side of relationship, and inserting that into the “Many” side of the relationship. This now becomes what is known as a “FOREIGN KEY”.

12. Many-to-Many Relationships Student TableClass Table Student_ID (PK) Class_ID (PK) First Name Class Name Last Name Class Description Street Instructor_ID City Max Enrollment State Zipcode Phone Problem here is how to link Students with classes. Each student can take multiple classes, and each class has multiple students.

13. Rectifying Many-to-Many Relationships Enrollment Student TableTable Class Table Student_ID (PK) Student_ID (CPK/FK) Class_ID (PK) First Name Class_ID (CPK/FK) Class Name Last Name Class Description Street Instructor_ID City Max Enrollment State Enrollment (???) Zipcode Phone Notice that the Student-Class Table serves to link the Student Table and the Class Table. As such, it has both of the primary keys from each of these tables as foreign keys. The relationship between Student Table and Enrollment Table is One-to-Many (because each student can take many classes, but there is only one Student_ID, Class_ID combination in the Student-Class Table for each student Class Table and Enrollment Table is One to Many because each student can be enrolled in a class only once

14. Table RelationshipsRectifying Many-to-Many