DAY 15 ACCESS CHAPTER 2 Larry Reaves larry

DAY 15: ACCESS CHAPTER 2 Larry Reaves larry. reaves@mail. wvu. edu October 7, 2013 1

LAST CLASS • • • Access Overview Tables Queries Forms Reports Relationships 2

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 3

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 arithmetic – Link tables using common fields 4

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 5

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 6

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

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 8

DESIGN TO ACCOMMODATE 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 9

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 10

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 11

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 Camel. Case for table and field names – The first letter of each word is capitalized and the spaces are removed – This. Is. ALong. Example. Of. Camel. Case 12

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: – Account. Number for an Accounts table – ISBN for a Books table – Student. Id for a Students Table 13

PRIMARY KEYS CONTINUED • When there is no natural primary key, you can create a primary key fields with the Auto. Number data type • This type will automatically increment each time a record is added 14

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 15

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 16

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 17

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) 18

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 19

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 20

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 21

ONE-TO-MANY • All the examples so far have been one-tomany • One-to-many is the most common relationship type 22

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 23

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 24

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) 25

NEXT CLASS • Queries – Query Wizard – Design View • Specifying query criteria (filtering results) • Sorting results • Copying and running queries 26