Day 15: Access Chapter 2

1. Day 15:Access Chapter 2 Larry Reaveslarry.reaves@mail.wvu.edu October 7, 2013

2. Last class • Access Overview • Tables • Queries • Forms • Reports • Relationships

3. Tables • A table is a storage location in a database that holds related information • A table consists of records, each record is make up of fields • When designing a database, the first step is to identify the tables you need

4. Choosing fields • Once you have identified the tables you need, you must add the necessary fields to each table using these guidelines: • Include the necessary data • Design for now and the future • Store data in its smallest parts • Add calculated fields to a table • Design to accommodate date arithmetic • Link tables using common fields

5. Include the necessary data • Ask what information will be expected from the system and determine the data required to produce that information • If the information can be calculated from the data it should not be included as a separate data field • If it will be commonly used, you can add a calculated field

6. Design for now and the future • Consider the future needs and build in the flexibility to satisfy those demands • Especially note the size of data types • A tinyint might be okay for an ID field for now, but it is limited to the values 0-255. After 256 records, you will not be able to create more records

7. Store data in its smallest parts • By dividing data up as much as possible we create more flexibility • Example: names • You could store names as one field like “Larry Reaves” • However, if you store names in two separate fields: first_name and last_name it is easier to create reports using different formats for the names • “Larry Reaves” • “Reaves, Larry” • “Mr. Larry Reaves” • “Mr. Reaves”

8. Add calculated fields • A calculated field produces a value from an expression or function that references one or more existing fields • Calculated fields are not available in Access versions earlier than 2010, and will be used on homework assignments • As always, Access 2010 is available in Open Lab or on the library computers

9. Design to accommodate date arithmetic • When dealing with periods of time it is better to store dates than numeric values • Example: age • If you store a person’s age in the database, it must be updated every time they have a birthday • If instead you store the birthday, you can use date arithmetic to calculate their age

10. Link tables using common fields • As you create tables and fields, keep in mind that the tables will be joined in relationships using common fields • Common fields must have the same data type and usually the same name, although different names are allowed • Avoid data redundancy • Data redundancy is storing duplicate data in two or more tables

11. Creating tables • Once your design is complete, you can begin creating your tables in Access • Table details can be specified in Datasheet view, Design view, or imported from another database or from Excel • No matter how it was created, a table can always be modified later to add a new field or change an existing field

12. Table and field names • Fields names should be descriptive and can include letters, numbers, and spaces • However, it is best to avoid spaces since they can cause problems when creating queries, forms, and reports • For this course we will almost always be using CamelCase for table and field names • The first letter of each word is capitalized and the spaces are removed • ThisIsALongExampleOfCamelCase

13. Establishing a primary key • The primary key is the field or combination of fields that uniquely identifies each record in a table • Primary keys should be selected to use unique and infrequently changing data • Examples: • AccountNumber for an Accounts table • ISBN for a Books table • StudentId for a Students Table

14. Primary Keys continued • When there is no natural primary key, you can create a primary key fields with the AutoNumber data type • This type will automatically increment each time a record is added

15. Foreign Keys • A foreign key is a field in one table that is the primary key of a different table • This key relates the records in the foreign key table to the records in the table with the matching primary key

16. Field properties • Fields have properties that determine how the field looks and behaves • Data type: specifies what type of data is expected for that field • Caption: allows you to set a readable label that is used in datasheet view, forms, and reports • Validation Rule: ensures the data entered is formatted properly

17. More Field Properties • Default Value: this value is automatically used for new records when a data value for the field is not otherwise specified • Required: indicates that a value must be entered for the field • Indexed: when set to yes an index is maintained that allows faster lookup and sorting by that field

18. Referential Integrity • When creating relationships, you have the option to “Enforce Referential Integrity” • When this option is checked, you cannot enter a foreign key in the related table unless the primary key exists in the primary table • You also can not delete a record from the primary table if it has related records (unless cascade delete is also enabled)

19. Cascading • Cascade Updates: • When the primary key is changed, the foreign key in the related table is automatically updated • Cascade Delete • When the record with a specific primary key is deleted, all related records are also deleted • Use with caution

20. Sharing Data with excel • External Data->Import & Link->Excel • Select file • Select worksheet • Specify if the first row contains column headings • Adjust properties for each field • Skip fields you don’t want to import • Choose a primary key • Name the table • External Data->Export->Excel

21. Relationship types • One-to-Many • This is a relationship between the primary key in the first table and a foreign key in the second table. The second table can have many records with the same foreign key. • One-to-One • Two different tables use the same primary key • Many-to-Many • This type of relationship requires an additional table with two foreign keys per record: one for each primary key in the two related tables

22. One-to-many • All the examples so far have been one-to-many • One-to-many is the most common relationship type

23. One-to-one • Two tables have the same primary key • Two uses for one-to-one relationships: • Extending a table where the existing design must be preserved for legacy reasons (for example, custom software that relies on the existing design) • Security: Sometimes you must split the record into two tables: one that any user can view, and one that has sensitive information whose access is restricted

24. Many-to-many • An additional table is used to link the two primary tables • This third table contains two foreign keys, one matching each primary key in the two related tables • Example: • Employees and Projects • Each Project is assigned to multiple Employees and each Employee has multiple Projects

25. Establishing relationships • Table Tools->Table->Relationships or • Database Tools->Relationships • Drag the primary key to the foreign key (one-to-many) • Drag the primary key to the primary key (one-to-one) • Drag each primary key to each foreign key (many-to-many)

26. Next Class • Queries • Query Wizard • Design View • Specifying query criteria (filtering results) • Sorting results • Copying and running queries